How data is mirrored

July 15, 2009

In Part 1 of this series we saw a step by step illustration of how to setup Database mirroring. This article examines how data is mirrored from the Principal server to the mirrored server.

Let's create a table "Test" on the Principal database DB1 using the transact SQL below.

USE [DB1]
GO
/****** Object:  Table [dbo].[test]    
	Script Date: 07/03/2009 11:27:01 ******/
IF  EXISTS (SELECT * FROM sys.objects 
	WHERE object_id = OBJECT_ID(N'[dbo].[test]') 
	AND type in (N'U'))
DROP TABLE [dbo].[test]
GO
USE [DB1]
GO
/****** Object:  Table [dbo].[test]    
	Script Date: 07/03/2009 11:26:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[test](
	[id] [int] NULL,
	[name] [varchar](100) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO

Now let's add some data to the table using the following transact SQL.

insert into test values 
(1,'Test1'),
(2,'Test2'),
(3,'Test3'),
(4,'Test4'),
(5,'Test5')

At the mirrored server, you will notice that the mirrored database is in restoring state. [Refer Figure 1.0]

If you try to access the "test" table on the mirrored server, you get following error message. [Refer Fig 1.1]

select * from DB1.dbo.test

The only way you can query the table on the mirrored database is by creating a database snapshot.

Create the database snapshot, DB1_Snapshot, using the following transact SQL query.

USE [master]
GO

/****** Object:  Database [DB1_Snapshot]    Script Date: 07/03/2009 11:39:51 ******/
IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'DB1_Snapshot')
DROP DATABASE [DB1_Snapshot]
GO

CREATE DATABASE DB1_Snapshot
ON (NAME = 'DB1', 
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2K8\MSSQL\DATA\DB1_snapshot.snp')
AS SNAPSHOT OF DB1

GO

Now let's query the "Test" table from the database snapshot using the following transact SQL statement. [Refer Figure 1.2]

select * from DB1_snapshot.dbo.test

If you want to check the tables in the mirrored database often, you can automate the creation of database snapshots for every hour or so. Follow the steps from " SQL Server 2005 – Automating Creation of Database Snapshots."

The next thing we need to know is if the mirroring from the principal server is synchronized and running successfully. For that, we could use catalog views to check the database mirroring configuration. [Refer Figure 1.3]

Execute the following transact SQL as shown below on the principal server.

select dm.mirroring_partner_instance, 
dm.mirroring_role,
dm.mirroring_state, 
dm.mirroring_witness_state,dm.mirroring_safety_level, 
dm.mirroring_witness_name from 
sys.database_mirroring dm 
join sys.databases d on (dm.database_id=d.database_id) 
where (d.name=N'DB1') and mirroring_guid is not null

The column value that you should watch for is mirroring_state. The value 4 represents that the mirroring is synchronized. The other possible values for this column are:

Note: The column value of the column mirroring_role will tell you if the server is the mirrored server or the principal server. If the value is 1, then it is the principal server; if the value is 2, then it is the mirrored server. If the value is NULL, then it means that the database is inaccessible or is not mirrored.

In order to check the status of the database mirroring we can use the store procedure "sp_dbmmonitorresults" in MSDB database.

Execute the following transact SQL as shown below on the principal server. [Refer Figure 1.4]

use msdb
go
exec sys.sp_dbmmonitorresults @database_name=N'DB1', @mode = 0, @update_table = 1
go

Note: The average_delay, time_recorded, time_behind tells you the rate of mirroring and delays. The column value of the column "role" tells you if the server is the mirrored server or the principal server. If the value is 1, the it is the principal server; if the value is 2, then it is the mirrored server.

Now let's execute the same set of transact SQL that we executed on the principal server on the witness server and see the results.

Execute the following transact SQL as shown below on the principal server. [Refer Fig 1.5]

select dm.mirroring_partner_instance, 
dm.mirroring_role,
dm.mirroring_state, 
dm.mirroring_witness_state,dm.mirroring_safety_level, 
dm.mirroring_witness_name from 
sys.database_mirroring dm 
join sys.databases d on (dm.database_id=d.database_id) 
where (d.name=N'DB1') and mirroring_guid is not null

Execute the following transact SQL as shown below on the principal server. [Refer Figure 1.6]

use msdb
go
exec sys.sp_dbmmonitorresults @database_name=N'DB1', @mode = 0, @update_table = 1
go

The average_delay, time_recorded, time_behind tells you the rate of mirroring and delays.

Note: The average_delay, time_recorded, time_behind tells you the rate of mirroring and delays.

Conclusion

In this article, we have seen how data is mirrored from the principal server to the mirrored server.

» See All Articles by Columnist MAK