README Mirroring Failover (ODBC)

  • Article

This sample demonstrates the mirroring failover feature in SQL Server 2005 SP1. This sample is not supported using SQL Server 2005 Express Edition (SQL Server Express).

Languages

This sample uses Visual C++.

Prerequisites

Before running this sample, make sure the following software is installed:

  • SQL Server 2005 including Database Engine.
  • The SQL Server 2005 Database Engine samples. These samples are included with SQL Server 2005. You can download the latest version of the samples at the Microsoft SQL Server Developer Center.
  • .NET Framework SDK 2.0 or Microsoft Visual Studio 2005. You can obtain .NET Framework SDK free of charge. See Installing the .NET Framework SDK. You will need to modify the INCLUDE environment variable to include the \Microsoft SQL Server\90\SDK\Include directory (if you are using Microsoft Visual Studio 2005, you can do this in the development environment; see VC++ Directories, Projects and Solutions, Options Dialog Box for more information).

You will also need to configure a principal database mirror database for the principal database; for more information, see How to: Set Large Data (OLE DB) and Troubleshooting Database Mirroring Setup.

If you prefer, you can enable database mirroring on your machine with the following scripts. These scripts can be executed within SQL Server Management Studio or by using SQLCMD at the command prompt.

Ensure Database and Endpoints Used in This Sample Do Not Exist on the Principal Server Instance

The following script will ensure that the principal server does not have a database or endpoint name used by the sample.

-- Step 1 (on Principal)
-- Return primary server instance to a premirroring state
-- 
ALTER DATABASE HADB SET PARTNER OFF
DROP DATABASE HADB;
DROP ENDPOINT Mirroring;
EXEC sys.sp_dbmmonitordropmonitoring;

Ensure Database and Endpoints Used in This Sample Do Not Exist on the Mirror Server Instance

The following script will ensure that the mirror server instance does not have a database or endpoint name used by the sample.

-- Step 2 (on Mirror)
-- Return mirror server instance to a premirroring state
-- 
ALTER DATABASE HADB SET PARTNER OFF
RESTORE DATABASE HADB WITH RECOVERY;
DROP DATABASE HADB;
DROP ENDPOINT Mirroring;

Create Database and Database Backups

This script, which is run on the principal server instance, creates the database and database backups.

-- Step 3 (on Principal)
-- create db and backups
-- 
USE master;
GO
CREATE DATABASE HADB; 
ALTER DATABASE HADB SET RECOVERY FULL; 

-- CREATE BACKUPS
BACKUP DATABASE HADB 
    TO DISK = 'C:\HADB.bak'
    WITH FORMAT;
GO

BACKUP LOG HADB
   TO DISK = 'C:\HADB.bak';

Create the Mirror Database

This script, which is run on the server instance that will contain the mirror database, creates the mirror database from the backup created in the previous script. If the mirror server and the principal server are not on the same physical machine, you will need to copy c:\hadb.bak from the principal server machine to the mirror server machine.

-- Step 4 (on Mirror)
-- Create the mirror database
RESTORE DATABASE HADB
FROM DISK='C:\HADB.bak'
WITH NORECOVERY, FILE=1,
MOVE 'HADB' TO 'C:\HADB.mdf',
MOVE 'HADB_log' TO 'C:\HADB_log.ldf'
;

RESTORE LOG HADB
FROM DISK='C:\HADB.bak'
WITH NORECOVERY, FILE=2,
MOVE 'HADB' TO 'C:\HADB.mdf',
MOVE 'HADB_log' TO 'C:\HADB_log.ldf'
;

Create Mirror Endpoint on the Principal Server

This script, run on the principal server instance, creates a mirroring endpoint.

-- Step 5 (on Principal)
-- create mirroring endpoint on PRINCIPAL server
-- 
CREATE ENDPOINT Mirroring
STATE = STARTED
AS TCP(LISTENER_PORT = 7022)
FOR database_mirroring (ROLE = PARTNER);

Create Mirror Endpoint on the Mirror Server Instance

This script, run on the mirror server instance, creates a mirroring endpoint.

You can discover your machine name and domain name (full computer name) by looking at the properties of your computer (on Windows XP, right-click My Computer, click Properties, and look on the Computer Name tab).

-- Step 6 (on MIRROR)
-- create mirroring endpoint
-- 
CREATE ENDPOINT Mirroring
STATE = STARTED
AS TCP(LISTENER_PORT = 7023)
FOR database_mirroring (ROLE = PARTNER);

-- also on mirror, set partner to PRINCIPAL server 
--(This must be done on mirror first.)
ALTER DATABASE HADB SET PARTNER='tcp://machine_name.domain:7022';

Set Principal to the Mirror Server

This script, run on the principal server, specifies the mirror database. You may see a failure at this point if the principal server has a firewall that prevents connections on port 7022. In this case, make an exception in your firewall for this port number.

-- Step 7 (on Principal)
-- Set partner to the mirror server on port 7023
-- 
ALTER DATABASE HADB SET PARTNER='tcp://machine_name.domain:7023';

-- start collecting mirroring history:
USE master;
EXEC sys.sp_dbmmonitoraddmonitoring;

Building the Sample

To build the sample, open Failover.sln in Visual Studio (CTRL+SHIFT+O) and click Build Solution on the Build menu, or invoke MSBUILD from a command prompt on the solution.

Running the Sample

  • From Visual Studio, invoke Start Without Debugging (CTRL+F5).
  • If you built with MSBuild.exe, invoke Failover.exe.
  • Change the Principal Server and Principal Server in the user interface to your server names.
  • Click Init Table. This creates a simple table with a column used as an update counter.
  • Click Connect.
  • Click Update to update the table, and click Commit to commit the changes to your database.
  • To force manual failover, click Failover.

Remarks

You can now click Connect, Update, Commit, and Disconnect. The Count field increments each time you click Update. If you click Failover with uncommitted updates, disconnect and then reconnect you will see the update state after your last commit. When you disconnect, you'll see an error and the connection and the current transaction will be rolled back after failover happen.

An application can detect which server it connects to and the server's partner. You can do this by selecting the Track Changes check box. The advantage of doing this is that an application that updates its connection string is more likely to go to the active server directly on startup, which is a marginal gain, and can have zero long-term maintenance costs. Suppose A is mirrored by B and A fails, causing B to become the active server. Now a new server C is introduced as a mirror to B. An application requesting A as principal and B as mirror would fail to connect when B fails over to C.

Click Continuous Test and observe the counter. Now click Failover at intervals. The time to failover is displayed (look at the code to see how this is measured). Also, the code demonstrates simple high level failover logic. That is, assume the current transaction is lost, make a small number of reconnection attempts (three in the demonstration) and repeat the failed transaction, or report it to the user.

There are buttons to get a current client setting for the alias ActiveServer, and to set this alias to the current active server. If you use an alias with scripts and utilities then you can use SMO to update the alias after failover. Now utilities and scripts will connect to the new active server.

See Also

Concepts

Data Access Samples

Help and Information

Getting SQL Server 2005 Assistance