I have researched this on here (and other sites) quite a bit. I feel like I am missing something simple. So please direct me to another posting or help answer my question.
Basically, we have out grown our current database server. The main issue is high CPU usage because of poorly written reports on top of a poorly designed relational database. The other problem is due to the IO usage for both workloads running. Users experience locks and issues trying to save into the system during high usage times and when the reports are pointing to the same server. Indexing and maintenance is done frequently. There is little fragmentation in the indexes that would lead to increased IO or CPU. Locking (by the reports) has been minimized, however other queries still need to acquire locks to maintain compliance.
I am forced to split the two main workloads from it... Which by the way, should happen anyway, since one is an OLTP workload and the other is a reporting workload using SSRS.
Here are the three main options I have tried to create a separate reporting database also with the reasons why each one will not work.
Things tried already:
Full database copy – This is what we have been doing but it is slow and our reports will have 12 hour old data. This does not meet our business requirements or end user expectation.
Log Shipping – I tried to implement this but we lose the 15 minute restore option for our backups (Data Protection Manager). Being able to restore the database in 15 minute increments seemed more important than having a separate reporting database. This is a valid point made by my supervisor, though it is becoming an increasing problem due to the issues the users are experiencing with the OLTP side of the system. The other problem with log shipping is that the report server would be down while the restore happens on the reporting database.
3.Replication – Transactional Replication is Microsofts recommended solution. It would keep our 15 minute backups in place as well as give us almost live reporting data. Due to some of the code they use in our medical records system, it is not compatible with replication. The heavy use of truncate (not supported by replication) as well as the (questionable) ability to enable end users to create new database columns (changes to the schema) via the web interface.
Any help out there? Thank you.