Re: Database Synchronization between SQL Server and Linked Server

SQLServerCentral is supported by Red Gate Software Ltd.

Home Articles Editorials Forums Scripts Blogs QotD Books SQL Jobs Training Authors About us Contact us Newsletters Advertise Write for us

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » Re: Database Synchronization between SQL...

Re: Database Synchronization between SQL Server and Linked Server

Rate Topic

Display Mode

Topic Options

Author

Message

jlp3630

Posted Saturday, September 20, 2008 7:18 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, June 30, 2009 9:03 AM
Points: 142, Visits: 430

What's the best approach to synchronize a linked server to a local SQL server database? DBAmp, which is OLE DB provider used for Salesforce, is utilized for setting up the linked server object. My initial thoughts were writing CREATE TABLE, UPDATE, INSERT, & DELETE statements. However, I'm not sure if that's the quickest approach in terms of man hours or implementation.

Post #573077

SQLBOT

Posted Sunday, September 21, 2008 6:09 PM

SSC Veteran

Group: General Forum Members
Last Login: Thursday, July 02, 2009 2:43 PM
Points: 243, Visits: 361

I think what you're essentially talking about is an ETL job.
You might consider a couple of options.

1. Use your OLE DB provider as a datasource in SSIS; use agent to schedule the SSIS job
2. if it's really really simple, you can write a stored procedure to keep the tables up-to-date; use agent to schedule the Stored proc.

Either way you're stuck with /how/ to programatically apply just the changed data (delta), and there are a number of good ETL/Data Warehousing tricks for capturing delta, if you're really lucky, there will be 'lastupdated' columns in your datasource you can leverage... otherwise it gets trickier, but is still very possible. Google will be very helpful and this article is a good introduction to the topic http://en.wikipedia.org/wiki/Change_data_capture

I think there is also a SSC.com forum dedicated to ETL or Data Warehousing, that might be another good resource for you to leverage.

Good luck!

~Craig

Craig Outcalt
MCITP, MCDBA

Post #573201

Jacob Luebbers

Posted Monday, September 22, 2008 12:59 AM

SSC Veteran

Group: General Forum Members
Last Login: Thursday, July 02, 2009 11:06 PM
Points: 270, Visits: 833

First hit on Google for "DBAmp oledb" brings up this link: http://www.salesforce.com/web-common/assets/doccache/MultiForceDir/01530000000Ev3IAAS.pdf

Looks like exactly what you want AFAIK - check the section on SP_Refresh and SP_RefreshAll.

Regards,

Jacob

Post #573273

« Prev Topic | Next Topic »

Permissions