March 25, 2009 at 4:25 am
hi all..
I want to implement database mirroring in MS SQL SERVER.After creating Endpoint on both the server,I have grant the permission to the user.
Now when I execute the following alter syntax on the mirror server
Alter database db_name set partner='tcp://primary_server:5022' ,the message I got is
'Command(s) completed successfully.'
But when I execute the alter statement on the primary server,the message I got was
" Msg 1418, Level 16, State 1, Line 1
The server network address "TCP://MSSQL: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."
When I saw the error log on the primary server,it displays
"The Database Mirroring protocol transport is now listening for connections.
Database mirroring has been enabled on this instance of SQL Server.
Error: 1474, Severity: 16, State: 1.
Database mirroring connection error 4 'An error occurred while receiving data: '10054(An existing connection was forcibly closed by the remote host.)'.' for 'TCP://MSSQL:5022'.
Error: 1443, Severity: 16, State: 2.
Database mirroring has been terminated for database 'DB_MIRRORING'. This is an informational message only. No user action is required."
And the Mirror server Error log displays
"Server is listening on [ 'any' 5022].
The Database Mirroring protocol transport is now listening for connections.
Database mirroring has been enabled on this instance of SQL Server.
Database Mirroring login attempt by user 'MSSQL\ACUTE$.' failed with error: 'Connection handshake failed. The login 'MSSQL\Guest' does not have CONNECT permission on the endpoint. State 84.'. [CLIENT: 192.168.1.222]"
plz suggest the solution
March 25, 2009 at 5:10 am
what sort of accounts are the SQL servers running on ?
March 25, 2009 at 5:18 am
what sort of accounts are the SQL servers running on ?
March 25, 2009 at 5:47 am
Hi...
thx for the reply...
I have used both server and windows authentication.
When I am using server authentication,I have created a certificate and used authentication=certificate in Endpoint created on both the server...
But still there was the same error message...
March 25, 2009 at 6:51 am
I mean , is the SQL service running under a Domain Account or a Local System Account
March 25, 2009 at 7:43 am
Local System Account
March 25, 2009 at 8:54 pm
When using Windows Authentication, if the server instances use different accounts, specify the service accounts for SQL Server. These service accounts must all be domain accounts (in the same or trusted domains).
http://msdn.microsoft.com/en-us/library/ms189434(SQL.90).aspx
MJ
March 26, 2009 at 11:30 am
While this might not be the same problem I had, please review this info and see if it helps you:
Try this:
Take a fresh backup (in full recovery) on the primary DB.
Restore the DB on the mirrored location (MAKE SURE TO SPECIFY NORECOVERY).
Try starting your mirroring session then.
This is a misleading error message.
You have to think of mirroring as "automated log shipping" and you are continuously restoring logs, so the DB on the secondary site must be brought online in norecovery mode, as if you are going to apply more T-logs to it.
Here are my steps:
1. Set DB to full recovery model on primary system.
2. Take full backup.
3. Restore full backup with norecovery (important)
4. Ensure named pipes is enabled on both systems.
5. Configure mirroring.
6. Start mirroring.
Steve
March 26, 2009 at 11:33 am
SK,
you have to take a full backup follwed by a transaction log backup of primary database and restore it in mirror database "with no recovery" mode.Then perform mirroring
[font="Comic Sans MS"]+++BLADE+++[/font]:cool:
March 26, 2009 at 3:00 pm
I've done it both ways with success 🙂
One way or another- the error message that gets generated if you forget to leave it in norecovery is not very intuitive.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply