12

What are best practices/important steps when the Windows Server machine hosting SQL Server 2008 R2 is moved to another domain? What steps definitely/might need to be done for SQL Server before and after the new domain is joined?

2
  • 3
    What is the SQL Service account? Ie. is the service itself running as a domain account or not? Mar 26, 2013 at 14:18
  • Yes, the service account is the Windows account SQL Server service is running under. The instructions presume a domain account is being used. Jun 7, 2019 at 17:31

3 Answers 3

13

What do you need to take into account when migrating SQL Server to another domain?

The steps below presume:

  1. IP address will also change
  2. SQL Server is NOT clustered

Steps:

A. Backup:

  • BEFORE: backup the databases off-machine

B. Services:

  • BEFORE: depending on the nature of the change/move, you may want to set service start to Manual for all SQL Service
  • AFTER: Once things or up and running properly, return service start to its original setting

C. SA account:

  • BEFORE: If all administrator accounts are domain accounts or groups, temporarily enable the 'sa' account with a strong password
  • AFTER moving: once the domain-based accounts are setup in the new domain, 'sa' can be disabled again

D. Service Windows account:

  • BEFORE moving: for each SQL-Server-related Windows service, change the service to use a LOCAL windows account or one of the built-in accounts
  • AFTER moving: grant the necessary privileges to the service new domain accounts. When special permissions are not needed, the SQL Service Configuration Manager can be used to change the service account

E. Windows domain accounts used to login to SQL Server

  • Re-create the needed accounts or use corresponding accounts in the new domain.
  • BEFORE moving, script out permissions for OLD domain accounts, including SQL permissions, Windows folder and file permissions, Windows permissions, etc.
  • AFTER moving, apply these scripts to the corresponding NEW domain accounts so they will have the same permissions

F. IP Address: SQL Server (unless clustered) will use the new IP address

  • AFTER: Client applications that reference the service by IP address will need to be configured with the new IP address.

G. Firewall:

  • AFTER: OLD firewall openings that are no longer used will need to be closed, NEW firewall openings may need to be created for SQL Server, OLAP services, SSRS between servers and clients

H. DNS entries:

  • AFTER: verify DNS has correctly updated
  • AFTER: Clients and services that reference by DNS name, may need to be restarted AND/OR their host systems may need their DNS cache flushed. For windows workstations, this can be done with "ipconfig /flushdns"

I. Service Principle Names (SPNs). Some standalone (and all clustered) instances use SPNs.

  • AFTER: The OLD SPN must be dropped and a NEW SPN must be created. Although it's not recommended to use a SQL Server service account to manage (its own) SPNs, if this is the case, the NEW domain service account will need to be granted WriteServicePrincipalName" privilege

J. Client Network Utility Alias.

  • AFTER: Update any clients that use these will need to updated

K. Client application and service connection configuration:

  • AFTER: Data Source Names (DSNs), connection strings, config files, Oracle TNS names for connections - will need to be udpated and applications and services may need to be restarted

L. Internal machine name.

  • AFTER: If the machine name is also changing, SQL Server's internal machine name entry may need to be udpated

    sp_dropserver 'MyOldMachineName' go sp_addserver 'MyNewMachineName','local' go

M. Merge Replication - If merge replication is in use, it will also need to be reconfigured.

  • BEFORE: ensure all replicas are up-to-date
  • AFTER: re-configure merge replation

Attributions - some information added from these sources:

https://serverfault.com/questions/49681/change-ad-domain-membership-of-a-server-2008-running-ms-sql-08

http://social.msdn.microsoft.com/Forums/en-US/sqlsecurity/thread/f3e8ff83-8fcd-4335-87fe-ea5641ae6b88

2

Logins and permissions are the first that come to mind. You'll also have to correct how applications reach the database. This might be rather bothersome. Have a look at this post on the MSDN, might be helpful.

2

Another issue I have seen is the name is sys.servers. Do a

SELECT * from sys.servers

I have seen times where the name column does not change or is not the FQDN of the new server.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.