Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
IEC
Starting Member
4 Posts |
 Posted - 2009-08-18 : 12:12:43
|
| Good morning SQL Team,I have an ODBC database (BASIS ODBC Driver 3.01) I am using to connect to BASIS datasource.Here is what I am trying to accomplish:I would like to be able to import the ODBC data into a SQL server database, then replicate the data to a "Live" SQL Server database where clients could connect to the replicated data in near real-time.I would like to have the replication occur approx every 10 mins so the data the clients connect to is as close to "live" as possible.I have attempted to make this work in DTS but the problem I have is that during the replication process, all the data in the "Live" tables gets deleted so there are no records until the replication is complete.Is there a better way I could accomplish this? Perhaps a 3rd party solution, or maybe I have DTS and replication configured improperly. I have very new to DTS and replication so your help will be greatly appreciated.Thank you in advance!Dave JonesDavid A. Jones |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-08-18 : 13:27:13
|
| why are you deleting from the live tables. can you import only changed data?what type of replication are u using? |
 |
|
|
IEC
Starting Member
4 Posts |
Posted - 2009-08-18 : 18:39:23
|
| I don't want to delete from the live tables. I am using Transactional replication, so I am not sure why it deleting all the rows. I only want it to update changed data.David A. Jones |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-08-19 : 10:10:41
|
| then the dts must be doing the deletes (which gets replicated of course).you need to look at the dts, have it import new or changed records only |
|
|
|
IEC
Starting Member
4 Posts |
Posted - 2009-08-19 : 11:20:21
|
| Thanks for the reply Russell...the problem is that I don't know how to set up dts to only send over new recs.Could you point me to a resource where I could learn how?Thanks again!Most appreciated!David A. Jones |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-08-19 : 12:06:25
|
| do the records change (get updated/deleted) at the source, or just new records added?the usual way to do it is to have a datetime field indicating last time the record was updated. say the job runs every 24 hours. just import the records that were modified (or created) in the last 24 hours.or you can import the whole thing into a staging table and compare the two for differences. |
|
|
|
IEC
Starting Member
4 Posts |
Posted - 2009-08-19 : 13:58:34
|
| Unfortunately there are no time stamps on the data.This data needs to be as close to real time as possible do it needs to be updated at regular intervals (every 15 to 20 minutes is the goal)I have written several 3rd party apps that production uses for report writing that many users access throughout the work day. They need fairly current data.What I would like to see happen is this:Step 1: All data in the DTS staging tables gets deletedStep 2: The ODBC data gets imported into the staging tablesStep 3: only AFTER DTS is done importing the data, THEN the data is replicated from the staging tables to our "live" tables using transactional replication so only the recs that have been changed get updated.I hope that makes more sense.David A. Jones |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-08-19 : 14:53:01
|
| if you must delete everything and reimport, can do this in the dts1. Create a staging table with same schema as live table 2. have dts import into staging table in same database as live data3. add any indexes that may exist on live tbl to staging tbl4. drop live table5. exec sp_rename to name the staging table the live table nameif you're using transactional replication and scheduling it to run every 10 minutes, maybe better off letting the dts handle that instead of replication. you'll have to if you're going to implement above solution.that said, this sure isn't pretty. how many records get imported. a dts that does this might be better:1. import into staging table2. compare records between staging and live, looking for records in live not in staging. Delete those records from live. 3. compare again, looking for differences. delete those.4. compare again, looking for records in staging not in live. insert those. |
|
|
|
|
|
|
|
|
DTS / Replication / Live Data