December 21, 2010 at 5:27 am
Hi All,
I have to upgrade only the Database from SQL 2000 to SQL 2008R2.
Please give suggestions or Guidelines.
I will Deattach from SQL 2000 And Attch same in SQL 2008R2
or
Take backup from SQL 2000 and Restore in SQL 2008R2
Here i need to provide the Logins/Users/ Access permissions of DB in SQL 2000 as same in SQL 2008 R2.
SQL 2000 is 32-Bit
SQL 2008R2 is 64-Bit
Any Info to proceed...
Thanks,
Sasidhar Pulivarthi
December 22, 2010 at 5:00 am
hi
Here are some of the ways of move the database
In-place Upgrade
Side by Side Upgrade
Detach/Attach
Copy Database Wizard
Manual Schema Rebuild and Data Export/Import
Choosing upgrade method
The two upgrade methods discussed above have several different advantages and disadvantages with it as given below, we have to evaluate it in terms of our requirements and current circumstances before moving for upgrade.
In-place Upgrade Advantages:
It's easier and faster, especially in small systems.
It's mostly an automated process.
The instance will be offline for a minimum amount of time.
The resulting instance after upgrade will have the same name as the original, as the new setup will replace the older version.
No additional hardware is required in many cases.
o Disadvantages:
It's very complex to rollback.
Not applicable in scenarios where we want to upgrade a part of system like upgrading just one single databases.
We cannot run an upgrade comparison after doing the upgrade.
Side by Side Upgrade
o Advantages:
More control over the upgrade, as we can upgrade the components, which we want to.
We can keep our application running even when we are installing SQL 2008 as the old instance or server will be available.
Easy to do a rollback as the original database server is untouched.
In-place Upgrade Disadvantages:
We might need additional hardware resources in terms of disk space, CPU and RAM.
Manual intervention is required to migrate databases, Jobs, logins etc.
There will be change in configuration settings, which are used by the application to connect to the database.
More time is required while moving VLDB to the new version of database.
also check
December 22, 2010 at 5:49 am
backup / restore is nice and easy.
syncing logins will be an issue. you will not be able to script out logins from 2000 and script them in to a SQL 2008 R2 instance. something about hash changes between versions.
it is possible to script out logins from 2000 to 2005. then script them out again from the 2005 to 2008 R2.
December 22, 2010 at 7:11 am
This is one of those places where the Microsoft documentation, best practices and advice is the best place to go for information. They really do lay out the choices correctly. Make sure you're running the Upgrade Advisor on any 2000 databases and that you deal with the results prior to migrating your database.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 26, 2010 at 6:29 pm
Pulivarthi Sasidhar (12/21/2010)
SQL 2000 is 32-BitSQL 2008R2 is 64-Bit
Any Info to proceed...
Thanks,
Sasidhar Pulivarthi
Sounds like you have 2 different machines ?? You're not upgrading an existing machine from 2000 to 2008 ?
Then you can detach - attach
You also need to import the logins as mentioned.
December 27, 2010 at 3:35 am
Hi All,
Thanks for Info...
Yes, two different Machines..
For logins i have below link
http://support.microsoft.com/kb/246133
I am almost done with Logins & Restore of DB's.
need to Generate script for all Jobs and Replication existed in SQL 2000 by SQL / T-SQL stmts only.
The same generated scripts are going to Run on SQL 2K8 R2.
Any further Info plz...
December 27, 2010 at 3:52 am
i dont think there would be any major difference in moving the database to 2008 or 2008R2, so same strategy is appicable.
Post upgrade, need to make sure that stats are updated and compatibility level changes to reflect the correct product version. Also revisit your scheduled jobs.
----------
Ashish
December 29, 2010 at 7:21 pm
Sorry to hijack, but I also am now in an environment going 2000 > 2008R2 and wonder if there is a way to script out DTS packages?
Not sure migration will be worth it as I need to improve the processes and just need the workflow documented so I can build new in SSIS.
I am in second week on the job - EVERYTHING is someone elses work and I don't understand the business at all. Would rather the uphill go ONE WAY!
December 29, 2010 at 8:43 pm
for DTS, i will suggest to save them as file and then import them as file in ssms.
----------
Ashish
December 29, 2010 at 9:02 pm
For DTS, save as structured storage files. Use the runtime to execute them: http://msdn.microsoft.com/en-us/library/bb500440.aspx
Note that SQL 11 will not have support for DTS
December 30, 2010 at 9:41 am
Thanks, folks!
All will be SSIS within however many months it takes me to re-write...
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply