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.

1. Service Manager is already installed.
2. New installation of 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:

1. Install two node windows server 2012 cluster.
2. Install SQL Server 2012 on both the nodes.
3. Create and configure AlwaysON Availability Group.
4. Pre-requisite for Service Manager 2012 SP1 install on SQL AlwaysON.
5. Install Service Manager.
6. Test failover.

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.

Step 1:  

Setup windows Server 2012 cluster, I recently wrote a blog on installing windows cluster on virtual machines.

http://blogs.technet.com/b/babulalghule/archive/2013/02/16/how-to-configure-two-node-windows-server-2012-cluster-on-virtual-machines-for-testing.aspx

Step 2:

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.

Step 3:

Enable SQL alwaysON feature on both the nodes.

• SQL Server Configuration Manager -> SQL Server Services -> Open properties of the SQL Service
• Click on “AlwaysON High Availability” tab and check “Enable AlwaysON Availability Groups” and restart SQL Service for changes to take effect.

Create SQL AlwaysON Availability Group

Pre-Requisite:

• Need minimum one database to create availability group.
• Any databases that you are planning to add to availability groups should have “Full” recovery mode.
• Take the full backup of database which you are adding to the availability groups.
• Need shared network location which is accessible to both the nodes.

We will create sample database in order to create availability group for Service Manager Installation.

• Create Sample database with full recovery mode and take the full backup of the database.
• Create shared network location

I’m using same windows cluster setup which is described on this blog post.

• Created share (\\iSCSITarget\SQLAlwaysON) on my iSCSI target server from Server Manager -> File and Storage Services -> Shares.

Create Availability Group (Refer this link for details)

• Open SQL Management studio on any of the SQL Server node.
• AlwaysON High Availability -> right click on Availability Groups  and select “New Availability Group Wizard”.

• Specify Availability Group Name.

• Select SampleDB on databases selection tab.

• Add secondary SQL node on Specify Replicas tab.

• Select the options on each of the four tabs (Replicas, Endpoints, Backup preferences and listener) which suit your requirement. This TechNet link has very good explanations on all four tabs and options available.

• On Listener tab select Create an availability group listener.

 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.

• On Initial Data Synchronization page, select option (Full, Join Only and Skip initial data synchronization) which suits your requirement. This TechNet link has very good explanations on all three options available.

• On Validation page, see if all success.

• Click on Finish on Summary page and then click on Close to finish the availability group creation.

          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

• Follow the planning and deployment guides for all the standard Service Manager installation pre-requisite.
• During the installation specify the Database server as Availability Group listener DNS name.

• And complete the installation steps as per the service manager deployment guide.

Configure Service Manager Database for Availability Groups

• Service Manager Installation using the SQL availability group listener does not configure Service Manager Database in availability groups.
• To configure Service Manager database in availability group, change the recovery model (refer this link for details on Recover Model) from Simple to Full
• Take full backup of Service Manager Database.
• Right click on Availability Databases ->  Add Database wizard and follow the instruction on wizard to add Service Manager Database to the Availability Databases.

• Verify that if you see the Service Manager database on secondary node.

Now, it’s time to test Service Manager Database failover

• Open failover cluster manager and verify the owner node.

• Right click on availability group and move to secondary node.
• If you are using immediate failback option then this will immediately failback to primary.

In my case I’m using prevent failback.

• Open Service Manager Console and verify if everything is working fine after failover.
• After failover I got few errors in the OpsMgr logs, first thing I provided permission for Service Manager SDK service account on SM database on secondary node.
• Restarted the SDK restart and got the below error.

Log Name:      Operations Manager

Source:        DataAccessLayer

Date:          16-02-2013 12:37:17

Event ID:      33333

Task Category: None

Level:         Warning

Keywords:      Classic

User:          N/A

Computer:      SCSMSP1.monlab.com

Description: Data Access Layer rejected retry on SqlError:  Request: ObjectTemplateByCriteria --
(LanguageCode1=ENN), (LanguageCode2=ENU), (TypeID0=4b1e00f8-1f3d-ad95-acdb-5587b3cf7147), (LastModified0=16-02-2013
19:43:53)

 Class: 16

 Number: 6263

 Message: Execution of user code in the .NET Framework is disabled. Enable "clr enabled" configuration option.

• Executed below query on secondary node

            sp_configure 'clr
            enabled', 1

           go

           reconfigure

           go

I forgot to run this SQL query on secondary node before attempting to failover.

Now everything works as expected.

Hope this helps!