July 1, 2009 at 11:01 am
Hello-
We have configured Log shiiping in sql server 2005. Now, I want to change the roles between Primary and secondary. For this purpose:
1. I have to MUST failover to secondary, bring the secondary database online, transfer logins, transfer jobs, create linked server.
2. After step 1 only, we are allowed to do the Role change between primary and secondary????(From BOL..http://msdn.microsoft.com/en-us/library/ms191233(SQL.90).aspx .After you have recovered a secondary database, you can reconfigure it to act as a primary database for other secondary databases. For more information, see Changing Roles Between Primary and Secondary Servers.
)
3. With out performing Step1, can we switch the roles between primary and secondary?
please clarify me what exact steps I need to follow..
thanks
July 1, 2009 at 11:39 am
rambilla4 (7/1/2009)
Hello-We have configured Log shiiping in sql server 2005. Now, I want to change the roles between Primary and secondary. For this purpose:
1. I have to MUST failover to secondary, bring the secondary database online, transfer logins, transfer jobs, create linked server.
2. After step 1 only, we are allowed to do the Role change between primary and secondary????(From BOL..http://msdn.microsoft.com/en-us/library/ms191233(SQL.90).aspx .After you have recovered a secondary database, you can reconfigure it to act as a primary database for other secondary databases. For more information, see Changing Roles Between Primary and Secondary Servers.
)
3. With out performing Step1, can we switch the roles between primary and secondary?
please clarify me what exact steps I need to follow..
thanks
Right,
The first and foremost thing you should worry about is the synchronization between primary and secondary. Is this a planned maintenance? I understand that you want to failover to the secondary server? and I haven't found bring the secondary database online, transfer logins, transfer jobs, create linked server. this line anywhere in the link your posted.
If you want to failover to the secondary:
1.) If primary is accessible, backup the active portion of the log WITH NO RECOVERY
2.) Copy all the Tlogs that haven't been copied to the shared folder in the secondary server.
3.) Restore them in a sequence with the final Tlog with RECOVERY which brings the secondary online.
4.) Then transfer the logins, jobs, linked servers etc.. the only reason being is Log shipping is a database level while the jobs, logins etc.. are created at a server level so, you should define a job which does this for you.
July 1, 2009 at 12:41 pm
4.) Then transfer the logins, jobs, linked servers etc.. the only reason being is Log shipping is a database level while the jobs, logins etc.. are created at a server level so, you should define a job which does this for you.
We have 8 databases on primary server and we are log shipping 3 databases to secondary.
Now how to transfer the logins and users related to only these 3 datbases from primary to secondary after these 3 databases brought online on secondary server??
thanks
July 1, 2009 at 12:46 pm
Now how to transfer the logins and users related to only these 3 datbases from primary to secondary after these 3 databases brought online on secondary server??
thanks
check this out:http://support.microsoft.com/kb/918992
OR you can use the SSIS Transfer logins task which is much easier.
July 1, 2009 at 1:32 pm
No matter how many databases you log ship to secondary, when you bring those databases online on secondary, we need to transfer all the Logins to secondary from Primary?? is that correct??
Lets say in Primary server, login smith has access to database MyDB1 and has NO access to MyDB2
I have log shipped only Mydb2 But NOT MyDB1.And I brought MyDB2 online on Secondary. In this case we do not need to transfer Login smith right? because it has nothing to do with MYDB2
please correct me if misunderstood the concept of transferring logins
July 2, 2009 at 11:55 am
could please advice me on the above..
July 2, 2009 at 2:01 pm
Yes, you are right. You should transfer only those logins which have corresponding users in your databases.
If the authentication mode in your Primary server is 'Windows Authentication', then you can transfer respective logins by scripting them and running on secondary server or using SSIS package.
If the authentication mode in your Primary server is 'Mixed Mode', then you have to work it as per the article suggested by Krishna.
_http://support.microsoft.com/kb/918992
If you follow the article, it help you to create login script for all the logins in your primary server. From the result set, you can choose the logins you want to transfer and you can run that script on the secondary server.
Also after you complete the transfer of logins, you can sp_change_users_login 'report' to find any orphan users on the failed over databases.
July 2, 2009 at 2:26 pm
Thanks,
We have all SQL logins...
I have scripted all the logins in primary using sp_help_revlogin and saved the generated output script a file and ran in secondary server(after bringing all databases on line).And executed the command sp_change_users_login 'report' and I got result with only coulmn names NO data.
UserName UserSIdD
That means I have NO orphand user problem right?
July 2, 2009 at 2:35 pm
Yes, if there is no output it means you dont have any orphaned user. But you have to run it on all databases....
July 2, 2009 at 3:02 pm
Thank You.
I got one orphand user in one database as below:
UserName UserSID
John 0x3922A096AD89EB49B6BA68362BFDA1B6
Could you please tell me how can I fix this???
July 2, 2009 at 3:17 pm
you can use sp_change_users_login with 'Auto_Fix' or 'Update_One'. Just check if the login already exists, then u can use 'Update_one'
When you use Auto_Fix, you must specify user and password if the login does not already exist, otherwise you must specify user but password will be ignored. login must be NULL. user must be a valid user in the current database. The login cannot have another user mapped to it.
Update_one links the specified user in the current database to an existing SQL Server login. user and login must be specified. password must be NULL or not required.
July 3, 2009 at 1:16 pm
rambilla4 (7/2/2009)
Thank You.I got one orphand user in one database as below:
UserName UserSID
John 0x3922A096AD89EB49B6BA68362BFDA1B6
Could you please tell me how can I fix this???
HI,
Try this .... I use this to fix orphaned users and its a nice script ... thanks to the original writer !
--NOTE: This only works if the database users match the SQL logins
ref: http://damonripper.wordpress.com/2008/10/17/orphaned-users/
declare @ExecString nvarchar(255),
@Name nvarchar(50),
@Count int,
@FixString varchar(8000),
@CurrentDB varchar(50),
@OrphanedCount int
set nocount on
set @Count = 0
set @CurrentDB = DB_NAME()
select @OrphanedCount = count(*)
from sysusers
where issqluser = 1
and (sid is not null and sid 0x0)
and suser_sname(sid) is null
if @OrphanedCount > 0
begin
declare CURS cursor for
select name
from sysusers
where issqluser = 1
and (sid is not null and sid 0x0)
and suser_sname(sid) is null
order by name
open CURS
fetch next from CURS into @Name
select ' ' as 'Attempting Repairs'
while @@FETCH_STATUS = 0
begin
print 'Attempting to repair "' + @Name + '"'
if exists(select * from master..syslogins where name = @Name)
begin
set @ExecString = N'exec sp_change_users_login ''update_one'',''' + @Name + N''',''' + @Name + N''''
exec sp_executesql @ExecString
print ' "' + @Name + '" successfully repaired...'
end
else
begin
print ' "' + @Name + '" does not have a matching entry in syslogins...'
set @FixString = isnull(@FixString,'') + 'exec sp_addlogin ''' + @Name + ''', ''{password}'', ''' +
@CurrentDB + '''' + char(13) + char(10) + 'GO' + char(13) + char(10)
set @Count = @Count + 1
end
fetch next from curs into @Name
end
print ' '
close CURS
deallocate CURS
Hope it helps,
Thanks and N'joy your weekend -- 4th JULY ....
\\K
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply