Next week I’ll also be presenting in person for the Tampa (Pinellas) SQL User Group (event link) where I’ll be presenting my “What is a BI DBA?” talk.

Also, I’m proud to say I’ll be presenting at this year’s PASS Summit conference in Charlotte! I’m honored to have secured a Spotlight (90-minute) session for the conference where I’ll be doing an extended version of “What is a BI DBA?” presentation.

Constant Vigilance!

For today’s post I’m channeling my inner Brian Kelley (Blog | Twitter) and talking about security, in particular Internet security. The post today is actually an email exchange between myself and my younger sister. She asked me for some general advice and I wrote a long response and figured I’d share it with you readers (which maybe you can in turn use to send to your own family seeking similar advice).

Without further ado, here’s the email exchange:

Hi Jorge,

   Quick question Michael and I want to be more vigilant about passwords and Internet protections. Do you think companies like Life Lock are worth it? What would you suggest recommend? What do you use? Let me know, Thanks,

Love you,

Vivi

 Constant Vigilance!

As we changed connections over, however, we quickly ran into a roadblock. It seems when we tried to make a connection to SQL Server via BO we got the error of ‘Unable to bind to Configuration Objects WIS 10901′. What made this situation strange is that from a Windows box you could connect but from Linux itself, it wasn’t having any of it. After some digging around we found we needed a third party ODBC driver to make this connection work.

It was suggested to us by the folks at SAP that we use a third party driver for ODBC connectivity. We were pointed to drivers by DataDirect (NOTE: This is not an endorsement for said product, this is simply the solution we tried and went with. There are several third party vendors that offer Linux ODBC connectivity so please evaluate and choose what works for your environment). Have you run in to this issue before? How’d you handle it? Feel free to share your solutions in comments.

This year the conference runs from October 15-18. I hope to see you at the Summit this year! http://www.sqlpass.org/summit/2013/

A quick way around this is by using the magnification feature in SQL Server Management Studio 2012. To do this simply hold down the Ctrl button on your keyboard and with your mouse scroll the mouse wheel up to increase the magnification and scroll down to decrease it.  Alternatively you can simply click on the magnification dropdown, which is located at the bottom left of the query window (by default) and select your desired level of magnification.

That’s it! Now you can quickly magnify your code to make it easier for your audience to see and you can reserve the zooming to highlight other areas as needed.

Welcome to the Roc!

Just a quick note that I’ll be presenting this weekend, Saturday May 11th, at SQLSaturday in Rochester, NY. Exciting to return to Central New York as I graduated right down the road at Syracuse University. Go Orange!​If you’re in the area come on down, should be a great day of training. Check out the day’s schedule (link), I’ll be presenting two sessions this weekend:

• Do More With Less: Consolidate and Virtualize
• Policy-Based Management in a Nutshell

You can follow the event along on Twitter using the #sqlsatroc hashtag as well. See you this weekend in the Roc!

Rumors of my demise have been exaggerated…

I Wrote A Book!

Well not by myself, but I was a primary author on very major title in Microsoft SQL Server Bible 2012. This was crazy since it was such a massive title and effort from all authors involved. Huge thanks to my fellow author team of

• Adam Jorgensen (Blog | Twitter)
• Patrick LeBlanc (Blog | Twitter)
• Jose Chinchilla (Blog | Twitter)
• Aaron Nelson (Blog | Twitter)

and contributing authors including:

• Audrey Hammonds (Blog | Twitter)
• Julie Smith (Blog | Twitter)
• Tim Chapman (Blog | Twitter)
• Scott Klein (Blog | Twitter)
• David Liebman (Blog | Twitter)

I’d also like to give a special thanks to our technical editors on this project:

• Kathi Kellenberger (Blog | Twitter)
• Wendy Pastrick (Blog | Twitter)
• Mark Stacey (Blog | Twitter)
• Bradley Schacht (Blog | Twitter)

After the book writing, I decided to take a break from writing/presenting for a bit. I needed a mental break to regroup and refocus. I have a tendency to overload myself with commitments and after doing that for a couple of years straight it starts to wear on you as well as your family.

PASS Summit 2012

In November was the annual PASS Summit conference in Seattle. Unfortunately I wasn’t chosen to speak at last year’s event (fingers crossed for 2013 in Charlotte!) but I was fortunate enough to attend. Exciting news was announced about SQL Server 2012, PDW and vNext information around Project Hekaton. As always, this conference was great not just for the vast amounts of technical knowledge shared and acquired, but seeing all my friends and extended SQLFamily! This year Pragmatic Works also debuted our sponsored Rock Karaoke party which absolutely ROCKED! For those wondering, yes, we will be doing this event again so make sure if you’re in Charlotte this year to stop by Pragmatic Works’ booth and pick up your wristband before they’re all gone!

Happy New Year!

New year rang in, the Mayans were wrong and we’re all still kicking. Not much more to say than that I suppose. In the resolutions department, I never really set any but my current short terms goals include:

• Getting back into blogging (regularly)
• Losing weight – I’m back up to my heaviest weight again
  • In the interest of actually having a concrete goal, let’s say initial goal is to lose 10 lbs
• Attain some 2012 certifications
  • MCSA SQL Server 2012
  • Perhaps the Private Cloud certification?

SQL University is Dead, Long Live SQL University!

This will be explained more in a separate blog post but yes, SQL University is undergoing a major change and for the better (I believe). Don’t worry, all the content you’ve come to know and love will still be available but the project/site as a whole is going to have a massive facelift. Stay tuned for more information in an upcoming post!

New Roost for the Chicken

There’s been a whole lot of moving on my part in the last month. The first move happened with this blog. For the last few years, Brent Ozar (Blog | Twitter) has been gracious enough to host, without charging mind you, the blogs of many of your favorite neighborhood SQL Server bloggers on his server. Unfortunately the free ride couldn’t last forever and we bloggers had to find a new hosting home.

I’d like to take a moment to give HUGE thank you to Brent. A huge part of my career advancement has been in large part due to to his mentorship, friendship and support. His blog on starting a technical blog was one of the large catalysts that helped me get into the SQL Server Community and going down that path was one of the best choices I’ve ever made. So THANK YOU Brent!

The second move was actually a physical one. For the last two years my wife Jessica (Blog | Twitter) and I have been living in Jacksonville, Florida. In February we made the move back to Tampa, Florida. For those wondering, yes, I’m still with Pragmatic Works. No, you don’t need to live in Jacksonville to work for them so if you’re thinking of applying to work with us (and yes, we’re always looking for great SQL Server talent, both BI and DBA) then shoot your resume over to jobs@pragmaticworks.com and tell them I sent you.

New Presentations

On the professional side of things I’ve been working on a few new presentations this year including:

• What Is a BI DBA? – Overview of administering the Microsoft BI stack (SSIS, SSAS, SSRS)
• Creating a Personal VM Lab – General walkthrough of how to create/setup VMs on your laptop for creating a personal test lab
• Do More With Less: Consolidation and Virtualization – Overview of consolidation strategies

I’m hoping to present at least one of these at PASS Summit this year, fingers crossed! I’ve been on a bit of a presentation tear the last few months. If you’d like me to present (remote is great too if your group supports it), please drop me a line at jorge@sqlchicken.com or shoot me a tweet. I love me some presenting!

Do you like training? Do you like FREE training? Then make sure you check out the 24 Hours of PASS event that starts today at 12:00 GMT. If you miss the events of the day, no worries, the recordings will be up within a few weeks.

Check out the great schedule of events:

Thursday, September 20, 2012

Session 01 (BIA) – Starts at 12:00 GMT
Choosing the Right Reporting Platform
Presenter: Brian Knight, Devin Knight

Session 02 (DBA) – Starts at 13:00 GMT
Best Practices for Upgrading to SQL Server 2012
Presenters: Robert Davis

Session 03 (AppDev) – Starts at 14:00 GMT
Three Ways to Identify Slow Running Queries
Presenter: Grant Fritchey

Session 04 (AppDev) – Starts at 15:00 GMT
Fasten Your Seatbelt – Troubleshooting the Most Difficult SQL Server Problems
Presenter: Klaus Aschenbrenner

Session 05 (CLD) – Starts at 16:00 GMT
SQL Server Private Cloud != Azure
Presenter: Allan Hirt, Ben DeBow

Session 06 (AppDev) – Starts at 17:00 GMT
What are the Largest SQL Server Projects in the World?
Presenter: Kevin Cox

Session 07 (AppDev) – Starts at 18:00 GMT
Practical Demos of Text Mining and Data Mining using SQL Server 2012
Presenter: Mark Tabladillo

Session 08 (DBA) – Starts at 19:00 GMT
PowerShell 101 for the SQL Server DBA
Presenters: Allen White

Session 09 (BID) – Starts at 20:00 GMT
Mobile Business Intelligence
Presenter: Jen Underwood

Session 10 (BID) – Starts at 21:00 GMT
Slow MDX Queries: The Case of the Empty Tuples
Presenter: Stacia Misner

Session 11 (DBA) – Starts at 22:00 GMT
Using SQL Server 2012 Always On
Presenters: Denny Cherry

Session 12 (PD) – Starts at 23:00 GMT
Leadership – Winning Influence in IT Teams
Presenter: Kevin Kline

Friday, September 21, 2012

Session 13 (BIA) – Starts at 00:00 GMT
BI Architecture With SQL 2012 & SharePoint 2010
Presenter: Rod Colledge

Session 14 (DBA) – Starts at 01:00 GMT
DBCC, Statistics, and You
Presenters: Erin Stellato

Session 15 (BIA) – Starts at 02:00 GMT
SSIS Design Patterns for Fun and Profit
Presenter: Jessica Moss, Michelle Ufford

Session 16 (AppDev) – Starts at 03:00 GMT
Characteristics of a Great Relational Database
Presenter: Louis Davidson

Session 17 (BIA) – Starts at 04:00 GMT
What’s All the Buzz about Hadoop and Hive?
Presenter: Cindy Gross

Session 18 (AppDev) – Starts at 05:00 GMT
Taking SQL Server Into the Beyond Relational Realm
Presenter: Michael Rys

Session 19 (BIA) – Starts at 06:00 GMT
Agile Data Warehousing with SQL Server 2012
Presenter: Davide Mauri

Session 20 (AppDev) – Starts at 07:00 GMT
Digging Into the Plan Cache
Presenter: Jason Strate

Session 21 (BIA) – Starts at 08:00 GMT
Introduction to Microsoft’s Big Data Platform and Hadoop Primer
Presenter: Denny Lee

Session 22 (BID) – Starts at 09:00 GMT
Big Data Analytics with PowerPivot and Power View
Presenter: Peter Myers

Session 23 (CLD) – Starts at 10:00 GMT
Best Practices and Lessons Learned Using SSIS for Large Scale Azure Data Movement
Presenter: Steven Howard

Session 24 (PD) – Starts at 11:00 GMT
Mentoring for Professional Development
Presenter: Andy Warren

* Please be sure to check our time zone guide for exact times in your area. This 24 Hours of PASS event begins at 12:00 GMT on September 20 and runs for 24 consecutive hours.

Sure, that may not be too bad on a table with a few hundred rows but what about on a table with millions? That could cause a lot of performance problems since you’re trying to read all the data off disk (again, potentially).  Secondly, do you really need all those rows and/or columns? It’s a waste of time and resources to pull back every column from a table if your application is only going to be using a few of them anyways.

So how do we prevent this? Well I recently learned an extremely evil way of preventing such a query. I’d like to start off with, this was NOT my idea. I learned this trick from Adam Jorgensen (Blog | Twitter). I’d also like to add this disclaimer:

DO NOT JUST GO DO THIS IN YOUR PRODUCTION ENVIRONMENT! I am not responsible for whatever evil you turn loose upon your environment. Always test things out in a development environment first and get proper approvals before making any changes.

Pure Evil Method

This method is actually evil in its simplicity. What we’ll be doing is adding a new column to the existing table. The “trick” is that this will be a computed column whose formula will cause an error, specifically a divide by zero error. As shown in screenshot below, create the new column on the table and call it something obvious like ‘DoNotSelectAll’. In the Column Properties window, under the Table Designer section, there is a property called Computed Colum Specification. In the formula section, enter (1/0). Save your table.

Now if I try to do my SELECT * on this table, I’ll get this lovely message:

Alright, we learned our lesson, now we’ll explicitly name our columns that we need:

Now this last query worked but notice how I didn’t put a WHERE clause so it pulls back all rows anyways? Yup, your users can still pull back everything, but at least they’re not doing a SELECT *. Also keep in mind, if you’re used to right-clicking that table in SSMS and selecting TOP 1000, with this column in place it will error for you as well. What are your alternative options?

LESS EVIL METHODS

Abstraction

Another way to control this kind of behavior is by not letting users hit base tables at all. You could create Views that have queries in them that limit rows returned. This way a user can do a SELECT * on a view, but the underlying code of the view itself is limiting row returns.

Depending on your situation, this could work and it could not. If the user needed very specific data returned that wasn’t in that limited pool of results could adversely affect whatever process they’re using the data for.

Another option is wrapping everything in stored procedures and granting users access to executing stored procedures rather than querying tables and views. On the one hand, could be good since you’re encapsulating the code. Users can pass parameters to stored procedures so you could make the queries somewhat dynamic.

Handbrake

In SQL Server 2008 they introduced a feature called the Resource Governor. This feature allows you to throttle resources on queries based on custom functions and groupings you specify. Yes, it’s an Enterprise Edition feature but it can be well worth it if you’re having resource-related issues due to runaway queries.

Now this feature will NOT prevent users from doing SELECT * –type queries, however you can throttle how much resource is allocated toward a query so you can at least control how badly it’ll affect you.

Security

My friend Brian Kelley (Blog | Twitter) will probably appreciate this one. Be stringent with the accesses you grant! Grant users only the accesses they need. Also, ff you don’t want users banging against your transactional systems directly, think about setting up a dedicated/isolated reporting environment and point the users there instead.

The reporting box you stand up doesn’t have to be (necessarily) as beefy as your transactional system and you can setup customized security on that database. This is especially helpful for when the transactional system is a vendor application which you can’t make any modifications to the code.

Do you have any other suggestions/tricks to help prevent crazy user queries? Let’s hear it in the comments!

The need for this solution came from the fact that despite my having alerts for failed agent jobs, we had a process pull a Palin and went rogue on us. The job was supposed to process a cube but since it never failed, we (admins) weren’t notified. The only way we got notified was when a user finally alerted us and said “the cube hasn’t been updated in a couple days, what’s up?”. Sad trombone.

As Devin mentioned in his post the code/solution below is very much a version 1 product so if you have any modifications/suggestions then have at it. We’ve documented in-line so you can figure out what the code is doing. Some caveats here:

• This solution has been tested/validated on SQL Server 2005 (SP4) and 2008 R2 (SP1).
• Code requires a table to be created in a database. I’ve setup a DBAdmin database on all servers here for custom scripts for DBAs such as this, Brent Ozar’s Blitz script, Ola Hallengren’s maintenance solution, Adam Machanic’s sp_whoisactive, etc. You can use any database you’d like to keep your scripts in but just be aware of the USE statement at top of this particular code
• This solution requires that you have Database Mail setup/configured
• To setup this solution, create an Agent job that runs ever few minutes (we’re using 5) to call this stored procedure
• FYI, I set the mail profile name to be the same as the server name. One – makes it easy for me to standardize naming conventions across servers. Two – Lets me be lazy and code stuff like I did in the line setting the mail profile name. If your mail profile is set differently, make sure you correct it there.
• Thresholds – This is documented in code but I’m calling it out anyways. We’ve set it up so that any job whose average runtime is less than 5 minutes, the threshold is average runtime + 10 minutes (e.g. Job runs average of 2 minutes would have an alert threshold of 12 minutes). Anything beyond a 5 minute average runtime is controlled by variable value, with default value of 150% of average runtime. For example, a job that averages 10 minute runtime would have an alert threshold of 15 minutes.
• If a job triggers an alert, that information is inserted into a table. Subsequent runs of the stored procedure then check the table to see if the alert has already been reported. We did this to avoid having admins emailed every subsequent run of the stored procedure.

CODE (WARNING: This code is currently beta and subject to change as we improve it)

Last script update: 7/24/2012

Change log:

7/12/2012 – Updated code to deal with “phantom” jobs that weren’t really running. Improved logic to handle this. Beware, uses undocumented stored procedure xp_sqlagent_enum_jobs

7/24/2012 - Updated code to v 1.16 to deal with email alerts error. Removing code from blog post and asking folks to instead download directly from download link below. Formatted code on blog makes it messy and a pain when updating.

Download script link – Click here

Got any feedback/comments/criticisms? Let me hear them in the comments!