Lesson 2: SMS Database Disaster Recovery and Maintenance
The site database and software metering database are supported in SQL Server version 6.5 or version 7.0. SQL Server terminology beyond that explained in Chapter 2 and SMS-related SQL Server functions are explored in this lesson.
NOTE
See Chapter 2 for information on databases, transaction logs, and devices.
After this lesson, you will be able to
- Back up and restore a database using SQL Server.
- List and describe the functions of SMS tasks built into SMS 2.0.
- Configure tasks and create scheduled SQL commands in the SMS Administrator console.
Performing a Backup and Restore Procedure in SQL Server
SQL Server Enterprise Manager provides a facility for database and transaction log backup and restore.
Backing Up the SMS Databases
Backing up a database makes copies of its system tables, user-defined objects, and data. A backup is referred to as a dump in SQL Server version 6.5.
Databases and transaction logs are backed up onto backup devices or directly to a file. A backup device may be a disk file or a tape drive. You can back up a database to one device or to multiple devices. You can back up multiple databases or transaction logs to a common device or to separate devices.
Backup responsibility is usually assigned to either the SQL Administrator (sa) or the database owner. However, permissions to back up a database or a transaction log can be transferred by the database owner to other users. In either case, the user responsible for performing database backup should set up a regular backup schedule.
NOTE
Do not use an operating system copy command to make copies of your databases. This method will not ensure transaction integrity.
Use either SQL Server Enterprise Manager or the Transact-SQL BACKUP command to back up the database.
The SQL Server Backup dialog box is accessed from the Tools menu in SQL Server version 6.5. In SQL Server version 7.0, select the database in the SQL console tree, or from the details pane (shown in Figure 13-2), select All Tasks and then choose Backup Database.
Figure 13-2. Initiating a database backup of the site database in SQL Server Enterprise Manager.
Before performing the backup, provide or change the following information:
- The device or file you want to use for your backup
- The database you want to back up
- Whether SQL Server should back up the entire database (including the transaction log), the transaction log only, or a single user table
- Whether to write over the existing data and initialize the backup device
- Whether SQL Server should read or skip existing ANSI tape labels
- Whether SQL Server should rewind and unload the tape when finished
- Whether to set an expiration date for the backup
- Whether to execute the backup immediately or schedule it for later execution
Viewing Histories of Database Backups
SQL Server Enterprise Manager maintains a record of database backups, including backups made through other applications. For backups from SQL Server version 6.5, the Restore tab of the Database Backup Restore dialog box automatically presents a list of database backups, table backups, and transaction log backups from which to choose.
In SQL Server version 7.0, select the database in the console tree, select All Tasks, and then choose Restore Database.
Restoring the SMS Databases
When restoring the database, identify the backup device that contains the backup to be restored. Look at the header information to determine the database name, device size, and date of backup.
A database may be restored using SQL Server Enterprise Manager or the Transact-SQL LOAD command. SQL Server version 7.0 also supports the Transact-SQL RESTORE command. While LOAD will work in SQL Server version 7.0, it is only present for backward compatibility. Future versions of SQL Server will not support the LOAD statement.
Figure 13-3 shows a basic restore operation in SQL Server version 7.0.
Figure 13-3. Initiating a database restore of the site database in SQL Server Enterprise Manager.
See SQL Server Books Online for detailed information on both backing up and restoring databases. There are many differences between the two versions of SQL Server and many new options in SQL Server version 7.0.
Configuring SMS Database Maintenance Tasks
There are several database maintenance activities that are performed automatically by the SMS SQL Monitor process. Additional Transact-SQL commands may be added and scheduled through the SMS Administrator console. These commands are added to the database maintenance activities completed by the SMS SQL Monitor. Scheduled tasks and Transact-SQL commands are configured from the Database Maintenance node in the SMS Administrator console (Figure 13-4).
Figure 13-4. The Database Maintenance node in the SMS console tree.
SMS Automated Tasks
Automated tasks are enabled and scheduled through the SMS Administrator console, as shown in Figure 13-5.
Figure 13-5. Configuring automated tasks in the SMS Administrator console.
By default, all tasks are enabled except for the automatic database export and site server backup tasks.
Export Databases and Transaction Log Tasks
There are four tasks dedicated to backing up the site database, the software metering database, and their associated transaction logs (Figure 13-5). These tasks are disabled by default.
To use these tasks, you must first create a backup device, which is similar to configuring a backup routine directly in the SQL Server Enterprise Manager. Enter this device name in the `Export to' field, also shown in Figure 13-5. Specify a schedule and enable the task.
NOTE
If the `Truncate log on checkpoint' checkbox is selected in the SQL Server Enterprise Manager for the SMS databases, the transaction logs cannot be backed up. This is the default for SMS databases.
Back Up SMS Site Server
This task stops the SMS services and then backs up the site database, the software metering database (if it exists), the site server registry, and the Sms directory on the site server. The task then restarts the SMS services. Therefore, make sure that this backup routine runs when SMS services are not being heavily utilized.
Rebuild Indexes
This task is enabled by default to run once a week on Sundays. Indexes are used by SQL Server to speed up data retrieval. Over time, the data in the tables that are used to create the indexes change as new systems management data is collected. Therefore, it is important that the indexes are updated to the contents of the database tables in order to provide peak performance gains. The Rebuild Indexes task reorganizes the indexes to remove disk fragmentation and correct the index pointers. This maximizes SQL Server performance.
Monitor Keys and Recreate Views
This task is enabled by default to run once a week on Sundays. It monitors the integrity of the primary keys (those columns that uniquely distinguish one row from another in a database table) and writes a status message if a problem is discovered.
Delete Aged Items
These tasks are enabled by default. Old status messages are removed daily. Inventory data, discovery data, and collected files older than 90 days are removed once a week. For example, if a computer does not forward a discovery data record (DDR) to the site database for 90 days, then that computer will be deleted from the site database the next time this task runs. Heartbeat discovery is used to provide an updated DDR regularly enough so that computer resources that do not log on regularly are not deleted.
Update Statistics
This task is enabled by default to run every Monday, Wednesday, Friday, and Saturday. As the database is changed during normal SMS operations, internal statistics used to optimize SQL Server performance become less optimal. This task updates these statistics so that queries run faster. In SQL Server version 7.0, statistics are updated automatically.
Adding Additional SQL Commands
Tasks are only enabled and scheduled—not created—from the Tasks node. To create an automated task, use the SQL Commands node. For example, you can add the DBCC Transact-SQL commands discussed earlier in this lesson to the details pane of the SQL Commands node. The results of a SQL command can be logged to a specified file when you configure the scheduled command. If logging is not enabled, the command still runs. To verify that the command has run, view the SMS SQL Monitor from the Component Status node in the SMS Administrator console.
Exercise 60: Creating SMS SQL Commands
In this exercise, you will configure SMS to automatically run a stored procedure on a schedule. This stored procedure will determine how much drive space is available in the SMS site database.
NOTE
Complete this exercise from the primary site server with only the SMS Administrator console running.
- Start this exercise by enabling logging of SMS_SQL_MONITOR using the SMS Service Manager.
NOTE
The steps for enabling logging using SMS Service Manager was explained in Chapter 11, Exercise 51, "Installing SMS 2.0 on Computer 2."
