Quickest way to get SQL 2000 database up and running on SQL 2008.

Question

Quickest way to get SQL 2000 database up and running on SQL 2008.

Horatio

Mr or Mrs. 500

Points: 542
More actions
August 25, 2010 at 3:01 am

#222995

Hi there,
Our main client is busy moving all of their satellite sites to one main site at a new location. Before I joined the company the biggest (and by far, the most difficult to move) branch was moved to the new location and has been running successfully there for almost a year now.
We are now planning to move the second biggest branch to the main site as well.
The branch we're moving are currently running on SQL 2000 SP4. Since the move with the first branch to SQL 2008 worked fine, we are doing that as well for the 2nd branch. We have been testing the new site and all is finally falling into place, config wise. However, the back up and restore of the databases is worrying me.
When we started the migration, a 3 week old backup was used to set up the main database so we could ensure everything is working (dry-run). The restore of the database (from the SQL 2000 full backup) took over 5 hours to complete for the 28GB database. Now, I don't know if this is due to hardware or because of the upgrade to SQL 2008 and restore at the same time, but when we move we can't wait for 5 hours for the restore to complete.
Would detaching the DB at the old site and re-attaching at the new site be quicker?
We also have SQL users attached to custom created roles to bring over, but I already have a script to export the users without issue (I tested everything and it was working fine).
Any tips/tricks would be greatly appreciated!!!
Also, the SQL server is currently running RTM SQL 2008 (Not R2). Does anyone know of a good resource that I can use (or URL) that would justify the upgrade to SQL 2008 SP 1 that I can send to the client? I just want to cover all my bases before we go live with the new site.
Regards,
The H............................................................................

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

jerry-621596 SSCertifiable Points: 6096 More actions · Answer 1

If you have the capability to take the production database down, detach, copy, and then attach the database on the new server, that would be fastest and safest.

Horatio Mr or Mrs. 500 Points: 542 More actions · Answer 2

Horatio

Mr or Mrs. 500

Points: 542

August 25, 2010 at 11:16 am

#1211804

Thanks,

Will do.

Derrick Smith SSChampion Points: 10833 More actions · Answer 3

Also just a quick note....5 hours for a 28gb database means something is severely wrong.

I have a 700gb database that I routinely restore in < 2 hours, for comparison. Obviously no two infrastructures are identical, but still..you may want to look into your storage.

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 4

It is usually much faster (least downtime) to do it this way:

Make a backup of the source database while the source system is still live.

Restore to the target system with norecovery.

Restore all transaction log backups from the source system to the target with norecovery.

Shutdown the application on the source system, make one last transaction log backup, and restore it to the target with recovery.

Set the new server to live production

With proper planning, your downtime can be only a few minutes.

Lowell SSC Guru Points: 323518 More actions · Answer 5

also if you don't actually turn off the original satellite servers, change all the databases to read only on the old servers the moment you switch over;

it will help identify everyone who is still connecting because the connection string didn't get updated, prehistoric apps people for got about etc. they will whine whent hey cannot update, but not change any data.

there is another thread on here where someone found out some older apps were still updating the old database by mistake after a major migration, and they are trying to sort thru what is different on the old server vs different on the new, as they both ran concurrently for a month;tyhey cannot just backup and restore again, as the data was updated on both systems the alst 30 days.

everyone always forgets something else that is connecting;

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Horatio Mr or Mrs. 500 Points: 542 More actions · Answer 6

Thanks for the tips guys.

I'll look into it.

I also thought the DB restore took a little long. The new server has 4 partitions (1 = OS, 2 = Log Files, 3 = DB Data Files, 4 = Temp Storage), which I assume are a few disks in RAID 5.

The System admin also told me that the new server was not set up properly when I did the DB restores. I'll restore another copy of the old backup today and time how long it takes this time.

I am not a DBA in the true sense of the word, but is there maybe a way to keep the databases in sync somehow? I know how to do this using replication, but since the old and new sites are not in the same location (about 20 miles apart), I'll probably have to resort to something like log shipping. Is this possible between SQL 2000 SP4 and SQL 2008 RTM?

The new site has to be reached via a VPN connection, but that is not a problem as the old server has its own dedicated DSL line, so no issues with proxies or anything like that.

THANKS!!!!!!!!!!!!