Server inventory is essential for SQL Server DBAs. It comes in handy when you are trying to get yourself familiarized with a particular SQL Server instance, collecting information before a server migration/upgrade project, preparing for business continuity procedure (BCP) testing, or re-evaluating server specifications for upgrade. It helps you understand how SQL Server is constructed and whether its capacity is going to last for ongoing business growth.
How SQL Server inventory is collected
SQL Server inventory collection ideally should be handled by an automated process. There usually will be a centralized inventory collector agent or process that connects to each individual SQL instance within the firm and fetches all the information it gathers and then writes into a centralized repository database. This repository database is then to be queried when needed, or in some advanced environments, a reporting service is used to present it in a user-friendly format, and an analysis service is used to summarized and aggregate collective historical information.
Of course, if you have not gotten to the point of automating server inventory collection, manually performing the work serves the same purpose.
Why SQL Server inventory is important
We often collect server inventory for information inquiry. The information is useful for cases like SQL Server migration, upgrade, BCP testing, disaster recovery, server consolidation, performance review and evaluation.
The biggest gain by collecting server inventory is to narrow down when things aren't working exactly the same as before or troubleshooting anything around SQL server. It serves as a referential material for a DBA to go back and see how things were set up and how we maintain the same status (if not improved) after any major system changes.
What to collect for SQL Server inventory
Now let's go over the top 10 things to collect for SQL Server inventory.
1. Operating System
In hardware specification, you will need to find out the location of the server, how many processors, the version and service pack for windows operating system, whether it's 32 bit or 64 bit, what model of the machine it is, how much memory the server has, and what are the program/application/services installed on this machine.
2. Drive, purpose and space availability
In a well-constructed server, each drive serves a specific purpose and they are properly allocated with sufficient space. So it's very important to find out how many drives the server has and how each drive is being used, whether they are mount point drive, local disks or 3par disk, how much space is left on each drive and if there is a need for space expansion.
3. SQL Server version and edition
It's imperative to find the version, edition and patch level of the SQL installation. We need to know whether it's SQL 2008 R2, SQL2008, SQL 2005 or 2000, whether it's standard or enterprise edition (the features and capacity offering on enterprise is much greater than standard edition). You will also want to know what service pack and hotfix the SQL installation is. It's also important to find out what SQL components are installed — is it just the database engine, or are analysis, reporting or integrated services also part of the installation?
4. SQL Server configuration settings
SQL Server configuration settings can be seen from the outcome by executing sp_configure. The default settings are well suited for most environments. You will want to keep track of your existing server configuration setting and make sure the new server or DR server has exactly the same setting values. You will want to see the same Min Server Memory, Max Server Memory and Max Degree of Parallelism defined properly for all environments, or take advantage of backup compression in SQL 2008 by enabling it on sp_configure.
This is one of the most important bits of information to collect when doing inventory. A SQL instance does not serve any purpose without user databases residing within. Here you want to make sure the following information is collected:
· Database Name, creation date and database owner
· Database logical file name and its physical location
· Database recovery model, full, bulk-logged or simple
· Compatibility level for each database
· Database file max size limitation, current size and file growth in MB or Percentage
6. SQL agent job/batch job/scheduled jobs
There are usually tons of jobs created around database environments. They are either database maintenance jobs to do backup, restore, index management, or application-related jobs to do data processing. It's important to get a grasp on all the jobs scheduled to run against the SQL instance. Often, firms spend effort on consolidating all jobs onto a single platform for better management, but even if your environments have jobs scattered on different schedulers, you can still manage them efficiently by maintaining inventory information about jobs.
We usually collect the following information about jobs:
· Job name
· Job schedule
· Active jobs or disabled jobs
· Category of the job, sample categories like...
o Maintenance job
o Application job
· Description of the job
· Job scheduler
o SQL Agent job scheduler
· Average run time
7. SQL Server backup history
You will want to know that what time the backup is supposed to be performed. If the timing of the backup looks off, then you will want to speak with your application owner to find out the best timeframe for database backup; some backups happen at certain time for a reason, and you will want to know the story behind it. By analyzing backup history, you can also learn about the maintenance window for each database , the database growth rate historically , how much space is needed for all backups, whether the database is in log shipping mode since transactional log backups are being taken.
8. DTS and SSIS
For DTS and SSIS, you will want to know what packages are created within the SQL instance and a high level understanding on what each package will do, and how they are being executed, through any scheduling system or SQL Agent jobs.
9. Logins, Users and permission setting
Maintaining proper inventory on logins and permission is so important that I believe all DBAs have faced users complaining about their logins not working. Below are the properties to be maintained, and make sure they are available anytime:
· Login creation statement along with password, default schema, default database
· Login system role setting
· Login server securable setting
· Login database access
· Login's corresponding user name within database
· User default schema
· User database securable setting
· User database role setting
· User database object permission setting
Last but not least, we will also want to cover the following items to complete server inventory:
· Proxy account created for running SQL agent job
· Linked server setup for the SQL instance
· Other RDBMS client installed on the server, such as Oracle, Sybase, etc.
· SQL Mail and Operator
· Network libraries enabled and TCP/IP port number
· Startup parameter and trace flag
· Startup stored procedure
Making it a habit to collect SQL Server inventory whenever possible will help you expedite the learning process on a system. During the process of collecting information, you will get to know a lot about the environment so there is no guessing work needed when it comes to why something is not working or why the performance is suffering or other problem, and of course, fewer sleepless nights.