Spreading Load on SQL Server database

Question

We have an enterprise application that uses Microsoft SQL Server as the database backend. We have been coming across a fair few instances where the customer has grown the application into a huge db and some of the queries being run are causing locks and performance issues for themselves and the other users.

We have tried to apply as many indexes as possible and perf tuned all queries too the limit but we have one application that must suit a lot of different customer types so its hard to create one solution that fits all.We dont have the resources to apply customer specific indexing/performance for every customer.

We know the main queries that are causing issues are the ones that are generated to drive reports and kpi's.

My question is, is there a way that we can spread the load of the application so the day to day use isn't hampered by the report/kpi generation. i.e. Could we someway mirror/duplicate the db so that day to day actions are sent to SQL Entity A and the data intense queries and sent to SQL Entity B? Therefore the data intense queries have no effect on the day to day side of things and we could queue the queries going to SQL Entity B.

In this scenario SQL Entity A and B will need to be kept in alignment at all times, but SQL Entity B will always be read only.

Can anyone suggest any paths we could try to accomplish this? Or is there another method I should be looking at to get us performance wins.

Thanks

Answer 1

It seems you can go with any replication option there is and be fine. In one of my previous jobs we used Log Shipping http://technet.microsoft.com/en-us/library/ms187103.aspx for that purpose.

Also you can work through replication types: http://technet.microsoft.com/en-us/library/bb677158.aspx and see which one should suit you best as you can do more than just reports on the secondary database.

If I remember correctly my initial experience, Log Shipping is extremely easy to set up so you may want to start there.