locked
Database Mirroring Configuration at remote Partner RRS feed

  • Question

  • I am trying to automate database mirroring failover using a Stored Procedure. It works good with regard to failover from primary to backup site and vice versa. For the Enterprise environment, after the failover has occurred I would like to set the SAFE OFF (asynchronous) setting back. I am using linked Server to fix permissions etc. which are all working good except SAFETY OFF setting. I am expecting something like to be executed at the remote server using linked server (already created) and it has adequate privileges:

    USE MASTER; ALTER DATABASE DBNAME SET SAFETY OFF;

    I tried couple of ways as follows but no success yet:

    USE MASTER; ALTER DATABASE [LinkedServer].[DBMirror_Testing] SET SAFETY OFF;
    select * from openquery(LinkedServer, 'ALTER DATABASE DBMirror_Testing SET SAFETY OFF')

    Your help would be very much appreciated.

    Thursday, October 25, 2012 8:49 PM

Answers

  • Hi Fraz,

    Turning off transaction safety shifts the session into asynchronous operating mode, which maximizes performance. SAFETY OFF sets the level of transaction safety. You can specify SAFETY only on the principal server.

    To turn off transaction safety:

    1. Connect to the principal server.
    2. Issue the following statement:
          ALTER DATABASE <database> SET PARTNER SAFETY OFF

    where <database> is the mirrored database.

    For more information, please refer to Change Transaction Safety in a Database Mirroring Session (Transact-SQL): http://msdn.microsoft.com/en-us/library/ms189061.aspx.

    Thanks.


    Maggie Luo

    TechNet Community Support

    • Marked as answer by Maggie Luo Sunday, November 4, 2012 2:57 PM
    Saturday, October 27, 2012 1:20 PM
  • Hallo Fraz,

    this is quite simple to handle with EXEC() AT [LinkedServer]

    e.g.

    EXEC sp_addlinkedserver @server = 'MY_PRETTY_SERVER', @srvproduct = "SQL Server";
    EXEC sp_addlinkedsrvlogin @rmtsrvname = 'MY_PRETTY_SERVER', @useself='True';
    
    EXEC sp_serveroption @server = 'MY_PRETTY_SERVER', @optname = 'rpc', @optvalue = 'True'
    EXEC sp_serveroption @server = 'MY_PRETTY_SERVER', @optname = 'rpc out', @optvalue = 'True'
    
    -- Now I execute the command on my linked server
    EXEC ('ALTER DATABASE [Principal] SET SAFETY OFF') AT [MY_PRETTY_SERVER]

    Get more details concerning linked servers and it's options here:
    http://msdn.microsoft.com/en-us/library/ms190479.aspx
    http://msdn.microsoft.com/en-us/library/ms189811.aspx
    http://msdn.microsoft.com/en-us/library/ms178532.aspx

    Information about EXEC can be found here:
    http://msdn.microsoft.com/en-us/library/ms188332.aspx


    Uwe Ricken

    MCITP Database Administrator 2005
    MCITP Database Administrator 2008
    MCITP Microsoft SQL Server 2008, Database Development

    db Berater GmbH
    http://www-db-berater.de

    • Marked as answer by Maggie Luo Sunday, November 4, 2012 2:57 PM
    Monday, October 29, 2012 3:36 PM
  • The following worked. Thanks for all the help.

    EXEC ('EXEC(''USE MASTER; ALTER DATABASE ' + @DBName + ' SET SAFETY OFF;'') AT ' + @linkedServer)

    • Proposed as answer by MalikF Monday, October 29, 2012 10:27 PM
    • Marked as answer by Maggie Luo Sunday, November 4, 2012 2:58 PM
    Monday, October 29, 2012 10:27 PM

All replies

  • Hi Fraz,

    Turning off transaction safety shifts the session into asynchronous operating mode, which maximizes performance. SAFETY OFF sets the level of transaction safety. You can specify SAFETY only on the principal server.

    To turn off transaction safety:

    1. Connect to the principal server.
    2. Issue the following statement:
          ALTER DATABASE <database> SET PARTNER SAFETY OFF

    where <database> is the mirrored database.

    For more information, please refer to Change Transaction Safety in a Database Mirroring Session (Transact-SQL): http://msdn.microsoft.com/en-us/library/ms189061.aspx.

    Thanks.


    Maggie Luo

    TechNet Community Support

    • Marked as answer by Maggie Luo Sunday, November 4, 2012 2:57 PM
    Saturday, October 27, 2012 1:20 PM
  • Hallo Fraz,

    this is quite simple to handle with EXEC() AT [LinkedServer]

    e.g.

    EXEC sp_addlinkedserver @server = 'MY_PRETTY_SERVER', @srvproduct = "SQL Server";
    EXEC sp_addlinkedsrvlogin @rmtsrvname = 'MY_PRETTY_SERVER', @useself='True';
    
    EXEC sp_serveroption @server = 'MY_PRETTY_SERVER', @optname = 'rpc', @optvalue = 'True'
    EXEC sp_serveroption @server = 'MY_PRETTY_SERVER', @optname = 'rpc out', @optvalue = 'True'
    
    -- Now I execute the command on my linked server
    EXEC ('ALTER DATABASE [Principal] SET SAFETY OFF') AT [MY_PRETTY_SERVER]

    Get more details concerning linked servers and it's options here:
    http://msdn.microsoft.com/en-us/library/ms190479.aspx
    http://msdn.microsoft.com/en-us/library/ms189811.aspx
    http://msdn.microsoft.com/en-us/library/ms178532.aspx

    Information about EXEC can be found here:
    http://msdn.microsoft.com/en-us/library/ms188332.aspx


    Uwe Ricken

    MCITP Database Administrator 2005
    MCITP Database Administrator 2008
    MCITP Microsoft SQL Server 2008, Database Development

    db Berater GmbH
    http://www-db-berater.de

    • Marked as answer by Maggie Luo Sunday, November 4, 2012 2:57 PM
    Monday, October 29, 2012 3:36 PM
  • Thanks Uwe and Maggie.

    The following statement works from a remote server if I put linked server already configured:

    EXEC ('ALTER DATABASE [DBNAME] SET SAFETY OFF') AT LINKEDSERVERNAME

    But I want to pass LinkedServer as a variable so that the linkedserver can be passed as a parameter in the stored procedure to automate the whole failover process. Example, I am looking for something like this:

    EXEC ('ALTER DATABASE [DBName] SET SAFETY OFF') AT @LINKEDSERVER 

    Regards...Fraz

    • Proposed as answer by MalikF Monday, October 29, 2012 9:58 PM
    Monday, October 29, 2012 9:00 PM
  • The following worked. Thanks for all the help.

    EXEC ('EXEC(''USE MASTER; ALTER DATABASE ' + @DBName + ' SET SAFETY OFF;'') AT ' + @linkedServer)

    • Proposed as answer by MalikF Monday, October 29, 2012 10:27 PM
    • Marked as answer by Maggie Luo Sunday, November 4, 2012 2:58 PM
    Monday, October 29, 2012 10:27 PM