September 30, 2013 at 1:29 pm
It was suggested to me that we could just have 1 witness for all of our {application name} databases for development, test, and production.
While keeping one witness for one production database instance makes sense to me, why on earth would I want one witness for multiple DB instances?
I like keeping devl/test/prod separate. So lets assume that is correct, one witness for development databases, another witness for test, and another witness for prod.
But, if you have multiple databases instances(e.g. in production) installed on either the same server or seperate servers, it doesn't seem best practice to use one witness for them all.
I think we should have 1 witness per database instance and configure all mirrored databases on that one instance to use 1 witness and not share that witness with other DB instances.
What is best practice for mirror witness to database instance?
Thanks.
October 1, 2013 at 7:35 am
While keeping one witness for one production database instance makes sense to me, why on earth would I want one witness for multiple DB instances?
One reason could be licensing fees, even though theoratically you could use a SQL Express instance as a witness.
Remember the witness doesn't hold any data of the mirrored databases, so from that point of view there's no issue with using one witness server for prod and test. But moest customers I deal with have separate network segments for test and production servers and in that case using one witness server means opening firewall ports between the VPN's which is not really ideal.
[font="Verdana"]Markus Bohse[/font]
October 1, 2013 at 7:57 am
alanspeckman (9/30/2013)
While keeping one witness for one production database instance makes sense to me, why on earth would I want one witness for multiple DB instances?
Why wouldn't you? Witness has no data, the role adds just about no load. All it is is an instance that provides quorum in the case of a failure of one of the other nodes. Doesn't make sense to install multiple SQL instances just to sit there and do just about nothing.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 2, 2013 at 8:05 am
The only reason would be single point of failure. One witness acting as quorum over serveral production database mirroring instances are all counting on that one express edition witness instance.
What else?
October 2, 2013 at 9:43 am
Yes it's a single point of something, but losing the witness will only remove the ability of the mirroring to automatically fail over. It'll have no other effect.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 4, 2013 at 9:47 am
Thanks Gail,
I've decided to go with one Witness in a separate physical location for production, and one witness for the dev/test environment.
I appreciate your responses.
Al
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply