Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Large database migration best practices Expand / Collapse
Author
Message
Posted Saturday, May 3, 2014 10:21 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, May 4, 2014 11:25 AM
Points: 176, Visits: 406
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.
Post #1567309
Posted Sunday, May 4, 2014 12:41 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:50 AM
Points: 6,175, Visits: 13,319
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"
Post #1567316
Posted Sunday, May 4, 2014 11:31 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, May 4, 2014 11:25 AM
Points: 176, Visits: 406
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?
Post #1567343
Posted Sunday, May 4, 2014 1:52 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:50 AM
Points: 6,175, Visits: 13,319
DBA_Learner (5/4/2014)
HA?


[quote]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"
Post #1567366
Posted Monday, May 5, 2014 4:53 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 3:11 PM
Points: 15,517, Visits: 27,898
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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1567466
Posted Monday, May 5, 2014 10:53 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 3:51 PM
Points: 1,967, Visits: 2,905
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)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1567625
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse