question

Elysian avatar image
Elysian asked

best way to replicate data every hour

Please tell me the best way to replicate data(one database only) between sql server 2008 r2 to sql server 2012 every hour.

Replicated Database on sql 2012 should be read and write.

@WRBI

replicationsql server 2012
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

JohnM avatar image JohnM commented ·

Do you want the writes on the secondary to be sent back to the primary version of the database? If so, merge replication is about the only way that I know of to make the secondary writable and have those writes sent back to the primary. If not, then there are multiple options to make this happen. Transactional replication being probably the easiest, log shipping is an option however you'd have to kick all of the users out from the secondary to update the database. You could also use SSIS to just update the data from the source ever hour, but that depends on how big the DB is and how many objects.

1 Like 1 ·
Elysian avatar image Elysian commented ·

@WRBI- please help

0 Likes 0 ·
Elysian avatar image Elysian commented ·

I am thinking to configure one way merge replication that is ..to apply changes from publisher(sql 2008) to subscriber(SQL 2012)

0 Likes 0 ·
Elysian avatar image
Elysian answered

Thanks for ur answer @johnm. I want to make secondary writable but dont want secondary changes to apply on primary. Means primary changes will be applied to secondary but not vice versa

2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

JohnM avatar image JohnM commented ·

So transactional would work for your needs. Beware though that if you alter the replicated data, it can cause issues. IE: deleting data in the secondary and then update the same row in the primary. SSIS would also work if you wanted to update a specific set of tables and have a way to identify the deltas between the tables in some fashion.

0 Likes 0 ·
JohnM avatar image JohnM JohnM commented ·

Actually, now that I think about it for a minute, be cautious with replication. If you have to reinitialize with a snapshot (if replication breaks) it will blow away any changes (including indexes) that you might have made to the replicated tables/articles.

1 Like 1 ·
lokeshlehkara avatar image
lokeshlehkara answered

@Elysian

Because your Secondary server is writable and is not merged to Primary. I would suggest to go for SSIS incremental load. Implement CDC in your primary server and incremental load into secondary. Going with replication, will need additional care and may cause latency\blockages in you scenario. Going with SSIS will give you control on job execution as per your requirement.

Ultimately it depends on data size and how frequently Primary is updated.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.