April 30, 2007 at 12:05 am
We are creating a company-wide table of ZipCodes, States, GPS info, etc. This table can be used by our development and production servers (many of them.) We could place the table on a given server and use linked servers to grant access to that table to the other servers. But is there a better way to handle this globally-useful table?
Bill
P.S. Clearly, we don't want to have multiple copies of this table scattered around on various servers. That introduces synchronization issues.
April 30, 2007 at 8:24 am
You might want to consider creating a web service to query and return data.
Gregory A. Larsen, MVP
May 1, 2007 at 6:27 am
We have a similar situation in which we have implemented a master data solution using transactional replication. Here was my thought process:
Linked servers were out because of two things - they were getting used for a few things and since we have servers in several different locations across the country, they were causing performance problems. Also, every time we have a frame failure, a location would lose access to master data and applications would stop working.
A web service was giving us one of the same problems - the frame relays must be up. In addition, using a web service would force us to combine data at the application level rather than the database level.
What we ended up setting up is a basic hub and spoke MDM solution. A single server with our master data (accounts, address information, etc) and an application that would be used to directly enter and update this information. We then use a simple transactional replication of the appropriate information that each of our individual transactional systems needs. We end up with a UI for MDM and a UI for each individual system, but we made them work well together and the users hardly notice. Since we used replication, the latency is typically only a few seconds, but if we lose our connection, everything keeps working and replication usually fixes itself when the connection is re-established.
So, the things we thought about were performance, stability of the connection, and ease of development and maintenance. Every situation is different.
Feel free to post plans and I am sure we can help you find any holes in them.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply