Share via

OpsMgr 2007/2012: How to Configure SQL Server Log Shipping

  • Article

Introduction

This document provides deployment guidance for installing and configuring the SQL Server 2005, SQL Server 2008, and SQL Server 2008 R2 Log Shipping feature as an option to provide redundancy of the System Center Operations Manager 2007 or System Center 2012 Operations Manager operational database between two SQL Server servers in the same management group. This configuration is intended to compliment your recovery strategy.

Excerpts regarding the consideration of implementing sub-minute log shipping was originally published here - http://sqlcat.com/sqlcat/b/technicalnotes/archive/2009/02/24/scheduling-sub-minute-log-shipping-in-sql-server-2008.aspx and have been reproduced here with slight modifications.

SQL Log Shipping Overview and Recommendations

Log shipping consists of three operations:

  1. Back up the transaction log at the primary server instance.
  2. Copy the transaction log file to the secondary server instance.
  3. Restore the log backup on the secondary server instance.

The log can be shipped to multiple secondary server instances. In such cases, operations 2 and 3 are duplicated for each secondary server instance.

A log shipping configuration does not automatically fail over from the primary server to the secondary server. If the primary database becomes unavailable, any of the secondary databases can be brought online manually and Operations Manager will need to be reconfigured in order to reference the secondary server.

In SQL Server 2005, 2008, and 2008 R2, SQL Server Management Studio user interface allows the frequency of the scheduled jobs to be one minute or more, which means that the minimum latency of log shipping is as long as three minutes (one minute each for the backup, copy, and restore jobs). The scheduling of the jobs can be set for sub-minute frequency (the minimum is ten seconds), however, there are some important considerations that you should be aware of which could impact your overall strategy:

  • The next execution of the job will not start until the previous execution has completed. Let’s assume you have set the frequency interval of the log backup job to 10 seconds, but one execution of the log backup takes 12 seconds to complete. The next backup job will start at the next scheduled time, which is 20 seconds after the start of the previous backup job. One execution of the job is skipped in this case.
  • Every time a log backup is completed, a message similar to the following is shown in the SQL Server ERRORLOG:

2009-02-09 15:25:56.94 Backup Log was backed up. Database: Test_LS, creation date(time): 2009/02/09(14:27:24), first LSN: 19:145:1, last LSN: 19:145:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'\PRIMARY_DL380\LSBackup\Test_LS_20090209232551.trn'}). This is an informational message only. No user action is required.

If you take a log backup every 10 or 15 seconds, the SQL Server ERRORLOG flooded with such messages.

If you don’t want these messages flooding the SQL Server ERRORLOG, you can enable trace flag 3226. This trace flag doesn’t alter the behavior of backup jobs; it just suppresses the backup completion messages, preventing them from getting into the SQL Server ERRORLOG. Note that this trace flag suppresses all backup messages – database backup as well as transaction log backup.

  • Information about each backup is also recorded in the msdb database (the msdb.dbo.backupset, msdb.dbo.backupmediaset, msdb.dbo.backupmediafamily, msdb.dbo.backupfile, and msdb.dbo.backupfilegroup system tables). If you back up too frequently, you can expect these tables to grow faster than usual. You should periodically check the size of these tables and delete or archive the old information as necessary. To delete the old backup history, use the stored procedure sp_delete_backuphistory.
  • The backup compression feature in SQL Server 2008 provides significant space and time savings. Backup compression results in smaller backups, and it helps improve the performance of all the operations performed by log shipping by providing the following:
  • Faster backup of the transaction log on the primary server.
  • Faster copying of the transaction log backup file to the secondary over network.
  • Faster restore of the log backup on the secondary.

However, the benefits of backup compression come with the price of higher processor utilization. If your log backup jobs use compression and are scheduled too frequently, you may notice frequent spikes in processor utilization on the primary server. Restoring from a compressed backup uses more processing power, and you could see frequent spikes in processor utilization on the secondary servers as well.

Operations Manager Prerequisites

 

Selecting an Account for the SQL Server Agent Service

The service startup account defines the Microsoft Windows account in which SQL Server Agent runs and its network permissions. SQL Server Agent runs as a specified user account. In order to support the log shipping feature of SQL Server, this service must run under a domain user account and is a member of the local SQLServer2005SQLAgentUser$Computername$Instance group for SQL Server 2005 and for SQL 2008 and 2008 R2, the account should be a member of the SQLServerSQLAgentUser$ComputerName$Instance group.

For further information, please review, Setting up Windows Service Accounts in the SQL Server Books Online.

Firewall Access for SQL Server

To access an instance of SQL Server through a firewall, you must configure firewall rules to allow access for the following ports: 

Scenario  Port
SQL Server Default Instance TCP Port 1433
File and Printer Sharing TCP Ports 137, 138, and 139

For further information, please review Configuring the Windows Firewall to Allow SQL Server Access in the SQL Server Books Online.

Create the Backup Folder

Before you configure log shipping, you must create a share to make the transaction log backups available to the secondary server. This is a share of the directory where the transaction log backups will be generated.

  1. Log onto the primary SQL Server hosting the OperationsManager database with administrative credentials.
  2. Create a directory to backup the transaction log files to. Example: D:\TransLogs.
  3. Share the directory created in Step 2 and grant the domain user account associated with the SQL Agent (Instance) service account Full Control rights.
  4. Grant the SQL Agent (Instance) service account Full Control NTFS permissions on the directory created in Step 2.
  5. Repeat steps 1 through 4 on the secondary SQL Server.

Set the Full Recovery Model for the OperationsManager Database

By default, when the databases are created in support of Operations Manager, they are set with the simple recovery mode, as it inherits its recovery model from the model database. In order to support SQL Server 2005, 2008, 2008 R2 Log Shipping, the database must be reconfigured to use the full recovery model. By changing this setting, it is important to note that the transaction logs will continuously grow and will not be truncated until a transactional backup has successfully completed as part of the log shipping configuration. Scheduling the backup of the transaction logs to occur every 15 minutes is recommended and should complement your overall backup strategy for the databases. Test this configuration in a lab environment before implementing in production and monitor closely in order to ensure you have defined an appropriate schedule frequency which will complete all three log shipping activities within that window and avoids performance or storage space issues on the primary SQL server.

Enable Log Shipping

  1. Right click the OperationsManager database, and then click Properties.
  2. Under Select a page, click Transaction Log Shipping.
  3. Select the Enable this as a primary database in a log shipping configuration check box.
  4. Under Transaction log backups, click Backup Settings.
  5. In the Network path to the backup folder box, type the network path to the share you created for the transaction log backup folder.
  6. If the backup folder is located on the primary server, type the local path to the backup folder in the If the backup folder is located on the primary server, type a local path to the folder box. (If the backup folder is not on the primary server, you can leave this box empty.)
  7. Configure the Delete files older than and Alert if no backup occurs within parameters.
  8. Note the backup schedule listed in the Schedule box under Backup job. If you want to customize the schedule for your installation, then click Schedule and adjust the SQL Server Agent schedule as needed.
  9. Click OK.
  10. Under Secondary server instances and databases, click Add.
  11. Click Connect and connect to the instance of SQL Server that you want to use as your secondary server.
  12. In the Secondary Database box, choose the OperationsManager database from the list.
  13. On the Initialize Secondary database tab, choose the option that you want to use to initialize the secondary database.   Note: If you choose to have Management Studio initialize the secondary database from a database backup, the data files created on the secondary server will have the same names as those on the primary server, and will be created in an identical directory structure, including the drive letter.
  14. On the Copy Files tab, in the Destination folder for copied files box, type the path of the folder into which the transaction logs backups should be copied. This folder is often located on the secondary server.
  15. Note the copy schedule listed in the Schedule box under Copy job. If you want to customize the schedule for your installation, click Schedule and then adjust the SQL Server Agent schedule as needed. The frequency of 15 minutes is recommended for the schedule.
  16. On the Restore tab, under Database state when restoring backups, choose the Standby mode option.
  17. If you chose the Standby mode option, choose if you want to disconnect users from the secondary database while the restore operation is underway.
  18. Note the restore schedule listed in the Schedule box under Restore job. If you want to customize the schedule for your installation, click Schedule and then adjust the SQL Server Agent schedule as needed. The frequency of 15 minutes is recommended for the schedule.
  19. Click OK.
  20. Under History retention, choose the length of time you want to retain a record of your log shipping history.
  21. Click OK.
  22. On the Database Properties dialog box, click OK to begin the configuration process.

Once you have completed setting up log shipping, the transaction (.TRN) files should start to appear in the pre-defined shares created to host the transaction log files on both servers. To confirm that log shipping is functioning correctly, check the log shipping jobs have been successful in the Job Activity Monitor under the SQL Server Agent in SQL Management Studio on both servers.

Failing Over to a Log Shipping Secondary

Preparing for a Controlled Failover

Typically, the primary and secondary databases are unsynchronized, because the primary database continues to be updated after its latest backup job. Also, in some cases, recent transaction log backups not have been copied to the secondary server instances, or some copied log backups might still not have been applied to the secondary database. We recommend that you begin by synchronizing all of the secondary databases with the primary database, if possible.

  1. Copy any uncopied backup files from the backup share to the copy destination folder of each secondary server.
  2. Apply any unapplied transaction log backups in sequence to each secondary database.
  3. After the secondary server is synchronized, you can fail over by recovering its secondary database and redirecting the Root Management Server and management servers in the Operations Manager 2007 R2 management group to that server instance. For an Operations Manager 2012 management group, you can fail over by redirecting the management servers. Recovering puts the database into a consistent state and brings it online.

For more information about using this function in SQL Server, see the SQL Server documentation at http://technet.microsoft.com/en-us/library/ms191233. 

Recover a Database without Restoring

  1. Open SQL Server Management Studio.
  2. In the Connect to Server dialog box, select the appropriate values in the Server type list, in the Server name list, in the Authentication list, and then click Connect.
  3. Click New Query.
  4. In the query window, enter the following query:   RESTORE DATABASE OperationsManager WITH RECOVERY
  5. Click Execute.
  6. Close SQL Server Management Studio.

Preparing the Operations Manager 2007 R2 Management Group

  1. Stop the Operations Manager services (System Center Data Access, System Center Management, and System Center Management Configuration for Root Management Servers and System Center Management for management servers) in the management group.
  2. In a clustered Root Management Server environment, use Cluster Administrator to configure each of the three services listed above with the Take Offline option.
  3. Update the registry on each management server in the management group to reference the new SQL Server-based computer. Complete this step also on the Root Management Server, and if the Root Management Server is clustered, then you must complete this step on all the nodes in the cluster.  Note:  Before editing the Registry, follow your site's backup policies with regard to the registry.
  4. Log onto the management server with administrator permissions.
  5. Click Start, select Run, type regedit in the Open box, and then click OK to start Registry Editor.
  6. Under HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft Operations Manager\3.0\Setup, double-click the value DatabaseServerName, and then change the value to the hostname of the SQL Server-based computer now hosting the OperationsManager database.
  7. Click OK.
  8. Close the Registry Editor.
  9. Update the OperationsManager Database with the New Database Server Name, ensure that the account that you are logged on with has sufficient privileges on the SQL Server instance.
  10. Open SQL Server Management Studio.
  11. Expand Databases, OperationsManager and Tables.
  12. Right-click dbo.MT_ManagementGroup, and then click Open Table.
  13. Change the value in the SQLServerName_6B1D1BE8_EBB4_B425_08DC_2385C5930B04 column to reflect the name of the new SQL Server-based computer.
  14. Save your change.
  15. On the new server hosting the OperationsManager database, add the correct permission for the Login of the Root Management Server on which the SDK Account is running, as follows:
    1. Open Microsoft SQL Server Management Studio, and in the Object Explorer pane, navigate to Security and then expand Logins.
    2. Locate the SDK Account - add the account if it is not listed.  Note:  If the SDK Account is running as LocalSystem, use the format <domain\computername$> in SQL Logins, where <computername> is the name of the Root Management Server.
    3. Right-click the SDK Account, and select Properties.
    4. In the Login Properties dialog box, in the Select a page pane, select User Mapping.
    5. In the Users mapped to this login list, in the Map column, check the box that corresponds to OperationsManager (default name).
    6. In the Database role membership for: OperationsManager list, ensure that the following items are checked: configsvc_users, db_datareader, db_datawriter, db_ddladmin, and sdk_users.
    7. Click OK to save your changes and to close the Login Properties dialog box.
  16. On the new server hosting the OperationsManager database, add the correct permission for the Login of the Root Management Server on which the Action Account is running, as follows:
    1. Open Microsoft SQL Server Management Studio, and in the Object Explorer pane, navigate to Security and then expand Logins.
    2. Locate the Action Account - add the account if it is not listed. If the Action Account is running as LocalSystem, use the format <domain\computername$> in SQL Logins, where <computername> is the name of the Root Management Server
    3. Right-click the Action Account, and select Properties.
    4. In the Login Properties dialog box, in the Select a page pane, select User Mapping.
    5. In the Users mapped to this login list, in the Map column, check the box that corresponds to OperationsManager (default name).
    6. In the Database role membership for: OperationsManager list, ensure that the following items are checked: db_datareader, db_datawriter, db_ddladmin, and dbmodule_users.
    7. Click OK to save your changes and to close the Login Properties dialog box.
  17. On the new server hosting the OperationsManager database, add the correct permission for the Login of the Data Warehouse Server on which the Data Warehouse Action Account is running, as follows:
    1. Open Microsoft SQL Server Management Studio, and in the Object Explorer pane, navigate to Security and then expand Logins.
    2. Locate the Data Warehouse Action Account - add the account if it is not listed.
    3. Right-click the Data Warehouse Action Account, and select Properties.
    4. In the Login Properties dialog box, in the Select a page pane, select User Mapping.
    5. In the Users mapped to this login list, in the Map column, check the box that corresponds to OperationsManager (default name).
    6. In the Database role membership for: OperationsManager list, ensure that the following items are checked: db_datareader and dwsynch_users.
    7. Click OK to save your changes and to close the Login Properties dialog box.

Preparing the Operations Manager 2012 Management Group

  1. Stop the Operations Manager services (System Center Data Access, System Center Management, and System Center Management Configuration) on all the management servers in the management group.
  2. Update the registry on each management server in the management group to reference the new SQL Server-based computer.  Note:  Before editing the Registry, follow your site's backup policies with regard to the registry.
    1. Log onto the management server with administrator permissions.
    2. Click Start, select Run, type regedit in the Open box, and then click OK to start Registry Editor.
    3. Under HKEY_LOCAL_MACHINE\Software\Microsoft\System Center\2010\Common\Database, double-click the value DatabaseServerName, and then change the value to the hostname of the SQL Server-based computer now hosting the operational database.   Note:  If you are using a named instance of SQL Server, be sure to use the ServerName\Instance name format.
    4. Click OK.
    5. Close the Registry Editor.
  3. On each management server, edit the following file: %ProgramFiles%\System Center 2012\Operations Manager\Server\ConfigService.config. In the <Category> tag named “Cmdb”, change the value for ServerName to the name of the new SQL server.
  4. Update the operational database with the new database server name.
    1. Open SQL Management Studio.
    2. Expand Databases, OperationsManager, and Tables.
    3. Right-click dbo.MT_Microsoft$SystemCenter$ManagementGroup, and then click Edit Top 200 Rows.
    4. Change the value in the SQLServerName_6B1D1BE8_EBB4_B425_08DC_2385C930B04 column to reflect the name of the new SQL Server-based computer.
    5. Save the change.
  5. On the new SQL server hosting the operational database, expand Security, then expand Logins, and then add the data writer account.   For more information, see How to: Create a SQL Server Login.
  6. Also in Logins, add the action account.
  7. Also in Logins, add the Data Access Service (DAS) computer account, using the form “domain\computername$”.
  8. For the DAS computer account, add the following user mappings  (Note:  If an account has note existed before in the SQL instance in which you are adding it, the mapping will be picked up by SID automatically from the restored operations database. If the account has existed in that SQL instance before, you receive an error indicating failure for that login, although the account appears in Logins. If you are creating a new login, ensure the User Mapping for that login and database are set to the same values as the previous login: DW Data Writer: apm_datareader, apm_datawriter, db_datareader, dwsynch_users, DAS/Configuration account: ConfigService, db_accessadmin, db_datareader, db_datawriter, db_ddladmin, db_securityadmin, sdk_users, sql_dependency_subscriber.  If DAS/Configuration uses the LocalSystem account, specify computer account in form <domain>\computername>$. ):
    1. ConfigService
    2. db_accessadmin
    3. db_datareader
    4. db_datawriter
    5. db_ddladmin
    6. db_securityadmin
    7. sdk_users
    8. sql_dependency_subscriber
  9. Execute the following SQL commands on the operational database instance:
    1. sp_configure ‘show advanced options’,1
    2. reconfigure
    3. sp_configure ‘clr enabled’,1
    4. reconfigure
  10. Click Execute.

To Set ENABLE_BROKER

Before you can run tasks and use the Discovery Wizard to install agents, you need to set the ENABLE_BROKER value.

After moving the OperationsManager database, the status of the Sql Broker Availability Monitor might be set to ‘critical’ or to 'Sql Broker is disabled'. You can check the state of the Sql Broker Availability Monitor by running the following SQL query:   SELECT is_broker_enabled FROM sys.databases WHERE name='OperationsManager'

Where ‘OperationsManager’ is the default database name, replace this name as appropriate.

If the query result is ‘0’, then the Sql Broker is disabled and you must re-enable it using the following procedure.

  1. Open SQL Server Management Studio.
  2. In the Connect to Server dialog box, select the appropriate values in the Server type list, in the Server name list, in the Authentication list, and then click Connect.
  3. Click New Query.
  4. In the query window, enter the following query:
  5. ALTER DATABASE OperationsManager SET SINGLE_USER WITH ROLLBACK IMMEDIATE
  6. Click Execute.
  7. Enter the following query:
  8. ALTER DATABASE OperationsManager SET ENABLE_BROKER
  9. Click Execute.
  10. Close SQL Server Management Studio.  Note:  Closing SQL Server Management Studio closes the connection to the database in single user mode. Depending on your configuration, you may have to manually kill any process that is connected to the database before completing the ALTER query below.
  11. Open SQL Server Management Studio.
  12. In the Connect to Server dialog box, select the appropriate values in the Server type list, in the Server name list, in the Authentication list, and then click Connect.
  13. Click New Query.
  14. In the query window, enter the following query:
  15. ALTER DATABASE OperationsManager SET MULTI_USER
  16. Click Execute.

You can verify the setting for ENABLE_BROKER is set to 1 by using this SQL query:   SELECT is_broker_enabled FROM sys.databases WHERE name='OperationsManager'.

Start the Operations Manager services (System Center Data Access, System Center Management, and System Center Management Configuration) on all the management servers in the Operations Manager 2012 management group.

Start the System Center Management Configuration, System Center Data Access Service, and System Center Management service on the Root Management Server, and then restart only the System Center Management service on the remaining management servers in the Operations Manager 2007 R2 management group.

Important

Do not start the System Center Management Configuration and System Center Data Access Service on the management servers, as these services should only be running on the Root Management Server.