Managing a large SQL Server environment poses a number of challenges to the DBAs and IT staff responsible for its maintenance. Keeping the databases available to their users is of prime concern to the individuals as is optimizing their performance to provide the best user experience. Best practices also stress the importance of monitoring the servers to enable proactive measures to be taken to avoid outages.
Another strategy that can be extremely useful when multiple SQL servers are involved is to periodically conduct an inventory of your servers. You may be surprised at the insights into your environment that are gained through an inventory. This information can help you make critical decisions regarding the current environment and any future changes that are being considered.
Why Inventory Your SQL Servers?
The intricacies of a small SQL server installation may be early understood by the responsible DBAs. When faced with a large number of server instances, it becomes virtually impossible to keep track of every critical aspect of the systems. This problem is exacerbated by the proliferation of cloud instances and virtual servers which further complicate the task of keeping up with changes to the environment.
There are many valid reasons to perform an inventory on your SQL servers. Some of them are:
- Performing capacity planning based on server growth rates.
- Ensuring that all databases are being backed up on time.
- Keeping track of licenses.
- Scheduling maintenance and understanding which applications may be affected by an outage.
- Troubleshooting issues in the SQL server environment.
- Identifying obsolete or unused SQL servers that can be decommissioned.
- Making informed decisions regarding the staffing and computing requirements to keep the SQL servers running at peak efficiency.
Conducting a regularly scheduled inventory is a good practice that should be adopted, but at times you will be compelled to run one for a particular reason. This could be after a merger when new servers are incorporated into your current environment. You might also want to run an inventory to provide a picture of the SQL server landscape to a new member of the DBA or IT management team. The knowledge gained from an inventory can be indispensable when planning changes to your installation, such as moving some of the work to the cloud.
Data to Capture in Your SQL Server Inventory
There is a wealth of information that can be obtained from your SQL servers. Here are some of the more important items that you will want your inventory to query.
- Details about the operating systems on which your SQL servers are running;
- Storage information regarding the drives and capacity available to the server;
- SQL version, edition, and patch level;
- SQL Server configuration settings;
- Database information including the name and owner of the DB;
- Information about SQL agents and scheduled jobs and processes;
- Backup history for each SQL server instance; and
- Login details and user permission settings.
These are just some of the data points that can be captured in an SQL server inventory. Using a quality inventory tool will enable you to gather these details and present them in an easily understandable format.
How an SQL Server Inventory Can Save Money
So, how can all of this information be used to save your company money? There are some obvious answers and some that might not immediately spring to mind.
Eliminating SQL server instances – Each instance that you maintain comes with some type of financial cost. It may be hardware that you need to provide or a bigger bill from your cloud provider, but adding instances means adding computing capacity. Getting rid of unused or duplicate instances can trim your on-premises and cloud budget, freeing those resources for other purposes.
Meeting customer SLAs – Understanding your environment and where potential problems may arise enables you to take proactive measure to mitigate issues that may affect your users. This makes it more likely that you can meet any SLA requirements regarding the availability of the associated databases and avoid paying for avoidable outages.
Constructing an intelligent maintenance program – A bird’s eye view of your SQL servers can help you assign resources more efficiently when performing upgrades or patching.
Capacity planning – The information provided by an inventory allows you to make smart choices regarding capacity requirements. You may be able to shift resources around to obtain the proper configuration. Without the inventory, you may make capital expenditures that are not necessary.
Compliance – The heightened focus on compliance introduces new challenges for all IT personnel. Knowing what applications reside on particular SQL servers can be critical information which allows you to control access to sensitive systems. Failure to take this step leaves you open to fines when you are found to not be compliant with privacy initiatives.
Tools for Conducting Your SQL Server Inventory
You could break out the spreadsheet program and start manually building your SQL server inventory database. If you had three servers, this may be an appropriate method, but for larger environments, an automated tool is required. IDERA’s SQL Inventory Manager is an excellent solution for constructing and maintaining your SQL server inventory.
SQL Inventory Manager offers a plethora of features that enable you to inventory and fully understand your SQL servers. A unified dashboard supplies a high-level view of your total inventory and can be accessed from anywhere over the web. Reporting on your inventory can be done with the built-in reports or by creating customizable displays tailored to your requirements.
SQL Inventory Manager is a comprehensive solution for inventorying your SQL servers. If you are mystified as to what is out there in SQL Server land, we suggest an inventory is in order as soon as possible!