#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.