September 21, 2010 at 10:06 am
Hi all,
i am planning to move a database from sql 2000 to SQL 2008. My question is can i just take the full backup of the 2000 database and do a restore on 2008. Will it work or do i need to perform some other steps, apart from running the upgrade wizard of sql 2008.
September 21, 2010 at 10:45 am
the short answer is 'maybe'.
the other question is; will my app be able to access the 2008 database?
i have found upgrading the DB is the easy part. getting the app to connect and work the same way is where the trouble usually lies.
September 21, 2010 at 11:17 am
could you please be a little elaborate on the migration part because that is the main concern right now.
September 22, 2010 at 4:41 pm
I would not do that directly in production, make sure you can test this out first.
Our main application still sits in SQL 2000 because it does not work in 2008, regardless of what we do or try.
September 22, 2010 at 7:24 pm
bbsr
Might I make a suggestion.
1. Purchase the developer edition of SQL SERVER 2005 - Cost less than $50 USD.
2. Restore a back up of your 2000 DBs to SQL 2005
3. Increase the comparability level to 90
4. Test your applications, stored procedures, etc., etc.
5. At the completion of all necessary changes, then purchase the developer edition of SQL SERVER 2008 - Cost about $50 USD.
5. Increase the capability level to 100 (SQL Server 2008)
6. Test again
September 23, 2010 at 11:20 am
bbsr,
Yes, you can restore a backup made on a SQL 2000 server to a SQL 2008 server. SQL will automatically convert the database to the 2008 format (rendering it impossible to then take a 2008 backup and restore it to a 2000 server). After you do this, you should run DBCC UPDATEUSAGE on the transferred database.
Microsoft also has a nice upgrade tool that can check our 2000 databases and see if there might be any problems when moving to 2008 - i.e., do you 2000 databases reference any system tables that have gone away, etc. It's call the SQL 2008 Upgrade Advisor I believe.
But also be mindful of what other have said in this thread. The big issue won't be moving the database. The big issue will be making sure the client apps can still connect to it.
I've recently gone through the 2000 to 2008 migration process and will be doing it again in a couple weeks. I wrote a couple blog posts you might find helpful. This one[/url] talks about transferring you logins from 2000 to 2008 and this one[/url] talks about the whole process. That last link also has a link to the 2008 Upgrade Advisor.
September 23, 2010 at 11:42 am
Thank you so much SSC Journeyman for the reply. I didnt try it out till now but i believe it can be done after reading your blogs.
thanks again
September 25, 2010 at 12:49 pm
bitbucket-25253 (9/22/2010)
bbsrMight I make a suggestion.
1. Purchase the developer edition of SQL SERVER 2005 - Cost less than $50 USD.
2. Restore a back up of your 2000 DBs to SQL 2005
3. Increase the comparability level to 90
4. Test your applications, stored procedures, etc., etc.
5. At the completion of all necessary changes, then purchase the developer edition of SQL SERVER 2008 - Cost about $50 USD.
5. Increase the capability level to 100 (SQL Server 2008)
6. Test again
Why do you suggest to go through the upgrade and test to SQL Server 2005 and then to SQL Server 2008? I would think that is really just extra time spent that isn't necessary, since it was specifically stated that SQL Server 2008 is the goal.
Is there something I am missing that doing the double upgrade would make testing and modifications easier/faster?
September 26, 2010 at 11:31 pm
I think what Bitbucket is trying to tell you, is that you should wade into the water instead of diving in head first.
Once you have ironed out the obvious problems, which will be evident from using the upgrade advisor, there still might lurk a number of hidden aspects under the surface, more precisely in your application.
Once the database is upgraded to 100 compatibility, your database objects return data when called from the application; you would want to also verify the performance and data integrity.
Back in the SQL 2000 days there were some specific performance work-arounds that people might have coded into the TSQL, which might not execute in the same predictability after upgrading.
Look for query hints in the code, queries through linked servers, and some queries relying on the order in which a record set is presented by the query engine. Explicit order clauses might not have been provided (in these cases) because the query engine returned the data in the required order. This could have been a forgiven situation in SQL 2000, but in SQL 2008 could change your result set, because the implicit order was relied on before.
If you are thorough in your test cycles, which requires a full end-to-end integration test cycle (with load) in a mission critical environment, you should catch most of the problems. However, if your system is not a very sensitive one, and you could get away with corrections applied to the production environment, you could take a dive.
The safest option though is to wade in, test and teat until you're sure.
September 27, 2010 at 3:39 pm
Drikus Roux (9/26/2010)
If you are thorough in your test cycles, which requires a full end-to-end integration test cycle (with load) in a mission critical environment, you should catch most of the problems. However, if your system is not a very sensitive one, and you could get away with corrections applied to the production environment, you could take a dive.The safest option though is to wade in, test and teat until you're sure.
I still don't see why he is saying to go through all that testing twice. once to 2005 and then again to 2008. I understand the need for all that testing, I just don't understand the stop to 2005 in the middle.
September 27, 2010 at 4:26 pm
UMG Developer (9/27/2010)
Drikus Roux (9/26/2010)
If you are thorough in your test cycles, which requires a full end-to-end integration test cycle (with load) in a mission critical environment, you should catch most of the problems. However, if your system is not a very sensitive one, and you could get away with corrections applied to the production environment, you could take a dive.The safest option though is to wade in, test and teat until you're sure.
I still don't see why he is saying to go through all that testing twice. once to 2005 and then again to 2008. I understand the need for all that testing, I just don't understand the stop to 2005 in the middle.
On this one, maybe he meant 2008 and it was a typo.
Personally, I have performed the upgrade to 2008 from 2000 on numerous databases. In some cases we had to alter procs and in some cases we could not use the application against SQL 2000 due to checks placed in the app to verify that it was running on SQL 2000 and only 2000. Our upgrade path was to backup and restore to a new server.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 28, 2010 at 1:59 am
I have posted some steps here
http://www.sqlservercentral.com/Forums/Topic993548-338-1.aspx#bm993618
could be useful for you
----------
Ashish
September 28, 2010 at 4:32 am
I am not insisting on the step in the middle (SQL 2005), only on testing thoroughly. It was probably mentioned as an avenue of gradual upgrade towards the goal of 2008.
You could jump straight from 2000 - 2008, just make sure you test outside of the upgrade advisor boundaries.
One that we picked up was on a JOIN (without a WHERE clause) where the result set was ordered by a field, differently in SQL 2008 compared to SQl 2000. This caused some description of a product to be picked that didn't belong with the product. The root cause was a difference in how SQL treated the information order (who knows exactly why) and the resolution was to code a WHERE in there to force the order, which should have been in there in the first place. This stuff you can't pick up unless you do some kind of system integration testing or you develop Code Analysis rules in Visual Studio for TSQL (rules specifically checking for bad coding patterns in relation to your upgrade path) and run it against your database code. That way you should proactively weed out obvious candidates
If you are running a complex SQL system and your testing is on the weak side, I assure you of some post upgrade issues that'll need fixing in the next weeks.
🙂
September 29, 2010 at 10:12 am
I regularly restore 100-1000GB SQL Server 2000 database backups to SQL Server 2008 R2 servers without issues; whether the stored procedures all still work, I don't know, however the data is just fine.
Don't forget a DBCC UPDATEUSAGE(database) afterwards, and upgrading compatibility level to 100 if you want to.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply