Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Jul 15, 2009

How data is mirrored

By Muthusamy Anantha Kumar aka The MAK

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



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM