This is number two on my list of Essential DBA tasks. This is really a disaster recovery function. The question to ask yourself is “Do I have all of the hardware and software configuration information that I would need if I had to re-create my SQL Server(s)?” These are high, server-level scripts that can be run on any/all servers that I am responsible for. I don’t want to tell you how to do this since each DBA has their own “preferred” way of operating. Just make sure that you do have this documentation…just in case.
So, what is needed here? I don’t make it fancy, but I want something repeatable. I know DBAs that document their hardware/software configuration in a document or spreadsheet; that’s fine, but how do you keep up with the changes? I prefer to have a small set of SQL Scripts that query all of the configuration information and dump the results to text files. Then I can just run the scripts on a regular basis to have the latest configuration documentation. I keep the text files someplace safe (i.e. NOT the SQL Server since it might be gone in a disaster!) so that I can use the text files to re-build if needed. My scripts are executed via SQLCMD that dumps the results to a text file; it’s that simple. You can even setup a SQL Agent job to run the script (say monthly) to keep the info up-to-date.
To give credit where due I didn’t come up with all of this on my own (just some of it)… The book SQL Server Tackle Box by Rodney Landrum (Simple Talk Publishing) has some good info on this subject especially in the early chapters.
Here’s a list of configuration items that I try to document; your list may vary and that’s fine…
SQL Software Configuration
- Server name (NetBIOS) and SQL instance name
- SQL Server product level, version, edition and license type
- Integrated security settings
- Cluster node options
- SQL Server collation and character set options
- Full Text install options
- Filestream install options
SQL Installation Configuration
- Sp_configure output
- List of all database files and locations
- List of all scheduled SQL Agent Jobs
- List of all SQL Logins
- List of all linked servers
- DBMail configuration