SQL Server and Other Topics


I got my start with SQL Server in 1997 with SQL Server 6.5 at BellSouth Cellular. From that point on I've had the opportunity to work on Versions 6.0 to 2014. I specialize in Performance Tuning, High Availability and SQL Development. Contact me through this site or through my Twitter handle @SQLDiver

AlwaysOn Availability Groups Migration - Case Study Part 2

Part 1 > Part 2 > Part 3


In part 1 I started building the framework for moving from SQL Server Database Mirroring (mirroring) to SQL Server AlwaysOn Availability Groups (AGs).

Inventory what you have in all environments, what are you going to need to migrate to the new server (remember this is about moving to new (rebuilt) servers. There are many blogs about how to move mail configuration, sql jobs, users, alerts, operators, database and server configuration, etc., etc.  Don't forget anything. Get the team together to brainstorm all of the things that must be moved and configured.

Complete Inventory

Create a design document. This design document should cover all aspects of what the environment will "look" like after the migration. It is critical to think through the licensing, the availability replica modes, failover  policy, etc. It gives you a chance to think deeply about what needs to be done to configure all aspects of the migration. Know the roadblocks, the requirements for an Availability Group and where AGs will not work. Here are a few:

  1. DTC - Don't do AGs if you're application uses DTC.
  2. Cross Database Transactions - Don't do it! If you have a failover and your app is doing a cross database transaction, the primary could come back in suspect mode. This arfticle will help confirm if you're using cross database transactions.
  3. Domain - A domain is required for the WSFC (that changes in Windows Server vNext).
  4. Cluster - The AG replicas must all be in the same cluster.

 There are more issues you will encounter with Ags... see here.

Determine what the business SLAs are for Recovery Time Objective (RTO) and Recovery Point Objective (RPO) and document in the design document.  You want to be able to tell management how much downtime to expect.

Use Visio to create diagrams, for your own benefit as much as everyone Else's. These diagrams will help keep your head straight with what your design is. It will allow you to plan the number of licenses for management, and it will be your "evidence" after your done when management says, you didn't tell us... oh yes I did, a a matter of fact it is right here in the design document that you approved. Ok, so there is another critical element. Make sure management approves the design document (put a spot for signatures on the cover page), it will prompt them to sign it. Don't deliver the design document until you have tested the migration in a lower environment first. It will expose gotchas that you didn't think about (hopefully you tested prior to getting buy in from management). 

Approved Design

Now that we have the design approved by the client (management), its time to work on the most important step...planning. Without a proper plan not only will you not succeed, but you will probably fail horribly. Let's put that down as a critical component.

Complete Plan

The plan should be a step by step plan with dependencies,  start time of each step (as a calculation based upon the previous step duration), estimate duration step, step status (Completed, In Progress, Cancelled, etc.), responsible party, day of week (calculated from date time column) and step number for quick reference. I usually put my plans in Excel and have a conditional format for the row based upon the status.

This plan should include all steps include server builds, network, stress test, benchmark testing, etc. If you want to be a plan Nazi include the regression testing, etc. Hopefully your company or client has an experienced  project manager for the project. It will take a little longer, but if you have a good project manager your project will have a complete project plan avoiding gotchas at a later date.


I know this is a lot of work, but it will save you in the end. Your job is to eliminate all possibility of accidental downtime and minimize the length of downtime as well as have a highly available environment that works for your company or client. Please take your time and do the work.

Comments are closed.
Showing 1 Comment
Avatar  [Pingback] 4 years ago

Pingback from sqlserverassociates.com

SQL Server and Other Topics | AlwaysOn Availability Groups Migrathttp://sqlserverassociates.com/Blog/PostId/10/alwayson-availability-groups