Note : SQL Server AlwaysOn Availability Groups functionality is supported by all versions of System Center 2012 – Service Manager for the default server instance. However, SQL AlwaysOn Availability Groups functionality is not supported for a named instance. Refer TechNet : http://technet.microsoft.com/en-us/library/hh495585.aspx
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 ?
in our Company we do per policy can use only named inctances and now only Availability Groups with SQL2012.How can i do this?
@Dan: Multi Subnet Configuration within SQL works just fine for SCSM. You will need to use 1 IP when installing, then flip it to multi-subnet and test a failover. I recently installed this for a customer and it worked fine. You will also need to flip SQL back to 1 IP when installing the Self Service Portal as it gets confused by the multi-subnet just like the SCSM management server install does. One thing to note: During failover testing, we did see some problems with the data access service on the Primary Management Server. But after a re-boot and a couple of services restarts, it seemed to get back on line. The key steps are 1: Fail Over SQL, 2: Restart SCSM Data Access Service. For the SCSM DW. make sure to STOP all the DW Jobs via Powershell , Failover SQL, - restart Data Access Service on the SCSM DW. then start the SCSM DW Jobs again via powershell.
@John: Yes, you can give your SQL AG a DNS Name.
Hi Greg, Thanks for the answer!If i try to install it in the AG...it do not work...and installing on one node...i have no idea how to move the Analysis Database in the AG! Do anyone have an instruction...SCSM install on a named instance SQL2012 Availability Group with Analysis services
Hi Mike,SQL AlwaysOn Availability Groups on a named instance is not supported for Service Manager. I have updated blogs and this is also documented on TechNet.http://technet.microsoft.com/en-us/library/hh495585.aspxRegards,Babulal
Has anyone tried to use SQL AlwayOn with System Center 2012 R2 Service Manager and a new install? I am getting an error that A required SQL Server service is not running on.... when I try to use the Listener name. If I use the Listener IP Address, Failover
Cluster Management Name, or Individual Server names, I am not get this error.
Follow this guide from Steve Buchanan.
I've had the same problem and MS gave us the following workaround:
To resolve the issue we need to add the following REG_MULTI_SZ value on both nodes of the cluster.
Is it possible to install the service manager DWH on a SQL AlwaysOn Availability Group named instance?
I don't think a named instance is supported for AlwaysOn SCSM setup.