March 1, 2011 at 3:46 pm
Me and my senior had this argument an hour ago.
Me:
May be we can Mirror our Databases from Production to ServerB and take snapshots out of the mirrors and use it for reporting.
Senior: SO you mean both our mirror and reporting dtabases are being housed on the same server ?
Me: Yes, That way we are accounting for distater recovery too.
Senior: How come? Mirroring has nothing to do with disaster recovery.
Me: What if we keep the mirror in a offsite location. That way if our data center blows away we still have a our mirrored server located in a remote safe location
Senior: If you Production server blows out, your mirrorr is of no use ?
My question:
Is mirror of no use in this senario ?
What do you infer from this discussion ? I am i being baseless?
[font="Verdana"]
Today is the tomorrow you worried about yesterday:-)[/font]
March 1, 2011 at 5:25 pm
Mirroring is predominantly a high availability/disaster recovery solution. Reporting is a secondary usage of it. If you have mirroring and the primary server fails (shuts down, destroyed, etc), the mirroring takes over, if the apps are using SNAC they fail over transparently and all is well.
I suspect your senior may need to do some additional reading on mirroring.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 2, 2011 at 12:24 am
Mirroing is highavailability solution and this is the primary role of this technology. There are two cases :
1.
If you configured your mirroing with witness server in that case If pinciple got down then mirror take over the prod database i.e. mirror would act as primary and user will not be affected. And there is no DATA LOSS in this scenario.
2.
If you didn't configure ur mirroing with witness , in that case If principle sever got down , then u have to do force failover and u may have some data loss however mirror would be up and will act as principle.
Thanks
Ashok
MCP|MCTS|MCITP|MBA IT|
March 2, 2011 at 12:32 am
Mirroring is considered HA. If you mirror across separate locations, you have created an opportunity where your mirrored database can be brought up at the secondary site in case your server crumbles - thus it can serve the DR purpose. We set one up like that once. And yes you can run reports off the secondary server.
As Gail said - have your senior read up a bit more on it.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 2, 2011 at 1:23 am
ha\dr that is the technology. You could also log ship with mirroring to create another database instance besides the mirror for reporting if you were really wanted to segregate your DR database
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 2, 2011 at 2:29 am
ashok_a2004 (3/2/2011)
Mirroing is highavailability solution and this is the primary role of this technology. There are two cases :1. If you configured your mirroing with witness server in that case If pinciple got down then mirror take over the prod database i.e. mirror would act as primary and user will not be affected. And there is no DATA LOSS in this scenario.
2.If you didn't configure ur mirroing with witness , in that case If principle sever got down , then u have to do force failover and u may have some data loss however mirror would be up and will act as principle.
Thanks
Ashok
MCP|MCTS|MCITP|MBA IT|
Chances of DATA LOSS depends on mode (high-safety or high-performance).
There is a chance of data loss if your mode is high-performance (asynchronous) even if you have Witness server.
March 2, 2011 at 4:26 am
Do Not use Mirroring as a substitution for replication.As in your case your boss suggested to use mirroring for reporting purpose.
Mirroring should only be used for High availability only.Keep it that way.For reporting set up a transactional replication on your principal server.Only condition would be mirror and principal use same distribution database.
For more information have a look here
http://msdn.microsoft.com/en-us/library/ms151799.aspx
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
March 2, 2011 at 2:54 pm
Allin,
Yes, database mirroring was designed for Disaster Recovery. There are some concerns in using snapshots for reporting, users will be disconnected when snapshots occur, the data will be as current as the last snapshot. It may be a good option if only a few snapshots a day are required.
Mirroring for High Availability? Not so sure about that. I'd lean towards clustering for a HA solution.
Dan
March 2, 2011 at 3:32 pm
allin1 (3/1/2011)
My question:Is mirror of no use in this scenario ?
What do you infer from this discussion ? I am i being baseless?
Mirroring was designed for HA/DR, although there is a definite difference between HA and DR, the lines are blurred.
Mirroring has an advantage over Windows Clusters in that the SAN isn't a single point of failure as it is with most clusters.
If you use DB Mirroring for DR only you don't pay SQL Licences on the Mirror server BUT as soon as you use snapshots and start reporting from them, you will need to licence SQL.
Note that Mirroring was not designed to be used in conjunction with snapshots to run reports, this is just happens to work and as far as I know it's not really Microsoft recommended and can give performance problems. When you run a report against the Snapshot, any table that hasn't been updated since the snapshot is actually being read from the Mirror Database. I've seen scenarios where both the mirroring process and the report process start causing blocking, so you will need to watch this.
I agree with Sachin that if you want to do reporting rather use Replication and publish only what you need to report from. This way you can also design indexes and stored procs etc. that are specific to the report environment as opposed to for the OLTP environment. This is a much cleaner reporting solution. You still need to have a robust HA/DR solution, you could Mirror and replicate to the same Server (different databases) and still be better off than using Mirroring & Snapshots.
Cheers
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
March 2, 2011 at 3:51 pm
Leo.Miller (3/2/2011)
Note that Mirroring was not designed to be used in conjunction with snapshots to run reports, this is just happens to work and as far as I know it's not really Microsoft recommended and can give performance problems.
It was fully designed to work that way, it's the only time a snapshot can be created on a database that's not online.
http://technet.microsoft.com/en-us/library/ms175511.aspx
Yes, it may cause performance problems, but any snapshot can do that.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 9, 2011 at 5:48 am
Yes, we have used mirroring for disaster recovery purposes for many years. It works great even over a WAN.
March 10, 2011 at 2:42 am
Mirroring is an inexpensive H\A and DR solution. Is has beed designed for that and developed for that.
Not for reporting purpose, you must have a report server if you need reporting service and maybe link the report server to the databases server. Secondary server in mirroring is a stanby server in case the primary database server fails, not necessary db server even if the Database that is mirrored fails then sec will take over.
Always consider that mirroring has nothing to do with reporting.
March 11, 2011 at 10:32 am
Mirroring can be expensive. It depends on what latency your business can live with.
In Finance it tends to be < 5 ms as loosing your primary business / trading platform can get very expensive very quickly.
As with most things in SQL and life it's horses for courses.
April 20, 2012 at 8:33 am
GilaMonster (3/1/2011)
Mirroring is predominantly a high availability/disaster recovery solution. Reporting is a secondary usage of it. If you have mirroring and the primary server fails (shuts down, destroyed, etc), the mirroring takes over, if the apps are using SNAC they fail over transparently and all is well.I suspect your senior may need to do some additional reading on mirroring.
Can someone please explain what is SNAC?
April 20, 2012 at 8:38 am
SQL Native Access Client.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply