SQL Server Performance Forum – Threads Archive
Real time reporting
I have been reading about Mirroring and wanted to know, If the following scenario works? My users want to have data available in real time for there metrics reports. Unfortunately when ran on production, it caused a havoc. Users experienced lot of blocking and there were some dead-locks aswell. Then came across with the new feature in 2005 about mirroing. Have been reading a lot on mirroring and wanted to know, if this can be used: Setup a High Protection operating mode with safety FULL, without witness in my environment (I already have cluster, so i was wondering no need for a witness server for a automatic failover) So only one principal database and a secondary server with my mirror production database copy. On this secondary mirror database, i shall be creating snapshots and will have my user’s point to this database for there real time reporting purpose. But my question is, what will be the performance impact on principal? Since my production will ship the logs to the mirror, will be there performance degrade? Remember my production is a very very heavy used with constant insert,update running simantenously. Apprecaited if someone please provide some input. Many thanks,Cali
If your prinicpal is heavily used, I don’t think it is advisable to run the mirrroing in safety FULL mode…
Running the reports on mirrored server should not cuase any performance issues on pricipal server but safety FULL mode can cause some issues because it has to commit on both servers before it sends aknowledgement to the client… With mirroring you can’t provide 100% real time reports because your reports are based on snapshots not based on mirrored db…
and you have to kill the users to recreate the new snapshot…
If you create the snashot at 7:00 am, users can only see the data upto 7:00 am from the snashot…You have drop and create the new snapshot you want to provide newer data to the users… I believe only transactional replication will provide without interuption and real time reports db… MohammedU.
Microsoft SQL Server MVP
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
Thank you MohammedU for your answer. Man there gotta be some way around for real time reporting without replication. anyway. i was reading some 2005 article and it said, server broker in 2005 is a new feature which does lot’s of things. dont know, what it actually does. do you have any idea about this?
Service Broker provides queuing and reliable messaging for SQL Server. It is used both for applications that use a single SQL Server instance and applications that distribute work across multiple instances. Within a single SQL Server instance, Service Broker provides a robust asynchronous programming model. Database applications typically use asynchronous programming to shorten interactive response time and increase overall application throughput. So it is not useful for your reporting purpose. Ok coming to your scenario where Database mirroring is setup, what sort of issues you have faced with blocking. Look at the queries the users executing and normalizing them will have better performance as compard to normal actions. For this reason you can use MIRROR server as a reporting instance to reduce overhead on thelive server. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
@http://www.askasqlguru.com/ This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
Thank you Satya for your answer. I shall post the user queries and will list the issue’s what we encountered. Thanks,
You can run your reporting queries with isolation level READ UNCOMMITTED to reduce the blocking unless it is neccessary READ COMMITTED…
MohammedU.
Microsoft SQL Server MVP
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
I would suggest to test it before having on the production,http://technet.microsoft.com/en-us/library/ms345124.aspx fyi. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
@http://www.askasqlguru.com/ This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
]]>