Best approach for integrating data between two SQL databases?

Question

Hello,

I am writing an events calendar, and the data for the calendar I will need to pull from another database and make some changes to before committing to mine.

What are some basic approaches for this that you would suggest?

This data is not super sensitive or in need of enterprise solution.

I am thinking of writing a service which runs on my server that will check the external database every hour for changes, if there are any - load them into my database tables. Is there a simpler approach?

Thanks

Answer 1

There are a bunch of options. 2 pretty easy solutions to implement are:

1. You can use SSIS (Sql Server Integration Services) to transport data to and from data sources
2. You can also use SQL Server Replication and set up a publisher / subscriber scenario.

Answer 2

You could just write a stored procedure that checks for new data in the external database, makes your necessary changes then brings it over.

You could then set up a SQL Server Agent job that executes that stored procedure and set it to run on an hourly basis.

Answer 3

I agree with cody, both 1 and 2 of his answer have a use. Although maybe a little complicated for your purpose

Using stored procedures - recommended approach - might have to link the servers first.

It seems that this might be possible for you to use, either run the SP in code, manually or set up a scheduled job in sql server agent

http://msdn.microsoft.com/en-us/library/aa213778(SQL.80).aspx

Answer 4

Add a Linked Server to the other server, and you can query from it like:

select * from [LinkedServer].dbname.dbo.Table

This way, you'll always be using current information, and there's no need to write a script or stored procedure that determines "what's new" or "which rows have changed".

Answer 5

Your options depend somewhat on the flavor of SQL Server you are running. But the exact requirements of your integration matter more. The simplest and least efficient method for one-way integration is to delete every calendar event from the target before loading new ones from the source. This may be good enough if there are not many events to synchronize, like if you don't have to load past events at all. But if you need to keep track of the synchronization state, things get more complicated and tools start to matter. There are two stages in this kind of integration:

1. Extracting changed data from the source and
2. Transforming and loading data to the destination

Extracting changes

Every version of SQL2008 has a new change tracking feature that is especially targeted for synchronization scenarios. Change tracking is different from change data capture that is supported only by SQL 2008 Enterprise Edition. If the source database is running SQL 2008, I would definitely look change tracking first. The main benefit is that you don't need to set up metadata to handle change data detection, like storing the timestamp of the last load and comparing it to event modification timestamps etc. You should'n need to make any DDL changes to your user tables to track changes, except turning the change tracking on:

ALTER DATABASE AdventureWorks2000 SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON);
GO
USE AdventureWorks2000;
GO
ALTER TABLE Person.Person ENABLE CHANGE_TRACKING
  WITH (TRACK_COLUMNS_UPDATED = ON);
GO

If you can't use change tracking, I would suggest using timestamps or version numbers instead of populating a separate change table with triggers. Triggers might cut it here, but I still recommend to avoid them :) You probably have the necessary timestamps in the database schema already.

Setting up replication is an interesting method for doing change data capture. In fact, technically it is the precursor for CDC found in SQL2008 Enterprise Edition. I haven't used replication for CDC myself, but for example in this book the writers have good experiences using it.

Transforming and loading

Use SQL Agent to schedule an SSIS package. If you can do a full load each time instead of loading changes, this is all you need.

Another option is to schedule a stored procedure but handling things like logging errors won't be as simple. My experience is that developing SSIS packages is lot faster than using T-SQL, especially if linked servers would be involved.

SQL Server Express Issues

SQL Server Express (2005/2008) does not have SQL Agent and can only act as a replication subscriber. I have usually ended up programming a Windows service for SQL Express integration jobs, but having an external scheduler to run stored procedures might just work well enough. Writing and scheduling a stored procedure would probably be lot faster than developing a service.

SQL Server Express 2008 does have SSIS runtime, but I don't exactly know how limited it is since not all features are supported by it. Import/Export wizard does work, however.