Loading the Cloud - SQL Server Integration Services Design Patterns - page 365

• Are hosted by a third party
• Are outside of the physical enterprise domain
I understand these points are subject to debate. I will not debate them here. This
definition will likely not survive the years following this writing (2012) and revision
(2014). And even now, there is ambiguity and a blurring of lines between what is and is
not considered “in the cloud.”
For demonstration, I am using data collected from my local weather station in
Farmville, Virginia. The weather data are exposed and available at AndyWeath-
er.com . AndyWeather.com is hosted by a large hosting company that provides
remotely-accessible SQL Server database connectivity. As such, the data are stored in
the cloud (according to my definition).
I also host weather data using a Microsoft Azure SQL Database: the same data,
stored in a different location. “Why?” The simple answer: fault tolerance. Fault toler-
ance is the same reason DBAs perform database backups and test database restores.
The difference between a technician and an engineer—or a developer and an archi-
tect—is that a technician builds systems that succeed whereas engineers build systems
that don't fail. It's about mindset. Technicians get it working and stop there. Engineers
consider many ways the system can fail and try to fix these things before they break.
Incremental Loads to Azure SQL Data-
base
An incremental load is one in which only new or updated rows (and sometimes de-
leted) rows are loaded or changed. Incremental loads can be contrasted with the
truncate-and-load pattern, where the existing data in the destination is deleted and all
data is reloaded from the source. Sometimes truncate-and-load is most efficient. As
data scale—especially at the destination—truncate-and-load performance often suffers.
How do you know which will perform best? Test, measure, rinse, and repeat.
One benefit to truncate-and-load is simplicity. It is difficult to mess up a simple
mechanism. Incremental loads introduce complexity, and change detection is the first
place complexity enters the solution.
Change Detection