Database mirroring is the feature in SQL Server 2005 and SQL Server
2008 that provides a high availability solution for Databases. This feature can
be enabled and used only on a database with Full recovery models. The database
can be mirrored from one SQL Server instance to another SQL Server instance.
The source instance is called Principal server; the target instance is called
Mirrored server. We could have one more server called Witness server–we will
talk about that in later part of this article series.
The principle server sends the active transaction log record to the
mirrored server. The mirrored server applies the transaction log record one by
one in sequence.
Database mirroring can be configured in two different modes, High-Safety
mode also known as synchronous mode and High-Performance mode also known as asynchronously.
The term synchronous and asynchronous says it all.
In the synchronous mode, the principal server sends the transaction
and waits until the transaction is committed on the mirrored server. Then the
transaction is committed on the principal server.
In Asynchronous mode, the principal server sends the transaction to
the mirrored server and does not wait for the transaction on the mirrored
server to commit.
We will discuss transaction safety in detail in a future installment
of this series.
Now let’s setup database mirroring between the SQL Server instance
PowerPCSQL2008 [our principal server] and PowerPCSQL2k8 [our mirrored
server].
The following are the pre-requisites for database mirroring.
Now let’s create a database DB1 on the principal server,
PowerPCSQL2008, 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 [DB1] Script Date: 06/20/2009 21:10:33 ******/
IF EXISTS (SELECT name FROM sys.databases WHERE name = N’DB1′)
DROP DATABASE [DB1]
GO
USE [master]
GO
/****** Object: Database [DB1] Script Date: 06/20/2009 21:10:13 ******/
CREATE DATABASE [DB1] ON PRIMARY
( NAME = N’DB1′, FILENAME = N’C:Program FilesMicrosoft SQL ServerMSSQL10.SQL2008MSSQLDATADB1.mdf’ ,
SIZE = 1280KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N’DB1_log’, FILENAME = N’C:Program FilesMicrosoft SQL ServerMSSQL10.SQL2008MSSQLDATADB1_log.LDF’ ,
SIZE = 504KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
Now let’s create a database DB1 on the mirrored server, PowerPCSQL2K8,
using the following transact SQL statement.
USE [master]
GO
/****** Object: Database [DB1] Script Date: 06/20/2009 21:10:33 ******/
IF EXISTS (SELECT name FROM sys.databases WHERE name = N’DB1′)
DROP DATABASE [DB1]
GO
USE [master]
GO
/****** Object: Database [DB1] Script Date: 06/20/2009 21:10:13 ******/
CREATE DATABASE [DB1] ON PRIMARY
( NAME = N’DB1′, FILENAME = N’C:Program FilesMicrosoft SQL ServerMSSQL10.SQL2K8MSSQLDATADB1.mdf’ ,
SIZE = 1280KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N’DB1_log’, FILENAME = N’C:Program FilesMicrosoft SQL ServerMSSQL10.SQL2K8MSSQLDATADB1_log.LDF’ ,
SIZE = 504KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
If the target server does not have the database with same name, you
will get the following error when configuring database mirroring. [Refer Fig
1.0]
Fig 1.0
Note: Instead of creating the DB1 database on the
mirrored server, you could restore the database backup and tranlog backup using
the 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 DB1 to disk =’C:BackupsDB1.Bak’ with init
go
Backup log DB1 to disk =’C:BackupsDB1.trn’ with init
go
Restore the database on the target server using the following transact
SQL statement.
use master
go
restore database DB1 from disk =’C:BackupsDB1.Bak’ with norecovery,
replace,
move ‘DB1’ to ‘C:Program FilesMicrosoft SQL ServerMSSQL10.SQL2K8MSSQLDATADB1.mdf’,
move ‘DB1_log’ to ‘C:Program FilesMicrosoft SQL ServerMSSQL10.SQL2K8MSSQLDATADB1_log.ldf’
go
restore log DB1 from disk =’C:BackupsDB1.trn’ with norecovery, replace,
move ‘DB1’ to ‘C:Program FilesMicrosoft SQL ServerMSSQL10.SQL2K8MSSQLDATADB1.mdf’,
move ‘DB1_log’ to ‘C:Program FilesMicrosoft SQL ServerMSSQL10.SQL2K8MSSQLDATADB1_log.ldf’
go
On the target server, if the database is not in restore mode you will
get the following error. [Refer Fig 1.2]
Fig 1.2
Configure the database DB1 on the principal server for database
mirroring. Using SQL Server management studio, expand the databases and click
on the Database DB1. Right click on the database DB1 and select properties. In
the properties window select the "Mirroring" option as shown below. [Refer
Fig 1.3]
Fig 1.3
Now click on the "Configure Security" button and you will
see the following screen. Since we are not going to setup the witness server,
select the option "No" and click next. [Refer Fig 1.4]
Fig 1.4
Select the default port and the endpoint name chosen by the SQL server
management studio and click Next. [Refer Fig 1.5] If you are choosing some
other port, then make sure that port is open and available. 
Fig 1.5
Now select the mirrored server name, click on the "Connect"
button and make sure you can connect to the mirrored server. Select the default
port and the endpoint name chosen by the SQL server management studio and click
Next. [Refer Fig 1.6] If you are choosing some other port, then make sure that
port is open and available. 
Fig 1.6
Type the appropriate service account you want to use for the database
mirroring. [Refer Fig 1.7]
Fig 1.7
Double check the summary details and click finish. This will configure
database mirroring. [Refer Fig 1.8, 1.9, 1.10]
Fig 1.8
Fig 1.9
Fig 1.10
On the next screen, click on the button "Start Mirroring". [Refer
Fig 1.11]
Fig 1.11
On the next screen, click on the "Yes" button. [Refer Fig
1.12]
Fig 1.12
The following screen shows that database mirroring is configured and
running. [Refer Figure 1.13]
Fig 1.13
Click OK and refresh the databases. You can see the caption of the DB1
database has changed in both principal and mirrored server. [Refer Fig 1.14]
Fig 1.14
Part I of this series explained the basics of the Database mirroring
feature in SQL Server 2008. It also illustrated a step-by-step process on how
to create database mirroring. Part 2 of this series we will discuss how to add
data on the principal server and how data is mirrored to the mirrored server.
»
See All Articles by Columnist MAK
Subscribe to Cloud Insider for top news, trends & analysis
DatabaseJournal.com publishes relevant, up-to-date and pragmatic articles on the use of database hardware and management tools and serves as a forum for professional knowledge about proprietary, open source and cloud-based databases--foundational technology for all IT systems. We publish insightful articles about new products, best practices and trends; readers help each other out on various database questions and problems. Database management systems (DBMS) and database security processes are also key areas of focus at DatabaseJournal.com.
Advertise with TechnologyAdvice on Database Journal and our other IT-focused platforms.
Property of TechnologyAdvice.
© 2022 TechnologyAdvice. All Rights Reserved
Advertiser Disclosure: Some of the products that appear on this site are from companies from which TechnologyAdvice receives compensation. This compensation may impact how and where products appear on this site including, for example, the order in which they appear. TechnologyAdvice does not include all companies or all types of products available in the marketplace.
Leave a Reply