July 27, 2007 at 2:06 pm
Initially, the articles I read suggested that doing an "in place" upgrade is the simplest path, with the drawback that if something goes wrong, I'll have to re-install 2000. And that migrating is more difficult since I'll have to recreate all the logins , security, replication ....
Then I saw a series of posts saying migration is the best ... backup & restore (or detach, reattach) the DBs to a new instance of SQL 2005. What about the systems DBs, such as MSDB and the DTS packages ?? Also, If I migrate to a new instance of SQL, how will I re-establish the connections from other servers ? They are replicating or DTS-ing to ServerA, but now SQL2005 is on ServerA\SQL2005 (Same server, different instance.
In the scale of things, we have fairly simple environments, although our Production database is almost 300 Gig.
July 27, 2007 at 2:18 pm
Do you have a budget? there are a variety of 3rd party tools to make your life easier. otherwise, you are looking at variations on brute force scripting and other efforts.
Search the various forums here for upgrade or conversion to see what other people have said. Then you have to weigh the evidence and decide for yourself.
Good luck, have a good weekend, and enjoy the adventure!
July 27, 2007 at 2:51 pm
Is there a pressing business need to upgrade to 2005, if not it might be best to wait until Febuary 2008 for the release of SQL 2008.
July 30, 2007 at 3:39 am
Personally, I would always use a clean install of SQL 2005 instead of an upgrade in place. Doing an uninstall of SQL 2000 folled by a SQL 2005 install gives a far cleaner result that is less likely to give long-term support issues.
We have deleloped some scripts to export DTS, SQL Jobs, logins, etc to make the upgrade process simpler. If anyone is interested in these, send me your email address and I will send you our scripts. We also have what I think is a good SQL 2005 Build document that covers a few things that did not get into the MS documentation - say if you also want this.
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
July 30, 2007 at 6:25 am
I did an upgrade in place once, never again. Use it as a way to upgrade your databases if you wish, but then get a new clean sql 2005 install and migrate the databases.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
July 30, 2007 at 6:46 am
I'd definately prefer to backup the SQL2000 then restore in SQL2005. This should allow better testing and planning. I used SSIS to copy logins, messages etc. Don't forget to run the upgrade advisor first.
I hit the following gotchas:
-make sure that logins and database users work OK;
-One server had different collations, which I only noticed half way through the process. I then had to standardise on one.
-One SQL2000 view had an order by clause which a stored proc relied on. when the dbase came to SQL2K5 our names were is a random order.
July 30, 2007 at 7:57 am
The articles & documentation make it sound as though upgrade in place is the thing to do .... Why is it such a bad idea ??
If I install a new instance of SQL 2005 on my SQL 2000 server, and migrate everything from 2000 to 2005, I now have 2 instances: ServerA and ServerA\SQL2005. All my other servers are referencing the original instance name of ServerA. How do I resolve that problem so that communication between the other servers will still work ??
" ...... Is there a pressing business need to upgrade to 2005 ...." Not at this point ... we just wanted to stay current and take advantage of the performance improvement and other enhancements.
July 30, 2007 at 9:38 am
The best approach for getting a fully functional and fully supported SQL 2005 is
a) Script out DTS, Jobs, Logins, etc from SQL 2000
b) Detach user databases
c) Uninstall SQL 2000
d) Install SQL 2005
e) Load into SQL 2005 everything you took out of SQL 2000
If you upgrade in place, some of the configuration tools do not work. My guess is they expect a file format in the SQL 2005 format of \MSSQL.n\, and only find \MSSQL\ as they give an array bounds error after an upgrade in place. This and other issues make me think that an upgrade in place is a bad idea. The last thing you want to find in a few year's time is that a Service Pack or Hotfix simply will not apply to your SQL 2005 instance because of upgrade in place issues. All of this can be avoided by doing a clean install.
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
July 30, 2007 at 10:07 am
We've chosen to migrate rather than upgrade mostly because we wanted an easy way to fallback if something didn't work as expected in SQL 2005 and because some of our users wanted a side-by-side comparison between SQL 2000 and SQL 2005.
As far as communicating with your other servers, you'll have to coordinate changing connection strings to the new intance name. This requires pretty good communication between the DBA group and the developers or sysadmins who administer the application servers.
Greg
Greg
July 30, 2007 at 10:15 am
I hadn't considered uninstalling SQL 2000 prior to installing 2005. If so, 2005 would have the same default instance name that 2000 had, so I wouldn't have connectivity issues with other servers ?? However, there's no way to get anything else out of 2000 that may have been overlooked, or to compare the 2 versions after 2005 is installed. Unless perhaps 2000 is re-installed later with a new instance ?
July 30, 2007 at 10:58 am
" ... If you upgrade in place, some of the configuration tools do not work. My guess is they expect a file format in the SQL 2005 format of \MSSQL.n\, and only find \MSSQL\ as they give an array bounds error after an upgrade in place. This and other issues make me think that an upgrade in place is a bad idea. The last thing you want to find in a few year's time is that a Service Pack or Hotfix simply will not apply to your SQL 2005 instance because of upgrade in place issues. All of this can be avoided by doing a clean install. ... "
And Microsoft hasn't addressed this ??
It just seems that manually migrating everything is much more prone to error that doing the upgrade-in-place.
July 30, 2007 at 10:59 am
What went wrong ?
July 30, 2007 at 11:12 pm
Hi Edvassie,
I am also facing so many problem in upgradation from 2000 to 2005 kindly send me scripts to export dts, sql jobs, logins, etc which you developed. your co-operation would be appreciated.
July 30, 2007 at 11:20 pm
July 31, 2007 at 3:04 am
Another issue about upgrading in place...The SQL 2005 install can have problems if the system databases are not in their default locations.
Our SQL 2000 standard is that each database has its own folder with data and log files on separate drives, so we would have (simplified) E:\SQL\master\master.mdf, E:\SQL\model\model.mdf, F:\SQL\master\mastlog.ldf, etc. On one upgrade in place the upgrade worked OK, leaving the new SQL 2005 system database mdf and log in the standard default location. In 2 other trial upgrades in place with similar system database locations the upgrade failed leaving SQL totally unuseable. The only way out was to uninstall SQL and do a clean SQL 2005 install.
Our standard for production upgrades is to always uninstall SQL 2000 and do a clean SQL 2005 install. (After problems with applying SP2, our standard is now to leave master and mssqlsystemresource mdf and ldf in their default locations.) My company is very risk averse and the risk of a production upgrade in place failing or giving long-term support issues is more than we are happy with. Each site has to make their own decision on these issues.
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
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply