July 10, 2012 at 7:36 am
I need to replicate a 1.5TB database to a testing environment. They want at least a weekly update from prod to test. What is the best way to do this considering the large size? Or is there a way to only send over updates? I'm also concerned about server resources on production. This is a highly used reporting server that is frequently maxed on memory and running high cpu at various points during normal working hours.
Production: SQL Server 2005 SE 64-bit
Test: SQL Server 2008 R2 EE 64-bit
July 10, 2012 at 9:01 am
sqluser_8119 (7/10/2012)
I need to replicate a 1.5TB database to a testing environment. They want at least a weekly update from prod to test. What is the best way to do this considering the large size? Or is there a way to only send over updates? I'm also concerned about server resources on production. This is a highly used reporting server that is frequently maxed on memory and running high cpu at various points during normal working hours.Production: SQL Server 2005 SE 64-bit
Test: SQL Server 2008 R2 EE 64-bit
I can think of a couple of ways of doing it.
Logshipping is out as you will not be able bring the database into standby unless you make them the same version.
You could set it up as a transaction replication subscriber with a scheduled distribution agent. You would need size the distribution database accordingly to store 7 days of data.
OR better still, you could use a continuous transactional replication subscriber and take weekly database snapshots.
There could be other ways I haven't thought of yet 🙂
July 10, 2012 at 9:32 am
I will suggest Snapshot replication.
July 10, 2012 at 9:45 am
jitendra.padhiyar (7/10/2012)
I will suggest Snapshot replication.
To replicate a 1.5TB database to a test environment? The table locking on the production server alone is enough to make me wince. Let alone the extra disk capacity required to store and transfer the entire database on a weekly basis.
With transactional you only transfer the changes on a weekly basis.
July 10, 2012 at 9:45 am
jitendra.padhiyar (7/10/2012)
I will suggest Snapshot replication.
To replicate a 1.5TB database to a test environment? The table locking on the production server alone is enough to make me wince. Let alone the extra capacity required to store and transfer the entire database on a weekly basis.
With transactional you only transfer the changes on a weekly basis.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply