Allonby IT Limited
Solving real problems with creative technology

AD migration, SQL, SSAS, SSIS, SSRS and SID History

The problems with twins…


The joys of AD Migration, SID History, coexistence and SQL Server.

Imagine, please , the following scenario:

You are a contractor working for the fictitious Allonby Spoon Company. The Allonby Spoon Company or ASC is a wholly owned subsidiary of the equally fictitious Great Allonby Cutlery Company or GACC. The board of GACC sees the increase in healthy eating and so predicts the decline in spoon sales as puddings become passé. They divest themselves of ASC to a fictitious venture capital company called Allonby Investments or AI. ASC is currently on a sub OU in the GACC AD forest ,, and so must be moved to a separate AD infrastructure before it can sever ties with the old parent company. The Binary Tree AD Migration tool has been selected by the client to perform the User and computer migrations, applications and back office are to be done manually.

A new forest is created and ASC.Local comes into existence and trusts are placed between asc.local and to allow for coexistence. SID history is enabled and ADMT adds the sids to the corresponding user accounts in asc.local. The users have been migrated to their new accounts and now the servers are being migrated…

Bob and Hilda manage two separate report servers, one each, but have differing views on how to tackle the migration. Hilda is proactive and looks through her role memberships and for each user object adds corresponding memberships for asc.local users so that when the split comes all will be well. Bob, on the other hand, is suffering from a motivation deficit and makes no changes. The SQL database servers including a failover cluster are to be migrated to the new domain. Next the SSRS, SSAS and SSIS servers are to be migrated.

You have been tasked with sorting it all out…


Migrating the database engine

Well how hard can this be ? Surely we just need to change the logins and service accounts…
Oh and the following…

  • Database Owners

  • Linked Servers

  • Schema owners

  • Credentials

  • System attendant jobs Etc

Not to mention searching every stored proc, view definition and function to check for references to the old domains. I wrote an number of scripts some more successful than others. I’ll share them with you in this section. Run them on all SQL servers as you will be surprised what they throw out. It is the stuff outside the obvious that are the hidden gems that will save you on migration day…

Migrating SSAS

SSAS is probably the easiest just a few role members and data connections to consider. let me show you what to look for an where.



Migrating SSIS

We have to think about connection permissions whether it is to database or file store, where are the packages, where is the config, what needs to be changed.

Migrating SSRS

And it was all going so nicely… And the award for the most difficult goes to SSRS. I learnt so much on this one and I’ll share it all with you, apart from the late nights, foul language and grey hair.



Migrating Clusters

They said it can’t be done, and in a way it can’t, You can not migrate a cluster from one domain to another with a modern version of windows, but here is how to get round all that….

Other useful Gubbins

During the coexistence phase you notice a few anomalies in nested group membership for a starter so this section will cover some of the scripts written to investigate the extent of the issues…