In the last post we looked into the needs for having a database server documented and the various components of database documentation. In this post i will explain each component and also attach some templates for your reference.The main document is as below.
· Overview
The purpose of the document – to be used for disaster recovery procedures, new employee training, audit….<any other>. Most larger companies have specified formats for this.
· Hardware/Software Overview
Necessary information from <My computer>, <View System Information> and <Computer Management>, <Device manager>. It is important to emphasize ‘necessary information’ because there is a LOT of information here and you can run documentation into several pages of screenshots. What is necessary should be determined by organizational needs and consulting with people in charge of network, hardware and Windows. It may not be the DBA’s responsibility to capture and keep all of this information. In our case what is needed was only computer name, IP address, Model #, CPU, separate charts for SAN and Cluster as necessary, Memory installed, version of windows and list of services on the machine.
· Instance Information Settings
For each instance – document instance name, and version. For most of the other settings, you can run sp_configure with advanced options on a consistent basis and store the results.
(
sp_configure 'show advanced options', 1
GO
reconfigure
GO
sp_configure
GO (cut and paste results))
Database settings and documentation
For each instance, run the query select * from sys.databases and keep the results separately or as part of the documentation.
· SQL Server Agent Settings
There is no easy way out of this other than right click on the agent and take screen snapshot of each tab. Most people do not modify agent settings at all, so if you are very confident there is nothing of concern here this can be safely left out.
· Backup Details
Maintain a simple table minimally stating schedule and nature of backups. If backup is run by 3rd party tools there may be special documentation needed such as where the tool keeps its log, failure/error logging and so on.
|
DB Name
|
Full backup
|
Differential
|
Log
|
Stored at
|
|
mydb
|
Daily 9 pm
|
Every 2 hours
|
Every half hour
|
\\apps\sqllogs
|
· Maintenance task details
Document list of maintenance tasks with warnings or remarks attached in case of failure or other situations such as changing recovery model.
|
Task
|
Frequency
|
Remarks
|
|
Reindexing – 1 (mydb1, mydb2)
|
Friday night 8 pm
|
CAUTION: Changes recovery model to simple so check recovery model if failure or any other situation happens. Very important for app1 performance.
|
|
Reindexing – 2 (mydb3, mydb4)
|
Saturday night 8 pm
|
CAUTION: Changes recovery model to simple so check recovery model if failure or any other situation happens.
|
|
Integrity Check
|
Daily 9 pm
|
Very IO intensive, not to schedule any io intensive task during this time.
|
Other jobs can be documented depending on how important they are – you can right click and script the job or add custom documentation. In our case we choose to document reindexing and database integrity check jobs since that is an important piece of information for DBAs to know.
· OPTIONAL: Job Calendar – A job calendar is a useful piece of documentation when there is a need to schedule more jobs or find the right time for any activity. There are many tools available that can show a good job calendar, the one we use comes with Quest Software’s Capacity Manager. There are queries also that can be run to pull this details such as http://www.sqlservercentral.com/scripts/Jobs/69088/.
· Linked server details – Right click on the linked server and script the linked server out with ‘create’.
· Security details – Keep periodic results of sp_help_revlogin as described http://support.microsoft.com/kb/246133
In addition to this document the following documents may be maintained:
1 Maintenance history document- lists history of maintenance/changes performed on the server with reasoning and approvals.
· Last server reboot with reason
· Operating system patch/hotfix/service pack – when and why
· Database instance patch/hotfix/service pack – when and why
2 Cluster Layout Chart if server is part of a cluster.
3 SAN Layout Chart – as applicable, usually maintained by SAN Administrators.
4 Special Documentation – if the server happens to be part of Replication, Mirroring, Log Shipping or any other.
5 SSIS Package documentation – If the server happens to be hosting SSIS packages (other than maintenance jobs) – then details of those packages and what they do.
The attached zip file has a basic template with some sample attachments also. A complex mission critical server will have several documents in addition to this. If maintained with diligence and care these documents can be extremely important in times of crisis or even to impart necessary learning skills to a team member.