Solved

SQL Server 2008 Mirror (Witness issue Error 1456)

Posted on 2014-04-15
4
654 Views
Last Modified: 2016-05-28
Hello,

I have set up database mirroring before and know most of the gotchas with adding in a mirror, but I am really stuck on this new build.

Our setup is

3 physical boxes all with live IP and local lan.
Server A and Server B are domain controllers and the Witness is a member of the domain.

SQL and the agent runs as domain\administrator

We have mirroring working on Server A and B but without the Witness.

All servers through SSMS can connect to each other using the Windows AD login.

So, why is it when I add in the Witness do I get

TITLE: Microsoft SQL Server Management Studio

The ALTER DATABASE command could not be sent to the remote server instance 'TCP://witness:5022'. The database mirroring configuration was not changed. Verify that the server is connected, and try again. (Microsoft SQL Server, Error: 1456)

On the witness

select * from sys.database_mirroring_endpoints

Mirroring      65536      261      2      TCP      4      DATABASE_MIRRORING      0      STARTED      0      2      WITNESS      1      3      NEGOTIATE      0      1      RC4

All servers have hosts / dns entries, nothing is connecting on IP.

So I am now very stuck and need to fix this quickly :(
0
Comment
Question by:andy_booth
  • 3
4 Comments
 
LVL 13

Expert Comment

by:sameer2010
ID: 40001224
Do you have connect permission to anonymous on database?
Also, ensure that the port 5022 access is enabled through firewall.
0
 
LVL 1

Author Comment

by:andy_booth
ID: 40001268
Hi Sameer,

Do you have connect permission to anonymous on database? I am not sure what you mean by this, could you explain in more detail?

I can 100% confirm that I can telnet in all directions between the servers.
0
 
LVL 1

Accepted Solution

by:
andy_booth earned 0 total points
ID: 40006133
Well, I got it to work.

However I am not really too sure what fixed it, I rebooted all the servers and then the mirror broke.

So I stopped and started the end point on the mirror server and it all worked.

For reference, these are the commands

select * from sys.endpoints (to get the name of the end point)


ALTER ENDPOINT mirroring STATE = STOPPED


ALTER ENDPOINT mirroring STATE = STARTED
0
 
LVL 1

Author Closing Comment

by:andy_booth
ID: 41621775
fixed it myself
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question