locked
Moving from SQL 2005 to SQL 2008 RRS feed

  • Question

  • We are going to move our production SQLs from SQL 2005 Standard edition to SQL 2008 Standard Edition; we are moving to a completly new box, so the plan is to do a restore from backups; what is the best way to approach the move of the following:

    - MSDB
    - Login Information from Master
    - Replcation - we are currently replicating a database in the same box for reporting purposes

    Any suggestion would be greatly appreciated.

    Thanks

    Thursday, September 15, 2011 4:08 PM

Answers

All replies

  • Hello,

    Use the following article to transfer logins between the two serves.

    http://support.microsoft.com/kb/918992

    Script jobs or transfer them using Transfer Jobs Task.

    http://www.mssqltips.com/sqlservertip/2081/transfer-sql-server-jobs-task-and-transfer-sql-server-logins-task-in-ssis/

    About replication, please read the following article:

    http://msdn.microsoft.com/en-us/library/ms143699(v=SQL.100).aspx   

    Hope this helps.

    Regards,

    Alberto Morillo
    SQLCoffee.com

    • Proposed as answer by Peja Tao Friday, September 16, 2011 8:42 AM
    • Marked as answer by Peja Tao Wednesday, September 21, 2011 7:26 AM
    Thursday, September 15, 2011 7:15 PM
  • 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

     

     

    Thursday, September 15, 2011 8:13 PM