considerations when using triggers on a replicated (target) database

Our SQL Sever 2008 application database is replicated from Server A to Server B (push replication). We use the copy, let’s call it database_b, on Server B to create reports and run other queries so that our reports do not interfere with the application. Currently we leverage inefficient views to combine data across multiple tables in database_b so that report writing is simplified for our report writers (who have basic SQL skills).

99.9% of the database activity is INSERTS, so we are exploring a way to replace the inefficient views with tables we can optimize. Here is a simplified example:

There is an appointment table and a location (lookup) table. Every time a new appointment is scheduled, a row is added to the appointment table. Every time this INSERT happens, I want to take that appointment_id and insert it and its corresponding location name (join on location_id from both tables) into a reporting table.

I have accomplished this with a trigger on the appointment table in database_b on Server B.

My question is – are there any particular considerations given that database_b is a replicated copy? Do I need to worry about a failing trigger mucking up the entire (push) replication process? Anything else I am missing?

Unfortunately it’s difficult to test this in our development environment, so I don’t have the opportunity for a lot of trial and error.

This sounds like a great situation to use indexed views. These are pretty much what they sound like: a view that physically stores its results in the database, and is updated automatically when the base tables are updated.

A few cautions:

• Having a lot of these in your subscription database could slow down replication (but so could lots of triggers).
• There are some strict SET options you need to change in order to create them, and you have to use WITH SCHEMABINDING when creating the view.
• There are additional strict requirements about what can be in your view. For example, you can’t use outer joins, aggregates, or cross/outer apply.
• If you’re using Standard Edition, you need to query the view by name using the WITH (NOEXPAND) hint. SQL Server won’t automatically use the view if you attempt to query its base tables.

There’s (a lot) more detail in this article, as well as a nice example using the AdventureWorks database:
http://msdn.microsoft.com/en-us/library/ms191432%28v=sql.100%29.aspx

If it’s a relatively simple query that’s only performing poorly because of a lot of data and (inner) joins, this could be an easy way to improve it.

I suggest you explore the following options if you haven’t already done that

1. Propagation through Custom Stored Procedures. You could have the trigger logic in this Custom proc. But the disadvantage is your Subscriber and Publisher are coupled.
   http://msdn.microsoft.com/en-us/library/ms147880(v=sql.105).aspx

2. Specify that the change should be propagated using an INSERT, UPDATE, or DELETE statement and have the trigger.

This option might speed up replication if multiple rows are updated/inserted with a single statement.