SQL Server Performance Forum – Threads Archive
role change
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
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
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
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
]]>