Adding Witness Server in Mirroring

• Ziljan4

  Ten Centuries

  Points: 1136

  More actions

  April 23, 2008 at 11:52 pm

  #123459

  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

• Andy sql

  SSCrazy Eights

  Points: 9497

  More actions

  April 29, 2008 at 4:49 am

  #808221

  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

• pruthvirajgowda 8776

  Mr or Mrs. 500

  Points: 561

  More actions

  February 11, 2016 at 7:37 pm

  #1858229

  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