Synchronizing SQL Server 2008 with SQL Server 2000

We’re planning an upgrade of our database environment from SQL Server 2000 to SQL Server 2008. This is done by simply detaching the 2000 database files and attaching a copy in the 2008 server. This works beautifully, but once the files has been attached in 2008, they cannot be reopened in 2000. And if we for some reason have to roll back to the old environment after a day or a week, the old database has to be in sync.
What would be the simplest and easiest way to do it? Remember that the two databases are identical and the synchronization should only be active for a short period of time.
Are we talking about a lot of triggers? Log shipping? Replication?
Thanks in advance,
Brian Hallkvist

Yes, you should have a proper rollback strategy before going for upgradation. The upgraded DB can not be restored into old version. Neither you can do Backup/Restore nor Detach/Attach. If the data is not changed then if you have Old version backup you can restore from that backup. But if the data changed in the new version db then it need to be handled carefuly. Database Publishing Wizard can help you in this context. But try in a Testing env before going to Prod.
http://www.microsoft.com/downloads/...C5-BF17-42E0-A410-371A838E570A&displaylang=en
Madhu

Thanks…
The SQL Server Database Publishing Wizard looked very promising and I downloaded it right away.Unfortunately Ï got the message “This SQL Server version (10.0) is not supported.” when I tried to connect to the 2008 database.

Ohh... i never tried with 2008. If it is not supported then you may create a SSIS package which transfer the tables and data. or Replicaiton may be another option to keep the old version in sync
Madhu

Yes… that might be the way to do it. The only thing I’m concerned about is the size of the database. Its’ currently at about 50 GB and growing, and I assume an export/import via SSIS would take too long time. That’s why I thought I would keep the two databases constantly in sync via triggers or the like.

For the ability to Rollback; I would not use Replication or triggers of any kind; as during testing; you dont know what impact this will have on either environment and it will just be another variable to contend with.
The easiest solution is to Upgrade SSMS on your machine to 2008 and then to perform backups on SQL2000, then copy these over and restore them.
You have the benefit of testing your databases in your new environment; but also the critical one of being able to roll back very quickly should something be overlooked come the day you migrate.
You will also need to script out your user details - beware you also need to know the username and password as this isnt scripted for obvious reasons. I'm not sure that if you use the 'Transfer Logins' task in SSIS; if this will copy the passwords also...Cant remember off the top of my head.
HTH
Steve

Thanks guys!
I’ve decided to go with the data export feature in SSMS. That way I can transfer my 4 GB test db in about 15 minutes from 2K8 to 2K. All I have to do now is to check how long it’ll take with the real db, which is about 50 GB.