April 11, 2010 at 10:22 am
xinyu.wang1 (4/11/2010)
i always like to say HADR - combining HA and DR together.
That's why I like term "Business Continuity" rather than HA or DR. You're absolutely right, the two are intertwined and you should be thinking about the big picture as a whole and not HA and DR as two distinct concepts.
April 11, 2010 at 11:52 am
Robert Davis (4/10/2010)
That's why most people use SAN's that have redundancy, large caches, considerable battery power, and well-thought out RAID arrays that provide redundancy and performance. That's not to say that all SAN's are great. A poorly configured SAN can be just as bad as any thing else.
We were looking at the Dell MD3000 and MD3000i as options.
Then we started looking at the Dell EqualLogic PS6010XV, but the price seems to get quickly out of had.
When we call Dell and talk to the server group they don't seem to be able to answer our questions about proformance on these units. Wish I could find someone to just talk Cluster vs. Mirror - that actually knows what they are doing!
April 11, 2010 at 12:10 pm
CirquedeSQLeil (4/9/2010)
Unless absolutely necessary I would not mirror that many databases...
It might not even be possible. On a 32-bit server, ten worker threads are required per mirrored database - plus one global thread. That's 1,301 worker threads! :w00t:
By default, SQL Server will only create up to 256 worker threads for the entire 32-bit server, assuming 4 CPUs. (Note that each worker requires 512KB of address space too).
Even on 64-bit, just running mirroring would require 261 threads, from a total pool of 512 - with each thread using 2MB VAS (though this is less of an issue on 64-bit systems).
I would not even attempt it.
References:
Things to consider when setting up database mirroring in SQL Server
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 11, 2010 at 12:14 pm
bhanf (4/11/2010)
Wish I could find someone to just talk Cluster vs. Mirror - that actually knows what they are doing!
This might help:
Database Mirroring Best Practices and Performance Considerations (MSDN Technical Article)
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 11, 2010 at 5:48 pm
You may want to look at bringing in a consultant that works with DR/HA/BC as a living. Having some one with experience in this area, especially in the planning phase where it is cheaper to fix issues than in the implementation phase.
April 12, 2010 at 9:15 am
I agree about hiring an expert. But I don't know who we could trust to give us correct information on the options.
Any suggestions in our area? USA Minnesota - Minneapolis
April 13, 2010 at 3:51 pm
Other than HA, you haven't actually stated what the requirements are - what does HA mean to you?
Questions to ask:
* how long can you afford to be down?
* how much data loss can you afford?
* what are you trying to protect against?
* will the application allow you to take full advantage of the solution?
* do you have the in-house expertise to maintain each possible solution, or will you be paying for ongoing support?
I'm sure there are many others. Replication may be an option for you. If you can give us more specifics we may be able to provide more specific detail 🙂
Matt.
April 13, 2010 at 4:14 pm
matt stockham (4/13/2010)
* how long can you afford to be down?* how much data loss can you afford?
Very Little - 2-3 minutes up to last 20 transaction per client.
matt stockham (4/13/2010)
* what are you trying to protect against?* how long can you afford to be down?
both - If our server is down for more than 2 hours on 10 specific days our company will go out of business. (we work on political campaign, so the day before an election outage would kill us)
matt stockham (4/13/2010)
* will the application allow you to take full advantage of the solution?* do you have the in-house expertise to maintain each possible solution, or will you be paying for ongoing support?
Not sure to the first, not sure what I would need to answer that.
And we would not hire out.
April 14, 2010 at 9:02 am
matt stockham (4/13/2010)
* will the application allow you to take full advantage of the solution?
Clustering is essentially invisible to the app, but mirroring needs to know about the mirror server(s). If your app can use the Native Client then it allows you to specify the failover server and will also determine this info from the principal - if a failover occurs then it's generally invisible to the app. If you can't use the Native Client then the app will have to be smart enough to figure it out itself, or require some manual intervention.
Hardware and licensing costs are going to be part of your equation. Are all of your databases on a single server currently? Mirroring depends on resources - network (bandwidth and latency), cpu, IO - and if you don't have enough then you will introduce an overhead into your primary server due to the need to commit on the mirror prior to the principal. You may be able to work around this to a degree by splitting the databases over multiple servers, or even by having the mirrors on multiple servers. Unfortunately the resources required are specific to your setup and it's going to need someone onsite to figure out if they are sufficient. Clustering is protecting everything but the disks, so you need to make sure that a drive failure or two isn't going to shut you down.
There are two other factors that will play into your decision - version and edition. Enterprise has more capabilities than Standard for both clustering (number of nodes) and mirroring (number of redo threads on the mirror). SQL2008 compresses the log stream to the mirror (2005 does not) which may make a difference considering the number of databases you want to protect.
April 21, 2010 at 9:50 am
After reading through the thread I can see what is the difference between clustering and mirroring. However I would like to get insight from the folks on this forum regarding using mirroring for Biztalk. Microsoft recomends using clustering for Biztalk enviorment. However due to the licensing cost we are also looking at doing syncronous mirroring for Biztalk. What do you all think?
April 22, 2010 at 7:52 am
Database Mirroring and Window clustering both help with High Availability in different ways and both have limitations.
I look at Window clustering as keeping a spare server ready to go. It requires a SAN which cost $$$ and does not support SQL Server Reporting Services. The hardware does not have to be identical, however when running on the slower node, the applications will probably notice.
Database mirroring does not require a SAN but does require a quick and reliable connection to keep the data up to date on the backup sever. Supposedly .NET applications can be to setup to automatically switch to the backup server when needed.
Both of these options only address the database availability requirement. You need to look at application side as well. Having the database available but the applications down is not very useful. Then there are the feeds to and from other database and application servers.
David Bird
April 23, 2010 at 11:55 am
Based off what you said above, I would say consider clustering for HA and log shipping to a remote site for DR.
Clustering will be invisible to the app and will protect you from a server/software crash. It will not protect you if your SAN goes away. Log shipping will, but you will have to restore 130 databases and reconfigure your app. That will take some time. But the way we look at it here, if your SAN goes away, you are going to have a really bad day, no matter what.
Also we are using DPM 2010. This is a really nice product, it lets us restore a database back to any point, up to 20 days back, in 15 minute increments. At least this is how we have it configured. This will come in very handy if you ever have any data corruption.
To be honest, I have not worked with Mirroring that much, but do believe with the number of databases you have, you will have problems with it. The overhead is going to be huge.
A long time ago, when I first got into doing DB work, an instructor told me, "If you can recover from the city that your data center is in going away, then everything else is cake." Just something I have tried to keep in mind when designing my DR. Hope it helps.
April 23, 2010 at 12:38 pm
My boss has decided on a mirror. We have found an ISP to co-locate in that has two data centers with a LAN between them that is fiber and currently running a 1GB connection - upgrading this year to a 10GB connection. We are hoping that this limits our exposure on the overhead of running mirrored. As far as the limits on the number of concurrent connections we see that is the default and that we will be able to mirror about 400 database's.
The two new servers we bought are super fast and have 128 GB RAM - I think we will be able to mask a few of the preformance issues that come up with mirroring.
Our goal will be to add clustering to each site in the next year or two.
April 23, 2010 at 4:08 pm
400 databases? On how many servers?
April 23, 2010 at 9:09 pm
Robert Davis (4/23/2010)
400 databases? On how many servers?
Terrifyingly, just the one it seems :blink:
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 15 posts - 16 through 30 (of 51 total)
You must be logged in to reply to this topic. Login to reply