Featured Post

File for #PASS Virtual Group Webcast: Transactional Replication

Okay so here are the files I promised. It includes the slides and the code I talked about. Sorry if it seemed a little rushed but I was already behind the power-curve starting out on time and wanted to cover as much stuff as possible. Be sure to check out the other webcasts coming up for the XRMVirtualGroup and PTI’s webcast series. You’ll find the links in previous posts here….. Also, for those of you around the Nashville area, be sure to support the upcoming SQL Saturday #51 coming up on August 21st,2010. Right now I think the registration count is up to 250+ …….also I heard that if registration hits over 400 that Kevin Kline will shave his head….just saying….don’t know where it came from …..but that’s the word on the street:) Webcast Support Files Cheers, AJ                          

Read More

SQL Server 2008 Setting Up Database Mirroring with T-SQL

Posted by Arie | Posted in SQL Server | Posted on 15-07-2009

Tags: , , , ,

0

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

Write a comment