February 4, 2011 at 2:43 pm
Scenario:
In order to not HIT prod boxes with querries, i am planning to create a Reporting server
To create one , I need to soem how replicate copies/snapshots of Prod DBs
Solution Being Considered:
I choose mirroring coz i cant afford clustering.
I paln not to use Witness Server coz i cant afford for one more server.
So I plan to mirror production DBs on to a Server and create MIRROR.
Out of those mirrorred DBs i paln to take several snapshots
So my REPORTING Server is MY MIRRORED Server
These snapshots will be taken based on the reporting needs and direct user qurrries to those snapshots.
Issues/Questions:
1. Do you think this approach works ?
2. Can REPORT SERVER BE my failover choice (Sicne i am using the same as MIRROR)
3. If it is a safe/workable choice , What happens to those snapshots when i failover to my MIRROR ?
4. Can a DB be mirrored on to Virtual Server ? (What are the disadvantages ?)
5. What am i loosing by not opting for a witness box in my mirroring enviornment?
🙂 Feel Free to tell me if this entire process is a big flop 😉
Sorry for too many questions.
[font="Verdana"]
Today is the tomorrow you worried about yesterday:-)[/font]
February 6, 2011 at 7:40 am
I could answer few of your questions
1. Do you think this approach works ?
YES
2. Can REPORT SERVER BE my failover choice (Sicne i am using the same as MIRROR)
YES.Since your reporting server is your mirrored instance, the mirror db will have the transactions
of the primary db based on the mirroring mode
5. What am i loosing by not opting for a witness box in my mirroring enviornment?
You are loosing the chance of automatic failover..
M&M
February 6, 2011 at 9:20 am
This solution will definitely work for reporting - however, you need to make sure you are running Enterprise Edition since snapshots are not possible in Standard.
Also, if you plan on using this reporting system as a possible failover you will need to setup mirroring with high safety. This setting means that all transactions must commit on the mirror before they commit on the principal. If there is any network latency this is going to cause you problems.
Additionally, the more snapshots you have - the more potential you have for issues. There are some limitations with snapshots that you definitely need to be aware of. For example, the sparse file cannot exceed 64GB - and that could be quite a bit smaller depending upon the actual data file and usage. Note: this is the size on disk - not the allocated size of the file.
I had a system where we had to drop and recreate the snapshot every 6 hours. If we went longer than that the system would end up hanging and caused issues in production.
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
May 16, 2011 at 1:20 pm
I would like to implement a similar solution but I don't understand what role snapshots play in this scenario? Is it not enough to just set up the mirror and point your reports to it as a data source?
Thanks in advance for any help offered.
Bob Beaghan
May 16, 2011 at 8:10 pm
RLB (5/16/2011)
I would like to implement a similar solution but I don't understand what role snapshots play in this scenario? Is it not enough to just set up the mirror and point your reports to it as a data source?Thanks in advance for any help offered.
Bob Beaghan
The mirrored database is not accessible until you failover from the principal. Database snapshots can be used to create a snapshot of the mirrored database though. This allows access to a read only copy of the database at the time when the database snapshot was created.
However, you need Enterprise Edition to create database snapshots.
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
May 17, 2011 at 3:50 am
note that you can't issue any writes and your mirror server must be licensed. ( normally failover/standy servers don't require sql licenses )
I will say I did some tests with mirrored databases for reporting and in my case I found that under load at the source database ( heavy inserts/updates ) the mirror couldn't keep up.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
May 17, 2011 at 2:01 pm
colin.Leversuch-Roberts (5/17/2011)
note that you can't issue any writes and your mirror server must be licensed. ( normally failover/standy servers don't require sql licenses )I will say I did some tests with mirrored databases for reporting and in my case I found that under load at the source database ( heavy inserts/updates ) the mirror couldn't keep up.
Did you find that there were issues with the snapshots associated with the mirrored database? Or were you having different issues with mirroring?
If you setup mirroring with high performance, and there is heavy load - I can see that the mirror will fall behind, but not sure how that would actually affect a database 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
May 18, 2011 at 1:59 am
snapshots are fine - most of my servers have lots of power to spare, the problem I had was that nearly all the reports had to do a load of data updates before publishing the report, which isn't very helpful. As another issue I was unconvinced of the available bandwidth in the datacentre and when I ran heavy insert and update tests the mirror ended up several hours behind. I am talking about a test which inserts 4 million rows ( 32gb data ) in about 15 mins. I'm looking into using mirroring for reporting on another project but I'm fighting against old school who can only think of almost bulk extracts for reporting.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
May 18, 2011 at 3:54 am
why not just use log shipping for the report databases, no need for snapshots or mirroring then?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 18, 2011 at 8:31 am
Perry Whittle (5/18/2011)
why not just use log shipping for the report databases, no need for snapshots or mirroring then?
Because then you have to kick off users to be able to apply the latest tran log backup. It is good for this purpose for some cases where all queries are quick.
May 18, 2011 at 10:02 am
Robert Davis (5/18/2011)
Because then you have to kick off users to be able to apply the latest tran log backup.
I personally think this is a small price to pay and depending on the frequency of the restores would possibly not even be noticed by the user base, especially if they are aware the database is restored\updated once every 2 or four hours say.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 18, 2011 at 11:05 am
If you only update the log shipping secondary every 2 or 4 hours, then I agree that it becomes a trivial issue.
If you're using log shipping primarily as DR and secondarily as reporting offload, then you will likely be restoring every 15 or 30 minutes. It becomes a much larger issue then.
Definitely, I agree with using log shipping secondaries for this purpose. Just answering your question about "why not".
May 20, 2011 at 2:44 am
log shipping didn't work for reporting because sadly all our reports need to issue writes ( don't ask ) to the data during generation - so I can only run reports against live.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
May 20, 2011 at 6:00 am
colin.Leversuch-Roberts (5/20/2011)
log shipping didn't work for reporting because sadly all our reports need to issue writes ( don't ask ) to the data during generation - so I can only run reports against live.
Ever considered a secondary db to host these writable objects ?
( link a view to that table if you don't want to modify any code )
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 20, 2011 at 7:27 am
If they need to do write operations, then mirroring isn't going to work either unless you do something like what ALZDBA recommends. What is the nature of the writes? If the writes are just temporary for manipulating data, can they do that in tempDB?
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply