Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
homebrew
Posting Yak Master
114 Posts |
 Posted - 2007-07-30 : 11:40:55
|
| 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.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 ?? |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-07-30 : 12:08:57
|
| OBviously you will do this in a scheduled down-time. Generally the migration plan I have seen is back up db, install 2005 (after uninstalling 2000), restore the db, change compatibility level to 90. before uninstalling 2000, script out the logins and permissions, recompile them on 2005. Replication has to be setup again. As for DTs packages, I think some of them work as-is, some of them need to be rewritten.In any case, I am assuming you would do a series of tests in a dev environment before upgrading the prod servers?Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
 |
|
|
homebrew
Posting Yak Master
114 Posts |
Posted - 2007-07-30 : 12:18:56
|
| Yes, I have a test area. Initially I was planning on installing a new instance of SQL 2000, and doing "upgrade in place". But maybe it would be better to test by installing SQL 2005, and copying over all the SQL 2000 databases, logins, replication etc ...How do the DTS packages get migrated with this method ?? |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-07-30 : 12:20:30
|
| I have "heard" about issues with inplace upgrades. We are currently doing 2005 load tests and our plan is to uninstall 2000, reinstall 2005 and restore db.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-07-30 : 12:24:21
|
| If you have "your own stuff" in the "master" database and a bunch of things in MSDB (jobs, Backup history, etc) and a fair number of Logins / Users then you are going to be better with an Upgrade. But its a one-time-deal, so make sure you are well backed up and can revere the process if the upgrade goes completely pear-shaped.Kristen |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-07-30 : 12:27:30
|
| The key is to test the entire process, documenting each step, atleast a few times. The more you test higher will be the confidence level.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-07-30 : 12:30:13
|
Well my two-pennyworth would be that its very hard to start with an old server which has had lots of patches, stuff installed, removed, reinstalled, etc. in order to make an "identical" QA machine to then do a test upgrade AND have 100% confidence that the Real World Live Server is going to behave exactly the same  Which is probably why folk go the Migrate-to-brand-new-machine route!Kristen |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-07-30 : 12:47:06
|
quote:
Originally posted by Kristen
Well my two-pennyworth would be that its very hard to start with an old server which has had lots of patches, stuff installed, removed, reinstalled, etc. in order to make an "identical" QA machine to then do a test upgrade AND have 100% confidence that the Real World Live Server is going to behave exactly the same Which is probably why folk go the Migrate-to-brand-new-machine route!Kristen
I agree it can be painful but its worth all the trouble. No matter how much we think we are prepared for the day, there is always some little configuration think we would have missed. You can always find a development machine and install an image of basic OS with security related patches (assuming most prod servers have nothing but SQL Server on them). I think it comes down to balancing all the variables.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
|
|
|
|
|
2000 to 2005: Upgrade or Migrate ?