Configuration Data Best Practice

After my recent Health and Risk Assessment visit from my Microsoft Premier Field Engineer, I learned some new and exciting  SQL Server best practices and I thought that I would share them with you in this blog space.  Our topic-o-choice today is all about backing up your configuration data.  I must admit that I have not given this topic a second thought in the past and luckily it has never bitten me before.  Microsoft defines configuration data as your sp_configure, SSIS packages and your replication topologies.  These items are not usually backed up in your nightly backups and may not be contained in your system databases.

Microsoft recommends scripting your SQL Server configuration data to a file using SELECT * FROM sys.configurations anytime you make changes to your server.  I would then include this in your disaster recovery walk-away bag or remote location destinations.  Personally, I would recommend documenting these settings if at all possible in a Word document explaining why you have chosen the settings and if they relate to your department’s best practices so that when you get hit by a bus, your replacement DBA will understand the intricacies of your choices and not chalk them up to the ‘this guy was an idiot who knew not the ways of the force.’

In addition, if you are using replication then you should script out all of the components for your disaster recovery.  It is also a good idea to back up your master and MSDB databases for the publisher, distributor and all of your subscribers.  I personally have never done that, but Microsoft recommends it…so do IT!  Enjoy!

Advertisements

About SQLGator

Florida Gator and Microsoft Data Platform MVP, Star Wars fanatic and is there anything else...oh yeah PS4! I am a geek and SQL Server DBA who dabbles with VMWare, there are other technologies greater than these? Not so fast my friend! I also love to travel to new and exotic places.

Posted on February 10, 2012, in Database Administration Tasks and tagged , . Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: