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.

All Forums  SQL Server 2000 Forums  Import/Export (DTS) and Replication (2000)  Is replication the best solution?

Author  Topic  jrervin Starting Member 2 Posts Posted - 2004-05-12 : 16:26:20 I have a database that contains information that we push to institutional customers. Each customer has its own database on the same server with records linked to the records in our database, the "master" database. Our folks enter information into the master database using a Web interface that's built with ASP, VBScript, and ADO. These scripts make the appropriate changes to the data in the master database, then loop through an array that contains the name of each customer database to apply those same changes in each customer database individually. This worked well when we had only a few customers, but is becoming clumsy and time-consuming as we add customers; I can envision a day when our customer list grows longer and this approach will no longer be practical, much less efficient (as if it were now). The customer databases contain some of the same records as the master database along with some of their own proprietary (locally-maintained) records; customers can also assume control of records provided by the master database (convert them to locally-maintained). The design of the tables in the customer databases is similar to the corresponding tables in the master database, with an extra field that contains the master database primary key (mID), along with their own primary key (ID). The ASP scripts use mID to find the corresponding record in the customer databases to which they should apply changes. The ID (primary key) varies between the customers. For example, consider the table that contains a list of subject areas (I work in a library.), which has the following design in the master and customer databases, respectively: dbmaster..tblSubjs -ID (int, primary key, identity for replication) -subjname (nvarchar) -subjabbr (nvarchar) -subjdesc (nvarchar) dbcustomer..tblSubjs -ID (int, primary key, identity for replication) -mID (int, value of ID in dbmaster..tblSubjs.ID) -lm (bit, "Locally-Maintained", indicates whether this record is to be updated from the master database (centrally) or locally) -subjname (nvarchar) -subjabbr (nvarchar) -subjdesc (nvarchar) What's the best way to apply our changes to the corresponding records in our customers' databases? I'm working on a stored procedure for the master database that would push changes to the customers, but I'm stuck on looping through the customer databases. I've plotted designs during boring meetings for a new database that would hold both master data and customer data, but I haven't come up with anything that preserves some other important aspects of the application. So, now I'm looking at replication as a possible answer. I see that I can set up publications and subscriptions and I've learned that I can even publish to a subscriber that's the same instance of SQL Server. However, I've not sorted out how to transform the data in the way that I require -- getting the value from the master's ID field and putting it into the customer's mID field. Can someone out there help? Is replication the best solution in this case? If so, how can I do it? If not, how might I make one of the other scenarios work? Many thanks for your time and consideration.