Bringing up baby

When I was a brand-new DBA pappa, I was so proud of my SQL Server. I watched sp_whoisactive all day. I would have Activity Monitor open looking for spit ups/issues. I would continually review the Job Activity Monitor for when my lil SQL Server would stumble, and jobs would fail.

Then when I had my second SQL Server, it was still the apple of my eye. For this second server, I would show it how special it was by having different locations for data files/logfiles/error files as compared to the first one. I would have job names named inconsistently, but it was ok, it was just my second server. I could have my maintenance rules, schedules, policies, etc. different because I know each server was it’s own special unique gift to the world.

Time started to fly. I had five servers. I still wanted to treat each one like it was my precious gem. Before I knew it, I had twenty servers. I desperately tried to remember each one’s name when I called after it, rather than just “Hey you! STOP THAT!” By the time I got to fifty servers, I hardly remember how old each one was. I struggled to recall the purpose of each server.

Ok, so maybe being a GREAT DBA is nothing like parenthood. You would never want to treat your children like commodities. But, to handle servers at scale, that is precisely what you need to do.

Inventory

You must have some means to keep track of all your servers. Creating a list of your servers could be as simple as an Excel sheet. Or it could also be as complex as its own database with tables. There are even third-party software solutions that will help you locate and manage an inventory for you. A few of the items you will want in your inventory are:

• Server name
• Business owner
• Applications housed on server
• Version and edition of the SQL server
• # of CPUs
• RAM size
• What type of environment (dev, test, QA, prod, etc.).

Monitoring

You can’t possibly expect to be able to keep an eye on even 20+ children without supervision. Great teachers can do it, but even they get exhausted after a time. Monitoring hundreds or thousands of servers is even more difficult. You need to invest in proper monitoring. If your company cannot (read: will not) pay to buy 3rd party monitoring tools, there are free ones out there. But for your sanity, help yourself keep an eye on your servers.

Failed Jobs

You could have failure notifications emailed to you or your DBA team’s distro list. Having the server automatically notify you will help as you grow. But it is hard to track those failures in emails. If your company uses a ticketing system to handle incoming requests or incidents, utilize that system to also handle failed jobs. Having a centralized location for working failures becomes even more critical when you have multiple DBAs. Otherwise, if you continue to just get emailed alerts to your full team then either:

• Each DBA will think they need to pick it up and work the issue, and then you are duplicating efforts.
• Each DBA will think someone else will pick it up, and no one will work the problem.

Standard file locations

When you are under the crunch to add new databases, review log files, look at errors, or move a file from one location to another, nothing is worse than trying to remember where in the world that file is. To make your life easier and to be able to help automate things, files should be in the same place on every server. I’m not going to tell you there is a right or wrong spot to place any file. The important takeaway is that the file is in the same place where you expect it, regardless of which server you are on.

Database/Table/Proc/etc naming convention

Same as the file locations, there is no right or wrong answer on the naming convention. But you need some sort of convention that you and the developers will follow. Otherwise it becomes more and more difficult the more unique you try to make every database. It also makes it more difficult to automate different tasks.

DB Maintenance

One of the most significant pains points, if you do not standardize, is your maintenance plans. Whether you use Ola’s scripts, the native tools, or some other third-party software, you need to keep these consistent. It makes it much easier to tell the business that you run full backups on this day of the week, or that log backups run every 15 minutes, or we reindex every 3rd night because you know it’s true across the board. Or, if you need to split this out, use some logic so that you can easily recall what that rule is.

Automation

I’ve alluded to this several times so far, but the final key that I want to leave you with dear reader is automation, Automation, AUTOMATION. You will never be able to manage more than a handful of servers without it. Being able to automate your process helps with many things:

• Looking at each server for some piece of data
• Rolling out new jobs or making changes to schedules
• Deploying the latest version of some script or code
• Even creating new SQL Servers or patching/upgrading existing ones.

The best/easiest tool for automation these days is PowerShell. For DBAs, the #1 module I would recommend is dbatools! There are so many different commands in that tool now it’s unbelievable. You could practically get away from using SSMS. With dbatools and the core commandlets in PowerShell, you can perform any task. This allows you to manage the SQL Servers widely far more easily.

The final piece to consider if your company develops custom applications is a DevOps mindset. This topic could easily be a full article (and in the future, I plan on at least one post). However, the simple summary is the idea of the pipeline – Progressing from Idea to Development to Testing to QA to Production in an automated and completely transparent fashion. It doesn’t just end there; you also need to be able to have your operations folks (DBAs and others) give continual feedback to the developers on improvements, problems, and critical areas on which to continually focus. And finally, this feedback loop needs to occur throughout the pipeline in small bite-size chunks that provide the ability to experiment and fail quickly/early in the pipeline so that problems arise long before they hit production. But when they do, your whole organization learns from it and puts countermeasures in place to identify that problem sooner in the pipeline. There is no way you can deploy reliably to dozens, hundreds, and definitely not thousands of servers without a DevOps style CI/CD pipeline in place.

Any other items I missed that you can think of that DBAs should know about as they start to accumulate more and more servers? Let me know in the comments below!