locked
alter database set partner RRS feed

  • Question

  • Hi All,

    What does alter database set partner  in database mirroing does?

    What privilege it needs to execute alter database set partner  command.

    Is this possible to exceute without dbowner or syadmin privileges.?

     


    Regards, Sudhagar.G
    Friday, December 16, 2011 11:04 AM

Answers

  • I have checked Its working for me - If you grant below two permission to account.

    1. Connect on endpoint

    2. Alter any database


    http://uk.linkedin.com/in/ramjaddu
    • Marked as answer by Stephanie Lv Monday, December 19, 2011 7:28 AM
    Friday, December 16, 2011 4:45 PM
  • Hi All,

     Pls find the step i have taken so afr.

    create an SQL id 'opsdba' with belwo permission on both server.

    1)      Add the group as a login to SQL Server and provide the below mentioned server roles.

                                                                  i.      Diskadmin

                                                                ii.      Processadmin

                                                              iii.      Serveradmin

                                                              iv.      Setupadmin

     

    2)      GRANT CREATE DATABASE Permissions to OPSDBA at server level

    3)      GRANT ALTER TRACE TO OPSDBA at server level

    4)      GRANT VIEW ANY DEFINITION TO OPSDBA at server level

    5)      GRANT ALTER ANY DATABASE TO OPSDBA at server level

    6)      GRANT VIEW DATABASE STATE TO OPSDBA at Database level

    7)      GRANT ALTER TO OPSDBA at Database level

    8)      GRANT Control TO OPSDBA at Database level

     

    9)      Provide db_backupoperator and db_denydatawriter role to OPSDBA in all databases

    10)  Provide db_owner roles in master and msdb database to OPSDBA

    11)  Provide the following roles to OPSDBA in msdb database other than the db_owner role

                                                                  i.      DatabaseMailUserRole

                                                                ii.      Db_dtsadmin/db_ssisltduser/db_ssisoperator role

                                                              iii.      SQLAgentOperatorRole

                                                              iv.      ServerGroupAdministratorRole

                                                                v.      PolicyAdministratorRole

                                                              vi.      dbm_monitor

     

    Now effctive privilge at both server is below:

    database VIEW DATABASE STATE
    database ALTER
    database CONTROL
    database BACKUP DATABASE
    database BACKUP LOG
    database CONNECT
    database CHECKPOINT
    database VIEW DATABASE STATE
    server CONNECT SQL
    server SHUTDOWN
    server CREATE ENDPOINT
    server ALTER ANY ENDPOINT
    server ALTER ANY LINKED SERVER
    server ALTER ANY CONNECTION
    server ALTER RESOURCES
    server ALTER SETTINGS
    server VIEW ANY DATABASE
    server VIEW SERVER STATE
    server ALTER SERVER STATE
    server CREATE ANY DATABASE
    server ALTER TRACE
    server VIEW ANY DEFINITION
    server ALTER ANY DATABASE

    Step taken to conffigure mirroing using "opsdba ID" above created

    1) Backuedp full and log backup of test db and restored in mirroe server in norecovery state. "succesfull"

     

    2)create endpoints at both pricpal and mirror as below.   "succesfull"

    CREATE ENDPOINT [Mirroring]
    STATE=STARTED
    AS TCP (LISTENER_PORT = 7028, LISTENER_IP = ALL)
    FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE,
    ENCRYPTION = REQUIRED ALGORITHM RC4);
    GO

    3) Execute it on Principal Server"succesfull"

    -- Grant Connect to Permission for the DB Engine Accounts (Of Mirror and Witness Server)
    -- on the Database Mirroring Endpoint (On Principal Server)
    GRANT CONNECT ON ENDPOINT::Mirroring TO [EXAMPLE\Administrator];
    GO
    4)
    -- Execute it on Mirror Server "succesfull"
    -- Grant Connect to Permission for the DB Engine Accounts (Of Principal and Witness Server)
    -- on the Database Mirroring Endpoint (On Mirror Server)
    GRANT CONNECT ON ENDPOINT::Mirroring TO [EXAMPLE\Administrator];
    GO
      
    5) Setup the Mirroring sessions.
     
    Adding the database to Database Mirroring Session (Execute it on Mirror Server) using opsdba id
    USE [master]
    ALTER DATABASE [TestDB]
    SET PARTNER = 'TCP://Node1.EXAMPLE.COM:7028';
    GO

    its failed with belwo error:and create a dump file

    Msg 0, Level 11, State 0, Line 0
    A severe error occurred on the current command.  The results, if any, should be discarded.
    Msg 0, Level 20, State 0, Line 0
    A severe error occurred on the current command.  The results, if any, should be discarded.

    We tried above with syaadmin id, it went succesfull.

     

    6)  Adding the database to Database Mirroring Session (Execute it on Principal Server) using opsdba id. "succesfull"

    USE [master]
    ALTER DATABASE [TestDB]
    SET PARTNER = 'TCP://Node2.EXAMPLE.COM:7028';
    GO

    So, we have to created SP to perform step 5.

     

    So, where is the problem from my end or its really Connect on endpoint and Alter any database permission is enough.

    We ahve used sql server 2005\sp3 version for testing 

    Regards, Sudhagar.G
    • Marked as answer by Stephanie Lv Monday, December 19, 2011 7:28 AM
    Saturday, December 17, 2011 9:02 AM

All replies

  • It controls the database properties that define the failover partners of a database mirroring session and their behavior. Some SET PARTNER options can be set on either partner; others are restricted to the principal server or to the mirror server. For more information, see the individual PARTNER options that follow. A SET PARTNER clause affects both copies of the database, regardless of the partner on which it is specified.

    You need ALTER ANY DATABASE permission to use this command

    you don't need to be a dbowner or sysadmin to do this -

     

    more here http://msdn.microsoft.com/en-us/library/bb522476.aspx

     


    http://uk.linkedin.com/in/ramjaddu
    Friday, December 16, 2011 11:36 AM
  • Hi,

    Please see: http://msdn.microsoft.com/en-us/library/bb522476.aspx

    You need a minimum of ALTER permissions on the database(s). See - http://msdn.microsoft.com/en-us/library/ms174269.aspx



    Thanks,

    Andrew Bainbridge
    SQL Server DBA

    Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you
    Friday, December 16, 2011 11:39 AM
  • Hi ,

     ALTER ANY DATABASE or ALTER permissions on the database does not seem to be enough to intiate mirroing.

     

    USE [master]
    ALTER DATABASE [TestDB]
    SET PARTNER = 'TCP://Node1.EXAMPLE.COM:7028';
    GO

    throwing error:

    process 66 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process


    Regards, Sudhagar.G
    Friday, December 16, 2011 2:21 PM
  • Yes did you have grant to connect permission on endpoints?

    Grant Connect on EndPoing::Mirroring to [Login]


    http://uk.linkedin.com/in/ramjaddu
    Friday, December 16, 2011 3:50 PM
  • In case you are looking for a step-by-step guide, here is a post, that might help :)
    SKG: Please Marked as Answered, if it resolves your issue.
    Friday, December 16, 2011 3:54 PM
  • I have checked Its working for me - If you grant below two permission to account.

    1. Connect on endpoint

    2. Alter any database


    http://uk.linkedin.com/in/ramjaddu
    • Marked as answer by Stephanie Lv Monday, December 19, 2011 7:28 AM
    Friday, December 16, 2011 4:45 PM
  • Hi All,

     Pls find the step i have taken so afr.

    create an SQL id 'opsdba' with belwo permission on both server.

    1)      Add the group as a login to SQL Server and provide the below mentioned server roles.

                                                                  i.      Diskadmin

                                                                ii.      Processadmin

                                                              iii.      Serveradmin

                                                              iv.      Setupadmin

     

    2)      GRANT CREATE DATABASE Permissions to OPSDBA at server level

    3)      GRANT ALTER TRACE TO OPSDBA at server level

    4)      GRANT VIEW ANY DEFINITION TO OPSDBA at server level

    5)      GRANT ALTER ANY DATABASE TO OPSDBA at server level

    6)      GRANT VIEW DATABASE STATE TO OPSDBA at Database level

    7)      GRANT ALTER TO OPSDBA at Database level

    8)      GRANT Control TO OPSDBA at Database level

     

    9)      Provide db_backupoperator and db_denydatawriter role to OPSDBA in all databases

    10)  Provide db_owner roles in master and msdb database to OPSDBA

    11)  Provide the following roles to OPSDBA in msdb database other than the db_owner role

                                                                  i.      DatabaseMailUserRole

                                                                ii.      Db_dtsadmin/db_ssisltduser/db_ssisoperator role

                                                              iii.      SQLAgentOperatorRole

                                                              iv.      ServerGroupAdministratorRole

                                                                v.      PolicyAdministratorRole

                                                              vi.      dbm_monitor

     

    Now effctive privilge at both server is below:

    database VIEW DATABASE STATE
    database ALTER
    database CONTROL
    database BACKUP DATABASE
    database BACKUP LOG
    database CONNECT
    database CHECKPOINT
    database VIEW DATABASE STATE
    server CONNECT SQL
    server SHUTDOWN
    server CREATE ENDPOINT
    server ALTER ANY ENDPOINT
    server ALTER ANY LINKED SERVER
    server ALTER ANY CONNECTION
    server ALTER RESOURCES
    server ALTER SETTINGS
    server VIEW ANY DATABASE
    server VIEW SERVER STATE
    server ALTER SERVER STATE
    server CREATE ANY DATABASE
    server ALTER TRACE
    server VIEW ANY DEFINITION
    server ALTER ANY DATABASE

    Step taken to conffigure mirroing using "opsdba ID" above created

    1) Backuedp full and log backup of test db and restored in mirroe server in norecovery state. "succesfull"

     

    2)create endpoints at both pricpal and mirror as below.   "succesfull"

    CREATE ENDPOINT [Mirroring]
    STATE=STARTED
    AS TCP (LISTENER_PORT = 7028, LISTENER_IP = ALL)
    FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE,
    ENCRYPTION = REQUIRED ALGORITHM RC4);
    GO

    3) Execute it on Principal Server"succesfull"

    -- Grant Connect to Permission for the DB Engine Accounts (Of Mirror and Witness Server)
    -- on the Database Mirroring Endpoint (On Principal Server)
    GRANT CONNECT ON ENDPOINT::Mirroring TO [EXAMPLE\Administrator];
    GO
    4)
    -- Execute it on Mirror Server "succesfull"
    -- Grant Connect to Permission for the DB Engine Accounts (Of Principal and Witness Server)
    -- on the Database Mirroring Endpoint (On Mirror Server)
    GRANT CONNECT ON ENDPOINT::Mirroring TO [EXAMPLE\Administrator];
    GO
      
    5) Setup the Mirroring sessions.
     
    Adding the database to Database Mirroring Session (Execute it on Mirror Server) using opsdba id
    USE [master]
    ALTER DATABASE [TestDB]
    SET PARTNER = 'TCP://Node1.EXAMPLE.COM:7028';
    GO

    its failed with belwo error:and create a dump file

    Msg 0, Level 11, State 0, Line 0
    A severe error occurred on the current command.  The results, if any, should be discarded.
    Msg 0, Level 20, State 0, Line 0
    A severe error occurred on the current command.  The results, if any, should be discarded.

    We tried above with syaadmin id, it went succesfull.

     

    6)  Adding the database to Database Mirroring Session (Execute it on Principal Server) using opsdba id. "succesfull"

    USE [master]
    ALTER DATABASE [TestDB]
    SET PARTNER = 'TCP://Node2.EXAMPLE.COM:7028';
    GO

    So, we have to created SP to perform step 5.

     

    So, where is the problem from my end or its really Connect on endpoint and Alter any database permission is enough.

    We ahve used sql server 2005\sp3 version for testing 

    Regards, Sudhagar.G
    • Marked as answer by Stephanie Lv Monday, December 19, 2011 7:28 AM
    Saturday, December 17, 2011 9:02 AM