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 series is a follow-up to my whitepaper Migration Guide: Migrating to SQL Server 2012 Failover Clustering and Availability Groups from ... . In this series, I will walk you through the following end-to-end migration scenario inside a test environment:
Before I go further, l want to refer you to a whitepaper AlwaysOn Architecture Guide: Building a High Availability and Disaster Recovery Solution by Usi... . 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.
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 ... 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.
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 ... . 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 |
|
|
|
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.
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.
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. If you choose to use your specific HADR solution as the starting point, you can likewise use your own client applications if you like.
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:
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.
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, I have included a few common migration criteria.
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.
To perform the migration, you need the following additional hardware and/or software:
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.
Role: Domain Administrator
Set up the domain controller using the following steps:
Domain Name |
CORP |
FQDN |
corp.contoso.com |
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 |
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
Role: Database Administrator
Perform the following steps to create the databases and configure database mirroring sessions.
CREATE TABLE dbo.table1(col1 nchar(10), col2 nchar(10))
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
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:
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.
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);
}
}
}
}
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!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.