How to configure Database Mirroring

I have configured database mirroring with database sansu as the principal and with following 3 instances namely,

Deepak —> Principal

DeepakSansu —> Mirror

DeepakTest —> Monitor

I have drafted the following steps to configure database mirroring in SQL 2005,

Step 1: Prior to configuring mirroring we need to take a full backup and1 tran log backup of the principal db and restore them in mirror with NORECOVERY option.

configure_database_mirroring_1

Step 2: In SSMS expand the SQL server Deepak—> expand databases—> right click the db which is to be mirrored(in my case db name is sansu)—> select properties.This invokes the database properties window.

configure_database_mirroring_2

Step 3: Click configure security button at the top right of the window.Now a window will pop up with the name “Configure database mirroring security wizard” click next to proceed further.

configure_database_mirroring_3

Step 4: Now a new window pops up with the name “Include witness server”.Choose YES to include a witness server, so that the failover will be Automatic and click next.

configure_database_mirroring_4

Step 5: In the next window “Choose servers to configure” Just enable mirror and witness since principal has been greyed out and is currently enabled by default.Click next to proceed.

Step 6: Now furnish the details of principal server name and the port in which it listens with mirror.You can give any port number, but ensure that that particular port is not blocked.

configure_database_mirroring_6

Step 7: Similar to previous step mention the details of mirror server name and listener port details also mention the witness and its listener port.Click ok and proceed to next step

configure_database_mirroring_7

configure_database_mirroring_8

Step 8: Since I am running 3 instances in the same machine I’ve used local system account for all three so I’ve left the box as empty.If you are using different service accounts for all 3 servers you need to specify the account name here.

configure_database_mirroring_9

Step 9: Click Finish and you will see a window which will display success if endpoints are configured successfully.

configure_database_mirroring_10

configure_database_mirroring_11

Step 10: In the next window you need to press the “Start Mirroring” button and if you press the status button in the bottom it will display the status as databases are fully synchronized.

configure_database_mirroring_12

configure_database_mirroring_13

Step 11: Now if you come to object explorer you can in the Deepaksansu instance that the db sansu is configured as mirror and is in restoring state.Similarly in the sever Deepak the db sansu has been configured as principal.

configure_database_mirroring_14

Step 12 : Now having configured mirroring with witness server we can test the automatic failover as follows, stop the sql services in principal server Deepak and once it is done the mirror db sansu in the server Deepaksansu will become principal db and marked as “sansu”(principal,disconnected) you can verify the same in the object explorer window as shown below,

configure_database_mirroring_15

Step 13 : Now the principal server is down and your mirror db has become principal as automatic failover has taken place.Once you restart sql services in principal the original principal server Deepak has become the mirror server.You can see that the db sansu in original principal is now in restoring state as it has become the mirror currently.

configure_database_mirroring_16


Posted

in

by

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *