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=@name, @id=@id, @newloginname=@new_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:
