SSIS or Log Shipping?
Brian Moran (@briancmoran) and I were meeting with a Linchpin People (@LinchpinPeople) client recently. They had a common complaint. “Our Production system performance is being impacted by reporting queries,” they told us. The client had done their research and concluded a data warehouse is the long-term solution. We agreed.
“But what can we do right now?” they asked. As we talked through possible short-term options, it became clear that Brian and I were advocating different solutions. We were doing so while considering the same constraint – time. Brian was advocating Log Shipping. I was advocating an SSIS solution. But we were both thinking, “How do we get this client out of hot water as soon as possible?”
Considerations and Constraints…
Log shipping can be set up quickly. It allows us to set up a second “warm” (minimally latent) copy of the data that the reporting folks can utilize for a source. There are several other facets to consider but our primary thought was time, and so log shipping made sense.
“What about SSIS?” I thought. We could use SSIS to maintain a warm copy of the data. Further, utilizing the incremental load pattern we could load vital tables every five minutes. The reporting database fed via incremental loaders would also make an excellent staging database for the client’s eventual enterprise data warehouse.
Traditionally it takes roughly two hours per to write (and test) an incremental load SSIS package. I like to build one SSIS package per table. If the client has 50 tables, I’m quoting 200 hours for SSIS development. We can set up and test log shipping in a matter of days. When time is your top concern, log shipping wins over traditional SSIS hands down.
But we’re not doing traditional SSIS.
SSIS and Business Intelligence Markup Language (Biml)
Linchpin People’s Data Integration Group uses Mist from Varigence to compose Business Intelligence Markup Language (Biml). Our solution – originally developed by Varigence as a demo – allows us to condense hundreds of hours of development into a few days of work. As Brian and I talked with our customer we realized we could deliver either log shipping or an SSIS solution in the same amount of time.
With the time difference off the table, we could compare the advantages and disadvantages of each solution. Our customer agreed with us – options are good!