In-Place or Side-by-Side Upgrade?
-
When migrating a SQL Server 2000 database to 2005 how do I determine which is the best option to pursue (In-Place or Side-by-Side), and do you have a recommendation? If you have a recommendation, do you have suggestions on a plan of action for the option you recommend?
My server environment is not complex - there are the system databases and two key databases (under 20GB in size). There is one custom .Net application that runs against this database. I have a development and staging environment so I can test the upgrade in a sufficiently similar environment before rolling out to production. The hardware is older, but will not be replaced. We have a number of stored procedures, but very few DTS packages. We are a small support group so less complicated, less risk is better.
I’m slowly working my way through:
http://www.microsoft.com/technet/traincert/virtuallab/sqlupgrade.mspx
http://technet.microsoft.com/en-us/library/ms143516.aspx
http://technet.microsoft.com/en-us/library/ms130214.aspx
& http://www.microsoft.com/technet/prodtechnol/sql/2005/sqlupgrd.mspx
I’m love real world examples, experiences and a gotchas...
MANY thanks,
jd
-
if you need to upgrade an existing instance of sql server you can make use of INPLACE UPGRADE.......it will overwrite the existing sql instance directly for that you need to make use of upgrade advisor and then check if everything is fine with the db before you upgrade........in side by side upgrade you can very well install a different instance of sql server apart from the one that is already existing...you can make use of detach/attach or backup/restore or even copy database wizard to upgrade the dbs to sql 2005..........I would recommend you to go ahead with side by side upgrade as it would be simple and easy to rollback in case of any errors while upgradtion........refer these links,
http://sql-articles.com/articles.php?pageNum_alltop=4&totalRows_alltop=30
http://sql-articles.com/articles.php?pageNum_alltop=5&totalRows_alltop=30
[font="Verdana"]- Deepak[/font]
-
My favorite answer....it depends on your environment and business requirements!
Have you ran the upgrade advisor yet? This is helpful and does a decent job of letting you know if you will have any gotcha's!
We had only a few DTS packages but they were complex and I didn't want to migrate them. Once migrated, SQL 200 DTS packages can't be easily changed.
Always, Always, Always run a full backup before the upgrade
. That way you have the latest SQL 2000 copy of your data in case of some server failure during the upgrade (either in-line or side-by-side). Some questions that I ask myself in making the decision.
1. Will I be able to recover from a failure and how critical is this machine? Doesn't matter which type you do.
2. How much RAM or horsepower is on the box? Do I have enough to run SQL 2000 and SQL 2005 on the same hardware? If yes, then you can do a side-by-side, otherwise you may have to do an in-line.
3. How many connections strings will I have to change if you go with a side-by-side? You will have a new instance that will need to be changed in the connection strings. Obviously, you shouldn't have to change things if you do an in-place. At once place, we had over 100 places to change the connection string on the various local machines, so we opted for the in-line upgrade.
4. How much downtime do I have? In some cases, I had very little downtime so I chose a side-by-side and minimized the downtime to basically a detach and attach. The in-place takes a bit longer.
This should get you started! Good luck!!
-
If possible, I would reccommend you move into a 64 bit OS with 64 bit SQL Server. Unless your OS is currently 64 bit, this would make an in place upgrade impossible.
-
Go side by side, less risk. Big thing is to be sure that a new instance name won't mess up your code/application. This is the safest, especially if you are hardware limited.
If you were changing hardware, it might be worth 64-bit, but you could have issues with your .NET app, so I wouldn't recommend that, especially with such small data sizes.
-
I'd recommend a side-by-side. This way you can test everything as you move it and if the move doesn't work, it's a work of moments to turn the old database connections back on. With a side-by-side, you can do it piecemeal instead of all at once. Which helps when you're upgrading a ton of DTS packages and/or SQL Server jobs.
Also, if you do an in place upgrade and don't pay attention, you risk permanently upgrading your database to SQL 2005 and can't go back if something gets messed up.
Accepting all invitesViewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply