Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Server 2008 Mirror (Witness issue Error 1456)

Posted on 2014-04-15
4
Medium Priority
?
1,007 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

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

618 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