<b>Migrating SQL Servers</b>

Sun Dec 4, 2005 by Derrick Leggett in b-sql-server-b

I see a lot of question on the forums about migrating to new servers. At all the companies I have worked at, we have implemented server build processes to help with this process and also document needed DR procedures. For this post, I’m including a generic SQL Server Build Process and some nice scripts I keep on all the servers to assist in the process of migration.

SQL Server Build Process

Policy Statement

This document will be used to build all SQL Server Servers and instances at . All new SQL Server installations must follow this process and be documented accordingly.

Scope

This policy will cover the entire enterprise in regards to SQL Server installations. No installations of SQL Server will be completed without following this process.

This document does not cover the following:

1 Processes and policies for acquisition of hardware and/or software.

2 Process for physical server build or installation of the OS, drivers, etc. IT has a process in place covering physical server builds. All servers with SQL Server should be built according to the IT process coving SQL Server builds.

Build Process

Installation

Remote into the server to which the new instance of SQL Server will be added

Go to \\filshare\directory\SQL Server 2000 - Enterprise Edition (or CD/DVD) and run the autorun.exe

1 Install Components

2 Install Database Server

3 Install onto the local computer

4 Create a new instance of SQL Server

5 Install server and client tools

6 If creating a named instance, disable the default instance and rename

7 Choose to run a custom install only if the instance should have a collation different than standard collation. Use custom install if replacing or moving from an instance that had any other collation setting and use same collation.

8 Select to use the same account for each service (might vary by enterprise)

9 For Service Settings, select to use a Domain User account and provide the user account and password that will be used for SQL Server

10 For Authentication Mode, select Mixed Mode and provide the sa password if using mixed mode authentication. It's preferable not to use this at all.

11 If doing a custom install then disable named pipes and enter the properties for TCP/IP and change the default number to the desired port when prompted (if doing a typical install then go into Programs>Microsoft SQL Server>Server Network Utility to perform this step)

12 For Licensing Mode, select Processor License for x number of physical processors on box (might vary by enterprise if your company uses CAL licenses)

13 Install the latest service pack which can be found at \\fileshare\directory\sql2ksp3\setup.bat for each instance that has been installed.

14 If installing SP3a instead of SP4, install mdac2.7 – sp1 refresh which can be found at \\fileshare\directory\mdac2.7-sp1refresh {This needs to run once only, this is a server patch not a per instance refresh}

15 If installing SP3a instead of SP4, install the SQL Server 2000 818, \\fileshare\directory\SQL2000 – KB815495-8.00 {This needs to be run for ever instance that has just been installed}

If you do not already have this software installed on your fileshares, the downloads can be obtained at: http://www.microsoft.com/sql/downloads/default.mspx

configure logins

1 In Enterprise Manager, go to the Security>Logins under the Instance that was created

2 Add the \ADGroup group that will be used by the DBA staff to administer SQL Server and grant "System Administrators" access listed in the Server Roles tab {Once per instance}

3 Remove the BUILTIN\Administrators group. DO NOT do this before following step 2.

For additional security measures, www.sqlsecurity.com has a hardening document for restricting public access, tightening up network layer, etc.

Configure Server Administrators

3 Go to Computer Management>System Tools>Local Users and Groups> Groups

4 Open up the Administrators dialog box

5 Add the \ADGroup group that will be used by the DBA staff to administer SQL Server

COnfigure SQL Server to use Outlook

1 Install Outlook on the server if it is not already there

o \\fileshare\directory\SETUP.EXE

o Custom Install

o Deselect everything but outlook

2 Configure Outlook on the server to use the appropriate exchange server and the mailbox that was assigned by exchange group for SQL Server usage

3 Select “yes” when prompted to register Outlook as the default and close Outlook

4 In Enterprise Manager, go to properties of the new SQL Server Instance and click on the Server Settings tab

5 Click on the “change” button under SQL Mail and in the dialog box change the mail profile to “MS Exchange Settings” and click OK {SQL Server agent may need to be started before continuing on to the next step.}

6 Go to the properties of SQL Server Agent and set the mail profile to “MS Exchange Settings” and select “yes” when prompted to stop and start the agent

Configure DTC for network access (for windows 2003 servers)

1 In Component Services, go to the Properties of My Computer

2 On the MSDTC tab, go to Security Configuration and select the Network DTC Access check box

3 Under Network DTC Access, click Network Transactions

4 Make sure that the DTC Logon Account is set to NT Authority\NetworkService

5 Reboot the server for the changes to take effect

6 See the Knowledge Base article: http://support.microsoft.com/default.aspx?scid=kb;en-us;329332

SET UP NEW PAGING FILE on the server

1 Go to My Computer>Properties

2 Under the Advanced tab, click on the Settings button under Performance

3 Go to the Advanced tab on the Performance Options dialog box and click the Change button under Virtual Memory

4 Select Custom Size and set up the initial and maximum size of the D drive to be like that of the C drive (this will vary by Enterprise)

Move system databases

1 Be sure to remove the pubs and Northwind databases from the new instance

2 First create a directory structure under both the C and E drives for each new instance like the following:

C:\mssql\\data

C:\mssql\\log

Move tempdb data and log files under the directory structure just created on E and the rest of the system databases to the directories under C. Follow the directions for moving sql server databases to a new location with detach/attach from http://support.microsoft.com/default.aspx?scid=kb;en-us;224071&Product=sql

INSTALL SQL LITESPEED

1 Install SQL LiteSpeed from \\fileshare\directoryLiteSpeed CD

This is specific to enterprises with LiteSpeed. You will need appropriate license keys.

use dts to add extra objects to the master database

1 Use the import/export features in EM to import the objects from the master database on \ to the new instance’s master database

2 Copy objects and data

3 Deselect all options

4 Select all objects

You can also use the export or import features in EM or script out objects from the source control tool.

Restore User Databases

1 Restore the user databases to the new location from backups. It’s also possible to replace the user databases from the previous server using attach/detach. Do NOT use this for step for the system databases though.

Restore jobs, operators, and dts packages

1 When creating one instance from one instance

o Restore the msdb database

o Run the following:

USE msdb

UPDATE SYSJOBS

SET ORIGINATING_SERVER = ‘’

2 When creating one instance from 2 or more instances

o Restore the msdb database from one instance

o Script out all jobs and operations from the other instance and run them as well

o Copy the DTS packages from the second instance

restore linked servers and logins

1 Use “linked servers scriptor.sql” to create a script for adding the linked servers which can be run on the new database

2 Restore the logins by running sp_help_revlogin from the source server and using the output script on the destination server

NOTE: You will need to install any local users you have on the SQL Server with access to your instances. These should be documented in a security document somewhere. If not, you should create one.

Additional Instructions for Specific Databases

1 If you have databases that have extended stored procedures, full-text indexing, or other special features, then add a section in for each of these exceptions. The document will then be complete. Test thoroughly on a development or lab server using personnel not familiar with the initial authoring of the document to test.

Supporting Scripts

The first two scripts, or variations of them, can be found on numerous forums and the MS website. The are sp_hexadecimal and sp_help_revlogin. By using these to script the logins in the master database of the source server, you have a script you can apply to the destination server AFTER the user databases are restored that will seamlessly setup your user accounts without having to worry about mismatches on the SIDs.

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_hexadecimal]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_hexadecimal]
GO

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
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_help_revlogin]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_help_revlogin]
GO

CREATE PROCEDURE sp_help_revlogin @login_name
sysname = NULL
AS
--Declare needed variables.
DECLARE
@name    sysname,
@xstatus int,
@binpwd varbinary (256),
@txtpwd sysname,
@tmpstr varchar (256),
@SID_varbinary varbinary(85),
@SID_string varchar(256),
@dbname varchar(255)
--Determine whether to process one login or all. Set up cursor accordingly.
IF (@login_name IS NULL)
BEGIN
  DECLARE login_curs CURSOR FOR
     SELECT
    sxl.sid,
    sxl.name,
    sxl.xstatus,
    sxl.password,
    sd.name AS dbname
   FROM
    master..sysxlogins sxl
    INNER JOIN master..sysdatabases sd ON sxl.dbid = sd.dbid
     WHERE
    sxl.srvid IS NULL
    AND sxl.name <> 'sa'
END
ELSE
BEGIN
  DECLARE login_curs CURSOR FOR
      SELECT
    sxl.sid,
    sxl.name,
    sxl.xstatus,
    sxl.password,
    sd.name AS dbname
   FROM
    master..sysxlogins sxl
    INNER JOIN master..sysdatabases sd ON sxl.dbid = sd.dbid
     WHERE
    sxl.srvid IS NULL
    AND sxl.name <> @login_name
END
OPEN login_curs
FETCH NEXT FROM login_curs
INTO
@SID_varbinary,
@name,
@xstatus,
@binpwd,
@dbname
--If no logins found, exit the procedure.
IF (@@fetch_status = -1)
BEGIN
  PRINT 'No login(s) found.'
  CLOSE login_curs
  DEALLOCATE login_curs
  RETURN -1
END
SELECT @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SELECT @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 (@binpwd IS NOT NULL)
       BEGIN -- Non-null password

        EXEC sp_hexadecimal @binpwd, @txtpwd OUT
        IF (@xstatus & 2048) = 2048
         BEGIN
          SET @tmpstr = 'SET @pwd = CONVERT (varchar(256), ' + @txtpwd + ')'
         END
        ELSE
         BEGIN
          SET @tmpstr = 'SET @pwd = CONVERT (varbinary(256), ' + @txtpwd + ')'
         END

        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
        BEGIN

         -- Null password
         EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
         SET @tmpstr =
          'EXEC master..sp_addlogin ''' + @name + ''', NULL, @sid = ' + @SID_string + ', @encryptopt = '
        END
       END
      IF (@xstatus & 2048) = 2048
       BEGIN
        -- login upgraded from 6.5
        SET @tmpstr = @tmpstr + '''skip_encryption_old'''
        PRINT @tmpstr
       END
      ELSE
       BEGIN
        SET @tmpstr = @tmpstr + '''skip_encryption'''
        PRINT @tmpstr
       END
     END
  --Add the default database.
  SET @tmpstr = 'EXEC master..sp_defaultdb ''' + @name + ''',''' + @dbname + ''''
  PRINT @tmpstr
END
FETCH NEXT FROM login_curs
INTO
  @SID_varbinary,
  @name,
  @xstatus,
  @binpwd,
  @dbname
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

The next script is used to script out linked servers. It was found on the internet and modified by a DBA working for me. You will need to know the passwords for SQL Server users in each linked server. It doesn’t script out passwords for you. J

use master
SET NOCOUNT ON
DECLARE @this_server VARCHAR(255),
  @server_ct INT,
  @server VARCHAR(255),
     @srvproduct VARCHAR(255),
     @provider VARCHAR(255),
     @datasrc VARCHAR(255),
     @location VARCHAR(255),
     @provstr VARCHAR(255),
     @catalog VARCHAR(255),
  @rpc INT,
  @pub INT,
  @sub INT,
  @dist INT,
  @dpub INT,
  @rpcout INT,
  @dataaccess INT,
  @collationcompatible INT,
  @system INT,
  @userremotecollation INT,
  @lazyschemavalidation INT,
  @collation VARCHAR(255)

CREATE TABLE #outputLog(
rowId INT IDENTITY(1, 1),
outputData VARCHAR(1000))

CREATE TABLE #srvLogin(
   rowId  INT IDENTITY(1, 1),
   linkedServer VARCHAR(255),
   localLogin VARCHAR(255),
   isSelfMapping INT,
   remoteLogin VARCHAR(255))

SELECT @this_server = srvname FROM sysservers WHERE srvid = 0
SELECT @server_ct = 1

WHILE @server_ct <= (SELECT max(srvid) FROM sysservers)
BEGIN
select
  @server = srvname,
     @srvproduct = srvproduct,
     @provider = CASE WHEN srvproduct = 'SQL Server' THEN NULL ELSE providername END,
     @datasrc = CASE WHEN srvproduct = 'SQL Server' THEN NULL ELSE datasource END,
     @location = CASE WHEN srvproduct = 'SQL Server' THEN NULL ELSE location END,
     @provstr = CASE WHEN srvproduct = 'SQL Server' THEN NULL ELSE providerstring END,
     @catalog = CASE WHEN srvproduct = 'SQL Server' THEN NULL ELSE catalog END,
  @rpc = rpc,
  @pub = pub,
  @sub = sub,
  @dist = dist,
  @dpub = dpub,
  @rpcout = rpcout,
  @dataaccess = dataaccess,
  @collationcompatible = collationcompatible,
  @system = system,
  @userremotecollation = useremotecollation,
  @lazyschemavalidation = lazyschemavalidation,
  @collation = collation
from
  sysservers
WHERE
  srvid = @server_ct

INSERT INTO #outputLog
SELECT 'EXEC sp_addlinkedserver ''' + @server + ''', '
   + CASE WHEN @srvproduct IS NULL THEN 'NULL' ELSE '''' + @srvproduct + '''' END + ', '
   + CASE WHEN @provider IS NULL THEN 'NULL' ELSE '''' + @provider + '''' END + ', '
   + CASE WHEN @datasrc IS NULL THEN 'NULL' ELSE '''' + @datasrc + '''' END + ', '
   + CASE WHEN @location IS NULL THEN 'NULL' ELSE '''' + @location + '''' END + ', '
   + CASE WHEN @provstr IS NULL THEN 'NULL' ELSE '''' + @provstr + '''' END + ', '
   + CASE WHEN @catalog IS NULL THEN 'NULL' ELSE '''' + @catalog + '''' END

INSERT INTO #outputLog
SELECT 'EXEC sp_serveroption ''' + @server + ''', ''' + 'rpc'', ' + CASE WHEN @rpc = 1 THEN '''TRUE''' ELSE '''FALSE''' END

INSERT INTO #outputLog
SELECT 'EXEC sp_serveroption ''' + @server + ''', ''' + 'pub'', ' + CASE WHEN @pub = 1 THEN '''TRUE''' ELSE '''FALSE''' END

INSERT INTO #outputLog
SELECT 'EXEC sp_serveroption ''' + @server + ''', ''' + 'sub'', ' + CASE WHEN @sub = 1 THEN '''TRUE''' ELSE '''FALSE''' END

INSERT INTO #outputLog
SELECT 'EXEC sp_serveroption ''' + @server + ''', ''' + 'dist'', ' + CASE WHEN @dist = 1 THEN '''TRUE''' ELSE '''FALSE''' END

INSERT INTO #outputLog
SELECT 'EXEC sp_serveroption ''' + @server + ''', ''' + 'dpub'', ' + CASE WHEN @dpub = 1 THEN '''TRUE''' ELSE '''FALSE''' END

INSERT INTO #outputLog
SELECT 'EXEC sp_serveroption ''' + @server + ''', ''' + 'rpc out'', ' + CASE WHEN @rpcout = 1 THEN '''TRUE''' ELSE '''FALSE''' END

INSERT INTO #outputLog
SELECT 'EXEC sp_serveroption ''' + @server + ''', ''' + 'data access'', ' + CASE WHEN @dataaccess = 1 THEN '''TRUE''' ELSE '''FALSE''' END

INSERT INTO #outputLog
SELECT 'EXEC sp_serveroption ''' + @server + ''', ''' + 'collation compatible'', ' + CASE WHEN @collationcompatible = 1 THEN '''TRUE''' ELSE '''FALSE''' END

-- INSERT INTO #outputLog
-- SELECT 'EXEC sp_serveroption ''' + @server + ''', ''' + 'system'', ' + CASE WHEN @system = 1 THEN '''TRUE''' ELSE '''FALSE''' END

INSERT INTO #outputLog
SELECT 'EXEC sp_serveroption ''' + @server + ''', ''' + 'use remote collation'', ' + CASE WHEN @userremotecollation = 1 THEN '''TRUE''' ELSE '''FALSE''' END

INSERT INTO #outputLog
SELECT 'EXEC sp_serveroption ''' + @server + ''', ''' + 'lazy schema validation'', ' + CASE WHEN @lazyschemavalidation = 1 THEN '''TRUE''' ELSE '''FALSE''' END

-- INSERT INTO #outputLog
-- SELECT 'EXEC sp_serveroption ''' + @server + ''', ''' + 'collation'', ' + CASE WHEN @collation IS NULL THEN 'NULL' ELSE '''' + @collation + '''' END + ''

INSERT INTO #srvLogin
EXEC sp_helplinkedsrvlogin @server

INSERT INTO #outputLog
SELECT 'EXEC sp_addlinkedsrvlogin @rmtsrvname = '+ CASE WHEN linkedServer IS NULL THEN 'NULL' ELSE '''' + linkedServer + '''' END
   + ', @useself = ' + CASE WHEN isSelfMapping = 1 THEN '''TRUE''' ELSE '''FALSE''' END
   + ', @locallogin = ' + CASE WHEN localLogin IS NULL THEN 'NULL' ELSE '''' + localLogin + '''' END
   + ', @rmtuser = ' + CASE WHEN remoteLogin IS NULL THEN 'NULL' ELSE '''' + remoteLogin + '''' END
   + ', @rmtpassword = ' + CASE WHEN isSelfMapping = 1 THEN 'NULL' ELSE '''ENTER_PASSWORD_HERE''' END

FROM #srvLogin

DELETE #srvLogin

SELECT @server_ct = @server_ct + 1
END

SELECT outputData from #outputLog
ORDER BY rowId

DROP TABLE #outputLog
DROP TABLE #srvLogin

Ending Notes:

· In addition to the scripts contained above, attach and detach scripts are maintained for each instance.

· There is also an Excel spreadsheet maintained that has network, windows, and SAN specific checklists. Before following the SQL Server Build process, the checklists are completed to insure the server is setup correctly.

· Finally, a comprehensive environment spreadsheet in maintained which included exact LUN configurations, network ports for each server, network segment setup, etc.

Proper documentation can be a lot of overhead initially. In the end though, it produces faster turnaround on processes like server migration, disaster recovery, and consolidation. Most importantly, it could save your job and the company in certain circumstances.

Hopefully, this will help people see the importance of planning and make your migrations smoother in the future. If you would like to see anything added, or see mistakes, let me know by adding a comment to the blog.

Kristen mentioned an alternate way to do this that would allow you to have better uptime. It's not appropriate in all instances (database server being completely down for example). We wouldn't use it just because we have the SAN and can easily switch LUNs in the most likely scenario. Check it out though and see if you can use it. It might also have some things I should incorporate into our process. I will need to fully read it later. Here is the links:

http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=44537

Legacy Comments


Lord Strange 2005-12-06	re: <b>Migrating SQL Servers</b> After careful analysis of your update, I conclude that your pluarization of the word "link" is inappropriate-- wrong, one might say. <p> PS: Dont forget my chips :)

Lord Strange's Monkey named Chet 2005-12-17	re: <b>Migrating SQL Servers</b> I note that the code in your ginormous docupost is thouroughly uppercasified. I wonder if you've made use of that Fabulous Drewsoft product: The Uppercasifier. So have you? Punk?