Subscriber trigger

Question

There are two servers. The first is an ERP production system. The second is a BI server for heavy analytic queries. We update the BI server daily with backups. However, this is not enough, some users want to see that their data changes more often than the next day. I do not have access to the ERP server and can do nothing but request backups or replicas.

Before you start requesting replication. I want to understand whether it is possible to use subscriber triggers to process not all data, but changed data. There is an ETL process that speeds up the execution of certain queries (indexing, conversion, etc.). Triggers should do the trick, but I can’t find a way to use them only on the subscriber side. The ERP system does not allow changes at the database level. Thus, the subscriber base seems to be suitable for triggers (they do not affect the performance of the ERP server). However, I cannot find a way to configure them. Processing all the data is crazy overhead.

Use case . A simplified example, let's say we have two replicated tables:

+------------+-------------+--------+ | dt | customer_id | amount | +------------+-------------+--------+ | 2017-01-01 | 1 | 234 | | 2017-01-02 | 1 | 123 | +------------+-------------+--------+ +------------+-------------+------------+------------+ | manager_id | customer_id | date_from | date_to | +------------+-------------+------------+------------+ | 1 | 1 | 2017-01-01 | 2017-01-02 | | 2 | 1 | 2017-01-02 | null | +------------+-------------+------------+------------+ 

I need to convert them to the following indexed table:

 +----------+-------------+------------+--------+ | dt_id | customer_id | manager_id | amount | +----------+-------------+------------+--------+ | 20170101 | 1 | 1 | 234 | | 20170102 | 1 | 2 | 123 | +----------+-------------+------------+--------+ 

So, I created another database where I store the table above. Now, to update the table, I have to truncate it and insert all the data again. I can join them all to check the differences, but this is too heavy for large tables. A trigger helps track only record changes. The first input table can use a trigger:

 create trigger SaleInsert on Table1 after insert begin insert into NewDB..IndexedTable select //some transformation from inserted left join Table2 on Table1.customer_id = Table2.customer_id and Table1.dt >= Table2.date_from and Table1.dt < Table2.date_to end 

The same idea for updating, deleting, a similar approach for the second table. I could automatically update DWH with slight delays. Yes, I expect performance delays for busy databases. Theoretically, it should work smoothly on servers with the same configurations.

But, again, there are no triggers on the subscriber side. Any ideas, alternatives?

Answer 1

MS SQL Server has "Change Tracking" features that may be useful to you. You activate the database to track changes and configure which tables you want to track. SQL Server then creates the change records each time it is updated, inserted, deleted from the table, and then requests changes to the records made since the last check. This is very useful for synchronizing changes and more efficient than using triggers. It’s also easier to manage than creating your own tracking tables. This has been a feature since SQL Server 2005.

How to use SQL Server change tracking

Change tracking only tracks the primary keys of tables and allows you to query which fields can be changed. Then you can query table joins on these keys to get the current data. If you want it to capture data, you can also use Change Capture, but this requires additional overhead and at least the corporate version of SQL Server 2008.

Change data collection

General process:

• Get current sync version
• Get the latest sync version that you used to receive changes
• Get all the primary keys of tables that have changed since the last version (insert, update and delete).
• Attach the keys to the data and pull out the data (if you do not use change capture).
• Save the data and the current synchronization version (from the first step).

Then you repeat this process when you want to subscribe to the next set of changes. SQL Server does all the magic behind the scenes to store changes and versions. You may also want to take a look at Snapshot Isolation ... it works well with it. The related article contains additional information about this.

Answer 2

This answer is somewhat roundabout, but given your severe limitations, you might consider it.

First, go to replication, as you seem to have decided. You mentioned creating another database, but are stuck with how to create triggers to populate it. The answer lies in the ability to run post-snapshot scripts. When creating a replication publication, the database administrator can specify a script to run on the subscriber after the snapshot.

You can create a script with all the triggers you need.

In addition, to prevent the replication of your triggers using “no trigger” (as defined in the ERP database), the database administrator will need to make sure that for each table on which you have triggers, the Copy custom triggers property is set to False .

Answer 3

You cannot receive updates in the ERP database until you implement triggers or track tracking in the ERP database. The best way is replication. If you do not have access to the ERP server and you can do nothing but request backups or replicas.

Answer 4

If you use the full recovery model in the ERP system database, you can use the log file delivery for this. This will still have some level of delay between the production system and the reporting system. If the delay between the DML operations issued in the ERP system and the reporting system will work. If you need almost instant access to data in a reporting system, replication and the associated overhead are your only good option. For more information about setting up sending a log file: https://docs.microsoft.com/en-us/sql/database-engine/log-shipping/about-log-shipping-sql-server