Think Outside the Box

SQL Multi Script

I enjoy reading what other people in the industry have to say, I might do something one way whereas someone else might do the same thing totally different but equally good if not better. I try to read from some of the top DBA’s in the industry ~ I have picked up so much from them and different techniques that have enhanced my capabilities in my own work situations.

In saying that I happened to be reading on Steve Jones’ blog the other night and he laid down a challenge. Well…….maybe not a challenge per say but I think he hit the nail on the head and it challenged me. Many of us have many types of tools at our disposal; I just so happen to have some that Steve mentioned in his blog from Red Gate. Basically, it is easy for us to get stuck in a rut and rely on the same tools that we’ve been using for months, years, or even decades.

I personally have the Red Gate Tool Belt at my disposal. Have I used all of them ~ nope. Have I even opened all of them ~ nope. So in reading Steve’s blog I felt challenged to test drive each product that was in the tool belt. Well, you know what IT PAID OFF.

I will try to lay out my scenario in how this has helped me. I have many .SQL files stored in TFS in various directories for replication along with corresponding index files. When having to blow away replication or if updates have been made to the files and a deployment is needed then my normal routine would be to go to each directory pull what I need, place it into an analyzer and execute my code.

This was a painstakingly slow process. So instead I completed the following:

  • Created two SSIS packages – I was dealing with .SQL files for publications and then files for indexes. Now I get the latest from TFS; execute the first package to grab all my publication files and dump them into one directory (will explain why in a second). The second package grabs the index files and pushes them out the the server in any of my environments.
  • I go back to my directory with all my publications and because I use SQL CMD variables in them I open each one up in sequential order and remove the commenting out of the variables I need. Then execute each one in analyzer; each one is ran simultaneously.
  • I then use the SQL Multi Script 1 utility from Red Gate that allows me to select .SQL files I created to start each agent by generating a snapshot automatically instead of having to start each one manually from the replication monitor.

Needless to say I took about a 45 minute process and turned into a 2 minute process. Using tools that you have available to you pays off; I’m making a point to go through what I have if I’ve never used it before and see if I can shore up any other processes I have.

Leave a Reply

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

You are commenting using your 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 )

Connecting to %s