none
Unable to start Mirroring in SQL 2008 R2

    Pregunta

  • I'm thinking this is access related, meaning my primary can not logon to the mirrored server DB successfully which is why the connection is being forcibly terminated by the mirroring server.  I just don't know how to grant my primary servers "Network Service account" access to my mirroring servers database.  Or should I set my services to start-up using either a Local system account or say the Administrator account.
    What am I doing wrong here.  Any help or suggestions would be great. 

    ERROR LOGS
    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://FQDN:5022'
    Error: 1443, Severity: 16, State: 2.
    Database mirroring has been terminated for database 'Vantage'. This is an informational message only.  No user action is required.

    CONFIGURATION OF SQL MACHINES
    Primary and Mirroring Server
    SQL Server 2008R2
    In Workgroup (Not domain)
    Both use Network Service to start (for SQL Service and Agent)

    martes, 06 de marzo de 2012 20:45

Respuestas

  • Thank you for your quick reply Sean.  I actually got it up and running.  It was indeed the SQL service account that was the problem here.  These were the steps that I performed to get things working properly.

    Kevin

    To Establish Mirroring in SQL 2008R2

    1) In a WORKGROUP environment the SQL Service must start using Administrator credentials (with same password) as opposed to NETWORK SERVICE
    NOTE: (The Mirroring wizard will GRANT CONNECT on ENDPOINT automatically to the admin account.)
    2) Set Primary DB's Recovery Model to FULL.
    3) Perform a FULL Backup of DB and Transaction logs on primary to single file *.bak
    5) Restore single *.bak file to Mirroring server in NO RECOVERY mode. (insert check box's to restore both the DB and Transaction logs.)
    NOTE: Select to Overwrite the existing database (WITH REPLACE)
    6) Once the Mirroring Server has been restored walk through the Mirroring wizard to begin synchronization.

    • Marcado como respuesta KAlexH martes, 06 de marzo de 2012 21:49
    martes, 06 de marzo de 2012 21:48

Todas las respuestas

  • Hello,

    Are you using windows authentication or SQL authentication for the mirroring endpoint? If you are using SQL Auth the only way to do this is through certificates, if using windows auth and running as the network service account you'll have to grant the COMPUTER object login rights and connect permissions on the endpoint (or the opposite server).

    Generally when I see that a connection was forcibly closed, I find an issue with an improperly setup firewall which closes the port. Double check your firewall settings (You may not have installed one, but windows has one enabled by default which you may not have rules setup to allow your 5022 port access on the appropriate protocol).

    Let us know a little more information and check into the firewall possibility.

    -Sean

    martes, 06 de marzo de 2012 21:29
  • Thank you for your quick reply Sean.  I actually got it up and running.  It was indeed the SQL service account that was the problem here.  These were the steps that I performed to get things working properly.

    Kevin

    To Establish Mirroring in SQL 2008R2

    1) In a WORKGROUP environment the SQL Service must start using Administrator credentials (with same password) as opposed to NETWORK SERVICE
    NOTE: (The Mirroring wizard will GRANT CONNECT on ENDPOINT automatically to the admin account.)
    2) Set Primary DB's Recovery Model to FULL.
    3) Perform a FULL Backup of DB and Transaction logs on primary to single file *.bak
    5) Restore single *.bak file to Mirroring server in NO RECOVERY mode. (insert check box's to restore both the DB and Transaction logs.)
    NOTE: Select to Overwrite the existing database (WITH REPLACE)
    6) Once the Mirroring Server has been restored walk through the Mirroring wizard to begin synchronization.

    • Marcado como respuesta KAlexH martes, 06 de marzo de 2012 21:49
    martes, 06 de marzo de 2012 21:48