We’re Taking sp_BlitzUpdate Out to the Farm

Last year at the PASS Summit, one of my favorite moments in my Blitz session was showing off my new equivalent of Windows Update for stored procedures.  I demoed how to use OPENROWSET to connect your SQL Server to the cloud, fetch an updated version of our sp_Blitz® stored procedure, and apply the update automatically.

I knew it was risky and I specifically told the audience repeatedly, “You shouldn’t use this in production.”  It was chock full of nuts security risks.  You’re connecting to a complete stranger’s server and running the code you find.  I could be dropping your databases, and you wouldn’t know until it was too late.  Even worse, my SQL Server could get hacked and a malicious user could replace my tasty sp_Blitz® code with sp_HackTheServer code and trash your stuff.

It was a lot of fun while it lasted, and nobody got hacked – but it’s time to quit while we’re ahead.  This is really more of a security risk than we’re comfortable taking with your SQL Servers; after all, you trust us to take care of you.  We decided to shut down the update service and let everybody get their updates the old-fashioned way – through our Blitz page.

That’s not to say I don’t have tricks up my sleeve for this Summit, though.  One of them is up to you: vote for my Enterprise DBA: Bob Dylan Explains TempDB lightning talk.  Today’s the last day for voting, and I’ve been practicing my impression.  If you wanna see my next costumed session, vote now!

Previous Post
Potential Problems with Partitioning
Next Post
The Top Five Signs You Need a Break from SQL Server

9 Comments. Leave new

  • Jeffry Altamar Thalliens
    September 6, 2012 3:38 pm

    Regarding the comments on the sp_blitz on the filter, keeping in mind that the database tempdb cannot be back up, the script do not search for results on it, based on microsoft msdn,

    WHERE d.database_id 2 /* Bonus points if you know what that means */

    Backup and restore operations are not allowed on tempdb. http://technet.microsoft.com/en-us/library/ms190768.aspx*/

    Comments will be really appreciated.

    Reply
  • The blitz script could do with a check for 32 bit editions of SQL Server running without the \3GB switch on servers with 8GB or more of memory.

    Auto grow file increments of over say 1GB on servers without Instant Initialize would also be a good one.

    It’s a fantastic starter for 10 script though, thanks.

    Reply
  • Check if 32 bit edition, if yes check physical memory on the server, check MAX Server Memory Utilization and current virtual memory consumption by SQL Server. From these values calculate probably actual PAE, AWE & 3GB settings, and suggest ideal settings (based on assumptions such as only SQL running on the server and so on)

    Retrieve last auto-grow event from the default server trace and estimate whether Instant Initialization is probably on or off.

    There must be better methods but these are all I can think of.

    Regards

    Reply
  • A simple check that would enhance your script is ‘Does the database compatibility level match the server compatibility level’. Sometimes, SQL Engine upgrades are performed but this database setting is left at the pre-upgrade setting.
    Hope this one helps,
    Regards

    Reply
  • Hi Brent,
    I was on Powershell training all week and we were querying WMI but even with this you can’t get local security policy info such as ‘Perform Volume Maintenance’. A few bits are there under RSOP but not much.

    You can build really nice form based scripts in Powerrshell and your sp_Blitz script might work well run like this. It would open up a few new avenues such as checking server logs, what other processes and services are running, WMI information on drives and so on.

    It could work similar to the PAL tool, complete with yellow flashing progress updates 🙂 it wouldn’t be that difficult to add all Glen Berrys diagnostic queries to it too. Group yours and his as either server or database level scoped, multi-server executions via Powershell jobs and so on.

    I might have a go next week and wanted to let you knowI’ll be fiddling about with your SQL. I’ll leave all references and comments in there.

    Best wishes
    Paul

    Reply
    • Paul – thanks for the heads up. Just as an FYI – be careful with adding other peoples’ queries into your own tools. Often code published on the web is copyrighted or distributed under a terms of use – know how that works before giving away someone else’s code.

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}