locked
Mirroing issue RRS feed

  • Question


  • I need some assistance in an issue with database mirroring.

    I have a database in mirroring with this initial implementation. A server A (Principal) and a server B (Mirror).

    This same database is in merge replication, Server A is Publisher and Distributor, with a remote server.

    Afterward, there was the need to change the role of the servers, so server A became the mirror and server B the principal. So server B became Publisher and Distributor of the replication, at the same time a third machine was implemented as witness of the mirroring.

    The problem is the following, everything works perfectly, mirroring and replication, but if i have an accidental failover of the principal server (B), the role principal does not pass to server A, however if i have a failover of server A the principal role automatically passes to B.

    Anyone can help me with this issue?

    By the way i'm using SQL Server 2005 SP2

    Thanks.

    Tuesday, August 3, 2010 10:19 PM

All replies

  • Not sure I fully understand your question, so server B is the principal for mirroring and server A is the mirror, you also have server B as publisher and distributor for a third server which is replicating to a third server.  If the mirroring failover occurs and server A becomes the primary is server B still available for the mirroring (best practice in this case would be to use a third server as your distributor in the case of a server outage), for merge replication to identify the failover partner you need to specify the -PublishFailoverPartner agent option for the Snapshot Agent, Merge Agent, SQL Server replication listener, and SQL Merge ActiveX Control.

    Here are three great articles for you to review:

    MSDN: Replication and Database Mirroring - http://msdn.microsoft.com/en-us/library/ms151799.aspx

    ReplicationAnswers.com: Implementing High Availability for Merge Replication in SQL Server 2005 - http://www.replicationanswers.com/MergeHA.asp

    MSDN: Managing Logins and Jobs After Role Switching - http://msdn.microsoft.com/en-us/library/ms191458.aspx


    MCM: Microsoft Certified Master - SQL Server 2008 | MCT: Microsoft Certified Trainer | MCITP: Database Administrator 2008 | MCITP: Database Developer 2008 | MCITP: Database Administrator | MCITP: Database Developer | MCDBA: Microsoft SQL Server 2000
    Wednesday, August 4, 2010 12:28 AM
  • First of all, thanks for your attention.

    In a mirroring i have a Principal (server B), a Mirror (server A) and a witness (server W). the witness acts only as witness at this moment it doesn't have any other role.

    server B is publisher and distributor of a merge replication, the subscriber is a remote server and doesn't have any other role in the process.

    What happens is the following:

    1 . if a fail-over occurs at the principal (server B), server A doesn't becomes principal as it should.

    2 . if i make a manual fail-over in the principal (server B), server A becomes principal and server B mirror

    3 . if a fail-over occurs in the new principal (server A), server B becomes principal and server A mirror.

    I have specified -PublishFailoverPartner agent option for the Snapshot Agent, Merge Agent, SQL Server replication listener, and SQL Merge ActiveX Control.

    In distributor properties i have indicated server A and server B as publishers.

    My problem is point number 1 , is this happening because i have the distributor and the snapshot folder are on server B?

    Wednesday, August 4, 2010 11:20 AM
  • So you are saying something has happened which should cause server B to failover to server A as the primary for mirroring and this is not happening...  correct?  If this is the case you may check what your mirroring time-out is as server B may not have been offline long enough to initiate a failover.  In high-perfromance mode the timeout is always 10 seconds and in high-safety mode the timeout can be adjusted but the default is 10 seconds.

    To change the time-out value (high-safety mode only)

    To view the current time-out value

     

    Here is a great article on setting up alerts for database mirroring events: http://www.mssqltips.com/tip.asp?tip=1564


    MCM: Microsoft Certified Master - SQL Server 2008 | MCT: Microsoft Certified Trainer | MCITP: Database Administrator 2008 | MCITP: Database Developer 2008 | MCITP: Database Administrator | MCITP: Database Developer | MCDBA: Microsoft SQL Server 2000
    Wednesday, August 4, 2010 4:06 PM
  • First of all let me thank you help.

    I've tried to increased and decreased the time-out value, but the result is exactly the same, if there is a fail-over on B it doesn't passes the principal to A.

    I tried the same implementation in a different database and everything word exactly as it should be. the only difference that i have in this databases is that the one that fail-over works OK is not in replication while the one in which the fail-over doesn't work is in replication

    Wednesday, August 4, 2010 11:24 PM
  • How are you failing over the database. I just set up your scenario and everything worked fine, I was able to fail the database back and forth multiple times with transactional replication setup.

    Can you also check your logs and post any errors here.


    MCM: Microsoft Certified Master - SQL Server 2008 | MCT: Microsoft Certified Trainer | MCITP: Database Administrator 2008 | MCITP: Database Developer 2008 | MCITP: Database Administrator | MCITP: Database Developer | MCDBA: Microsoft SQL Server 2000
    Thursday, August 5, 2010 2:42 AM
  • I made the failover by stopping the sql server in server B.

    following are the logs from the three servers on the occasion of the failover

    LOG SERVER B (MIRROR)

    08/06/2010 00:27:04,spid22s,Unknown,The server instance Witness rejected configure request; read its error log file for more information. The reason 1451<c/> and state 3<c/> can be of use for diagnostics by Microsoft. This is a transient error hence retrying the request is likely to succeed. Correct the cause if any and retry.
    08/06/2010 00:27:04,spid22s,Unknown,Error: 1438<c/> Severity: 16<c/> State: 2.
    08/06/2010 00:26:44,spid22s,Unknown,The server instance Witness rejected configure request; read its error log file for more information. The reason 1451<c/> and state 3<c/> can be of use for diagnostics by Microsoft. This is a transient error hence retrying the request is likely to succeed. Correct the cause if any and retry.
    08/06/2010 00:26:44,spid22s,Unknown,Error: 1438<c/> Severity: 16<c/> State: 2.
    08/06/2010 00:26:24,spid22s,Unknown,The server instance Witness rejected configure request; read its error log file for more information. The reason 1451<c/> and state 3<c/> can be of use for diagnostics by Microsoft. This is a transient error hence retrying the request is likely to succeed. Correct the cause if any and retry.
    08/06/2010 00:26:24,spid22s,Unknown,Error: 1438<c/> Severity: 16<c/> State: 2.
    08/06/2010 00:26:04,spid22s,Unknown,The server instance Witness rejected configure request; read its error log file for more information. The reason 1451<c/> and state 3<c/> can be of use for diagnostics by Microsoft. This is a transient error hence retrying the request is likely to succeed. Correct the cause if any and retry.
    08/06/2010 00:26:04,spid22s,Unknown,Error: 1438<c/> Severity: 16<c/> State: 2.
    08/06/2010 00:25:44,spid22s,Unknown,The server instance Witness rejected configure request; read its error log file for more information. The reason 1451<c/> and state 3<c/> can be of use for diagnostics by Microsoft. This is a transient error hence retrying the request is likely to succeed. Correct the cause if any and retry.
    08/06/2010 00:25:44,spid22s,Unknown,Error: 1438<c/> Severity: 16<c/> State: 2.
    08/06/2010 00:25:24,spid22s,Unknown,The server instance Witness rejected configure request; read its error log file for more information. The reason 1451<c/> and state 3<c/> can be of use for diagnostics by Microsoft. This is a transient error hence retrying the request is likely to succeed. Correct the cause if any and retry.
    08/06/2010 00:25:24,spid22s,Unknown,Error: 1438<c/> Severity: 16<c/> State: 2.
    08/06/2010 00:25:04,spid22s,Unknown,The server instance Witness rejected configure request; read its error log file for more information. The reason 1451<c/> and state 3<c/> can be of use for diagnostics by Microsoft. This is a transient error hence retrying the request is likely to succeed. Correct the cause if any and retry.
    08/06/2010 00:25:04,spid22s,Unknown,Error: 1438<c/> Severity: 16<c/> State: 2.
    08/06/2010 00:24:44,spid22s,Unknown,The server instance Witness rejected configure request; read its error log file for more information. The reason 1451<c/> and state 3<c/> can be of use for diagnostics by Microsoft. This is a transient error hence retrying the request is likely to succeed. Correct the cause if any and retry.
    08/06/2010 00:24:44,spid22s,Unknown,Error: 1438<c/> Severity: 16<c/> State: 2.
    08/06/2010 00:24:24,spid22s,Unknown,The server instance Witness rejected configure request; read its error log file for more information. The reason 1451<c/> and state 3<c/> can be of use for diagnostics by Microsoft. This is a transient error hence retrying the request is likely to succeed. Correct the cause if any and retry.
    08/06/2010 00:24:24,spid22s,Unknown,Error: 1438<c/> Severity: 16<c/> State: 2.
    08/06/2010 00:24:04,spid22s,Unknown,The server instance Witness rejected configure request; read its error log file for more information. The reason 1451<c/> and state 3<c/> can be of use for diagnostics by Microsoft. This is a transient error hence retrying the request is likely to succeed. Correct the cause if any and retry.
    08/06/2010 00:24:04,spid22s,Unknown,Error: 1438<c/> Severity: 16<c/> State: 2.
    08/06/2010 00:23:44,spid22s,Unknown,The server instance Witness rejected configure request; read its error log file for more information. The reason 1451<c/> and state 3<c/> can be of use for diagnostics by Microsoft. This is a transient error hence retrying the request is likely to succeed. Correct the cause if any and retry.
    08/06/2010 00:23:44,spid22s,Unknown,Error: 1438<c/> Severity: 16<c/> State: 2.
    08/06/2010 00:23:24,spid22s,Unknown,The server instance Witness rejected configure request; read its error log file for more information. The reason 1451<c/> and state 3<c/> can be of use for diagnostics by Microsoft. This is a transient error hence retrying the request is likely to succeed. Correct the cause if any and retry.
    08/06/2010 00:23:24,spid22s,Unknown,Error: 1438<c/> Severity: 16<c/> State: 2.
    08/06/2010 00:23:04,spid22s,Unknown,The server instance Witness rejected configure request; read its error log file for more information. The reason 1451<c/> and state 3<c/> can be of use for diagnostics by Microsoft. This is a transient error hence retrying the request is likely to succeed. Correct the cause if any and retry.
    08/06/2010 00:23:04,spid22s,Unknown,Error: 1438<c/> Severity: 16<c/> State: 2.
    08/06/2010 00:22:44,spid22s,Unknown,The server instance Witness rejected configure request; read its error log file for more information. The reason 1451<c/> and state 3<c/> can be of use for diagnostics by Microsoft. This is a transient error hence retrying the request is likely to succeed. Correct the cause if any and retry.
    08/06/2010 00:22:44,spid22s,Unknown,Error: 1438<c/> Severity: 16<c/> State: 2.
    08/06/2010 00:22:24,spid22s,Unknown,The server instance Witness rejected configure request; read its error log file for more information. The reason 1451<c/> and state 3<c/> can be of use for diagnostics by Microsoft. This is a transient error hence retrying the request is likely to succeed. Correct the cause if any and retry.
    08/06/2010 00:22:24,spid22s,Unknown,Error: 1438<c/> Severity: 16<c/> State: 2.
    08/06/2010 00:22:04,spid22s,Unknown,The server instance Witness rejected configure request; read its error log file for more information. The reason 1451<c/> and state 3<c/> can be of use for diagnostics by Microsoft. This is a transient error hence retrying the request is likely to succeed. Correct the cause if any and retry.
    08/06/2010 00:22:04,spid22s,Unknown,Error: 1438<c/> Severity: 16<c/> State: 2.
    08/06/2010 00:21:44,spid22s,Unknown,The server instance Witness rejected configure request; read its error log file for more information. The reason 1451<c/> and state 3<c/> can be of use for diagnostics by Microsoft. This is a transient error hence retrying the request is likely to succeed. Correct the cause if any and retry.
    08/06/2010 00:21:44,spid22s,Unknown,Error: 1438<c/> Severity: 16<c/> State: 2.
    08/06/2010 00:21:24,spid22s,Unknown,The server instance Witness rejected configure request; read its error log file for more information. The reason 1451<c/> and state 3<c/> can be of use for diagnostics by Microsoft. This is a transient error hence retrying the request is likely to succeed. Correct the cause if any and retry.
    08/06/2010 00:21:24,spid22s,Unknown,Error: 1438<c/> Severity: 16<c/> State: 2.
    08/06/2010 00:21:04,spid22s,Unknown,The server instance Witness rejected configure request; read its error log file for more information. The reason 1451<c/> and state 3<c/> can be of use for diagnostics by Microsoft. This is a transient error hence retrying the request is likely to succeed. Correct the cause if any and retry.
    08/06/2010 00:21:04,spid22s,Unknown,Error: 1438<c/> Severity: 16<c/> State: 2.
    08/06/2010 00:20:44,spid25s,Unknown,Database mirroring connection error 4 'An error occurred while receiving data: '10054(error not found)'.' for 'TCP://srd-server2:10112'.
    08/06/2010 00:20:44,spid25s,Unknown,Error: 1474<c/> Severity: 16<c/> State: 1.
    08/06/2010 00:20:44,spid22s,Unknown,Database mirroring is inactive for database 'soe001'. This is an informational message only. No user action is required.
    08/06/2010 00:20:43,spid24s,Unknown,Database mirroring connection error 4 'An error occurred while receiving data: '64(error not found)'.' for 'TCP://srd-server2:10112'.
    08/06/2010 00:20:43,spid24s,Unknown,Error: 1474<c/> Severity: 16<c/> State: 1.
    08/06/2010 00:20:18,spid51,Unknown,Using 'xplog70.dll' version '2005.90.1399' to execute extended stored procedure 'xp_msver'. This is an informational message only; no user action is required.
    08/06/2010 00:20:17,spid51,Unknown,Using 'xpsqlbot.dll' version '2005.90.1399' to execute extended stored procedure 'xp_qv'. This is an informational message only; no user action is required.
    08/06/2010 00:20:17,spid51,Unknown,Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install.
    08/06/2010 00:20:17,spid51,Unknown,Configuration option 'Agent XPs' changed from 0 to 1. Run the RECONFIGURE statement to install.
    08/06/2010 00:20:16,spid51,Unknown,Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
    08/06/2010 00:13:54,spid22s,Unknown,Database mirroring is active with database 'soe001' as the mirror copy. This is an informational message only. No user action is required.

    LOG SERVER B (PRINCIPAL)

    08/06/2010 00:30:11,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.
    08/06/2010 00:30:11,spid52,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.
    08/06/2010 00:30:10,spid52,Unknown,Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install.
    08/06/2010 00:30:10,spid52,Unknown,Configuration option 'Agent XPs' changed from 0 to 1. Run the RECONFIGURE statement to install.
    08/06/2010 00:30:10,spid52,Unknown,Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
    08/06/2010 00:29:59,spid5s,Unknown,Launched startup procedure 'sp_MScleanupmergepublisher'.
    08/06/2010 00:29:59,spid5s,Unknown,Launched startup procedure 'sp_MSrepl_startup'.
    08/06/2010 00:29:59,spid20s,Unknown,Database mirroring is active with database 'SOE001' as the principal copy. This is an informational message only. No user action is required.
    08/06/2010 00:29:59,spid5s,Unknown,Recovery is complete. This is an informational message only. No user action is required.
    08/06/2010 00:29:59,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.
    08/06/2010 00:29:57,spid51,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.
    08/06/2010 00:29:56,spid21s,Unknown,Service Broker manager has started.
    08/06/2010 00:29:56,spid21s,Unknown,The Database Mirroring protocol transport is now listening for connections.
    08/06/2010 00:29:56,spid21s,Unknown,Server is listening on [ 'any' <ipv4> 10112].
    08/06/2010 00:29:56,spid21s,Unknown,The Service Broker protocol transport is disabled or not configured.
    08/06/2010 00:29:56,spid19s,Unknown,Starting up database 'iBERIA_INTERFACES'.
    08/06/2010 00:29:56,spid18s,Unknown,Starting up database 'distribution'.
    08/06/2010 00:29:56,spid17s,Unknown,Starting up database 'DocumentosWord'.
    08/06/2010 00:29:56,spid16s,Unknown,Starting up database 'SOE001'.
    08/06/2010 00:29:56,spid15s,Unknown,Starting up database 'ReportServerTempDB'.
    08/06/2010 00:29:56,spid13s,Unknown,Starting up database 'msdb'.
    08/06/2010 00:29:56,spid14s,Unknown,Starting up database 'ReportServer'.
    08/06/2010 00:29:56,Server,Unknown,SQL Server is now ready for client connections. This is an informational message; no user action is required.
    08/06/2010 00:29:56,Server,Unknown,Dedicated admin connection support was established for listening locally on port 1434.
    08/06/2010 00:29:56,Server,Unknown,Server is listening on [ 127.0.0.1 <ipv4> 1434].
    08/06/2010 00:29:56,Server,Unknown,Server named pipe provider is ready to accept connection on [ \\.\pipe\sql\query ].
    08/06/2010 00:29:56,Server,Unknown,Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\MSSQLSERVER ].
    08/06/2010 00:29:56,Server,Unknown,Server is listening on [ 'any' <ipv4> 1433].
    08/06/2010 00:29:56,Server,Unknown,A self-generated certificate was successfully loaded for encryption.
    08/06/2010 00:29:56,spid9s,Unknown,Starting up database 'tempdb'.
    08/06/2010 00:29:55,spid9s,Unknown,Clearing tempdb database.
    08/06/2010 00:29:55,spid5s,Unknown,Server name is 'SRD-SERVER2'. This is an informational message only. No user action is required.
    08/06/2010 00:29:55,spid9s,Unknown,Starting up database 'model'.
    08/06/2010 00:29:55,spid5s,Unknown,The resource database build version is 9.00.3042. This is an informational message only. No user action is required.
    08/06/2010 00:29:55,spid5s,Unknown,Starting up database 'mssqlsystemresource'.
    08/06/2010 00:29:55,spid5s,Unknown,SQL Trace ID 1 was started by login "sa".
    08/06/2010 00:29:55,spid5s,Unknown,Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
    08/06/2010 00:29:55,spid5s,Unknown,Starting up database 'master'.
    08/06/2010 00:29:55,Server,Unknown,Database mirroring has been enabled on this instance of SQL Server.
    08/06/2010 00:29:55,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.
    08/06/2010 00:29:54,Server,Unknown,Attempting to initialize Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
    08/06/2010 00:29:54,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.
    08/06/2010 00:29:54,Server,Unknown,Set AWE Enabled to 1 in the configuration parameters to allow use of more memory.
    08/06/2010 00:29:54,Server,Unknown,Detected 2 CPUs. This is an informational message; no user action is required.
    08/06/2010 00:29:54,Server,Unknown,SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
    08/06/2010 00:29:54,Server,Unknown,-l C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf
    08/06/2010 00:29:54,Server,Unknown,-e C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG
    08/06/2010 00:29:54,Server,Unknown,-d C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf
    08/06/2010 00:29:54,Server,Unknown,Registry startup parameters:
    08/06/2010 00:29:54,Server,Unknown,This instance of SQL Server last reported using a process ID of 1316 at 8/6/2010 12:20:45 AM (local) 8/5/2010 11:20:45 PM (UTC). This is an informational message only; no user action is required.
    08/06/2010 00:29:54,Server,Unknown,Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'.
    08/06/2010 00:29:54,Server,Unknown,Authentication mode is MIXED.
    08/06/2010 00:29:54,Server,Unknown,Server process ID is 8032.
    08/06/2010 00:29:54,Server,Unknown,All rights reserved.
    08/06/2010 00:29:54,Server,Unknown,(c) 2005 Microsoft Corporation.
    08/06/2010 00:29:54,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 2)
    08/06/2010 00:20:45,spid5s,Unknown,SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.
    08/06/2010 00:20:45,spid5s,Unknown,SQL Server is terminating in response to a 'stop' request from Service Control Manager. This is an informational message only. No user action is required.
    08/06/2010 00:20:44,spid15s,Unknown,Database mirroring is inactive for database 'SOE001'. This is an informational message only. No user action is required.
    08/06/2010 00:20:44,spid12s,Unknown,Service Broker manager has shut down.
    08/06/2010 00:20:44,spid12s,Unknown,The Database Mirroring protocol transport has stopped listening for connections.
    08/06/2010 00:20:42,spid51,Unknown,Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install.
    08/06/2010 00:20:42,spid51,Unknown,Configuration option 'Agent XPs' changed from 1 to 0. Run the RECONFIGURE statement to install.
    08/06/2010 00:20:42,spid51,Unknown,Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
    08/06/2010 00:20:06,spid51,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.
    08/06/2010 00:20:06,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.
    08/06/2010 00:20:05,spid51,Unknown,Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install.
    08/06/2010 00:20:05,spid51,Unknown,Configuration option 'Agent XPs' changed from 0 to 1. Run the RECONFIGURE statement to install.
    08/06/2010 00:20:05,spid51,Unknown,Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
    08/06/2010 00:13:55,spid15s,Unknown,Database mirroring is active with database 'SOE001' as the principal copy. This is an informational message only. No user action is required.

    LOG SERVER WITNESS

    08/06/2010 00:20:25,spid15s,Unknown,Database mirroring connection error 4 'An error occurred while receiving data: '64(O nome de rede especificado já não está disponível.)'.' for 'TCP://srd-server2.SOERAD.PT:10112'.
    08/06/2010 00:20:25,spid15s,Unknown,Error: 1474<c/> Severity: 16<c/> State: 1.
    08/06/2010 00:20:25,spid15s,Unknown,Database mirroring connection error 4 'An error occurred while receiving data: '64(O nome de rede especificado já não está disponível.)'.' for 'TCP://srd-server2.SOERAD.PT:10112'.
    08/06/2010 00:20:25,spid15s,Unknown,Error: 1474<c/> Severity: 16<c/> State: 1.

    Thursday, August 5, 2010 11:58 PM
  • Verify in Database Mirroring Monitor that everything is functioning properly before you fail over.  It looks as though you have an issue with the witness, possibly endpoint permissions.

    You can verify permissions with this:

    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

    Here is a script to set up mirroring with a witness using certificates, since I am unsure what account you are using (You will need to modify the directory to store your certificate backups or create a directory of C:\CERTS on the servers and copy the certificates between the servers. To create the C:\CERTS directory you can run this command from SQLCMD Mode !!mkdir C:\Certs):

    -- =====================================
    -- Mirroring with Certificates Template
    -- =====================================
    -- CONFIGURE OUTBOUOND CONNECTIONS
    ---- ON PRINCIPAL SERVER - PRINCIPAL
    USE master;
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssword1';
    GO
    USE master;
    CREATE CERTIFICATE PRINCIPAL_cert
       WITH SUBJECT = 'PRINCIPAL certificate';
    GO
    CREATE ENDPOINT Endpoint_Mirroring
       STATE = STARTED
       AS TCP (
          LISTENER_PORT=7024
          , LISTENER_IP = ALL
       )
       FOR DATABASE_MIRRORING (
          AUTHENTICATION = CERTIFICATE PRINCIPAL_cert
          , ENCRYPTION = REQUIRED ALGORITHM AES
          , ROLE = ALL
       );
    GO
    BACKUP CERTIFICATE PRINCIPAL_cert TO FILE = 'C:\CERTS\PRINCIPAL_cert.cer';
    GO

    ---- ON MIRROR SERVER - MIRROR
    USE master;
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssword1';
    GO
    CREATE CERTIFICATE MIRROR_cert
       WITH SUBJECT = 'MIRROR certificate for database mirroring';
    GO
    CREATE ENDPOINT Endpoint_Mirroring
       STATE = STARTED
       AS TCP (
          LISTENER_PORT=7024
          , LISTENER_IP = ALL
       )
       FOR DATABASE_MIRRORING (
          AUTHENTICATION = CERTIFICATE MIRROR_cert
          , ENCRYPTION = REQUIRED ALGORITHM AES
          , ROLE = ALL
       );
    GO
    BACKUP CERTIFICATE MIRROR_cert TO FILE = 'C:\CERTS\MIRROR_cert.cer';
    GO

    ---- ON WITNESS SERVER - WITNESS
    USE master;
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssword1';
    GO
    USE master;
    CREATE CERTIFICATE WITNESS_cert
       WITH SUBJECT = 'WITNESS certificate';
    GO
    CREATE ENDPOINT Endpoint_Mirroring
       STATE = STARTED
       AS TCP (
          LISTENER_PORT=7024
          , LISTENER_IP = ALL
       )
       FOR DATABASE_MIRRORING (
          AUTHENTICATION = CERTIFICATE WITNESS_cert
          , ENCRYPTION = REQUIRED ALGORITHM AES
          , ROLE = WITNESS
       );
    GO
    BACKUP CERTIFICATE WITNESS_cert TO FILE = 'C:\CERTS\WITNESS_cert.cer';
    GO

    -- Configure Inbound Connections
    ---- ON PRINCIPAL SERVER PRINCIPAL
    USE master;
    CREATE LOGIN MIRROR_login WITH PASSWORD = 'P@ssword1';
    GO
    CREATE USER MIRROR_user FOR LOGIN MIRROR_login;
    GO
    CREATE CERTIFICATE MIRROR_cert
       AUTHORIZATION MIRROR_user
       FROM FILE = 'C:\CERTS\MIRROR_cert.cer'
    GO
    GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [MIRROR_login];
    GO

    USE master;
    CREATE LOGIN WITNESS_login WITH PASSWORD = 'P@ssword1';
    GO
    CREATE USER WITNESS_user FOR LOGIN WITNESS_login;
    GO
    CREATE CERTIFICATE WITNESS_cert
       AUTHORIZATION WITNESS_user
       FROM FILE = 'C:\CERTS\WITNESS_cert.cer'
    GO
    GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [WITNESS_login];
    GO

    ---- ON MIRROR SERVER MIRROR
    USE master;
    CREATE LOGIN PRINCIPAL_login WITH PASSWORD = 'P@ssword1';
    GO
    CREATE USER PRINCIPAL_user FOR LOGIN PRINCIPAL_login;
    GO
    CREATE CERTIFICATE PRINCIPAL_cert
       AUTHORIZATION PRINCIPAL_user
       FROM FILE = 'C:\Certs\PRINCIPAL_cert.cer'
    GO
    GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [PRINCIPAL_login];
    GO

    USE master;
    CREATE LOGIN WITNESS_login WITH PASSWORD = 'P@ssword1';
    GO
    CREATE USER WITNESS_user FOR LOGIN WITNESS_login;
    GO
    CREATE CERTIFICATE WITNESS_cert
       AUTHORIZATION WITNESS_user
       FROM FILE = 'C:\CERTS\WITNESS_cert.cer'
    GO
    GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [WITNESS_login];
    GO

    ---- ON WITNESS SERVER WITNESS
    ------ LOGIN FOR PRINCIPLE SERVER
    USE master;
    CREATE LOGIN PRINCIPAL_login WITH PASSWORD = 'P@ssword1';
    GO
    CREATE USER PRINCIPAL_user FOR LOGIN PRINCIPAL_login;
    GO
    CREATE CERTIFICATE PRINCIPAL_cert
       AUTHORIZATION PRINCIPAL_user
       FROM FILE = 'C:\CERTS\PRINCIPAL_cert.cer'
    GO
    GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [PRINCIPAL_login];
    GO
    ------ LOGIN FOR MIRROR SERVER
    USE master;
    CREATE LOGIN MIRROR_login WITH PASSWORD = 'P@ssword1';
    GO
    CREATE USER MIRROR_user FOR LOGIN MIRROR_login;
    GO
    CREATE CERTIFICATE MIRROR_cert
       AUTHORIZATION MIRROR_user
       FROM FILE = 'C:\CERTS\MIRROR_cert.cer'
    GO
    GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [MIRROR_login];
    GO

    -- NOW RESTORE YOUR DATABASE AND TRANSACTION LOG BACKUPS TO THE MIRROR WITH 'NORECOVERY'

    -- CONFIGURE MIRRORING PARTNERS
    ---- ON MIRROR SERVER MIRROR
    ALTER DATABASE MIRRORDB
        SET PARTNER = 'TCP://PRINCIPAL.DOMAIN.com:7024';
    GO

    ---- ON PRINCIPAL SERVER PRINCIPAL
    ALTER DATABASE MIRRORDB
        SET PARTNER = 'TCP://MIRROR.DOMAIN.com:7024';
    GO
    ALTER DATABASE MIRRRORDB
        SET WITNESS = 'TCP://WITNESS.DOMAIN.com:7024';
    GO

     


    MCM: Microsoft Certified Master - SQL Server 2008 | MCT: Microsoft Certified Trainer | MCITP: Database Administrator 2008 | MCITP: Database Developer 2008 | MCITP: Database Administrator | MCITP: Database Developer | MCDBA: Microsoft SQL Server 2000
    • Proposed as answer by Tom Li - MSFT Friday, August 6, 2010 2:15 AM
    Friday, August 6, 2010 1:48 AM
  • I'm using the accounts of the domain and every machine is under the same domain The permissions i have are this

    the only issue here is that that endpoint named MirroringEndPoint_Mirror is on the principal and the endpoint MirroringEndPoint_Principle is the mirror

    MirroringEndPoint_Mirror       G    soerad\administrador    CO      NT AUTHORITY\ANONYMOUS LOGON

    MirroringEndPoint_Principle    G    SOERAD\administrador    CO      NT AUTHORITY\ANONYMOUS LOGON

    MirroringEndPoint_Witness    G    SOERAD\Administrador    CO      NT AUTHORITY\ANONYMOUS LOGON

     

    I´ve tried to establish the mirror in another database in the same servers and the same endpoints and everything works fine, it doesn't matter how i make the failover that the mirroring works perfectly. the only difference is that the new database is not on replication.

    Friday, August 6, 2010 10:33 AM
  • This is the error that concerns me:

    08/06/2010 00:21:04,spid22s,Unknown,The server instance Witness rejected configure request; read its error log file for more information. The reason 1451<c/> and state 3<c/> can be of use for diagnostics by Microsoft. This is a transient error hence retrying the request is likely to succeed. Correct the cause if any and retry.

    Error |  Severity | Event logged | Description (message text)

    1451  | 16          | No                | Database mirroring information was not found in the system table.

    I would set up mirroring again from scratch and try this over as something seems to be incorrect.  If you have a database that is failing over correctly currently then set up replication for this database and see if everything works fine.
     


    MCM: Microsoft Certified Master - SQL Server 2008 | MCT: Microsoft Certified Trainer | MCITP: Database Administrator 2008 | MCITP: Database Developer 2008 | MCITP: Database Administrator | MCITP: Database Developer | MCDBA: Microsoft SQL Server 2000
    Saturday, August 7, 2010 1:02 AM
  • OK, thanks i will try it and tell you something
    Sunday, August 8, 2010 12:23 AM
  • Hello, here i am again,

    I've tried what you told me , I've put other database in the same conditions, merge replication and then mirrroring, this is what i had found.

    - In the first implementation I've made the replication and them mirroring in this order. Server A - Principal, Server B - Mirror. but i had the need to change the order of the servers, Server  B became Principal and Server A became Mirror, at the same time I've introduced a Server W - Witness.

    In this implementation the failover from Server B to Server A was not working correctly, however the failover from Server A to Server B was OK.

     

    - Then, I tried to create the mirroring with same configuration in another database  but without replication. Failover worked correctly in both directions. Server B to Server A, and Server A to Server B.

     

    - At last, I've  tried with the previous database the first inplementation, (merge replication and mirroring), but I've created like this: Server B (Principal), Server A (Mirror), Server W (Witness). and I've found that if I failover the principal from Server B (Principal) to Server A (Mirror) was working fine, but the failover from Server A (Mirror) to Server B (Principal) didn´t worked.

     

    - I've removed the mirroring from the first database (this is a production database,) the replication remained unchanged, redone the mirroring, but this time with this change Server B was create as Principal, Server A as Mirror and Server W  as Witness, (like in the test database), exactly as in the test database, if I failover the principal from Server B (Principal) to Server A (Mirror) works fine, but the failover from Server A (Mirror) to Server B (Principal) doesn´t work.

     

    For the moment I can live with that, but the only explanation that I think of, is that something in the replication is preventing the correct failover in both directions.

    If you have any idea about this, I appreciate your opinion. Anyway I wish to thank you for your help and for your time.

    Thanks

    Friday, August 13, 2010 9:48 AM