Sql-server – Should I use Log Shipping, Replication, SSIS or something else

replicationsql serversql-server-2012

I have a BizTalk server Environment running and now the time has come that we've all experienced. People are asking for reports of the data flows and statistics on processing quantities. The data resides in a SQL server database where the tables have minimal indexing and in order not to incur any heavier costs for inserts and updates (which is the primary activity in those tables) and in order for us to customize the data a bit we've decided to have a separate application database to be the base of a web application we're setting up.

However, looking into the possibilities of copying the data from our prod. server to the applications db server gives quite a lot of options.

So, what should I opt for? Log shipping, replication, using SSIS to create some form of scheduled ETL or something else?

The basic requirements are:

  • Data should be in the application DB within minutes (real time not required)
  • The data in the application DB might not be identical in structure to the source table (i.e some columns might be missing, some others might be added)
  • The data should ONLY be added, no updates or deletions in the target even if the source database is purged.

I'm a bit at a loss here for a clear path forward, so your input is greatly appreciated.

Best Answer

From all the techniques that you are thinking of, only replication will suit your requirements.

Data should be in the application DB within minutes (real time not required)

Use transactional replication.

The data in the application DB might not be identical in structure to the source table (i.e some columns might be missing, some others might be added)

You can publish what columns you want. Make sure that @replicate_ddl is set to 0.

The data should ONLY be added, no updates or deletions in the target even if the source database is purged.

This is where T-Rep shines out. You can choose do not replicate DELETE statements in the statement delivery --> Delete delivery format (same for update delivery format as well). This will require reinitialization.

Be careful when generating snapshot as it will overwrite all the data on subscriber side, though there are ways to carefully do it.

Related Question