DBA / Developer Tool Set

As part of a SQL Manchester pass presentation I did called “The Proactive Database Professional” I showed  a few things that could make you more productive. One was a customisable tools set, which  I have detailed below

The first thing needed is a set of scripts, ( downloadable links at bottom of the blog from One Drive / Google Drive)  these consist of the following procedures.

The names are quite descriptive in what they do, but as shown shortly you can add much more to these to make them useful tools

DBA_tools_list

The code in the scripts are basically DMV( Dynamic Management views) and SQL that I have acquired/written over the years while being a developer / DBA / Data Architect.

One that should be on every production system is Adam Mechanics sp_WhoIsActive  procedure, which is also part of the tools.

These consist of routines that can help you monitor / check for issues or can get you out of a fix. I’ll list / show some of them in future blogs in more detail. But putting them on a development system and having a play will show you more yourself.

For each Procedure ( these are created as procedures to make them easier to use / remember – I do have a standalone version for system that you can’t roll out procedures too) I add it in the format as sp_DBA_[Name of the procedure] the method in the madness is that you can then create an index of these useful procedures.

This is done by creating 3 extended properties as below

Extended_Events

  1. Version This allows you to easily view versions on different servers and keep versions upto date
  2. Description – Says what it is on the tin, description of what the procedure does and how it can be used
  3. Parameters  – Parameters that the procedure takes

Then using the code from sp_DBA_tools procedure it displays the tools available with description , version and parameters is can take

Additionally I make this a short cut key ( Tools / Option / Environment / Keyboard)  – will need to open a new query window before it works

Short_cutkey

You will get something like the following by executing sp_DBA_tools

sp_DBA_tools

Rolling these out is easy you can roll out individually, Obviously we don’t want to do that as were being proactive database professionals right, so we can create a master roll out script. We can use SQLCMD – (Query \ SQLCmdmode in SSMS) below we specify file location (1), the best thing with this its on your local machine. So you can change to Server A or Server B and the script will still work as its looking at the same location

We then we specify the name of the SQL file which is the name of the procedure (2) and when executing it will run all the procedure in at a single time to that server. So literally its a 30 second job to roll these out onto a new server

Rollout_script

I also have a set of PowerShell scripts that allow these to be pushed out to many servers at a single time. This will be shown in the next blog post.

 

Links to zip file

GoogleDriveOne Drive

Advertisements
This entry was posted in Management Studio, Productivity, Tips & Tricks, Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s