My company's business is divided into two geographic locations. In SQL Server 7.0, I want to run a stored procedure at my site that updates sales data that resides at the other site and immediately generates the remote location's sales report in a graph. How do I share data and information between the two sites?
With SQL Server 7.0, you have two options for sharing data between two sites: replication and linked servers. Your decision will be based on cost versus performance.
SQL Server 7.0 supports several forms of replication, which you can use to generate a local copy of the remote data in near realtime. You control the latency by defining the replication schedule when you subscribe and by allocating sufficient bandwidth to propagate changes in time. After you retrieve a local copy of the data, you can provide your own querying interface to users. The advantage to this approach is that you retain some data locally, even if the connection to the remote servers is down.
Alternatively, you can use linked servers. SQL Server is flexible in letting you query remote data in realtime, which has the advantage of immediacy. However, using linked servers also carries disadvantages, which include the following:
- The connection must always be up.
- You can't request a large amount of data. (As a rule of thumb, you need to limit the amount of data returned from the remote database to one-sixteenth of the calling server's memory.)
- You need to avoid clogging the communications line with multiple parallel or long-running requests, which can cause a long wait for the user.
- You can't use query hints on the remote query (unless you're using OpenQuery). Therefore, if you wanted to read data from a remote table that is frequently updated, for example, you could use the following query:
SELECT * FROM OPENQUERY(RemoteSvr, 'SELECT name, id FROM joe.titles (NOLOCK)')
Passing in the (NOLOCK) hint ensures that your query doesn't block the updates.
In the scenario you mention, we would use replication—perhaps from tables containing pre-aggregated data—to reduce the amount of sales data that you need to read from the remote server. You can use a trigger on your remote tables to capture inserts, updates, and deletes and add, adjust, or subtract from your aggregated table to maintain the aggregated data.
