SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

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

Databases – Infrastructure – Security

Brian Kelley is an author, columnist, and Microsoft SQL Server MVP focusing primarily on SQL Server security. He is a contributing author for How to Cheat at Securing SQL Server 2005 (Syngress), Professional SQL Server 2008 Administration (Wrox), and Introduction to SQL Server (Texas Publishing). Brian currently serves as an infrastructure and security architect. He has also served as a senior Microsoft SQL Server DBA, database architect, developer, and incident response team lead.


Leave a comment on the original post [truthsolutions.wordpress.com, opens in a new window]

Loading comments...