locked
Restart MS SQL Server in mirror RRS feed

  • Question

  • Hi everyone!

    I have new client with 5 databases in mirroring configuration.

    They DBA left that company without any paperwork to stay. Client IT staff must restart the primary SQL Server and databases must not failover. Please help me, how do I do this? Do you want to stop the SQL service on the whiteness server? Or can I just pause mirroring?

    Hope for answer,

    Best regards Oleg.

    Monday, January 27, 2020 2:44 AM

Answers

  • Run 

    ALTER DATABASE [db_name] SET WITNESS OFF

    after restart add witness back

    ALTER DATABASE [db_name] SET WITNESS = 'TCP://[FQDN]:[port_number]'


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, January 27, 2020 5:03 AM
  • Hi Heroy74,

    >> i need just restart principal server WITHOUT FAILOVER, database must stay on principal server !

    As Uri suggested, you can turn off Database Mirroring Witness. The witness is an optional instance of SQL Server that enables the mirror server to recognize whether to initiate an automatic failover. Supporting automatic failover is the only role of the witness.

    ALTER DATABASE [db_name] SET WITNESS OFF
    after restart add witness back
    ALTER DATABASE [db_name] SET WITNESS = 'TCP://[FQDN]:[port_number]'

    >> Instead of witness off  may be i just  stop the SQL Service on witness? 

    No, you can't. If a witness is configured for a database mirroring session, the principal server must be connected at least to one of the other server instances, the mirror server or the witness, or both of them. Otherwise, the database becomes unavailable and forcing service (with possible data loss) is impossible.

    Please refer to Database Mirroring Witness to get more information.

    >> Or just switch mode to high safety without automaticaly failover?

    Yes, you can. You need to remove the witness from database mirroring.  Please follow the steps from MS document.


    Best regards, 
    Cathy 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com




    Tuesday, January 28, 2020 3:48 AM

All replies

  • Hi everyone!

    I have new client with 5 databases in mirroring configuration.

    They DBA left that company without any paperwork to stay. Client IT staff must restart the primary SQL Server and databases must not failover. Please help me, how do I do this? Do you want to stop the SQL service on the whiteness server? Or can I just pause mirroring?

    Hope for answer,

    Best regards Oleg.

    Hello Heroy,

    You can move the resources by below query :

    ALTER DABABASE [DbName] SET PARTNER FAILOVER

    You need to failover all the database in mirror , and then restart the primary server.

    Please mark me as answer if my post helps you.

    Br

    ChetanV

    Monday, January 27, 2020 4:18 AM
  • Hello, ChetanV,

    i need just restart principal server WITHOUT FAILOVER, database must stay on principal server !

    Monday, January 27, 2020 4:44 AM
  • Hello, ChetanV,

    i need just restart principal server WITHOUT FAILOVER, database must stay on principal server !

    Then just stop the service on Witness server and then restart your prod wont initiate failover.

    Please mark me as answer if my post helps you.

    Br

    ChetanV

     
    Monday, January 27, 2020 5:01 AM
  • Run 

    ALTER DATABASE [db_name] SET WITNESS OFF

    after restart add witness back

    ALTER DATABASE [db_name] SET WITNESS = 'TCP://[FQDN]:[port_number]'


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, January 27, 2020 5:03 AM
  • Run 

    ALTER DATABASE [db_name] SET WITNESS OFF

    after restart add witness back

    ALTER DATABASE [db_name] SET WITNESS = 'TCP://[FQDN]:[port_number]'

    Hello Uri,

    Instead of witness off can't we stop the SQL Service on witness? 

    Monday, January 27, 2020 5:12 AM
  • Hello Uri,

    Instead of witness off  may be i just  stop the SQL Service on witness? 

    Or just switch mode to high safety without automaticaly failover?

    Tuesday, January 28, 2020 2:52 AM
  • Hi Heroy74,

    >> i need just restart principal server WITHOUT FAILOVER, database must stay on principal server !

    As Uri suggested, you can turn off Database Mirroring Witness. The witness is an optional instance of SQL Server that enables the mirror server to recognize whether to initiate an automatic failover. Supporting automatic failover is the only role of the witness.

    ALTER DATABASE [db_name] SET WITNESS OFF
    after restart add witness back
    ALTER DATABASE [db_name] SET WITNESS = 'TCP://[FQDN]:[port_number]'

    >> Instead of witness off  may be i just  stop the SQL Service on witness? 

    No, you can't. If a witness is configured for a database mirroring session, the principal server must be connected at least to one of the other server instances, the mirror server or the witness, or both of them. Otherwise, the database becomes unavailable and forcing service (with possible data loss) is impossible.

    Please refer to Database Mirroring Witness to get more information.

    >> Or just switch mode to high safety without automaticaly failover?

    Yes, you can. You need to remove the witness from database mirroring.  Please follow the steps from MS document.


    Best regards, 
    Cathy 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com




    Tuesday, January 28, 2020 3:48 AM
  • Dear colleagues, the task has changed.
    It is necessary to switch databases to the mirror server, the main one to turn off and service. The IT command expands the disk space, works on the server, and then returns the databases back to the primary server.
    I understand that before switching it is necessary to stop all task jobs that worked with switched bases, then to carry out switching and on return bases again enable jobs.
    Tuesday, January 28, 2020 6:48 AM
  • Hi, Chetan,

    The Impact of a Witness on High-Performance Mode

    If you use Transact-SQL to configure high-performance mode, whenever the SAFETY property is set to OFF, we strongly recommend that the WITNESS property also be set to OFF. A witness can coexist with high-performance mode, but the witness provides no benefit and introduces risk.

    If the witness is disconnected from the session when either partner goes down, the database becomes unavailable. This is because, even though high-performance mode does not require a witness, if one is set, the session requires a quorum consisting of two or more server instances. If the session losses quorum, it cannot serve the database.

    When a witness is set in a high-performance mode session, the enforcement of quorum means that:

    • If the mirror server is lost, the principal server must be connected to the witness. Otherwise, the principal server takes its database offline until either the witness or mirror server rejoins the session.

    Thursday, January 30, 2020 2:49 AM