1

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?

Improve this question

3 Answers 3

Reset to default
1

I would migrate as you are doing, but isolate those "Must Stay Alive" applications that can't easily be altered and leave them on the old server. Then virtualize the SQL2000 server if you have a VM environment. The old app probably isn't going to benefit from moving to SQL2008, unless you are having performance issues and need more processing headroom, and the VM will be a constant reminder of the Bad Apps that really should be replaced.

Improve this answer
1
  • This is the direction we're going to go. Of all the scenarios available, this one has the least pain involved. Commented Apr 14, 2011 at 15:05
0

Virtualising what is left over is a thoroughly sound idea.... alternatively, you could move them to SQL Server 2008 (after testing that they still work), and use DNS magic to repoint. I've seen this done many times - it works, though it is not as clean.

Improve this answer
1
  • I'm curious what DNS magic you speak of. I did try a DNS name change, so that a ping of "old" would resolve to "new"; but while this worked fine for the filesys, SQL Server would have none of it. Commented Apr 11, 2011 at 16:20
0

I've found myself in similar catch-22 situations before and the approach taken was to move off to another SQL 2000 server what can be easily changed but can't be upgraded to 2008 and once you're down to the couple of applications (like the one you've mentioned) where the server name can't be changed we removed the old server from the domain and powered it off, stubbed in a DNS alias pointing to the new SQL server. Using an isolated network helps you address all the issues you will encounter in your production setup but without disturbing anything.

If you have an isolated network (or can have one created) where you can duplicate the server names and have at least one client to test with I would use that for evaluation. This will allow you to test a DNS alias. Copy over the backup for each given database and restore it to SQL 2008 x64 or whatever edition/architecture your prod server will be and test with different compatibility levels per database.

I hope this helps with some ideas!

Improve this answer

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.