Tips for splitting database

Question

I have read a couple of books on SQL Server 2005 but have not found a proper answer to what I am looking for.

The problem is like this :- I have a database that is being used by 5-20 users at a time for booking customer orders. They receive many orders in a day on phone so placement of orders and lookup of products\old orders have to be fast.

Over time many orders have been placed. There are many tables which contain this order details and there are many reports which use this data. Problem is that the reports are very slow. Indexing has helped a bit but not as much as I expected.

After reading around a bit I want to try dividing the database into two - one for online transactions and one for reporting only.

I would be very thankful to you if you help me by suggesting a book or site that will teach me how to design the database for quick reporting and how to separate out the two databases - one for online transactions and other for quick reporting (Will this be a datawarehouse design for quick reporting? )

My main goal is to make very fast reports ( some reports now take 5 mins to run and it will get slower with more data ). Any help in pointing me in the right direction will be deeply appreciated.

Answer 1

Look at Data Warehouse Toolkit from Ralph Kimball. Just a plain star-schema may be able to speed up reporting. And here is an example of how the star schema simplifies reporting.

Answer 2

First, definitely look at your existing design and workload.

If you can't optimize your OLTP side any further, I would totally go to a Kimball data warehouse methodology. Update your data on a regular database using SSIS or whatever and transform your data into stars. What you should find is that your reporting performance should improve drastically and not interfere with your production transactions on the OLTP/normalized side.

This can improve to the point where you can even keep the two databases in very close sync using the spare cycles which were previously beeing eaten up by reporting on a normalized database schema which is not well suited to reporting. You can use triggers or scheduled tasks to keep the warehouse up to date relatively easily with more complex options available if you scale up.

If your database is not terribly huge, this does not necessarily need to be in two databases, you could just use a different schema to keep them logically organized, and even if you split it, you can put views in your OLTP database to make them available without changing the database on your connection. The main benefits of having a separate database is to change your database-wide options, like collations or backup model (of course you can also use file groups to help with that).