asked on

SQL Mirror to multiple instances

EE,

All the Mirror docs I've read online only discuss a single mirror but I'm mirroring 3 instances on the same server but only one is working. Do I need to create new endpoints or each instance with a different port number other than 5022?

Thanks in advance for the assistance

Rich Weissler

I think you are asking about using database mirroring to replicate out one database to three other SQL instances. I was under the impression that database mirroring only works to mirror a given database between two sql instances... so you have a principle and a mirror. You could combine a database mirror and log shipping... or in SQL 2012, you could use Availability Groups to maintain additional copies (which sounds like the solution you are looking for... but that isn't available in SQL 2008 or SQL 2005).

snyderkv

ASKER

Thanks for responding Rich.

To be more specific, I already mirrored one SQL instance to another SQL server but the other two won't follow along. I'm guessing I have to ALTER Database and create new endpoints?

For example:
SQL 1
Instance SharePoint
Instance SCCM
Instance Solar

SQL 2 Mirror
Instance SharePoint MIrror
Instance SCCM Mirror
Instance Solar MIrror

ASKER CERTIFIED SOLUTION

Rich Weissler

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

snyderkv

ASKER

Thanks Rich, I finally got around to labing this at home before performing at work. After recreating the exact problem, instances do in fact require a different port as said black and white in the link you provided: "Because each server has a distinct mirroring endpoint that uses a unique port, the port number uniquely identifies a specific server instance. This permits multiple server instances on a single server to participate in database mirroring (which is typically done only for testing purposes)" I don't know what they mean by "testing" though plus this must be an old article as my ports default to 5022. I just changed to 5030.

You can do this in the GUI but for anyone with the same issue, I first had to drop the current endpoint because the GUI options for the port and encryption will be grey'ed out:

DROP ENDPOINT mirroring
GO

Used T-SQL to make the endpoint but you can run through the GUI as well. Did this for each server

CREATE ENDPOINT Mirroring
STATE = STARTED
AS TCP ( LISTENER_PORT = 5022 ) <--- Change
FOR DATABASE_MIRRORING
(ENCRYPTION = DISABLED,ROLE=ALL)
GO

I was then able to use the Mirror wizard and run through it getting rid of error 1418