Setup Operations Manager 2012 R2 with SQL AlwaysOn in Microsoft Azure

I was involved in a customer engagement for a high available design of Operations Manager 2012 R2. The customer has 2 Data Center sites connected with high speed networks, but there is no storage replication between the sites. Obviously the solution will be multiple Management Servers with SQL Server AlwaysOn.

 

Since Microsoft Azure starts supporting SQL AlwaysOn Group Listener with Internal Load Balancing, I can configure a testing environment in Azure to verify the design. The following articles include detailed steps on the SQL AlwaysOn configuration in Azure.

 

Tutorial: AlwaysOn Availability Groups in Azure (GUI)

Tutorial: Listener Configuration for AlwaysOn Availability Groups

 

NOTE: The SQL Server virtual machines must be in a separate cloud service from Operations Manager virtual machines. It is because "Azure does not support direct server return with client and server in the same cloud service."

My AlwaysOn Availability Group and Listener are configured successfully, with some steps skipped from the articles, since I already have an Active Directory domain in Azure.

SQL Sever Management Studio	Failover Cluster Manager

 

Here are several things I learnt when installing Operations Manager 2012 R2 with SQL AlwayOn.

• Configure RegisterAllProvidersIP and HostRecordTTL setting

  Operations Manager 2012 R2 doesn't support MultiSubnetFailover keyword. So I configured RegisterAllProvidersIP and HostRecordTTL settings using PowerShell script based the article below.

   

  Create or Configure an Availability Group Listener (SQL Server)

  Get-ClusterResource
  SCAWOCAS
  |
  Set-ClusterParameter
  RegisterAllProvidersIP
  0

  Get-ClusterResource
  SCAWOCAS
  |Set-ClusterParameter
  HostRecordTTL
  300

  Stop-ClusterResource
  SCAWOCAS

  Start-ClusterResource
  SCAWOCAS

   

  NOTE: The SQL Server Availability Group resource must be manually brought online after the above changes.

   

• Configure additional end point ports on Internal Load Balancer (ILB)

  I saw the following error when configuring Operations Manager database.

  

  The solution is to add additional 2 ports (135 and 445) on the ILB. I use the following PowerShell script to add the end point ports.

  # Define variables

  $ServiceName
  =
  "sg1omawo"
  # the name of the cloud service that contains the availability group nodes

  $AGNodes
  =
  "sql2012a","sql2012b"
  # all availability group nodes containing replicas in the same cloud service, separated by commas

  $EndpointName
  =
  "omawoendpoint"
  # name of the endpoint

  $EndpointPorts
  =
  "135","445"

   

  $ILBName
  =
  "omawoILB"
  # chosen name for the new ILB

   

  # Configure a load balanced endpoint for each node in $AGNodes using ILB

  ForEach ($node
  in
  $AGNodes)

  {

  ForEach ($EndpointPort
  in
  $EndpointPorts)

  {

  Get-AzureVM
  -ServiceName
  $ServiceName
  -Name
  $node
  |
  Add-AzureEndpoint
  -Name
  "$EndpointName-$EndpointPort"
  -LBSetName
  "$EndpointName-$EndpointPort-LB"
  -Protocol
  tcp
  -LocalPort $EndpointPort
  -PublicPort
  $EndpointPort
  -ProbePort
  59999
  -ProbeProtocol
  tcp
  -ProbeIntervalInSeconds
  10
  -InternalLoadBalancerName
  $ILBName
  -DirectServerReturn
  $true
  |
  Update-AzureVM
  -verbose

  }

  }

  NOTE:
  The variables $ServiceName, $AGNodes, $EndpointName, and $ILBName should be modified based on settings in your Azure environment.

   

• Configure OperationsManager DB and DW to Availability Group

  After completing the first Management Server installation, the OperationsManager DB and DW needs to be added into Availability Group. The steps are same for both databases:

1. Configure database Recovery model to "Full".
2. Make a full database backup.
3. Add database to Availability Group.

I use "Show Dashboard" from my "OMAWO" Availability Group to verify OperationsManager DB and DW synchronization states.

 

• Create SQL Login on the Secondary replica SQL Server

  Since Operations Manager 2012 R2 only creates SQL logins on the primary SQL server instance during setup, I have to manually create those SQL Logins on the secondary SQL server instance.

Compare SQL Logins on the primary and secondary replica servers.   New SQL logins should be created on replica servers for the following accounts:   -Management Server -MS Action Account -OM DW Reader -OM DW Writer -OM SDK Service Account
Generate the SQL query to create the SQL login from the Primary SQL server instance.
Copy the SQL query to the replica SQL instance and execute.

 

 

• Configure Transaction Log backup

  I modified the default Availability Group "Backup Preference" setting from "Prefer Secondary" to "Primary", before configuring Transaction Log backup maintenance job.

  

  NOTE:
  My reason on modifying the setting is to make sure the Transaction Log will always be backed up even the Secondary replica SQL instance is down. You may want to consult your SQL admin on the most suitable "Backup Preference" setting for the Availability Group.

   

   

  I created a maintenance plan to backup transaction log every hour for OperationsManager DB and DW databases. At first I didn't create the maintenance plan. Within one day my Operations Manager stops working. A good lesson learnt.