locked
Why SQL Server 2005 database mirror Can't failover? RRS feed

  • Question

  •            

     

    Hello All,

     

    I've tried to replicated exactly what it says in the online documentation.  At the end of the post are the SQL statements issued.

     

    1. Enable encrypted outbound connections on the primary server

    2. Enable encrypted outbound connections on the mirror server

    3. Enable encrypted outbound connections on the witness server

    4. Enable encrypted inbound connections on the primary server

    5. Enable encrypted inbound connections on the mirror server

    6. Enable encrypted inbound connections on the witness server

    7. Set mirror''s partner to the primary

    8. Set primary''s partner the mirror

    9. Set principal’s witness.

    Every step run''s normally,but why can''t auto failover.

     

    I used the following Script:

     

    /* -------- 1. ENABLE OUTBOUND CONNECTIONS ON THE PRIMARY -------- */

    use master;

    go

    DROP USER PrincipalLogin1

    DROP LOGIN PrincipalLogin1

    DROP USER PrincipalLogin2

    DROP LOGIN PrincipalLogin2

    GO

     

    Create login PrincipalLogin1

    with password='PrincipalLogin'

    go

    Create user PrincipalLogin1

    from login PrincipalLogin1

    go

     

    Grant connect on endpoint::Principal_Endpoint_Mirroring to PrincipalLogin1

    go

    ---stop

     

    create certificate MirrorCertForPartnerPub

    authorization PrincipalLogin1

    from file = 'd:\BackupCert\MirrorCertForPartner.cer';

    go

     

    Create login PrincipalLogin2

    with password='PrincipalLogin'

    go

    Create user PrincipalLogin2

    from login PrincipalLogin2

    go

     

    Grant connect on endpoint::Principal_Endpoint_Mirroring to PrincipalLogin2

    go

    create certificate WintnessCertForPartnerPub

    authorization PrincipalLogin2

    from file ='D:\BackupCert\CertForDBRole.cer'

    GO

     

     

     

    ALTER DATABASE Mirrortest

          SET PARTNER = 'TCP://10.1.40.158:5022'

    GO

     

    ALTER DATABASE Mirrortest

          SET witness = 'TCP://10.10.11.30:7000'

    GO

     

    select * from sys.certificates

     

     

     

    /* -------- 2. ENABLE OUTBOUND CONNECTIONS ON THE MIRROR -------- */

    --On Mirror

    --Remove the database

    --Connect to either partner.

    --Issue the following Transact-SQL statement:

     

    ALTER DATABASE Mirrortest SET PARTNER OFF

     

    --Optionally, you can recover the former mirror database. On the server instance that was the mirror server, enter:

     

    --RESTORE DATABASE  Mirrortest WITH RECOVERY;

     

    RESTORE DATABASE [Mirrortest] FROM 

    DISK = N'D:\BackupCert\Mirrortest.bak'

    WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  REPLACE,  STATS = 10

    GO

    select * from sys.endpoints

     

    drop endpoint Mirror_Endpoint_Mirroring

     

    select * from sys.certificates

     

     

    Create certificate MirrorCertForPartner

    with subject ='this is the certificate for mirror',

    start_date='06/25/2006'

     

    backup certificate MirrorCertForPartner

    to file='d:\backupcert\MirrorCertForPartner.cer'

     

     

     

    CREATE ENDPOINT Mirror_Endpoint_Mirroring

          STATE=STARTED

          AS TCP (LISTENER_PORT=5022)

          FOR DATABASE_MIRRORING (

    Authentication=Certificate MirrorCertForPartner

    ,ROLE=ALL)

    GO

     

    /* -------- 3. ENABLE OUTBOUND CONNECTIONS ON THE WINTESS -------- */

     

    --On Witness

     

    USE [master]

    GO

     

     

    select * from sys.certificates

     

    drop certificate CertForDBRole

    GO

     

     

    create master key

    encryption by password='asdf'

     

    Create certificate CertForDBRole

    with subject='this is a certificate for pricipal role',

    start_date='06/25/2006'

     

    backup certificate CertForDBrole

    to file='c:\CertForDBRole.cer'

     

     

    select * from sys.endpoints

     

    drop endpoint Witness_Endpoint_Mirroring

     

     

    CREATE ENDPOINT Witness_Endpoint_Mirroring

          STATE=STARTED

          AS TCP (LISTENER_PORT=7000)

          FOR DATABASE_MIRRORING (

    Authentication=Certificate CertForDBRole

    ,ROLE=ALL)

    GO

     

     

    /* -------- 4. ENABLE INBOUND CONNECTIONS ON THE PRIMARY -------- */

    use master;

    go

    DROP USER PrincipalLogin1

    DROP LOGIN PrincipalLogin1

    DROP USER PrincipalLogin2

    DROP LOGIN PrincipalLogin2

    GO

     

    Create login PrincipalLogin1

    with password='PrincipalLogin'

    go

    Create user PrincipalLogin1

    from login PrincipalLogin1

    go

     

    Grant connect on endpoint::Principal_Endpoint_Mirroring to PrincipalLogin1

    go

    ---stop

     

    create certificate MirrorCertForPartnerPub

    authorization PrincipalLogin1

    from file = 'd:\BackupCert\MirrorCertForPartner.cer';

    go

     

    Create login PrincipalLogin2

    with password='PrincipalLogin'

    go

    Create user PrincipalLogin2

    from login PrincipalLogin2

    go

     

    Grant connect on endpoint::Principal_Endpoint_Mirroring to PrincipalLogin2

    go

    create certificate WintnessCertForPartnerPub

    authorization PrincipalLogin2

    from file ='D:\BackupCert\CertForDBRole.cer'

    GO

     

     

     

    ALTER DATABASE Mirrortest

          SET PARTNER = 'TCP://10.1.40.158:5022'

    GO

     

    ALTER DATABASE Mirrortest

          SET witness = 'TCP://10.10.11.30:7000'

    GO

     

    select * from sys.certificates

     

     

    /* -------- 5. ENABLE INBOUND CONNECTIONS ON THE MIRROR -------- */

    use master;

    go

    DROP USER MirrorLogin1

    DROP LOGIN MirrorLogin1

    DROP USER MirrorLogin2

    DROP LOGIN MirrorLogin2

    GO

     

    Create login MirrorLogin1

    with password='MirrorLogin'

    go

    Create user MirrorLogin1

    from login MirrorLogin1

    go

     

    Grant connect on endpoint::Mirror_Endpoint_Mirroring to MirrorLogin1

    go

     

    --stop

    create certificate PrincipalCertForPartnerPub

    authorization MirrorLogin1

    from file = 'd:\BackupCert\PrincipalCertForPartner.cer';

    go

     

    Create login MirrorLogin2

    with password='MirrorLogin'

    go

    Create user MirrorLogin2

    from login MirrorLogin2

    go

    create certificate WitnessCertForPartnerPub

    authorization MirrorLogin2

    from file ='D:\BackupCert\CertForDBRole.cer'

    GO

     

    Grant connect on endpoint::Mirror_Endpoint_Mirroring to MirrorLogin2

    go

     

     

    ALTER DATABASE Mirrortest

          SET PARTNER = 'TCP://10.10.116.42:5022'

    GO

     

    ALTER DATABASE Mirrortest

          SET WITNESS  = 'TCP://w7mis02:5024'

    GO

     

     

     

     

    /* -------- 6. ENABLE INBOUND CONNECTIONS ON THE WITNESS -------- */

     

    use master;

    go

    DROP USER WitnessLogin1

    drop login WitnessLogin1

    DROP USER WitnessLogin2

    drop login WitnessLogin2

     

    Create login WitnessLogin1

    with password='WitnessLogi*n@'

    go

    create user WitnessLogin1

    from login WitnessLogin1

    go

     

    create  certificate PrincipalCertForPartnerPub

    authorization WitnessLogin1

    from file = 'c:\backupcert\PrincipalCertForPartner.cer';

    go

    --***************************************

    Create login WitnessLogin2

    with password='WitnessLogi*n@'

    go

    create user WitnessLogin2

    from login WitnessLogin2

    go

     

    create certificate MirrorCertForPartnerPub

    authorization WitnessLogin2

    from file = 'c:\backupcert\MirrorCertForPartner.cer';

    go

     

    Grant connect on endpoint::Witness_Endpoint_Mirroring to WitnessLogin1

    go

    Grant connect on endpoint::Witness_Endpoint_Mirroring to WitnessLogin2

    go

     

    All runs normally,But how can't be auto failover?

     

    Wednesday, June 28, 2006 4:55 AM

Answers

  • Without a witness configured, you can't get an automatic failover.  In fact, if the mirror were to disconnect, the principal would also go offline.  If the principal goes offline, the mirror will not promote itself.

    The error log on the witness should contain error messages related to why the configuration could not be completed.  The basic issue with configuration almost always boils down to security in some form or another.

    Wednesday, July 5, 2006 6:55 AM

All replies

  •  

    Hi - you've listed the steps for configuring the server side, but not the client side. The client side is what re-directs the application such that it can re-connect when there is a failure that causes a server failover. Can you post the test case steps for failover? For example, you configure the above and it shows a "synchronized state" and you next pull the plug on the principal and the mirror does not become the principal?

     

    Matt Hollingsworth

    SQL Server High Availability

    Wednesday, June 28, 2006 4:57 PM
  • Hi Matt,

    Thank you very much for your reply.

    when principal server and mirror server in "synchronized state",I stop the principal sql server service,but the Mirror server can't become the principal.they can't change their role except I click the Failover in management studio.

     

    Best Regards,

    David

    Wednesday, June 28, 2006 6:27 PM
  • Hi ALL,

    when I stop the principal sql server service,the mirror instance show:"Mirror,Disconnected/In Recovery" instead of "Mirror, Synchronizing/Restoring"

     

    When I check the windows log,I fount the following error on Mirror instance:

    the server instance Witness rejected configure request;read its error log file for more information,the reason 1451, and state 3,can be of use for diagnostics by Mircrosoft,this is the transient error hence retrying the request is likely to sueeccd,Correct the cause if any and retry.

     

    and the error in witness:

    Database mirroring connection error 4'An error occurred while receiving data:' 64(error not found)',' for 'Tcp://w1mis55:5022'

     

    wamis55 is the pricipal server.

     

    Thanks.

     

    Wednesday, June 28, 2006 7:11 PM
  • Without a witness configured, you can't get an automatic failover.  In fact, if the mirror were to disconnect, the principal would also go offline.  If the principal goes offline, the mirror will not promote itself.

    The error log on the witness should contain error messages related to why the configuration could not be completed.  The basic issue with configuration almost always boils down to security in some form or another.

    Wednesday, July 5, 2006 6:55 AM