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.
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.
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.
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.
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.
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
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.
Step 9: Click Finish and you will see a window which will display success if endpoints are configured successfully.
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.
Step 11: Now if you come to object explorer you can in the Deepak\sansu 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.
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 Deepak\sansu will become principal db and marked as "sansu"(principal,disconnected) you can verify the same in the object explorer window as shown below,
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.
Reference :sql-article
Comments