Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Multiple Mirrored databases, Dev, Test, Prod off of one Witness? Expand / Collapse
Author
Message
Posted Monday, September 30, 2013 1:29 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, July 28, 2014 6:49 PM
Points: 87, Visits: 545
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.
Post #1500177
Posted Tuesday, October 1, 2013 7:35 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 7:14 AM
Points: 4,432, Visits: 4,168

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.


Markus Bohse
Post #1500407
Posted Tuesday, October 1, 2013 7:57 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:02 AM
Points: 42,470, Visits: 35,543
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1500427
Posted Wednesday, October 2, 2013 8:05 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, July 28, 2014 6:49 PM
Points: 87, Visits: 545
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?
Post #1500784
Posted Wednesday, October 2, 2013 9:43 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:02 AM
Points: 42,470, Visits: 35,543
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1500820
Posted Friday, October 4, 2013 9:47 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, July 28, 2014 6:49 PM
Points: 87, Visits: 545
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
Post #1501655
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse