|

SQL Server and Other Topics

rss

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


Overview

At a client who is a credit card payment provider, with a 5 nines (99.999) availability requirement,  I've been asked to move a set of databases from one physical server to another with the least amount of down time possible. In addition to this requirement they have a Recovery Time Objective (RTO) of 15 seconds. Unfortunately this isn't possible with the environment they have. Good news is they know this and accept the downtime.

This is Phase One of moving to Microsoft SQL Server AlwaysOn Availability Groups.

 

Current State

  1. Two nodes of a Windows Server Failover Clustering (WSFC) cluster on Windows 2008 R2 Enterprise and SQL Server 2008 R2 Enterprise FCI in New York Data Center.
  2. Two nodes of a Windows Server Failover Clustering (WSFC) cluster on Windows 2008 R2 Enterprise and SQL Server 2008 R2 Enterprise FCI in Delaware Data Center.
  3. The databases on the WSFC cluster in NY are configured as a SQL Server Database Mirroring principal mirroring to the DE data center for high availability.

This is already a complex environment in that have the mirroring configured as synchronous over the WAN between NY and DE. The move is to a second set of servers already being used by a different set of application databases.

Future State

 One multi-subnet Windows 2012 R2 WSFC cluster across two data centers. The nodes in NY will serve as WSFC FCI active/passive failover partners and the nodes in DE will serve as WSFC FCI active/passive failover partners.

Mirroring will be configured with the principal homed on the active node in NY and the mirror on the active node in DE.

 

Requirements

 Migrate the production databases to an upgraded set of servers with a downtime goal of zero.

 Strategy for Migration

In order to minimize down time as well as ensure a minimal RTO for the duration of the migration, the following strategy will be used.
Log shipping will be implemented to the two instances that will act as principal and mirror. This will allow the database to maintain the RTO while the backups are copied to the DR site in DE.

During the cut over, a tail log backup will be completed, putting the database in no recovery to prevent additional transactions from occurring, and the backup will be applied to the two log ship secondary databases, the log ship secondary databases that will act as principal will be recovered (brought online).
A log backup will be taken from the databases that will be the principal and restored to the secondary databases that will act as the mirror then immediately implement mirroring between the new principal and mirror.

 

 The process will be scripted to prevent missing steps, human error, and complete the process in a way to minimize downtime to a bare minimum.

 Testing Configuration

Because of the necessity to avoid downtime this will have to be tested thoroughly. The risk is very high for this process as minutes down would be a very high loss of revenue.The servers and databases we will use for our testing will be as follows:

  1. QA-MSSQL03-NY - SQL Server 2008 R2
  2. Q-MSSQL01-LB - SQL Server 2014
  3. Q-MSSQL11-LB - SQL Server 2014
  4. Q-MSSQL12-LB - SQL Server 2014
  5. Q-MSSQL13-LB - SQL Server 2014

Databases to migrate TST_SWITCH and TST_TRANSACTIONS

Mirror Configuration

We want to setup mirroring on our test databases to simulate the production starting point. In production the databases are in pairs and will be move two at a time. We'll just use the wizard to configure mirroring. This assumes you have configured mirroring previously and understand the steps. Prior to this step, ensure you have mirror endpoints configured for each server being used.