SQL Server Performance

role change

Discussion in 'SQL Server Log Shipping' started by THIRUVENGADAM, Nov 11, 2003.

  1. THIRUVENGADAM New Member

    i am in the process of testing rolechage.
    i am using windows2003 and sql2000 sp3

    my questions is

    will i have to follow create dts package,transfer the syslogins to the
    secondary server and run the dts package because,i can see that whenever i create a new user on the primary server log shipping database,it gets transferred to secondary server and i can see both the sid are the same in both the server,so my doubt is,is the problem of different sids at primary server and secondary server is fixed at windows2003 and sql2000 sp3.If so do i need to run dts package,and two jobs for transferring syslogins,while making the secondary server in to primary server,please advise.

    THIRUVENGADAM
  2. satya Moderator

    If you create the login it will defined in master and user database and log shipping will only be limited to user database and there could be mismatch of sids between master and secondar database.

    This is the reason you need to create the DTs package to transfer the logins so whenever any role changes occurs the secondary server will pickup the logins without any issues.
    For information refer to thishttp://www.microsoft.com/technet/tr...prodtechnol/sql/deploy/prodspecs/logship2.asp link.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  3. THIRUVENGADAM New Member

    you are absoultely correct satya,i cam to know about this while performing role change.

    now my error is

    i created the package
    copied the syslogins.dat in the local primary server
    transfered it to the secondary server
    these jobs finished 100%

    but
    while performing role change

    i am running this procedure in the old primary server

    EXEC iocl1.msdb.dbo.sp_change_primary_role
    @db_name = 'lstest',
    @backup_log = 1,
    @terminate = 0,
    @final_state = 2,
    @access_level = 1
    GO

    using sa login but i am getting the follwing error

    Server: Msg 3013, Level 16, State 1, Line 1
    BACKUP LOG is terminating abnormally.
    Server: Msg 3101, Level 16, State 1, Line 1
    Exclusive access could not be obtained because the database is in use.

    infact my backup and restore freequency is every 3 minutes

    my question is , will i have to run this procedure in the primary server ,which i am going to make it as secondary or in the secondary server, which i am going to make it as primary.

    THIRUVENGADAM
  4. satya Moderator

    Error 3101 clearly represents it cannot restore log due to exclusive usage of database is not possible and related is 3013. Ensure to kill all connections on database and then restore.

    sp_change_primary_role must be run on the instance of SQL Server marked as the current primary server.

    In order to complete a log shipping role change, you must perform several steps in addition to running this procedure. For more information, see How to set up and perform a log shipping role change (Transact-SQL).

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com

Share This Page