I was doing a database server migration for a client this week that involved a local database server with Access front end. And this was being kept in sync using merge replication with a database on a hosted, dedicated server that had a web front end. The client had originally asked for help with some problems with the merge replication but decided they'd upgrade their SBS 2003 server at the same time. I never got to have a planning meeting with the client after they’d decided on upgrading the servers so I just had to show up and start day one by planning out how best to design the new systems, acquire the installation media, handle the database migrations with as little down time as possible, etc. I recommended to move the database off to it's own server and upgrade the database at the same time. Both local and remote servers were running SQL 2000. We decided to upgrade to the latest and greatest since they had the licensing for it, SQL 2008 R2 Ent for local and SQL 2008 R2 Web Edition on the hosted server. In order to minimize downtime I suggested we setup the two new servers and get the databases working with merge replication and then test the Access project and websites to make sure we new what all was required to migrate them. Then use log shipping to keep the new databases in sync until they were ready to cutover. Little detail I forgot about log shipping on SQL 2000, it required Enterprise Edition back then, and the client had standard edition of SQL 2000. OK, plan B, once everything is working between the new servers we'll make and then restore log backups right before taking the applications offline and then we have to make only one more small log backup, so that it takes less time.
Once I got the Access database pointed at the new server and started testing I ran into a problem referencing initcolvs. SSC has a couple of short posts about it and the most helpful one I found was http://www.sqlservercentral.com/Forums/Topic458279-338-1.aspx
initcolvs turned out to be a function call made by legacy triggers used in replication. I guess sp_removeredbplication doesn't get everything. Had to remove all the triggers manually and then moved on to the next problem.
That next problem turned out to be a bunch of legacy DTS packages. The migration wizard requires the DTS runtime be installed on your 2008 R2 server. So whether you're migrating them to SSIS or just importing them onto the server, you'll have to install the SQL 2005 Backwards Compatibility feature, which is a separate download.
This link will get you started if you need it: http://msdn.microsoft.com/en-us/library/ms143755.aspx
So a little while later I had the DTS packages moved over and was starting to redirect them to the correct server. I'm not sure yet that they are going to work without modifications to the Access project. I'm guessing not. It might be easier to go back and do an import instead and leave them as SQL 2000 DTS packages. Either way, I ran out of time for the day and so I'll have to get back to it when I return to that client in a few weeks. If you're wondering what happened to the new hosted server, the client didn't purchase it until halfway through day 1 and it still wasn't available at the end of day 2. I only had two days scheduled originally because that job was to troubleshoot some merge replication issues and make some recommendations on best practices/fine tuning their databases. Doing a database upgrade and migration of two servers, combined with a Small Business Server 2003-2008 upgrade just doesn’t fit into two days. Now I’ll have to do a full restore when I go back and they’ll be paying for both hosted servers for a month. Luckily I kept good notes of all the steps necessary post database restore that I’ve encountered up to this point. Planning... if only there had been a little more planning done.