Configuring Database Mirroring and Testing Mirroring Failover | SansSQL

Wednesday, October 9, 2013

Configuring Database Mirroring and Testing Mirroring Failover


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.


No comments:

Post a Comment