April 29, 2010 at 12:48 am
Here is the situation:
There is a transaction intensive database - used for both routine transactions and reports.
I was wondering if I could isolate these two operations and 2 independent databases, so reports could run off of one database and all the transactions could occur in another one. This would improve performance for the OLTP SQL database.
I have gone over a few options like, Mirroring, Log shipping, Replication, Snapshots, Clustering - but would like to discuss the best possible strategy for the desired result.
Please advise the best solution to implement this strategy, or any other thoughts/suggestion you may have.
Thanks all for the feedback.
April 29, 2010 at 3:52 am
At this moment I do not know anything about your reporting needs and environment. I'd say that the database snapshot is suitable for you, however consider the limitations (BOL ---> Limitations and Requirements of Database Snapshots), for example the peformance decrease due to the additional I/O. If this really hurts you, can you set up a reporting database, based on a recent production backup? You can synchronize the content by periodically exporting the new data during the off-peak hours. Another way of bringing the databases in synch is the Service Broker, which would be OK from the performance point of view, but, again, choosing a solution depends on your reporting needs.
* Database snapshots are available only in Microsoft SQL Server 2005 Enterprise Edition
April 29, 2010 at 4:34 am
The real solution is to design an incremental ETL process that loads to a report optimized database. If a closer to real time data is required, triggers, modified replication or possibly asynchronous triggers might be the way to go.
April 29, 2010 at 5:12 am
It is better to go for Mirroring if you intend for the the same database structure to be used as of OLTP database.
I would suggest that if you are thinking for separate database for reports (which is a good approach), go for de-normalized structure for reporting database. Load the data every night (or when the load is less) by a well written ETL process.
April 29, 2010 at 11:52 am
I sure wouldn't recommend triggers as a mechanism for this.
It really depends on what your business needs are. If you have the need for a near real time copy of the entire database, you should take a look at mirroring. Another alternative to cover the whole database or a sub-set is transactional replication. If your reporting needs are such that you're rearchitecting the data into star schema's (or whatever) in order to optimize reporting, you'll need to look at ETL, like SSIS.
But it's all driven by the business and the needs you're hoping to address.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 29, 2010 at 12:24 pm
Thanks all for the response.
The primary database is a heavily transactional OLTP database for a Shipping-System. Currently, the database is serving 2 major purposes: 1- routine shippping/scanning etc, and 2- reporting to users
In order to optimize the performance, I am hoping to create a new database (identical to primary OLTP one) for the reporting purposes - to deflect some of the calls to this new database. So, the need is basically is to have an almost realtime identical image got this OLTP database for reporting purposes.
Please advise thoughts.
April 29, 2010 at 12:45 pm
ankitwaitshere (4/29/2010)
Thanks all for the response.The primary database is a heavily transactional OLTP database for a Shipping-System. Currently, the database is serving 2 major purposes: 1- routine shippping/scanning etc, and 2- reporting to users
In order to optimize the performance, I am hoping to create a new database (identical to primary OLTP one) for the reporting purposes - to deflect some of the calls to this new database. So, the need is basically is to have an almost realtime identical image got this OLTP database for reporting purposes.
Please advise thoughts.
Mirroring or transactional replication. I'd bias a bit towards mirroring.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 29, 2010 at 7:33 pm
Grant Fritchey (4/29/2010)Mirroring or transactional replication. I'd bias a bit towards mirroring.
I would also lean towards mirroring - with the caveat that you need Enterprise Edition and the ability to create and manage database snapshots. You can't make the mirror database available to users directly.
You also have to be aware that your users will have to be added to both systems to allow for access. You'll need a login on each system - and add the user to the production system with appropriate rights. If you use SQL Authentication - you have to create the logins on the reporting system with the same SID, or the user will be orphaned and will not be able to access the snapshot.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 29, 2010 at 10:33 pm
if you are going for mirroring, this article might help you;
June 2, 2010 at 3:31 pm
ankitwaitshere (4/29/2010)
Here is the situation:There is a transaction intensive database - used for both routine transactions and reports.
I was wondering if I could isolate these two operations and 2 independent databases, so reports could run off of one database and all the transactions could occur in another one. This would improve performance for the OLTP SQL database.
I have gone over a few options like, Mirroring, Log shipping, Replication, Snapshots, Clustering - but would like to discuss the best possible strategy for the desired result.
Please advise the best solution to implement this strategy, or any other thoughts/suggestion you may have.
You are pretty close to discovering data warehousing, congrats! 🙂
I'll start by asking myself a question:
- Can reporting be done on "yesterday's" data?
If the answer is Yes - as it is 99.9% of the time then you have two options...
a) Do reporting as it was done a few years ago by taking a copy of your OLTP database every single day and adding some indexes to help with reporting...
OR
b) Do a proper data warehouse. This is going to take a little more time, I would suggest to go with dimensional modeling and build one datamart at a time. A daily ETL window would be enough to keep your DWH up-to-date.
If the answer is No - then you are in a rare situation where a near-real-time data warehouse is needed. If your answer is "no", think again. If after three times you still are aligned with a real-near-time data warehouse then I strongly suggest to hire a consultant with proven history of on time, high quality delivery.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply