July 9, 2014 at 12:41 am
is it possible to migrate 7TB database within 30 minutes ?
if yes, what is the best way ?
Regards
Jernas
July 9, 2014 at 12:51 am
solomon.jernas (7/9/2014)
is it possible to migrate 7TB database within 30 minutes ?if yes, what is the best way ?
Regards
Jernas
Without more information, it is impossible to say. Any kind of answer would be a pure speculation. On the other hand, 7TB in 30min is only about 4Gb a second, definitely doable on a moderate hardware.
😎
July 9, 2014 at 1:02 am
solomon.jernas (7/9/2014)
is it possible to migrate 7TB database within 30 minutes ?if yes, what is the best way ?
Regards
Jernas
It is hard to say without any details. How you are migrating database? Using some routines / SSIS packages? I think you need super hardware so it can handle IO very very fast.
Thanks
July 9, 2014 at 1:03 am
we have 7TB database is running sql 2000 ,so need to migrate 2005 , as they requested 30 min time enough for migration this db...
you said , impossible ..
July 9, 2014 at 1:13 am
This isn't too much of a challenge and definitely NOT a question of transferring 7TB in 30 min. Here is a quick suggestion:
a. Do a full backup of the 2K database.
b. Restore the backup on the 2K5
c. Attach the db to the 2K5
d. Do a differential backup on the 2K
e. Apply the differential to the 2K5
Only the two last steps have to happen within the 30 min window!
😎
July 9, 2014 at 1:14 am
solomon.jernas (7/9/2014)
we have 7TB database is running sql 2000 ,so need to migrate 2005 , as they requested 30 min time enough for migration this db...you said , impossible ..
He said 'impossible to say'. What he meant was he can't make a judgement on the limited information he's got. He also said that it works out at about 4GB a second and given the right hardware it's completely feasible. All that means is we just don't know enough about your setup to make a call.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
July 9, 2014 at 3:33 am
Are you using a SAN? The Storage Admins might have some options to help you through disk mirroring and/or snapshots. It worth talking to them if you have any storage administrators in your company.
July 9, 2014 at 4:40 am
same system how much time will take of deatch and attach option for 7 TB database ?
July 9, 2014 at 5:17 am
I wouldn't opt for the detach/attach option. I would go for a similar option as Eirikur Eiriksson already mentioned in one of the previous posts:
1. Copy all logins with access to the database from the original server to the target server
2. Backup the database on the original server
3. Restore the backup on the target server (leave restored database in NORECOVERY)
/*** next will start the downtime of the database***/
4. Set the database to READ_ONLY and stop all access/modifications to the database
5. Create a differential backup on the original server
6. Restore the differential backup on the target server (use WITH RECOVERY)
7. Set the restored database on the target server to READ/WRITE
8. Check if all logins can access the database on the target server
/*** end of downtime of the database ***/
9. Modify all connections (from applications, etc.) to point to the database on the target server
10. Optional: set the database on the original server OFFLINE
If you execute step 2 and 3 several days before the actual migration, you could backup/restore an additional differential backup on the day of the migration. Next, instead of using a differential backup in steps 5 and 6 you can use a LOG backup. This way you can migrate any database in only a few minutes, especially when you have all actions scripted out upfront!!
July 9, 2014 at 5:19 am
And in addition to my previous post:
Usinig the backup/restore method you always keep the original database available in case you need to fallback the migration.
July 9, 2014 at 5:29 am
Thank you very much for you good valuable explanation.
Thank to all..
July 9, 2014 at 6:52 am
I'll come right out and say it. No, it's impossible to just migrate your database over using any straight method.
I agree with the steps Hanshi laid out for a multi-step migration.
What you could do is go one step further than what Hanshi suggested and setup log shipping so that you can have, at most, maybe 2-3 minutes of downtime. You ship the 2000 server to the 2005 server, then you shut off access to the 2000 server, get the last log backup, shift that over, take the 2005 server out of log shipping and bring it online. The downtime involved is extremely minimal. If you script it very carefully (and test it to be sure), you can actually have no more than 10-15 seconds.
"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
July 9, 2014 at 8:15 am
Grant Fritchey (7/9/2014)
I'll come right out and say it. No, it's impossible to just migrate your database over using any straight method.I agree with the steps Hanshi laid out for a multi-step migration.
What you could do is go one step further than what Hanshi suggested and setup log shipping so that you can have, at most, maybe 2-3 minutes of downtime. You ship the 2000 server to the 2005 server, then you shut off access to the 2000 server, get the last log backup, shift that over, take the 2005 server out of log shipping and bring it online. The downtime involved is extremely minimal. If you script it very carefully (and test it to be sure), you can actually have no more than 10-15 seconds.
Kind of missing part of the equation here is how busy is the 2K system, what kind of role does it have (transactional, reporting etc.), how beefy is the 2K5 system, has the 2K system gone through the Upgrade Advisor (detect discontinued syntax such as dump transaction etc.), is there a normal operational maintenance window (backups etc.) on the 2K and so on and so forth. We are still guessing and speculating!
When attaching a 2K database to a 2K5 server, there is a lot of I/O and CPU load involved. My rough rule of the thumb is 1 core + 8Gb ram = 50 Gb of db file size p. minute. A very rough guesstimate though 😉 That would be about 150 core minutes, my guess is that it entirely rules out such an operation within the downtime window, which is more or less in line with what Grant said.
Without the whole picture it is still guessing, one can ask if it is possible to travel 35km in a minute. It is but it takes military grade (very expensive) hardware and yet it would need a flying start!
😎
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply