SSIS and Sql Server Journey

Personal Notes of Sarabjit Singh and Bhavpreet Singh

Sql Server Mirroring

on June 11, 2012

Prerequisites:
A) SQL Servers on Principal and Mirror server shopud be started under account with the same name (xyzMachineUser)
B) You should be connected to SQL Server under sqlmirroring SQL Account (With same user name and password: lets say we have account: sqlmirroring )

Steps:
1) Take Full Backup from Principal Database (database must be in Full recovery model)
2) Take Transaction Log backup from Primary Database
3) Restore Full backup on mirror server with NORECOVERY option on mirror server(with user sqlmirroring)
4) Restore Transaction Log backup with NORECOVERY option on mirror server
5) Right click on database you want to start mirroring -> Properties -> Mirroring..
6) Click ”Configure Security” -> Choose not to Configure Witness server -> Connect to Mirror Server under sqlmirroring user (other steps – click next) (Enable encryption – remove checkbox mark)
7) In dialog window choose “Do not start mirroring”
8) Check radiobutton “High Performance”
9) In “Server network addresses” replace machine names to ip addresses of servers
10) Click “Start Mirroring”

If principal server goes down, you should restore mirror database using this commands:
ALTER DATABASE database_name SET PARTNER OFF
GO
RESTORE DATABASE database_name WITH RECOVERY
GO
And connect your front end with this database.
Your website is UP again.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: