In this post I will discuss the importance of metrics and how you can use them to represent your SQL Server environment, as well as how to collect a metric that a lot of DBAs don’t collect but should—the installation date.
At some point someone will ask you for metrics about your SQL Servers. When that time comes, you should be prepared with some of the following:
1. How many SQL Servers are installed?
2. How many SQL Servers are used as production?
3. How many SQL Servers are used as development?
4. How many SQL Servers are used as quality assurance (QA) and user acceptance testing (UAT)?
5. How many SQL Servers are used as disaster recovery?
6. How many SQL Servers are at your location, cloud, other sites, etc.?
7. What applications are using each SQL Server(s)?
8. How many databases do you have?
9. How big are the databases and total size of disk use?
10. What were the installation dates of the SQL Servers
Why do you want to track the installation date? If you already collect the above information and the date SQL Server was installed, you can show more information about your SQL environment, leverage it to show trends and even present it to management as an argument for hiring more DBAs.
Let’s start with the T-SQL Script
Here is the SQL script that will show the SQL Server name, version of SQL Server and date of installation of your SQL Server:
You may want also opt to use one of the SERVERPROPERTY values. SERVERPROPERTY also has a parameter that will allow you to return the edition, which may be useful for reporting on, especially if there is to be a consolidation project.
Here is a sample result:
SQL Server Name SQL Server Version Date Installed
VDV1OPS03 Microsoft SQL Server 2012 (SP1) - 11.0.3373.0 (X64) 2012-04-17 6:48:49.907
Execute this script on all your SQL Servers and record this information. You can record the results into a spreadsheet or a table. I won’t get into how to export your data, as I’m sure you can think of many ways to do this. I stored this information in a database.
What to do with the data?
Once you have recorded this information on all your SQL Servers, you can then use Excel Pivots or SQL Server Reporting Services (SSRS) to create all types of interesting information.
Here is some of the information that you can pull with the additional data:
• With just a SUM of the column, you get a total SQL Server count
• If you had previous years’ totals, you can show how many SQL Servers were added this and each preceding year
• If you sort your data by year and month, you can show how many SQL Servers were installed, when they were installed, and show when the busiest time for installation was
• If you include SQL Server versions, you can then break down the report based on versions with totals and when they were installed. With this data you can also see which SQL Servers need be upgraded and show corresponding names and departments (again, if you have collected this additional information)
• Sorting your data by types (production, development, UAT, etc.) you can show how many of each type you have. Once again, if you collected department data you can also show how many SQL Servers each one has
• With Excel Pivot tables you can produce a graph showing current growth rate and trend future growth
As you can see the list above, there is a lot if information you can pass on to management that will show interesting trends. I find that this type of reporting helps management to better understand your SQL Server environment.
In the next post, I will show some samples of what that data looks like.
For more information on SQL Solutions from Dell, and to download a free trial, go to: http://dell.to/1enPM6g