Database Mirroring in SQL Server

Recently, I delivered a SQL Server training. I explored several high availability technologies in SQL Server such as database mirroring and transaction log shipping. After exploring several technologies, I began to like database mirroring. Reason is that I’m able to quickly implement applications since I could specify database mirroring details on the connection string itself. To me, this saves a lot of my time to implement my own custom load balancer (which I have plans to implement one using C#).

Database mirroring is a technology available for increasing database availability. Database mirroring transfers transaction log records directly from one server to another and can quickly fail over to the standby server. You can code client applications to automatically redirect their connection information, and in the event of a failover, automatically connect to the standby server and database. Fast failover with minimal data loss has traditionally involved higher hardware cost and greater software complexity. Database mirroring, however, can fail over quickly with no loss of committed data, does not require proprietary hardware, and is easy to set up and manage.

The following are the software that I have installed on my virtual image for testing:
. SQL Server 2005 Developer Edition Service Pack 1 (Note: You need at least Service Pack 1)
. Windows Server 2003 Enterprise Edition Service Pack 1

I installed 3 instances of SQL Server (instance 1 = default instance, instance 2 = SQLINSTANCE2, instance 3 = SQLINSTANCE3). SQLINSTANCE2 and SQLINSTANCE3 are the instance names for the second and third instance. For testing, I used the AdventureWorks database. The default instance will be the principal, the second instance will be the mirror and the third instance will be the witness. The following is the screenshot of all the instances:

Okay. These are the steps required to implement database mirroring:

Step 1: Do this on the principal instance
–Create endpoint on principal instance and set recovery model
CREATE ENDPOINT endpoint_mirroring
    STATE = STARTED
    AS TCP ( LISTENER_PORT = 5022 )
    FOR DATABASE_MIRRORING (ROLE=PARTNER);
GO
ALTER DATABASE AdventureWorks
SET RECOVERY FULL;

After step 1, you should see the following:

Step 2: Do this on the mirror instance
–Create endpoint on mirror instance
CREATE ENDPOINT endpoint_mirroring
    STATE = STARTED
    AS TCP ( LISTENER_PORT = 5023 )
    FOR DATABASE_MIRRORING (ROLE=PARTNER);
GO

After step 2, you should see the following:

Step 3: Do this on the witness instance
–Create endpoint on witness instance
CREATE ENDPOINT endpoint_mirroring
    STATE = STARTED
    AS TCP ( LISTENER_PORT = 5024 )
    FOR DATABASE_MIRRORING (ROLE=WITNESS);
GO

After step 3, you should see the following:

Step 4: Do this on the principal instance. I’m assuming that you have created a directory called MirrorBackup prior to this step.
–Backup principal database
BACKUP DATABASE AdventureWorks
TO DISK=’C:\MirrorBackup\AWBackup.BAK’
with init;
GO

Step 5: Do this on the mirror instance
–Restore mirror database
RESTORE DATABASE AdventureWorks
FROM DISK=’C:\MirrorBackup\AWBackup.BAK’
WITH NORECOVERY,
MOVE ‘AdventureWorks_Data’ TO ‘C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\AdventureWorks_Data.mdf’,
MOVE ‘AdventureWorks_Log’ TO ‘C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\AdventureWorks_Log.ldf’;
GO

–On the mirror server set the principal server
ALTER DATABASE AdventureWorks
    SET PARTNER =
    ‘TCP://MIAMI:5022’
GO

After step 5, you should see the following:

Step 6: Do this on the principal instance
–On the principal server set the mirror server
ALTER DATABASE AdventureWorks
    SET PARTNER = ‘TCP://MIAMI:5023’
GO

–On the principal server, set the witness
ALTER DATABASE AdventureWorks
    SET WITNESS =
    ‘TCP://MIAMI:5024’
GO

After step 6, you should see the following:

Step 7: Do this on the principal instance to switch roles from principal instance to mirror instance.
— Perform manual failover
ALTER DATABASE AdventureWorks
    SET PARTNER FAILOVER
GO

After step 7, you should see the following:

That’s all. 🙂

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: