Using Replication to keep your Admin Scripts Current

We all have handy scripts that we want present in our SQL Server database instances. Examples would includesp_WhoIsActive, sp_Blitz, sp_BlitzCache, Ola's maintenance scripts or custom scripts like Estimated Time to Complete. But how do you keep all your instances updated when new versions are released? You certainly don't want to run the updated scripts on each instance one-by-one, especially if you manage quite a few instances. One way to keep them in sync is by using a Central Management Server (CMS) and running the updated script on multiple instances at once. However, what if there are some instances that you don't really want or need to deploy the scripts to? That was my situation, so I opted to use SQL Server Replication to keep my scripts in sync.

I personally have an instance that I call my "UTILITY" instance. I use this instance as my CMS. It's also where I store all my monitoring databases like Spotlight, utility databases like ClearTrace and most importantly - my central DBADB. I have a DBADB in every instance I administer. It's a place to keep all your DBA related scripts and tables (maintenance logging tables, sp_BlitzCache output, harvested wait stat data, etc.) and you can have peace of mind that nobody else is going to go in there and mess with them. I then created a publication on my DBADB, selected only the stored procedures that I want to deploy and set up subscriptions to all the target instances. With just a few clicks of the mouse, I was able to deploy out the latest and greatest version of all my administrative scripts.

Full post: bit.ly/1r6Te9o