+ Reply to Thread
Results 1 to 8 of 8

Database Mirroring - what about rebooting?

  1. Database Mirroring - what about rebooting?

    Hi,

    One of the options for keeping a warm standby server is to use database
    mirroring. This can be done with only two servers (no witness). We have
    a scenario where I believe this is the correct solution.

    Server A - SQL 2005 databases in full recovery mode
    Server B - SQL 2005 same databases but recovering

    Same logins on both servers

    Jobs disabled on Server B (not much sense running jobs like backup on
    the mirror?)

    After I enable the mirror in high performance mode, I assume
    transactions will be sent from Server A to Server B.

    My question is what happens when we need to stop a service or reboot a
    server? Will the mirror pause itself and wait for the partner to come
    back on-line?

    An other question is regarding forced service failover. My understanding
    is that this can only be done using T-SQL, not from the Management Studio?

    --
    Gerry Hickman (London UK)

  2. Re: Database Mirroring - what about rebooting?

    Gerry
    > My question is what happens when we need to stop a service or reboot a
    > server? Will the mirror pause itself and wait for the partner to come back
    > on-line?


    Perhaps you would be better of disable the mirroring just before restart and
    re-enable it afterwards
    -- to remove mirroring, issue on either partner:
    ALTER DATABASE dbm SET PARTNER OFF;

    -- to check the state of mirroring, issue:
    SELECT * FROM sys.database_mirroring;








    "Gerry Hickman" wrote in message
    news:ODmzSU4jJHA.4448atTK2MSFTNGP05dotphx.gbl...
    > Hi,
    >
    > One of the options for keeping a warm standby server is to use database
    > mirroring. This can be done with only two servers (no witness). We have a
    > scenario where I believe this is the correct solution.
    >
    > Server A - SQL 2005 databases in full recovery mode
    > Server B - SQL 2005 same databases but recovering
    >
    > Same logins on both servers
    >
    > Jobs disabled on Server B (not much sense running jobs like backup on the
    > mirror?)
    >
    > After I enable the mirror in high performance mode, I assume transactions
    > will be sent from Server A to Server B.
    >
    > My question is what happens when we need to stop a service or reboot a
    > server? Will the mirror pause itself and wait for the partner to come back
    > on-line?
    >
    > An other question is regarding forced service failover. My understanding
    > is that this can only be done using T-SQL, not from the Management Studio?
    >
    > --
    > Gerry Hickman (London UK)




  3. RE: Database Mirroring - what about rebooting?

    Hi Gerry,
    Welcome to MSDN Managed Newsgroup Support Services. Nice to see you again!

    For your first question, "what happens when we need to stop a service or
    reboot a
    server? Will the mirror pause itself and wait for the partner to come back
    on-line?",

    Since no witness, your database mirroring scenario is not under the high
    safety mode FULL, so the automatic failover feature is not able to be
    enabled. In this case, before you stop a service or reboot a server, I
    recommend that you manually failover your database mirroring according to
    the following articles:
    How to: Manually Fail Over a Database Mirroring Session (SQL Server
    Management Studio)
    http://msdn.microsoft.com/en-us/libr...8(SQL.90).aspx
    Of course, you can also manually failover your database mirroring session
    by T-SQL according to these articles:
    How to: Manually Fail Over a Database Mirroring Session (Transact-SQL)
    http://msdn.microsoft.com/en-us/libr...1(SQL.90).aspx
    ALTER DATABASE Database Mirroring (Transact-SQL)
    http://msdn.microsoft.com/en-us/library/bb522476.aspx

    For your second question regarding forced service, yes, it can be only done
    by T-SQL on your mirror.
    ALTER DATABASE SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS
    SSMS does not support configuring forced service failover. Please refer to:
    How to: Force Service in a Database Mirroring Session (Transact-SQL)
    http://msdn.microsoft.com/en-us/library/ms189270.aspx

    If you have any other questions or concerns, please do not hesitate to let
    me know.

    Best regards,
    Charles Wang
    Microsoft Online Community Support
    =========================================================
    Delighting our customers is our #1 priority. We welcome your
    comments and suggestions about how we can improve the
    support we provide to you. Please feel free to let my manager
    know what you think of the level of service provided. You can
    send feedback directly to my manager at: msdnmgatmicrosoftdotcom.
    =========================================================
    This posting is provided "AS IS" with no warranties, and confers no rights.
    =========================================================








  4. Re: Database Mirroring - what about rebooting?

    Hi Charles, Uri,

    I didn't make the question very clear.

    I want to be able to reboot as part of routine maintenance, I don't mind if
    the database is unavailable to users for this short time. I don't really
    want to fail over the mirror. I don't want data corruption due to rebooting.
    In other words, I want to be able to reboot either server without anything
    going wrong.

    I expect this to work because the whole point of having the mirror is to
    survive the failure of one (or other) partner.

    If this isn't possilble (or isn't safe), I'm stuck with having to
    pause/break the mirror every time we apply a patch, which isn't practical as
    we have too many servers and not enough staff.

    Thanks for any additional comments.

    ""Charles Wang [MSFT]"" wrote in message
    news:6mGUeqBkJHA.1704atTK2MSFTNGHUB02dotphx.gbl...
    > Hi Gerry,
    > Welcome to MSDN Managed Newsgroup Support Services. Nice to see you again!
    >
    > For your first question, "what happens when we need to stop a service or
    > reboot a
    > server? Will the mirror pause itself and wait for the partner to come back
    > on-line?",
    >
    > Since no witness, your database mirroring scenario is not under the high
    > safety mode FULL, so the automatic failover feature is not able to be
    > enabled. In this case, before you stop a service or reboot a server, I
    > recommend that you manually failover your database mirroring according to
    > the following articles:
    > How to: Manually Fail Over a Database Mirroring Session (SQL Server
    > Management Studio)
    > http://msdn.microsoft.com/en-us/libr...8(SQL.90).aspx
    > Of course, you can also manually failover your database mirroring session
    > by T-SQL according to these articles:
    > How to: Manually Fail Over a Database Mirroring Session (Transact-SQL)
    > http://msdn.microsoft.com/en-us/libr...1(SQL.90).aspx
    > ALTER DATABASE Database Mirroring (Transact-SQL)
    > http://msdn.microsoft.com/en-us/library/bb522476.aspx
    >
    > For your second question regarding forced service, yes, it can be only
    > done
    > by T-SQL on your mirror.
    > ALTER DATABASE SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS
    > SSMS does not support configuring forced service failover. Please refer
    > to:
    > How to: Force Service in a Database Mirroring Session (Transact-SQL)
    > http://msdn.microsoft.com/en-us/library/ms189270.aspx
    >
    > If you have any other questions or concerns, please do not hesitate to let
    > me know.
    >
    > Best regards,
    > Charles Wang
    > Microsoft Online Community Support
    > =========================================================
    > Delighting our customers is our #1 priority. We welcome your
    > comments and suggestions about how we can improve the
    > support we provide to you. Please feel free to let my manager
    > know what you think of the level of service provided. You can
    > send feedback directly to my manager at: msdnmgatmicrosoftdotcom.
    > =========================================================
    > This posting is provided "AS IS" with no warranties, and confers no
    > rights.
    > =========================================================
    >
    >
    >
    >
    >
    >
    >



  5. Re: Database Mirroring - what about rebooting?

    If you dont care for the availability there should be no problem with
    restarting the service (or rebooting the server) on either side of the
    mirroring (principal and mirror).
    All transactions in progress get rolled back on both the principal and
    mirror as if there was no mirroring active. After the reboot the two
    partners might need a few seconds to sync up, but it works.

    Just be carefull if you shutdown the mirror and dont bring it online for a
    long time (hours, days, ...) you cant clear the transaction log on the
    principal, so it might grow a bit.

    =tg=

    "Gerry Hickman" wrote in message
    news:eBgFm0BkJHA.5964atTK2MSFTNGP03dotphx.gbl...
    > Hi Charles, Uri,
    >
    > I didn't make the question very clear.
    >
    > I want to be able to reboot as part of routine maintenance, I don't mind
    > if the database is unavailable to users for this short time. I don't
    > really want to fail over the mirror. I don't want data corruption due to
    > rebooting. In other words, I want to be able to reboot either server
    > without anything going wrong.
    >
    > I expect this to work because the whole point of having the mirror is to
    > survive the failure of one (or other) partner.
    >
    > If this isn't possilble (or isn't safe), I'm stuck with having to
    > pause/break the mirror every time we apply a patch, which isn't practical
    > as we have too many servers and not enough staff.
    >
    > Thanks for any additional comments.
    >
    > ""Charles Wang [MSFT]"" wrote in message
    > news:6mGUeqBkJHA.1704atTK2MSFTNGHUB02dotphx.gbl...
    >> Hi Gerry,
    >> Welcome to MSDN Managed Newsgroup Support Services. Nice to see you
    >> again!
    >>
    >> For your first question, "what happens when we need to stop a service or
    >> reboot a
    >> server? Will the mirror pause itself and wait for the partner to come
    >> back
    >> on-line?",
    >>
    >> Since no witness, your database mirroring scenario is not under the high
    >> safety mode FULL, so the automatic failover feature is not able to be
    >> enabled. In this case, before you stop a service or reboot a server, I
    >> recommend that you manually failover your database mirroring according to
    >> the following articles:
    >> How to: Manually Fail Over a Database Mirroring Session (SQL Server
    >> Management Studio)
    >> http://msdn.microsoft.com/en-us/libr...8(SQL.90).aspx
    >> Of course, you can also manually failover your database mirroring session
    >> by T-SQL according to these articles:
    >> How to: Manually Fail Over a Database Mirroring Session (Transact-SQL)
    >> http://msdn.microsoft.com/en-us/libr...1(SQL.90).aspx
    >> ALTER DATABASE Database Mirroring (Transact-SQL)
    >> http://msdn.microsoft.com/en-us/library/bb522476.aspx
    >>
    >> For your second question regarding forced service, yes, it can be only
    >> done
    >> by T-SQL on your mirror.
    >> ALTER DATABASE SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS
    >> SSMS does not support configuring forced service failover. Please refer
    >> to:
    >> How to: Force Service in a Database Mirroring Session (Transact-SQL)
    >> http://msdn.microsoft.com/en-us/library/ms189270.aspx
    >>
    >> If you have any other questions or concerns, please do not hesitate to
    >> let
    >> me know.
    >>
    >> Best regards,
    >> Charles Wang
    >> Microsoft Online Community Support
    >> =========================================================
    >> Delighting our customers is our #1 priority. We welcome your
    >> comments and suggestions about how we can improve the
    >> support we provide to you. Please feel free to let my manager
    >> know what you think of the level of service provided. You can
    >> send feedback directly to my manager at: msdnmgatmicrosoftdotcom.
    >> =========================================================
    >> This posting is provided "AS IS" with no warranties, and confers no
    >> rights.
    >> =========================================================
    >>
    >>
    >>
    >>
    >>
    >>
    >>

    >



  6. Re: Database Mirroring - what about rebooting?

    "=tg= Thomas H. Grohser" wrote in message
    news:%23D5wfhHkJHA.5724atTK2MSFTNGP02dotphx.gbl...
    > If you dont care for the availability there should be no problem with
    > restarting the service (or rebooting the server) on either side of the
    > mirroring (principal and mirror).


    Thank you, this is how I would expect it to work.

    > Just be carefull if you shutdown the mirror and dont bring it online for a
    > long time (hours, days, ...) you cant clear the transaction log on the
    > principal, so it might grow a bit.


    Yes, I am aware of this, there's a warning in the docs about it.

    --
    Gerry Hickman
    London (UK)


  7. Re: Database Mirroring - what about rebooting?

    Hi Gerry,
    Thank you for your clarification.
    Do not worry! If you don't mind if the database is unavailable to users
    for this short time. It is no problem to perform a server shutdown for
    maintenance purpose.

    Thank you!

    Best regards,
    Charles Wang
    Microsoft Online Community Support
    =========================================================
    Delighting our customers is our #1 priority. We welcome your
    comments and suggestions about how we can improve the
    support we provide to you. Please feel free to let my manager
    know what you think of the level of service provided. You can
    send feedback directly to my manager at: msdnmgatmicrosoftdotcom.
    =========================================================
    This posting is provided "AS IS" with no warranties, and confers no rights.
    =========================================================


  8. Re: Database Mirroring - what about rebooting?

    Thanks Charles,

    This is very helpful and puts my mind at rest.

    --
    Gerry Hickman
    London (UK)

    ""Charles Wang [MSFT]"" wrote in message
    news:fh$xSCPkJHA.1704atTK2MSFTNGHUB02dotphx.gbl...
    > Hi Gerry,
    > Thank you for your clarification.
    > Do not worry! If you don't mind if the database is unavailable to users
    > for this short time. It is no problem to perform a server shutdown for
    > maintenance purpose.
    >
    > Thank you!
    >
    > Best regards,
    > Charles Wang
    > Microsoft Online Community Support
    > =========================================================
    > Delighting our customers is our #1 priority. We welcome your
    > comments and suggestions about how we can improve the
    > support we provide to you. Please feel free to let my manager
    > know what you think of the level of service provided. You can
    > send feedback directly to my manager at: msdnmgatmicrosoftdotcom.
    > =========================================================
    > This posting is provided "AS IS" with no warranties, and confers no
    > rights.
    > =========================================================
    >



+ Reply to Thread