How data is mirrored

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]



Fig 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



Fig 1.2

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



Figure 1.3

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:

0 = Suspended

1 = Disconnected from the other partner

2 = Synchronizing

3 = Pending Failover

4 = Synchronized

5 = The partners are not synchronized. Failover is not
possible now.

6 = The partners are synchronized. Failover is potentially
possible.

NULL = Database is inaccessible or is not mirrored.

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



Fig 1.4

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



Fig 1.5

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



Figure 1.6

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

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles