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
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.
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.