2

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.

3
  • 2
    Since your requirement is to add data to reporting database and that must also not syn with source database you are left with replication or some manual method like SSIS to move data but 1 min delay cannot be guaranteed.
    – Shanky
    Sep 27 '16 at 10:25
  • Thanks. A 1 min delay isn't a show stopper. As long as it will be kept within minutes rather than hours. :)
    – JaggenSWE
    Sep 27 '16 at 11:29
  • As long as your source database tables have date/time fields that are updated during data modifications, you should be able to write a custom query that connects, gathers only the modified records and imports them into a staging area for further processing. This can be achieved in many ways, there is no best way. Ask yourself what you are comfortable doing (SSIS, Powershell, T-SQL / Linked Server) and the skill set of those that will be supporting it.
    – datagod
    Sep 27 '16 at 17:20
3

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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.