Replication


Although we are not here to teach replication, it is necessary to know a bit about replication, if our database contains triggers. We will cover the more salient points when looking at replication with user-defined triggers.

Three possible types of replication are possible:

  • Transactional – We have one master database, the publisher, where all changes are made and are passed to the subscribers, which are any other databases set up to receive any replicated information.

  • Merge – We still have a publisher database that sends out changes, but the subscribers can also send out changes.

  • Snapshot – The publisher database is copied as a whole to all subscriber sites.

With snapshot replication, triggers are not a problem as the whole database is replicated as a snapshot at a particular time, however we have to design carefully with the other two.

With transactional replication, we will be aware of what data is being replicated and how. Triggers on the subscribers do not need to contain any business validation necessarily, but contain updates to tables that are not replicated, such as tables in a different database.

Finally, with merge replication, we will have triggers on both tables with business rules and cascading actions. When data published on one system propagates through to the replicated systems, any trigger in place has to cater to data coming either from its own system, or from replicated data. Merge replication use triggers created by the Snapshot Agent to place any changes in the replicated tables into the system replication defined tables.

When replication is created for a table that is defined as immediate or queued update subscriber, then the replication sets up a default trigger that will fire first. Therefore, any change completed on that table will be replicated before any other trigger, when replication is allows subscriber databases to modify data to be passed back to the publisher database.

Note

Immediate or queued update subscriber is an update mode for subscriber databases to replicate any changes to the publisher.

One other point to note is that the nested trigger option must be enabled while using immediate or queued update subscriber replication. This is the default setting, but if we have disabled this option as some sort of safety check in our system, we will have to enable it again.

Important

If we already have a trigger defined as the first trigger, we must remove this before making the table an immediate or queued update subscriber, otherwise an error will occur. We cannot make any other trigger a first trigger, once this replication is set up.

While replicating, any trigger that has been defined as WITH ENCRYPTION will never be published. We either have to remove the encryption, which is probably not the best action, or manually place the trigger on each subscriber with the encryption included.

If we have user-defined triggers on the subscriber database, they have to be replicated to ensure that they are really needed there. We may find that moving the logic to the stored procedures defined for the replication itself makes the code more efficient, but will have to be controlled in a very tight source code fashion if we ever have to recreate the database with replication, for example when moving from a test setup to the live setup. Preferably, this option of moving code should only be considered if we find the replication slow, and there are lags in the updates.




SQL Server 2000 Stored Procedures Handbook
SQL Server 2000 Stored Procedures Handbook (Experts Voice)
ISBN: 1590592875
EAN: 2147483647
Year: 2005
Pages: 100

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net