Could not start mirroring on a single laptop with two instances of SQL Server 2005

I tried to set up mirroring on my laptop.

I have got two instances of SQL Server 2005 SP2 on my laptop (the first one is the default instance).

Checked configuration running the following scripts

SELECT type_desc, port FROM sys.tcp_endpoints;

GO

SELECT state_desc FROM sys.database_mirroring_endpoints

go

SELECT role FROM sys.database_mirroring_endpoints;

GO

SELECT 'Metadata Check';

SELECT EP.name, SP.STATE,

CONVERT(nvarchar(38), suser_name(SP.grantor_principal_id))

AS GRANTOR,

SP.TYPE AS PERMISSION,

CONVERT(nvarchar(46),suser_name(SP.grantee_principal_id))

AS GRANTEE

FROM sys.server_permissions SP , sys.endpoints EP

WHERE SP.major_id = EP.endpoint_id

ORDER BY Permission,grantor, grantee;

GO

Everything is OK on both instances. Tried to start mirroring and I have got this error message:

TITLE: Database Properties

An error occurred while starting mirroring.

ADDITIONAL INFORMATION:

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

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

The server network address "TCP://kookaburra.sydney.ssw.com.au:5022" can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational. (Microsoft SQL Server, Error: 1418)

I could connect to both instances:

C:\Documents and Settings\SergeiTchernykh.SSW2000>sqlcmd -U sa -P <password> -S k
ookaburra,5022
1> exit
C:\Documents and Settings\SergeiTchernykh.SSW2000>sqlcmd -U sa -P <password> -S k
ookaburra\sydney2005,5023
1> exit

I could ping my laptop

C:\Documents and Settings\SergeiTchernykh.SSW2000>ping -a kookaburra

Pinging kookaburra.sydney.ssw.com.au [10.0.0.1] with 32 bytes of data:

Reply from 10.0.0.1: bytes=32 time<1ms TTL=128
Reply from 10.0.0.1: bytes=32 time<1ms TTL=128
Reply from 10.0.0.1: bytes=32 time<1ms TTL=128
Reply from 10.0.0.1: bytes=32 time<1ms TTL=128

Ping statistics for 10.0.0.1:
Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
Minimum = 0ms, Maximum = 0ms, Average = 0ms

Thank you.

Problem solved.

"Database Mirror Wizard" does not show the correct error.

This error was displayed - "Alter failed for Database 'Northwind'. (Microsoft.SqlServer.Smo)" after executing T-SQL statement "ALTER DATABASE SET PARTNER .."

After running SQL Server Profiler I found out that the first error was: Database Northwind wasn't configured for mirroring.

Mirror database should be in RECOVERING mode.

Dropped and restored Northwind database again with NORECOVERY and mirroring started working.

|||

Hi Sergei,

How did you create the endpoints? Since you were able to connect to the sql server instances listening on ports 5022 and 5023, I suspect that you created them for T-SQL payload and not DB mirroring.

Try using something similar to this to create the endpoints:

CREATE ENDPOINT [dbm] STATE=STARTED

AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)

FOR DATABASE_MIRRORING (ROLE = ALL)

Thanks,

Kaloian.

|||

Hi Kaloian,

As long as I have got 3 instances of SQL Server 2005 (later on) on my laptop (default, instance_1 and instance_2 (witness)) I manually configured endpoint for witness using statement "CREATE ENDPOINT ..." on port 5024.

But it wasn't the problem. Problem in my case was that to set up a mirror you need a mirroring database in RECOVERING mode (Ididn't configure that) but the error returned didn't specify that.

Thanks,

Sergei

sql