In my developer life, my motto was ‘Automate all the things’ and since transitioning to a DBA role, my motto hasn’t changed a bit. Since taking my first official DBA position earlier this year I found that other intelligent folks love automation as well. It seems that automation in IT is the smart thing to do. Automation = Consistency, and consistency in systems is what we strive for.
Upon joining the team at Raymond James (RJ), I was introduced to the automation suite surrounding our SQL Installs. Long time DBA and Sarasota SQL User Group leader, Michael Wells, created Scripted PowerShell Automated Deployment Engine (SPADE) for use in-house at RJ, but has since added it to CodePlex for use by the community. If the name doesn’t give it away, SPADE is a set of PowerShell scripts to automate the install and setup of all new SQL Instances. There’s not too much work going on in the CodePlex project, but thats because, it works.
Here’s a few points of its capabilities:
- Can Install SQL 2005 and Up
- Automated Service Packs and Cummalative Updates (2008 SP1 and Up)
- Can execute custom SQL and Powershell before and after the install
- Its FREE
The install and setup is nice, as is the SP and CU isntalls, but the true awesomeness of this suite, is the Pre and PostInstall steps. With our current setup, we use approximately 20 Pre–Install checks to ensure the server is setup to our standards, check connections to various support servers (CMS, File Shares, etc) and other taks. If any of those tasks throw an error, the process will stop so you can correct it. For the Post Install steps we apply all the standard best practices that you would apply to your servers such as TempDB Files, Max Memory, Lock Pages in Memory, and a whole host of other settings.
While we can register the new instance into our CMS with a Post-Install script, we have yet to figure out how to automate inserting it into our Confio Ignite monitoring instance which would really round out the automation of our installs. BUT…. we do have a daily check to compare the two and alert us when an instance is not being monitored, so in the spirit of automated things, I guess we’re covered here too.
In the DBA world, we have been given all the tools we need in order to automate our way into a stable, consistent environment. It will be up to you, the protector of the data, the DBA to take those tools and set them up in your environment.
Links:
- CodePlex – Scripted Powershell Automated Deployment Engine
- Pass Summit 2013 – Michael Wells Sessions
- Free Tool Review: Scripted Powershell Automated Deployment Engine – Kevin Kline
You can read more entries from this weeks T-SQL Tuesday, hosted by SQLChow
