SQL

Welcome back to the series “Server Review Essentials for Accidental and Junior DBAs.” So far in this series we’ve taken a look at how to set up your work environment, best practices for onboarding new servers and clients, and prepping your servers for daily reviews.

What You’ve Accomplished Thus Far

After all of our work and prep during the last several posts in this series, it’s finally time to get down to the “real” work of a DBA.

Wait, that’s not quite right. While it may seem as if everything covered so far in this series has been pretty basic and production servers have barely been touched, don’t get the idea that the work done so far isn’t of high value. Think about some of the things you’ve already learned about your servers during this preparation process:

• You’ve gathered all of the necessary credentials needed to log into VPNs and individual servers, verifying the accuracy of those credentials.
• You’ve set up a stable work environment.
• You’ve built a starting personal script library that will grow and evolve over time.
• You’ve collected “biographical” information about every server you’ll be reviewing, so you have a baseline to see how everything stands before you begin reviews.
• You’ve learned the expected RPO and RTO for all of your databases.
• You’ve built a Server Inventory List that shows the status and use of every server for which you will be responsible for administrating.
• You’ve discovered the current patch level of all the servers within your purview, which will give you vital knowledge as you move forward.
• You’ve learned what maintenance jobs are already active on your servers.
• You’ve learned what the current backups look like and whether or not you’re currently meeting RPO and RTO.
• And you’ve created a DBA Tools database on all of your servers so that you’re prepared for regular server reviews.

Why is all of the above so vital? If I’ve learned one thing in my two years of database administration it’s that there seems to be a large number of folks who really have no clear understanding of what’s going on in their server environments. Their approach to preserving their data is more seat of the pants and reactionary than proactive monitoring.

As outside consultants, these are the types of folks who typically end up calling us to say, “my server is on fire!” Many of the issues we see on an “emergency” basis could have been prevented with regular reviews.

A Proactive Approach

At Dallas DBAs, we much prefer a proactive approach to database maintenance and performance. When we take on a new group of servers, the first thing we do is run a health check on the boxes and search for any issues that may need immediate remediation or any server settings that can be adjusted for immediate benefit.

While these topics are not covered in this series, we’re often looking for common issues related to things such as Cost Threshold for Parallelism, Instant File Initialization, MAXDOP, max server memory, the current state of backups, TempDB configuration, and more.

This gives us a launchpad to implement all of the prep work mentioned in this series so far, and better guide the people who are depending upon the data to run their business or otherwise get work done.

By taking a proactive approach, we often know about problems days or hours before our clients do. We can see if backups have failed, ensure index maintenance is occurring regularly, and keep an eye on job failures and errors.

We often prefer to do a quick 15 minute or less check of all servers daily, running a handful of scripts that I’ll begin sharing in the next few posts. These scripts quickly show us the state of the backups, reveal any job failures or errors we need to investigate, and find alerts we may need to be aware of.

This proactive approach also helps us get to know what is expected from the servers. As we report job failures and errors, we may learn that certain issues are common knowledge and are not necessarily causing the end-users any pain.

We learn how our data users feel about deadlocks. They may not care about them if they don’t “feel” them. A job that runs every 10 minutes and is the victim of a deadlock once or twice a day likely isn’t going to be an issue. Sometimes job failures are simply random “network blips” that happen periodically, so all you can do is name a note, shrug your shoulders, and move on.

That’s all for now. Next time we’ll take a deeper dive into looking at investigating your server’s backup history.

If you’d like to know more about SQL Server Maintenance, check out Kevin’s Getting Started with SQL Server Maintenance course at Pluralsight.

Follow me on Twitter at @SQLandMTB, and if you’re into mountain bikes come over and check out my site NTX Trails.

Follow @Dallas_DBAs

Welcome back to our series, Server Review Essentials for Accidental and Junior DBAs, where we’ve been taking an in-depth look at how to get ready to perform regular SQL server reviews for your company or clients. So far, we’ve covered how to get your working environment set up (Part 1 | Part 2) setting up biographical information about your environments, collecting server information, building a maintenance job list, and backup frequency list, as well as providing sample biographies and server reports.

Now it’s time to move forward and put all your preparation to use and perform regular server checks…almost. We’ve got one more round of prep work to do before your environment is completely ready to go.

Verify RPO

Before you go any deeper into this process, now is the time to ensure all of the servers you’ve been entrusted with are meeting the stated Recovery Point Objectives. My advice at this point is to not worry over the Recovery Time Objective until you’ve ensured you’re meeting RPO. Your company or clients won’t care how fast their data can be recovered if they can’t recover the data they actually need.

Use the backup frequency list you created in an earlier step and take the time to look through each server to see if the backup schedule meets the stated RPO in terms of FULL and LOG backup frequency (and DIFFs if relevant). We’re not confirming the existence of the backups yet, we’ll do that in a later step. For now, just make sure the backup jobs are scheduled properly to meet your RPO requirements.

If you find that your backup jobs are not scheduled properly, get with your senior DBA or the relevant person in authority and correct the backup job schedules accordingly.

Create Your Own DBA Tools Database

Though not necessary, I’d suggest creating your own DBA Tools database on each of the servers for which you are responsible. Most companies and clients won’t have any problem adding such a database since it is lightweight, provides tools beneficial to your server health, and creates a sandbox in which those tools can reside separately from the data.

This is a common method for us here at Dallas DBAs. Whenever we’re given the responsibility for administrating a new server or environment, one of the very first things we do is evaluate the current state of the server, implement some best practices, and create a new database like this to install our preferred scripts.

We’re not going to cover the steps to creating a new database for your tools. If you don’t know how to do that, check out the Microsoft Docs to learn how to make a new database. Having said that, we feel it’s always best to put the database in SIMPLE recovery model and make sure the database owner is “sa”. Otherwise, you can pretty much use the defaults in creating your new DBA Tools database.

What scripts do we install and use regularly? In the following posts in this series we’ll showcase some of the scripts we use, but below you’ll find a list of scripts and stored procedures we like to implement on the servers we touch.

Keep in mind the purpose of this post is not to explain how to install or use any of the scripts and tools listed below, only to show you some of the tools we like to use.

• Ola Hallengren’s SQL Server Maintenance Solution is our preferred tool for all database backups, integrity checks, and index optimization. His scripts are far superior to the built-in Maintenance Plans in SQL Server.
• Adam Machanic’s sp_WhoIsActive.
• Brent Ozar’s First Responder Kit, which includes fantastic tools such at sp_Blitz, sp_BlitzCache, sp_BlitzIndex, and more.

Get Ready for Proactivity

Now your servers are primed for regular reviews, which means you will often know about potential issues long before they become catastrophic failures. Having done all of this work doesn’t guarantee your servers will be problem-free, but it does mean your servers will be less likely to suffer common issues and you’ll be better equipped to handle issues as they arise.

If you’d like to know more about SQL Server Maintenance, check out Kevin’s Getting Started with SQL Server Maintenance course at Pluralsight.

Follow me on Twitter at @SQLandMTB, and if you’re into mountain bikes come over and check out my site NTX Trails.

Follow @Dallas_DBAs

Let’s take a look at the final few steps of your initial server intake process and get your workflow up and running as you push toward performing regular server reviews. These last few steps are relatively easy and quick to set up.

(Also see Part 1, Part 2, and Part 3 of this series)

Build a Backup Frequency List

Now that you’ve set up your Maintenance Job list, you should be able to see how backups are scheduled for your servers. This is the point where you begin pushing from simply gathering info on your servers towards ensuring your servers are set up correctly.

Depending on your situation and environment, you may not need to create an actual list in a document or spreadsheet. Sometimes simply having a familiarity with what databases are backed up and their frequency is enough, but it may be best to create an actual list in the beginning.

You’ll need to be able to determine what types of backups are happening—FULLs, DIFFs, LOGs—and when those backups occur. Once you’ve collated this information then you’re ready to see if the current backup scheme meets the expected Recovery Point Objective and Recovery Time Objective. If not, then getting your backups whipped into shape should be the first task you assign yourself for your new servers.

I’m personally at the point where I have enough servers spread across different environments that I need to create a new writeup of backup schedules for those environments. There are currently too many environments to keep straight in my head, which means it’s far too easy to miss whether or not a particular server group’s backups are on schedule and up-to-date.

How so? Some of the server groups I check are scheduled to create Full backups daily, while others are only creating these backups weekly. I have some server groups in which there’s a mix of scheduling, and others that have either native SQL Server backups, virtual device “snapshot” backups, or sometimes both. One client in particular has two server groups that I check regularly, one of which is using native SQL Server backups while the second group is primarily using virtual device backups.

So, if you decide to write up an actual list, what do you include? Here’s a not-so-comprehensive list of items you may want to include in your document:

• Database name
• Recovery model
• Backup types (FULL, DIFF, LOG)
• Backup frequency for each type (weekly, daily, hourly, etc.)
• Schedule/frequency of each backup type
• Backup path/device
• Backup retention period
• Backup compression yes or no
• Native SQL Server backup, virtual device backup, or other
• Maintenance Plan, Ola scripts, or other

The above is simply a sampler of items you may choose to include. Again, depending on your environment and SLAs, your list might look somewhat different.

Example Server Review Report

Now that you’ve gotten all of your server’s baselines gathered you’re ready to start implementing regular checks. How often you perform these server reviews will depend on your employer’s dictates or current SLAs. For me, I currently have several server groups I check daily, one server group that is checked twice per week, and one server group that is only checked once per week.

What’s included in my regular reports? In the coming weeks I’ll be stepping through how I get my reviews done, but for now I’m including a sample report below so you can get a sense of the sorts of items you’ll be looking over as you check your servers.

ABC Server Review 03-22-2021

Servers:

     Unable to connect to ABC-SQL-03. Upon ping "Ping request could not find host ABC-SQL-03.". 

     Recommend applying CU22 for SQL Server 2017 and Security update for SQL Server 2017 CU22: January 12, 2021 [CVE-2021-1636] to ABC-SQL-01. 

Third Party Monitor:

     If there are any third party monitoring products in use—such as Solarwinds DPA or Redgate Monitor—any current alerts get notated here.


Replication:

     ABC2020_Pub5 is reporting Critical performance.

     All other publications are reporting Excellent performance.


Jobs:

     The job 'Write Stuff to the Table' failed at step 'Load Stuff' on ABC-SQL-01 on 03-21-21 at 4:00 AM. This job is scheduled to run hourly and failed previously on 03-17-21. The job has executed successfully since 03-21-21 at 5:00 AM.
          The job was the deadlock victim.

Backups:

     All backups are up to date.

Errorlogs:

     Login failure for user 'ABC\lcage' on ABC-SQL-02 on 03-21-21 at 7:36 AM. Reason: Could not find a login matching the name provided. [CLIENT: 100.10.10.1]

     66 deadlocks on ABC-SQL-02 on 03-21-21 through 03-22-21.

Alerts:

     No alerts to report.

AG Health

     All Availability Groups reporting Healthy.

Other:

Your own reports may look significantly different than what you see above. Obviously, if your servers are not using features such as replication or Availability Groups then there’s no need to include them in your report. Depending on your employer’s or client’s expectations—and your SLAs—your report may be more or less detailed. You may even have some sort of ticketing system in place.

In my case, I send my reports to my senior, Kevin, as an attachment to an email for his review. I’ll note any items I believe are critical and need immediate attention in the body of the email and highlight them in red text. There are certain issues which are appropriate for someone at my level that I will rectify on my own, otherwise Kevin and I will address issues together, or he will contact the necessary personnel who oversee particular aspects of the servers as needed. This is the workflow that works for us, but your practices and procedures may look different.

Diving Deeper

At this point, you may need to go even deeper and to build a full server inventory which is more comprehensive than what I’ve laid out in the last few posts.

This may also be the point where you need to do a full Health Check on your servers and look for any best practices that need to be implemented and note any problems you find that need to be addressed. Create a list of these items and your suggested recommendations for remediation, as well as a potential schedule of when and how it might be best to address these issues.

Stay tuned! In the next group of posts we’ll start an overview of implementing server monitoring and active reviews.

If you’d like to know more about SQL Server Maintenance, check out Kevin’s Getting Started with SQL Server Maintenance course at Pluralsight.

Follow me on Twitter at @SQLandMTB, and if you’re into mountain bikes come over and check out my site NTX Trails.

Follow @Dallas_DBAs

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

Server Name	Environment	Version	Purpose
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 Publisher. 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:

• SQL Server Updates
• SQL Server Builds

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.

If you’d like to know more about SQL Server Maintenance, check out Kevin’s Getting Started with SQL Server Maintenance course at Pluralsight.

Follow me on Twitter at @SQLandMTB, and if you’re into mountain bikes come over and check out my site NTX Trails.

Follow @Dallas_DBAs

Welcome back to my ongoing series about how we perform regular server reviews here at Dallas DBAs, and the tips and tricks I’ve picked up in my first 1.5 years of work as a DBA. If you’re new to the series, go back and check out the Setting Up Part 1 and Part 2 posts where I step through the most basic beginnings from setting up your work environment to getting your script library in order.

Today, we’re moving forward with getting familiar with your client, their servers, and their specific needs. Keep in mind that this is all written from an outside consultant’s point of view. If you work in-house as a DBA for a company or corporation, some of what you read here will be different than your experience. I challenge you to take what you read and see if you can apply it to your work environment and become a more efficient DBA.

Create a Client Bio

I’ll admit from the beginning of this post that much of what you’ll read below may not apply to you if you work on-prem. For those of us who jump from client to client throughout the day, getting a baseline of information at the beginning can save you from many headaches in the future.

Here at Dallas DBAs, we have set up client bio documents in Microsoft Teams for each client we serve. This is a very convenient way for us to not only access all of a client’s vital information, but we can also share and edit the information for our entire team quickly and easily.

We feel like the best approach to writing a client bio is to think about what another DBA would need to know in order to do your job in case you’re unavailable. How can you continue to provide your clients the best service possible in the event you have a car accident or get sick and are unable to work for a while? How can you best help a backup DBA cover for you effectively?

With that in mind, here’s a rundown of what we collect for our client bios.

Contact Information

Be sure to collect the various names, email addresses, and phone numbers of relevant team members from your client. To whom do you send daily/weekly server reports? Who do you contact if you discover an issue on a server? How do you contact them? Who will contact you in an emergency?

Connectivity Information

How do you connect to the client’s servers? What VPN client is used and where can it be downloaded? Are there any special notes or connectivity quirks another DBA needs to know about? Do they connect to a jumpbox or directly to production servers?

Put together a list of VPN URLs and RDP IP addresses as necessary.

What about credentials? Can the backup DBA share your credentials, or do they need their own? What is your client’s policy on that score? Does your backup have all the permissions they need to get the job done? Make sure your backup has all the credentials and clearances they need in your absence.

This is especially important if your client uses some sort of two-factor authentication. If you haven’t gotten your backup set up ahead of time, they’re going to need to jump through extra hoops to get the work done.

Environment Features

Make note of things like whether the servers are on-prem or VMs or a mix of the two. Is the client using any of SQL Server’s “special features” such as replication, log shipping, or availability groups?

What about third party monitoring tools such as Manage Engine, DPA, SQL Sentry, or Redgate SQL Monitor, etc? Does your backup even need to bother with checking these? If yes, then what are you looking for and what are the reporting procedures? (For instance, one of our clients likes to see screengrabs of some of the graphs on a weekly basis.) Also if yes, make sure your backup has the credentials they need to access these additional tools.

Task List

What, specifically, needs to be checked on the servers and how often? Write up a list of tasks that should be performed and their frequency. Depending on your SLA, some clients may get daily checks while others will only receive weekly checks. How often are deeper health checks performed and when?

Detailed Procedures

Once you’ve got the task list put together, be sure to create a detailed explanation of procedures for your client’s server check. It may seem sort of pedantic to type up a list of what scripts need to be run and how the results should be reported, but this is actually the most vital portion of your job when it comes to performing regular server checks. This is how are able to discover and remedy potential problems before they become serious issues.

And remember, you’re writing this as if your backup knows nothing about the client’s environment. Which scripts need to be executed? Where are the scripts located? Against which servers? How frequently should the scripts be run?

This is where you will likely want to create a repository of scripts and other files you use for your client. Create an archive of your custom scripts that are particular to your client. If you’re using password management software, add the master file to this archive. Are you using Remote Desktop Connection Manager for this client? If yes, have you provided an RDG file? What about a REGSRVR file for the Registered Servers list in SSMS?

Think about gathering everything you need to get stuff done, not only for your backup, but yourself. Imagine some strange scenario where you couldn’t access your work computer during a client emergency? Could you borrow a laptop from someone and get your work done?

But wait! There’s More

There are a few more things you should consider including in your client bio such as a server inventory, maintenance job list, and backup frequency chart. I’ll cover these in the next few posts.

You’ll find that gathering all this information is extremely helpful not only for your potential backup, but for yourself as well. I can’t tell you how many times something weird has happened on my end that prompts me open up the client bio and find the information I need. Yes, write the bio as if your backup needs to start from a position of complete ignorance of the client, but also write it in such a way to help yourself remember what to do for your client and how to get it accomplished.

I’ve found that the greatest challenge is keeping client bios updated. Very often I find myself in the situation where things have slowly changed in tiny increments over time, so it’s easy to make personal adjustments as necessary without updating the bio. Eventually all these changes add up and I’ll discover that my client bios are very out of date and it takes some time to update everything.

In fact, as I write this, I see that I’ve got some work to do on my own client bios…

If you’d like to know more about SQL Server Maintenance, check out Kevin’s Getting Started with SQL Server Maintenance course at Pluralsight.

Follow me on Twitter at @SQLandMTB, and if you’re into mountain bikes come over and check out my site NTX Trails.

Follow @Dallas_DBAs