Seeking advice on an unsupported SQL Server upgrade
May 10, 2010 6:30 PM   Subscribe

Can I switch the SQL Server inside a VMWare appliance to a license-fee-free edition without undue pain?

I need to install a certain virtual server package for a school administrative function. It's supplied by the vendor (a government Education Department - don't even bother asking about talking to them for better-than-script-monkey technical support) as a VMWare appliance including Windows Server 2003, and Microsoft SQL Server 2000. The school needs to buy licenses for both of those products before it can use this software legally.

Our virtual server infrastructure here is based on KVM, not VMWare Server; converting the appliance to run OK in the new hosting environment was fairly straightforward. So far, so good.

Now, Windows Server 2003 licensing is not too bad, but SQL Server licenses are expensive. Since the number of concurrent users is likely to be low (tens), I'm convinced that we would not run into capacity issues with SQL Server 2005 Express, which is free to use.

So I'd like to uninstall SQL Server 2000 from the appliance and install SQL Server 2005 Express in its place. But I know almost nothing about administration on Microsoft's SQL Server family, and I don't have source code or documentation for the app.

Since there's no official MS-supported "upgrade path" from SQL Server 2000 Standard to SQL Server 2005 Express, a simple upgrade installation won't work. What do I need to know to get this done by hand? Is it in fact likely to be easy, or am I planning to cost myself more pain than the price of a SQL Server Standard license is worth?

If it matters, the appliance's function appears to be some kind of batching proxy between local web clients and a state-wide upstream database (the appliance runs IIS as well as SQL Server).
posted by flabdablet to Computers & Internet (9 answers total)
 
This is a relatively simple task. Just install SQL Server 2005 Express on the server (it will install as a named instance). Install the management tools (typically a seperate install). Dismount the database files from SQL 2000, mount them in SQL 2005 (you might actually need to do a formal backup/restore -- though maybe not).

Then update your app's ODBC link, or wherever it defines it's database to reference the SQL Server 2005 Express instance.

It really should be rather simple.
posted by SirStan at 7:19 PM on May 10, 2010


Well, the good news is that you're doing this in a virtual machine, so make a good snapshot before you try this. I wouldn't be very surprised if you could just install the new SQL Server Express right on top of it and have it work. At worst, you'd have to point it at the old .mdb files and it could import them into the new install. I'd also expect that you'd have to edit the ODBC datasources, but that might actually carry over as well.

Plan B would be to shut down SQL Server 2000, copy the .mdb files somewhere safe, uninstall, reinstall, and import the .mdb files. You may also need to export the info/users in the master db.
posted by advicepig at 7:24 PM on May 10, 2010


Response by poster: I had a feeling it should be simple. It's mainly my own ignorance getting in the way.

Assuming I can't find the app's ODBC link (which I rather suspect will in any case be hardcoded): would it be possible to dismount the DB files from SQL 2000, then uninstall SQL 2000, then install 2005 with the same instance name as 2000 used to have, then mount the DB files again?

If so: how can I find out what the instance names are, where do I look for the DB files, and how do I perform dismounts and mounts? All I really need, I think, is links to digestible docs - I'm not expecting to be spoonfed.
posted by flabdablet at 7:26 PM on May 10, 2010


Response by poster: And yes, snapshots will definitely be my friend :-)
posted by flabdablet at 7:27 PM on May 10, 2010


Response by poster: I'd also expect that you'd have to edit the ODBC datasources, but that might actually carry over as well.

From the digging I've done so far, I believe that this is what should happen on an upgrade installation from one SQL Server version to a later one; but (presumably because SQL Server 2000 Standard is a for-money thing while SQL Server 2005 Express isn't) there is no Upgrade option made available by SQL Server 2005 Express Setup in this case. Hence my pestering of you lovely people.
posted by flabdablet at 7:30 PM on May 10, 2010


Response by poster: You may also need to export the info/users in the master db.

How is that done?
posted by flabdablet at 7:31 PM on May 10, 2010


I'm no big fan of windows, but there are a few factors that will probably make this easier than you'd ever guess.

It's a royal pain in the rear to hardcode connection info in Windows programming. Most installers would rather make a system level ODBC connection. This should remain the same if the "just install the new one" plan works out. The new install of SQL Server 2005 Express will listen on the same ports and just work.

In Windows, it's often easier to assume it will figure it out and either be pleasantly surprised when it works or start fixing stuff from the half right config.

I'll check back when I'm at the office and see if there are any pointers I can give you after poking around an install for a few minutes.
posted by advicepig at 7:58 PM on May 10, 2010


There is a 'default' instance with SQL Server. MSDE (Sql Server Express) typically installs as a 'named' instance (SQLEXPRESS). There should be some way to install Sql Express as a "default instance" which should assume the same connection defaults that the SQL Server 2000 assumed (as the previous default instance).

http://msdn.microsoft.com/en-us/library/ms143722%28SQL.90%29.aspx
posted by SirStan at 8:04 PM on May 10, 2010


I'm no expert, but I do know you can find the instance name in the registry:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL

Also, another caveat: SQL Express 2005 does not include SQL Agent, so if your appliance is using jobs/scheduling, even if you get it all set up right, it still won't work. MSDE (essentially SQL Express 2000) does have SQL Agent and is still free to use.

I have no idea if this would work, but you could try running the SQL Express install from the command line with the appropriate upgrade parameters.
posted by natabat at 8:35 AM on May 11, 2010


« Older I'm shipping up to Boston.   |   My boyfriend needs a job in Seattle Newer »
This thread is closed to new comments.