Database mirroring is normally one of those subjects that everyone thinks is a really cool idea but think that it is a complicated process to implement without the usage of SSMS wizards. Who can blame you, database mirroring is the low cost,readily available high availability solution. How can a killer high availability solution like this have simple, elegant underpinnings.

However, if you simply look at the T-SQL commands that can be used set up a database mirror then you will gain an appreciation of how elegant of a solution Microsoft has implemented. The following set of commands show an example of setting up a simple database mirror and then add a witness. Basically, this takes place in a couple of steps.

  1. Restore mirrored instance with NO RECOVERY option
  2. Set up HTTP endpoints
  3. Set partners
  4. Make sure to update protection mode
 
--Create endpoint on principal and mirror
--run once on each server
CREATE ENDPOINT PE_MIRROR
     STATE=STARTED
     AS TCP(LISTENER_PORT=5022)
     FOR DATABASE_MIRRORING(
     AUTHENTICATION=WINDOWS NEGOTIATE,
     ENCRYPTION=SUPPORTED,
     ROLE=PARTNER)
 
--Set partner on mirrored instance
ALTER DATABASE MediaDB 
     SET PARTNER= N'TCP://DB2.PROGRAMMERSEDGE.COM:5022'
 
--Set principal database up now
ALTER DATABASE MediaDB 
     SET PARTNER= N'TCP://DB1.PROGRAMMERSEDGE.COM:5022'
 
--Set protection mode we will use asynchronous
--Default is always synchronous
ALTER DATABASE MediaDB SET SAFETY OFF
 
--You can set up a witness if you wish. First the endpoint
--Run this on the witness server
CREATE ENDPOINT PE_MIRROR
     STATE=STARTED
     AS TCP(LISTENER_PORT=5022)
     FOR DATABASE_MIRRORING(
     AUTHENTICATION=WINDOWS NEGOTIATE,
     ENCRYPTION=SUPPORTED,
     ROLE=WITNESS)
 
--Now run this on the principal server to set up the witness
ALTER DATABASE MediaDB 
     SET WITNESS= N'TCP://WITNESS.PROGRAMMERSEDGE.COM:5022'

 

Hopefully, this post helps to clear up a little bit of the mystery in setting up database mirroring on your systems.

Cheers,

AJ