April 15, 2008 at 11:46 am
Okay.....here's the scenario I'm looking at....
We have a large Imaging application and we're in the process of moving the back-end of the application from a IBM DB2 database to a MS SQL Server 2005 database. While doing this, I am also in the process of re-working our reporting application. The reports' backend has always been MS SQL but now that we are moving the actual application's data over to MS SQL, we've decided to put both pieces on one MS SQL Server, just in separate databases.
So.....workflows kick off in the Application database and generate some data over in the Reporting database......both databases are sitting on the same server (which is actually just an instance on a cluster).
I want to keep the Application database as fine-tuned and "lag-free" as possible.
My Question....
Should I be trying to keep as much of the workload being done within the Reporting Database or does it matter because any lag caused on the Reporting database will affect the App Database as well?
Thanks in advance.
April 15, 2008 at 11:50 am
Since both databases are on one server then they will contend for server resources (memory, cpu, network, tempdb, etc...), but locking in one will not affect locking in the other, except where related to server resources.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 15, 2008 at 12:35 pm
Okay, so all these triggers I'm making.....it doesn't really matter whether they go in the App database or the Reporting Database.....if they cause any lag then that lag is going to be felt in both databases anyway, right?
April 15, 2008 at 12:38 pm
On the triggers, yes.
Most of the time, but not always, a reporting database can be loaded periodically from the OLTP database. That means, instead of every update happening in both databases, run by triggers, you instead do the update/insert/delete in one database, and then periodically synch up the data. That means the reports might be a little bit lagged, but in most cases, a delay of 1 day/hour/whatever in the reports isn't that big a deal.
You might want to check that kind of thing out as an option.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 15, 2008 at 12:44 pm
It is really going to depend on what your triggers are doing.
If, for example, you have a trigger that takes 30 seconds to run, if it is on one of your application data tables, the insert/update/delete that fires the trigger will wait for the trigger to complete and make your application slow. However, a trigger running for 30 seconds in one database may have a minimal impact on another database.
Now, having triggers in a reporting database seems strange and I would have to wonder how you have a trigger that could be on your oltp database or on your reporting database. I would expect the two databases to have completely different insert and update activity.
April 15, 2008 at 1:14 pm
Okay, I wasn't clear that you wanted to implement this using triggers. Your triggers would have to go on the tables in the source db and implementing this way certainly is going to imporve performance than trying to do this over a linked server.
I would agree with GSquared that you should do periodic loads of the reporting database, not immediate using triggers. Why? Because you don't want a failure in a transfer to the reporting database to force a rollback of the original transaction. Making this asynchronous is much better. You may even want to look at service broker for this.
If the reporting database is a "copy" of the production db you may want to look into Sanpshots or Mirroring.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 15, 2008 at 2:51 pm
I have never worked with an OLTP Database before and really don't know anything about them.
The reason there are triggers firing all over the place for what I'm doing.....
The Application database has the data I need spread out over a whole bunch of tables as it was not really built with the intent of reports coming off it. It also stores much more data than I need so it's not really a straight sync.
So, a workflow gets kicked off in the Application Database......there is data elements A, B, C, D and E related to it but are joined tables away from the table storing the workflow data (and those tables have millions of rows in them). Obviously, I don't want to join through that much data while generating a report so that is why I am thinking of creating a correlating workflow row in my Reporting database and then using triggers to pull the data that I need.
Currently, the Reporting database is populated via nightly SSIS package runs. This is still a possibility with the arrangement we are moving towards, I was just thinking that it'd be nice to be able to provide real-time reports to the end users.
Thanks for all your input so far 🙂
April 16, 2008 at 5:47 am
Triggers may work for this, but they could cause you a lot of trouble. Since they are part of the transaction that fires them, they can hurt performance and cause errors pretty easily if you are not careful.
Make sure real-time is what the users really want before investing time in it. It always sounds nice but when two people run a report an hour apart and get different results they often do not understand why.
If they want real-time reporting, I typically start with a solution using transactional replication. When you configure replication it defaults to generating a bunch of stored procedures in the subscriber for inserts, updates, and deletes. Changing these procedures to insert, update, and delete a different schema is pretty easy and separates your transaction. This can all be run on a single server.
If it gets really complicated, service broker can also allow you to create events on data changes that are not part of the transaction. These are sometimes referred to as asynchronous triggers. This is a reasonable way to get a reporting database to self-update at the database level in real time. It is more complicated than using replication, but it is more configurable and ultimately more scalable.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply