Three Keys to Managing Large SQL Server Environments
March 21, 2016 | Best practices
Greg Ota Manager, Database Engineering
Instances of SQL servers are proliferating nearly as fast as the Big Data they are expected to manage, with Gartner projecting that the market for relational database management systems will grow to $40 billion by 2018, according to Wired.
This rapid growth puts a real strain on IT organizations as they strive to operate these systems efficiently so companies can best leverage all of that data they are producing. One server we support has more than 800 instances, and for many companies, more than 400 instances under management requires a full-time database administrator just to login to each instance and make sure that backups run successfully.
Improving productivity and effectiveness under these circumstances calls for three key approaches.
While technical requirements may not allow you to make everything identical, the closer, the better. If there is a clear understanding of what “right” looks like, then any database administrator should be able to easily scan through a system and notice configuration settings that are not standard. Best practices are a great way to be proactive and prevent problems before they arise.
If a monitoring tool is not in the budget, SQL Server does provide some of this functionality. At a minimum, administrators should check the SQL Server availability, critical maintenance jobs such as backups, and critical capacity issues including running out of disk space. A finely turned monitoring system can help you be proactive by warning you before a problem occurs, such as triggering a warning threshold on your disk space, as well as when a problem has occurred.
Use a Centralized Management Server
A single database server that is able to gather metadata from your other database servers Improves oversight and reporting. It allows companies to create a single consolidated report on job status, saving significant time compared to analyzing separate reports from each system. It also enables generating and tracking of trend analysis and other key performance indicators tailored to each company’s needs. In addition, companies can opt to use monitoring tools to support this consolidated functionality rather than pay the extra cost for a license or Azure cloud solution.
Using these approaches allows companies to consolidate information flow, thus reducing communication traffic. These best practices also decrease the number of incidents. The continuous review of standards, monitoring and centralized management is central to greater SQL server efficiency and productivity.