November 18, 2008 at 8:11 pm
I've got a large database that I need to migrate from SQL2000 to SQL2005 AND change the collation. I could use BCP out and in but I'm looking at a 24 hour downtime like this. I thought I'd look at doing transactional replication as this could be done with no downtime, then when I'm ready to point the apps to the new server I can stop subscription it will all be sweet.
In tests I've done I always get a "cannot update " on the identity column, even when I try apply the initial snapshot. THis is even with NOT FOR REPLICATION on the column on the subscriber side. I've tried a 2000 subscriber and this works fine but it then doesn't replicate any updates done on the publisher.
Does anyone know what I need to do to fix this?
Versions are:
SQL2000 SP4 32bit
and SQL2005 64bit
Thansk
Leo
November 19, 2008 at 6:51 am
How did you create your initial snapshot of your database? If you use the NOT FOR REPLICATION option on the publisher and allow replication to create the snapshot, the subscriber will not have IDENTITY columns.
This is probably not what you really want. One of the issues with replication for something like this is that the replicated database is inherantly different than the publisher. Your down time sounds like it is all of the data copying. Have you considered log shipping?
You can log ship from a SQL 2000 database to a SQL 2005 database if you do not bring the database online. You can restore a SQL 2000 backup leaving it offline and then restore log files to it. When you bring the database online it upgrades it to the SQL 2005 schema. Doing this may minimize your down time. Copy over and restore your full backup, restore each log backup you have until you are up to the lastest backup. Stop user access to the old database, make a final log backup and restore that bringing the new database online. Then you have to update statistics with fullscan and you should be in business. You will still be offline for a bit of time and user access will be slow while statistics are being updated, but your down time should not be 24 hours.
November 19, 2008 at 12:05 pm
Thanks for the reply.
As I mentioned we are also changing the collation, so we can't just restore and logship. So I create the new ,empty database from scripts. I then have to load all the data (about 150GB after index builds etc.). With BCP I can do this in about 16 hours, then we need about 4 hours to fully test. I've added anoter 4 hours "fat" so the whole process would take 24 hours. I had hoped I could replicate to the empty database.
Leo
November 19, 2008 at 12:13 pm
In that case, you can, but it will be a bit of work.
The trick is to modify the procedures that get created for replication. By default, replication will create insert, update, and delete stored procedures for data modifications. You can go into these and make changes. In your case, you need to add the SET IDENTITY_INSERT statements for the insert procedures.
November 20, 2008 at 8:32 am
November 20, 2008 at 12:25 pm
Log shipping won't allow me to change the collation.
November 20, 2008 at 12:37 pm
sounds like a job for SSIS!
replication doesnt replicate all objects, for instance if one of your tables has no PK it will not replicate. Then you have all the stored procedures, views, etc that use that table??
DTS\SSIS sounds the way to go
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 20, 2008 at 1:09 pm
Is there a reason that you can't change the DB collation once you've restored your last log?
November 20, 2008 at 1:24 pm
DTS? SSIS? - NO WAY. This is a once off and it also involves making changes to stored procs and some user defined data types to make them 2005 compatable. I've already got complete scripts for all objects, plus patches for the changes. I've got scripts to auto generate all the BCP out and BCP in code, including automaticly generating the SET IDENTITY_INSERT (on/off) if the table has an identity.
I fiddled with DTS and it performed so badly and essentially also ended up with downtime.
Actually there are about 7 databases, 2 of them over 70GB. All with the "wrong" collation due to an ititail build error 6 years ago. I'm taking the chance I have with the move to 2005 to fix a whole lot of bad practices, clean up and standardise.
November 20, 2008 at 1:32 pm
Last time I looked changing the DB Collation didn't change the collation on the individual columns. This is a bit more tricky/risky. Has this changed in SQL2005.
This also doesn't resolve the deprecated and changed features from 2000 to 2005. This is a front line app so I want 2005 in 90 mode.
I've actually been through this before on another database, another site. There I had enough disk space to restore side by side and SELECT INTO directly from the restored DB to the new DB. This time the DB's are just to big and I can't get that much space. I've tested doing a BCP out to SAN and then BCP in, then add all indexes, triggers etc. It works well but requires the system to be down as I can't do updates to the live data once the BCP starts.
November 20, 2008 at 2:28 pm
You are correct - changing the collation on the DB does not change the collation on columns. However, you could easily write a script that would look for the old collation and generate ALTER TABLE statements to change to the new collation. You can also change the compatability level to 90 after applying your last log.
The nice thing about the log shipping option is that you can bring the DB out of read-only mode and test the application of all your scripts, then when you know they work just reapply the original backup and logs to get back into read-only mode. And as someone else said, when you use replication you have to account for all your other DB objects. With log shipping you're getting a bit for bit copy so you shouldn't drop anything in the move.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply