Automation of MS SQL Server Database Support Tasks
At DBServ, we have adopted several practices for automating MS SQL Server databases, ensuring our clients’ databases operate smoothly and efficiently. Here, we will discuss these practices, which include index and statistics maintenance, backup procedures, and database integrity checks
SQL Server Index and Statistics Maintenance
Without regular maintenance, index fragmentation can slow down table operations, potentially reducing business efficiency and leading to financial losses. To address this, DBServ recommends using IndexOptimize — a procedure for rebuilding and reorganizing indexes, as well as updating statistics.
This customizable procedure offers a wide range of parameters. It can be set to execute for all databases, databases of a specific type (such as system or user databases), or a particular database. Various index rebuild modes are available:
- Online
- Offline
- Reorganization modes
- Advanced conditional modes
An example condition might be: “Reorganize the index; perform an online rebuild if reorganization is unsupported; perform an offline rebuild if other options are unavailable.” For any level of index fragmentation, suitable maintenance parameters are available, as the procedure checks this value in the sys.dm_db_index_physical_stats dynamic system view.
A page count filter can also be configured. For example, if a table has fewer than 50 pages, the procedure won’t execute, as it would be inefficient. The number of processors used for reindexing can also be specified, with maximum parallelism as the default to accelerate the maintenance process.
Updating statistics is part of maintenance, ensuring that the query optimizer can create the most efficient execution plans, thus speeding up data exchange. We balance the update schedule — both too frequent and too rare updates can be counterproductive. Updates can be set for all indexes and columns or selectively.
Backup
Implementing backup policies for MS SQL databases is essential for data security and protection. Databases are vulnerable to risks like hard disk failures, OS errors, virus attacks, and human error. Regular backups enable data recovery in case of data loss.
At DBServ, we offer a flexible approach for each client, proposing various backup strategies depending on the industry. Each client receives tailored guidelines, standards, and data storage requirements.
Our main solution is the flexible and efficient Database Backup procedure. It allows setting backup policies for all databases or for a subset — system or user databases. Special flags enable excluding specific databases from maintenance or even configuring a policy for a single database. The storage location for backups can be chosen—either a local directory or a network resource.
Database Backup creates a directory structure in the root backup directory using server name, instance name, database name, and backup type. If the database is part of an availability group, the cluster name and availability group name are used instead of the server and instance names.
There are three backup types:
-
- Full
- Differential
- Transaction log
Depending on the database’s importance and configuration, different setups can be applied. Typically, all three backup types are configured for production databases, enabling restoration to any point in time.
For backup storage, there are cleanup mode and cleanuptime mode settings. These allow us to configure automatic deletion of unnecessary copies to save disk space for new backups. For example, BEFORE_BACKUP mode deletes the old copy before creating a new one, while AFTER_BACKUP deletes old backup files after backup completion. In the latter case, if the backup fails, backup files are not deleted. Additional space savings are achievable through backup compression.
Integrity Check
A key practice in database management is integrity checking, ensuring that table data complies with specific rules and constraints.
For this, we use an effective tool—the DatabaseIntegrityCheck stored procedure. The job can be configured for any database subset, including those configured with AAG. Integrity checks can be performed at the database or individual filegroup level.
The PhysicalOnly parameter can limit the check to the database’s physical structure, while the DataPurity parameter assesses the relevance of field values. Depending on system configuration, the job can operate in multi-threaded mode. The TimeLimit and LockTimeout parameters allow flexible timing configurations.
Our Practices (Algorithm)
Our primary service goal is to ensure fast, reliable, and robust system performance. The practices listed above are only part of our database management and automation efforts. We carefully configure necessary parameters for the OS and DBMS, following this algorithm to guarantee high efficiency and reliability for MS SQL Server.
- Before adding a server to support, we configure it thoroughly for optimal performance.
- Appropriate power mode, Windows Defender and Firewall settings, file system parameters, and other configurations are selected.
- MS SQL Server is parameterized based on server performance.
- Tempdb’s settings are planned according to the number of processors, as it is a crucial shared resource.
- Data and log file size and auto-growth are configured for each database, avoiding compression, which is suboptimal for DBMS performance.
- Important trace flags are configured.
- Tasks are created to evaluate real-time and retrospective server load and issues.
To start a collaboration, please leave your contact details through the application form, and our specialists will get in touch with you as soon as possible.