April 23, 2008 at 11:52 pm
Hello Everyone,
I have DB mirroring set up for 10 databases between server A and server B. At the moment there is no witness server.
Now what if I like to add witness server C . Do I need to break mirroring between A & B for all databases , and drop mirroring end points ? I tried going to database properties and then mirroring , and set up the security again ,but at the very end it thorws an error message, Alter database ........failed.
Thanks in Advance
April 29, 2008 at 4:49 am
I suspect that you have a problem with the configuration of the Endpoint for your Witness. Are you able to run through the "Configure Database Mirroring Security Wizard", successfully configuring the Witness?
Interesting question though.... As a learning exercise, I created the following code snippets to see exactly what happens when changing to the various Mirroring States:
/*
What happens when the Mirroring State is changed for a database?
HS = High Safety
HP = High Performance
AF = Automatic Failover
+/- = with/without
Run all these commands in [master]
*/
--show the current Mirroring State
select database_id, (select db_name(database_id)) as 'database_name', mirroring_state_desc, mirroring_role_desc, mirroring_safety_level_desc, mirroring_partner_name, mirroring_witness_name, mirroring_witness_state_desc from sys.database_mirroring where mirroring_guid is not null
-- mirroring_safety_level_desc = FULL = HS
-- mirroring_safety_level_desc = OFF = HP
-- mirroring_witness_name = 'TCP:// ' = +AF
-- mirroring_witness_name = '' = -AF
--change from HS+AF to HS-AF
ALTER DATABASE [AdventureWorksDW] SET WITNESS OFF
--change from HS-AF to HS+AF
ALTER DATABASE [AdventureWorksDW] SET WITNESS = N'TCP:// '
--change from HS+AF to HP
ALTER DATABASE [AdventureWorksDW] SET WITNESS OFF
ALTER DATABASE [AdventureWorksDW] SET SAFETY OFF
--change from HP to HS-AF
ALTER DATABASE [AdventureWorksDW] SET SAFETY FULL
--change from HP to HS+AF
ALTER DATABASE [AdventureWorksDW] SET WITNESS = N'TCP:// '
ALTER DATABASE [AdventureWorksDW] SET SAFETY FULL
February 11, 2016 at 7:37 pm
the below document will show you exactly how to add witness server without breaking mirroring.
hope this helps
https://gallery.technet.microsoft.com/ADDING-WITNESS-TO-SERVER-7bb9e392
Thank you
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply