SQL Server AlwaysOn Team Blog

Connecting to Availability Group Listener in Hybrid IT

Cephas Lin — Thu, 14 Feb 2013 18:51:00 GMT

In this blog, I'd like to explore client connectivity strategy when you have an availability group that spans both your on-premise servers and Windows Azure VMs. From the outset, I should clarify that the availability group listener is not supported in Windows Azure at the moment, which contributes to the interesting scenario in hybrid IT where the availability group listener is useful part of the time (i.e. when an on-premise replica is the primary replica). In a simple scenario, you have a primary replica and synchronous secondary replica running in your on-premise network for high availability. You then have an asynchronous secondary replica running in a Windows Azure VM for disaster recovery. The Windows Azure virtual network is connected to your on-premise network through a site-to-site VPN tunnel.

If you configure an availability group listener for this hybrid-IT scenario, as a matter of course you will specify two IP addresses for the listener's network name, one for your on-premise subnet and one for the Windows Azure subnet. Purely as a behavior of AlwaysOn Availability Groups, the multi-subnet cluster will always assign the listener IP for the availability group to be the one with same subnet mask as the primary replica. For example, if you create a listener called AG1 with the IP addresses 10.1.1.111/10.2.2.222 for the respective subnets, as shown below, the listener IP address 10.1.1.111 is active because the primary replica is in the 10.1.1.0/24 subnet.

The IP address 10.1.1.111 will be the active IP as long as the availability group fails over between the two on-premise replicas. Clients can connect to the listener name AG1 and be automatically redirected to the correct primary replica. However, when the availability group fails over to the asynchronous secondary in Windows Azure, the listener IP 10.2.2.222 becomes active instead. You can observe this in Failover Cluster Manager, where 10.1.1.111 is brought offline and 10.2.2.222 is brought online during the failover. At this point, if clients are configured to connect to the listener name AG1, the IP address resolves to 10.2.2.222 but the clients cannot connect to that IP address (again, because the listener is not supported in Windows Azure). Failover Cluster Manager may indicate that 10.2.2.222 is online, but actually no traffic can be routed to it in Windows Azure.

While you cannot connect to the availability group listener (yet), you can still connect directly to the SQL Server instance on the Windows Azure VM. So whenever you perform a failover from an on-premise replica to a Windows Azure replica, you must also change the client connection strings to connect directly to the Windows Azure VM. In a production environment, a failover to the Windows Azure VM should only happen in a disaster recovery scenario, and you should have a disaster recovery plan that is fully tested. As part of your disaster recovery procedure you want to do the following:

bring the cluster back online by forcing quorum on the Windows Azure VM,
perform a forced failover with data loss,
change the client applications (e.g. SharePoint) to connect to the Windows Azure VM directly,
recover your on-premise network/servers,
fail over back to one of the on-premise replicas,
change the client database connection to use the availability group listener again.

Simplifying Client Connectivity During Disaster Recovery

Reconfiguring client applications during disaster recovery is a complex process that can be simplified by creating dedicated DNS records. In the example above, you can create an A record that points to 10.1.1.111 or a CNAME record that points to AG1, then point the client applications to use this DNS record to connect to the availability database. Your disaster recovery procedure then may look as follows:

bring the cluster back online by forcing quorum on the Windows Azure VM,
perform a forced failover with data loss,
change the DNS record to point to the Windows Azure VM (if an A record, it's the VM's IP address; if a CNAME record, it's the VM's hostname),
recover your on-premise network/servers,
fail over back to one of the on-premise replicas,
change the DNS record to point to the availability group listener again.

This technique is particularly helpful if you have multiple clients that point to the same availability group. Instead of reconfiguring client applications, you only need to make sure that the one DNS record points to the proper IP address (A record) or network name (CNAME record).

When Replicas are in Named Instances with Different Names

If the replicas in your availability group are located in named instances that do not have the same instance names, you can still use the same technique, but you must make sure that the named instance on the Windows Azure VM listens on the same static port that the availability group listener is configured with and that client applications connect directly to the server name and port number. In other words, the availability group listener and the named instance on the Windows Azure VM need to be configured such that clients can use the same connection string to connect to either of them, as long as the DNS record points to the correct network name. In the example below, you can configure AG1 and NamedInst both to use port number 10000. The port numbers for the on-premise replicas can remain dynamically allocated because you can the availability databases through the listener.

Once you have configured your solution as shown above, configure all client applications to AG-VNN and port number 10000. For example: "Data Source=AG-VNN,10000;Network Library=dbmssocn;Initial Catalog=MyDataBaseName;Integrated Security=SSPI". This way, the on-premise.

For information on specifying a static port number for a named SQL Server instance, see Configure a Server to Listen on a Specific TCP Port (SQL Server Configuration Manager).

Test Lab: Create an AlwaysOn Availability Group in Windows Azure End-to-End

Cephas Lin — Wed, 23 Jan 2013 19:51:00 GMT

In this blog, you will learn how to configure AlwaysOn Availability Groups end-to-end using Windows Azure VMs. The steps here are the GUI near-equivalent of the script-based tutorial, which has been published at Tutorial: AlwaysOn Availability Groups in Windows Azure. The end-to-end test lab is designed to help you go through all the basic paces of deploying an end-to-end AlwaysOn scenario in Windows Azure VMs. There are some notable differences between on-premise HADR SQL Server deployments and Windows Azure VM deployments, which I have written in detail at SQL Server High Availability and Disaster Recovery in Windows Azure Virtual Machines. Hopefully, walking through the steps can help you identify these differences and avoid potential pitfalls.

Briefly, the steps in this blog demonstrate how to create an AlwaysOn Availability Groups deployment in Windows Azure VMs with the following characteristics:

Four VMs are deployed in the same affinity group, virtual network, and cloud service and use the same storage account.
One VM is the domain controller (DC) server.
The remaining three VMs form a three-node Windows Server Failover Clustering (WSFC) cluster and are deployed in the same subnet and availability set.
Two of the WSFC nodes contain SQL Server instances and host an availability group with an availability database.

The figure below is a graphical representation of the solution.

Below is the outline of the steps:

Create Domain Controller Server
Create and Prepare WSFC Nodes
Create WSFC Cluster
Prepare SQL Server Instances for Availability Group
Create Availability Group
Create Public Endpoints for Remote Clients

Create Domain Controller Server

You begin with a new Windows Azure trial account with the Virtual Machine preview enabled. Once you have finished your account and preview feature sign-up, your Windows Azure portal should look similar to the following screenshot:

Click the New button at the lower-left corner of the page.
Click Network, then click Virtual Network, and then click Custom Create.
In Virtual Network Details, specify Name, Region, and Affinity Group Name as shown below. Then, click the right arrow at the bottom-right corner.
In DNS Servers and VPN Connectivity, do not change anything and click the right arrow at the bottom-right corner.
In Address Space and Subnets, specify Address Space and Subnets as shown below in CIDR format. Then, click the check mark at the bottom-right corner.
When virtual network creation is complete, your Windows Azure portal will look similar to the following figure.
Next, you create a new storage account you will use to store the VHD files of the VMs. Click New again, then Data Services, then Storage, and then Quick Create. Specify URL and Region/Affinity Group as shown below and click Create Storage Account. You must specify a unique URL for your storage account.

It may take some time for the storage account to be created. When the storage account is created, your Windows Azure portal will look similar to below:
Next, you create the VM you will use as the domain controller (DC). Click New again, then Compute, then Virtual Machine, and then From Gallery.
In Virtual machine operating system selection, select Windows Server 2008 R2 SP1. Then, click the right arrow.
In Virtual machine configuration, specify Virtual Machine Name, Size, New User Name, New Password, and Confirm as shown below. Then, click the right arrow.
In Virtual machine mode, specify DNS Name, Storage Account, Region/Affinity Group/Virtual Network, and Virtual Network Subnets as shown below. Then, click the right arrow.
In Virtual machine options, click the check mark to finish configuring the new VM and wait for the VM to be provisioned. This process takes a while to complete, and if you click to the Virtual Machine tab in the Windows Azure portal, you can see ContosoDC cycling states from Starting (Provisioning) to Stopped, Starting, Running (Provisioning), and finally Running. Once ContosoDC is provisioned, click the Connect button to download the remote desktop (RDP) file to your local directory.
Next, you configure ContosoDC as a DC server for corp.contoso.com. Launch the RDP file for ContosoDC and log in using your configured administrator account (ContosoAdmin). After you log in, run dcpromo from the Start menu.
In the wizard, select Use advanced mode installation and click Next, then click Next again.
In Choose a Deployment Configuration, select Create a new domain in a new forest and click Next.
In Name the Forest Root Domain, type corp.contoso.com and click Next.
In the Static IP Assignment warning window, click Yes, the computer will use an IP address automatically assigned by a DHCP server (not recommended).
In Additional Domain Controller Options, accept the default DNS server selection and click Next. In the pop-up window, click Yes.
Click Next again, then specify and confirm the administrator password and click Next, and then click Next again to finish configuring the DC server. After the DC server is provisioned, the VM restarts automatically.
Next, you configure Active Directory (AD) with the accounts you will use later. Launch the RDP file for ContosoDC again using the default domain administrator credentials (CORP\Administrator).
Once you log in, open Active Directory Users and Computers from the Start menu.
Expand corp.contoso.com, then right-click Users, then point to New, then click User.
Specify a user named Install as shown below and click Next.
Specify and confirm the account password, then select the check boxes as shown below, and then click Next.
Click Finish. You have now created CORP\Install in AD. You will use this account to configure the failover cluster and the availability group.
Following the same steps, create two additional accounts: CORP\SQLSvc1 and CORP\SQLSvc2. You will use them as service accounts for the SQL Server instances.

Next, you need to give CORP\Install the necessary permissions for configuring Windows Service Failover Clustering (WSFC).
In the View menu, click Advanced Features. This action lets you see all the hidden containers, as well as hidden tabs in the properties windows for AD objects.
Right-click corp.contoso.com and click Properties.
In corp.contos.com Properties, select the Security tab, then click the Advanced button.
In Advanced Security Settings for corp, click Add.
Type CORP\Install in the text box and click OK.
Select Allow for Read all properties and Create Computer objects, then click OK three times to exit corp.contoso.com Properties.

Next, you create the three VMs you will use which includes a WSFC cluster node and two SQL Server VMs. To create each of the VMs, go back to the Windows Azure portal, then click New, then Compute, then Virtual Machine, and then From Gallery.

Create and Prepare WSFC Nodes

In this section, you will create three more VMs in Windows Azure.

Use the following table to help you configure the three VMs.

	VM1	VM2	VM3
Operating system selection	Windows Server 2008 R2 SP1, December 2012	Microsoft SQL Server 2012 Evaluation Edition	Microsoft SQL Server 2012 Evaluation Edition
Virtual Machine Name	ContosoWSFCNode	ContosoSQL1	ContosoSQL2
Size	Medium	Large	Large
New User Name	ContosoAdmin	ContosoAdmin	ContosoAdmin
Connected to an existing virtual machine	Selected (ContosoDC)	Selected (ContosoDC)	Selected (ContosoDC)
Storage Account	contososa2	contososa2	contososa2
Virtual Network Subnets	Back (10.10.2.0/24)	Back (10.10.2.0/24)	Back (10.10.2.0/24)
Availability Set	Create availability set (SQLHADR)	SQLHADR	SQLHADR

When you select Connect to an existing virtual machine and select ContosoDC (the only choice), it is the same as telling Windows Azure to place the VM in the same cloud service as ContosoDC, which is ContosoSQLsvc as I have specified it in my screenshot earlier. Again, this cloud service name is unique on the internet.

It does not take that much time to configure the three VMs, but be prepared to wait for a long time for all three VMs to be provisioned. Depending on the circumstances, it can take anywhere from a few hours to a full day for Windows Azure to fully provision three VMs.

Once the three VMs are fully provisioned, you need to join them to the corp.contoso.com domain and grant CORP\Install administrative rights to the machines. To do this, follow the steps below for each of the three VMs.

Download each VM's remote desktop (RDP) file to your local directory by selecting the VM in the list and clicking the Connect button. To select a VM, click anywhere but the first cell in the row, as shown below.
Launch the RDP file you downloaded and log into the VM using your configured administrator account (ContosoAdmin).
Once you are logged in, the Initial Configuration Tasks window is automatically launched. Click the Configure networking link as shown below.
The Network Connections window is opened. Right-click Local Area Connection 3 and click Properties.
Select Internet Protocol Version 4 (TCP/IPv4) and click Properties.
Select Use the following DNS server addresses and specify 10.10.2.4 in Preferred DNS server, as shown below.

The address 10.10.2.4 is the address assigned to a VM in the 10.10.2.0/24 subnet in a Windows Azure virtual network, and that VM is ContosoDC. To verify ContosoDC's IP address, use the nslookup contosodc in the command prompt, as shown below.
Click OK twice to commit the changes. You are now able to join the VM to corp.contoso.com.
Back in the Initial Configuration Tasks, click the Provide computer name and domain link.
In the System Properties dialog, click Change.
Select the Domain check box and type corp.contoso.com in the text box, then click OK.
In the Windows Security popup dialog, specify the credentials for the default domain administrator account (CORP\Administrator) and the password you specified.
When you see the "Welcome to the corp.contoso.com domain" message, click OK.
In Computer Name/Domain Changes, click OK, then click OK in the popup dialog.
In System Properties, click Close, then click Restart Now in the popup dialog to restart the VM.
Wait a few minutes for the VM to be restarted, then launch the RDP file again to log into the VM using the BUILTIN\Administrator account.
In the Start menu, type "user", then click Give administrative rights to a domain user.
In the User Accounts dialog, click Add.
In Add New User, specify Install in User name and CORP in Domain, as shown below, then click Next.
Select Administrator to add the CORP\Install to the local Administrators group, then click Finish, and then click OK to close the User Accounts dialog.
Next, you add the Failover Clustering feature to the VM. In the Initial Configuration Tasks window, click Add features.
In the Add Features Wizard, select Failover Clustering and click Next.
In Confirm Installation Selections, click Install.
When the Failover Clustering feature installation is completed, click Close.
Log out of the VM.

Create WSFC Cluster

In this section, you create the WSFC cluster that will host the availability group you will create later. By now, you should have done the following to each of the three VMs you will use in the WSFC cluster:

Fully provisioned in Windows Azure
Joined VM to the domain
Added CORP\Install to the local Administrators group
Added the Failover Clustering feature

All these are prerequisites on each VM before you can join it to the WSFC cluster.

Also, note that the Windows Azure virtual network does not behave in the same way as an on-premise network, you need to create the cluster in the following order:

Create a single-node cluster on one of the nodes (ContosoSQL1)
Modify the cluster IP address to an unused IP address (10.10.2.101)
Bring the cluster name online
Remove the IP address resource for the cluster group
Add the other nodes (ContosoSQL2 and ContosoWSFCNode)

Finally, you are ready to move on. Follow the steps below to fully configure the cluster.

Launch the RDP file for ContosoSQL1 and log in using the credentials for CORP\Install.
Open Server Manager, then expand the Features node, then right-click Failover Cluster Manager, and then click Create a Cluster, as shown below.
In the Create Cluster Wizard, click Next. Then, type ContosoSQL1 in Enter server name and click Add, and then click Next again, as shown below.
In the Validation Warning page, click No, I do not require support from Microsoft for this cluster, and therefore do not want to run the validation tests. When I click Next, continue creating the cluster. Then, click Next.
In the Cluser Name text box, type Cluster1, then click Next.
In the Confirmation page, click Next to begin cluster creation. Once the cluster is created, click Finish to close the wizard.
In Server Manager, expand Failover Cluster Manager, then click Cluster1.corp.contoso.com, then scroll down in the center pane, and then expand Cluster Core Resources. You should see both the Name and the IP Address resources in the Failed state. The IP address resource cannot be brought online because the cluster is assigned the same IP address as that of the machine itself, which is a duplicate address. Right-click the failed IP Address resource, and then click Properties, as shown below.
Select Static IP Address and specify 10.10.2.101 in the Address text box, as shown below. Then, click OK.
In the Cluster Core Resources section, rlight-click Name: Cluster1 and click Bring this resource online, as shown below. Then, wait until both resources are online. When the cluster name resource comes online, it updates the DC server with a new AD computer account. This AD account will be used to run the availability group clustered service later.
Now that the AD account is created, bring the cluster name offline. Right-click the Name: Cluster1 resource and click Take this resource offline. In the pop-up confirmation dialog, click Take Name: Cluster1 offline.
Next, remove the cluster IP address, which is not needed to run the availability group. Right-click IP Address: 10.10.2.101 and click Delete. After you delete the cluster IP address, the Name: Cluster1 resource can no long come online because it depends on the IP address resource. However, your availability group does not depend on the cluster name or IP address in order to work properly, so you can leave the cluster name offline.
Finally, you add the remaining nodes to the cluster. In the browser tree, right-click Cluster.corp.contoso.com and click Add Node.
In the Add Node Wizard, click Next. Then, in the Select Servers page, add ContosoSQL2 and ContosoWSFCNode to the list by typing the server name in Enter server name and then clicking Add. Once your list looks similar to the screenshot below, click Next.
In the Validation Warning page, click No, I do not require support from Microsoft for this cluster, and therefore do not want to run the validation tests. When I click Next, continue creating the cluster. Then, click Next twice to add the nodes. Once the nodes are added to the cluster, click Finish.

Failover Cluster Manager should now show that your cluster has three nodes and list them in the Nodes container, as shown below.
Log out of the remote desktop session.

Prepare SQL Server Instances for Availability Group

In this section, you will do the following on both ContosoSQL1 and contosoSQL2:

Add a login for NT AUTHORITY\System with a necessary permissions set to the default SQL Server instance
Add CORP\Install as a sysadmin role to the default SQL Server instance
Open the firewall for remote access of SQL Server
Enable the AlwaysOn Availability Groups feature
Change the SQL Server service account to CORP\SQLSvc1 and CORP\SQLSvc2, respectively

The actions above can be performed in any order, nevertheless the steps below will walk through them in order. Follow the steps for both ContosoSQL1 and ContosoSQL2:

If you have not logged out of the remote desktop session for the VM, do so now.
Launch the RDP file for the VM and log in as BUILTIN\Administrator.
First, you add CORP\Install as a sysadmin role to the default SQL Server instance. From the Start menu, launch SQL Server Management Studio.
Click Connect to connect to the default SQL Server instance.
In Object Explorer, expand Security, right-click Logins, and click New Login.
Type NT AUTHORITY\System in Login name and click OK.
In Object Explorer, right-click NT AUTHORITY\System and click Properties.
In the Securables page, for the local server, select Grant for the following permissions and click OK.
- Alter any availability group
- Connect SQL
- View server state
In Object Explorer, right-click Logins again and click New Login.
Type CORP\Install in Login name.
In the Server Roles page, select sysadmin. Then, click OK. Once the login is created, you can see it by expanding Logins in Object Explorer.
From the Start menu, launch Windows Firewall with Advanced Security.
Right-click Inbound Rules and click New Rule.
In the Rule Type page, select Program, then click Next.
In the Program page, select This program path and type %ProgramFiles%\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn\sqlservr.exe in the text box. Then, click Next.
In the Action page, keep Allow the connection selected and click Next.
In the Profile page, accept the default settings and click Next.
In the Name page, specify a rule name, such as SQL Server (Program Rule) in the Name text box, then click Finish.
Next, you enable the AlwaysOn Availability Groups feature. From the Start menu, launch SQL Server Configuration Manager.
In the browser tree, click SQL Server Services, then right-click the SQL Server (MSSQLSERVER) service and click Properties.
Click the AlwaysOn High Availability tab, then select Enable AlwaysOn Availability Groups, and then click Apply. Click OK in the pop-up dialog, and do not close the properties window yet. You will restart the SQL Server service after you change the service account.
Next, you change the SQL Server service account. Click the Log On tab, then type CORP\SQLSvc1 (for ContosoSQL1) or CORP\SQLSvc2 (for ContosoSQL2) in Account Name, then fill in and confirm the password, and then click OK.
In the pop-up window, click Yes to restart the SQL Server service. After the SQL Server service is restarted, the changes you made in the properties window are effective.
Log out of the VM.

Create Availability Group

You are now ready to configure an availability group. Below is an outline of what you will do:

Create a new database (MyDB1) on ContosoSQL1
Take both a full backup and a transaction log backup of the database
Restore the full and log backups to ContosoSQL2 with the NORECOVERY option
Create the availability group (AG1) with synchronous commit, automatic failover, and readable secondary replicas

Follow the instructions below:

If you have not already logged out of the remote desktop sessions for ContosoSQL1 and ContosoSQL2, do so now.
Launch the RDP file for ContosoSQL1 and log in as CORP\Install.
First, under C:\, create a directory called backup. You will use this directory use to back up and restore your database.
Right-click the new directory, point to Share with, and then click Specific people.
Add CORP\SQLSvc1 and give it the Read/Write permission, then add CORP\SQLSvc2 and give it the Read permission, and then click Share. Once the file sharing process is complete, click Done.
Next, you create the database. From the Start menu, launch SQL Server Management Studio, then click Connect to connect to the default SQL Server instance.
In the Object Explorer, right-click Databases and click New Database.
In Database name, type MyDB1, then click OK.
Next, you take a full backup of the database. In the Object Explorer, expand Databases, then right-click MyDB1, then point to Tasks, and then click Back Up.
In the Source section, keep Backup type set to Full. In the Destination section, click Remove to remove the default file path for the backup file.
In the Destination section, click Add.
In the File name text box, type \\ContosoSQL1\backup\MyDB1.bak. Then, click OK, and then click OK again to backup the database. When the backup operation completes, click OK again to close the dialog.
Next, you take a transaction log backup of the database. In the Object Explorer, expand Databases, then right-click MyDB1, then point to Tasks, and then click Back Up.
In Backup type, select Transaction Log. Keep the Destination file path set to the one you specified earlier and click OK. Once the backup operation completes, click OK again.
Next, you restore the full and transaction log backups on ContosoSQL2. Launch the RDP file for ContosoSQL2 and log in as CORP\Install. Leave the remote desktop session for ContosoSQL1 open.
From the Start menu, launch SQL Server Management Studio, then click Connect to connect to the default SQL Server instance.
In the Object Explorer, right-click Databases and click Restore Database.
In the Source section, select Device, and click the … button.
In Select backup devices, click Add.
In Backup file location, type \\ContosoSQL1\backup, then click Refresh, then select MyDB1.bak, then click OK, and then click OK again. You should now see the full backup and the log backup in the Backup sets to restore pane.
Go to the Options page, then select RESTORE WITH NORECOVERY in Recovery state, and then click OK to restore the database. Once the restore operation completes, click OK.
Now, you create an availability group. Go back to the remote desktop session for ContosoSQL1. In the Object Explorer in SSMS, right-click AlwaysOn High Availability and click New Availability Group Wizard.
In the Introduction page, click Next. In the Specify Availability Group Name page, type AG1 in Availability group name, then click Next again.
In the Select Databases page, select MyDB1 and click Next. The database meets the prerequisites for an availability group because you have taken at least one full backup on the intended primary replica.
In the Specify Replicas page, click Add Replica.
The Connect to Server dialog pops up. Type ContosoSQL2 in Server name, then click Connect.
Back in the Specify Replicas page, you should now see ContosoSQL2 listed in Available Replicas. Configure the replicas as shown below. When you are finished, click Next.
In the Select Initial Data Synchronization page, select Join only and click Next. You have already performed data synchronization manually when you took the full and transaction backups on ContosoSQL1 and restored them on ContosoSQL2. You can instead choose not to perform the backup and restore operations on your database and select Full to let the New Availability Group Wizard perform data synchronization for you. However, this is not recommended for very large databases that are found in some enterprises.
In the Validation page, click Next. This page should look similar to below. There is a warning for the listener configuration because you have not configured an availability group listener. I intentionally skipped that step because availability group listeners are not currently supported in Windows Azure VMs.
In the Summary page, click Finish, then wait while the wizard configures the new availability group. In the Progress page, you can click More details to view the detailed progress. Once the wizard is finished, inspect the Results page to verify that the availability group is successfully created, as shown below, then click Close to exit the wizard.
In the Object Explorer, expand AlwaysOn High Availability, then expand Availability Groups. You should now see the new availability group in this container. Right-click AG1 (Primary) and click Show Dashboard.

Your AlwaysOn Dashboard should look similar to the one shown below. You can see the replicas, the failover mode of each replica and the synchronization state.
From the Start menu, open Server Manager again, then expand Features, then expand Failover Cluster Manager, then expand Cluster1.corp.contoso.com, and then expand Services and applications. A new clustered service called AG1 has been created in Cluster1 to represent the availability group that you just configured. Note that AG1 does not have any IP address by which database clients can connect to the availability group, because you did not configure one and because that IP address would not work even if you configured it because the Windows Azure virtual network does not support static IP addresses.

CAUTION: Do not try to fail over this clustered service. All failover operations should be performed from within AlwaysOn Dashboard in SSMS. For more information, see http://blogs.msdn.com/b/sqlalwayson/archive/2012/03/30/do-not-use-windows-failover-cluster-manager-to-perform-availability-group-failover.aspx.

Create Public Endpoints for Remote Clients

The new availability group is now online, but it is still not yet accessible from the internet. If your database client, such as an IIS server, runs within the same Windows Azure virtual network, then it is able to connect to the database servers directly within the virtual network and no additional steps are required. However, if your database client runs elsewhere, such as in your corporation's private network, you must open endpoints in Windows Azure to allow database access.

Follow the steps below:

Back in your Windows Azure portal, click Virtual Machines, then click ContosoSQL1.
In the dashboard for ContosoSQL1, click Endpoints, then click Add Endpoint.
In the Add Endpoint dialog, leave Add Endpoint selected and click the right arrow in the bottom-right corner.
Specify the endpoint details as shown below, then click the check mark in the bottom-right corner to commit the changes.

The private TCP port 1433 is the default port SQL Server uses to accept incoming remote connections. Note also that the public port 1 is actually opened on the cloud service ContosoSQLsvc which you created in the beginning, which is the only way to access the virtual machines. If you haven't noticed in the remote desktop connections, you have been connecting to different VMs by via the unique ports that Windows Azure has assigned to them within the cloud service.
Next, in the Windows Azure portal, click ContosoSQL2, then click Add Endpoint.
In the Add Endpoint dialog, leave Add Endpoint selected and click the right arrow in the bottom-right corner.
Specify the endpoint details as shown below, then click the check mark in the bottom-right corner to commit the changes. Since port 1 on cloud service ContosoSQLsvc is already used by ContosoSQL1, you use port 2 here.

You are done! You can remotely connect to these servers by pointing to the cloud service and the respective external port: ContosoSQLsvc.cloudapp.com:1 for ContosoSQL1 and ContosoSQLsvc.cloudapp.com:2 for ContosoSQL2.

HADR Tutorials for SQL Server in Windows Azure VMs

Cephas Lin — Tue, 18 Dec 2012 18:43:00 GMT

Hi Folks,

For those interested in how to do SQL Server HADR in Windows Azure VMs, we have published a set of tutorials on Azure Documentation. A conceptual article gives you the overview for the subject and the tutorials show you how to set up specific scenarios end-to-end. The docs are currently in pre-release mode.

SQL Server High Availability and Disaster Recovery in Windows Azure Virtual Machines

Tutorial: AlwaysOn Availability Groups in Windows Azure

Tutorial: AlwaysOn Availability Groups in Hybird IT

Tutorial: Database Mirroring for High Availability in Windows Azure

Tutorial: Database Mirroring for Disaster Recovery in Windows Azure

Tutorial: Database Mirroring for Disaster Recovery in Hybrid IT

Feel free to send your questions and feedback to cephalin@microsoft.com.

WSFC Cluster Configuration on Windows Azure Virtual Machines

Cephas Lin — Tue, 04 Dec 2012 00:03:00 GMT

Windows Azure Virtual Machines (VMs) are currently in preview at www.windowsazure.com. SQL Server high availability and disaster recovery solutions are supported on Windows Azure VMs.

For AlwaysOn Availability Groups, there is currently no support for availability group listeners in Windows Azure. Also, you must work around a DHCP limitation in Windows Azure when creating Windows Server Failover Clustering (WSFC) clusters in Windows Azure. For more information, see Configuring Windows Failover Cluster in Windows Azure for AlwaysOn Availability Groups.

AlwaysOn at SQLPASS 2012

luis vargas [MSFT] — Thu, 01 Nov 2012 01:20:00 GMT

SQLPASS is next week at the Washington State Convention Center (Seattle).

AlwaysOn will again be a significant aspect.

There’ll be many sessions discussing AlwaysOn from Microsoft and MVPs, a mission-critical booth where we’ll give guidance to customers, and a demo in the demo theater.

Wednesday 11/7

Sessions

SQL Server 2012 AlwaysOn Availability Groups Drilldown	Luis Vargas (Microsoft)	10:15 am – 11:30 am	302-TCC
Replication vs. AlwaysOn: Scalability Solutions with SQL Server 2012	Meir Dudai (MVP)	1:30 pm – 2:45 pm	611
SQLCAT: AlwaysOn HA/DR Design Patterns, Architectures and Best Practices	Sanjay Mishra, Mike Weiner (Microsoft)	3:00 pm – 4:15 pm	609
Real-Life SQL 2012 Availability Group Lessons Learned	Brent Ozar (MVP)	3:00 pm – 4:15 pm	6E
SQLCAT: AlwaysOn Unplugged – Everything You Want to Know About AlwaysOn	Steve Lindell, Min He, Eric Kang, Sanjay Mishra Goden Yao, Jean-Yves Devant, Sunil Agarwal, Dan Benediktson, Mahesh Sreenivas, Yuxi Bai, Matt Neerincx (Microsoft)	4:45 pm – 6:00 pm	305-TCC
SQLCAT: How Does Microsoft Run Its SAP Landscape on Windows and SQL Server?	Juergen Thomas (Microsoft)	4:45 pm – 6:00 pm	307-308

Booth

Mission-Critical

Luis Vargas (Microsoft)

2:00 pm – 4:00 pm

Product Pavilion

Thursday 11/8

Sessions

AlwaysOn: Availability Groups
and Replication Working Together

Jean-Yves Devant (Microsoft)

1:30 pm – 2:45 pm

302-TCC

SQLCAT: SQL
Server 2012 AlwaysOn HA/DR Customer Panel

Sanjay Mishra (Microsoft)

Ayad Shammout, David Smith,
Michael Steineke, Thomas Grohser, Wolfgang Kutschera (MVPs)

5:00 pm – 6:15 pm

608

Demo Theater

Using SQL Server 2012 AlwaysOn
Availability Groups for cost-effective high availability and disaster
recovery

Luis Vargas (Microsoft)

12:00 pm – 12:30 pm

Product Pavilion

Friday 11/9

Sessions

SQL Server in Azure VM patterns: Hybrid DR, data movement and BI	Guy Bowerman, Ross LoForte (Microsoft)	9:45 am – 11:00 am
The Good, Bad, and Ugly of Deploying AlwaysOn Availability Groups	Allan Hirt, David Smith, Michael Steineke (MVPs)	1:00 pm – 2:15 pm	615-617

How To: Migrate to AlwaysOn from Prior Deployments Combining Database Mirroring and Log Shipping – Part 2

Cephas Lin — Tue, 16 Oct 2012 16:35:00 GMT

Writer: Cephas Lin (Microsoft)

Contributors: Oleg Bulanyy (Microsoft), Jimmy May (Microsoft)

This blog is the continuation of a blog series that shows you how to perform the following end-to-end HADR migration scenario of SQL Server inside a test environment:

From a SQL Server 2008 R2 solution: Database mirroring for high availability and log shipping for disaster recovery
To a SQL Server 2012 solution: Availability Group for both high availability and disaster recovery

In How To: Migrate to AlwaysOn AlwaysOn from Prior Deployments Combining Database Mirroring and Log Shipping – Part 1, you created the base configuration. In this installment, you will apply the migration steps to the base configuration you created. Without further delay, let's start!

Step 1: Upgrade Log Shipping Secondary Database Server

Role: Database Administrator

The first step in the migration process is to upgrade the log shipping secondary database server (REMOTE1). If this server is a hot standby server (databases restored with the WITH STANDBY option), then you must first reconfigure all restore jobs to use the WITH NORECOVERY option before you can proceed. In this case, you have already configured the restore jobs to use the WITH NORECOVERY option, so you can simply proceed with the migration.

Log in to REMOTE1 as CORP\Install.
Run SQL Server Setup to upgrade the default SQL Server instance to SQL Server 2012. For more information, see Prerequisites.
Verify that the log shipping copy and restore jobs continue to run.
In Server Manager, add the Failover Clustering feature.
Install KB 2494036.

Step 2: Upgrade the Witness Server

Role: Database Administrator

The next server to be upgraded is the witness server in the database mirroring configuration (WITNESS). Note that when upgrading the servers in the database mirroring configuration, you are asked to remove WITNESS from the database mirroring session and rejoin it to the session multiple times. This procedure is based on the assumption that the upgrade process takes multiple days due to the additional verification steps you may wish to take after each server is upgraded. In this case, it is best to retain the automatic failover capability offered by WITNESS in the database mirroring session. However, if you plan to complete the upgrade within a few hours, there are multiple possible paths:

Remove WITNESS from the session, then proceed to upgrade PRIMARY1 and PRIMARY2 while upgrading WITNESS, and then rejoin WITNESS to the session.
Remove WITNESS from the session permanently. Upgrade PRIMARY1 and PRIMARY2, and then configure the availability group immediately.

When making a decision on your specific upgrade path, consider carefully how your decision changes the HADR capabilities of your system before, during, and after each upgrade step.

Follow the steps below to upgrade WITNESS.

Log in to WITNESS as CORP\Install.
Run SQL Server Setup to upgrade the default SQL Server instance to SQL Server 2012. For more information, see Prerequisites.
Verify that both PRIMARY1 and PRIMARY2 are reconnected to WITNESS in the database mirroring session.

Step 3: Upgrade the Mirror Database Server

Role: Database Administrator

The next server to be upgraded is the mirror server in the database mirroring configuration (PRIMARY2).

On PRIMARY1, remove the WITNESS server from the database mirroring configuration.
Log in to PRIMARY2 as CORP\Install.
Run SQL Server Setup to upgrade the default SQL Server instance to SQL Server 2012. For more information, see Prerequisites.
Verify that the database mirroring session is synchronized.
Return the WITNESS server to the database mirroring configuration.
In Server Manager, add the Failover Clustering feature.
Install KB 2494036.

Step 4: Upgrade the Primary Database Server

Role: Database Administrator

The next server to be upgraded is the primary server in the database mirroring configuration (PRIMARY1). This step involves failing over the databases to PRIMARY2, which has been upgraded to SQL Server 2012. Once the failover happens, the database mirroring session is suspended because data cannot move down SQL Server versions, resulting in growth of the transaction logs on PRIMARY2 (risking disk space exhaustion) until PRIMARY1 is upgraded to the same SQL Server version. Furthermore, if an automatic failover to PRIMARY2 occurs due to a failure, you cannot fail over the databases back to PRIMARY1 (no high availability) until PRIMARY1 is upgraded to the same SQL Server version. Therefore, make sure that you are ready to complete this upgrade step immediately once you failover the databases.

Perform a manual database mirroring failover of test1 and test2 from PRIMARY1 to PRIMARY2.
Remove the WITNESS server from the database mirroring configuration.
Log in to PRIMARY1 as CORP\Install.
Run SQL Server Setup to upgrade the default SQL Server instance to SQL Server 2012.
Resume the database mirroring session for the two databases, and then verify that the database mirroring session is synchronized.
Return the WITNESS server into the database mirroring configuration for both databases
Perform a manual database mirroring failover of test1 and test2 from PRIMARY2 to PRIMARY1.
In Server Manager, add the Failover Clustering feature.
Install KB 2494036.

At the end of these steps, you have the same HADR configuration that you started with, with the exception that all servers are running SQL Server 2012.

Step 5: Create a WSFC cluster

Domain Configuration

Role: Domain Administrator

You use CORP\Install to create and configure the WSFC cluster. In order for you to successfully create and run the cluster, this account needs to be configured to have the necessary permissions. Follow the steps below:

On DC, open Active Directory Users and Computers.
In the View menu, select Advanced Features.
Right-click corp.contoso.com and click Properties.
Click the Security tab. If you have not selected the Advanced Features option previously, this tab is not visible.
Click Advanced.
In the Advanced Security Settings for corp dialog, click Add, then type CORP\Install in the text box to add a security object for the account.
Select the Allow check box for the Create Computer Objects permission.
Click OK, then click OK again.

It is possible to set a more restricted permissions set for the WSFC cluster and clustered service. For more information, see Configure Windows Failover Clustering for SQL Server (Availability Group or FCI) with Limited Security.

Cluster Configuration

Role: Database Administrator

Before configuring the availability group, you must create a WSFC cluster that includes PRIMARY1, PRIMARY2, and REMOTE1. In this scenario, you use Node and File Share Majority for the WSFC quorum while removing the quorum vote from REMOTE1. This setup gives your WSFC quorum three votes at the primary site to ensure that the cluster is online in the event of a single-machine failure.

In a WSFC cluster, quorum health is paramount to the availability of the WSFC cluster and, consequently, of the availability group it hosts. Optimal quorum health not only determines whether an availability group has high availability, but also whether it is available at all. Make sure that you have full understanding of the WSFC quorum before attempting to configure an availability group on your production database servers. For more information, see Microsoft SQL Server AlwaysOn Solutions Guide for High Availability and Disaster Recovery.

Log in to any one of the following three servers as CORP\Install: PRIMARY1, PRIMARY2, or REMOTE1.
Create a new failover cluster with the following PowerShell commands (run with administrative privileges).

Import-Module FailoverClusters

New-Cluster -Name SQLServer2012 -Node PRIMARY1,PRIMARY2,REMOTE1
Create a share folder on WITNESS called \\WITNESS\WSFCQuorum and grant the SQLServer2012$ computer account Read/Write access to the folder. The SQLServer2012$ cluster account was created when you created the cluster in the previous step. It must be able to manipulate the file share witness before you can successfully configure the file share majority quorum.
Set the quorum mode and test the cluster with the following PowerShell commands:

Set-ClusterQuorum -NodeAndFileShareMajority \\WITNESS\WSFCQuorum

Test-Cluster
Remove the default quorum vote from REMOTE1 using the following PowerShell command:

(Get-ClusterNode "REMOTE1").NodeWeight = 0

The quorum for the SQLServer2012 cluster should now look as follows:
- PRIMARY1: 1 vote
- PRIMARY2: 1 vote
- File Share Witness: 1 vote
- REMOTE1: 0 vote
NOTE: The default quorum model, where each server has one vote without a file share witness, is technically an acceptable configuration. If intersite network fails, the primary site has node majority. Moreover, if any one of the three cluster nodes fails, the cluster still has node majority. However, the quorum model you configure here is a best-practice configuration that always gives the disaster recovery site zero votes while ensuring that the primary site has node majority in the event of a single-node failure. For more information, see Microsoft SQL Server AlwaysOn Solutions Guide for High Availability and Disaster Recovery.

Step 6: Configure the Availability Group

Role: Database Administrator

Finally, you configure the availability group to complete your migration process.

While the New Availability Group Wizard gives you the option to prepare the secondary replicas by taking the necessary backups, it is not designed for very large databases that may exist in a Tier-1 environment. For very large databases, it is recommended that you manually prepare the secondary replicas in the same way you prepare mirror databases in a database mirroring configuration. This preparation involves taking a full backup and the most recent log backup of your databases and restore them with the WITH NO RECOVERY option on the intended secondary replica servers. However, PRIMARY2 already has a synchronized copy of the databases, and REMOTE1 is already restoring the shipped logs. The existing configuration reduces the amount of work required to fully configure the availability group.

Enable AlwaysOn Availability Groups Feature

You must first enable the AlwaysOn Availability Groups feature for each SQL Server instance.

On REMOTE1, enable the AlwaysOn Availability Groups feature and restart the SQL Server instance.
On PRIMARY2, enable the AlwaysOn Availability Groups feature and restart the SQL Server instance.
On PRIMARY1, enable the AlwaysOn Availability Groups feature and restart the SQL Server instance. This action causes an automatic failover of the two databases to PRIMARY2.

The AlwaysOn Availability Group tab on each of your servers should look similar to the screenshot below.

At the end of these steps, PRIMARY2 is the principal database server in the database mirroring configuration and the primary server in the log shipping configuration.

Configure the Availability Group

Perform the following steps to configure the availability group:

Remove the log shipping configuration on PRIMARY2 (and on REMOTE1, as a side effect) for both database test1 and test2.
Perform a manual database mirroring failover of both databases back to PRIMARY1.
Remove the log shipping configuration on PRIMARY1 for both databases. Make sure that the log tail is restored on REMOTE1.
Remove the database mirroring sessions for both databases.

The console application on CLIENT should continue to show Success:... for write access. The connection string currently used by the application continues to work because it points to PRIMARY1 in the Server parameter, which was the principal database server before you removed the database mirroring configuration.
On REMOTE1, open the default port used by SQL Server for AlwaysOn Availability Groups, with the following command. Notice it is the same as the default database mirroring port. Remember that you already opened these ports on PRIMARY1 and PRIMARY2.

netsh advfirewall firewall add rule name='SQL Server HADR (TCP-In)' dir=in action=allow protocol=TCP localport=5022 profile=domain
While connected to PRIMARY1 in SSMS, right-click AlwaysOn High Availability and click New Availability Group Wizard.
In the New Availability Group Wizard, click Next.
In the Availability group name box, type AG1, then click Next.
Select test1 and test2, then click Next.
In the Replicas tab, click Add Replica.
In the Server name box, type PRIMARY2, and then click Connect.
In the Replicas tab, click Add Replica.
In the Server name box, type REMOTE1, and then click Connect.

Configure the three server instances as shown below.

Server Instance	Initial Role	Automatic Failover	Synchronous Commit	Readable Secondary
PRIMARY1	Primary	X	X	Yes
PRIMARY2	Secondary	X	X	Yes
REMOTE1	Secondary			Yes

Accept default settings in the Endpoints tab. The ports used for the endpoints should be 5022, the same as the ones previously configured for database mirroring.
In the Backup Preferences tab, keep the default Prefer Secondary option and configure the following priority settings for the replicas:
- PRIMARY1: 50
- PRIMARY2: 50
- REMOTE1: 60
By giving REMOTE1 higher priority, this configuration keeps the backup workload off the automatic failover set (PRIMARY1 and PRIMARY2).

Note: This configuration alone does not actually cause any backup job to run. You still need to configure the desired backup jobs on each availability replica. For more information, see Configure Backup on Availability Replicas (SQL Server).
In the Listener tab, select Create an availability group listener, and then specify the following settings for the listener:
- Listener DNS Name: AG1
- Port: 10000
- Network Mode: Static IP
- IPv4 Address for 10.1.1/24: 10.1.1.111
- IPv4 Address for 10.2.2/24: 10.2.2.222
Click Next.
Select Join only for data synchronization preference, and then click Next. You use the mirror databases and log shipping secondary databases from the previous configuration as secondary replicas, which all have recently restored log backups, so you do not need to synchronize data.
Once validation finishes without errors, click Next, then click Finish.

Note: The console application on CLIENT should continue to show Success:.... However, with the current availability group configuration, the Failover Partner parameter is only a dummy parameter. For conditions where the Failover Partner parameter can continue to work with an availability group, see Migration Guide: Migrating to AlwaysOn Availability Groups from Prior Deployments Combining Database Mirroring and Log Shipping Par I – Prescriptive Guidance (http://msdn.microsoft.com/en-us/library/jj635217). In this situation, you must reconfigure the client application to ensure that your application is highly available.

You can also view the WSFC cluster information in the Failover Cluster Manager, as shown in the screenshot below.

The availability group (AG1) runs inside the WSFC cluster as a clustered service. PRIMARY1 and PRIMARY2, which are in the automatic failover set, are the preferred owners of AG1. The IP addresses for the availability group listener are in an OR configuration, where one IP address is online at any given time, depending on which subnet the primary replica resides. The WSFC service manages aspects the resource group ownership and monitors the quorum health for the availability group.

IMPORTANT: Other than quorum model and health management, you should avoid modifying the clustered service properties of the availability group in the Failover Cluster Manager. For example, do not perform availability group failovers using the Failover Cluster Manager. SSMS is the user interface you should use for availability group management, such as performing failovers, adding and removing replicas and databases, and managing availability group listeners. For more information, see DO NOT use Windows Failover Cluster Manager to perform Availability Group Failover.

Reconfigure the Client Application

Role: Application Developer

Next, you reconfigure your client application to point to the new availability group listener. The availability group listener is not the only way to connect to the databases. You can also connect directly to the databases on any one of the server instances. For example, you can connect directly to test1 on PRIMARY2 for read-only workload using Server=PRIMARY2;Initial Catalog=test1. AlwaysOn Availability Groups also support new connection string keywords. For more information on client connectivity for AlwaysOn Availability Groups, see Availability Group Listeners, Client Connectivity, and Application Failover (SQL Server).

If you have not already done so, close the running console application on CLIENT.
In Program.cs, locate the following line:

const string connString = @"Server=PRIMARY1;Failover Partner=PRIMARY2;Initial Catalog=test1;Integrated Security=True";
Replace this line with the following code to use the availability group listener and port number you configured:

const string connString = @"Server=tcp:AG1,10000;Database=test1;IntegratedSecurity=SSPI";
Run the application again by opening the Debug menu and clicking Start Debugging. The console application on CLIENT should now show Success:....

You're Done!

Congratulations! You have successfully migrated your HADR solution to AlwaysOn Availability Groups. Note that CORP\SQLClient still does not have access to the databases on the REMOTE1 instance. However, if you fail over the availability group to REMOTE1 in order to add the necessary permissions, you risk the possibility of data loss because REMOTE1 hosts an asynchronous replica. I would recommend that you simply incorporate this step into your disaster recovery plan.

Remember, the steps I present here may be much different from what you must implementyou're your particular migration scenario, but I hope it has given you a framework whereupon you can build the details. Whatever path you take to migrate your solution to SQL Server AlwaysOn, be sure to test your particular migration scenario extensively, including rehearsing failover scenarios and disaster recovery scenarios, before performing the migration on your production system.

Also, I have not shown you the new workloads that are possible with your new SQL Server AlwaysOn solution, such as using the read-only secondary replica for read workloads. For more information, see Availability Group Listeners, Client Connectivity, and Application Failover (SQL Server).

How To: Migrate to AlwaysOn AlwaysOn from Prior Deployments Combining Database Mirroring and Log Shipping – Part 1

Cephas Lin — Tue, 16 Oct 2012 15:42:00 GMT

Writer: Cephas Lin (Microsoft)

Contributors: Oleg Bulanyy (Microsoft), Jimmy May (Microsoft)

This blog series is a follow-up to my whitepaper Migration Guide: Migrating to AlwaysOn Availability Groups from Prior Deployments Combining Database Mirroring and Log Shipping. In this series, I will walk you through the following end-to-end migration scenario inside a test environment:

From a SQL Server 2008 R2 solution: Database mirroring for high availability and log shipping for disaster recovery
To a SQL Server 2012 solution: Availability Group for both high availability and disaster recovery

Before I go further, l want to refer you to a whitepaper AlwaysOn Architecture Guide: Building a High Availability and Disaster Recovery Solution by Using AlwaysOn Availability Groups. This is a great architectural guide for anyone who is interested about the end-to-end deployment of the AlwaysOn scenario I'm discussing in this blog.

How to Use This Blog Series

This is a disclaimer similar to the one I have in How To: Migrate from Failover Cluster Instances and Database Mirroring to SQL Server AlwaysOn. My walkthrough steps are to help people get started on implementing a end-to-end migration scenario in a test environment. I have already published prescriptive guidance on the migration scenario in Migration Guide: Migrating to AlwaysOn Availability Groups from Prior Deployments Combining Database Mirroring and Log Shipping. If you are a database architect seeking guidance on migrating your production system to SQL Server AlwaysOn, I advise you to look there first. If you are a database administrator who needs help to get started on migrating your company's database system to SQL Server AlwaysOn, then my blog is for you. To expedite the testing phase of you migration scenario, I recommend that you walk through the entirety of this blog series, which can help you recognize and avoid many issues that can trip you up along the way. Once you have successfully completed the walkthrough in your test environment, you will know what success looks for a generalized scenario. The next natural step continue the testing phase with the specification of your SQL Server deployment.

I cannot emphasize enough: DO NOT apply these migration steps blindly to your production system. To minimize the risks of your actual migration to SQL Server AlwaysOn, test these steps thoroughly against every aspect of your SQL Server deployment (I repeat, in a test environment) and incorporate all the necessary tweaks to accommodate your specific scenario.

Migration Walkthrough Overview

You will walk through the migration procedures as covered in Migration Guide: Migrating to AlwaysOn Availability Groups from Prior Deployments Combining Database Mirroring and Log Shipping Par I – Prescriptive Guidance.

The following table outlines the starting point and the end point of the walkthrough.

Start	End
PRIMARY1, PRIMARY2, and WITNESS in Subnet1, implementing database mirroring for local high availability REMOTE1 in Subnet2, shipping logs from PRIMARY1 and PRIMARY2 for remote disaster recovery	One availability group on PRIMARY1, PRIMARY2, and REMOTE1 PRIMARY1 as primary replica PRIMARY2 as synchronous secondary replica REMOTE1 as asynchronous secondary replica

The four stages of migration as covered in the whitepaper are as follows:

Online upgrade of the log shipping secondary database server (REMOTE1).
Online upgrade of the mirror database server (PRIMARY2).
Online upgrade of the principal database server (PRIMARY1).
Configuration of an availability group.

If you build the base configuration I prescribe here, you will find a clear path to success in the migration procedure. You can instead use your existing HADR solution as the base configuration, but just realize that I cannot guarantee that my migration steps will work as-is with your HADR solution. In all likelihood, you will need to tweaks as you follow along. Make sure that you test the migration steps thoroughly before attempting the same steps on your production environment.

As client connectivity is an important part of the migration scenario, you will simulate a client workload on the database system to test the HADR capabilities of the system before, during, and after the migration process. In addition, the client workload is used to demonstrate how to configure client applications to work with AlwaysOn Availability Groups.

Industry Best Practices

You should observe all industry best practices in the implementation of your HADR system in general. For example, network redundancy is crucial for a production environment to circumvent outages due to network anomalies. You may notice that I simplify such elements especially in some screenshots, so that they don't become a distraction from the migration steps themselves.

Success Criteria

This walkthrough attempts to balance between a real-life migration environment and requirements and the generalized use case. While it does not possess the complexity of a real-life scenario, a few common migration criteria are included.

Maintain separation of roles, where applicable (domain administrator, database administrator, application developer).
Minimize downtime during migration.
Approximate the high availability and disaster recovery capabilities during migration.

Prerequisites

To prepare the base configuration, you need the following hardware and/or software:

Hardware to implement the network topology, including servers, switches, and so on. You can simulate the network topology using Hyper-V.
Windows Server 2008 R2
SQL Server 2008 R2 SP1 (see note below)
Visual Studio
Windows 7

To complete the migration process successfully, you need the following additional software:

SQL Server 2012 (see note below)
KB 2494036

NOTE: For more information on all supported version upgrades, see Supported Version and Edition Upgrades. The in-place upgrade described in this blog series does not support upgrade paths from or to Evaluation Editions of SQL Server. Furthermore, you must use an edition of SQL Server that supports database mirroring, log shipping, and AlwaysOn Availability Groups where applicable. The minimum license requirement for completing the walkthrough is summarized below.

SQL Server Developer	PRIMARY1, PRIMARY2, and REMOTE1
SQL Server Express	WITNESS

For more information, see Features Supported by the Editions of SQL Server 2012.

Step 0: Setup Base Configuration

So, let's start!

First, you implement the network topology shown in the diagram below. The two subnets simulate the two sites in Contoso Corporation. The client machine simulates a write workload on the SQL Server instances during the migration process.

The walkthrough steps focus on the software configuration on the server nodes and not on the implementation of the network topology.

Set up the Domain Controller and Accounts

Role: Domain Administrator

Set up the domain controller(DC) for DNS service with the domain specifications below.

Domain Name

CORP

FQDN

corp.contoso.com

Create the domain user accounts show below:

Domain Administrator	CORP\Administrator
SQL Installation	CORP\Install
Database Administrator	CORP\DBAdmin
Client Application	CORP\SQLClient
SQL Service Accounts	CORP\SQLSvc1 CORP\SQLSvc2 CORP\SQLSvc3 CORP\SQLSvc4 CORP\SQLAgent1 CORP\SQLAgent2 CORP\SQLAgent3 CORP\SQLAgent4

Deploy SQL Server 2008 R2 Solution

Role: Database Administrator

Perform the following steps on PRIMARY1, PRIMARY2, WITNESS, and REMOTE1:

Install Windows Server 2008 R2 SP1, and then join the machine to the domain.
Give CORP\Install administrative privileges on the local machine.
Install the default SQL Server 2008 R2 instance with the respective service accounts shown below.

Machine

SQL Service Account

SQL Agent Account

PRIMARY1

SQLSvc1

SQLAgent1

PRIMARY2

SQLSvc2

SQLAgent2

WITNESS

SQLSvc3

SQLAgent3

REMOTE1

SQLSvc4

SQLAgent4
Open the firewall for remote access of the default SQL Server instance with the following command:

netsh advfirewall firewall add rule name='SQL Server (TCP-In)' dir=in action=allow protocol=TCP localport=1433 profile=domain
In SQL Server Configuration Manager, under the SQL Server Network Configuration node, enable the TCP/IP protocol.
Restart the database instance.

Create Databases and Configure Database Mirroring

Role: Database Administrator

Perform the following steps to create the databases and configure database mirroring sessions.

On both PRIMARY1 and PRIMARY2, open port 5022, the default port SQL Server uses for database mirroring with the following command:

netsh advfirewall firewall add rule name='SQL Server Mirroring (TCP-In)' dir=in action=allow protocol=TCP localport=5022 profile=domain
In SQL Server Management Studio (SSMS), create two databases on PRIMARY1, named test1 and test2.
Create a table in both test1 and test2, called table1, using the following script:

CREATE TABLE dbo.table1(col1 nchar(10), col2 nchar(10))
Prepare the mirror databases for test1 and test2 on PRIMARY2.

Establish the database mirroring sessions on the two databases using the specifications below.

Principal Role	PRIMARY1
Principal Service Account	CORP\SQLSvc1
Principal Endpoint	TCP://primary1.corp.contoso.local:5022
Mirror Role	PRIMARY2
Mirror Service Account	CORP\SQLSvc2
Mirror Endpoint	TCP://primary2.corp.contoso.local:5022
Witness Role	WITNESS
Witness Service Account	CORP\SQLSvc3
Witness Endpoint	TCP://witness.corp.contoso.local:5022
Operating Mode	High safety

After you configure database mirroring on table1 and table2, you can use the Database Mirroring Monitor to make sure that the servers have connectivity and are synchronized for both databases. It should look similar to the screenshot below.

Configure Log Shipping

Role: Database Administrator

Perform the following steps to set up log shipping for the two databases.

On PRIMARY1, PRIMARY2, and REMOTE1, in SQL Server Configuration Manager, set the SQL Server Agent service to start automatically.
Start the SQL Server Agent service on all three machines.

Create the following share folders:

Network Name

Permissions

\\WITNESS\test1

\\WITNESS\test2

SQLSvc1: Read and Write

SQLSvc2: Read and Write

SQLSvc4: Read

SQLAgent4: Read

\\REMOTE1\test1

\\REMOTE1\test2

SQLSvc4: Read

SQLAgent4: Read and Write

On PRIMARY1 and PRIMARY2, set up log shipping of test1 and test2 to REMOTE1 using the following parameters:

Database	Backup Folder	Destination Folder for Copied Files	Restore Mode
test1	\\WITNESS\test1	\\REMOTE1\test1	No recovery
test2	\\WITNESS\test2	\\REMOTE1\test2	No recovery

NOTE: Since you are mixing database mirroring and log shipping, you cannot perform this step using the SSMS user interface alone. For instructions, see the Log Shipping to a Third Destination from a Database Mirroring Pair section in Database Mirroring and Log Shipping Working Together.

When you are finished, the SSMS view of the three database instances in Object Explorer should look similar to the screenshot below. PRIMARY1 is the principal database server for both databases and PRIMARY2 is the mirror database server.

Configure Access for the Client Application

Role: Database Administrator

For client access to the databases, perform the following steps in SSMS for the various database instances:

On PRIMARY1, add CORP\SQLClient as a login, and then give CORP\SQLClient the following database memberships for both test1 and test2:
- db_datareader
- db_datawriter
Perform a manual database mirroring failover for both test1 and test2 to PRIMARY2.
In PRIMARY2, add CORP\SQLClient as a login, and then give CORP\SQLClient the following database memberships for both test1 and test2:
- db_datareader
- db_datawriter
Perform a manual database mirroring failover back to PRIMARY1.

You will use CORP\SQLClient to connect to the databases from the client application in later steps. If a failure happens during your migration process, you have ensured that clients have access to the automatic failover partner. In a disaster recovery scenario, you must bring REMOTE1 online, and then add CORP\SQLClient to REMOTE1, and then configure the same database memberships so that client applications can connect to it.

Role: Application Developer

On CLIENT, perform the following steps to run the client application.

Install Windows 7.
Join the machine to the corp.contoso.com domain using the CORP\Administrator account.
Logout and login as CORP\Install.
Install SQL Server 2008 R2 Management Studio.
Start SSMS and connect to PRIMARY1 and PRIMARY2 to verify that the database instances can accept remote connections.
Install Visual Studio.
Logout and login as CORP\SQLClient.
Create a console application (named ConsoleApplication1 by default), and then replace the code in Program.cs with the code below. This code continually attempts to connect to test1 and insert a record. Each connection attempt has three retries and a 5-second interval between each retry. The connection logic is taken from Implementing Application Failover with Database Mirroring.

using System;

using System.Data;

using System.Data.SqlClient;

using System.Threading;

namespace ConsoleApplication1

{

class Program

  {

    const string connString = @"Server=PRIMARY1;Failover Partner=PRIMARY2;Initial Catalog=test1;Integrated Security=True";

    static bool ExecuteSQLWithRetry_NoResults(string strConn, string strCmd, int iRetryInterval, int iMaxRetries)

    {

      SqlConnection conn = null;

      SqlCommand cmd = null;

      for (int iRetryCount = 0; iRetryCount < iMaxRetries; iRetryCount++)

      {

        try

        {

          conn = new SqlConnection(strConn);

          conn.Open();

          if (null != conn && ConnectionState.Open == conn.State)

          {

            cmd = new SqlCommand(strCmd, conn);

            cmd.ExecuteNonQuery();

            return true;

          }

        }

        catch (Exception ex) { }

        finally

        {

          try

          {

            if (null != cmd)

            cmd.Dispose();

            if (null != conn && ConnectionState.Closed != conn.State)

            conn.Close();

          }

          catch (Exception ex) { }

        }

        if (iRetryCount < iMaxRetries)

        Thread.Sleep(iRetryInterval * 1000);

      }

      return false;

    }

    static void Main(string[] args)

    {

      while (true)

      {

        Random rand = new Random();

        string cmdString = "Insert into table1 values (N'" + rand.Next() + "',N'" + rand.Next() + "')";

        if (ExecuteSQLWithRetry_NoResults(connString, cmdString, 5, 3))

        Console.WriteLine("Success: " + cmdString);

        else Console.WriteLine("Error: " + cmdString);

      }

    }

  }

}
Run the application by opening the Debug menu and clicking Start Debugging.

You have now finished setting up the base configuration and are ready to start the migration scenario to the SQL Server AlwaysOn solution in SQL Server 2012.

But wait, this blog has gotten too long already! In the next installment, you will actually start the migration steps. Stay tuned!

How To: Migrate from Failover Cluster Instances and Database Mirroring to SQL Server AlwaysOn – Part 3

Cephas Lin — Fri, 28 Sep 2012 22:07:00 GMT

Writer: Cephas Lin (Microsoft)

Contributors: Oleg Bulanyy (Microsoft), Jimmy May (Microsoft)

NOTE: SQL Server Failover Clustering has been renamed AlwaysOn Failover Cluster Instances (FCI) in SQL Server 2012. For simplicity, the term FCI in this paper applies to either SQL Server Failover Clustering in SQL Server 2008 R2 or AlwaysOn FCI in SQL Server 2012.

This blog is the continuation of a blog series that shows you how to perform the following end-to-end HADR migration scenario of SQL Server inside a test environment:

From a SQL Server 2008 R2 solution: SQL Server Failover Clustering for high availability and database mirroring for disaster recovery
To a SQL Server 2012 solution: AlwaysOn Failover Cluster Instances for high availability and AlwaysOn Availability Groups for disaster recovery

In Part 2, you created a temporary FCI3 for primary site workload, reconfigured FCI2, and created an availability group that encompasses FCI3 and FCI2. This availability group contains both DB1 and DB2. In this installment, you will finish the migration steps by reconfiguring FCI1 and returning to the two-FCI configuration that you had pre-migration. So, let's start!

Step 5: Reconfigure FCI1 and Join It to Availability Group

In this section, you reconfigure FCI1 and join it to the availability group.

Reconfigure FCI1 with SQL Server 2012

Role: Database Administrator

First, you reconfigure FCI1 by reinstalling Windows Server and SQL Server completely on the FCI1 machines. For the reasons described in Step 3: Reconfigure FCI2 with SQL Server 2012 in Part 2, you cannot simply upgrade SQL Server 2012 on the existing configuration for FCI1.

Following the procedures at Quickly Setup SQL Server AlwaysOn Failover Cluster Instance (FCI) in Hyper-V, deploy FCI1 at the primary site using the specifications in the table below. The steps in the blog post can be applied to both physical and Hyper-V environments.

Make sure you use the steps below the table to join machines to Cluster3.

Version	SQL Server 2012
FCI Name	FCI1
Instance Name	LIVE_INST1
Machine Names	PRIMARY1 PRIMARY2
File Server Name	FILE1
WSFC Cluster Name	Cluster3*
Cluster Service IP Address	10.1.1.111
SQL Service Account	CORP\SQLSvc1
SQL Agent Account	CORP\SQLAgent1

*Instead of recreating Cluster1, you join the machines to the new cluster Cluster3. Follow the instructions below:

When you initialize the shared disks, do not create a new iSCSI target for the quorum disk because the existing Cluster3 cluster already has a quorum disk. Instead, for the iSCSI target of the existing quorum disk, add the IP addresses of PRIMARY1 and PRIMARY2 to the iSCSI Initiators Identifiers page.
When you configure PRIMARY1, use the following steps instead of the ones in the Configure [MACHINE_NAME1] section:
1. Log in as CORP\Install.
2. Join the machine to the Cluster3 cluster with the following PowerShell command.
  
  Import-Module FailoverClusters
  
  Get-Cluster Cluster3 | Add-ClusterNode PRIMARY1,PRIMARY2
  
  Move-ClusterGroup "Available Storage" -Node PRIMARY1
  
  Get-ClusterAvailableDisk | Add-ClusterDisk
  
  Test-Cluster
  
  After the Add-ClusterNode command runs, ignore the errors for the three missing SQL Server DLLs. These DLLs are added when you install FCI1 on the nodes.
  
  Note: The Move-ClusterGroup command moves the Available Storage resource group to PRIMARY1. This resource group holds all newly added shared storage. Unless either PRIMARY1 or PRIMARY2 owns this resource group, you cannot bring the shared disk online after you add it to the cluster because it cannot be brought online on any other node in the cluster.
  
  After the nodes are added to the cluster, your WSFC quorum should look as follows:
  - PRIMARY1: 1 vote
  - PRIMARY2: 1 vote
  - TEMP1: 1 vote
  - TEMP2: 1 vote
  - REMOTE1: 0 vote
  - REMOTE2: 0 vote
  - Quorum disk: 1 vote
  IMPORTANT: You should reconsider your quorum model carefully to maintain optimal WSFC quorum health both when and after adding the nodes. For more information, see the WSFC Quorum Modes and Voting Configuration section in Microsoft SQL Server AlwaysOn Solutions Guide for High Availability and Disaster Recovery.
3. Set the appropriate owners of the existing clustered services for FCI3 and FCI2, including the respective service resources, using the following PowerShell commands. Setting the proper node ownership ensures that FCI3 does not attempt to failover to the FCI1 nodes, which cannot host that FCI3.
  
  Get-ClusterGroup "SQL Server (TEMP_INST)" | Set-ClusterOwnerNode -Owners TEMP1,TEMP2
  
  Get-ClusterGroup "SQL Server (TEMP_INST)" | Get-ClusterResource | Set-ClusterOwnerNode -Owners TEMP1,TEMP2
  
  Get-ClusterGroup "SQL Server (LIVE_INST2)" | Set-ClusterOwnerNode -Owners REMOTE1,REMOTE2
  
  Get-ClusterGroup "SQL Server (LIVE_INST2)" | Get-ClusterResource | Set-ClusterOwnerNode -Owners REMOTE1,REMOTE2
4. Change to the directory that contains the setup.exe file for SQL Server 2012, then install FCI1 using the following command. Note that even though you initialize the data disk to use drive letter Z based on the blog instructions, the WSFC service changes it to a different drive letter when adding to because that drive letter is already used in the cluster (by FCI3). You must use the new drive letter that is assigned to your shared disk.
  
  .\Setup.exe /QS /ACTION=InstallFailoverCluster /IACCEPTSQLSERVERLICENSETERMS /FEATURES=SQL, ADV_SSMS /INSTANCENAME=LIVE_INST1 /SQMREPORTING=0 /FAILOVERCLUSTERDISKS="Cluster Disk 4" /FAILOVERCLUSTERIPADDRESSES="IPv4;10.1.1.111;Cluster Network 1;255.255.255.0" /FAILOVERCLUSTERNETWORKNAME=FCI1 /AGTSVCACCOUNT="CORP\SQLAgent1" /AGTSVCPASSWORD=[PASSWORD] /INSTALLSQLDATADIR="[SHAREDDISK_DRIVELETTER]:\" /SQLSVCACCOUNT="CORP\SQLSvc1" /SQLSVCPASSWORD=[PASSWORD] /SQLSYSADMINACCOUNTS="CORP\Install" "CORP\DBAdmin"
5. Run the following commands to open the firewall for SQL Server connectivity.
  
  netsh advfirewall firewall add rule name='SQL Server Browser (UDP-In 1434)' dir=in action=allow protocol=UDP localport=1434 profile=domain
  
  netsh advfirewall firewall add rule name='SQL Server (TCP-In)' program='C:\Program Files\Microsoft SQL Server\MSSQL11.LIVE_INST1\MSSQL\Binn\sqlservr.exe' dir=in action=allow protocol=TCP profile=domain
6. Run the following commands to set the proper ownership for the clustered service, including all the service resources.
  
  Get-ClusterGroup "SQL Server (TEMP_INST)" | Set-ClusterOwnerNode -Owners TEMP1,TEMP2
  
  Get-ClusterGroup "SQL Server (TEMP_INST)" | Get-ClusterResource | Set-ClusterOwnerNode -Owners TEMP1,TEMP2
  
  Get-ClusterGroup "SQL Server (LIVE_INST2)" | Set-ClusterOwnerNode -Owners REMOTE1,REMOTE2
  
  Get-ClusterGroup "SQL Server (LIVE_INST2)" | Get-ClusterResource | Set-ClusterOwnerNode -Owners REMOTE1,REMOTE2
  
  Get-ClusterGroup "SQL Server (LIVE_INST1)" | Set-ClusterOwnerNode -Owners PRIMARY1,PRIMARY2
  
  Get-ClusterGroup "SQL Server (LIVE_INST1)" | Get-ClusterResource | Set-ClusterOwnerNode -Owners PRIMARY1,PRIMARY2
  
  Get-ClusterGroup "Cluster Group" | Get-ClusterResource | Set-ClusterOwnerNode -Owners PRIMARY1,PRIMARY2,REMOTE1,REMOTE2,TEMP1,TEMP2
  
  The resource group "Cluster Group" contains cluster-level resources for Cluster3, including the quorum disk. The last command ensures that that all nodes have access to the quorum disk.

Once FCI2 has been reconfigured, it should look similar to Figure 9 when viewed in Failover Cluster Manager. Note that Cluster3 now has two cluster networks and two clustered services.

Figure 9 FCI1 after Reconfiguration as Shown in Failover Cluster Manager

Enable AlwaysOn Availability Groups on FCI1

Role: Database Administrator

First, you enable the AlwaysOn Availability Groups feature on FCI1. To do this, open a SQL Server PowerShell window from SSMS and run the following commands:

Enable-SqlAlwaysOn -Path SQLSERVER:\SQL\FCI1\LIVE_INST1 -Force

Add Primary Site to Availability Group

Role: Database Administrator

Next, you will add the primary site to the availability group.

Take both a full backup and a log backup for the two databases on FCI3\TEMP_INST and restore them on FCI1\LIVE_INST1. When restoring the databases, make sure that the WITH NO RECOVERY option is used.
In the AlwaysOn High Availability node for FCI3\TEMP_INST, under Availability Groups, right-click AG1 (Primary) and click Add Replica.
Click Next. In the Connect to Existing Secondary Replicas page, click Connect All, then click Connect.
Click Next.
In the Replica tab, click Add Replica.
In the Server name box, type FCI1\LIVE_INST1, and then click Connect.
For FCI1\LIVE_INST1, select Synchronous Commit (Up to 3) and Readable Secondary=Yes.
In the Backup Preferences tab, change the backup priority for FCI1\LIVE_INST1 to 50. Your backup priorities should now be as follows:
- FCI1\LIVE_INST1: 50
- FCI2\LIVE_INST2: 60
- FCI3\TEMP_INST: 50
This setting makes the remote site the preferred secondary replica for backup operations.
Select Join only for data synchronization preference, and then click Next.
Once validation finishes without errors, click Next, and then click Finish.

Once the availability group configuration is finished, you can monitor the availability group health in SSMS by right-clicking AlwaysOn High Availability and selecting Show Dashboard. It may take some time for FCI1\LIVE_INST1 to become synchronized. Once it is synchronized, the AlwaysOn Dashboard looks similar to Figure 10.

Figure 10 AlwaysOn Dashboard after Addition of FCI1\LIVE_INST1

Step 6: Remove Temporary FCI

The last step is to return the availability group to a two-FCI configuration. You will failover the availability group to the FCI1\LIVE_INST1, and then remove FCI3\TEMP_INST from the availability group.

Failover to Primary Site

Role: Database Administrator

Perform the following steps while connected to FCI3\TEMP_INST, which is currently hosting the primary replica in the availability group.

In the Object Explorer, right-click AlwaysOn High Availability, and click Show Dashboard.
In the dashboard, click AG1.
In the top-right corner of the dashboard, click Start Failover Wizard.
Click Next. Select FCI1\LIVE_INST1 as the new primary replica and click Next again.
In the Connect to Replica page, click Connect, then click Connect again, and then click Next.
Click Finish.
In the Results page, you are reminded to validate WSFC quorum vote configuration. Since your quorum state has not changed in this failover, you do not need to change the quorum settings. Click Close.

The AlwaysOn Dashboard may show error states until failover is complete and the dashboard is refreshed. By default, the dashboard refreshes every 30 seconds. You can refresh it manually by pressing F5.

Important: At this point, CORP\SQLClient still does not have access to the databases on FCI1\LIVE_INST1. You must perform the following steps immediately after the failover so that the client applications can recover database connectivity.

Add CORP\SQLClient as a login.
Give CORP\SQLClient the following database memberships for both DB1 and DB2:
- db_datareader
- db_datawriter

Remove Temporary FCI from Availability Group

Perform the following steps while connected to FCI1\LIVE_INST1, which is currently hosting the primary replica in the availability group.

In the Object Explorer, expand AlwaysOn High Availability Group, then expand Availability Groups, then expand AG1 (Primary), and then expand Availability Replicas.
Right-click FCI3\TEMP_INST (Secondary) and click Remove from Availability Group.
Click OK.

Once the replica has been removed, you will remove the clustered service and the nodes from Cluster3.
Log in to PRIMARY1 as CORP\Install and open Failover Cluster Manager.
In Failover Cluster Manager, expand Cluster3.corp.contoso.com, and then expand Services and applications.
Right-click SQL Server (TEMP_INST) and click Delete. Confirm by clicking Delete SQL Server (TEMP_INST).
In Cluster3.corp.contoso.com, click Storage.
In the Available Storage section, right-click Cluster Disk 2 and select Delete.

IMPORTANT: Before evicting TEMP1 and TEMP2 from the cluster, make sure all nodes have access to the quorum disk. If TEMP1 and TEMP2 are still the only nodes that can access it, the quorum disk is taken offline immediately after you evict the two nodes because no other node is able to take ownership of the quorum disk. You can test this condition by attempting to move the quorum disk between the remaining nodes. For example, to move the quorum disk to PRIMARY1, the following PowerShell command:

Move-ClusterSharedVolume "Cluster Disk 1" -Node PRIMARY1
In Cluster3.corp.contoso.com, click Nodes.
Inspect all the nodes in Cluster3 and make sure that all nodes are online. Specifically, the success of the following steps depends on whether the quorum is in optimal health. In other words, at least the following nodes should be online (the ones that have quorum votes):
- PRIMARY1
- PRIMARY2
- TEMP1
- TEMP2
Click Cluster3.corp.contoso.com and expand the Cluster Core Resources tab.
Make sure that the disk witness is online.
Click Nodes again.
Right-click TEMP1, then point to More Actions, and then click Evict.
Confirm by clicking Evict node TEMP1.
Right-click TEMP2, then point to More Actions, and then click Evict.
Confirm by clicking Evict node TEMP2.

Your Cluster3 quorum should now look as follows:
- PRIMARY1: 1 vote
- PRIMARY2: 1 vote
- REMOTE1: 0 vote
- REMOTE2: 0 vote
- Quorum disk: 1 vote

After you finish removing the FCI3 nodes from Cluster3, the cluster should look similar to Figure 11. The storage view is shown here to demonstrate that the quorum disk is online and hosted by one of the remaining nodes.

Figure 11 Failover Cluster Manager View after Removal of FCI3

You're Done!

Whew! Three blogs later, you have migrated a SQL Server 2008 R2 HADR solution to a SQL Server AlwaysOn solution. Take a look at the table below for a comparison of the two solutions.

	SQL Server 2008 R2 Solution (Pre-Migration)	SQL Server AlwaysOn Solution (Post-Migration)
Number of WSFC Clusters	2	1
Number of FCIs	2	2
Number of Cluster Networks	1 in each cluster for the respective subnet	2 in the cluster, spanning both subnets
Number of Cluster Disks	2 in each cluster (1 for quorum and 1 for FCI)	3 (1 for quorum and 1 for each FCI)
Number of Nodes	2 in each cluster	4
Number of Clustered Services	1 in each cluster	3 (1 for each FCI and 1 for availability group)
Cluster Resource Ownership	Owned by all nodes in each cluster	FCI resources owned by respective FCI nodes. Availability group resources owned by all nodes.
HA Failover Approach	FCI failover in Failover Cluster Manager	FCI failover in Failover Cluster Manager
DR Failover Approach	Database mirroring failover in SQL Server Management Studio	Availability group failover in SQL Server Management Studio

Migrating an HADR solution is not a trivial matter, and clearly, migrating to SQL Server AlwaysOn is not an exemption. Hopefully, I have put you on the right track by helping you avoid many of the potential issues. I want to remind you at this point that the whitepaper: Migration Guide: Migrating to SQL Server 2012 Failover Clustering and Availability Groups from Prior Clustering and Mirroring Deployments, Part 1, shows you an alternative migration approach without using intermediate hardware. If there is the interest, I can blog about that in a separate series. Whatever path you take to migrate your solution to SQL Server AlwaysOn, be sure to test your particular migration scenario extensively, including rehearsing failover scenarios and disaster recovery scenarios, before performing the migration on your production system.

How To: Migrate from Failover Cluster Instances and Database Mirroring to SQL Server AlwaysOn – Part 2

Cephas Lin — Fri, 28 Sep 2012 21:33:00 GMT

Writer: Cephas Lin (Microsoft)

Contributors: Oleg Bulanyy (Microsoft), Jimmy May (Microsoft)

This blog is the continuation of a blog series that shows you how to perform the following end-to-end HADR migration scenario of SQL Server inside a test environment:

From a SQL Server 2008 R2 solution: SQL Server Failover Clustering for high availability and database mirroring for disaster recovery
To a SQL Server 2012 solution: AlwaysOn Failover Cluster Instances for high availability and AlwaysOn Availability Groups for disaster recovery

In Part 1, you created the base configuration. In this installment, you will apply the migration steps to the base configuration you created. Without further delay, let's start!

Step 1: Deploy FCI3 as Intermediate FCI

Role: Database Administrator

First, you create a temporary FCI in 10.1.1.X/24. Following the procedures at Quickly Setup SQL Server AlwaysOn Failover Cluster Instance (FCI) in Hyper-V, deploy FCI3 in 10.1.1.X/24 using the specifications in the table below. The steps in the blog post can be applied to both physical and Hyper-V environments.

WSFC Cluster Name	Cluster3
SQL Server Version	SQL Server 2012
FCI Network Name	FCI3
Instance Name	TEMP_INST
Machine Names	TEMP1 TEMP2
File Server Name	FILE1
Cluster Service IP Address	10.1.1.133
SQL Service Account	CORP\SQLSvc3
SQL Agent Account	CORP\SQLAgent3

Figure 5 shows the details of FCI3 after you create it, as viewed in Failover Cluster Manager. Similar to FCI1 and FCI2, FCI3 has a quorum model of Node and Disk Majority.

Figure 5 FCI3 as Shown in Failover Cluster Manager

FCI3 is used in this walkthrough as a temporary replacement FCI to preserve the two-FCI configuration when you are reconfiguring FCI1 or FCI2. You can perform migration without the temporary FCI and still preserve the high availability of your databases, but note that when FCI1 or FCI2 is being reconfigured or upgraded using this alternate approach, the high availability of your database instance rests upon the single remaining FCI.

In addition, FCI3 is used to set up the availability group in the SQL Server AlwaysOn solution, and the WSFC cluster used to host FCI3 (Cluster3) will become a permanent fixture in the final configuration.

Step 2: Redirect Database Mirroring to FCI3

Role: Database Administrator

In this section, you will remove the existing database mirroring session between FCI1\LIVE_INST and FCI2\LIVE_INST and configure a new database mirroring session between FCI1\LIVE_INST and FCI3\TEMP_INST. These actions effectively remove FCI2 from the HADR solution so that it can be reconfigured, while still maintaining two FCIs in your HADR database system. However, removing FCI2 also means that the system does not have any disaster recovery capability until FCI2 is reintroduced to your HADR system.

Perform the following steps while connected to FCI1\LIVE_INST and FCI3\TEMP_INST.

Prepare the mirror databases for DB1 and DB2 on FCI3\TEMP_INST.
On FCI1\LIVE_INST, remove the existing database mirroring sessions for DB1 and DB2.
On FCI1\LIVE_INST, establish the database mirroring sessions on DB1 and DB2 using the specifications in Table 7.

Primary FCI\Instance	FCI1\LIVE_INST
Mirror FCI\Instance	FCI3\TEMP_INST
Primary Service Account	CORP\SQLSvc1
Mirror Service Account	CORP\SQLSvc3
Primary Endpoint	TCP://fci1.corp.contoso.local:5022
Mirror Endpoint	TCP://fci3.corp.contoso.local:5022

At this point, FCI2 is no longer being used, and it can be reconfigured to be part of Cluster3.

Step 3: Reconfigure FCI2 with SQL Server 2012

Role: Database Administrator

Next, you reconfigure FCI2 by reinstalling Windows Server and SQL Server completely on the FCI2 machines. You cannot simply upgrade SQL Server 2012 on the existing configuration for FCI2 for the following reasons:

The SQL Server AlwaysOn solution requires that all the machines to be joined to the new WSFC cluster (Cluster3).
As each machine can be joined to only one WSFC cluster at the time, the existing WSFC cluster (Cluster2) must be destroyed.
Since FCI2 is clustered service hosted by Cluster2, destroying Cluster2 also renders FCI2 unusable.
Previously, FCI1 and FCI2 can share the same instance name in a database mirroring configuration because they run in separate WSFC clusters. However, the fact that they ultimately participate in the same availability group requires that they run in a single WSFC cluster, each having a unique instance name.

While you can manually delete both the FCI2 clustered service, the Cluster2 cluster, and the existing SQL Server installation, it can add unnecessary complication and troubleshooting to your migration process. Unless you have a compelling reason to preserve the existing Windows installation, it is simpler just to reinstall Windows.

Following the procedures at Quickly Setup SQL Server AlwaysOn Failover Cluster Instance (FCI) in Hyper-V, deploy FCI2 using the specifications in the table below. The steps in the blog post can be applied to both physical and Hyper-V environments.

Due to the reasons described previously, make sure you use the steps following the table to join machines to Cluster3.

WSFC Cluster Name	Cluster3*
SQL Server Version	SQL Server 2012
FCI Name	FCI2
Instance Name	LIVE_INST2
Machine Names	REMOTE1 REMOTE2
File Server Name	FILE2
Cluster Service IP Address	10.2.2.222
SQL Service Account	CORP\SQLSvc2
SQL Agent Account	CORP\SQLAgent2

*Instead of recreating Cluster2, you join the machines to the new cluster Cluster3. Follow the instructions below:

When you initialize the shared disks, do not create a new iSCSI target for the quorum disk because the existing Cluster3 cluster already has a quorum disk. Instead, for the iSCSI target of the existing quorum disk, add the IP addresses of REMOTE1 and REMOTE2 to the iSCSI Initiators Identifiers page.
When you configure REMOTE1, use the following steps instead of the ones in the Configure [MACHINE_NAME1] section:
1. Log in as CORP\Install.
2. Join the machine to the Cluster3 cluster with the following PowerShell command.
  
  Import-Module FailoverClusters
  
  Get-Cluster Cluster3 | Add-ClusterNode REMOTE1,REMOTE2
  
  Move-ClusterGroup "Available Storage" -Node REMOTE1
  
  Get-ClusterAvailableDisk | Add-ClusterDisk
  
  Test-Cluster
  
  After the Add-ClusterNode command runs, ignore the errors for the three missing SQL Server DLLs. These DLLs are added when you install FCI2 on the nodes.
  
  Note: The Move-ClusterGroup command moves the Available Storage resource group to REMOTE1. This resource group holds all newly added shared storage. Unless either REMOTE1 or REMOTE2 owns this resource group, you cannot bring the shared disk online after you add it to the cluster because it cannot be brought online on any other node in the cluster.
3. Set the appropriate owners of the existing clustered service SQL Server (TEMP_INST), including all the service resources, with the following PowerShell commands. Setting the proper ownership ensures that FCI3 does not attempt to failover to REMOTE1 or REMOTE2, which cannot host the TEMP_INST SQL Server instance.
  
  Get-ClusterGroup "SQL Server (TEMP_INST)" | Set-ClusterOwnerNode -Owners TEMP1,TEMP2
  
  Get-ClusterGroup "SQL Server (TEMP_INST)" | Get-ClusterResource | Set-ClusterOwnerNode -Owners TEMP1,TEMP2
4. Remove the quorum votes for the remote nodes. Setting the proper node votes ensures that the WSFC cluster correctly determines the quorum health and makes proper decisions whether to fail over or take the cluster offline during a failure.
  
  (Get-ClusterNode "REMOTE1").NodeWeight = 0
  
  (Get-ClusterNode "REMOTE2").NodeWeight = 0
  
  Your Cluster3 quorum should now look as follows:
  - TEMP1: 1 vote
  - TEMP2: 1 vote
  - REMOTE1: 0 vote
  - REMOTE2: 0 vote
  - Quorum disk: 1 vote
  IMPORTANT: You should reconsider your quorum model carefully to maintain optimal WSFC quorum health both when and after adding the nodes. For more information, see the WSFC Quorum Modes and Voting Configuration section in Microsoft SQL Server AlwaysOn Solutions Guide for High Availability and Disaster Recovery.
5. Change to the directory that contains the setup.exe file for SQL Server 2012, then install FCI2 using the following command. Note that even though you initialize the data disk to use drive letter Z based on the blog instructions, the WSFC service changes it to a different drive letter when adding to because that drive letter is already used in the cluster (by FCI3). You must use the new drive letter that is assigned to your shared disk.
  
  .\Setup.exe /QS /ACTION=InstallFailoverCluster /IACCEPTSQLSERVERLICENSETERMS /FEATURES=SQL, ADV_SSMS /INSTANCENAME=LIVE_INST2 /SQMREPORTING=0 /FAILOVERCLUSTERDISKS="Cluster Disk 3" /FAILOVERCLUSTERIPADDRESSES="IPv4;10.2.2.222;Cluster Network 2;255.255.255.0" /FAILOVERCLUSTERNETWORKNAME=FCI2 /AGTSVCACCOUNT="CORP\SQLAgent2" /AGTSVCPASSWORD=[PASSWORD] /INSTALLSQLDATADIR="[SHAREDDISK_DRIVELETTER]:\" /SQLSVCACCOUNT="CORP\SQLSvc2" /SQLSVCPASSWORD=[PASSWORD] /SQLSYSADMINACCOUNTS="CORP\Install" "CORP\DBAdmin"
6. Run the following commands to open the firewall for SQL Server connectivity.
  
  netsh advfirewall firewall add rule name='SQL Server Browser (UDP-In 1434)' dir=in action=allow protocol=UDP localport=1434 profile=domain
  
  netsh advfirewall firewall add rule name='SQL Server (TCP-In)' program='C:\Program Files\Microsoft SQL Server\MSSQL11.LIVE_INST2\MSSQL\Binn\sqlservr.exe' dir=in action=allow protocol=TCP profile=domain
7. Run the following commands to set the proper ownership for the clustered service, including all the service resources.
  
  Get-ClusterGroup "SQL Server (TEMP_INST)" | Set-ClusterOwnerNode -Owners TEMP1,TEMP2
  
  Get-ClusterGroup "SQL Server (TEMP_INST)" | Get-ClusterResource | Set-ClusterOwnerNode -Owners TEMP1,TEMP2
  
  Get-ClusterGroup "SQL Server (LIVE_INST2)" | Set-ClusterOwnerNode -Owners REMOTE1,REMOTE2
  
  Get-ClusterGroup "SQL Server (LIVE_INST2)" | Get-ClusterResource | Set-ClusterOwnerNode -Owners REMOTE1,REMOTE2
  
  Get-ClusterGroup "Cluster Group" | Get-ClusterResource | Set-ClusterOwnerNode -Owners TEMP1,TEMP2,REMOTE1,REMOTE2
  
  The resource group "Cluster Group" contains cluster-level resources for Cluster3, including the quorum disk. The last command ensures that that all nodes have access to the quorum disk.

Once FCI2 has been reconfigured, it should look similar to Figure 6 when viewed in Failover Cluster Manager. Note that Cluster3 now has two cluster networks and two clustered services.

Figure 6 FCI2 after Reconfiguration as Shown in Failover Cluster Manager

Step 4: Make FCI3 the New Primary FCI and Create Availability Group

In this section, you will perform a failover to make FCI3 the new primary partner and stop database mirroring. Then, you will establish an availability group with the temporary FCI as the primary replica and the remote FCI as the secondary replica. Ultimately, this availability group becomes a permanent fixture in the post-migration solution.

Enable AlwaysOn Availability Groups on FCI3 and FCI2

Role: Database Administrator

First, you enable the AlwaysOn Availability Groups feature on FCI3 and FCI2. You will perform this step now so that you do not have to restart FCI3 after it becomes the primary FCI. To do this, open a SQL Server PowerShell window from SSMS and run the following commands:

Enable-SqlAlwaysOn -Path SQLSERVER:\SQL\FCI3\TEMP_INST -Force

Enable-SqlAlwaysOn -Path SQLSERVER:\SQL\FCI2\LIVE_INST2 -Force

Create Availability Group on FCI3

Role: Database Administrator

Next, you will perform the database mirroring failover to FCI3 and configure the availability group that you will use for the SQL Server AlwaysOn solution.

IMPORTANT: Your client application will experience downtime in this part of the migration process. The duration of the downtime depends on your efficiency, the database size, the speed of file transfer, and other factors.

On FCI1\LIVE_INST, perform a manual database mirroring failover for both DB1 and DB2, from FCI1 to FCI3.

IMPORTANT: This is the beginning of downtime. The console application on CLIENT should begin to show Error:... for write access failures.
For client access, connect to FCI3\TEMP_INST in SSMS, and then add CORP\SQLClient as a login and give it the following database memberships for both DB1 and DB2:
- db_datareader
- db_datawriter
On FCI3\TEMP_INST, remove the existing database mirroring sessions for both DB1 and DB2.
On FCI2\LIVE_INST2, prepare the secondary DB1 and DB2 databases for the availability group you will create, by taking both full and log backups of the databases from FCI3\TEMP_INST and restoring them on FCI2\LIVE_INST2. When restoring the databases, make sure that the WITH NO RECOVERY option is used.
On FCI3\TEMP_INST, right-click AlwaysOn High Availability and click New Availability Group Wizard.
In the New Availability Group Wizard, click Next.
In the Availability group name box, type AG1, then click Next.
Select DB1 and DB2, then click Next.
In the Replicas tab, click Add Replica.
In the Server name box, type FCI2\LIVE_INST2, then click Connect.
Select Synchronous Commit (Up to 3) for FCI3\TEMP_INST.
Select Readable Secondary=Yes for both FCI3\TEMP_INST and FCI2\LIVE_INST2.
In the Backup Preferencestab, change the backup priority of the nodes as follows:
- FCI3\TEMP_INST: 50
- FCI2\LIVE_INST2: 60
In the Listener tab, select Create an availability group listener, and then specify the following settings for the listener:
- Listener DNS Name: AG1
- Port: 10000
- Network Mode: Static IP
- IPv4 Address for 10.1.1.X/24: 10.1.1.121
- IPv4 Address for 10.2.2.X/24: 10.2.2.242
Click Next.
Select Join only for data synchronization preference, and then click Next.

NOTE: While the Full option automatically prepares the secondary replicas by taking the necessary backups, it is not designed for very large databases that may exist in a Tier-1 environment. For more information on the prerequisites for AlwaysOn Availability Groups, see Prerequisites, Restrictions, and Recommendations for AlwaysOn Availability Groups (SQL Server).
Once validation finishes without errors, click Next, then click Finish.

Once the availability group configuration is finished, you can monitor the availability group health in SSMS by right-clicking AlwaysOn High Availability and selecting Show Dashboard. The view of the AlwaysOn Dashboard is shown in Figure 7.

Figure 7 AlwaysOn Dashboard after Availability Group Creation

The Failover Cluster Manager view of AG1 is shown in Figure 8. The Cluster3 cluster now contains three clustered services: two FCI services and one availability group service. The WSFC service manages aspects the resource group ownership and monitors the quorum health for the availability group. Note that other than quorum model and health management, you should avoid modifying the clustered service properties of the availability group in the Failover Cluster Manager. For example, do not perform availability group failovers using the Failover Cluster Manager. You can use the AlwaysOn Dashboard to perform this task. For more information, see DO NOT use Windows Failover Cluster Manager to perform Availability Group Failover.

Figure 8 New Availability Group as Shown in Failover Cluster Manager

At this point, CORP\SQLClient still does not have access to the databases on FCI2\LIVE_INST2. If you must fail over to FCI2\LIVE_INST2 due to a failure during or after migration, you must perform the following steps immediately after the failover so that the client applications can recover database connectivity.

Add CORP\SQLClient as a login.
Give CORP\SQLClient the following database memberships for both DB1 and DB2:
- db_datareader
- db_datawriter

Reconfigure the Client Application

Role: Application Developer

Next, you reconfigure your client application to re-establish database connectivity. In a real-life migration scenario, you can proceed with this step as soon as downtime begins so that your client applications can re-establish database connectivity as soon as the availability group is online.

If you have not already done so, close the running console application on CLIENT.
In Program.cs, locate the following line:

const string connString = @"Server=FCI1\LIVE_INST;Failover Partner=FCI2\LIVE_INST;Initial Catalog=DB1;Integrated Security=True";
Replace this line with the following code to use the availability group listener and port number you configured:

const string connString = @"Server=tcp:AG1,10000;Database=DB1;IntegratedSecurity=SSPI";
Run the application again by opening the Debug menu and clicking Start Debugging.

IMPORTANT: The console application on CLIENT should now show Success:... for write access. This is the end of downtime.

You have now created the availability group, lost and recovered client connectivity, and recovered HADR capabilities. The only remaining tasks are reconfiguring FCI1, add it to the availability group, and remove the temporary FCI. I will cover that in the next installment. Stay tuned!

How To: Migrate from Failover Cluster Instances and Database Mirroring to SQL Server AlwaysOn – Part 1

Cephas Lin — Fri, 28 Sep 2012 19:21:00 GMT

Writer: Cephas Lin (Microsoft)

Contributors: Oleg Bulanyy (Microsoft), Jimmy May (Microsoft)

This blog series is a follow-up to my whitepaper Migration Guide: Migrating to SQL Server 2012 Failover Clustering and Availability Groups from Prior Clustering and Mirroring Deployments. In this series, I will walk you through the following end-to-end migration scenario inside a test environment:

From a SQL Server 2008 R2 solution: SQL Server Failover Clustering for high availability and database mirroring for disaster recovery
To a SQL Server 2012 solution: AlwaysOn Failover Cluster Instances for high availability and AlwaysOn Availability Groups for disaster recovery

Before I go further, l want to refer you to a whitepaper AlwaysOn Architecture Guide: Building a High Availability and Disaster Recovery Solution by Using Failover Cluster Instances and Availability Groups. This is a great architectural guide for anyone who is interested about the end-to-end deployment of the AlwaysOn scenario I'm discussing in this blog.

How to Use This Blog Series

Giving direct, step-by-step migration guidance on an HADR solution is inherently dangerous because there are too many variations of SQL Server HADR scenarios in real-life production systems, and each presents unique pitfalls and require unique recommendations. Therefore, for production systems, Migration Guide: Migrating to SQL Server 2012 Failover Clustering and Availability Groups from Prior Clustering and Mirroring Deployments gives you the prescriptive guidance on the migration process without diving into the nuances. However, if you have already read that whitepaper, and still need help walking through the paces of migration in your test environment, then this blog can hopefully help you avoid many issues that can trip you up along the way. Once you can successfully migrate to SQL Server AlwaysOn in the test environment, the next natural step is to determine the tweaks you must make in order to migrate your production SQL Server deployment to SQL Server AlwaysOn.

A more severe way of saying the same thing is: DO NOT apply these migration steps blindly to your production system. To minimize the risks of your actual migration to SQL Server AlwaysOn, test these steps thoroughly against every aspect of your SQL Server deployment (I repeat, in a test environment) and incorporate all the necessary tweaks to accommodate your specific scenario.

Migration Walkthrough Overview

You will walk through the migration procedures as covered in the Migration With Intermediate Hardware section in Migration Guide: Migrating to SQL Server 2012 Failover Clustering and Availability Groups from Prior Clustering and Mirroring Deployments. The whitepaper presents a separate migration approach: Migration Without Intermediate Hardware. I suggest that you read through the two options and decide which approach is best suited for your needs.

Table 1 summarizes the solution characteristics between the starting point and the end point of the migration walkthrough.

Start	Migration Strategy	End
WSFC cluster Cluster1 on the primary site FCI1 running in Cluster1 for local high availability WSFC cluster Cluster2 on the remote site FCI2 running in Cluster2 for local high availability Database mirroring on databases DB1 and DB2 from FCI1 to FCI2 for disaster recovery	Add WSFC cluster Cluster3 to the primary site Run FCI3 as temporary FCI in Cluster3 Reconfigure FCI1 and FCI2 in Cluster3, which spans both the primary and remote sites	FCI1 on the primary site, running in Cluster3, for local high availability FCI2 on the remote site, running in Cluster3 for local high availability Availability group on DB1 and DB2, running in Cluster3, for disaster recovery FCI1 as primary replica FCI2 as secondary replica, with asynchronous commit

Table 1 Solution Characteristics Between Migration End Points

At this point, it is useful to elaborate on what exactly you will do. The migration procedures are divided into the following sections. Step 0 shows you how to set up the pre-migration solution, and Step 1 is the beginning of the migration steps.

Step 0: Set up Base Configuration as the Starting Point (Part 1)
Step 1: Deploy FCI3 as Intermediate FCI (Part 2)
Step 2: Redirect Database Mirroring to FCI3 (Part 2)
Step 3: Reconfigure FCI2 with SQL Server 2012 (Part 2)
Step 4: Make FCI3 the New Primary FCI and Create Availability Group (Part 2)
Step 5: Reconfigure FCI1 and Join It to Availability Group (Part 3)
Step 6: Remove Temporary FCI (Part 3)

If you build the base configuration I prescribe here, you will find a clear path to success in the migration procedure. Once you know what success looks like using the prescribed base configuration, you can determine the necessary deviations to address the needs of your HADR solution. You can instead use your existing HADR solution as the base configuration, but just realize that I cannot guarantee that my migration steps will work as-is with your HADR solution. In all likelihood, you will need to tweaks as you follow along. Make sure that you test the migration steps thoroughly before attempting the same steps on your production environment.

Simplifications

Because the migration walkthrough is designed for a test environment, I oversimplify some important aspects of an FCI deployment. If you are keen, you will realize that I don't follow three industry best practices:

Hyper-V In earlier testing cycles of a migration deployment, Hyper-V makes a convenient technology choice to quickly create and destroy SQL Server installations. In most production mission-critical applications, it does not perform as well as physical server blades.
SAN Storage Production systems that utilize FCI for high availability require the performance and data security offered by a full storage area network (SAN) solution with fibre channel. Instead, the walkthrough asks you to use iSCSI Software Target to simulate SAN storage. You should fully understand that iSCSI is not practical for most real-life tier-1 environments.
Network Redundancy Hardware redundancy is crucial on the physical network for a production environment to circumvent outages due to network anomalies. You are not asked to abide by this rule in the walkthrough, but you are advised to follow best practices in your production environment.

The walkthrough steps are the same whether you use physical hardware or simulate them in Hyper-V. Moreover, if you have access to SAN hardware and redundant network cards for your test environment, you are welcome to follow the steps using them instead of accepting the simplifications outlined above. As an alternative, you can introduce the hardware after you have successfully performed the migration steps without them.

Success Criteria

Maintain separation of roles, where applicable (domain administrator, database administrator, application developer)
Minimize downtime during migration
During migration, approximate the HADR capabilities of the pre-migration solution

Prerequisites

To implement the base configuration described in this whitepaper, you need following hardware and/or software. You can instead use your existing HADR solution as the base configuration as long as the solution characteristics are the same as the characteristics outlined in the Start column of Table 1.

Hardware to implement the network topology in Figure 1, including servers, switches, and so on. You can also implement the network topology virtually using Hyper-V.
Windows Server 2008 R2
iSCSI Software Target 3.3
SQL Server 2008 R2
Visual Studio
Windows 7

To perform the migration, you need the following additional hardware and/or software:

Two server machines, physical or virtual, to use as intermediary hardware
SQL Server 2012
KB 976097
KB 2494036

Step 0: Set up Base Configuration as the Starting Point

Finally, we are ready to start!

First, you implement the network topology shown in the diagram below and the SQL Server configuration outlined in the Start column of Table 1. The two subnets simulate the two sites in Contoso Corporation. The client machine will simulate write access to the SQL Server instances during the migration process. If you intend to use your existing HADR solution to test the migration steps, skip the rest of the steps in this blog and see Part 2.

Figure 1 Network Topology for Base Configuration

The walkthrough steps will focus on the software configuration on the server nodes and not on the implementation of the network topology.

Set up the Domain Controller and Accounts

Role: Domain Administrator

Set up the domain controller using the following steps:

Set up the domain controller (DC) with the domain specifications as shown in the table below.

Domain Name	CORP
FQDN	corp.contoso.com

Create the domain user accounts as shown in the table below:

Domain Administrator	CORP\Administrator
SQL Installation	CORP\Install
Database Administrator	CORP\DBAdmin
Client Application	CORP\SQLClient
SQL Service Accounts	CORP\SQLSvc1 CORP\SQLSvc2 CORP\SQLSvc3 CORP\SQLAgent1 CORP\SQLAgent2 CORP\SQLAgent3

Deploy SQL Server 2008 R2 FCIs

Role: Database Administrator

Following the procedures at Quickly Setup SQL Server AlwaysOn Failover Cluster Instance (FCI) in Hyper-V, deploy the FCIs for the primary site and for the remote site using the specifications in the table below. The steps in the blog post can be applied to both physical and Hyper-V environments.

FCI Name	FCI1	FCI2
WSFC Cluster Name	Cluster1	Cluster2
SQL Server Version	SQL Server 2008 R2	SQL Server 2008 R2
FCI Network Name	FCI1	FCI2
Instance Name	LIVE_INST	LIVE_INST
Machine Names	PRIMARY1 PRIMARY2	REMOTE1 REMOTE2
File Server Name	FILE1	FILE2
Cluster Service IP Address	10.1.1.111	10.2.2.222
SQL Service Account	CORP\SQLSvc1	CORP\SQLSvc2
SQL Agent Account	CORP\SQLAgent1	CORP\SQLAgent2

The steps given in the blog post shows you how to setup your FCIs with the Node and Disk Majority quorum model. This quorum model preserves the high availability of your FCI in a single-node failure. Figure 2 and Figure 3 show the details of the two FCIs you created, as viewed in Failover Cluster Manager.

Figure 2 FCI1 in Base Configuration as Shown in Failover Cluster Manager

Figure 3 FCI2 in Base Configuration as Shown in Failover Cluster Manager

Create Databases and Configure Database Mirroring

Role: Database Administrator

Perform the following steps to create the databases and configure database mirroring sessions.

In SQL Server Management Studio (SSMS), connect to FCI1\LIVE_INST and create two databases, named DB1 and DB2.
Create a table in both DB1 and DB2, called, table1, using the following script:

CREATE TABLE dbo.table1(col1 nchar(10), col2 nchar(10))
Prepare the mirror databases for DB1 and DB2 in FCI2\LIVE_INST.
Establish the database mirroring sessions on the two databases using the specification shown in the table below.

Principal Role	FCI1\LIVE_INST
Principal Service Account	CORP\SQLSvc1
Principal Endpoint	TCP://fci1.corp.contoso.local:5022
Mirror Role	FCI2\LIVE_INST
Mirror Service Account	CORP\SQLSvc2
Mirror Endpoint	TCP://fci2.corp.contoso.local:5022
Operating Mode	High performance (asynchronous)

Figure 4 shows the view in the Mirroring tab of the Database Properties dialog after you have successfully set up database mirroring for DB1.

Figure 4 Database Properties View for Database Mirroring of DB1

Configure Access for the Client Application

Role: Database Administrator

You next set up a client workload to test the HADR capabilities of your HADR solution. First, you must make sure that the account used for client access (in this case, CORP\SQLClient) can access both the principal and mirror databases in the database mirroring configuration.

Perform the following steps in SSMS:

In FCI1\LIVE_INST, add CORP\SQLClient as a login, and give it the following database memberships for both DB1 and DB2:
- db_datareader
- db_datawriter
Perform a manual database mirroring failover for both DB1 and DB2 to FCI2\LIVE_INST.
In FCI2\LIVE_INST, add CORP\SQLClient as a login, and give it the following database memberships for both DB1 and DB2:
- db_datareader
- db_datawriter
Perform a manual database mirroring failover back to FCI1\LIVE_INST.

You will use CORP\SQLClient to connect to the databases from the client application. If a failure happens during your migration process, you have ensured that clients have access to FCI2\LIVE_INST.

Role: Application Developer

You are now ready to simulate the client workload. On CLIENT, perform the following steps to run the client application.

Install Windows 7.
Join the machine to the corp.contoso.com domain using the CORP\Administrator account.
Logout and login as CORP\Install.
Install SQL Server 2008 R2 Management Studio.
Start SSMS and connect to FCI1\LIVE_INST and FCI2\LIVE_INST to verify that the FCIs can accept remote connections.
Install Visual Studio.
Logout and login as CORP\SQLClient.
Create a console application (named ConsoleApplication1 by default), and then replace the code in Program.cs with the code below. This code continually attempts to connect to DB1 and insert a record. Each connection attempt has three retries and a 5-second interval between each retry. The connection logic is taken from Implementing Application Failover with Database Mirroring.

using System;

using System.Data;

using System.Data.SqlClient;

using System.Threading;

namespace ConsoleApplication1

{

  class Program

{

const string connString = @"Server=FCI1\LIVE_INST;Failover Partner=FCI2\LIVE_INST;Initial Catalog=DB1;Integrated Security=True";

static bool ExecuteSQLWithRetry_NoResults(string strConn, string strCmd, int iRetryInterval, int iMaxRetries)

{

SqlConnection conn = null;

      SqlCommand cmd = null;

for (int iRetryCount = 0; iRetryCount < iMaxRetries; iRetryCount++)

{

    try

      {

          conn = new SqlConnection(strConn);

          conn.Open();

          if (null != conn && ConnectionState.Open == conn.State)

          {

          cmd = new SqlCommand(strCmd, conn);

            cmd.ExecuteNonQuery();

            return true;

          }

        }

        catch (Exception ex) { }

        finally

        {

          try

          {

            if (null != cmd)

            cmd.Dispose();

            if (null != conn && ConnectionState.Closed != conn.State)

            conn.Close();

          }

          catch (Exception ex) { }

        }

        if (iRetryCount < iMaxRetries)

        Thread.Sleep(iRetryInterval * 1000);

      }

      return false;

    }

    static void Main(string[] args)

    {

    while (true)

      {

      Random rand = new Random();

        string cmdString = "Insert into table1 values (N'" + rand.Next() + "',N'" + rand.Next() + "')";

        if (ExecuteSQLWithRetry_NoResults(connString, cmdString, 5, 3))

            Console.WriteLine("Success: " + cmdString);

        else Console.WriteLine("Error: " + cmdString);

      }

    }

}

}
Run the application by opening the Debug menu and clicking Start Debugging.

You have now finished setting up the base configuration and are ready to start the migration scenario to the SQL Server AlwaysOn solution in SQL Server 2012.

But wait, this blog has gotten too long already! In the next installment, you will actually start the migration steps. Stay tuned!

Machine	SQL Service Account	SQL Agent Account
PRIMARY1	SQLSvc1	SQLAgent1
PRIMARY2	SQLSvc2	SQLAgent2
WITNESS	SQLSvc3	SQLAgent3
REMOTE1	SQLSvc4	SQLAgent4