We have an old SQL 2000 server, let's call him "oldSQL2K" and a new SQL 2008 server, let's call it "newSQL2K8". Both are in production. oldSQL2K has about 90 user databases on it, accessed by both humans via ODBC connections, and by applications via SQL logins.
I'm looking for advice on how best to go forward with migrating the databases from oldSQL2K to newSQL2K8.
I have manually migrated about a dozen databases by doing a SQL backup on old, and a SQL restore on new. I have already migrated all the SQL logions from old to new, so that's done.
As an experiment, I tried a simple DNS name change, but of course that failed.
Here are the challenges: One of the legacy apps we support that "Must Stay Alive" has the oldSQL2000 server name baked into the compiled code, and the source code is gone! Long story, not my fault but now my problem. This app runs on about 50 workstations.
Our service center has identified about a thousand workstations that will be affected by this migration. Ow.
We do have the SQL Client Network utility on those 50 workstations mentioned, and udpating the alias there does work. Installing it on the other workstations and servers though is something I'd like to avoid if at all possible.
So folks, how would you proceed with this migration if it was you?