error occurred while starting mirroring

Question

 

Hi,

 

I've Configured the Mirroring using the two machines, I've checked up everything,creation of end points successfully on both the machines, databases are in full recovery mode as well.

 

while Start Mirroring i'm facing following error :

 

TITLE: Database Properties
------------------------------

An error occurred while starting mirroring.

------------------------------
ADDITIONAL INFORMATION:

Alter failed for Database 'MIS'.  (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Alter+Database&LinkId=20476

------------------------------

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

The server network address "TCP://DBD-P3-01047.macmill.com:5023" can not be reached or does not exist. Check the network address name and reissue the command. (Microsoft SQL Server, Error: 1418)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=1418&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

and when i'm using the following command :

ALTER DATABASE [mis] SET PARTNER =N'TCP://DBD-P3-01063.macmill.com:5022'

to start mirroring the following error has come up :

 

The partner server instance name must be distinct from the server instance that manages the database.

The ALTER DATABASE SET PARTNER command failed.

 

How can i resolve this issue?Help is greatly appreciated!!!!

- Hitesh

 

Answer 1

Have you tried specifying an ip rather than the servername?  I have had similar occurrences, although this was only in a lab setting and both instances resided on the same machine, and once supplying an ip it was resolved.

 

Answer 2

Can you try to alter this endpoint with a different port number other than 5022 or 5023 and see how it goes ? We faced this issue and managed to alter the endpoint to solve it.

- Deepak

Answer 3

Hi,

 

Thanks for the response.

 

I've tried both cases, changed the End point port numbers at both the computers and also given the IP instead of Server name,but still the problem is occuring.

 

Then what might be the problem?

 

I ensured almost every thing :

- Firewall is not blocking that particular port no.

- TCP port is also listening to the Sql Process ID.

 

Please help me out.

-Hitesh

 

 

Answer 4

Verify whether you have latest sp installed in your server as there are few bugs fixed in sp2

 

 

Check this as well http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=67626

Answer 5

I had this exact same problem just now setting up our mirroring in our production environment between 2 separate servers. Our issue was that the SQL Server services were using the local system account as their logon account on both servers. Since the local system accounts are different on each server they aren't using the same account and for mirroring to work the services on both servers need to be running under the same user account. I created an administrator account speccifically for the SQL Services to use and set it as an admin on both servers and now I no longer receive this message.

It took me a lot of digging to find this answer so I figured I would post it here in hopes that it helps someone else in the future.

Cheers,
Steve

Answer 6

1- Mirroring DB should be restored with NoRecovery option, for more information; It is really Important to follow the steps mentioned in the following link:

http://msdn.microsoft.com/en-us/library/ms189047.aspx

2- In addition to that create a windows User and Grant him access to connect the Mirroring End Point

GRANT CONNECT ON ENDPOINT::[ EndPointName] TO [DomainName\UserName] ;


Answer 7

 

Looks like one / more service accounts does not have enough permissions on one / more of principal, mirror or witness

Please check below

- Principal, mirror and witness - SQL and Agent Service accounts running under same domain accounts.

- Does service accounts have enough access permissions thru port 5022 on all above servers

- Under what account Endpoints configured? Does account have enough permissions on principal, mirror and witness

 

more here for troubleshooting mirror issues http://msdn.microsoft.com/en-us/library/ms189127.aspx

---

http://uk.linkedin.com/in/ramjaddu

Answer 8

Hi Hitesh,

Even me too fased this problem.

first execute following command in mirror db.

ALTER DATABASE <DBNAME> SET PARTNER OFF

then go ahead.

When you restoring the principal db in mirror db change the file paths.

and drop existing endpoints and reconfigure mirroring.

Make sure that your principal and mirror database names are same.

while you restoring the principal db into mirror server must change the path.

mirror db must be restroing mode.

principal and mirror db in full recovery model.