Share via

  • Article

Using SQL Server 2012 Always On Availability Groups with System Center 2012 SP1 - Operations Manager

 

Updated: May 13, 2016

Applies To: System Center 2012 R2 Operations Manager

System Center 2012 Service Pack 1 (SP1), Operations Manager supports SQL Server 2012 AlwaysOn functionality.

The procedures explained here are not intended to provide detailed instructions on how to configure a SQL 2012 AlwaysOn Availability Group, but instead provide tasks that need to be exercised in order for Operations Manager to work effectively when using availability groups, and also emphasizes specific SQL Server AlwaysOn functionality that SP1 supports.

For more information on SQL Server 2012 AlwaysOn Availability Groups, see AlwaysOn Availability Groups (SQL Server). A Word document describing SQL Server 2012 AlwaysOn Multisite Failover Cluster Instances can be found at SQL Server 2012 AlwaysOn: Multisite Failover Cluster Instance.

Important

We do not support a topology where the reporting FCI (the instance hosting the reporting  services database only) is configured as part of the AlwaysOn Availability Group.

Note

Operations Manager does not support setting the MultiSubnetFailover parameter. This parameter is not used in Operations Manager connection strings.

SQL 2012 AlwaysOn supported Operations Manager databases

SQL 2012 AlwaysOn supports the following Operations Manager databases.

  • Operations Manager Operational database

  • Operations Manager Data Warehouse

  • Operations Manager Audit Collection Services (ACS) database

Important

For the Operations Manager Data Warehouse and the Operations Manager Audit Collection Services (ACS) database, see the procedures in How to Move the Data Warehouse Database, but replace the new SQL server in the procedure with the <name,port> of the Availability group listener.

Note

A common deployment pattern prescribes using separate SQL Server instances for the Operations Manager, Operations Manager Data Warehouse, and Operations Manager ACS databases. If you are using this pattern, then ensure that all SQL Server instances are added to the availability group.

New Management Group Installation

Use the following series of tasks when installing a new management group with a SQL 2012 AlwaysOn Availability Group.

Before Installing Operations Manager on an availability group

  1. Make sure to use the Group listener Name and port when installing Operations Manager for the databases that are going to be added to the availability databases.

  2. The first management server will use the Group listener to get the primary SQL instance, and will install the databases on that instance.

After installing the first management server

  1. Ensure that the recovery model of the database is full: open SQL Server Management Studio and connect to the instance where the database(s) are installed. Right click on the targeted database, and select its properties and select Options. If the recovery model is not listed as “Full”, select Full from the drop down list.

  2. Make a full back up the databases.

  3. Use SQL Server Management Studio to add the databases to the availability databases. Notice that when adding the databases to the availability databases under Select Data Synchronization, three choice are possible: Full, Join only and Skip initial data synchronization. Choose the option that is most appropriate for you. We recommend selecting Full and allowing the Add Database wizard create a full backup and restore of the databases on the secondary replicas. More steps might or might not be needed depending on which choice you made. See Manually Prepare a Secondary Database for an Availability Group (SQL Server) for more information.

  4. On the new server hosting the operational database, expand Security, then expand Logins, and add the data writer account name. For more information on how to create a SQL Server login, see Create a Login.

  5. Under Logins, add the action account.

  6. Under Logins, add the Data Access Service (DAS) computer account, using the form “domain\computername$”.

  7. For the DAS computer account, add the following user mappings:

    1. ConfigService

    2. db_accessadmin

    3. db_datareader

    4. db_datawriter

    5. db_ddladmin

    6. db_securityadmin

    7. sdk_users

    8. sql_dependency_subscriber

  8. On the new server hosting the data warehouse database, expand Security, then expand Logins, and then add the data writer account. For more information on how to create a SQL Server login, see Create a Login.

  9. Under Logins, add the data reader account.

  10. Under Logins, add the Data Access Service computer account, using the form “domain\computername$”.

  11. For the DAS computer account, add the following user mappings:

    1. db_datareader

    2. OpsMgrReader

    3. apm_datareader

Known Issues

When you open the Operations Manager console after failing from one node to the other you might encounter the following issue:

Execution of user code in the .NET Framework is disabled. Enable “clr enabled” configuration option. Could not use view or function ‘dbo.fn_ModuleTypeView’ because of binding errors.

To resolve this issue, please run the following SQL command on the database of the new primary replica SQL instance.

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

Existing Management Group

Use the following series of tasks when using an existing management group with a SQL 2012 AlwaysOn Availability Group.

Tasks to perform with the existing management group

  1. Make sure that all SQL machines hosting your Operations Manager databases are part of the availability group replicas, or add them to it if they are not.

  2. Open Management Studio on the SQL Machine hosting the Operations Manager databases, right click on each database that is going to be part of the availability databases, and for each select its properties and select Options to change the recovery model to Full from the drop down list.

  3. Note the name and the port of the availability group listner.

  4. On each management server run regedit from an elevated CMD, then edit HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\System Center\2010\Common\Database.

    Change the DatabaseServerName to <AvailabilityGroupListnerName,portNumber>

  5. 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 availability group listener and change the PortNumber to the availability group listener port.

  6. Update the Operations Manager database with the group listener name and port by following these steps:

    1. Open SQL Server Management Studio.

    2. Expand Databases, Operations Manager, and Tables.

    3. Right-click dbo. MT_Microsoft$SystemCenter$ManagementGroup, and then click Edit Top 200 Rows.

    4. Change the value in the SQLServerName_<GUID> column to reflect the <name,port> of the availability group listner.

    5. Save the change.

  7. Update the Operations Manager database with the availability group listener to specify the location of the application performance monitoring tables.

    1. Open SQL Server Management Studio.

    2. Expand Databases, Operations Manager, and Tables.

    3. Right-click dbo. MT_Microsoft$SystemCenter$OpsMgrDB$AppMonitoring, and then click Edit Top 200 Rows.

    4. Change the value in the MainDatabaseServerName_<GUID> column to reflect <name,port> of the availability group listener and its port.

    5. Save the change.

  8. Right click each database, and under Task select Back up (Full Back up).

  9. Navigate to the Availability Group node and expand it. Right click Availability database, and select Add database.

    On the Select Initial Data Synchronization page, select a data synchronization preference. We recommend selecting Full. Full data synchronization has the benefit of creating a full backup and restore of the databases on the secondary replicas.

    At the end of this task all databases will be added to the availability database, and restored on all availability replica nodes.

  10. Use SQL Server Management Studio to add the databases to the availability databases. Notice that when adding the databases to the availability databases under Select Data Synchronization, three choice are possible: Full, Join only and Skip initial data synchronization. Choose the option that is most appropriate for you. We recommend selecting Full and allowing the Add Database wizard create a full backup and restore of the databases on the secondary replicas. More steps might or might not be needed depending on which choice you made. See Manually Prepare a Secondary Database for an Availability Group (SQL Server) for more information.

  11. For each of the secondary replicas, open build_mom_db_admin.sql in notepad. The file is located under <installationMedisFolder>\Setup\AMD64). Then search for the MOMv3 messages section. Copy this section into SQL Server Management Studio, starting and running a new query.