SQL Server Performance

Problem with database mirroring

Discussion in 'SQL Server 2005 Database Mirroring' started by sma, Feb 28, 2007.

  1. sma New Member

    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
  2. satya Moderator

    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.
  3. sma New Member

    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?
  4. madhuottapalam New Member

    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
  5. sma New Member

    It not works, the mirroring still not started <img src='/community/emoticons/emotion-6.gif' alt=':(' /><br />There are 3 errors:<br /><br /><b>First:</b><br />Event Type: Error<br />Event Source: MSSQLSERVER<br />Event Category: (2)<br />Event ID: 1479<br />Date: 05.03.2007<br />Time: 10:05:12<br />User: N/A<br />Computer: MCS-DB1<br />Description:<br />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.<br /><br />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><br />Data:<br />0000: c7 05 00 00 10 00 00 00 Ç.......<br />0008: 08 00 00 00 4d 00 43 00 ....M.C.<br />0010: 53 00 2d 00 44 00 42 00 S.-.D.B.<br />0018: 31 00 00 00 00 00 00 00 1.......<br /><br /><b>Second:</b><br />Event Type: Error<br />Event Source: MSSQLSERVER<br />Event Category: (2)<br />Event ID: 1413<br />Date: 05.03.2007<br />Time: 10:05:12<br />User: BALTICCINEMAadministrator<br />Computer: MCS-DB1<br />Description:<br />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.<br /><br />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><br />Data:<br />0000: 85 05 00 00 10 00 00 00 #133;.......<br />0008: 08 00 00 00 4d 00 43 00 ....M.C.<br />0010: 53 00 2d 00 44 00 42 00 S.-.D.B.<br />0018: 31 00 00 00 09 00 00 00 1.......<br />0020: 43 00 69 00 6e 00 65 00 C.i.n.e.<br />0028: 74 00 69 00 78 00 33 00 t.i.x.3.<br />0030: 00 00 .. <br /><br /><b>Third:</b><br />Event Type: Error<br />Event Source: MSSQLSERVER<br />Event Category: (2)<br />Event ID: 1443<br />Date: 05.03.2007<br />Time: 10:05:12<br />User: N/A<br />Computer: MCS-DB1<br />Description:<br />Database mirroring has been terminated for database 'Cinetix3'. This is an informational message only. No user action is required.<br /><br />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><br />Data:<br />0000: a3 05 00 00 10 00 00 00 £.......<br />0008: 08 00 00 00 4d 00 43 00 ....M.C.<br />0010: 53 00 2d 00 44 00 42 00 S.-.D.B.<br />0018: 31 00 00 00 00 00 00 00 1.......<br /><br />May be it helps for problem investigation.<br /><br />Max
  6. sma New Member

    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:program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAmastlog.ldf
    03/05/2007 14:13:42,Server,Unknown,-e C:program FilesMicrosoft SQL ServerMSSQL.1MSSQLLOGERRORLOG
    03/05/2007 14:13:42,Server,Unknown,-d C:program 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:program 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
  7. gurucb New Member

    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
  8. Tupac Sad Day New Member

    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.
  9. satya Moderator

    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.
  10. Tupac Sad Day New Member

    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.

Share This Page