April 15, 2014 at 8:37 am
Bill,
Your senario might work if I had only a few databases to mirror and a few "applications" to change connection strings.
I have 66 databases, and growing, on the cluster. I need to keep the instance name the same. There are probably just over 125 applications that connect to the cluster, not counting Sharepoint. Then there are the hundred plus data sources in Reporting Services.
I would be lynched by the developers and the sys admins if we had to change the connection strings.
Bill Soranno
MCP, MCTS, MCITP DBA
Database Administrator
Winona State University
Maxwell 143
"Quality, like Success, is a Journey, not a Destination" - William Soranno '92
April 15, 2014 at 8:55 am
William Soranno (4/15/2014)
Bill,Your senario might work if I had only a few databases to mirror and a few "applications" to change connection strings.
I have 66 databases, and growing, on the cluster. I need to keep the instance name the same. There are probably just over 125 applications that connect to the cluster, not counting Sharepoint. Then there are the hundred plus data sources in Reporting Services.
I would be lynched by the developers and the sys admins if we had to change the connection strings.
We simply create dns alias names for all of the servers. That way when you replace the server or cluster you simply change the alias name to point to the new hardware. No one has to change anything then.
April 15, 2014 at 8:57 am
William,
Unfortunately, you can't add a Windows 2012R2 node to a Windows 2008R2 cluster. If you could I'd add an 8th reason to my article 😉
I'm not sure there is a good solution in your instance without at least some short down time.
I like Bill's idea of creating mirrors, but agree that may be a lot of work. I guess you would just need to weigh that against downtime in doing backup/restores and/or database file copies.
One option that might lessen (I didn't misspell it this time) the downtime would be to bring up the new SQL Server cluster and mirror as Bill suggest, break the mirror at some point, then shut down the old cluster. Then create a DNS entry that points the old virtual SQL name to the new virtual name. That way, no applications would need to change. You could then, at a later date, make the connection changes to point to the new cluster name, at your leisure.
Francis
-----------------
SQLRanger.com
April 15, 2014 at 9:42 am
We actually did just that. We mirrored over, failed over and just did a DNS Alias. It's faster and effective. The only reason I didn't mention it prior is that after a few generations... someone has to go through and clean it all up. ^.^ It works great though. 66 Databases isn't a huge number. All things take time, but depending on your SLAs and the cost per minute of downtime... it may be worth the extra effort.
Personal opinions and all.
.
April 15, 2014 at 10:39 am
Markus (4/15/2014)
Interesting article and you make some good points. However, we are going to migrating away from our Clusters that we have as we upgrade in the coming years. We are virtualizing everything and snap cloning everything to our disaster site as well as replicating our current backups to our disaster site as well.
We are looking to migrate away from everything using Windows clustering (including SQL Server) as well. I agree with all the selling points in the article, but for us it has just been too problematic. For some reason in our virtual environment the clustered nodes will occasionally have a hiccup with the quorum disk. Whatever the cause, if the same issue happened to any other drive it wouldn't be problem, but with the quorum disk it is enough to bring the cluster completely down.
--Andrew
April 15, 2014 at 11:06 am
Since I didn't know what "clustering" was (other than an inference in a Clint Eastwood movie) it was an interesting learning event for me. I need a disaster recovery option to keep the database running. Seems like there are a lot of cluster-haters in the response group, but I realize it's not a representative sample. Thanx for the article and keep 'em coming!
Wallace Houston
Sunnyland Farms, Inc.
"We must endeavor to persevere."
April 15, 2014 at 11:09 am
To be fair, I don't hate clustering. I like what it is supposed to do, when it works. We've just had spotty luck with it.
--Andrew
April 15, 2014 at 11:24 am
I agree with most of the posts so far, in that clustering can be really tricky to get right, especially on anything under Windows 2008. Windows 2012R2 finally gets it (mostly) right 🙂
When I was first introduced to clustering, we were doing it with Windows 2000 and SQL Server 7. I inherited a SQL cluster that would crash several times a week. We had continuous problems keeping the cluster up and running. Our cluster was totally unreliable. After spending countless hours on troubleshooting, we decided to rebuild the cluster from scratch using windows 2003 (still SQL Server 7). The resulting cluster was rock solid and I can't remember ever having it fail. It did save our bacon several times during it's life when we ran into hardware issues with the nodes.
I guess my recommendation, if you are going to look into clustering, would be to start with Windows 2012R2, if at all possible. (That would be my recommendation too for Availability Groups by the way, as there are fixes that address issues with AG baked right in).
Francis
-----------------
SQLRanger.com
April 15, 2014 at 11:30 am
Don't want to get off topic, but we have our SQL running on a VM and would like to have another running SQL ready to go in case of emergency. I have another server running SQL, but they aren't in synch. I've looked into log shipping, but saw a little flak on that topic, also. Any preferences between clustering and log shipping?
Wallace Houston
Sunnyland Farms, Inc.
"We must endeavor to persevere."
April 15, 2014 at 11:35 am
That's good to know. I'll ask our admin to look at clustering again now that we are building new servers in 2012R2. Most of our problems have been with 2003 server, but just yesterday a 2008 file server cluster blew up. (Our admin thinks yesterday's crash may have been due to Diskeeper automatically enrolling the quorum disk for defragmentation when the virtual machine rolled over to a new host even though the quorum disk had been excluded previously.)
--Andrew
April 15, 2014 at 12:16 pm
Don't want to get off topic, but we have our SQL running on a VM and would like to have another running SQL ready to go in case of emergency. I have another server running SQL, but they aren't in synch. I've looked into log shipping, but saw a little flak on that topic, also. Any preferences between clustering and log shipping?
To do clustering, the server has to be setup to be clustered. SQL has to be installed as a cluster and there is a lot of work to do there. There's more than just two solutions here though.
availability groups, see clustering. http://www.brentozar.com/archive/2010/11/sql-server-denali-database-mirroring-rocks/
Clustering (two servers, shared storage, requires quorum, semi-complicated setup, few other things you should read into first)
Mirroring (two servers, independent storage, requires a third witness instance if you want automatic failovers, simple setup, async and sync replication, should still read into more)
VM, If I'm not mistaken, you can have V-Sphere setup so that a VM is being mirrored on another host to be spun up in case something breaks. I'm not a VM person.
Replication (snapshot subscription based) requires a bit of setup, timed replication. good if you have daily static data. not a normal scenario, good for some reporting.
log shipping not difficult to setup, you can even set in delays on the shipping if you want to make sure you have a secondary site to role back. still takes two servers.
Please correct me people if I have something wrong. I know I left a lot out here. As always, there's more than one way to skin a cat.
I personally like Mirroring. but that's just me.
.
April 15, 2014 at 1:32 pm
Nice post about clustering and I agree on the nice points of it, but I disagree about clustering everything. Even in production. I like clustering as an HA solution, but as always, it depends.
AlwaysOn and Peer to Peer Replication have the same upsides for the most part and have fewer contention points. People who love clustering and set it up everywhere have never had an entire disk array or multiple luns on a cluster go offline during business hours. At that point, failing over isn't bringing the data back online. With AO and P2PR (and really log shipping too) you at least have a separate copy of the data ready to go. Plus you can load balance across it and get some use out of those servers where they are replicated to.
Let's also look at infrastructure costs:
-if you have an AP cluster, you have resources that you have paid for that are sitting idle. You have paid for an entire duplicated server that will basically never do anything. You've doubled the cost of your server as well as the cost for upgrading it if you need to expand memory, add HBAs if you're hooked up to a SAN, etc (assuming you want performance the same on failover).
-licensing with SQL 2012 is a pain point here too for physical clusters. Since you've installed SQL Server on the second (and n+ nodes) you have to license that box, which once again will sit idle and do nothing. For VM clusters this isn't as big of a deal since all of the cores will be licensed on the host anyway.
April 15, 2014 at 1:44 pm
My expertise level in SQL is somewhere below the "DBA in diapers" (I couldn't think of a cool phrase to express my ineptness), but if the clustered servers all share one data storage, that's a single point of failure. That's not cool is it? However, if the data storage has a backup, it might be a little more cool. I don't know how hard it would be to point the server to the storage backup verses the original storage. Definitely seems like there would be down time involved.
Wallace Houston
Sunnyland Farms, Inc.
"We must endeavor to persevere."
April 15, 2014 at 1:44 pm
You could use "active/active" clustering to get use out of the other hardware, however, if you have to fail-over during a maintenance period you have to run both instances on the same node which may cause a severe performance degradation. If you were to perform the maintenance during a time when you knew the hardware could support both instances then it could be possible. All of these scenarios are definitely conjecture because every environment is different in terms of what will and will not work. So it's always going to be a just depends scenario.
April 15, 2014 at 2:58 pm
My expertise level in SQL is somewhere below the "DBA in diapers" (I couldn't think of a cool phrase to express my ineptness), but if the clustered servers all share one data storage, that's a single point of failure. That's not cool is it? However, if the data storage has a backup, it might be a little more cool. I don't know how hard it would be to point the server to the storage backup verses the original storage. Definitely seems like there would be down time involved.
Most storage has redundancy built in. SANs can have redundant arrays or even redundant sans. you gain redundancy when you are in different levels of RAID depending on your needs.... (yes raid 5 is fine... don't hate me....) you can have multiple pipes to the server so if one cable goes bad, you're still up. There's many ways to have good redundancy... it just all has a good price.
The less down time you can afford, the more server and hardware you need to buy. There are good solutions to help on price.... but there becomes a point that you just have to spend the cash or declare that the data isn't worth it and scale back your seven 9's theory for a database housing your internal share point.
You could use "active/active" clustering to get use out of the other hardware, however, if you have to fail-over during a maintenance period you have to run both instances on the same node which may cause a severe performance degradation. If you were to perform the maintenance during a time when you knew the hardware could support both instances then it could be possible. All of these scenarios are definitely conjecture because every environment is different in terms of what will and will not work. So it's always going to be a just depends scenario.
active/active doesn't truly exist as far as I've found. what you're talking about is using two servers with two active/passive instances of SQL so that both boxes are in use. and yes, one goes down, they may both go down. This is not something I'd want my customers relying on. I've worked with those systems and I hated my job. I would strongly advise against that setup unless you build up each server to handle both loads regardless.
Mirroring does all this without needing a cluster, without needing shared storage... you can still manually fail-over a mirrored instance and work on the other box. Clustering makes some things easier... one jobs to maintain for one, one set of logins and permissions, one server name to log in too.
on the active/active thing. Active/active suggest that each side can take writes and reads. The closest you can get with SQL would be Peer to Peer replication, even there you have a delay and a chance for two people updating the same record and many design issues to work through.
.
Viewing 15 posts - 16 through 30 (of 80 total)
You must be logged in to reply to this topic. Login to reply