June 3, 2008 at 9:02 pm
Seems we can use log shipping to upgrade SQL2000 to SQL2005, when need to upgrade to a new server.
Upgrading a SQL Server 2000 Log Shipping Configuration
http://msdn.microsoft.com/en-us/library/ms188297.aspx
Anyone has related experience to share?
June 3, 2008 at 9:20 pm
No. That link actually describes the procedure to upgrade an existing log shipping configuration from Sql 2000 to Sql 2005. It does not deal with migrating/upgrading from Sql 2000 to Sql 2005
[font="Verdana"]- Deepak[/font]
June 4, 2008 at 7:24 am
You mean it can not be used for database upgrade? Do you know why not?
June 4, 2008 at 8:54 am
Log Shipping copies logs to a different server for DR purposes. It has nothing to do with updating SQL Server binaries.
"Got no time for the jibba jabba!"
-B.A. Baracus
June 4, 2008 at 9:09 am
Database is being Logged shipping from SQL2000 to SQL2005. If we can not use SQL2005 database on the destination server, what is the log shipping for? 😉
June 4, 2008 at 9:47 am
in microsofts infinite wisdom they decided that log shipping using
transaction logs for sql 2005 would not support sql 2000 database
(this is kind of there way of forcing everybody up sql2005
the make it easy to move from 2000 to 2005 but doing anything with the 2000 servers becomes more and more difficult)
so 2000 has to go to 2000 and 2005 goes to 2005
same as you can restore a 2000 database to 2005 server
but you cant restore from a 2005 server to 2000
June 4, 2008 at 9:53 am
From the original post, it wasn't clear that you were referring to upgrading a database itself. So in essence, I suppose during the logshipping restore process you are essentially upgrading that database to 2005 (I'm still a new dba, so I'm winging it on this...).
However, if this is a DR scenario, and you have a disaster and need to restore back on the 2000 server, you could run into issues.
"Got no time for the jibba jabba!"
-B.A. Baracus
June 4, 2008 at 10:08 am
To clarify, I want to upgrade SQL2000 database from the existing server to a new server as SQL2005 database with minimum down time. Backup & restore/detach & attach take too much time. The purpose is to upgrade not log shipping.
While I read the link, seems like we can use log shipping to achieve this. Log shipping a SQL2000 database to a SQL2005 database, once SQL2005 is the primary, no need to have log shipping any more. Not sure how it works in a real world.
June 4, 2008 at 10:19 am
Your last assertion is correct - you can log ship from 2000 to 2005. I've personally done this before when faced with the same scenario you are in.
Kendal Van Dyke
June 4, 2008 at 11:02 am
Thank you Kendal.
How big was your database? How much down time in your case? Any performance problem for using this method?
June 4, 2008 at 2:54 pm
Vivien Xing (6/4/2008)
Thank you Kendal.How big was your database? How much down time in your case? Any performance problem for using this method?
I think it was somewhere around 20-30 GB. Because I was doing log shipping the downtime at the time of the switch was very minimal - a few minutes to take the last log, transfer it to the new server, and restore it, plus whatever time it takes to make connection string changes in applications which need to talk to the new server.
A couple of caveats that I didn't mention before...you'll only be able to restore with NORECOVERY; you cannot use a standby file to make the DB readable because upgrades need to be applied to the DB that can only be applied after the final restore. Also, your DB will be left in SQL 2000 compatability mode. If you want to fully leverage SQL 2005 don't forget to change compatability to 2005.
Kendal Van Dyke
June 4, 2008 at 6:45 pm
Thank you for sharing. This method is definitely worth a try.
I used full backup & transaction log backups for a 300 GB migration, not log shipping though.
Restoring the full backup took near 3 hours, but the consequent t-log backup restore plus the last t-log backup restore took over 40 minutes. There were only 5~6 t-log backup files, a couple GBs in total. It would not take that much time if it were restored to SQL2000. I doubt what was going on inside the t-log restore.
Not sure whether you had the same situation, may be your t-log backup is small and would not feel much difference.
June 4, 2008 at 7:26 pm
Vivien Xing (6/4/2008)
Database is being Logged shipping from SQL2000 to SQL2005. If we can not use SQL2005 database on the destination server, what is the log shipping for? 😉
Please refer this link, As others pointed above you cannot use log shipping from Sql 2000 to Sql 2005. The below link discusses on the same topic which might help you..
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=189557&SiteID=1
[font="Verdana"]- Deepak[/font]
June 4, 2008 at 7:41 pm
I should also mention that I rolled my own log shipping solution and didn't use what's built into SQL Server.
The point remains that you can still apply a full backup of a SQL 2000 database to a SQL 2005 server and apply subsequent transaction logs just fine.
-- Kendal
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply