| Slno |
Steps |
Stage |
Remarks |
| 1 |
Set the Database (Those Involved in Mirroring) to Readonly |
Pre Configuration |
While Configuring on production environment, set
the involved database to Read-Only to avoiding data modification during
configuration |
| 2 |
Backup Databases in Source |
Pre Configuration |
|
| 3 |
Move Backups to local Drives on Destination Server |
Pre Configuration |
|
| 4 |
Restore Backups on Mirror server with
No recovery |
Pre Configuration |
Execute AS USER='dbo' (To Make the Database Owner
as 'sa') Recommended to Use Scripts while restoring. Make Use of the above
Query in order to restore the database with 'sa' as owner when logged in as a
different User.
Ex: EXECUTE AS USER='dbo'
GO
Restore Database XYZ from Disk='C:\....' .... |
| 5 |
Manually Change the DB Owner to "sa" |
Pre Configuration |
If not Using Scripts in Step 4
Ex: Use DBName
GO
Exec sp_changedbowner 'sa' |
| 6 |
Make sure that the Databases are in
FULL Recovery
Mode |
Pre Configuration |
Full recovery mode against the principal instance
databases |
| 7 |
Script out required logins from Principal server
using sp_help_revlogin (More Information @
http://support.microsoft.com/kb/918992) and Create on VM's (Principal and
Mirror |
Pre Configuration |
This is done to match the SID's for SQL Users on
both Principal And Mirror |
| 8 |
Make sure that the Default Database for the Logins
involved in mirroring should be 'Master' |
Pre Configuration |
|
| 9 |
Make Sure SQL Services are running under the same
service account |
Pre Configuration |
|
|
|
|
|
| 10 |
Right-Click on the Principal Database
and Go to
properties |
Mirror Configuration |
|
| 10.A |
Go to "Mirroring" Tab |
Mirror Configuration |
|
| 10.B |
Click on "Configure Security" |
Mirror Configuration |
|
| 10.C |
Choose "Yes" In the Include Witness
Server Page |
Mirror Configuration |
|
| 10.D |
In the Next Page, Check the "Witness Server
Instance" option |
Mirror Configuration |
|
| 10.E |
Choose the Port Number and Chage the Endpoint Name
For the Principal Server. |
Mirror Configuration |
|
| 10.F |
In the Next Page, Connect to the Mirror Server and
Choose the appropriate Port Number and Endpoint Name for the Mirror Server. |
Mirror Configuration |
|
| 10.G |
In the Next Page, Connect to the Witness Server and
Choose the appropriate Port Number and Endpoint Name for the Witness Server. |
Mirror Configuration |
|
| 10.H |
In the Next Page, provide the Service Account Names
under which mirroring should be configured. |
Mirror Configuration |
Preferably a Service Account |
| 10.I |
Once step 10.H is completed, click Finish to start
the configuration of Mirroring |
Mirror Configuration |
|
| 10.J |
Once the Mirroring is setup, it will prompt for
starting the Mirroring.
Choose "Start Mirroring" if you are sure that the all the
Information that is shown in the Popup box is correct. If you find any
discrepancies choose "Do not Start Mirroring" and then make the
necessary changes and then Start the mirroring. |
Mirror Configuration |
|
| 10.K |
In the Database Properties Mirroring Page, the
status should show "Synchronized: the databases are fully
synchronized" if there are no issues encountered. |
Mirror Configuration |
|
| 10.L |
Once Step 10.K is confirmed, Click "OK"
on the Database Properties Mirroring Page |
Mirror Configuration |
|
|
|
|
|
| 12 |
Make Sure That the Mirror Database status is
"Mirror, Synchronized / Restoring ..." |
Post Configuration |
|
| 13 |
Give appropriate access to the DB Users in
Principal |
Post Configuration |
|
| 14 |
Give appropriate access to the DB Users in Mirror |
Post Configuration |
FailOver must be done for giving access to the
Mirror Database |
| 15 |
To Check the Mirroring Status, Right-Click on the
Principal Database and go to "Tasks" and choose "Launch
Database Mirroring Monitor …" |
Post Configuration |
|
| 16 |
Register The Mirrored Databases by connecting to the
appropriate Instance of SQL Server |
Post Configuration |
|
|
|
|
|
| 18 |
Go
to "Mirroring" Tab |
Testing
/ Test Case 1 |
|
| 19 |
And Click "FailOver" |
Testing / Test Case 1 |
|
| 20 |
In
the PopUp Window, Choose "Yes" |
Testing
/ Test Case 1 |
|
| 21 |
Once the failover is complete, Login to
the Mirror (Now Principal) using a SQL User and run few queries against the
database |
Testing / Test Case 1 |
|
|
|
|
|
| 22 |
Change
the Default DB of a SQL Login to the Mirrored Database. |
Testing
/ Test Case 2 |
|
| 23 |
Initiate Failover |
Testing / Test Case 2 |
|
| 24 |
Once
the failover is complete, Login to the Mirror (Now Principal) using the SQL
User and run few queries against the database |
Testing
/ Test Case 2 |
|
|
|
|
|
| 25 |
Shut
Down SQL Services on the Principal Server (Right Click on the Server and
Choose Stop Services) |
Testing
/ Test Case 3 |
|
| 26 |
Once the Services is stopped, Login to
the Mirror (Now Principal) and run few queries against the database |
Testing / Test Case 3 |
|
|
|
|
|
| 27 |
Hard
Shut Down SQL Services on the Principal Server (Execute SHUTDOWN WITH NO
WAIT) |
Testing
/ Test Case 4 |
Execute
The Command
Execute SHUTDOWN WITH NO WAIT |
| 28 |
Once the Services is stopped, Login to
the Mirror (Now Principal) using a SQL User and run few queries
against the database |
Testing / Test Case 4 |
|
|
|
|
|
| 29 |
Stop SQL Services on Witness Server. |
Testing / Test Case 5 |
There should be No Effect on Mirroring |
|
|
|
|
| 30 |
Pause
the Mirroring |
Testing
/ Test Case 6 |
|
| 31 |
Stop the Services on Principal |
Testing / Test Case 6 |
|
| 32 |
Rename
the Data file of the Principal database |
Testing
/ Test Case 6 |
|
| 33 |
Start the Services. |
Testing / Test Case 6 |
Does not Fail over to the Mirror. |
| 34 |
Stop
the Services on Principal |
Testing
/ Test Case 6 |
To
Bring back to Original State |
| 35 |
Rename the Data file of the Principal
database to original Name |
Testing / Test Case 6 |
|
| 36 |
Start
the Services. |
Testing
/ Test Case 6 |
Principal
and Mirror databases are still on the Same Servers. |
