March 2, 2010 at 4:56 pm
I'm searching for a best solution to replicate a 600GB database in a production server to a Reporting server on a weekly basis.
Hoping that someone has experience with large database replication.
A production server gathers data from several databases updating a main database in the process. Then, this database needs to be replicated to a reporting server in another suburb.
backup/restore would be simplest, but sending 600GB over the network is really not feasible.
A weekly update process changes about 3% of the main database so perhaps transactional replication, but i'd like to ONLY replicate once the main database finishes its weekly refresh...
Log shipping may be an option too. Have not had much experience with that, tho.
What is the best approach?
I'd appreciate comments from anyone who had experience with similar scenario.
J
March 2, 2010 at 7:43 pm
If you wanted to go with transactional replication you most certainly could. You could setup a synchronization schedule specific to your refresh, or even kick start the sync post refresh using a job step. Check out http://msdn.microsoft.com/en-us/library/ms151702.aspx
Just bear in mind that you have to take in consideration log space and primary keys.
March 2, 2010 at 8:02 pm
How many tables are we talking about here. 3% is about 18GB, does that sound right?
CEWII
March 2, 2010 at 8:15 pm
There are less then 70 tables where changes occur, but most tables are small, and changes are minute. About 10 tables contain the bulk of data, and you're right, I woudl not expect more than 20GB of changes a week, which should be fine for replication or log shipping.
I'm a little worried about the initial snapshot... 600GB of data may take ages to replicate over the network, unless there is an option to backup/restore initially then setup replication/log shipping without wiping clean the destination database. But I'm not aware of it.
J
March 2, 2010 at 8:28 pm
I'm a little leary of log shipping in this case.. What maintenance do you do on this data? Re-indexes and such?
I think you might be looking at "Sync with Backup"
http://msdn.microsoft.com/en-us/library/ms151224.aspx
CEWII
March 2, 2010 at 8:36 pm
JSteppenwolf (3/2/2010)
There are less then 70 tables where changes occur, but most tables are small, and changes are minute. About 10 tables contain the bulk of data, and you're right, I woudl not expect more than 20GB of changes a week, which should be fine for replication or log shipping.I'm a little worried about the initial snapshot... 600GB of data may take ages to replicate over the network, unless there is an option to backup/restore initially then setup replication/log shipping without wiping clean the destination database. But I'm not aware of it.
J
You can actually initialize replication from a backup, I have a process doing this now, works great. http://msdn.microsoft.com/en-us/library/ms151705.aspx
March 2, 2010 at 10:45 pm
Hi J,
First its depends upon your type of applications like banking,telecom etc
for banking,telecom replication is better one.
Only reporting purpose Just u have to take a snapshot of perticular database.this is just read only purpose.users cant do the dml operations with these snapshot.
Logshipping is oneway.updatations are sync to secondary server.
.it is just shipping transaction logs from one server to another server.You dont bother about size.at the time of configuring logshipping it will take the backup of your database.
you want to do transactions two-way replication is better.here publisher acts as a subscriber and subscriber acts as a publisher.two way data sync will be here. the advantage of this one is required objects of copies wil be maintained.
March 2, 2010 at 11:26 pm
Database mirroring can also be implemented in this case, check for the feasibility
March 2, 2010 at 11:59 pm
Database mirroring where you guarantee delivery requires two phase commit, which even on a local network is expensive, you need to be REALLY careful with that.
CEWII
March 3, 2010 at 12:16 am
JSteppenwolf (3/2/2010)
I'm searching for a best solution to replicate a 600GB database in a production server to a Reporting server on a weekly basis.Hoping that someone has experience with large database replication.
A production server gathers data from several databases updating a main database in the process. Then, this database needs to be replicated to a reporting server in another suburb.
backup/restore would be simplest, but sending 600GB over the network is really not feasible.
A weekly update process changes about 3% of the main database so perhaps transactional replication, but i'd like to ONLY replicate once the main database finishes its weekly refresh...
Log shipping may be an option too. Have not had much experience with that, tho.
What is the best approach?
I'd appreciate comments from anyone who had experience with similar scenario.
J
In your scenario, i think the best would be backup\restore only.
since you need to sync in DBs only @ weekends you can take a diff backup and send it accross on TAPE or HDD whatever suites you,
however, you can test replication and mirroring but the overhead of maintenance and network etc and the dependancy on other things would be very much and this will act like a regular pain in stomach. and you don't even need to sync in DBs Hrly or Daily so why to bother?
i would suggest that if the location is not far away, take a backup either full or Diff and send it accross on TAPE or HDD.
take the pain if it is worth. 😉
Regards,
Sarabpreet Singh 😎
Sarabpreet.com
SQLChamp.com
Twitter: @Sarab_SQLGeek
March 3, 2010 at 12:27 am
I am usually against manual solutions. I have to say that having to card the data accross town is a deal breaker for me.
CEWII
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply