Search This Blog

Loading...

Step By Step Guide to Database Mirroring

Principal Server: SQL8

Mirrored Server: SQL 4

Pre-Requisites

- Ensure that SQL Service /SQL Agent Services on all the machines start with the same domain account, otherwise mirroring wont work.

Step 1 : Install database on Mirrored Server

In order to get the database onto the mirrored server, we do a full backup of the ‘YourDatabase’ database on the Principal server, followed by a backup of the Transaction Log.

  • Perform a full backup of the database on the Principal server.
BACKUP DATABASE YourDatabase TO DISK = 'C:\YourDatabase_Full.Bak'
  • Perform a Transaction Log backup on the Principal server.



    BACKUP DATABASE YourDatabase TO DISK = 'C:\YourDatabase_Full.Bak'




  • Copy the backup files to the Mirror.

  • Do a restore of the full backup With NORECOVERY (if you use the visual tool, go to Options, then ensure you check the No Recovery option! This is vital! )

RESTORE DATABASE YourDatabase
FROM DISK = 'C:\YourDatabase_Full.Bak' WITH NORECOVERY,
MOVE 'YourDatabase_Data' TO 'D:\Data\YourDatabase.mdf',
MOVE 'YourDatabase_Log' TO 'C:\Data\YourDatabase.ldf'
  • Perform another restore of the Transaction Log, also with the NORECOVERY option. (This is important; otherwise you'll get an error when starting the mirror).



    RESTORE LOG YourDatabase FROM DISK ='C:\YourDatabase_Full.Bak' WITH NORECOVERY


You'll notice that the database on the Mirror server now is marked as "Restoring..." and can't be accessed. This is normal and expected! The Mirror is always in a permanent Restoring state to prevent users accessing the database, but will be receiving synchronization data. If the database fails over to the Mirror, then it will become an active database and the old Principal will go into the Recovering state.


clip_image002

Step 2: Setting up SQL Server Service impersonation

Make sure that the sql/ sql Agent services on both the principal and the mirrored servers are running under the same local user account and not Local system account.

  • Create a local user on both the Principal and the Mirror server with the same username and password. For example, "sqluser".
  • Edit the SQL Server Service and change the Logon to this user.
  • Do the same for the SQL Server Agent service.
  • Change the SQL Server Agent service to be Automatic.
  • Re-start the SQL Server Service
  • Do this on both the Principal and the Mirror

***Create a SQL Login on both SQL Servers for this user you created.

Step 3: Setting up the Mirror

Now it is time to setup Mirroring.

· Right click on YourDatabase database and select properties

· Click "Configure Security"

· Click Next on the wizard

· Choose whether you want a Witness server or not, choose “no” and click Next

· In the Principal Server Instance stage, leave everything as its default (you can't change anything anyway)


clip_image004



clip_image006



clip_image008

In the Mirror Server Instance stage, choose your Mirror server from the dropdown and click Connect to provide the credentials. Click Next.

clip_image010

  • In the next dialog about Service Accounts, leave these blank (you only need to fill them in if the servers are in a domain or in trusted domains)

clip_image012

  • Click Next and Finish
  • Click "Do not start mirroring"

clip_image014

  • Enter in the FQDN of the servers if you want, but this is not necessary (as long as it will resolve)
  • Change the operating mode to “High performance (asynchronous)”, otherwise the principal database will become slower.
  • Click Start Mirroring (if you do not have a FQDN entered, then a warning will appear, but you can ignore it)
  • The mirror should then start, and within moments, the Status should be "synchronized: the databases are fully synchronized"

clip_image016

Doing a forced failover

--Run on mirror if principal isn't available



USE MASTER
GO
ALTER DATABASE YourDatabase SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS
GO

Within 10 seconds the Mirrored database will come online, if you need to change the default failover time, run this on the principal server




ALTER DATABASE YourDatabase SET PARTNER TIMEOUT <Value in Secs, should be greater than 5>


that’s it, do some testing :)

2 comments:

kbmkr86 said...

On doing forced fail over on mirrored server database using the command ALTER DATABASE YourDatabase SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

We got the following error message
'the database mirroring service cannot be forced for database 'dbname' because the database is not in the correct state to become the principal database.

Please update on this asap

Aneesh said...

Sorry I missed few points,

Safety Full Without Witness
This scenario provides high safety, but automatic failover is not allowed. In the event of failure of the principal, the database service becomes unavailable. You need manual intervention to make the database service available. You must break the mirroring session and then recover the mirror database.

For example, prior to the failure, Server_A and Server_B acted as principal and mirror respectively. Server_A fails. You need to execute the following on Server_B to make the database service available:

ALTER DATABASE SET PARTNER OFF

RESTORE DATABASE WITH RECOVERY






Safety Off


In the event of failure of the principal, the database service becomes unavailable. You can perform a force service to make the database service available on the mirror. However, since the safety level is OFF, it is possible that there were transactions that didn’t make it to the mirror at the time of the failure of the principal. These transactions will be lost. Therefore, manual failover with safety OFF involves acknowledging the possibility of data loss.

For example, prior to the failure, Server_A and Server_B acted as principal and mirror respectively. Server_A fails. You need to execute the following on Server_B to make the database service available:

ALTER DATABASE SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

Once the database on Server_A becomes operational, it automatically assumes the role of the mirror. However, the mirroring session remains SUSPENDED, and you will need to manually RESUME the mirroring session.