SQL Server Performance Forum – Threads Archive

Problem with database mirroring

Hi All ! We have a database(DB) (size 16GB) which is backup form MS SQL Server 2000 and restore to the MS SQL Server 2005.
We have two computers SERVER1 and SERVER2 with Windows server 2003 R2 and SQL 2005 SP2.
We have default instances on each of computer. SQL services running with administrator rights. We have restored DB database to server SERVER1 as normal database. Then we create backup of this database for mirroring and restored it to second server SERVER2 as nonrecovery.
When we create a mirror we get this error message: TITLE: Database Properties
—————————— An error occurred while starting mirroring. ——————————
ADDITIONAL INFORMATION: Alter failed for Database ‘DB’. (Microsoft.SqlServer.Smo) For help, click:http://go.microsoft.com/fwlink?Prod…ceptionText&EvtID=Alter Database&LinkId=20476 —————————— An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) —————————— Communications to the remote server instance ‘TCP://SERVER2:5022’ failed before database mirroring was fully started. The ALTER DATABASE command failed. Retry the command when the remote database is started. (Microsoft SQL Server, Error: 1413) For help, click:http://go.microsoft.com/fwlink?Prod…42&EvtSrc=MSSQLServer&EvtID=1413&LinkId=20476 ——————————
BUTTONS: OK
——————————
And we can not find the reason why it is so. We try to do this mirroring many times and every time we have this message. P.S.
And when we try to make a new database on SERVER1 and create a mirror of this new database on SERVER2, IT WORKS ! May be the reason of this problem is database DB which is created in MS SQL Server 2000? Max

What is the recovery model set for this ‘db’ database? The error means that the communication link between the two partners was up and working. The initial negotiation between the partner servers has started, and then the connection was interrupted for whatever reason. Run the following code: *
Configure partner side of DB mirror (Partner = PARTNERB)
*/ — ——————————————————————————
— Local OUTBOUND Certificate Creation for PARTNERB
— —————————————————————————— use master
go –drop master key
create master key encryption by password = ‘somepassword’;
go –drop certificate PARTNERB_Certificate
create certificate PARTNERB_Certificate
with subject = ‘PARTNERB certificate for database mirroring’;
go — View certificates
select * from sys.certificates — Check for existing endpoints and create new if none exist
SELECT name, role_desc, state_desc, connection_auth_desc, encryption_algorithm_desc FROM sys.database_mirroring_endpoints; CREATE ENDPOINT Endpoint_Mirroring
STATE = STARTED
AS TCP (LISTENER_PORT=5022
, LISTENER_IP = ALL)
FOR DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE PARTNERB_Certificate
, ENCRYPTION = REQUIRED ALGORITHM AES
, ROLE = ALL);
GO — Backup certificate and transport to PARTNERA
backup certificate PARTNERB_Certificate TO FILE = ‘X:certificatesPARTNERB_SOMEDATE.cer’;
go — ——————————————————————————
— Add Partner Certificate and Logins/Users for Connect on Endpoint (PARTNERA)
— ——————————————————————————
use master — create SQL login and user
create login PARTNERA_Mirror_Login with password = ‘somepassword’
go SELECT * FROM sys.server_principals
go create user PARTNERA_Mirror_Login_User for login PARTNERA_Mirror_Login
go — view users
select * from sysusers — view certificates
SELECT * FROM sys.certificates –drop certificate PARTNERA_Login_Certificate
create certificate PARTNERA_Login_Certificate authorization PARTNERA_Mirror_Login_User from file = ‘X:certificatesPARTNERA_SOMEDATE.cer’
go — Grant CONNECT priv to the SQL mirror user
grant connect on endpoint::Endpoint_Mirroring to PARTNERA_Mirror_Login
GO –ALTER DATABASE plaync SET PARTNER OFF
Also:
1. Restart the SQL Server service on the mirror server.
2. Wait for the databases to be synchronized.
3. Issue the failover command again.
Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

I try it, but no result <img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ /> may be the problem is in the database which was restored from MS SQL Server 2000?

check the compatibility level of your restored database in SQL Server 2005. if itis restored from 2000 it might be 80 run this statement
sp_dbcmptlevel ‘somedatabasename’ Madhu

It not works, the mirroring still not started <img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ /> There are 3 errors: First: Event Type: Error Event Source: MSSQLSERVER Event Category: (2) Event ID: 1479 Date: 05.03.2007 Time: 10:05:12 User: N/A Computer: MCS-DB1 Description: The mirroring connection to "TCP://test101.balticcinema.lv:5024" has timed out for database "Cinetix3" after 10 seconds without a response. Check the service and network connections. For more information, see Help and Support Center at<a target="_blank" href=http://go.microsoft.com/fwlink/events.asp.>http://go.microsoft.com/fwlink/events.asp.</a> Data: 0000: c7 05 00 00 10 00 00 00 Ã‡……. 0008: 08 00 00 00 4d 00 43 00 ….M.C. 0010: 53 00 2d 00 44 00 42 00 S.-.D.B. 0018: 31 00 00 00 00 00 00 00 1……. Second: Event Type: Error Event Source: MSSQLSERVER Event Category: (2) Event ID: 1413 Date: 05.03.2007 Time: 10:05:12 User: BALTICCINEMAadministrator Computer: MCS-DB1 Description: Communications to the remote server instance ‘TCP://test101.balticcinema.lv:5024’ failed before database mirroring was fully started. The ALTER DATABASE command failed. Retry the command when the remote database is started. For more information, see Help and Support Center at<a target="_blank" href=http://go.microsoft.com/fwlink/events.asp.>http://go.microsoft.com/fwlink/events.asp.</a> Data: 0000: 85 05 00 00 10 00 00 00 #133;……. 0008: 08 00 00 00 4d 00 43 00 ….M.C. 0010: 53 00 2d 00 44 00 42 00 S.-.D.B. 0018: 31 00 00 00 09 00 00 00 1……. 0020: 43 00 69 00 6e 00 65 00 C.i.n.e. 0028: 74 00 69 00 78 00 33 00 t.i.x.3. 0030: 00 00 .. Third: Event Type: Error Event Source: MSSQLSERVER Event Category: (2) Event ID: 1443 Date: 05.03.2007 Time: 10:05:12 User: N/A Computer: MCS-DB1 Description: Database mirroring has been terminated for database ‘Cinetix3’. This is an informational message only. No user action is required. For more information, see Help and Support Center at<a target="_blank" href=http://go.microsoft.com/fwlink/events.asp.>http://go.microsoft.com/fwlink/events.asp.</a> Data: 0000: a3 05 00 00 10 00 00 00 Â£……. 0008: 08 00 00 00 4d 00 43 00 ….M.C. 0010: 53 00 2d 00 44 00 42 00 S.-.D.B. 0018: 31 00 00 00 00 00 00 00 1……. May be it helps for problem investigation. Max

Here is SQL Server logfile: Date,Source,Severity,Message
03/05/2007 15:14:38,spid22s,Unknown,Database mirroring connection error 4 ‘An error occurred while receiving data: ’64(error not found)’.’ for ‘TCP://xxx.xxxxx.xx:5024’.
03/05/2007 15:14:38,spid22s,Unknown,Error: 1474<c/> Severity: 16<c/> State: 1.
03/05/2007 15:13:10,spid22s,Unknown,Database mirroring has been terminated for database ‘DB’. This is an informational message only. No user action is required.
03/05/2007 15:13:10,spid22s,Unknown,Error: 1443<c/> Severity: 16<c/> State: 2.
03/05/2007 15:13:10,spid56,Unknown,Communications to the remote server instance ‘TCP://xxx.xxxxx.xx:5024’ failed before database mirroring was fully started. The ALTER DATABASE command failed. Retry the command when the remote database is started.
03/05/2007 15:13:10,spid56,Unknown,Error: 1413<c/> Severity: 16<c/> State: 1.
03/05/2007 15:13:10,spid22s,Unknown,The mirroring connection to "TCP://xxx.xxxxx.xx:5024" has timed out for database "DB" after 10 seconds without a response. Check the service and network connections.
03/05/2007 15:13:10,spid22s,Unknown,Error: 1479<c/> Severity: 16<c/> State: 1.
03/05/2007 15:12:07,spid51,Unknown,Using ‘xpsqlbot.dll’ version ‘2005.90.3042’ to execute extended stored procedure ‘xp_qv’. This is an informational message only; no user action is required.
03/05/2007 14:16:18,spid17s,Unknown,Database mirroring connection error 4 ‘An error occurred while receiving data: ’64(error not found)’.’ for ‘TCP://xxx.xxxxx.xx:5024’.
03/05/2007 14:16:18,spid17s,Unknown,Error: 1474<c/> Severity: 16<c/> State: 1.
03/05/2007 14:14:45,spid17s,Unknown,Database mirroring has been terminated for database ‘Cinetix3’. This is an informational message only. No user action is required.
03/05/2007 14:14:45,spid17s,Unknown,Error: 1443<c/> Severity: 16<c/> State: 2.
03/05/2007 14:14:45,spid53,Unknown,Communications to the remote server instance ‘TCP://test101.balticcinema.lv:5024’ failed before database mirroring was fully started. The ALTER DATABASE command failed. Retry the command when the remote database is started.
03/05/2007 14:14:45,spid53,Unknown,Error: 1413<c/> Severity: 16<c/> State: 1.
03/05/2007 14:14:45,spid17s,Unknown,The mirroring connection to "TCP://test101.balticcinema.lv:5024" has timed out for database "DB" after 10 seconds without a response. Check the service and network connections.
03/05/2007 14:14:45,spid17s,Unknown,Error: 1479<c/> Severity: 16<c/> State: 1.
03/05/2007 14:13:58,spid52,Unknown,Using ‘xpstar90.dll’ version ‘2005.90.3042’ to execute extended stored procedure ‘xp_instance_regread’. This is an informational message only; no user action is required.
03/05/2007 14:13:58,spid52,Unknown,Using ‘xplog70.dll’ version ‘2005.90.3042’ to execute extended stored procedure ‘xp_msver’. This is an informational message only; no user action is required.
03/05/2007 14:13:55,spid5s,Unknown,Recovery is complete. This is an informational message only. No user action is required.
03/05/2007 14:13:55,spid5s,Unknown,Recovery of any in-doubt distributed transactions involving Microsoft Distributed Transaction Coordinator (MS DTC) has completed. This is an informational message only. No user action is required.
03/05/2007 14:13:46,spid13s,Unknown,Service Broker manager has started.
03/05/2007 14:13:46,spid13s,Unknown,The Database Mirroring protocol transport is now listening for connections.
03/05/2007 14:13:46,spid13s,Unknown,Server is listening on [ ‘any’ <ipv4> 5024].
03/05/2007 14:13:46,spid13s,Unknown,The Service Broker protocol transport is disabled or not configured.
03/05/2007 14:13:45,spid9s,Unknown,Starting up database ‘tempdb’.
03/05/2007 14:13:44,spid17s,Unknown,Starting up database ‘small_db’.
03/05/2007 14:13:44,spid15s,Unknown,Starting up database ‘ReportServerTempDB’.
03/05/2007 14:13:44,spid16s,Unknown,Starting up database ‘DB’.
03/05/2007 14:13:44,spid13s,Unknown,Starting up database ‘msdb’.
03/05/2007 14:13:44,spid14s,Unknown,Starting up database ‘ReportServer’.
03/05/2007 14:13:44,spid9s,Unknown,Clearing tempdb database.
03/05/2007 14:13:44,Server,Unknown,SQL Server is now ready for client connections. This is an informational message; no user action is required.
03/05/2007 14:13:44,Server,Unknown,Dedicated admin connection support was established for listening locally on port 1434.
03/05/2007 14:13:44,Server,Unknown,Server is listening on [ 127.0.0.1 <ipv4> 1434].
03/05/2007 14:13:44,Server,Unknown,Server named pipe provider is ready to accept connection on [ \.pipesqlquery ].
03/05/2007 14:13:44,Server,Unknown,Server local connection provider is ready to accept connection on [ \.pipeSQLLocalMSSQLSERVER ].
03/05/2007 14:13:44,Server,Unknown,Server is listening on [ ‘any’ <ipv4> 1433].
03/05/2007 14:13:44,Server,Unknown,A self-generated certificate was successfully loaded for encryption.
03/05/2007 14:13:44,spid5s,Unknown,Server name is ‘MCS-DB1’. This is an informational message only. No user action is required.
03/05/2007 14:13:44,Server,Unknown,Virtual Interface Architecture protocol is not supported for this particular edition of SQL Server.
03/05/2007 14:13:44,spid9s,Unknown,Starting up database ‘model’.
03/05/2007 14:13:44,spid5s,Unknown,The resource database build version is 9.00.3042. This is an informational message only. No user action is required.
03/05/2007 14:13:44,spid5s,Unknown,Starting up database ‘mssqlsystemresource’.
03/05/2007 14:13:44,spid5s,Unknown,SQL Trace ID 1 was started by login "sa".
03/05/2007 14:13:44,spid5s,Unknown,Recovery is writing a checkpoint in database ‘master’ (1). This is an informational message only. No user action is required.
03/05/2007 14:13:44,spid5s,Unknown,Starting up database ‘master’.
03/05/2007 14:13:44,Server,Unknown,Database mirroring has been enabled on this instance of SQL Server.
03/05/2007 14:13:44,Server,Unknown,Attempting to recover in-doubt distributed transactions involving Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
03/05/2007 14:13:43,Server,Unknown,Attempting to initialize Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
03/05/2007 14:13:43,Server,Unknown,Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required.
03/05/2007 14:13:43,Server,Unknown,Set AWE Enabled to 1 in the configuration parameters to allow use of more memory.
03/05/2007 14:13:42,Server,Unknown,Detected 4 CPUs. This is an informational message; no user action is required.
03/05/2007 14:13:42,Server,Unknown,SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
03/05/2007 14:13:42,Server,Unknown,-l C

rogram FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAmastlog.ldf
03/05/2007 14:13:42,Server,Unknown,-e C

rogram FilesMicrosoft SQL ServerMSSQL.1MSSQLLOGERRORLOG
03/05/2007 14:13:42,Server,Unknown,-d C

rogram FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAmaster.mdf
03/05/2007 14:13:42,Server,Unknown,Registry startup parameters:
03/05/2007 14:13:42,Server,Unknown,This instance of SQL Server last reported using a process ID of 1072 at 05.03.2007 14:13:40 (local) 05.03.2007 12:13:40 (UTC). This is an informational message only; no user action is required.
03/05/2007 14:13:42,Server,Unknown,Logging SQL Server messages in file ‘C

rogram FilesMicrosoft SQL ServerMSSQL.1MSSQLLOGERRORLOG’.
03/05/2007 14:13:42,Server,Unknown,Authentication mode is MIXED.
03/05/2007 14:13:42,Server,Unknown,Server process ID is 5604.
03/05/2007 14:13:42,Server,Unknown,All rights reserved.
03/05/2007 14:13:42,Server,Unknown,(c) 2005 Microsoft Corporation.
03/05/2007 14:13:42,Server,Unknown,Microsoft SQL Server 2005 – 9.00.3042.00 (Intel X86) <nl/>Feb 9 2007 22:47:07 <nl/>Copyright (c) 1988-2005 Microsoft Corporation<nl/>Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
Max

It may not be problem with databases being restored from SQl Server 2000. As indicated by error message (if it benign) it is more related to network connectivity.
Are SQL Server part of same domain?
What are SQl Server Startup accounts (Are they domain accounts or local system accounts), If they are local can you convert them into domain accounts or Certficates needs to be used.
Can you Telnet from this server to the remote server. Command Telnet <Server2>, <port Number>., If telnet comes blank there should not be an issue.
Can you disable firewalls on both servers.
Check computers names (Right Click My computer , Properties and Network Name), check if they are of same domain, even if they are try retyping same domain and restart server and then enable mirrroring.
HTH

We are having the same issue with the same error message. What is interesting is that we have five other databases already being mirrored successfully from the same principal server to the same mirror server. Why would this database be different? We do get the mirror to recognize the principal server as the Partner but when we try to have the Principal to recognize the Mirror, this message occurs.

Tupac
Welcome to the forums.
As referred above if you can give more information on the version & service pack level of SQL server that is used it will help, also check whether any non-logged operations are happening on these databases.

We were able to mirror the database by using the workaround given by Microsoft. In essence, you shrink the log file before backing up the DB and log. Also, we had to stop data entry during the DB restore in order to keep the two systems in sync.
There is supposed to be a hot fix that resolves the need to do this but our policies prohibit hot fixes outside of a service pack on production machines.

]]>