Five tasks that SQL Server DBA must do

If you are a very busy SQL Server dBA, what do you need to pay attention to first? Which tasks need to be completed regularly to ensure database robustness? In the previous article, we showed you the five wrong practices for Operating SQL server. In this article, we will introduce you to five correct key practices. I believe that through this article, SQL Server DBA can better and more efficiently manage databases.

1. Better backup

A solid disaster recovery plan and granular data recovery plan are the first magic weapon to make you a good user of SQL database. The important thing is that you need to back up transaction logs frequently between full backup and differential backup. You can take full advantage of the so-called "backup 2.0" continuous data protection solution. vendors such as appassure and Sunbelt software provide corresponding services, this prevents unnecessary data loss when a fault occurs again. This solution also makes the task recovery easier and faster.

2. Prepare a maintenance plan

When backing up a database, make sure that you have updated the relevant statistics and re-modified indexes of the database. If your server is set to automatically start the SQL Server Agent (a separate service function), you need to use SQL management studio to create a new maintenance plan, in this way, all tasks can be automatically executed and completed. Do not ignore this and put it aside after the first setup. You need to regularly check the scheduled task log to ensure that it works properly.

3. Back up the master database

You may not realize it yet, but the primary database is where SQL server stores all its configuration settings. For disaster recovery, you need a backup of the latest primary database to restart the server. Of course, this does not mean that backup is required every minute, because the frequency of primary data changes is not that high. However, as long as you make configuration changes within the server or add or delete a database, do not forget to back up the master database.

4. Configure operations and alarms

Run the SQL Server Agent in SQL Server. You can configure the agent to replace DBA. Provide your work days and SQL Server related information, and then configure the alarm function, you can send a message to the DBA when a specific situation occurs. This function helps DBAs understand when the database has insufficient disk space and server faults.

5. INDEX OPTIMIZATION

In addition to regular index reconstruction, index optimization will have an important impact on SQL server performance. You can use the SQL analyzer (SQL Profiler) to obtain real database traffic and feed this information back to the Database Engine Tuning Advisor. Then you can get suggestions on how to improve the performance. Today's index design does not mean to adapt to tomorrow's changes. Therefore, it is a good practice to access advisor from time to time.

After reading the above content, you will find that there is no task that is particularly difficult, and there is no need to pay for it, and it will not increase your workload. Just a few simple tasks can make your database run more smoothly and ensure that your database can be completely restored in the event of a fault.