Where is the bottleneck / what is gotchas when selecting records from a remote (linked) SQL server?

Question

I am in a satellite office that needs to pull some data from our main office for display on our intranet. We use MS SQL Server in both places, and we plan to create a linked server in our satellite office, pointing to the main office. The connection between them is the VPN tunnel that I believe (is this right? What do I know, I'm a programmer!)

I am interested in generating a lot of traffic through a potentially slow connection. We will access the SQL view on the main office server. This is not a lot of data (~ 500 records) after starting the query of choice, but the view is huge (~ 30,000 records) without a query.

I assume that executing a request on a linked server will only return posting results (and not that the entire request is requested locally). In this case, the main bottleneck is most likely the connection itself, which involves indexing the view, etc. Are there any other bugs or potential bottlenecks (possibly based on how I build queries) that I should be aware of?

Answer 1

From what you explained, your connection is likely to be a bottleneck.

In addition, you can also consider caching data at a satellite location.
The decision will depend on the following:
- how many rows and how often the data is updated in the main database
- how often do you need to upload the same dataset to a satellite location

Two edge examples:

• Data is static or relatively static - they are inserted only in the main database. At a satellite location, users often request the same data over and over. In this case, it would be advisable to cache data locally at the satellite location.

• Data is unstable, many updates and / or deleted. Users on a satellite map rarely request data, and when they do, it is always different when the condition is. In this case, it makes no sense to cache. If the connection is slow and changes often occur, you cannot synchronize with the main database.

Another advantage of caching is that you can implement data compression, which will reduce the bad effect of a slow connection.

If you chose to cache in a local location, there are many options, but I believe that this will be a different topic.

[change]

About compression: you can use a compressed transaction log. In SQL 2008, compression is supported only in the Enterprise edition. In SQL 2008 R2, the launch of the standard version is available. http://msdn.microsoft.com/en-us/library/bb964719.aspx .

You can implement custom compression before sending transaction logs using any compression library you like.