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.