February 4, 2010 at 7:49 pm
Hi Experts,
I want to achieve High Availability on SQL server 2005 on our standby servers with zero RPO(Recovery Point Objective ) and according to my knowledge following are the solutions -
1)replication
2)mirroring &
3)clustering
but each one has its own advantages & disadvanges..
So I just want your expert opinion about these options if anyone has already applied on their setup.
Thanks in advance. 🙂
February 4, 2010 at 11:55 pm
Replication is not a HA technique.
By 0 RPO, do you mean absolutely 0 downtime? That's not possible. It's possible to get it to a couple seconds in favourable circumstances, but not 0.
How much budget is there? What are you protecting against?
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
February 5, 2010 at 10:00 am
Thanks Gail for sharing the info.
Budget is not much so customer dont want to go for clustering...
The goals to achieve are -
1) secondary server (standby) should be always in sync with primary(production) with say few seconds lag.
2) production server should not hamper in case of any disastor at standby server
3) Performance of production server should not be impacted
so according to me,
Replication is the best suited ... but still I m confused ....
February 5, 2010 at 11:41 am
Replication is not a high availability technique. While it can create a copy of a database, it's a pain to switch apps over, it may not be a complete copy and it's a real absolute pain to switch back.
Read up on database mirroring, it's probably the one you want.
Just bear in mind, in general, the less you're willing to spend on HA, the less you get in terms of easy of failover/failback, latency, etc.
p.s. Considering that you have the SQL 2005 certs, you should know about 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
February 5, 2010 at 12:12 pm
As Gail has pointed out, though Replication is listed / treated as one of the HA features, it would be cumbersome to implement it as a true DR or HA solution. It would still be one of the better features for having multiple copies of data.
Replication replicates only published objects (chosen as a part of the publication) but any new tables and other objects added in the database would need to be added in the existing publications or create newer one which would be messy and tedious not to mention the switch over / redirecting the application (already mentioned by Gail).
I would definitely go with Database Mirroring as the choice for achieving HA.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
February 5, 2010 at 12:15 pm
Bru Medishetty (2/5/2010)
It would still be one of the better features for having multiple copies of data.
Sure, it's fine for secondary reporting systems. It's just bad for hot failover servers, especially if you're looking for 0 failover time
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
February 5, 2010 at 12:25 pm
GilaMonster (2/5/2010)
Bru Medishetty (2/5/2010)
It would still be one of the better features for having multiple copies of data.Sure, it's fine for secondary reporting systems. It's just bad for hot failover servers, especially if you're looking for 0 failover time
I am in complete agreement with you Gail on Replication not being a HA / DR solution.
At each job I implemented replication, it was more of an additional database to move read only users away from the OLTP system or setting up a Data Warehouse which needed several different tables from multiple databases consolidated for DSS system..
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
February 5, 2010 at 2:28 pm
If cost is not constraint then Clustering is the best option for high availability, you have two options in clustering Active\Active and Active\Passive.
Active\Active: In any point in time both nodes are active meaning SQL services are running on both nodes. If any one nodes fails then all the services will failover to other node, so you have two instances running on same node now, downtime is in seconds may be 5 secs. Now you have time to fixed other node, and failback\move services back to original node.
Active\Passive: In any point in time only one node is active that is SQL services are running on only one node other node acts as stand by or passive. If active node fails all services will failover to passive node, which is active node now.
Remember clustering is good only for OS level, If disk fail you can't do anything, however you can manage disk failures at SAN level.
As Gail and Bru mentioned Replication is good for object level, you can do it in database level, but it's pain in the butt. Database mirroring and Log shipping can be considered as high availability solution only if you have cost constraint, all depends criticality of the business.
For clustering we don't need DBA intervention at all in most cases if node fails, thats not same with database Mirroring and Log Shipping.
Enjoy!
February 6, 2010 at 7:49 am
I guess the OP had mentioned Clustering is not an option due to budget constraints.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
February 9, 2010 at 6:27 am
Hi Gail/Bru
Thanks For the nice reply....
Very first even I have suggested customer to go for mirroring but customer can't afford 3rd server (which we can use as a witness) then I suggested for Synchronous mirroring but I guess in synchronous mirroring there could be performance impact on production server as it has to wait till the transaction completes on secondary server.Customer is also concerned about the performance impact on production :doze:
so in short custmore not agreeing to invest more for witness server and also wants a performance on production server :crazy:
February 9, 2010 at 6:38 am
As you have mentioned your client cannot afford for a 3rd server in setting up Mirroring, have you thought about the option of Log Shipping? Again it would not be that synchronous as DB Mirroring, but it would be a better option than Replication.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
February 9, 2010 at 6:51 am
Tushar-355587 (2/9/2010)
Hi Gail/BruThanks For the nice reply....
Very first even I have suggested customer to go for mirroring but customer can't afford 3rd server (which we can use as a witness) then I suggested for Synchronous mirroring but I guess in synchronous mirroring there could be performance impact on production server as it has to wait till the transaction completes on secondary server.Customer is also concerned about the performance impact on production :doze:
so in short custmore not agreeing to invest more for witness server and also wants a performance on production server :crazy:
The third server doesn't need to be dedicated. You could use another existing server, like a file server, by simply installing a copy of SQL Server Express for the sole purpose of serving as a witness server. It doesn't even have to host any databases.
February 9, 2010 at 6:55 am
You can do asynchronous mirroring without a witness, but it doesn't have the same level of data protection as synchronous, manual failover is required and there's a chance of data loss during the failover
HA is one of those areas where you get what you pay for. The less you're willing to spend, the less protection you can get.
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
February 9, 2010 at 7:03 am
I would suggest you follow Lynn's / Gail's suggestion on mirroring, I forgot to mention that.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
February 9, 2010 at 12:59 pm
Gail's suggestion about using a sql express instance works really well. I've implemented it like that and had zero problems over a 2 year period of time that I managed it.
If this is sql server 2008 and you're using FILESTREAM you can't do mirroring but can still do log shipping.
---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply