Solved

SQL Server 2008 Mirror (Witness issue Error 1456)

Posted on 2014-04-15
4
558 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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

746 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now