Previous Post:
In previous post I have define some ground work settings before starting database mirroring.
1- Same machine or server with 3 different database instances install on it.
2- Different machines or servers for Principal, Witness and mirror.
3- Use same or different machines with Principal and Mirror there is no witness required.
I am going to explain database mirroring using 1st point. In this point i have installed three different instances of sql server 2008 on same machine. look at the picture below
From the picture above we can see following instances installed on my setting
- na-sash\MSSQLSERVERR2: We will use this instance as a Principal Server.
- na-sash\mirror: We will use this instance as a Mirror Server.
- na-sash\witness: We will use this instance as a Witness Server.
To start database mirroring i am using a database "System" running on Principal server.
Step 1: Take a full backup of system database.
Step 2: Open the mirror server and restore that database in Recovery mode as seen in following diagrams.
Remember to change RESTORE WITH RECOVERY to RESTORE WITH NO RECOVERY MODE
Pic Step-2
Step 3: Open the Principal server and get properties of System database. If you look at the System database that you have restored recently on mirror server. It will show you in restoring... mode.
Step 4: On properties go to the Mirror tab.
Pic step - 4
Step 5: Click on Configure security.
Pic step - 5
Step 6: SQL server will ask for Witness configuration. As we have already defined if we need to create auto fail over we will include witness server as well.
Step 6: SQL server will ask for Witness configuration. As we have already defined if we need to create auto fail over we will include witness server as well.
Pic step - 6
A conformation window will explain about 3 servers and if you want to add Witness server later you can unchecked witness server here
A conformation window will explain about 3 servers and if you want to add Witness server later you can unchecked witness server here
Pic step - 6 -1
Step 7:
First tab will configure your principal server settings. If you are using same server with different instances as I am using in my example then you need to change port number for each configuration step. look at the listener port and End Point Name. 
Pic step 7-1
Step 7-2:
Second tab will configure your Mirror server settings. If you are using same server with different instances as I am using in my example then you need to change port number for each configuration step. look at the listener port and End Point Name.
Pic Step 7-2
Step 7-3:
Second tab will configure your Witness server settings. If you are using same server with different instances as I am using in my example then you need to change port number for each configuration step. If you did not ticked Witness server check box in Step 6 then this window will not appear. look at the listener port and End Point Name.
Pic Step - 7-3
Step 8:
If you are using Domain account for all three servers and account has rights in all three servers then you will specify that account in blank text boxes. If you are using SQL Server account then leave it blank and it will users that you specify during step 7 connectivity.
Pic Step - 8
Step 9:
Final summery before setup will confirm all previous settings.
Pic Step - 9
Step 10:
After you press finish you will step back to Mirror window but this time all three text boxes (Principal, Witness and Mirror) will have access path defined for mirroring. A conformation window will appear that will ask to Start Mirroring in High Safe Mode. Press start mirroring and on pressing refresh button at bottom you will see synchronized message as shown in pic below.
Congrats you have successfully configured database mirroring setup on you setup. To confirm that database mirroring drill down to you system database at Mirror server and you will see following status
DB Name: (Mirror, Synchronized / restoring...)
and on the Principal server you will see following status
DB Name: (Principal, Synchronized)
Previous Steps:
Next Steps:
No comments:
Post a Comment