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 Aug 5, 2009

Database Mirroring Using T-SQL

By Muthusamy Anantha Kumar aka The MAK

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.

view the configuration from the SQL Server Management studio
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

create a database snapshot
Fig 1.1

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



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


















Thanks for your registration, follow us on our social networks to keep up-to-date