Database Mirroring Using T-SQL

In Part 1 of this article series, was a step by step illustration of how to setup database mirroring. Part 2 discussed how data is mirrored from the principal server to the mirrored server and how to monitor them. This installment examines how to create database mirroring between the principal server and mirrored server using a script.

A script comes in handy when you want to recreate the entire data mirroring setup and is very useful in automation.

Let's assume that we want to mirror a database, DB2, from the SQL Server instance PowerPC\SQL2008 to DB2 on the SQL Server instance PowerPC\SQL2k8.

Now let's create a database, DB2, on the principal server PowerPC\SQL2008 using the following transact SQL statement. In this part of article series, we are going to discuss database mirroring with synchronous mode and with no witness server.

USE [master]
GO

/****** Object:  Database [DB2]    
	Script Date: 07/20/2009 21:10:33 ******/
IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'DB2')
DROP DATABASE [DB2]
GO


USE [master]
GO

/****** Object:  Database [DB2]    
   Script Date: 07/20/2009 21:10:13 ******/
CREATE DATABASE [DB2] ON  PRIMARY 
( NAME = N'DB2', FILENAME = 
   N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\
      MSSQL\DATA\DB2.mdf' , 
   SIZE = 1280KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'DB2_log', FILENAME = N'C:\Program Files\
      Microsoft SQL Server\MSSQL10.SQL2008\
      MSSQL\DATA\DB2_log.LDF' , 
   SIZE = 504KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
ALTER database DB2 set recovery full
GO

Now let's create the DB2 database on the PowerPC\SQL2K8 mirrored server using the following transact SQL statement.

USE [master]
GO

/****** Object:  Database [DB2]    
   Script Date: 07/20/2009 21:10:33 ******/
IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'DB2')
DROP DATABASE [DB2]
GO


USE [master]
GO

/****** Object:  Database [DB2]    
   Script Date: 07/20/2009 21:10:13 ******/
CREATE DATABASE [DB2] ON  PRIMARY 
( NAME = N'DB2', FILENAME = N'C:\Program Files\
      Microsoft SQL Server\MSSQL10.SQL2K8\MSSQL\DATA\DB2.mdf' , 
   SIZE = 1280KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'DB2_log', FILENAME = N'C:\Program Files\
      Microsoft SQL Server\MSSQL10.SQL2K8\MSSQL\DATA\DB2_log.LDF' , 
   SIZE = 504KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

Note: Instead of creating the DB2 database on the mirrored server, you could restore the database backup and tranlog backup using with replace option to create and restore at the same time.

Now let's backup the database and transaction on the principal server using the following transact SQL statement.

use master
go
Backup database DB2 to disk ='C:\Backups\DB2.Bak' with init
go
Backup log DB2 to disk ='C:\Backups\DB2.trn' with init
go

Restore the database on the target server using the following transact SQL statement.

use master
go
restore database DB2 from disk ='C:\Backups\DB2.Bak' with norecovery, 
replace,
move 'DB2' to 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2K8\MSSQL\DATA\DB2.mdf',
move 'DB2_log' to 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2K8\MSSQL\DATA\DB2_log.ldf'
go
restore log DB2 from disk ='C:\Backups\DB2.trn' with norecovery, replace,
move 'DB2' to 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2K8\MSSQL\DATA\DB2.mdf',
move 'DB2_log' to 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2K8\MSSQL\DATA\DB2_log.ldf'
go

The next step is to create ENDPOINTS for mirroring on the principal server. Execute the following statement on the principal server, PowerPc\SQL2008.

CREATE ENDPOINT DB2_Mirroring
    STATE=STARTED 
    AS TCP (LISTENER_PORT=7022) 
    FOR DATABASE_MIRRORING (ROLE=PARTNER)
GO

Note: If you have already created an endpoint for the server, you cannot create another one. You will get the following error message. In part 1 of this series we have created the endpoint called "Mirroring" listening to port number 5022.

Msg 7862, Level 16, State 1, Line 1
An endpoint of the requested type already exists.  Only one endpoint of this type is supported.  
Use ALTER ENDPOINT or DROP the existing endpoint and execute the CREATE ENDPOINT statement.
Msg 7807, Level 16, State 1, Line 1
An error ('0x800700b7') occurred while attempting to register the endpoint 'DB2_Mirroring'.

The next step is to create ENDPOINTS for mirroring on the mirrored server. Execute the following statement on the mirrored server, PowerPc\SQL2k8.

CREATE ENDPOINT DB2_Mirroring
    STATE=STARTED 
    AS TCP (LISTENER_PORT=7023) 
    FOR DATABASE_MIRRORING (ROLE=PARTNER)
GO

Note: If you have already created an endpoint for the server, you cannot create another one. You will get the following error message. In part 1 of this series we created the endpoint called "Mirroring" listening to port number 5023.

Msg 7862, Level 16, State 1, Line 1
An endpoint of the requested type already exists.  Only one endpoint of this type is supported.  
Use ALTER ENDPOINT or DROP the existing endpoint and execute the CREATE ENDPOINT statement.
Msg 7807, Level 16, State 1, Line 1
An error ('0x800700b7') occurred while attempting to register the endpoint 'DB2_Mirroring'.

Now execute the following transact sql statement on the mirrored server instance, PowerPc\SQL2008. This statement will make the initial principal server instance.

ALTER DATABASE DB2
    SET PARTNER = 'TCP://PowerPC:5022'

Execute the following transact sql statement on the mirrored server instance, PowerPc\SQL2k8. This statement will make the initial principal server instance.

ALTER DATABASE DB2
    SET PARTNER = 'TCP://PowerPC:5023'

You can view the configuration from the SQL Server Management studio by clicking the database properties of the DB2 database on the principal server as shown in Fig 1.0.

Now let's create a table in the principal server, PowerPC\SQL2008, as shown below.

USE [DB2]
GO

/****** Object:  Table [dbo].[test]    Script Date: 07/28/2009 01:44:54 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[test]') AND type in (N'U'))
DROP TABLE [dbo].[test]
GO

create table test (id int, name varchar(100))
insert into test select 1,'A'
insert into test select 2,'B'
insert into test select 3,'C'
GO

We already know that we need to create a snapshot to read the data in the table.

Let's create a database snapshot and then query the table on the database snapshot from the mirrored server, PowerPC\SQL2K8, as shown below. [Refer Figure 1.1]

CREATE DATABASE DB2_snapshot
ON (NAME = 'DB2', FILENAME = 'C:\Backups\DB2_Snapshot.SNP')
   AS SNAPSHOT OF DB2
GO
Select * from DB2_snapshot.dbo.test
GO

Conclusion

This installment has illustrated how to use a T-SQL script to create database mirroring between the principal server and the mirrored server.

» See All Articles by Columnist MAK