Database Mirroring: Cannot set witness in Microsoft SQL Server 2014 using Azure

1
votes

I am unable to connect to a witness server though Microsoft SQL Server 2014 for database mirroring. I am using Azure. Everything I have researched seems to point to this article https://support.microsoft.com/en-us/kb/940254 . But I cannot seem to get the DNS settings correct, or figure out how to set the host file, if that is even the fix.

I was mostly following this guide: https://msdn.microsoft.com/en-us/library/ms186384.aspx , for steps on setting up database mirroring. Here is a brief summary of my scenario.

Principal Server

  • Cloud Service: MP

  • VM: MP (Windows Server 2012 R2 Datacenter)

Mirror and Witness Server

  • Cloud Service: MF

  • VM mirror: MF (Windows Server 2012 R2 Datacenter)

  • VM witness: MW (Windows Server 2012 R2 Datacenter)

I am getting this error when trying to run the SQL COMMAND:

ALTER DATABASE database SET WITNESS = 'TCP://MF.cloudapp.net:5023';

Msg 1456, Level 16, State 3, Line 1

The ALTER DATABASE command could not be sent to the remote server instance 'TCP://MF.cloudapp.net:5023'. The database mirroring configuration was not changed. Verify that the server is connected, and try again.

I have opened specific ports on the firewall to allow connections and have set up Endpoints on the Azure portal. Any assistance you can provided will be very much appreciated.

In the meantime, I am going to try giving the witness server its own cloud service so the end points will all be the same (mentioned on this page: http://go4answers.webhost4life.com/Example/trouble-setting-witness-182317.aspx ). I also am going to try setting all 3 VMs on one cloud service then adding them to an availability set (mentioned on this page: https://cuteprogramming.wordpress.com/2014/10/16/database-mirroring-in-azure/ ).

2
sql-serverazureazure-sql-databasesql-server-2014mirroring
This really doesn't have anything to do with programming, but with DB administration - so it's off-topic here and belongs on dba.stackexchange.com - voting to move. – marc_s
I was able to try both methods in the meantime and I was given the same error message both times. – avidgamer

2 Answers

0
votes

Have you verified that there is bidirectional communication between the Database Mirroring replica and the Witness? Make sure that they can ping each other and that they are listening on their endpoints on both sides (check netstat). Make sure that the VMs are in the same VNET.

0
votes

This happened to me twice and both times it was due to the SQL Server and agent service account. When I changed it to the Domain account I was able to add the witness in one shot.