?
Solved

SQL Server 2008 Mirror (Witness issue Error 1456)

Posted on 2014-04-15
4
Medium Priority
?
902 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
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 UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

777 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