Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 5 down vote favorite

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.

share|improve this question
you should be able to create a publication on your dev server and then create a subscription database with a different name so that you can test on the same server. I haven't done this personally so YMMV. – DForck42 Nov 30 '12 at 21:15
create another instance on your dev server to replicate to for dev purposes. Then you can test away! – Max Vernon Dec 1 '12 at 0:28
Thanks for your feedback, guys, but I am unable to do that for rules-at-work reasons. It is what it is. And even if I could test it, I'd still be asking the question. :) – JHFB Dec 3 '12 at 11:18
It depends on the type of trigger - if you use an after insert trigger, you will be fine - however, I would put your trigger logic in a try/catch block. That way, if there is an error with your insert, you know that your generated table is out of synch. It's also important to keep in mind that with replication, when you need to re-initialize, it's possible that you will need to re-create your trigger - this depends, of course, on how you re-initialize. Another option, would be to write a simple stored proc which moves/generates this data for you an a set interval - i.e. every 10 minutes. – Jeremy Lowell Dec 6 '12 at 14:58
.... The last (good) option, would be to use SSIS for this as well - those options however all depend on how latent the data can be - if reporting can work with data which is 10 or 15 minutes old, then writing a process outside of using a trigger based approach would be the option that I'd personally target first. – Jeremy Lowell Dec 6 '12 at 15:00
show 1 more comment

2 Answers

up vote 5 down vote
+50

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.

share|improve this answer
This is an intriguing option and one I will be exploring today. To clarify your first bullet - could it slow down replication because data must be written to the index that is created on the view? – JHFB Dec 6 '12 at 14:09
Turns out this is not an option due to OUTER JOINS in the SELECT statement. :( – JHFB Dec 6 '12 at 14:30
Bummer! And to clarify, yes, the overhead of writing data to the view is what would slow it down. But you'd be doing this within triggers anyway, so the performance difference would probably be comparable. – db2 Dec 6 '12 at 16:42
up vote 0 down vote

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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