Sql Server 2000 DTS across network servers

[T2T2]

I have never used DTS, but I believe it is the tool for my current project.

We have regional databases supporting sales reps across the country. For the purposes of management reporting we want to store the data in a central database, containing all regions.

The aim is to have incremental updates sent from each regional database to the central database.

Reading through the online books under sql server 2000 there is a mass of information.
I have tried to play with the DTS new package panel to create connections, one for the source and one for the destination database.
I am struggling to achieve this.

Has anyone out there done anything similar? I'd appreciate some guidance.
Our transaction records will involve inserts and updates , no deletions.

I am wondering how incremental transactions can be processed.
I am assuming that sql can be written to filter rows based on date & time criteria.

I am also assuming that scripts have to be written on the destination side to instruct the process to update or insert the transactions transfered across from the source.

At the moment it seems vague and somewhat complicated for me, an example of sql or script would help me understand the processing involved.

[Krzemo]

1) There is no problem to connect DTS to many sources

2) Yes U can synchronize DBs by using triggers, buffer tables and DTS but it is very hard way to do it and it is easy to make it wrong.

3) U can do it also using message queue technologies (MSMQ for example).

4) The better way is to use buit-in technology like replication.


Best regards,
Krzemo.

[Shuja Ali]

The easiest way to create a DTS package is to open Enterprise Manager, select any table in the database, right click on it, select All Tasks and then Import Data. This will bring up a Wizard which helps you in creating the DTS package. At the end of the Wizard you can select the save package option and there you go.

The DTS package is saved and ou can use it now.

[T2T2]

Thanks for the advice.

I have just been asked to consider Publications Subscriptions (PS).
So now i have to compare DTS with PS. ?
Great more documentation to read through.

I believe with PS we will set up a copy of each regional database at our central office, and with PS we will be dropping these copies and recreating them on a periodic basis.

It will be like a Database copy / back up process over night.

Then we will use reporting services to read off the copied databases.

That's the theory at this stage.

[Krzemo]

I believe with PS we will set up a copy of each regional database at our central office, and with PS we will be dropping these copies and recreating them on a periodic basis.

It will be like a Database copy / back up process over night.

No, there is no dropping/recreating during replcation. And U can synchronize whole databases or just few tables. Replication process is based on transaction log. Publisher sends his log over network to subscribers. Subrscribers reads log and roll forward subscribed tables/databases .So transactions are recorded an than applyied on subscribers. Database is available during all that process.That way it is not so much intensive -and IMHO it is the best.

The aim is to have incremental updates sent from each regional database to the central database.

That is exactly P/S schema (incremental updates = transaction log).

Hope that it helps.