4

I'm 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 locations and we're planning to create a linked server in our satellite office pointing to the main office. The connection between the two is a VPN tunnel I believe (does that sound right? What do I know, I'm a programmer!)

I'm concerned about generating a lot of traffic across a potentially slow connection. We will be getting access to a SQL view on the main office's server. It's not a lot of data (~500 records) once the select query has run, but the view is huge (~30000 records) without a query.

I assume running a query on a linked server will bring back only the results over the wire (and not the entire view to be queried locally). In that case the major bottleneck is most likely the connection itself assuming the view is indexed, etc. Are there any other gotchas or potential bottlenecks (maybe based on the way I structure queries) that I should be aware of?

Alex Aza
  • 76,499
  • 26
  • 155
  • 134
Ken Pespisa
  • 21,989
  • 3
  • 55
  • 63
  • Sorry, I don't know offhand what the bottlenecks will be. If the database is used heavily and you experience problems, you may want to try having a local copy and using database replication, instead of a linked server. – RMorrisey May 07 '11 at 00:52
  • I definitely am keeping that in my mind as a plan B. The ability to have this data be real-time would be nice though. – Ken Pespisa May 07 '11 at 01:18

1 Answers1

2

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

Also, you might also consider caching data at the satellite location.
The decision will depend on the following:
- how many rows and how often data are updated in the main database
- how often you need to load the same data set at satellite location

Two edge examples:

  1. Data is static or relatively static - inserts only in main DB. In satellite location users often query the same data again and again. In this case it would make sense to cache the data locally at satellite location.

  2. Data is volatile, a lot of updates or/and deletes. Users in satellite location rarely query data and when they do, it is always different where condition. In this case it doesn't make sense to cache. If connection is slow and there are often changes you might end up never being at sync with the main DB.

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

If you chose to cache at local location there are a lot of options, but this I believe would be another topic.

[Edit]

About compression: You can use compressed transaction log shipping. In SQL 2008 compression is supported in Enterprise edition only. In SQL 2008 R2 it is available starting Standard version. http://msdn.microsoft.com/en-us/library/bb964719.aspx .

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

Alex Aza
  • 76,499
  • 26
  • 155
  • 134
  • Thanks! Any compression or caching options built into SQL that wouldn't require much setup on the main office side? I can only control the satellite side and might not have many options on the main office side. – Ken Pespisa May 07 '11 at 02:04