upgrading sql server 2000 database to sql server 2012

Question

Seeking some advice about an oudated database system we're working with.... hope this is an appropriate place to post this question.

I work with a city government arts program. In 2004, we contracted with a local database developer to develop a system that allows us to track student participation in our after school art classes. It tracks each student's address, demographics, what classes they enroll in, daily attendance, paperwork/documentation, class progress, etc. It was built on SQL Server 2000.

Because of issues with the city's technology systems back then we were not able to install it right away. When they finally got around to supporting us (I think around 2006 or 07), they gave us SQL Server 2005. We ended up installing an instance of SQL Server 2000 on SQL Server 2005 which itself runs on Windows Server 2003.

As of a few years ago, we are no longer under the city's technology systems and manage our own systems through independent contracted consultants. Last year, we terminated our contract with the original database developer because he wasn't following through on a number of issues we needed addressed over the years. Last year, we also went through a major overhaul of all our servers and desktops.

We've upgraded all our other servers but we still have one server left that's still running SQL server 2000 and Windows 2003 just because of this one outdated db system. We'd like to upgrade both SQL Server to 2012 and Windows Server to 2008R2 on this last server.

I'm trying to budget for how many hours it would take to upgrade a SQL Server 2000 database so it will run on SQL Server 2012. I realize you can't really tell much without actually seeing what we're working with, but all I'm looking for now is a ballpark figure (i.e., is it in the 20-30 hour range? 50 hours? 100 hours?).

If there is any other info I can provide to help get at an estimate feel free to post a comment....

Thanks for any help!

Answer 1

You can easily restore your 2000 DB to any higher version of SQL Server. The issue you may face is feature deprecation. If you aren't using any deprecated features, the actual DB migration is a non-event for the most part.

You can also use Profiler to trace for deprecated calls.

Answer 2

You cannot directly upgrade a SQL 2000 database to SQL 2012. You need to first upgrade your databases to SQL 2005/2008/R2 after that you can upgrade to SQL 2012.

Reference http://technet.microsoft.com/en-us/library/bb677622.aspx

Answer 3

As someone else pointed out, you can't upgrade directly from SQL 2000 to SQL 2012. you will need to upgrade to an intermediate release, it seems like you probably have sql2005 around somewhere, and then you can upgrade from there. This upgrade process is very simple, essentially you would backup your sql2000 database, restore it to sql2005, backup that sql2005 database and restore it to sql2012. The upgrade processing happens during that restore operaton. After you are done with that restore to sql2012, you should be sure to perform a full reindex of the database (per MS best practices) and I like to do a full DBCC CHECKDB. The amount of time required will mainly depend on the size of your database and the speed of your disk systems. If I have a small outage window available, I always like to try this once on a test machine before I try it in production so I have a good idea of how long it will take. If you can't do that, you could look at how long it takes to back up the database (restoration generally takes a bit longer) and the time it takes to do the current checkdb and reindex processing and use those as estimates. If you are moving to a new server, it is probably faster, so your estimates using the old server will probably be on the high side.

BUT: That is the easy part.

The more complicated part is testing your application so that you have some level of confidence that it will work properly on the new version. SQL Server isn't 100% forwards compatible. For example, if the old application is using system tables for anything, those system tables may have changed quite a bit (particularly from sql2000 to sql2005). Another example might be that the app makes assumptions about there being very loose security on the server (xp_cmdshell being a popular culprit) and that might no longer be true. Another issue might be required conversions from DTS to SSIS. Another issue might be lack of source code for some critical extended stored procedure that your vendor wrote, or something that prevents you from porting it to the SQL CLR. Another might be a lack of 64 bit drivers to third party databases. It's hard to quantify how much testing time is needed, even if you are pretty familiar with the app, and it's very, very hard to know how much time would be required to fix the code if you do find a problem. Since you don't have a good relationship with the vendor, that makes things harder. Since the app wasn't upgraded from sql2000 to sql2005 back in 2007, I can't help but suspect that there was some sort of roadblock discovered then and that it probably hasn't been fixed in the interim.

In short, getting the data onto sql2012 is easy. Making sure that your app works isn't. If it were me and I had no budget, I'd be looking at running a P2V on the existing old server and sticking the image into a VMWare (or HyperV) infrastructure that already exists. That way, you can junk the old server and won't have too much work to do.

Answer 4

I don't think there are much issues. Try restoring your old database to SQL Server 2012 using Management Studio. You don't need an extra budget allocation for this.