November 3, 2010 at 7:41 am
I have 3 database mirroring from different servers.
Can I use only 1 witness SQL server with multiple tcp endpoint? if yes,How?
Or What should I do to handle this situation? Separate witness sql server for each pair looks over kill for me.
I found it can use as witness for only 1 pair. If I try it on the second pair, it cut the witness out from first pair and left database as "Principle, Suspended"
Any help will be really appreciated.
Thank you in advance.
November 3, 2010 at 7:53 am
we have 1 witness 'server', but each pair it monitors is a seperate instance.
so we have 6 database servers that are mirrored. (12 seperate servers)
we have 1 witness server with 6 instances running.
everything runs great.
November 3, 2010 at 8:19 am
Thank Geoff
Is it possible to do something like 1 SQL server instance, 6 TCP endpoints?
November 3, 2010 at 8:28 am
no.
but you might be able to do something like;
instance pair 1 mirroring on port 5023
instance pair 2 mirroring on port 5024
instance pair 3 mirroring on port 5025
the witness instance has 1 endpoint all on port 5022
on each of the principal servers, run;
ALTER DATABASE <DATABASE_NAME>
SET WITNESS =
' TCP :// < system-address of the witness server > : 5022'
GO
however, the seperate instance on the witness will lead to a better solution imho....
November 3, 2010 at 10:17 am
Thank you so much.
For the second pair, I need to do mirroring in High Performance operating mode (change port as your reply) and add Witness as your reply on Principal later, everything comes out perfect.
Thank again.
May 17, 2011 at 1:52 pm
I am also trying to setup Mirroring with failover, the witness instance is on the same server as the mirror instance. My mirror has the default instance name, and my witness is named SQLWitness. How do I specify the following command, it will not allow me to name the instance.
ALTER DATABASE MainDB
SET WITNESS = 'TCP://MirrorDB\SQLWitness:5033';
GO
May 18, 2011 at 8:35 am
Melissa, you have the slash going the wrong direction. Should be / instead of \.
SET WITNESS = 'TCP://MirrorDB\SQLWitness:5033';
May 18, 2011 at 8:39 am
I'm almost certain I tried the forward slash.... but I will give it another try. Is there examples of this in the book you recommended?
May 18, 2011 at 8:55 am
Yes, there are examples and much, much more in the book I wrote. 🙂
May 18, 2011 at 9:12 am
When trying this command,
ALTER DATABASE SharePointDB
SET WITNESS = 'TCP://MirrorDB/SQLServerWitness:5033';
GO
I get the following error...
Msg 1449, Level 16, State 5, Line 1
ALTER DATABASE command failed due to an invalid server connection string.
May 18, 2011 at 9:15 am
An entire book about mirroring.... is there that much to go through on the topic? Does your book give all the T-SQL examples? And.... if I buy the book, will you give me the answer now to why this command is erroring out?
May 18, 2011 at 9:54 am
melissa.dougherty (5/18/2011)
When trying this command,ALTER DATABASE SharePointDB
SET WITNESS = 'TCP://MirrorDB/SQLServerWitness:5033';
GO
I get the following error...
Msg 1449, Level 16, State 5, Line 1
ALTER DATABASE command failed due to an invalid server connection string.
Melissa you should be specifying the fully qualified name for your server. Something like so
ALTER DATABASE SharePointDB
SET WITNESS = 'TCP://WitnessSrv.MyDomain.com:5033';
GO
In my example above the witness is set to the server WitnessSrv in domain MyDomain.com and to port 5033 which would have been previously configured with a database mirroring endpoint of role type Witness
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 18, 2011 at 10:50 am
I had read suggestions about not using the FQN... I put an alias in the hosts file. The reason this is difficult, is because I named the SQL Server instance, SQLServerWitness. So, when I execute the ALTER DATABASE it does not like the '/' with the instance name. I'm getting ready to purchase the book suggested on Kindle to look up the solution.
May 18, 2011 at 11:09 am
I'll always answer questions whether you buy my book or not. Help comes with no strings attached.
You can't specify an instance name for the conenction string. only server name (FQDN preferably) and port number. The port has to be unique on the server, so if you have multiple instances, you have to use different port numbers on each instance on the server for the endpoints.
May 18, 2011 at 11:27 am
That was the ticket.... when creating the endpoint, using a unique port number for that named instance was the answer. Now my witness is configured.
Thanks.
Melissa
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply