SQL Server domain migration (& renaming)


I have to do an SQL Server 2005 domain migration & renaming

Is my checklist complete?
Is there an impact if the "Database Owner" is from the old domain?

--Domain migration
security tab of the sql server:
check old domain groups/add new domain groups

sql services::
deactivate all sql services
change the service accounts to the new domain accounts

domain::
change the domain of the server

Reboot and activate the deactivated services.


--Rename SQL Server with default instance
Pre-Step Delete all your maintenance plans and recreate them after you changed the name
sp_dropserver <old_name>
GO
sp_addserver <new_name>, local
GO


ALSO_DBAsked:
Who is Participating?
 
Jon WinterburnConnect With a Mentor Commented:
That looks about right. As long as you know the SA password, do not worry about database ownership. If you run into issues with db ownership, you simply need to connect to the db under the sa account and then run the following stored procedure:

exec sp_changedbowner 'sa'

This will change the owner of the db to sa and then you can leave it as that or if you want a different owner on the new domain, simply change it to that. In fact you could even change the db owner to SA before the domain migration (obviously make a note of the current db owner so you can change it back later). That way there is definitely no problem with ownership.

To put your mind at ease, what I have mentioned is also documented at the following link under section "Step 3: How to resolve orphaned users" at: http://support.microsoft.com/kb/314546

I recently had to remove an old NT4 domain from a trust with our AD domain and a user from the old domain was an owner of a db. I did not notice this till after the old domain was removed, but as I am a domain admin and db admin I could still access the db and simply changed the owner to the correct one in the new domain with no problems.
0
 
MikeWalshCommented:
For Renaming: http://msdn.microsoft.com/en-us/library/ms143799.aspx some additional info and troubleshooting tips.

For Domain Migration --> the key is to add the new service accounts. If you use mixed mode, make sure you know a SQL authenticated account just in case. Also look at who owns the jobs and databases. Is it a domain user? Are these users on the right domain? verify after the change also.

Do you have a development instance you can try this on first to prove out your checklist and any gotchas that you bump into in your own environment?
0
 
DBAduck - Ben MillerPrincipal ConsultantCommented:
I would also be sure to remember that you should change the Service Accounts with the SQL Configuration Manager instead of Services, or other ways so that the Encryption Keys will be correctly handled.  If you do not do this then if you have encryption keys (Symmetric, Database Master, Assymetric keys) then they will break.  Always a good practice to change them with the SQL Config Manager.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.