SQL : SQL Server Migration Process

image002

SQL Server Migration Process

 

Environment information:

  • Old Domain Name: domold.local / DOMOLD
  • New domain name: domnew.local / DOMNEW

You will need to update these domain names in the relevant processes and scripts detailed below.

Identification of Application Domain Dependencies

Stage one of this process is to establish any domain dependencies within an application. This includes email and active directory (authentication).

Recreation of Service Accounts

Logon to the system you are intending to migrate and identify all service accounts that are on the old domain;

 


 

Each of these accounts must be recreated on the new domnew.local domain. Each account should be prefixed with ‘svc_’ in order to easily identify service accounts in the future.

 

  

 

The accounts should also be configured so that the password does not expire, passwords should be documented. Once recreated you must now modify services so that they use the new service account:

 

         

 

It may also be necessary to mail-enable service accounts, ensuring they have the correct email address.

 

 

File System Permissions

Verify file system permissions and create additional entries for the new service account. Generally speaking these will be application specific directories and must be set manually.


Quest Tools Processing

 

EU Operations are now able to process the machine using the Quest tools. This tool will ensure all file system level user permissions are migrated on the server (excluding service accounts).


  

 

After completion of this step we can now move the server into the domnew.local domain, once complete the server will reboot.

 

  

 

 

Local Server Security Polices

 

Local security policies must be modified to allow logon as a service / batch job rights for individual accounts. On the old domain this was done via GPO, however we have now moved back to local security policies to achieve this.

Identify where the existing service account has been granted permissions and manually recreate them.

 

To identify current permissions Start > Run > gpedit.msc to load the local security policy editor.

 

  

 

 

Expand Computer Configuration > Windows Settings > Security Settings > Local policies > User Rights Assignment

 

  

 

Check for any entries for the local domain such as DOMOLD accounts and replace these with DOMNEW accounts.

                               

 

Application Level Changes

We are now ready to change application level domain dependencies on the migrated server as identified in stage one.

Scheduled Tasks

You will need to ensure all scheduled tasks are using DOMNEW accounts.

  

For non-SQL servers the migration process ends here.


SQL Server Specific Elements for Migration

Create a new service account; this account must be a local administrator on the machine:

 

  

 

The account must be granted logon as a service rights:

 

  

 

 

The account must be granted a SQL Login at the server instance level, this can be achieved form within Enterprise Manager:

 

  

 

         

 

     

 

Please note if the account is shown as the ‘dbo’ user do not set the permissions, instead you must run the sp_changedbown stored procedure against the database:

 

  

 

The Query should look as follows:

 

USE

Expediciones

EXEC sp_changedbowner DOMNEW\SVC_USD1SQL’

 

USE

mdb

EXEC sp_changedbowner DOMNEW\SVC_USD1SQL’

 

Change the Database name (in bold) and the username as is appropriate.

Next we must change the SQL Agent Job owners for the database. You can view all jobs from enterprise manager:

 

  

 

Double-click each job and change the owner to the new DOMNEW object:

 

  


To automate job changes you can use the following T-SQL script, this will change the owner of all jobs that have domold.local ownership to domnew.local\svc_sqladmin

 

–********* Before proceeding please backup the MSDB database in order to provide roll-back. ***********

 

USE MSDB

GO

SELECT GETDATE() AS ‘ExecutionTime’

GO

SELECT @@SERVERNAME AS ‘SQLServerInstance’

GO

SELECT j.[name] AS ‘JobName’,

Enabled = CASE WHEN j.Enabled = 0 THEN ‘No’

ELSE ‘Yes’

END,

l.[name] AS ‘OwnerName’

FROM MSDB.dbo.sysjobs j

INNER JOIN Master.dbo.syslogins l

ON j.owner_sid = l.sid

WHERE l.[name] like ‘%DOMOLD\%’ or l.[name] like ‘%DOMOLD\%’

ORDER BY j.[name]

GO

 

SET NOCOUNT ON

SELECT ‘EXEC MSDB.dbo.sp_update_job ‘ + char(13) +

‘@job_name = + char(39) + j.[Name] + char(39) + ‘,’ + char(13) +

‘@owner_login_name = ‘ + char(39) + DOMNEW\svc_sqladmin’ + char(39) + char(13) + char(13)

FROM MSDB.dbo.sysjobs j

INNER JOIN Master.dbo.syslogins l

ON j.owner_sid = l.sid

WHERE l.[name] like ‘%DOMOLD\%’ or l.[name] like ‘%DOMOLD\%’

ORDER BY j.[name]

 

 

 

Next we must identify and change all DTS Packages that are owned by old domain accounts. The following T-SQL will identify all unique accounts, which own DTS packages:

 

SELECT distinct owner FROM sysdtspackages

 

 

You will need to manually list the unique users and then run the following T-SQL for each user. Change the @old_owner and @new_owner definition at the start of the script:

 

–********* Before proceeding please backup the MSDB database in order to provide roll-back. ***********

 

DECLARE @old_owner varchar(100), @new_owner varchar(100), @name sysname, @id uniqueidentifier

 

set @old_owner = ‘DOMOLD\sqladmin’

set @new_owner = DOMNEW\svc_sqladmin’

 

IF (NOT EXISTS (SELECT * FROM sysdtspackages WHERE [owner] = @old_owner))

BEGIN

   RAISERROR(‘User ”%s” does not own any packages’, 16, 1, @old_owner)

   RETURN

END

 

SELECT DISTINCT [name], [id]

FROM sysdtspackages

WHERE [owner] = @old_owner

 

 

DECLARE cur_sysdtspackages CURSOR FOR

   SELECT DISTINCT [name], [id]

   FROM sysdtspackages

   WHERE [owner] = @old_owner

OPEN cur_sysdtspackages

FETCH NEXT FROM cur_sysdtspackages

INTO @name, @id

WHILE @@FETCH_STATUS = 0

BEGIN

     Print +N’Re-assigning owner on DTS Package: ‘ + @name

       EXEC sp_reassign_dtspackageowner @name[email protected], @id[email protected], @newloginname[email protected]_owner

     FETCH NEXT FROM cur_sysdtspackages

     INTO @name, @id

END

CLOSE cur_sysdtspackages

DEALLOCATE cur_sysdtspackages

 

 

Finally, change the credentials for the SQL services:

  

 

Enter the new DOMNEW username and password:

 

  

Verify that Database Level Users are added for the new DOMNEW domain: