Get up to 50% off! Limited time only: Learn More.

Automation of SQL Server Environments for SQL Developers

Swaathi Kakarla June 14, 2019

DevOps Monitoring & Alerting
Automation of SQL Server Environments for SQL Developers Blog Banner Image

At any given time, a DevOps or DataOps team can be responsible for managing dozens of databases. With so much to monitor, automation is key for ensuring performance issues are recognized and addressed efficiently and quickly. To be sure, it can be daunting to hand off critical business operations to non-human operators (which is what happens when you automate database administration), but delivering repeatability and accuracy sometimes trumps gut feelings.

In this article, I explain how to automate database management. I’ll focus on SQL databases in particular, although many of these lessons apply to any type of database.

1. Committing schemas to source control

As developers add, remove and modify tables and relationships, the database structure is in a constant state of flux. There will be multiple instances of the databases across teams and versions.

In order to manage copies, DBA teams can create schemas of the database. The schema is a template of the current database structure. It includes the database name, table names, indexes, and more.

Many popular web frameworks already incorporate schemas. For example, this is the one that Ruby on Rails uses:

schema

The schema file can be committed to source control. This ensures the app version and the database version are always cohesive. Any changes in the app made by any team also carry the changes to the database. This reduces the chances of anything unwanted creeping into the build.

To standardize your schema management routine, it can be helpful to use a schema management tool that supports multiple types of databases, such as the open source Liquibase framework.

When DevOps teams deploy a nightly build, a new version of the database could be created, purely from the scripts in source control. Nightly integration tests could then be run to prove that each build produces a functioning database.

DevOps in Incident Management

2. Managing environments

During the development phase of any application, different environments are maintained. At the bare minimum, there is always a development environment and the production environment of the application source code. The development environment is constantly changing, and the production environment remains consistent on the production server.

This abstraction could also be brought into the SQL world. Once schemas are created, it becomes easier to maintain different environments for the database structure and configurations. For example, the development environment could contain configurations that allow for easy regeneration and modification. Then, test environments could load test data into the database, and production environments could contain configuration to prevent easy modification of the database data and structure.

3. Scheduling backups

This goes with any service that contains crucial data — Backups are mandatory. Backing up an SQL server is so easy now that it’s just a simple command. However, DBA teams should not be responsible for taking backups manually.

There are many ways to schedule automated backups:

  • Automated schedulers

A cron job triggers automatically at specific intervals on a Linux server. DBA teams can write simple cron scripts to take backups regularly. However, the downfall of this method is if the cron fails to trigger, a backup will not be taken. To create a foolproof process, it’s better to use professional tools.

  • Storing backups

Backup scripts create an SQL file of your database and store it to disk. Often, this is in the same machine as the application server. This is a big no-no. Routing backups take a lot of space and if the entire machine gets compromised, you risk losing your backups as well. So, it’s always a good idea to store these backups in the cloud or on disparate servers. Amazon’s S3 is a great contender for this.

  • Regulating permissions

Apart from storing backups in a remote location, the permission for accessing the backups must also be regulated. Set up audit logs and access control lists to prevent unauthorized access.

4. Performance monitoring

DBA teams are only as efficient as the tools they use.

A common task performed by DBAs is to check if index fragmentations for each table have met a threshold — and if so, proceed to rebuild the index. In order to solve these issues, most of the time, DBA teams get information from logs and inspect variables or performance schema. However, as databases expand and the business grows, this can’t be scaled.

To fix this, DBA teams must always have quick and easy insights into the health of the database, they must preemptively schedule scripts and they must routinely be on-call to thwart any malicious attempts that compromise data sanctity. For this, visualization is key.

Grafana is one of the industry’s leading open source metrics monitoring and visualization tools. It allows you to query, visualize and create alerts with your data. Teams can build specific dashboards that monitor various aspects of database server health.

To take it a step further, the VictorOps and Grafana integration allows you to forward Grafana alerts and dashboards into VictorOps – helping you notify the correct on-call users with detailed alert context. Organizations can speed up the early detection of malicious activities and take corrective action immediately. With the integration, organizations can:

  • Track system performance over time, visualize service health and alert on-call teams when applicable monitoring thresholds are met.

  • Surface alert context in the form of metrics, logs, graphs and links to runbooks.

  • Automatically route and escalate Grafana alert data through VictorOps on-call schedules and escalation policies. Then, the team can collaborate in a single pane of glass to drive rapid incident workflows.

  • Chat in-line with incident context to improve collaboration and quickly share critical infrastructure information and dashboards, reducing MTTA/MTTR over time.

Closing thoughts

Database environment automation is more than just schemas and data. Automated failover is also necessary, along with automated scaling to match demand, and so much more.

There are a number of methods and tools that organizations can use to automate their SQL environments. Yet, a frequent question that gets asked is, “What is the biggest hurdle to automating?” The answer is simple – getting people working together in the right ways.

The right people must collaborate with each other at the right times in the right ways. Automation is a daunting task in any environment, and when it comes to business-critical processes, the risk is high. But, the benefits of both SQL database automation and incident automation are unparalleled.

See how VictorOps helps you bring visibility, automation, on-call scheduling and collaboration to the deployment and incident management lifecycle – making on-call suck less. Try a 14-day free trial or request a free personalized demo today to learn more.

About the author

Swaathi Kakarla is the co-founder and CTO at Skcript. She enjoys talking and writing about code efficiency, performance, and startups. In her free time, she finds solace in yoga, bicycling and contributing to open source.

Ready to get started?

Let us help you make on-call suck less.