#TSQL2sDay – Data marts across a shaky WAN link

It sounded good in principle, especially given the requirements and the limitations:

  • We needed our various sites to be able to access the data on their customers.
  • Our line-of-business application that would be installed on the workstations will use this data.
  • Our sites resembled a snowflake schema with respect to WAN connectivity (this was back in the day when frame relay was king).

The solution? The monthly warehouse of data would be pushed out as data marts during off hours to key sites. We’d use DTS (this was back in the SQL Server 7/2000) days to accomplish the push each month and everyone would be happy. What could possibly go wrong?

A lot, apparently:

  • The network provider had a negotiated maintenance window on the circuits from 12 AM until 6 AM every day.
  • The network provider frequently, and without warning, used the maintenance window.
  • DTS didn’t have the greatest of restart capabilities nor was it designed to handle outages in connectivity.
  • Some of the links to the key sites didn’t have sufficient bandwidth for a data mart push.
  • The key line-of-business application front-loaded a bunch of data, MBs of data, and the auxiliary links were even slower than the links to the main sites.

Needless to say, the solution didn’t work. In the end all the SQL Servers in the field were recalled, the data mart push was cancelled, and a remoting solution which required far less bandwidth was deployed to provide our users with the new line-of-business application. Access to customer data outside of the line-of-business application was also deployed via the same remoting solution.

Want to read more T-SQL Tuesday stories? Jason Brimhall is this month’s host.

About these ads

2 Comments (+add yours?)

  1. Trackback: T-SQL Tuesday #051: Bets and Results | SQL RNNR
  2. Trackback: T-SQL Tuesday #051: Bets and Results - SQL Server - SQL Server - Toad World

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 3,836 other followers

%d bloggers like this: