SQL Server 2008 Setting Up Database Mirroring with T-SQL
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.
- Restore mirrored instance with NO RECOVERY option
- Set up HTTP endpoints
- Set partners
- 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