posts - 220, comments - 411, trackbacks - 27

My Links

Advertisement

News

Follow billgraziano on Twitter

Article Categories

Archives

Post Categories

Consulting

SQL Server

Utility to Script SQL Server Configuration

I wrote a small utility to script some key SQL Server configuration information. I had two goals for this utility:

  1. Assist with disaster recovery preparation
  2. Identify configuration changes

I’ve released the application as open source through CodePlex. You can download it from CodePlex at the Script SQL Server Configuration project page.

The application is a .NET 2.0 console application that uses SMO. It writes its output to a directory that you specify. 

Disaster Planning

imageScriptSqlConfig generates scripts for logins, jobs and linked servers.  It writes the properties and configuration from the instance to text files. The scripts are designed so they can be run against a DR server in the case of a disaster. The properties and configuration will need to be manually compared.

  1. Each job is scripted to its own file.
  2. Each linked server is scripted to its own file. The linked servers don’t include the password if you use a SQL Server account to connect to the linked server. You’ll need to store those somewhere secure.
  3. All the logins are scripted to a single file. This file includes windows logins, SQL Server logins and any server role membership. 
  4. The SQL Server logins are scripted with the correct SID and hashed passwords. This means that when you create the login it will automatically match up to the users in the database and have the correct password. This is the only script that I programmatically generate rather than using SMO.
  5. The SQL Server configuration and properties are scripted to text files. These will need to be manually reviewed in the event of a disaster. Or you could DIFF them with the configuration on the new server.

Configuration Changes

These scripts and files are all designed to be checked into a version control system.  The scripts themselves don’t include any date specific information. In my environments I run this every night and check in the changes. I call the application once for each server and script each server to its own directory. 

The process will delete any existing files before writing new ones. This solved the problem I had where the scripts for deleted jobs and linked servers would continue to show up.  To see any changes I just need to query the version control system to show many any changes to the files.

Database Scripting

Utilities that script database objects are plentiful.  CodePlex has at least a dozen of them including one I wrote years ago. The code is so easy to write it’s hard not to include that functionality. This functionality wasn’t high on my list because it’s included in a database backup.  Unless you specify the /nodb option, the utility will script out many user database objects. It will script one object per file. It will script tables, stored procedures, user-defined data types, views, triggers, table types and user-defined functions. I know there are more I need to add but haven’t gotten around it yet. If there’s something you need, please log an issue and get it added.

Since it scripts one object per file these really aren’t appropriate to recreate an empty database. They are really good for checking into source control every night and then seeing what changed. I know everyone tells me all their database objects are in source control but a little extra insurance never hurts.

Conclusion

I hope this utility will help a few of you out there. My goal is to have it script all server objects that aren’t contained in user databases. This should help with configuration changes and especially disaster recovery.

Print | posted on Monday, April 04, 2011 7:34 PM | Filed Under [ Utilities ]

Feedback

Gravatar

# re: Utility to Script SQL Server Configuration

Bill, this is awesome!

We were looking for a tool that does this just 4-6 weeks ago. We were prepping our DR site for a planned DR exercise that would run for 4 weeks, and I ended up doing the checks manually. 1-2 times a year we failover to our DR site to prove that we can and so that we can do major maintenance at our primary site. We did the planned failover in early March, and then this past Saturday we failed back to our primary site.

Your tool will definitely come in handy in the future. But shoot! I wish your tool had been released like two months ago. ;)
4/5/2011 12:21 PM | Tara Kizer
Gravatar

# re: Utility to Script SQL Server Configuration

I tried in on SQL Server 2008 SP2 and it worked fine. On SQL Server 2005 (9.0.4305) Standard edition it failed at the Database portion. I did try it on SQL Server 2000 and it failed as expected.

Thanks for making such a useful tool. I have been creating logins and jobs backup scripts, but having a single script do them all is a great timesaver. It even covers areas such as linked servers which I plan to do manually in case of a disaster recovery.
4/5/2011 1:12 PM | David
Gravatar

# re: Utility to Script SQL Server Configuration

Watch out! I ran this with a filespec parameter of \ and it erased my hard drive.
4/11/2011 6:58 AM | ricky lively
Gravatar

# re: Utility to Script SQL Server Configuration

Ugh! That's fixed in the current release. It now only removes directories it specifically creates.
4/11/2011 8:48 AM | Bill Graziano
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET