Tuesday, June 20, 2017

Migrating SQL to New Server (with different AD)

After 8 years I decided to migrate a server running w2008 with sql server and Active Directory to a new 2016 server.  The AD is small so I decided to create a new server with a new AD and copy the users and passwords manually.  I could have made the new server a backup AD for the old server, then demote the old server and promote the new server AD and the following steps would not have been necessary, but I wanted a clean slate.  

The problem was migrating the sql users to the new directory, as the logons, even if the same names, would not have the same SIDs as the old logons and hence would not be recognized by SQL server.

This is the basic order that worked.
New server -
Enter all users and groups in AD
Create server logons in SQL with the new AD users and assign appropriate server roles if any.
Copy the databases from the old server (attach files or restore from backup).

(It will save considerable time if all the securables for different users in the database are assigned through roles and not specific database users.  )

Delete  the users in the database.

Using the server logons, choose their properties and go to User Mappings. 
Map the logon to the appropriate database and database roles. 
If there the user had securables assigned not defined in the roles, then you need to right click on the user in the database and define the securables again.


0 Comments:

Post a Comment

<< Home