May 3, 2014 at 10:21 pm
We are planning to move 3-4TB of databases to new environment. Can someone explain some best practices. What should we supposed to take pre and post considerations. Looking for senior guru's.
Below are some of the tasks which I normally do, but still need your clarification:
Pre-tasks:
1. Backup database - Full /Diff.
Post tasks:
1. Index check
2. Statistics check
3. Checkdb.
Since, this is a very large database, running checkdb is really taking longer time. Do we have any fast and quick solutions.
May 4, 2014 at 12:41 am
DBA_Learner (5/3/2014)
We are planning to move 3-4TB of databases to new environment. Can someone explain some best practices. What should we supposed to take pre and post considerations. Looking for senior guru's.Below are some of the tasks which I normally do, but still need your clarification:
Pre-tasks:
1. Backup database - Full /Diff.
Post tasks:
1. Index check
2. Statistics check
3. Checkdb.
Since, this is a very large database, running checkdb is really taking longer time. Do we have any fast and quick solutions.
Do you have a generous downtime window available or are you required to complete this with little downtime?
Mirroring or log shipping the database to the new server and then switching when the 2 are in synch saves on downtime.
You should already have a backup regime in place so just ensure that this is successful would be sufficient and that the backup files are readily available.
You can run checkdb with PHYSICAL_ONLY which may provide a faster initial database check. It should be followed by a full checkdb on the next maintenance window.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 4, 2014 at 11:31 am
Thanks Ferry for your response. This is a data warehouse environment and we do not have any HA.
Based on previous statistics when we did maintenance it took approximately 14 hours to complete the maintenance tasks. I am not sure if business teams can provide the same amount of downtime.
Any alternative of running checkdb or checkdb with physical_only?
May 4, 2014 at 1:52 pm
DBA_Learner (5/4/2014)
DBA_Learner (5/4/2014)
Based on previous statistics when we did maintenance it took approximately 14 hours to complete the maintenance tasks. I am not sure if business teams can provide the same amount of downtime.Any alternative of running checkdb or checkdb with physical_only?
Physical_only is the minimum I would want to run to ensure some sort of consistency
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 5, 2014 at 4:53 am
If you follow Perry's suggestion of setting up mirroring or log shipping for the duration of the migration, you can offload all the long running processes and do them all correctly. You'll get everything moved over and validated prior to shifting the business.
"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
May 5, 2014 at 10:53 am
The description is vague. Is "new environment" a different server? Is it a different edition of SQL? Are the db files on SAN?
If, for example, the dbs are on SAN, it's possible you could simply detach all the dbs (after generating the required attach commands, of course :-)), move the SAN from one server/instance to the other, and re-attach all the dbs.
I wouldn't necessarily do a check before using the dbs after attaching them. If the dbs were clean when detached, they should be clean when attached, so I'd be comfortable with a later check.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
November 2, 2014 at 6:26 pm
If you’re moving to a different DB and are in a DW environment, maybe dump to flat files for central re-staging and cleansing anyway. “Big” data’s more portable there and faster to transform and load from. We use a tool for ETL/sort called CoSort from iri.com[/url]. They also have a data migration spin-off (without the big transforms, but designed for data and DB migrations) called NextForm. It uses the same metadata and Eclipse GUI. Either also works with their Fast Extract tool, and all support SQL Server. Good luck
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply