Tuesday, November 22, 2011

Step By Step Guide to Configure Database Mirroring In SQL Server

In this example I am using "AdventureWorks" for Mirrored Database. 

For creating a new mirror database requires taking a full backup of the principal database and a subsequent log backup and restoring them both onto the mirror server instance, using WITH NORECOVERY. The mirror database must be in the RESTORING state for mirroring to work. 

Step1: Take full backup of the database on the Principal server.

Full Backup Genera Tab

Step2: Perform a Transaction Log backup on the Principal server.
Transaction Log Genera Tab


Step3: Perform restore in the destination server with the same name. Make sure that restore the database with NORECOVERY.

Restore General Tab
 
Restore Option Tab
Step4: Perform another restore of the Transaction Log, with the No Recovery option.
Log Restore General Tab


Log Restore Option Tab
Step5: You will notice that the 'AdventureWorks' database on the Mirror server now is marked as "Restoring..." and can't be accessed. 

Step6: Now starts the actual mirror setup, on your Prinicpal server right click and select Properties on the 'AdventureWorks' database and choose the Mirroring tab and then Click "Configure Security...".

Step7: Now click Next on the wizard.


Step8: Select whether you want a Witness server or not, you dont want witness server then click 'NO' (In this article i am covering witness server, so i slected "YES') and then click Next.

Step9: Now click Next.

Step10: In the Principal Server, leave everything as its default and click Next.


Step11: In the Mirror Server Instance stage, click Connect, select the mirror server instance and provide the credentials then click Next.

Step12: In the Witness Server Instance stage, click Connect, select the Witness server instance(In this article my witness server name is Monitorserver) and provide the credentials then click Next. 

 


Step13: If everything successfully configured then click Next.

Step14: Now click Start Mirroring, done, you successfully configured Mirroring.


If you have any issues regarding mirroring setup, leave a comment.  :) 

Here you can find how to setup a database mirroring using Certificates.

step by step tutorial. step by step guide. Step by Step Installation. Step-by-step procedure. Database Mirroring step by step. Step by Step Guide to. sql server step by step. sql server step by step ebook. microsoft sql server step by step ebook. sql server step by step tutorial. step by step installation guide. microsoft sql server 2008 step by step. Prerequisites. Restrictions. and Recommendations for Database Mirroring. Prerequisites and Recommendations for Database Mirroring. SQL Server Database Mirroring Pre-requisites and guidelines. Prerequisites for Database Mirroring. Database Mirroring in SQL Server 2008. Prerequisites: Configuring database mirroring or database. Introduction to Database Mirroring. Setting Up Database Mirroring. Database Mirroring Witness. Database Mirroring Sessions. Configure availability by using SQL Server database mirroring. Setting Up Database Mirroring. Database Mirroring Best Practices and Performance Considerations. Things to consider when setting up database mirroring in SQL Server 2012. Database Mirroring and Other Features and Components. Database Mirroring Components In SQL Server 2012. Database Mirroring Components In SQL Server 2008 R2.

1 comments:

Thanks Nareshkar for this nice post. Waiting for your next post.....

Post a Comment