locked
SQL Server 2005 Mirrorring configured. Needs a account change and restart service.. RRS feed

  • Question

  • Hi,

    I have SQL 2005 Mirroring enabled. I need to change the SQL Server service to a domain account, it is using Local System account right now, need to change it to a domain account to run some of my jobs successfully. To change it to a domain account, it needs a restart.  If I restart, will it effect my mirroring? Can someone point me to docs what all I should be taking care of for doing this process?

    Thanks in advance.

    Suresh.

    Tuesday, August 3, 2010 6:42 PM

Answers

  • Hi Suresh,

    f you configured database mirroring with safety FULL, you can switch to the mirror server and make it available to the clients while you perform hardware or software maintenance on the principal. 

    1. Perform the hardware or software change on the mirror server first. If you need to restart the SQL Server service on the mirror or restart the mirror server, you can do so. As soon as the mirror database comes back up, the mirroring session is re-established automatically and the mirror starts synchronizing itself against the principal. (Note that the principal is exposed for the duration for which the mirror database is down, or up but not yet fully synchronized with the principal.)

    2. Once the mirroring state becomes SYNCHRONIZED, switch the principal and mirror (by executing the ALTER DATABASE FAILOVER command). Now the applications can connect to the new principal database and continue service. Open or in-flight transactions will be rolled back while switching the principal and mirror. Depending on the type of application, it is recommended that you stop the application for the brief moment of manual failover and restart after the failover succeeds. In this case, please note that you will need to point your application to the new principal database server. You can now perform the hardware and software changes on the old principal server. As soon as you are done with the change and the database becomes available, it automatically re-establishes the mirroring session and assumes the role of the mirror.

    3. If you have a witness server, you can now perform the required changes on that server.

    4. You have two options—either leave the principal and mirror alone, or switch the principal and mirror (by executing the ALTER DATABASE FAILOVER command) after the mirroring state becomes SYNCHRONIZED. Assuming the mirror has the same capacity as the principal, it is recommended that you leave the database there since switching the principal and mirror may cause another reconnect for the application.

    Thursday, August 5, 2010 3:45 AM

All replies

  • Hi

    I have not tested it by myself, but you can pause mirroring and and enable after restarting. But I think it does not afffect mirroring


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, August 4, 2010 6:52 AM
  • Thanks Uri, this is what I was thinking. Will do this in a week or so.
    Wednesday, August 4, 2010 5:29 PM
  • Hi Suresh,

    f you configured database mirroring with safety FULL, you can switch to the mirror server and make it available to the clients while you perform hardware or software maintenance on the principal. 

    1. Perform the hardware or software change on the mirror server first. If you need to restart the SQL Server service on the mirror or restart the mirror server, you can do so. As soon as the mirror database comes back up, the mirroring session is re-established automatically and the mirror starts synchronizing itself against the principal. (Note that the principal is exposed for the duration for which the mirror database is down, or up but not yet fully synchronized with the principal.)

    2. Once the mirroring state becomes SYNCHRONIZED, switch the principal and mirror (by executing the ALTER DATABASE FAILOVER command). Now the applications can connect to the new principal database and continue service. Open or in-flight transactions will be rolled back while switching the principal and mirror. Depending on the type of application, it is recommended that you stop the application for the brief moment of manual failover and restart after the failover succeeds. In this case, please note that you will need to point your application to the new principal database server. You can now perform the hardware and software changes on the old principal server. As soon as you are done with the change and the database becomes available, it automatically re-establishes the mirroring session and assumes the role of the mirror.

    3. If you have a witness server, you can now perform the required changes on that server.

    4. You have two options—either leave the principal and mirror alone, or switch the principal and mirror (by executing the ALTER DATABASE FAILOVER command) after the mirroring state becomes SYNCHRONIZED. Assuming the mirror has the same capacity as the principal, it is recommended that you leave the database there since switching the principal and mirror may cause another reconnect for the application.

    Thursday, August 5, 2010 3:45 AM
  • Since the database mirroring session depends on the DB Engine service account, after changing the service account, an explicit grant permission on the database mirrroring endpoints is required.
    SKG: Please Marked as Answered, if it resolves your issue.
    Friday, August 6, 2010 9:14 AM