Database Mirroring:Create and Manage Database Mirroring on SQL Server 2005.

Dear Friends,

Read this forum to know more about SQL Server Database Mirroring.

http://msdn.microsoft.com/en-us/library/ms189852.aspx

Now,Follow this Steps to create Database Mirroring in SQL Server 2005 using Domain User Service account.

Two Servers are available for Database Mirroring.

Note :Both Database running on same service account user and have same name in it.

1.Principal.
This is the server where source database exist.Database Name:DatabaseMirroring.


2.Mirror
This is the server where mirrored database exist.

First we need to configure mirror database by taking principal database backup plus latest transaction logs backup and restore with norecovery to mirror database.

Add entries of host in C:\WINDOWS\system32\drivers\etc\host file.

Turn firewall off.



1.Create Mirror Database.

Restore Database backup file from source database.

RESTORE DATABASE [DatabaseMirroring] FROM 
DISK = N'D:\mx.bak' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  REPLACE,  STATS = 10
GO

Now,Restore Transaction logs backup file.

RESTORE LOG [DatabaseMirroring] FROM  DISK = N'D:\mx.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10
GO





2.SQL Server Service run on domain user.Apply on both instance.





3.Configure Database Mirroring.

Connect Principal Database from SQL Server Management Studio.

Right click on DatabaseMirroring databas.

Go to Task-->Mirror.




Configure Security.



There is no witness in it.


Click on "NO".








Select Principal Server.



Select Mirror Server.




Most Important to use domain user in it.



Finish the configuration.




Check the success status.


Start Mirroring.





4.Monitor it. 

5.Querying on Mirror Database by Database Snapshot.

CREATE DATABASE SS_DatabaseMirroring ON
( NAME = DatabaseMirroring, FILENAME =
'D:\Data\SS_DatabaseMirroring.ss' )
AS SNAPSHOT OF DatabaseMirroring;
GO

 Use DatabaseMirroring 
Go

Select * from tUserRoles

Go  

 

2 comments:

  1. Hi Bhavesh,Your Blog is very Simple and Easy to understand.All things are propery Explain. Do write blogs to help others.-- Dinesh Vishe...

    ReplyDelete

Dear User,

Thank you very much for your kind response