Poor man's SQL pipeline service for SQL express 2008 R2

Question

Hi everyone,

I have a basic/simple need to create a pipeline transfer process from one SQL express 2008 database to another server (equally SQL 2008 express).

Basically:

1. I have one table on SERVER A which has data coming in, and a default field called 'downloaded' which is again, by default set to 'N'

2. I have the same table schema on SERVER B

3. On a timed basis (say every 10 mins), I need to get all records from SERVER A where the 'downloaded' field is set to 'N', and copy that whole record to SERVER B

4. As each record from SERVER A is read/successfully copied to SERVER B, I set the 'downloaded' flag to 'Y' (with a timestamp field too).

From old memories, I used DTS (now SSIS I guess) to do something similar.. but of course SQL express doesn't have the loveliness!!

Question:

Is it just a case of a SQL datareader to get data from SERVER A and manually either INSERT a SQL statement to SERVER B (or a proc of course)?? any other slick ways?

Thanks for all comments...

Answer 1

Well, how about MySQL with replication? Cheap and slick :-) But I afrait it's too late to change DB...

Answer 2

oh don't use flags! They are not good for indexing.

Add two columns to both source and target tables: dt_created dt_modified.

Add an index on each one.

From your target database, select the source database/table for dt_created > max(target table.dt_created). Those are your new records. Do the same for dt_modified, and those will be your modified records. See! Poor man's replication.