It’s time for the fourth post in my series about performing regular server reviews. In the first post, we took a look at setting up your local environment. The second post reviewed the workflow and quirks of RDP and VM environments, as well as getting SSMS set up on your machine. Part three led you through the first steps of creating a bio for your clients so that you and your backup can have all the information you need to get connected and working at your fingertips.
Today let’s take a look at the importance of building a server inventory and why it’s a critically important addition to your client bios.
NOTE: I’m using the word “client” throughout this post because my experience as a DBA is as an outside consultant. For full-time employees, different departments, teams, or groups within your place of business are essentially the same as clients. You can mentally replace the word “client” for one that is relevant to your circumstances and needs.
Build Your Registered Servers List
For starters, there’s sort of a chicken-and-egg situation when dealing with a new client. For the purposes of this post, we’re going to imagine you’re working with an amazing client that has given you a complete list of all the servers you’re expected to maintain and has already ensured you have access and permissions to all of those boxes.
Assuming you have all of this, now you’re ready to set up your Registered Servers list in SSMS. I’m not going to teach you how to set up your list in this post. To learn more I recommend reading “Registering SQL Server instances for easier management” from mssqltips.com. You can also learn more about Registered Servers at Microsoft Docs.
One tip I do have is to group your servers into subfolders according to their status or usage. For instance, we often separate the production servers from non-production servers in our lists. Many clients are not concerned with regular checks of development servers, but they do want us to have access in the event of some sort of problem they need help resolving.
For other clients we have not only created subfolders for Prod/Non-prod, but also nested subfolders for each set of nodes in an availability group.
What’s the point of a Registered Servers list? First, it gives you an easy-access listing of all the servers you’re responsible for maintaining. Second, the main advantage is that it gives you the ability to run your scripts against multiple instances all at once rather than one-by-one.
No matter how you set it up, be sure to craft your Registered Servers list in a way that meets your client’s needs and allows you to be a more efficient DBA.
Collect Server Info
Discovering the information we need to start building our server inventory is quite simple. Connect to your client’s servers and start up SSMS. From there, click the appropriate subfolder in the Registered Servers list and open a new query.
In the new query window, run the following simple script:
SELECT @@Version AS Version
This will return results similar to what you see below:
Some DBAs like to also use @@ServerName in their script, but as you can see it’s unnecessary since SSMS will return the server names along with the version information.
You can see that the script results provide the SQL Server version running on each server—along with the build number. The screenshot above is from one of my test boxes so the results are quite sparse. We’ll imagine we’ve got more to work on going forward for the purposes of this post.
From here, you can start building your Server Inventory List. My advice is to create a spreadsheet of some sort and file it with your client bio docs, or embed it in the doc itself if you can. Info that you’ll probably want to add to your spreadsheet includes the server name, the environment (Production, Development, Reporting, Archive, etc.), the current SQL Server version—including the build number, and the purpose and/or details relevant to the box.
Server Inventory List
|PRODSQL01\2016SP||Prod||2016 (RTM) - 13.0.1601.5 (X64)|
|PRODSQL02\REPORTSERVER||Prod||2016 (RTM) - 13.0.1601.5 (X64)||Reporting|
|PRODSQL03\SP2012PROD||Prod||2012 (SP3) (KB3072779) - 11.0.6020.0 (X64)||Website|
|PRODSQL04\AG1||Prod||2016 (SP1) (KB3182545) - 13.0.4001.0 (X64)||AG Primary|
|PRODSQL05\AG2||Prod||2016 (SP1) (KB3182545) - 13.0.4001.0 (X64)||AG Secondary|
|PRODSQL06||Prod||2016 (SP2-GDR) - 13.0.5102.14||Transactional replication
Log shipping primary.
|PRODSQL07||Prod||2016 (SP2-GDR) - 13.0.5102.14||Transactional replication
Subscriber from PRODSQL06.
|PRODSQL08||DR||2016 (SP2-GDR) - 13.0.5102.14||Log shipping secondary.|
|PRODSQL09\Monitor||Prod||2012 (SP3-GDR) (KB4019092) - 11.0.6251.0 (X64)||Monitoring/DBA jumpbox|
|DEVSQL10\Sandbox||Dev||2012 (SP3-GDR) (KB4019092) - 11.0.6251.0 (X64)|
|PRODSQL11||QA||2016 (SP1) (KB3182545) - 13.0.4001.0 (X64)|
You’ll want to keep this list up-to-date so you know what’s going on in your client’s environment. I like to update my list quarterly at minimum. Of critical importance is keeping the version build number fresh. I’ll explain some reasons why further below.
Putting Your Server Inventory List to Work
Once you have a complete server inventory list, take some time to study the list and see if any critical updates need to be applied to your servers. Some clients like to keep their installations up-to-date with the latest Service Packs and Cumulative Updates, while others take an “it’s working fine, so don’t touch it” approach.
At Dallas DBAs, we like to take a measured approach to updates. We don’t recommend applying the newest updates immediately unless there’s a security patch or hotfix that addresses a specific critical issue in SQL Server. We typically recommend against using an RTM version, and recommend only upgrading to a newer version of SQL Server after a couple of CUs are out and stable. Otherwise, we like to wait and see if a new update has bugs in it before recommending installation on our client’s servers.
Case in point, just look at how buggy Cumulative Updates for SQL Server 2019 have been: Microsoft pushed out the 2019 CU7 only to soon remove it and push CU8 as a fix what CU7 had broken.
Keeping up with patching can be critical since there are some absolutely deadly builds of SQL Server out there. Some builds are known to cause corruption during index rebuilds and should be patched immediately. (Example 1; Example 2). And wildly enough, sometimes you can run into a corruption bug when upgrading from one version of SQL Server to another.
Unsupported builds of SQL Server should also be updated. If there’s a problem with your servers bad enough to get Microsoft support involved, the first thing they’ll tell you to do is apply the requisite updates if necessary.
Ensuring your servers are up-to-date can also introduce additional features for free. Stop missing out on the best tools available to yourself and your clients.
If you’re not sure how to find out if your servers could benefit from an update, I highly recommend you grab Brent Ozar’s sp_Blitz script from his First Responder Kit.
Here are two great sources to find the latest information about available SQL Server updates:
If you want to take your Server Inventory List to the next level, check out Brent Ozar’s recommendations for your list.
In the next post we’ll take a look at adding a Maintenance Job list and Backup Frequency List to your client bios.