In this blog post, I walk you through the steps to install Service Manager 2012 SP1 with a SQL 2012 AlwaysON Availability Groups.
First, let’s understand the two scenarios for using SQL AlwaysON availability Groups for Service Manager.
In first scenario, we need to create availability group for already installed Service Manager Database and follow the Service Manager Database movement process (link) and use availability group listener as new SQL server name.
For second scenario, create availability group and use availability group listener name as SQL server name for installing service manager.
Here are the high level steps:
Let’s start with brief overview of SQL AlwaysON Availability Groups
An availability group supports a failover environment for a discrete set of user databases, known as availability databases that fail over together. An availability group supports a set of primary databases and one to four sets of corresponding secondary databases. Availability groups leverage Windows Server Failover Clustering (WSFC) functionality and enable multiple features not available in database mirroring.
Please refer this (link) TechNet for the details on SQL AlwaysON Availability Groups.
Setup windows Server 2012 cluster, I recently wrote a blog on installing windows cluster on virtual machines.
Install SQL Server 2012 on both the nodes with SQL Service running on domain account.
For detailed pre-requisite for SQL AlwaysON Availability Groups refer this link.
Enable SQL alwaysON feature on both the nodes.
Create SQL AlwaysON Availability Group
We will create sample database in order to create availability group for Service Manager Installation.
I’m using same windows cluster setup which is described on this blog post.
Create Availability Group (Refer this link for details)
Listener DNS Name: Specify the network name of the listener. This name must be unique on the domain and can contain only alphanumeric characters, dashes (-), and hyphens (_), in any order. When specified by using the Listener tab, the DNS name can up to 15 characters long.
This DNS Name will be used as SQL Server Name for Service Manager Installation.
Port: Specify the TPC port used by this listener.
This port will be used as SQL Server port for Service Manager Communications with database.
In SQL Management studio, this availability group and listener will look like as below.
On Failover cluster Manager, it will appear under Roles.
Now, it’s time to install Service Manager
Configure Service Manager Database for Availability Groups
Now, it’s time to test Service Manager Database failover
In my case I’m using prevent failback.
Log Name: Operations Manager
Date: 16-02-2013 12:37:17
Event ID: 33333
Task Category: None
Description: Data Access Layer rejected retry on SqlError: Request: ObjectTemplateByCriteria --(LanguageCode1=ENN), (LanguageCode2=ENU), (TypeID0=4b1e00f8-1f3d-ad95-acdb-5587b3cf7147), (LastModified0=16-02-201319:43:53)
Message: Execution of user code in the .NET Framework is disabled. Enable "clr enabled" configuration option.
sp_configure 'clr enabled', 1
I forgot to run this SQL query on secondary node before attempting to failover.
Now everything works as expected.
Hope this helps!
Thanks for your description of how to setup Service Manager Database on a SQL 2012 AlwaysOn Availability Group. A few questions that I have though are:
- Can the Service Manager Database and the Service Manager DW Database be on the same Availability Group?
- Since the DW Database uses SSRS... where would you install the SSRS features? From my understanding of SSRS, it cannot be installed on a cluster node.
1. SM and SMDW DBs can co-exists on same AG, I don't see any issues but l'll be testing this weekend.
2. SSRS, either you can install it on DW MS or on different server.
Hw to install service manager in HA mode.
Can the Availability Group be a named instance?
What about installing SM when a multi subnet AG is involved. i.e. where can I add the connection string parameter for multisubnetfialover=true. or is a multi subnet AG not supported for service manager ?