One of the most tedious and necessary tasks I have been entrusted with is documenting and maintaining database server configuration. The question that came to my mind when I was assigned this task was the same as you would imagine – we have working backups, why is this necessary? There are many reasons why documenting servers is very necessary -
1 Server Failure – Rebuild – If the server has to be rebuilt from scratch – if you have backups that work most of the information you need can be retrieved. (If your backups do not work in such a situation most DBAs would find themselves without a job rather quickly :)) But many key pieces of information such as settings at the server level or cluster level, what you put on which drive and why, when was a service pack or a hotfix/patch was applied and why, and so on may be required and may be gone for good if you have not taken pains to document it.
2 Building an Identical Server – Recently we were faced with a scenario when we had to build a server for a certain company that was acquired – due to some security reasons we were not allowed access to the old server. The old server was not documented and the project was a communication nightmare, with frequent questions and repeated interviews with the other company’s DBAs. When your server is documented you still need to ask questions maybe on why a certain setting was done a certain way but it eliminates much of the pain related to such processes.
3 Transitioning server to another owner – The easiest way a new owner can gain an understanding of how the server is configured is to read through your documentation. He/she may be able to gain access to documentation a lot earlier than they even gain login access to the server. It helps them prepare what they need to know well ahead of time and ask all necessary questions.
4 Moving a database to another server – We recently had a vendor supported application database move from an older server to a newer one – the vendor was struggling with why the application is not working as it should – it turned out that a certain linked server was not configured the same way on the new server as opposed to the older one. Such information can be provided by documenting server settings and making sure they are the same on the new server.
5 Capacity Planning – Server documentation helps hugely with capacity planning issues – such as answering sudden questions from management –
1 Can a certain database be moved to this server, do you have space or capacity? OR
2 This application is going to double the database in size, how much SAN would you need, what performance issues do you expect?
So what is involved in documenting a server? First of all unlike old times documentation is unlikely to be restricted to one word document. It is likely to be backed up by diagrams in Visio, spreadsheets with numbers in some cases, and in some cases even periodic results of sp_configure or system dmv queries. So it is a good idea to create a folder to keep all this information together and have some sort of versioning system to protect and get the latest version. In my case a lot of what is needed is defined by requirements of Disaster Recovery Management team.
So the general layout of a server would be as below:
1 Word document with
- Hardware/Software Overview
- Server Settings
- Instance Settings
For each instance -
- Database settings and documentation
- SQL Server Agent settings
- Maintenence task details
- Other job details
- Linked server details
- Security details.
2 Word Document with Maintenence history -
1 Last server reboot with reason
2 Operating system patch/hotfix/servicepack – when and why
3 Database instance patch/hotfix/servicepack – when and why
3 SAN Layout Chart
4 Cluster Layout Chart if server is part of a cluster.
5 Special Documentation – if the server happens to be part of Replication, Mirroring, Log Shipping or any other.
6 SSIS Package documentation – If the server happens to be hosting SSIS packages (other than maintenence jobs) – then details of those packages and what they do.
7 Periodic results of xp_revlogin, sp_configure queries.
8 Excel spreadsheet with space/growth requirements – this may not exactly be considered part of server documentation, more of capacity planning, but comes in handy if you keep it there in case there are sudden requests to look into needs for space on the server.
In next part will look at each of these needs with a sample template.