• Categories

  • Recent Posts

  • Archives

  • Copyright Notice

    Copyright © Nancy Hidy Wilson, 2010-2013. Unauthorized use and/or duplication of this material without express and written permission from this blog’s author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Nancy Hidy Wilson and nancyhidywilson.wordpress.com with appropriate and specific direction to the original content.
  • Advertisements

TSQL2sday #015 – Automation in SQL Server

What is T-SQL Tuesday (twitter hashtag: #TSQL2sday)? Back in late 2009, Adam Machanic (blog | twitter) had this brilliant idea for a monthly SQL Server blogger event (the origin of T-SQL Tuesday) hosted each month by a different blogger.  This month’s event is hosted by Pat Wright (blog | twitter) and his selected topic is “Automation in SQL Server”.

If you’ve read my home page then you know the topic of automation is my primary focus – so this topic for TSQL2sday got me excited as it will also definitely help me towards my “techie” goals posted during TSQL2sday #014!

I think everyone knows why we automate – improve efficiency, reduce errors/waste; what we automate – repetitive tasks; and when we automate – cost of implementing automation is less than continuing “manual” method (calculate the ROI).   So – where and how should we apply automation in the SQL Server world?

Where? Installations, of course! As hopefully you are aware – there is more to installing SQL Server than just clicking next, next, next, … finish!  Sure SQL Server will be up and running if you do that – but will it meet your corporate standards?  Plus, there really is more you should do (configuration-wise) after running setup.exe. But the details of that are for a future post!  Right now, I want to focus on the how of automating the installation and configuration process.

SQL Server provides the ability to specify a configuration file containing the parameters required by the setup.exe so that you can run an “unattended” installation.  This has been available as far back as I can remember (which although I’ve worked with SQL Server since 4.21 days, I only confess to remember SQL 2000 forward!). Each version of SQL Server has a slightly different configuration file, so be sure to refer to the current BOL for the version you are implementing (although hopefully very few of you still have to do SQL 2000 installs!).

If you want to take your deployment automation even another step forward, SQL Server 2008 R2 provides the ability to SysPrep your SQL Server image – http://technet.microsoft.com/en-us/library/ee210664.aspx.  I haven’t personally tried this, but depending on your environment you may want to check out this feature.

Once you have your unattended installation files configured to ensure all your deployments install exactly the same –the possibility for mental errors by a well-meaning human (including yourself!) who simply misses changing a default on a screen to your corporate standard is greatly reduced. But, you probably want to further automate your post-install configurations, such as changing the default size and number of files allocated to tempdb, setting up your standard maintenance jobs, etc.  Or – maybe you want to run some “pre-install” steps before executing the SQL Server setup (like ensure PowerShell 2 is installed).  So how to package all this up into ultimate flexibility and reduce the possibility of missing a script?  Enter DBLaunch.

DBLaunch is a product from DBA24Hrs for helping DBAs with automating installation, configurations, and maintenance tasks.  From an installation task perspective, DBLaunch allows you to define all of your setup files (including service packs) and “configuration” scripts whether batch files, PowerShell scripts, or T-SQL scripts. Then you create “templates” for each distinct installation type that you need – SQL Server 2005 x86 Standard Edition w/SP4, SQL Server 2008 Enterprise Edition w/SSIS, etc. You can clone templates in cases where you want the same configurations overall, but maybe need a different collation setting for example.

The benefits which I personally witnessed? Documentation of the steps for an installation was reduced by about 75%! Personnel resource time to perform an install dropped from 4 hours (including wait time for processes to complete before continuing to the next step) to 10-15 minutes! After running manual pre-checks (mostly to assure storage is correctly configured), all the installer has to do in DBLaunch is select the template to use and specify the ServerName and InstanceName to be installed and click “start”.  The installer is then free to go do another task (including starting another installation on a different server); come back in 45 minutes to an hour (depends on target server location and bandwidth to installation source) and verify that the install completed successfully.  Prior to DBLaunch, it could take 2-4 hours to manually execute all of our installation process.  And, we are assured that no steps were missed thereby reducing time for troubleshooting failed installations almost completely which ultimately reduced our delivery time to the customer.

If you are in an enterprise environment, then you absolutely must use automated means to ensure a consistently delivered installation and configuration.  Start with developing your unattended installation configuration file, then look to see what other processes in your configuration can be automated via scripts, then take a look at DBLaunch to see if it can help you pull it all together.  I’m not aware of another tool in the marketplace which does this (and more) – we justified it strictly on the ROI from installations.

So, regardless of whether you build your own deployment tool or go with a third-party product, bottom line is that you will ultimately save time, money and frustration by automating your installs!