I have a situation I'm sure has been resolved by many, many others. I have an idea on how to resolve it but when I research, it doesn't seem like a lot of folks have implemented what I'm thinking about.
Here is the state of affairs we are in--
--We have a single Database (SQL Server) that's structured and used as an OLTP DB
--We have a need to Batch in data that's needed in transactional context
--We have a need for up-to-date Reporting via an internal UI
The problem, as you may have already guessed, is that when we Batch data in and out of the OTLP database, it's competing for resources and in some cases, locking transactional tables.
What I'd like to do is introduce a second database, let the 2 databases mirror, and only let one DB be accessible by transactional applications. If data needs to be Batched in or out of the transactional database, it goes in or out of the secondary, non-transactional database, and the mirroring will take care of the syncing.
When I researched this approach, I didn't get many hits.
Is there a better / more accepted way of handling this?
Just my opinion without being able to find numbers doing the same searching, but I would think batching into the mirrored database to let it handle the synchronization would cause the exact same, or possibly worse locking.
I suggest as a first step to enable snapshot isolation (ALLOW_SNAPSHOT_ISOLATION) and READ_COMMITTED_SNAPSHOT.
https://msdn.microsoft.com/en-us/library/tcbchxcb(v=vs.110).aspx
https://www.brentozar.com/archive/2013/01/implementing-snapshot-or-read-committed-snapshot-isolation-in-sql-server-a-guide/
Note the warnings by Brent Ozar about the possible repercussions if you have long running transactional transactions that you're expecting synchronous results on across multiple threads.