July 31, 2007 at 7:09 am
Migrate...There are too many gotchas to risk an in-place upgrade on a production system.
We just ran into another one this AM...bcp in SQL Server 2005 vomits on mainframe formatted dates...CCYYMMDD. BCP importing of dates in this format work just fine on a 2000 platform but will not import on 2005 platform.
It has been a very long and arduous journey to get all the gremlins worked out of Version 2005.
July 31, 2007 at 9:51 am
the trouble with update in place is that all the previous version files are left there too. works same with o/s just not a good idea - you can imagine what the registry ends up like too!
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
August 2, 2007 at 11:50 am
Thanks for all the feedback !
I think I'll make a test instance of SQL 2000, copy over a backup of our big Prod database to test with, and run the "in place" upgrade to see what happens. Then I can remove that instance & try again. If it seems too problematic, then I can install a 2005 instance and try migrating instead.
Does that make sense ?
August 2, 2007 at 11:58 am
Before you start testing, Run the upgrade advisor which would good idea what would be the issues and work for resolution. I have done couple of inplace upgrade on production box, we have little challenge. but overall it went smooth.
August 2, 2007 at 12:02 pm
I think you're the first person who has anything positive to say about "in-place" upgrade. Perhaps it depends on the environment ... some installations are more suited to it, and some are not ??
Since I'm the only SQL person here, I would like to do the least work AND take the least risk (not always possible though)
August 2, 2007 at 1:50 pm
I think you're on the right track when you say "perhaps it depends on the environment".
The migration advocates have rightly pointed out the advantages of a "clean install", but there are trade-offs, just as there are for the in-place upgrade.
I'm one of the (apparently few) people who did an in-place upgrade and didn't have any problems with it. However, I did quite a bit of reading/studying and prep-work with the Upgrade Advisor and made adjustments intended to avoid problems with the upgrade. This was also a pretty vanilla server: just database and reporting services, a few simple DTS packages, and fairly standard maintenance plans. My reasoning was basically this: I was fairly confident that an in-place upgrade would succede, and could afford the added downtime of the worst case scenario if it didn't (i.e. uninstall, re-install, restore everything from backups).
If I would have had a more complex system or more stringent down-time and rollback requirements, I would have opted for the migration strategy and tried to get new hardware so I wouldn't have to mess with named instances and broken connection strings.
That's just my personal experience, for what it's worth (there are lots of people on this board with much more experience than I). Hope it helps.
August 2, 2007 at 2:35 pm
Don't forget to also copy over your MSDB database to get your jobs and DTS packages.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
August 2, 2007 at 2:44 pm
Don't copy or restore msdb from SQL 2000 to a clean install of sql 2005 - there are changes to this database in SQL 2005 that will be lost if you do. If you do an in-place upgrade, then the SQL 2005 changes will get applied during the upgrade.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
April 21, 2008 at 2:18 pm
Speaking about in-place upgrade, is there anyone over here that observed an increase of databases size after the upgrade ?
In fact, the database size has doubled after the upgrade. Any idea about what may have cause this ?
Thanks.
August 2, 2008 at 10:16 pm
HI can u send me both of them my email address is parthikparikh@gmail.com
August 3, 2008 at 3:02 am
i was involved in an upgrade from a sql2000 instnce to a sql2005 instance early last year. The point raised was how do we get the databases onto the new instance. I made the recommendation to detach and copy the databases but someone else preferred back up and restore.
We timed\tested both scenarios and it was 20 mins quicker to detach, coy and re attach the databases. All logins, jobs, etc were scripted and re applied. We went with the detach method for the live migration 😉
I would always prefer a clean install (same with OS, have you ever tried an upgrade from NT4 to W2K3 :sick: ). You may argue there is more work involved but to my mind it is well worth the journey. Just my 2 cents worth 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 3, 2008 at 8:16 am
Thanks Perry,
I would appreciate if you can provide me some materials related to that, I mean some sample scripts you have used for Transfer logins,jobs etc. Also I want a real practical process so that I can help my organization with my knowledge.
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply