Hello Carlos
for what it concerns msdb, when I do this kind of operations I simply migrate all the parties involved (include jobs, dts, dtsx...), mainly by scripting and recreating. This is easily doable, it obviuosly depends on what you have inside the db. For what
it concerns logins, I post down here two procs, create them on Master and run the second, you will get a longin list to apply to second server, include cryptated password and sid. Just clean from the logins you do not need to move.
For replication depends on the criticalites you have, the SLA, data amount and so on. My advice is to script it out (you can do via ManStudio) and create cleanly, and then do initialization. In some cases you can initialize trough back up, which will
cut downtime considerably, but again it depends on the scenario.
If you have more specific points please enquire.
Regards,
Marco
CREATE PROCEDURE sp_hexadecimal
@binvalue varbinary(256),
@hexvalue varchar(256) OUTPUT
AS
DECLARE @charvalue varchar(256)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)
BEGIN
DECLARE @tempint int
DECLARE @firstint int
DECLARE @secondint int
SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
SELECT @firstint = FLOOR(@tempint/16)
SELECT @secondint = @tempint - (@firstint*16)
SELECT @charvalue = @charvalue +
SUBSTRING(@hexstring, @firstint+1, 1) +
SUBSTRING(@hexstring, @secondint+1, 1)
SELECT @i = @i + 1
END
SELECT @hexvalue = @charvalue
GO
CREATE PROCEDURE [dbo].[sp_help_revlogin_Adv]
-- NULL per tutte le utenze, nome utente per specificare un singolo utente
@login_name SYSNAME = NULL
AS
DECLARE @name SYSNAME
DECLARE @xstatus INT
DECLARE @binpwd SYSNAME --(256)
DECLARE @txtpwd VARCHAR (256)
DECLARE @tmpstr VARCHAR (256)
DECLARE @SID_varbinary VARBINARY(85)
DECLARE @SID_string VARCHAR(256)
DECLARE @SysAdmin int
DECLARE @SecurityAdmin int
DECLARE @ServerAdmin int
DECLARE @DiskAdmin int
DECLARE @SetupAdmin int
DECLARE @ProcessAdmin int
DECLARE @DBCreator int
DECLARE @BulkAdmin int
DECLARE @DbName SYSNAME
DECLARE @Language SYSNAME
DECLARE @is_policy_checked bit
DECLARE @is_expiration_checked bit
DECLARE @IsNTName int
declare @a varbinary(256)
IF (@login_name IS NULL)
DECLARE login_curs CURSOR FOR
SELECT SL.sid, SL.name, SL.status, SL.password, SL.dbname, SL.language, SL.sysadmin, SL.securityadmin, SL.serveradmin, SL.setupadmin,
SL.processadmin, SL.diskadmin, SL.dbcreator, SL.bulkadmin, SL1.is_policy_checked, SL1.is_expiration_checked, sl.isntname
FROM master.sys.syslogins SL
left outer join sys.sql_logins SL1 ON SL.sid = SL1.sid
WHERE SL.name <> 'sa'
order by sl.isntname desc, sl1.name
ELSE
DECLARE login_curs CURSOR FOR
SELECT SL.sid, SL.name, SL.status, SL.password, SL.dbname, SL.language, SL.sysadmin, SL.securityadmin, SL.serveradmin, SL.setupadmin,
SL.processadmin, SL.diskadmin, SL.dbcreator, SL.bulkadmin, SL1.is_policy_checked, SL1.is_expiration_checked, sl.isntname
FROM master.sys.syslogins SL
left outer join sys.sql_logins SL1 ON SL.sid = SL1.sid
WHERE SL.name = @login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd,
@DbName, @Language, @SysAdmin, @SecurityAdmin, @ServerAdmin, @SetupAdmin,
@ProcessAdmin, @diskadmin, @DBCreator, @BulkAdmin, @is_policy_checked,
@is_expiration_checked, @IsNTName
IF (@@fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated '
+ CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
PRINT 'DECLARE @pwd sysname'
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr
IF (@xstatus & 4) = 4
BEGIN -- NT authenticated account/group
IF (@xstatus & 1) = 1
BEGIN -- NT login is denied access
SET @tmpstr = 'EXEC master..sp_denylogin ''' + @name + ''''
PRINT @tmpstr
END
ELSE BEGIN -- NT login has access
SET @tmpstr = 'EXEC master..sp_grantlogin ''' + @name + ''''
PRINT @tmpstr
END
END
ELSE
BEGIN -- SQL Server authentication
if @IsNTName = 0
BEGIN
IF (@binpwd IS NOT NULL)
BEGIN -- Non-null password
set @a = CONVERT(varbinary(256), @binpwd)
set @txtpwd = CONVERT(varchar(256), @txtpwd)
EXEC sp_hexadecimal @a , @txtpwd OUT
IF (@xstatus & 2048) = 2048
SET @tmpstr = 'SET @pwd = CONVERT (varchar(256), ' + @txtpwd + ')'
ELSE
SET @tmpstr = 'SET @pwd = CONVERT (varbinary(256), ' + @txtpwd + ')'
PRINT @tmpstr
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
+ ''', @pwd, @sid = ' + @SID_string + ', @encryptopt = '
END
ELSE
BEGIN
-- Null password
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
+ ''', NULL, @sid = ' + @SID_string + ', @encryptopt = '
END
IF (@xstatus & 2048) = 2048
-- login upgraded from 6.5
SET @tmpstr = @tmpstr + '''skip_encryption_old'''
ELSE
SET @tmpstr = @tmpstr + '''skip_encryption'''
PRINT @tmpstr
set @tmpstr = 'ALTER LOGIN [' + @name + '] WITH '
if not @is_policy_checked is NULL
begin
set @tmpstr = @tmpstr + ', CHECK_POLICY = ' + CASE WHEN IsNull(@is_policy_checked, 'FALSE') = 'TRUE' THEN 'ON' ELSE 'OFF' END
end
if not @is_expiration_checked is NULL
begin
set @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + CASE WHEN IsNull(@is_expiration_checked, 'FALSE') = 'TRUE' THEN 'ON' ELSE 'OFF' END
end
end
else
begin
set @tmpstr = 'CREATE LOGIN [' + @name + '] FROM WINDOWS WITH '
end
if IsNull(@DbName, '') <> ''
begin
set @tmpstr = @tmpstr + ', DEFAULT_DATABASE = [' + @DbName + ']'
end
if IsNull(@Language, '') <> ''
begin
set @tmpstr = @tmpstr + ', DEFAULT_LANGUAGE = [' + @Language + ']'
end
if @tmpstr LIKE 'ALTER LOGIN%'
begin
if @tmpstr <> 'ALTER LOGIN [' + @name + '] WITH '
begin
set @tmpstr = replace(@tmpstr, 'WITH ,', 'WITH ')
print @tmpstr
end
end
if @tmpstr LIKE 'CREATE LOGIN%'
begin
if @tmpstr = 'CREATE LOGIN [' + @name + '] FROM WINDOWS WITH '
begin
set @tmpstr = replace(@tmpstr, 'WITH ', '')
end
else
begin
set @tmpstr = replace(@tmpstr, 'WITH ,', 'WITH ')
end
print @tmpstr
end
if IsNull(@SysAdmin, 0) = 1
print 'EXEC master..sp_addsrvrolemember @loginame = N''' + @name + ''', @rolename = N''sysadmin'''
if IsNull(@SecurityAdmin, 0) = 1
print 'EXEC master..sp_addsrvrolemember @loginame = N''' + @name + ''', @rolename = N''securityadmin'''
if IsNull(@ServerAdmin, 0) = 1
print 'EXEC master..sp_addsrvrolemember @loginame = N''' + @name + ''', @rolename = N''serveradmin'''
if IsNull(@SetupAdmin, 0) = 1
print 'EXEC master..sp_addsrvrolemember @loginame = N''' + @name + ''', @rolename = N''setupadmin'''
if IsNull(@diskadmin, 0) = 1
print 'EXEC master..sp_addsrvrolemember @loginame = N''' + @name + ''', @rolename = N''diskadmin'''
if IsNull(@ProcessAdmin, 0) = 1
print 'EXEC master..sp_addsrvrolemember @loginame = N''' + @name + ''', @rolename = N''processadmin'''
if IsNull(@DBCreator, 0) = 1
print 'EXEC master..sp_addsrvrolemember @loginame = N''' + @name + ''', @rolename = N''dbcreator'''
if IsNull(@BulkAdmin, 0) = 1
print 'EXEC master..sp_addsrvrolemember @loginame = N''' + @name + ''', @rolename = N''bulkadmin'''
END
END
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd,
@DbName, @Language, @SysAdmin, @SecurityAdmin, @ServerAdmin, @SetupAdmin,
@ProcessAdmin, @diskadmin, @DBCreator, @BulkAdmin, @is_policy_checked,
@is_expiration_checked, @IsNTName
END
CLOSE login_curs
DEALLOCATE login_curs
GO
