Mirroring

As discussed about Disaster Recovery and High Availbility topic here continue on this. today would like to discuss some more information about mirroring.

As stated in my ealier post about mirroring, today I would like to write about how to conifigure mirroring, mirroring is very simple to configure. before going for it we should deside what time of mirroring we want to go with

High Availability

High Protection

High Performance

depending upon that we have to configure Witness.

I would say there are only two steps for it.

1. Create End points

2. alter database “” set PARTNER

this is the simple procedure I want to discuss there are other several ways to setup the mirroring.

1. need to decide what is your “priceple” database server , “Mirror” database server and “Witness” database server. this can be seperate box or difference named instance on the same server, but should be on differenct instance as on one  sql server you can create multiple “endpoints” which is the way Mirroring works(interacts)

the simple command for creating endpoints could be

CREATE ENDPOINT EndPointName
STATE=STARTED AS TCP(LISTENER_PORT =5022)

FOR DATABASE_MIRRORING (ROLE = ALL )
if you are on same server(box) with different instances then we have to give different port number(eg. 5023, 5024)

* Principle and Mirrored database should be having same edition of sql server. witness can be express edition, and should be of “FULL RECOVERY MODEL”

Backup the Principle and restore it on Mirrored database with NORecovery
once you create the “Endpoints” then we have to alter the database PARTNER to listen to that endpoint port like.

at principle

alter database MirrorDB  set Partner=’TCP://MirroredServer:5022′

at mirror server

alter database MirrorDB  set Partner=’TCP://PricipleSrv:5024′

we can check the property of the principle database of option “Mirror” you will see the end point specified and other mirroring related info.

you are done. this is quick and short way of configuring mirror, once priciple is down it will rollover to mirror database server and Mirror server will become priciple and when Priciple is up that will become “mirror” server ie. reverse the setup.

*You cannot mirror the master, msdb, temp, or model system databases.

You can add on the securites and other configuration on it.

This is what I learned today.

Thanx.

Vinay

http://rdbmsexperts.com/Blogs/

http://vinay-thakur.spaces.live.com/blog
http://www.codeproject.com/Articles/109236/Mirroring-a-SQL-Server-Database-is-not-as-hard-as-.aspx

twitter\thakurvinay

Advertisements
This entry was posted in Disaster Recovery, General, High Avaliability, Installation, Tool, Webcast, What I learned today, Whitepapers and tagged . Bookmark the permalink.

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