Database Mirroring in SQL Server 2008


Database Mirroring in SQL Server 2008

What is Database Mirroring?

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.

How does database mirroring work?

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.

Modes of Database Mirroring

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 PowerPC\SQL2008 [our principal server] and PowerPC\SQL2k8 [our mirrored server].

What are the Pre-Requisites of database mirroring?

The following are the pre-requisites for database mirroring.
  • Edition of SQL Server should be Standard, Enterprise or Developer edition
  • Principal Database involved in database mirroring should be in full recovery mode
  • Before configuring database mirroring, take a full backup, transactional log backup on the principal server and restored it on the mirrored server with NORECOVERY option.
Now let's create a database DB1 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 [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 Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\DB1.mdf' , \
        SIZE = 1280KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'DB1_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\DB1_log.LDF' , 
        SIZE = 504KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
Now let's create a database DB1 on the mirrored server, PowerPC\SQL2K8, 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 Files\Microsoft SQL Server\MSSQL10.SQL2K8\MSSQL\DATA\DB1.mdf' , 
        SIZE = 1280KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'DB1_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2K8\MSSQL\DATA\DB1_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:\Backups\DB1.Bak' with init
go
Backup log DB1 to disk ='C:\Backups\DB1.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:\Backups\DB1.Bak' with norecovery,
replace,
move 'DB1' to 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2K8\MSSQL\DATA\DB1.mdf',
move 'DB1_log' to 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2K8\MSSQL\DATA\DB1_log.ldf'
go
restore log DB1 from disk ='C:\Backups\DB1.trn' with norecovery, replace,
move 'DB1' to 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2K8\MSSQL\DATA\DB1.mdf',
move 'DB1_log' to 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2K8\MSSQL\DATA\DB1_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.


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



Comments

Post a Comment

Popular posts from this blog

MOLAP, ROLAP, And HOLAP

In the OLAP world, there are mainly two different types: Multidimensional OLAP (MOLAP) and Relational OLAP (ROLAP). Hybrid OLAP (HOLAP) refers to technologies that combine MOLAP and ROLAP. MOLAP This is the more traditional way of OLAP analysis. In MOLAP, data is stored in a multidimensional cube. The storage is not in the relational database, but in proprietary formats. Advantages : Excellent performance: MOLAP cubes are built for fast data retrieval, and is optimal for slicing and dicing operations. Can perform complex calculations: All calculations have been pre-generated when the cube is created. Hence, complex calculations are not only doable, but they return quickly. Disadvantages : Limited in the amount of data it can handle: Because all calculations are performed when the cube is built, it is not possible to include a large amount of data in the cube itself. This is not to say that the data in the cube cannot be derived from a large amount of data. Indeed, this...
Read more

Difference Between CURRENT_TIMESTAMP and GETDATE()

Image
SQL SERVER – Difference Between CURRENT_TIMESTAMP and GETDATE() – CURRENT_TIMESTAMP Equivalent in SQL Server January 4, 2012 by  s uraj A common question – I often get from Oracle/MySQL Professionals: “What is the Equivalent to CURRENT_TIMESTAMP in SQL Server?” Here is a common question I often get from SQL Server Professionals: “What are differences between Difference Between CURRENT_TIMESTAMP and GETDATE ()?” Very simple question but have showed up so frequently that I feel like to write about it. Well in SQL Server GETDATE() is Equivalent to CURRENT_TIMESTAMP. However, if you use CURRENT_TIMESTAMP in your select statement it will work fine. You can see in the above example – both of them returns the same value. Now let us go to next question regarding difference between GETDATE and CURRENT_TIMESTAMP. Well, the matter of the fact, there is no difference between them in SQL Server ( Reference Link ). CURRENT_TIMESTAMP is an ANS...
Read more

Convert Seconds to HH:MM:SS

Convert Seconds to HH:MM:SS Today 2 of my colleagues from reporting team had this requirement. They had one table where Seconds were stored as INT and in report they wanted to convert the seconds to HH:MM:SS format.  They already had  1  solution ready with them.   It was something like: --------------------------------------------------------------------------------------------------------------- DECLARE  @Seconds  INT SET  @Seconds = 3800 SELECT   CONVERT ( VARCHAR (10),@Seconds/3600)     + ':'     + RIGHT( '00' + CONVERT ( VARCHAR (2),(@Seconds%3600)/60),2)     + ':'     + RIGHT( '00' + CONVERT ( VARCHAR (2),@Seconds%60),2)  AS  [HH:MM:SS]    GO --------------------------------------------------------------------------------------------------------------- -- The output--  01:03:20 It was working fine, but my colleagues were looking for som...
Read more