April 7, 2008 at 6:10 am
I have a distributed system where certain data needs to be duplicated in each database. For example a client list. currently this client list is updated in each database by a third party system which goes to the main data source, gathers the client list, FTP's the data to the other servers where it is picked up and installed on the local database.
I want to improve this process and am exploring OPENDATASOURCE as an option. I want to have the main DB have a scheduled operation which looks up the connection info for each remote machine, opens a connection, and passes data to a proc on the remote machine. I have little experience with this and am curious about its limitations. The largest table that needs to be transmitted is 10,000 rows. Some of the remote servers are on shaky networks but I think this could be mitigated with some redundancy and error checking.
Thoughts appreciated.
ST
April 7, 2008 at 6:25 am
ST
I think using a DTS package would be simpler and possibly more robust.
John
April 7, 2008 at 6:27 am
Can DTS be scheduled? Is BOL a good place to look further?
Thanks
ST
April 7, 2008 at 6:30 am
ST
Yes and yes. BOL is always the place to start when you're not sure of something. And you can schedule DTS packages using the dtsrun command line utility - not sure whether you can't do it through the GUI as well.
John
April 7, 2008 at 7:53 am
You can definitely schedule it through the GUI.
-- You can't be late until you show up.
April 7, 2008 at 10:24 am
I've done a little research and it looks like with DTS I would be reinventing the same process I have now where the DTS package would be created, delivered, and the receiving server processes it. That sounds like a process better suited to a full DB backup. I have 12 tables to move. You mention that this would be more robust. Can you please tell me the pitfalls of using my suggested methodology?
Thanks
ST
April 8, 2008 at 1:37 am
ST
The main problem I have found in the past is that building the connection string is fiddly and unforgiving of the slightest error. Also, you may wish to consider the following from Books Online:
Like the OPENROWSET function, OPENDATASOURCE should only reference OLE DB data sources accessed infrequently. Define a linked server for any data sources accessed more than a few times. Neither OPENDATASOURCE, nor OPENROWSET provide all the functionality of linked server definitions, such as security management and the ability to query catalog information. All connection information, including passwords, must be provided each time OPENDATASOURCE is called.
If you are going to transfer this data regularly, your choice is probably between linked servers and DTS. Please make sure you compare the security implications of each before you make a decision.
John
April 8, 2008 at 5:40 am
Thank you, John. I have some research to do.
April 8, 2008 at 2:15 pm
Linked server, or just plain publish/subscribe replication
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply