As today’s database administrators (DBAs) manage the complexity of modern database environments, particularly containerized systems, they must lean into smart automation to transform database management. When DevOps teams and DBAs combine effective automation with Microsoft SQL Server use, it allows organizations to experience better server monitoring and increase server performance while removing common hurdles to software development.
When Should I Automate: Understanding Automation Use Cases
Before implementing any type of automation within your SQL Server, it’s important to understand the specific ways and instances in which automation can benefit your teams.
For example, optimizing backup and maintenance tasks is one of automation’s basic benefits. In fact, there should be a sense of alarm if a database or DevOps team is not automating these tasks.
Database teams that don’t implement daily automation spend valuable time each day manually performing regular maintenance or running server backups to support SLAs (service-level agreements).
In addition to wasting time that could be spent doing other tasks, manually performing maintenance or backups can lead to unnecessary errors or, worse, lost data. With the proper scripts, DBAs can schedule regular preventive maintenance—such as fragmentation or retrieving index statistics—to occur when there is little to no use of the SQL Server. Teams can also schedule automatic server backups based on the RPO (recovery point objectives) and RTO (recover time objectives) that your team has set in place.
Automation is also valuable for less frequent, more intensive tasks. This could include defragmentation or special end-of-the-month/year processing. Most DevOps or database teams implement processes such as defragmentation (or the largescale organization of individual files in a server) to free up space in the server, thereby increasing server speed.
Automating large-scale tasks such as defragmentation allows DBAs to become proactive in database management instead of reactive after there’s an issue with storage space or file availability.
In addition to automating tasks within your server, it’s also important to automate notifications. This is especially important for mission-critical operations such as data integrations. An automated alert allows immediate intervention if there is an issue with a mission-critical function, thereby avoiding server downtime or file corruption.
Common Automation Patterns
There are multiple automation patterns, particularly in Microsoft SQL servers, which can help both DBAs and less database-fluent DevOps personnel.
If you’re a veteran DBA, you may be familiar with Windows batch files, which are an old-school way to automate cmd.exe prompts, i.e., the command prompts used for file maintenance within a Microsoft SQL Server. It’s common practice for both experienced DBAs and DevOps personnel to combine batch files with Windows Task Scheduler. This way, you can create a command using Windows batch files and then schedule that command to take place at the cadence of your choice.
Another effective automation method is using T-SQL Scripts with SQL Server Agent. This method is more popular with DBAs who operate within a database instance. In addition to the standard commands that come with database management—such as copying, deleting, file transfers, etc.—SQL Agent allows you to create commands that have multiple steps and create alerts in case anything goes wrong during automation.
Lastly, PowerShell is an automation tool that provides an enhanced level of security with server automation. PowerShell allows DBAs to create and provide login credentials for any instance in which automation would take place. Now, you may be wondering, “To get better at automating, do I have to write all these scripts myself?” Not at all! The good news about the SQL Server community is that the members of the community share with each other. For example, if you’re using T-SQL Scripts with SQL Agent, you can check out ola.hallengren.com and minionware.net. For PowerShell, try dbatools.io or dbachecks. readthedocs.io/en/latest.
Best Practices for Automation
To ensure that automation doesn’t become a waste of time or cause more confusion in SQL Server management, there are a few best practices every team should implement.
Practice Standardization: Automation can have adverse effects on productivity or server remediation if each team associated with the server isn’t on the same page. It’s important for any automated job to have the same name and description across teams. This makes it much easier for all team members to understand what is going on should an alert about an error reach their inbox.
In addition, each team should share the same processes when announcing an alert. For example, each alert email should have as much information in the subject line as possible. This means providing the error number, incident severity level, the abbreviated server name, and the name of the person managing that particular server function. This allows the DBA and/or all necessary parties to respond appropriately. While it’s important to standardize across teams, it’s also a good idea, if possible, to standardize names and processes with vendors.
Understand the Type of Automation: When adopting automation processes into your server management, it’s important to understand the type of automation you’re implementing. Namely, is the automation reactive or proactive? Or, is it triggered or blind?
Proactive automation allows DBAs to get ahead of potential server issues before they happen. For example, DBAs may create an automation job that allows the server to send specific server statistics if the CPU begins to operate over a certain capacity. This allows the DBA to head into any remediation efforts with the exact reasoning for why an issue is occurring.
While a blind automation job operates regardless of what else is happening in the server, a triggered automation job goes beyond notification and executes a command in response to an event. For example, if a blocking chain reaches more than a certain number of server process IDs (SPIDs), a triggered automation task would remove the top holder in a blocking chain to free up server resources.
Keep Track of Your Automation: A key part of effective server automation is understanding exactly which jobs are occurring at all times. Popular relational databases, such as Microsoft SQL Server, allow you to keep a log of each job—its name, whether or not it’s currently enabled, and a small description of the job.
Tracking each automated task allows you to get ahead of potential problems in the future. For example, in the triggered example we discussed above, if the DBA is able to return to that job and see the reason it was triggered, they may be able to create another automation that doesn’t even allow the blocking chain to get that long in the future. In essence, you can fix a problem before the automation even activates.
Automation Makes Your Life Easier
We live in an era where C-Suites everywhere are asking more of their DevOps teams than ever before. As a result, database managers are dealing with an unprecedented amount of server activity and scale. The truth is that without automation, especially for simple tasks such as maintenance and backups, DBAs will be less equipped to support this increase in DevOps activity.
Moreover, without automation, the chance of causing errors increases and the ability to spot the reason for errors decreases.
To support innovation in the years to come, DBAs and DevOps need to invest in automation methods that streamline everyday tasks, alert them to server health, and ultimately allow them to effectively manage their server.