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
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
Microsoft SQL Server 2008Microsoft SQL Server 2005Microsoft SQL Server
Last Comment
snyderkv
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).
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
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
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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
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