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