Automating SQL Server installations / maintenance


I am laying down the plans for a standard server installation at work.  I want to have all production systems setup the same way to reduce the installation and maintenance overhead for each server.  Ideally, I won’t have to do anything by hand until the instance is setup and SQL Server is running.

Of course, I am a firm believer of not re-inventing the wheel, so my first course of action was to see what the great encyclopaedia called the internet could offer up…. and it didn’t fail me!

Ola Hallengren’s MaintenanceSolution

The first resource that I found comes from Ola Hallengren (http://ola.hallengren.com).

He has developed a set of maintenance scripts that do Integrity Checks, Database Backups and intelligent Index Reorgs/Rebuilds.  I took a first look at these scripts quite some time ago and proceeded to set them up on one of my production systems (mainly for the index maintenance).  I must say, that the code is nicely written and commented and allows you to easily see what Ola is intending to do.  I made a few small changes to fit my environment and the jobs have run happily ever since.

SQL Server FineBuild

The second resource that I found was SQL Server FineBuild from Ed Vassie (http://sqlserverfinebuild.codeplex.com).

This is a tool to help standardise SQL Server Installations (ha-ha! just what I want). After taking a look at the very detailed documentation and taking a few hours going through the installation options I had a completed configuration file for the installation.  I then took a brand new test server and set FineBuild on its way and waited to see the results.

FineBuild warned me that the installation could take 40 minutes or more, so I got on with something else, and when I looked back to see how FineBuild was doing it had created a summary .txt file showing that it had finished installing in 20 Minutes and everything had been successful.  I now had an all singing all dancing SQL Server installation with extra tools installed, all DB-Files separate from LOG-Files, and a plethora of added-extras to boot.

I then went about taking this installation and modifying the config file to further optimise the installation for my environment.  This was not necessary, but I wanted some of the cosmetic folder naming schemes to be kept and so on.

I am still tinkering with this config and integrating Ola’s scripts into the mix and will be setting up 5 identical servers for a replication system in the near future.  This will easily save me a couple of days setup and configuration.

Conclusion

Ola’s Scripts are great for all DBAs, but especially for those new/accidental DBAs who have little experience/knowledge, but have to look after SQL Server.  They have an easy entry point, but allow experienced users to configure the scripts to fit their requirements.

FineBuild is definitely for the more advanced user/dba. The wide range of options and add-ons means that a greater base knowledge of SQL Server is required before even thinking of using this tool.  If, however, you are comfortable setting up and administering SQL Server, you will find this tool extremely helpful in reducing your admin overhead.  The fact that once the settings are finalised (normally a one of task), you can then install an unlimited amount of servers, secure in the knowledge that each one will be identical in the basic structure.

A big thank you goes out to Ed Vassie and Ola Hallengren.  Both of whom have created resources that will save lots of time for DBAs everywhere!

Advertisements