October 30, 2002 at 8:51 am
Hello all,
A project I'm working on requires that two isolated system databases--a web site database and a CRM application database--share information on a day-to-day basis. E.g. customers that register on the web site will be added to the CRM database, etc. Both databases are using SQL Server 2000.
The systems are different enough so that replication does not seem to be a good fit for direct communication between the systems, so I've assembled a series of DTS packages that move/transform data from one system to the other. The overall apparatus comprises 5 or 6 packages, and maybe 30-40 tasks. This package set can be run daily, or weekly, etc. Essentially, this setup works in the current situation, as real-time updates are not needed.
However, I can't help but get the impression that DTS was not meant to be used in this way. I'm noticing some shortcomings of this setup, but, as a SQL Server newbie, I don't have the perspective to understand whether or not these are problems with my implementation or core feature limitations of DTS. Some examples are:
1) I'm using linked server queries to populate the Source data set in record update situations, e.g. propagating more recent user information from the web to the CRM database. In other words, I'm inner joining records from one database to another and selecting only those whose modification date on one system is greater than the modification date on the other. I originally decided to use distributed queries because performance was much better compared to the alternative of selecting every record in the source connection database and then using a lookup to determine the modification date of the user's record in the destination database. But, I get the impression that distributed queries are anathema. Unfortunately, the alternative seems to be a non-scalable solution. Is there another alternative?
2) As a result of using distributed queries, it seems like I'm locked into using hard coded server/database names in the source queries of my data pump and DDQ tasks. Is this true, or is there some way around this? For tasks which do not need distributed queries, what is the best way of dynamically setting things like source DB.table? Right now, everything is hardcoded, which makes my developer's heart weep, but I'm not sure how to duplicate the functionality of, say, an init file, e.g.
3) I'm using lookups to execute stored procedures that I've written to perform certain chunks of data manipulations. However, Query Designer, which DTS uses as a SQL editor for lookups, gives me an error every time I use the EXEC keyword, so, although it seems to work, I feel like I'm being hinted at that lookups are not the appropriate place to call stored procedures. Are they?
4) The built-in logging feature of DTS look to be insufficient for the kind of diagnostics reporting I'd like to implement. In its place, I've set up a few tables in one of the databases to serve as an activity log. Items are inserted into these log tables as various interesting things, such as errors or key events, happen throughout the execution of the DTS packages. Is this a reasonable way to handle diagnostics/reporting? Is there an established best practice for this sort of thing, in this situation?
All help is greatly appreciated. Thanks very much in advance.
--Andy
November 4, 2002 at 8:00 am
This was removed by the editor as SPAM
November 4, 2002 at 3:21 pm
I think at some point DTS becomes limiting and you might be better off to wrap the whole thing in a exe. Better tools, better error handling, statement completion! Logging to a central table makes perfect sense, I'd stick with it. Easy enough to make your queries 'dynamic' by adding in the db name at run time. Alternative is to have your procs in each db so that they run based on where they are.
As far as the queries themselves, I'd suggest posting a separate topic to see what answers you get. One way to resolve the remove query would be to replicate the data over so you can work with a local copy. Not saying its a great idea, just an option.
Andy
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply