Export (0) Print
Expand All

Administering Systems Management Server Databases

Archived content. No warranty is made as to technical accuracy. Content may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.
On This Page

Before You Begin
Lesson 1: Supporting SMS in SQL Server
Lesson 2: SMS Database Disaster Recovery and Maintenance
Lesson 3: Restoring an SMS Site
Chapter Summary

Before You Begin

Systems Management Server relies on the robust data storage and management features in SQL Server. SMS uses SQL Server databases containing tables, indexes, stored procedures, and triggers for data storage and management. SQL Server can be used to back up and restore SMS databases in order to prevent loss of data due to a database server failure. Database maintenance tasks specific to SMS may be configured in the SMS Administrator console. If an entire site fails, SMS provides a number of methods to recover the data. However, these recovery methods depend on regular and complete site backups.

To do the exercises in this chapter, you must have followed the instructions in "About This Book" and completed the exercises described in Chapter 2. That is, Computer 1 must be configured as a site server.

Lesson 1: Supporting SMS in SQL Server

The site database and software metering database are supported in SQL Server version 6.5 or version 7.0. SQL Server terminology beyond the database terminology 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

  • Define basic SQL Server version 6.5 and version 7.0 terms including stored procedure, trigger, and indexes.

  • Run basic SQL Server maintenance tasks.

Estimated Completion Time: 15 minutes

Objects in the SQL Server Database

Tables, indexes, views, stored procedures, and triggers are objects within the SQL Server database devices. Tables store data, and indexes provide a mechanism to rapidly retrieve data contained in the tables. Queries and other procedures may be run against the database using SQL commands written in the Transact-SQL language. A view is a virtual table whose contents are the result of a query of existing database tables. Stored procedures are pre-defined sequences of Transact-SQL commands compiled and run against the database. Triggers are stored procedures that run when requested by a database event.

Tables

A database can contain multiple tables. A table is a collection of rows (records) that have associated columns (fields). The SMS 2.0 site database contains over 200 tables.

Indexes

An index is a feature that enables users to gain fast access to data in the tables. If no index is available, the entire table is searched to locate the requested information. An entire table search is significantly slower than an indexed search. An index in SQL Server is similar in function to an index in a book: It provides a fast way to locate information. SMS 2.0 contains more than 250 indexes. There are two types of indexes: clustered and nonclustered.

  • Clustered

    Data is stored in sorted order on a specific column in the database table. Only one clustered index may be created for a table because the clustered index imposes a specific sort order upon the database table.

  • Nonclustered

    Data in the table is stored in non-sorted order. The nonclustered index is sorted, but the data it points to can be found in a random order within the table. Single tables may have multiple nonclustered indexes.

Views

Views enable users to store predefined queries as objects in the database for later use. SMS supports views created in SMS 1.2. However, for more complete access to the site database, use the Web-Based Enterprise Management (WBEM) SDK and SMS Toolkit to access data through the SMS Provider.

Stored Procedures

A stored procedure is a named collection of Transact-SQL statements stored on the server and compiled into a single execution plan. Transact-SQL is the language of SQL Server. Stored procedures are similar to batch (.BAT) or command (.CMD) files in that they can carry out multiple SQL procedures.

Triggers

A trigger contains Transact-SQL statements that are run when data in a specific table is inserted, updated, or deleted. For example, when a row is inserted into the orders table, a trigger checks to see if there is a corresponding row in the customers table.

Through the Trigger Manager thread of the SMS Executive and the SMS SQL Monitor process, SMS uses triggers extensively as a means of communicating to the different components when a related change has occurred in the site database. There are approximately 200 different triggers designed for the site database. These triggers are the means for activating processes and minimizing the number of polling cycles that might otherwise have to be put into effect. Most components "wake up" on a file or directory change notification when a SQL trigger fires, and a corresponding stored procedure places a wake-up file in the appropriate component's inbox.

Note: For more information on tables, indexes, views, stored procedures, and triggers, see SQL Server Books Online.

Maintaining the SMS Databases

Most database maintenance tasks for the site database and software metering database should be performed from the SMS Administrator console. At times, it may be necessary to use SQL Server tools to perform database maintenance tasks. However, before accessing the database using SQL Server tools, make sure you are well versed in SQL Server database management and SMS.

SQL Server Tools

A number of administrative tools are provided with SQL Server. The tool you will use most often for SQL Server administration is SQL Server Enterprise Manager. This tool provides easy, enterprise-wide management from a server or a workstation. It enables you to perform system administration tasks using a graphical interface. For example, you can configure servers, manage databases, schedule events, and configure replication.

In SQL Server version 7.0, SQL Server Enterprise Manager is a Microsoft Management Console (MMC) snap-in. All of the systems management data that SMS works with, except for the CIMOM repository and software inventory collected files, are stored in the site database or the software metering database. Therefore, it is essential that the databases are properly maintained and backed up.

Note: A proper backup of a site includes more than just a backup of the database. See Lesson 3, "Restoring an SMS Site," for more details on backup strategies.

Two other important tools in SQL Server are the SQL Query Window (iSQL/w) in SQL Server version 6.5 and the SQL Server Query Analyzer in SQL Server version 7.0. These tools provide a command window interface where you can execute Transact-SQL programming language commands, such as database queries and maintenance commands. Transact-SQL commands can also be executed from the Windows NT/2000 command prompt using iSQL.EXE.

Maintaining Free Space in the Database

Because the SMS databases can grow quite large, it is important to make sure you do not run out of free space in the database.

SQL Server Version 7.0 Provides Automatic Maintenance

If you are using SQL Server version 7.0, then you can set the database and transaction log to grow automatically, by a specified amount of space, each time the current file fills up. During an SMS setup that automatically creates the database and device files, the database is not configured to grow automatically. You must configure the database to grow automatically after the SMS installation is complete.

To find out how much space is available in SQL Server version 7.0, select the database in the SQL console tree. Database information, including space available, appears in the right pane, as shown in Figure 13-1.

Cc750104.f13xx01g(en-us,TechNet.10).gif

Figure 13-1: . Viewing the space allocated to the site database devices.

Notice in the left pane of Figure 13-1 that there is a database named SMS_S01LicDB; this is the software metering database. You can also view the space allocated in this database by selecting it and clicking the Space Allocated link.

SQL Server Version 6.5 Requires Attention to Free Space

In SQL Server version 6.5, if the site database, software metering database, tempdb database, or any of their transaction logs becomes full, SMS processing stops. Because database space is pre-allocated, it is important to monitor its usage to make sure there is always room for transactions. The database and logs should be checked weekly to ensure there is adequate free space.

To find out how much space is available in a SQL Server version 6.5 database, and to expand it if necessary, follow these steps:

  1. Start SQL Server Enterprise Manager.

  2. In the Server Manager window, under Databases, double-click the SMS database. The Edit Database dialog box appears.

  3. Click the Database tab, and then note the Data Space Available.

  4. If the database is 80 percent full, click Expand to increase the size of the database.

    If a database must be expanded beyond the limits of the device, either increase the device size or add a new device. This may entail adding a new hard disk. See the SQL Server version 6.5 documentation for more details on how to expand the device.

tempdb Database

The tables in tempdb are the temporary working tables whose size varies widely based on database activity. Free space should be checked during peak usage; that is, when several copies of the SMS Administrator console are querying the database. If the database is more than 60 percent full, it should be expanded. In Microsoft SQL Server version 6.5, use the SQL Performance Monitor to monitor database space use. Select SQLServer:Max Tempdb Space Used.

View the space used by tempdb in SQL Server version 7.0 by clicking the Space Allocated link for the tempdb database.

Transaction Logs

In SQL Server version 6.5 you can monitor the transaction logs using a counter (SQLServer-Log:Log Size) in the Windows NT Performance Monitor utility. Assign a Performance Monitor alert to start SQLALRTR.EXE, which records SQL Server errors in the Windows NT Event Viewer Application log.

In SQL Server version 7.0, the counter is SQL Server: Databases - Percent Log Used. SQL Server version 7.0 also allows you to monitor the logs directly from the SQL Server Enterprise Manager.

Integrity Checking

Once a month, plan to run an integrity check on the SMS database and log. If any of these checks produces errors, consider restoring the last backup that ran before the failed integrity check.

DBCC Commands

The Database Consistency Checker (DBCC) Transact-SQL language command is the SQL Server database consistency checker. DBCC helps ensure the physical and logical consistency of a database. It is recommended that you run periodic checks to ensure the logical and physical consistency of your data. DBCC commands are run from the command line using iSQL or through a Windows graphical interface. In SQL Server version 7.0, the graphical interface is SQL Server Query Analyzer; in SQL Server version 6.5, the graphical interface is iSQL/w.

Before running DBCC commands, make sure that all copies of the SMS Administrator console are closed, and shut down all SMS services. Leave the Microsoft SQL Server, MSSQL Service running in order to execute DBCC commands.

The commands listed below do not include the full syntax for the DBCC commands. Only simple syntax is provided here. For more details on the parameters that can be used with DBCC, refer to SQL Server Books Online, which is included with SQL Server.

  • DBCC CHECKALLOC [('database_name')]

    To run this command against a database named SMS_S01, for example, type DBCC CHECKALLOC ('SMS_S01').

    This command checks the specified database to make sure that all pages are correctly allocated and used. If no database name is given, DBCC CHECKALLOC checks the current database. It reports the amount of space allocated and used. The final result message generated from running this test in SQL Server version 7.0's SQL Server Query Analyzer application is:

    Total number of extents = 368, used pages = 2646, referenced pages = 2332 in this database.
      (number of mixed extents = 129, mixed pages = 1012)
      in this database.
     CHECKALLOC found 0 allocation errors and 0 consistency errors in database 'SMS_S01'.
    

    As a precaution, shut down and restart the server after DBCC has completed. This prevents the possibility of a manual checkpoint attempting to write the allocation pages.

  • DBCC NEWALLOC [('database_name')]

    This command performs the same function as DBCC CHECKALLOC for SQL Server version 6.5. In SQL Server version 6.5, use the iSQL/w query interface.

  • DBCC CHECKCATALOG [('database_name')]

    This command checks for consistency within and between system tables. DBCC CHECKCATALOG also reports on any segments that have been defined.

  • DBCC CHECKDB [('database_name')]

    This command checks each table in the database to see that index and data pages are correctly linked; that indexes are sorted in proper order; that all pointers are consistent; and that the data information on each page and page offsets is correct.

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.

Estimated Completion Time: 40 minutes

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.

Cc750104.f13xx02g(en-us,TechNet.10).gif

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.

Cc750104.f13xx03g(en-us,TechNet.10).gif

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).

Cc750104.f13xx04g(en-us,TechNet.10).gif

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.

Cc750104.f13xx05g(en-us,TechNet.10).gif

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.

  1. 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."

  2. In the SMS console tree, expand the Site Settings node, and then expand the Database Maintenance node.

    The SMS console tree displays the SQL Commands and Tasks nodes.

  3. Select the SQL Commands node.

  4. From the Action menu, select New, and then choose SQL Command.

    The SQL Command Properties dialog box displays 'General' settings for the new SQL command.

  5. Complete the SQL Command Properties dialog box using the following information:

    In this field/checkbox

    You supply

    Name

    SMS site database space

    'Enable SQL command'

    Selected

    SQL command

    sp_spaceused

    Log status to

    \\SERVER1\SMS_S01\LOGS\dbspace .LOG

  6. Under Schedule, set the Start after time to the current time, and the Latest start time to 5 minutes after the configured Start after time.

  7. Select the current day of the week, and then click OK.

    The SMS Administrator console appears with the new SQL command in the details pane.

In the following steps, you will use SMS status messages to verify that the SQL command was executed.

  1. In the SMS console tree, expand the System Status and then expand the Site Status node, S01 – Central Site. Then select the Component Status node.

    The list of SMS components appears in the details pane.

  2. View all status messages for SMS_SQL_MONITOR.

    The SMS Status Message Viewer for <S01> <Central Site> window appears. Notice the messages with IDs of 2408. These messages report the processing of SQL commands and tasks. There should be one message for the sp_spaceused command.

    Note: The status message may not appear yet, because it may take a few minutes for the SQL command to execute. You may refresh the list of status messages to determine when the command has completed.

  3. View the details of the message for the sp_spaceused command.

    Notice the SQL command listed as being run was SMS site database space.

  4. Close the SMS Status Message Viewer for <S01> <Central Site> window.

In the following steps, you will use SMS log files to verify that the SQL command ran successfully.

  1. Start SMS Trace.

    The SMS Tracer application appears.

  2. Open d:\Sms\Logs\smsdbmon.log.

    The SMS Tracer application displays the contents of the SMS SQL Monitor's log file.

  3. Search for executing with a time close to the current time.

    Notice the executing SQL command for SMS site database space. Also notice a status message was written with an ID of 2408.

  4. Close SMS Tracer.

  5. Use Notepad to open D:\Sms \Logs \dbspace .LOG.

    Notepad displays the contents of the log file created by the SQL command. Because this log file was not created by the SMS services as an SMS log file, the formatting is not the same as the standard SMS logs, and SMS Trace will not display the data properly.

    Notice the sp_spaceused command, as well as the results of the command, is referenced. The results are presented in the following format, where x represents the site code and numeric values for your site.

  • SMS_xxx is the database name.

  • x MB is the total device size.

  • x MB is the unallocated space.

  • x KB is the reserved space.

  • x KB is the data space (actual data in the database).

  • x KB is the index size.

  • x KB is the unused space.

Exercise 61: Configuring SMS Database Maintenance Tasks

In this exercise, you will configure the SMS database maintenance tasks to automatically back up the SMS site and software metering databases. You will begin by creating backup devices to store the backed up data.

  1. From the Start menu, select Programs. Choose Microsoft SQL Server 7.0 and then Enterprise Manager.

    The SQL Server Enterprise Manager MMC appears showing the Microsoft SQL Servers node.

  2. Expand the Microsoft SQL Servers node.

    The SQL Server Group node appears in the SQL console tree and SERVER1 appears in the details pane.

    Note: If SERVER1 does not appear in the details pane, you must first register the SQL Server from the Action menu.

  3. In the SQL console tree, select SERVER1 (Windows NT).

    The Getting Started Taskpad appears in the details pane.

  4. Expand the Management node and select the Backup object.

  5. From the Action menu, select New Backup Device.

    The Backup Device Properties – New Device dialog box appears. Notice that the default path is D:\Mssql7 \Backup \.

  6. In the 'Name' field, type SMSDBDump and then click OK.

    The SQL Server Enterprise Manager MMC appears.

    Create another backup device with the name SWMDBDump.

  7. Close the SQL Server Enterprise Manager MMC.

  8. View the contents of D:\Mssql7 \Backup .

    Notice that no files are displayed. Even though the backup devices were created in SQL Server Enterprise Manager, they are not created on the server's hard disk until they are going to be used.

In the following steps, you will configure SMS to automatically back up the SMS site database and the software metering database files.

  1. Switch to the SMS Administrator console.

  2. In the SMS console tree, find and then expand the Site Settings node.

  3. Select and then expand the Database Maintenance node.

    The SMS console tree displays the SQL Commands and Tasks objects.

  4. In the SMS console tree, select Tasks.

    The details pane displays the available database maintenance tasks.

  5. In the details pane, select Export Site Database. Then from the Action menu, choose Properties.

    The Export Site Database Task Properties dialog box displays 'General' settings for the database task.

  6. Select Enable this task.

  7. In the 'Export to' box, type SMSDBDump.

  8. Under Schedule, set the Start after time to the current time, and the Latest start time to five minutes after the configured Start after time.

  9. Select the current day of the week, and then click OK.

    The SMS Administrator console appears. Notice in the details pane that the Export Site Database task is now enabled.

  10. Repeat this procedure to enable backing up of the software metering database (Export Software Metering Database) using the backup device (SWMDBDump) created in the preceding steps.

  11. View the contents of D:\Mssql7 \Backup .

    When the automated tasks start, the databases will be backed up, and the files will appear in the directory.

    Notice the backup devices were created on the server's hard disk as the databases were backed up. The file size indicates the actual number of used pages in the database.

In the following steps, you will use SMS status messages to verify that the SMS database maintenance tasks were run.

  1. In the SMS console tree, find and then expand the Component Status node for S01 – Central Site.

    The list of SMS components appears in the details pane.

  2. View the status messages for SMS_SQL_MONITOR.

    The SMS Status Message Viewer for <S01> <Central Site> window appears. Notice the messages with IDs of 2408. These messages relate to the processing of the SQL database maintenance task for backing up the databases.

  3. View the details for one of the two recent messages with an ID of 2408.

    Notice the SQL command listed as being run was an export of the SMS site database or the software metering database.

  4. Close the SMS Status Message Viewer for <S01> <Central Site> window.

In the following steps, you will use SMS log files to verify that the SMS database maintenance tasks ran successfully.

  1. Start SMS Trace.

    The SMS Tracer application appears.

  2. Open D:\SMS\LOGS\SMSDBMON.LOG.

    The SMS Tracer application displays the contents of the SMS SQL Monitor's log file.

  3. Search for executing around the current time.

    The first occurrence of executing appears. This may be for the sp_spaceused command run earlier in the previous exercise.

  4. Continue searching for executing until you reach the current time.

    Notice the log entry for the executing of the backup of the SMS database indicating that the backup of SMS_S01 completed successfully.

    Following these entries more entries related to the backup of the software metering database will be listed.

  5. Close SMS Tracer.

Lesson 3: Restoring an SMS Site

After this lesson, you will be able to

  • Move the site database.

  • Replace or restore the site database.

  • Replace or restore a primary site server.

  • Restore site systems.

Estimated Completion Time: 20 minutes

Moving the SMS Databases

There may be circumstances requiring that you move the site database or software metering database to another SQL Server computer. For example, there might be a plan to move the SMS databases to a more powerful SQL Server computer to accommodate network growth. There are two ways to move the SMS databases:

  • Restore a backup of the SMS databases to a SQL Server computer configured identically as the original SQL Server computer. The computer name, the database names, SMS files, and registry settings must be identical.

  • Restore a backup of the SMS databases to a SQL Server computer configured differently than the original database server. Then, use SMS setup to reset the current installation to use the new database server.

Before moving the databases to another SQL Server computer, make sure that the following preliminary tasks are completed:

  • Close all SMS-related applications and tools.

  • Stop all SMS services on the site server and the existing SQL Server computer.

  • Back up the existing site database from SQL Server.

  • Install SQL Server on a new computer or choose another existing SQL Server computer.

  • If you plan on upgrading from SQL Server version 6.5 to SQL Server version 7.0, complete this task on the original SQL Server computer before moving the databases to the computer running SQL Server version 7.0.

  • If the new computer is running the same version of SQL Server as the current SQL Server, apply the same SQL Server service pack.

  • If the new computer is running the same version of the operating system, apply the same operating system service pack.

Important: Make sure the same database sort order is used, or the database restore will fail. Also make sure the new SQL Server uses the same hardware platform as the previous SQL Server. For example, do not move SQL Server from an x86 computer to an Alpha computer.

After all the preparatory steps are completed, the following tasks are used to move the SMS databases to a different SQL Server computer:

  1. Create data and log devices and a new database in the order in which they were originally created. The devices should be at least as large as the existing database. In SQL Server version 7.0 consider configuring the database for automatic growth.

  2. Restore the site database backup to the new database.

  3. At the site server, run SMS setup. Select the 'Modify or reset the current installation' radio button. The Systems Management Server Setup Wizard allows you to change the site database server or the software metering database server, as shown in Figure 13-6.

Cc750104.f13xx06g(en-us,TechNet.10).gif

Figure 13-6: . The Systems Management Server Setup Wizard Database Modification window.

Replacing a Failed SMS Database Server

Moving the site database is a simple process of backing up and restoring the database, as discussed earlier in this lesson. For a SQL Server computer configured differently than the original database server, the process of resetting the database configuration in SMS setup modifies the SQL Server to support the site database. This reconfiguration is necessary because the site database server stores configuration data in the registry, then reads the site control file. Therefore, it is important to save more than the database devices to fully recover a failed site database server.

Backing Up Key Configuration Components

The SMS database is uniquely tied to several SMS files. The backup of the database should coincide with saving the SMS registry key, the network abstraction layer (NAL) registry key, the master site control file, and the SMS directory on the site server.

If the database needs to be restored, and these SMS files are not also restored, the old database may not reflect the newer registry settings or the site configuration. For example, if a logon point has been added since the last database backup, the new logon point will not be recognized in the restored database.

When you run the 'site modify or reset' operation from SMS Setup, it resynchronizes the database, the registry, and the site control file.

The Key Configuration Components

Key files include registry keys, the site control file, and the SMS directory.

SMS Registry Key

Using the registry editor, export the following registry keys on the site server to a file:

  • HKEY_LOCAL_MACHINE \SOFTWARE \MICROSOFT \SMS

  • HKEY_LOCAL_MACHINE \SYSTEM \CURRENTCONTROLSET \SERVICES

    The SERVICES registry key should be part of the backup, but will not need to be restored if the services are not reconfigured since the last backup.

  • HKEY_LOCAL_MACHINE \SOFTWARE \Microsoft \NAL

Note: For more detailed information about the Windows NT Registry Editor, see the Windows NT documentation.

Site Control File

The SITECTRL.CT0 file is located in the smsdir\Inboxes \Sitectrl .BOX directory on the site server.

Site Server SMS Directory

The SMS directory contains items that are used to maintain the site and the SMS components. It also contains files such as 16-bit client computer inventory history (used by Inventory Processor to report changes to the client computer inventory), error history (used by SMS services to report status on a specific event), trace logs, compressed packages, and pending send requests.

Many of the files in the SMS installation directory are never modified between backups; the executable files in the smsdir\BIN\platform directory are good examples. You may want to pick and choose which files in the SMS installation directory to back up, depending upon your needs.

Naming the Backup Components

Decide on a naming convention for the backup files that incorporates the site code. This makes it easier to back up several sites and keep file naming unique for each site. The following table contains suggested naming conventions for the components to be backed up, with xxx being the site code.

Name of item

File Name

Site control file

SITECTRL.xxx

SMS registry key

SMSREG.xxx

Site server SMS directory

SITEINSTALLDIR.xxx

Backup directory

SITE_xxx.bak.date

As seen in the table, the name of the backup directory includes the date of the backup.

Tip Consider using an automated backup system or the Backup SMS Site Server task to back up all critical computer data rather than individually backing up each SMS component.

Restoring the Database

Make sure that SMS services are stopped on the site server and any component servers. In addition, make sure any SMS Administrator consoles connected to the site are closed.

SMS Database Restore

Use SQL Server Enterprise Manager to restore the database and any transaction logs. Also, if you need to restore the database because you had to reinstall SQL Server, consider restoring the master database as well. The master database has the original settings and options.

SMS File and Registry Key Restore

If it is necessary to restore the site database, it may also be necessary to restore the site control file and registry keys included in the backup. This is required if a site's configuration has changed since the last SMS backup. If you restore a database that is older than the configuration of the site server, the site server's registry may reflect changes made after the database was backed up. In this case, there will be inconsistency in the site.

For example, if distribution points have been added to the site after the last backup, they will not show up in the SMS Administrator console, but they will appear in the registry of the site server. Following are the registry keys that must be restored:

  • HKEY_LOCAL_MACHINE \SOFTWARE \Microsoft \SMS

  • HKEY_LOCAL_MACHINE \SOFTWARE \Microsoft \NAL

Registry keys can be restored by running REGEDT32.EXE and then selecting Restore from the Registry menu. Then load the backed-up SMS registry keys.

SMS Installation Directory

If necessary, restore the site server's SMS installation directory. Restoring this directory produces a smoother recovery because it contains inventory history files, any collected files, and trace logs, as well as the master site control file.

If the original SMS directory is not restored, remember to reapply all SMS service packs and copy the backup of the master site control file to \smsdir\Inboxes \Sitectrl .BOX\Sitectrl .CT0.

If there is no backup of the site control file, generate a new one from the database by using PREINST.EXE (located on the SMS installation CD-ROM in the SUPPORT\RESKIT\BIN\platform\DBMAINT directory). There is no need to copy PREINST.EXE to a local fixed disk, as it regenerates a site control file on the root of the partition containing smsdir when running the following command:

preinst /DUMP

The site control file generated by PREINST is named SMS_xxx.SCF. The xxx in the prefix is the site code for the site. Rename the SMS_xxx.SCF file for the site you are backing up to SITECTRL.CT0.

Configuring SMS to Use the New Site Database

Use SMS setup to reset SMS to use the new database, if there were any changes in the computer name, server configuration, or mandatory accounts since SMS was reinstalled.

Reset Site

Running the SMS setup 'Modify or reset the current installation' option resets the site. As a result the following activities take place:

  • All SMS services on the site server are stopped.

  • Two new site configuration files are written: one from the current site properties and one from the proposed site properties.

  • New configurations are evaluated against the previous site configuration file, the best configuration is selected, and then the site is updated using the selected site configuration file.

  • SMS services are restarted on the site server.

Replacing the Site Server Computer

If both the site database server and the site server fail, and need to be replaced, the process for replacement is similar to restoring a site database server. However, in this case you also need to reinstall SMS. Care must be taken to duplicate the original site server configuration.

If the site server or SQL Server fails, services on the other site systems and client computers will continue to run. Client computers continue to report inventory, run advertised programs, and meter software. However, data and status messages from these activities will not be updated if the site server is not running.

The key to recovering an SMS site server is to back up site information on a regular schedule, as discussed earlier.

Backup Components for an SMS Recovery

Assuming that the original site database server must be replaced and that the site server must be reinstalled, you must have at minimum:

  • A backup of the SMS database

  • A backup of the SMS registry keys

  • A backup of the site control file or the ability to regenerate it from the database using PREINST.EXE

These three items are the minimum requirement for a complete restoration of a damaged primary site server.

Consider using disk fault tolerance to protect the data on the site server. Using disk fault tolerance will reduce the chance of site server failure.

Final Preparation Before an SMS Recovery

Before the original site server is reinstalled, make sure that all traces of the previous SMS installation are removed from the site server. This is not necessary if a new computer will be used as the site server. Make sure the following files are deleted:

  • SMS directories including the distribution point, logon point, and CAP directories if the site server serves these roles

  • All SMS keys from the registry

It may be necessary to clean out previous registry control sets on the site server as well, because they may contain an SMS registry key.

Before reinstalling SMS, make sure the following tasks have been completed:

  • Microsoft SQL Server is installed and running.

  • Any SMS services on any site systems have been stopped.

  • All SMS Administrator console connections are closed.

Installing SMS on the Original or a New Site Server

If you reinstall SMS on a new computer, it must use the same computer name, domain name, and processor type as the original computer. It is not possible to reinstall an x86 SMS site on an Alpha-based computer. Likewise, it is not possible to move an x86 site server to an Alpha-based computer.

Because the site database stores drive and directory information, use the same drive and directory name for the SMS installation.

Restoring the Database

When SMS is reinstalled, it creates a new site database and, optionally, a new software metering database. Therefore, restoring to a new installation of SMS is a bit different than simply restoring the database into new devices created in SQL Server.

In the case of SQL Server version 6.5, make sure the devices and the transaction logs that will be used by the databases are large enough to accommodate the database and transaction log restores. The devices and log files in SQL Server version 6.5 and the database files in SQL Server version 7.0 are configured to the appropriate size in SQL Server Enterprise Manager or through Transact-SQL commands. In SQL Server version 7.0, consider configuring the database files for automatic file growth.

Note: For information on how to run a restore in SQL Server Enterprise Manager, refer to SQL Server Books Online.

Restoring Site Systems

It is not necessary to back up the SMS directory structure for a logon point, distribution point, CAP, or software metering server. The site server will rebuild a logon point, CAP, or software metering server that has failed. A distribution point will receive a new set of packages when distribution points are updated or refreshed. To find out which packages should be on a particular distribution point, view the Package Status system, as shown in Figure 13-7.

Cc750104.f13xx07g(en-us,TechNet.10).gif

Figure 13-7: . Viewing the Package Status system in the SMS Administrator console.

Logon points, CAPs, software metering servers, and distribution points are easily restored by SMS.

Chapter Summary

SMS databases use many SQL Server features, such as indexes and triggers, for performance and system maintenance. The SMS database is maintained using SQL Server tools and objects below the SMS Administrator console's Database Maintenance node. Database maintenance includes site server and site database backup routines.

SMS databases can be moved and entire sites can be restored. The success of a site restore depends on complete and regular backups being done on the site server and the site database server. Non–site server site system recovery is handled through the SMS Administrator console and takes minimal intervention on the part of an administrator.

Review

  1. The SMS Administrator console starts, but fails to connect you to the SMS database. What could be the problem?

    ______________________________________________________________________________

    ______________________________________________________________________________

    ______________________________________________________________________________

    ______________________________________________________________________________

    ______________________________________________________________________________

  2. You have detected that your site is not updating when you change a setting in the SMS Administrator console. You also notice that tasks that you have scheduled are not running. What components are responsible for informing other SMS components of site configuration changes and running tasks scheduled through the Database Maintenance node?

    ______________________________________________________________________________

    ______________________________________________________________________________

    ______________________________________________________________________________

  3. You check your site server services and see that there is no SMS SQL Monitor service running. Why not?

    ______________________________________________________________________________

    ______________________________________________________________________________

    ______________________________________________________________________________

    ______________________________________________________________________________

    ______________________________________________________________________________

  4. It appears the site control file is corrupted. You are able to access the SQL Server database. Name two methods for restoring the site control file.

    ______________________________________________________________________________

    ______________________________________________________________________________

    ______________________________________________________________________________

    ______________________________________________________________________________

  5. You have chosen to run weekly backups of the entire SMS databases. You notice that the tempdb device continues to increase in size and you understand that it is not necessary to continually back up the transaction log device. What is one possible cause for the uncontrolled transaction log file growth?

    ______________________________________________________________________________

    ______________________________________________________________________________

    ______________________________________________________________________________

    ______________________________________________________________________________

    ______________________________________________________________________________

  6. You have just completed a restoration of the SMS databases. The site server did not fail, so you did not need to reinstall SMS. What do you need to do to fully restore the SMS system?

    ______________________________________________________________________________

    ______________________________________________________________________________

    ______________________________________________________________________________

    ______________________________________________________________________________

    ______________________________________________________________________________

  7. You back up an SMS database, the site server SMS registry key, the NAL key, and the site control file on Monday. You add a new logon point to the site the following Wednesday, but do not back up the SMS registry keys. The site server fails on Friday. In restoring the site server and its database, how do you restore the logon point added on Wednesday?

    ______________________________________________________________________________

    ______________________________________________________________________________

    ______________________________________________________________________________

    ______________________________________________________________________________

    ______________________________________________________________________________

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft