What do you need to take into account when migrating SQL Server to another domain?
The steps below presume:
- IP address will also change
- 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
Does the Windows Failover Cluster for a multi-subnet SQL Server
Availability Group require a static IP entry for each subnet?
The CNO will require an IP address for every subnet it could reside in.
I am running SQL Server 2012 on Windows Server 2012 Hyper V VMs in 2
separate subnets in the same domain. I understand that I will need an
IP from each subnet when I create the listener for my AAG. What I am
unclear on is the configuration of IPs on the underlying Windows
Failover Cluster.
For the underlying WSFC you'll need at a minimum:
Node1 - IP Address for each unique subnet for each network interface
Node2 - IP Address for each unique subnet for each network interface
CNO - IP Address for each unique subnet
EX: 2 nodes, 2 subnets, 1 interface per node, subnets 192.168.1.1/24 and 192.168.2.1/24
Node1: 192.168.1.10
Node2: 192.168.2.10
CNO: 192.168.1.20, 192.168.2.20
Also, if the server hosting the secondary replica does require its own
IP, does it also require its own unique cluster name (and can you
explain why this is necessary)?
I'm not sure I understand this part of the question. All of the resources can only belong to a single cluster - there is no cluster inside of a cluster thing.
Edit - I looked at the link that you posted and I'm not sure why the author stated "•Cluster name for each node". My only guess is they meant each node needs a name and IP (for the node). Otherwise it's not a correct statement, the author should probably be contacted.
Best Answer
An important note is to ensure you have databases fully backed up before beginning any migration path, particularly when moving between domains. File permissions, and other problems could result in data loss & require reverting to those backups.
If you are using Windows Server 2019
With Windows Server 2019, migrating a cluster to a new domain is supported via a straightforward process documented here.
Given most folks are still on 2016 and older
With Windows Server 2016 & older, the migration process involves destroying the cluster, and rebuilding it on the new domain. Given the need to destroy & re-create the cluster, I recommend uninstalling before & reinstalling after the SQL Server installation. You will want to have good backups before the migration as well.
The instructions to move a Windows Server Failover Cluster to a new domain are included in the docs, and quoted below.
Build a fresh cluster
Building a fresh cluster & migrating to it is going to be your best option. (And moving to an Availability Group at the time would be my preferred path, too!)
If you don't have new or extra hardware to build new, you can do something like this to reuse existing servers:
Documented Cluster Migration steps: