|

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


Part I | Part II

Part II of  Move What? No downtime? What?

 Log Shipping Configuration

The strategy of log shipping to the two instances that will be the principal and mirror, will be configured in this part of the blog article. This step, since it is for testing can be completed through the wizard, but I recommend doing it exactly as it will be done in production so you get the hang of the backup, copy  and restore process since it is across a slow WAN that could take many hours or possibly even days.

 

Configure Folders for Log Shipping Backups

In order to avoid as much confusion as possible, create a folder for each database specific to the log shipping process. If you don't have folders setup you're going to run into issues. 

On each server create an empty folder for the log shipping and a folder for each database inside the log shipping folder.

 

These folders will be the home for the database log backups during the migration process. The current log backup jobs will need to be disabled during this process to ensure log backups are not missed. The log shipping restore jobs will restore the log backups from this location across the WAN.

For the log shipping full backup, create a folder for each database on both servers where the secondaries will reside. The backups will be copied to each location and restored. This is to simulate a large backup file backup, copy and restore across a slow WAN.

Q-MSSQL01-LB

 

Q-MSSQL11-LB

 

Naming Conventions of Logical File Name

So future scripts work as expected (based on a standard naming convention), make sure your logical file names are named with the same naming convention. The following script will help with this step. Replace <<DatabaseName>> with the name of your database. This will create a script you can run against the database. You'll have to be creative if you have more than one data file. Remember this is just for testing.

 

SET NOCOUNT ON

SELECT

'ALTER DATABASE  ' + DB_NAME(database_id) + '

MODIFY FILE (NAME = ' + name +  ', NEWNAME = '+ DB_NAME(database_id) + ') '

FROM sys.master_files

WHERE database_id = db_id('<<DatabaseName>>')

AND type = 0

 

SELECT

'ALTER DATABASE ' + DB_NAME(database_id) + '

MODIFY FILE (NAME = ' + name +  ', NEWNAME = '+ DB_NAME(database_id) + '_log) '

FROM sys.master_files

WHERE database_id = db_id('<<DatabaseName>>')

AND type = 1

 

This is a good spot to take a break. The next step will involve SQLCMD mode.


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.