Exception handling in a real time, SQL-Server driven system

Question

Hi,

I have developed a report viewer in .NET Winforms (it just runs queries and displays results).

This works against a reporting database. However, the above is a small subset of a much larger application, which gets data from another database. It looks like this:

Monitored system has a change in state (e.g. latency increases) => Event is recorded into SQL Server database (call this database A) as a transaction => This fires a trigger to write the same event into the reporting database.

I am not sure about the differences between the two databases, they may be tuned for different goals or there may be some financial or even political reason for the two databases.

Anyway, the term was mentioned that the reporting database is "transactionally dependent" on the main database. What exactly does this mean? The reporting database depends entirely on the transactions of database A? This made me think of some questions:

1) How could I handle the situation that the reporting database has no disk space, but database A is still firing triggers to the reporting database? Would it be good to queue 2) Linked to the above, would it work if I queue the triggers and their data not able to fire into the reporting db (not sure how, but conceptually...)? Even then, this makes the system not real time.

Are there any other dangers/issues with exception handling in a setup like this?

Thanks

Answer 1

Such dependencies are actually very bad in production. For once, triggers and updating (remote) databases is a sure shot to kill performance. But more importantly is the issue of availability. The applicaitons that depend on Database A are now tied to the availability of Database B, because if database B is unavailable then the trigger cannor do its work, it will fail and the application will hit errors. So righ now the amdinsitrator(s) of database B are on hook for the operations of the applications using database A.

There are many approaches for this issue, the simplest one is to deploy transactional replication from a publication in database A with a subscription in database B. This isolates the two databases from a transactional point of view, allowing for application dependent on database A to go ahead unhintered when database B is unavailable, or just slow.

Answer 2

If the system has to be real time, then triggers are the only way. Note that triggers are fully synchronous - the operation on the reporting database will have to complete successfully, or the trigger will fail, and it's likely you will then fail your operation on the transaction database since it's in a trigger, the statement on the original table will fail, which may or may not be caught, but either way the change to that table in the transaction database will not occur.

There are valid reasons for this scenario, but it really creates a dependency of the transaction database on the reporting database, since if the reporting database is down, the transaction database effectively becomes read-only or worse.

That's not really what you want.

You can look at replication if your database have the same structure. Typically, when I think of a reporting database, I'm thinking of something with a different structure which is optimized for reporting, not just another copy of the data isolated for performance reasons (which is fine, but this is basically simply throwing hardware at the problem to stop reporting users hurting transaction users).