Can I migrate a SQL Server cluster to a new AD domain?

  • Due to what one could consider the equivalent of a company merger I am tasked with the job...wait that should be phrased "opportunity"... of migrating our Microsoft SQL Server 2005 production cluster and SQL Server 2000 production cluster along with the other tiers of SQL Server from our existing AD Domain to a different AD Domain. This cluster is attached to a SAN. I am in the process of setting up a test cluster and test plan.

    According to various articles, particularly, and amongst others. Microsoft does not recommend migrating a production environment. Instead they recommend rebuilding the cluster. First I want to know why? What caveats and hidden surprises will I find? Second, if I must rebuild the cluster I presume this means I must completely reinstall SQL Server. Or is there a way to leave SQL Server installed, blow away the cluster, and then rebuild the cluster over the existing SQL Server installation? All this while the SAN remains behind in the existing AD to be migrated at a later time.

    Okay, let's assume I must resort to rebuilding the cluster and reinstalling SQL Server. I'm thinking the best approach would be to stop all SQL Server services on the existing cluster, take one last backup, copy all the .mdf, .ndf and .ldf files to a "safe" location. Rebuild the cluster, reinstall SQL Server, stop SQL Server services, copy the .mdf, .ndf and .ldf files to their respective locations, start SQL Server services, head out for a round of golf and a couple brews and everyone lives happily ever after....then of course I wake up from my fairy tale dream with 2000 end users and upper management screaming for their data.

    So what am I missing? What are my "gotcha's"? What about full text search, replication, linked server, etc? How about the SAN, does anyone have experience with that?

    Any insight and wisdom you share is highly appreciated!

  • How about build the new cluster first, set up logins, restore a copy of databases, set up all the linked server/replication/jobs for testing purpose, also should have application testing on the new environment, get everything verified and signed off.

    Even for DBA tasks, make sure backup/restore works on the new environment. DB optimization job running is reasonable comparing with the existing cluster. This is part of performance measurement.

    For the real migration, you only need to copy over the database. By that time, either backup/restore or detach/attach the mdf/ldf files to move over the data itself. At that time, you can stop SQL services. This will shorten the downtime.

    “Or is there a way to leave SQL Server installed, blow away the cluster, and then rebuild the cluster over the existing SQL Server installation?” à I do not think so, as SQL is built on top of windows cluster.

    Or your environment allows you to attach the existing SAN to the new cluster? The plan will be a little bit different.

    No matter what you need a backout plan.

  • Thanks. I should have probably explained that we won't be getting new hardware so the existing hardware will have to be pulled into the new domain. We will be using the same IP addresses and machine names along with the same cluster and virtual names. This would be so we have minimal changes to the client applications. We will have a temporary trust setup between the two existing domains, so the user logins will be migrated to the new domain first using the Active Directory Migration Tool (ADMT) from MS. At that point the applications may still work since the underlying SID is still the same. However since the domain is actually specified when setting up the SQL Server logins they very likely won't work at which point I will change all the domain names for the existing login to SQL Server.

    Two options here I can think of script all the roles and userids, make a global change for the domain name, run the scripts to add all the new domain logins and drop the old logins. Or two, to do the "unthinkable" and update the system tables :w00t: in master and all user dbs...UPDATE sysxlogins SET [name] = 'NEWDOM1' + SUBSTRING([name], 8, 50) WHERE [name] LIKE 'OLDDOM1%' ....then for every database UPDATE sysusers SET [name] = 'NEWDOM1' + SUBSTRING([name], 8, 50) WHERE [name] LIKE 'OLDDOM1%'. I could of course use the stored proc sp_MSforeachdb with that last user db update.

    I'm sure this will be an "interesting" experience and I foresee many beautiful Spring or Summer evenings and weekends being spent in the office....:(

  • Call Microsoft Support (PSS) and ask them these questions. I suspect they will recommend you build a new cluster on new hardware. As a minimum I suspect they will say to start with a new Windows install, and build on that. Document what they say and recommend an approach to your management.

    Personally, I would strongly argue for new hardware, and prove that everything is installed and working before cutting over Production to the new domain.

    If you take short-cuts you risk having to rebuild the box (a multi-day job) while Production is down. No manager looking to keep their job should approve such bad practice.

    Original author: 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

  • Thanks for the input Ed. I am definitely more inclined to push for some new hardware after what you and Vassie have written. I hope to be in contact with MS sometime this week and will post what I find out.

    It is interesting that something which at first glance seems relatively simple turns into such a large undertaking. It seems to me that this is not a unique situation. Companies get bought, sold and merged routinely. How do these businesses merge their network infrastructure - or do they? I don't have a great deal network expertise, but would it be smarter to make our domain a "sub-domain" of the larger forest than to make our domain disappear and become an independent OU within this larger forest?

    Here's a little more background to the "big picture" and perhaps this will get a little more discussion going. And perhaps this will need to be placed into a different forum...but here goes...

    This is a state governments project. Our state has a "centralized" IT department...sort of. We have a central IT office, but most individual agencies have their own IT department. The larger agencies have a larger and more developed IT department while smaller agencies may or may not have their own IT staff and are much more likely to depend on the central IT office. For better or worse there is some animosity between the IT departments as everyone has their opinion about the best way to do business and the best software to use. I'll stop with that so I don't end up writing a thesis about the pluses and minuses of centralized vs de-centralized IT departments. There is currently a state-wide project underway to centralize our email, calendaring and document storage systems utilizing MS Exchange and MOSS. The central office is promoting "single sign-on" to Exchange by bringing all user logins into their domain. This begged the questions, why not just setup a trust between our domains and will the users still be able to access servers in our domain? The answer were "we don't want the administrative overhead of maintaining multiple trusts" and "you can have a trust only temporarily while you bring all your servers into our domain." At this point this domain migration is "voluntary" if we (all agencies) do not comply we may be forced to comply.

    So my question to anyone reading this is: If you were put in charge of merging multiple independent agencies, each with their own domains (Windows, Novell, ???, ...) into one centralized AD forest and allow for single sign-on to Exchange and MOSS what would be the best approach and have the least impact on 20,000+ employees, thousands (maybe tens of thousands) of diverse web, mainframe, client server and stand alone systems, thousands of servers, multiple SANS and mainframes, tens of thousands of PCs and mobile devices, etc, etc...?

  • I was surprised that no new HW is involved. Considering the size of your organization and many users, buying a new HW is not a big deal. Unless the management allows quite a few hours downtime to rebuild from the scratch, what is the cost/$$? What if the worst scenario happens, it may take a day or more to fix it, what the cost/$$$$ then?

    It may be a good chance to upgrade HW. HW needs to be upgraded every 2-3 years anyway.

    Wish there is a way from network/domain side to do this without much cost. I would like to know as well.

  • Here's your firepower for new hardware and a new setup:

    How to move a Windows Cluster Server from one domain to another

    Right near the top:

    Note We do not recommend performing this type of move in a production environment.

    K. Brian Kelley

  • Yep, I've seen that, had a little chuckle....and quoted that to management. ...and I'm going to experiment with it anyway :w00t: I am approaching management with the idea that we do have hardware in development and testing environments (not to mention production) which need replacement. We could buy a couple nice servers and cycle some of our older equipment out the door after migrating. Of course this might only address my team and a dozen servers. There's probably another 12 dozen servers to deal with. Perhaps this could also be a push for more server consolidation and virtual servers.

  • It's been a few years when we had that coversation with MS and the basic gist was, "We'll be more than happy to put resources on site to assist, and they'll do everything they can to make it work, but at the end of the day, if it doesn't work, it's on you."

    K. Brian Kelley

  • Be very glad it is not a SQL7 cluster... I had to un-install SQL7 from a cluster (remotely) and after 8 hrs on the phone with MS tech; with no resolution from thier tier3 support, we gave up and drove out there; and rebuilt by hand... Thank the powers that be the site was very good about making backups... and we only lost 3 days of production... 😉 .... (and yes, the customer did not want to purchase a new cluster environment).

  • Coyote Blue (3/25/2008)

    Be very glad it is not a SQL7 cluster... I had to un-install SQL7 from a cluster (remotely) and after 8 hrs on the phone with MS tech; with no resolution from thier tier3 support, we gave up and drove out there; and rebuilt by hand... Thank the powers that be the site was very good about making backups... and we only lost 3 days of production... 😉 .... (and yes, the customer did not want to purchase a new cluster environment).

    Business drives technology. Despite our best efforts, we can't always salvage the day from a bad business decision. :crying:

    K. Brian Kelley

  • I guess all we can do as techno geeks (or as a person from another biz group in my company calls us, but they refuse to put it on my bizcard) "Harbingers of the Technopocalypse" is make our best arguments, and hope for the best... 😉

  • I hear ya. Thanks to everyone for their input. I'll move forward with some cursory testing and hopefully get a chance to speak with MS. At least this is opening up some conversation and making people here try to think through the consequences of this change. I'll post my results when I get something.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply