Monitoring SQL Servers

I have only been a DBA (database administrator) for a while now. One thing Brent Ozar recommends in his training’s is knowing your inventory. I didn’t realize how important it was until I started patching servers.

What I realized it is very easy to get lost in amount of information DBAs can gather, more easily if you work for a big firm and dealing with several different products, in different environments. Information overload is bad, hence I recommend having a simple system to collect data that you can track and repeat on regular basis. Remember it is not about doing it now, but how you can pull up your sleeves and get it done over and over again whenever needed and how quickly.

Here is a simple sql query that helps me get to know exactly what I need to know about each of my servers.

What am I collecting

1. Server Name
2. KB Version (This is optional)
3. BuildVersion — Since not everyone know what each build version means, we need to convert it into meaningful name. This comes in handy when creating reports for upper management.
4. ServicePack and Cumulative Update (This is good to know and audit to make sure that all your servers are on same level if need to be -unless there are exceptions)

Select @@SERVERNAME 
,SUBSTRING(@@VERSION,(PATINDEX('%[A-Z][A-Z][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%',@@version)),9) as KBVersion 
,ServerProperty('ProductVersion') as BuildVersion 
,CASE WHEN LEFT(CAST(SERVERPROPERTY ('ProductVersion') as VARCHAR),4) ='10.0' THEN 'SQL Server 2008' When LEFT(CAST(SERVERPROPERTY ('ProductVersion') as VARCHAR),4) ='10.5' THEN 'SQL Server 2008 R2' When LEFT(CAST(SERVERPROPERTY ('ProductVersion') as VARCHAR),4) ='11.0' THEN 'SQL Server 2012' When LEFT(CAST(SERVERPROPERTY ('ProductVersion') as VARCHAR),4) ='12.0' THEN 'SQL Server 2014' When LEFT(CAST(SERVERPROPERTY ('ProductVersion') as VARCHAR),4) ='13.0' THEN 'SQL Server 2016' When LEFT(CAST(SERVERPROPERTY ('ProductVersion') as VARCHAR),4) ='14.0' THEN 'SQL Server 2017' ELSE SERVERPROPERTY ('ProductVersion') END AS SQLServerVersion ,SERVERPROPERTY('ProductLevel') as ServicePack 
,SERVERPROPERTY ('ProductUpdateLevel') as CumulativeUpdate

In next article, I will share how to generate report and have count ready, to answer questions when your CTO drop by and ask you how many servers we have and what version they are on.