September 28, 2011 at 8:40 am
Hi all. Quick question -
In a clustered environment that is not using a SAN (both nodes are using local storage), is it safe to assume that I can write to the passive DB just as easily as I can to the active DB?
Thanks.
September 28, 2011 at 8:45 am
So this is one of the special configurations using the Quorum Mode: "Node and File Share Majority"? I assume you have the "shared" disks mirrored.
Like a regular cluster you shouldn't be writing to the passive node, as a matter of fact SQL shouldn't be running on that node.
Maybe you should explain what you want to do.
CEWII
September 28, 2011 at 8:45 am
No. In a cluster, the inactive node is not running, that's why it's inactive.
How are you doing a cluster with local storage? If it's not a SAN, there can't be SAN replication, so what??
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
September 28, 2011 at 9:01 am
GilaMonster (9/28/2011)
No. In a cluster, the inactive node is not running, that's why it's inactive.How are you doing a cluster with local storage? If it's not a SAN, there can't be SAN replication, so what??
I've seen some software that basically presents local storage as shared storage and handles the sync to the other machine. This config is difficult to setup due to everything needing to be on the same subnet. I have never built one myself and would think of it this way, less than 1% of all SQL installs are clustered, and of that 1% probably less than a 0.1% of those are in this config. So minute fractions of fractions.
One of the products is: SteelEye DataKeeper for Windows Cluster Edition[/url]
CEWII
September 28, 2011 at 9:06 am
Hmmm... ok, let me phrase it this way.
If our primary concern is having the ability to failover to a different database server in the event of a problem, but we can guarantee that the "passive" database will always be in-sync with the active database (and, so, don't need any form of transaction replication within SQL), what's the best solution for implementing that?
We've got a web content management system that pushes all changes to all databases (primary and backup) from a single location. It's smart enough to roll back any updates that don't get written to ALL databases. All other access to the DBs is read only. So, we've got a way to make sure the primary and secondary databases are the same. I'm just trying to find the best way to ensure constant availability via automatic failover in the event that the primary DB server goes offline.
September 28, 2011 at 9:34 am
are you attempting to implement a Geo cluster?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 28, 2011 at 9:37 am
sickpup (9/28/2011)
Hmmm... ok, let me phrase it this way.If our primary concern is having the ability to failover to a different database server in the event of a problem, but we can guarantee that the "passive" database will always be in-sync with the active database (and, so, don't need any form of transaction replication within SQL), what's the best solution for implementing that?
We've got a web content management system that pushes all changes to all databases (primary and backup) from a single location. It's smart enough to roll back any updates that don't get written to ALL databases. All other access to the DBs is read only. So, we've got a way to make sure the primary and secondary databases are the same. I'm just trying to find the best way to ensure constant availability via automatic failover in the event that the primary DB server goes offline.
Is normal failover clustering insufficient for this? Are you trying to do a multi-site cluster?
CEWII
September 28, 2011 at 9:52 am
Clustering works with shared storage. When the passive node picks up, it picks up the storage that was being used by the active node. Some SAN technologies or geo-clusters play around with replicating at the storage level, but from the SQL point of view, it's the same storage.
You might look at mirroring or log shipping instead if you are trying to keep databases in synch. Mirroring, the synchronous kind, can ensure all commits on the primary databases get sent to the secondary. no guarantee that multiple databases will be in complete sync for now. Denali will change that. (SQL 11)
September 28, 2011 at 11:04 am
Elliott Whitlow (9/28/2011)
sickpup (9/28/2011)
Hmmm... ok, let me phrase it this way.If our primary concern is having the ability to failover to a different database server in the event of a problem, but we can guarantee that the "passive" database will always be in-sync with the active database (and, so, don't need any form of transaction replication within SQL), what's the best solution for implementing that?
We've got a web content management system that pushes all changes to all databases (primary and backup) from a single location. It's smart enough to roll back any updates that don't get written to ALL databases. All other access to the DBs is read only. So, we've got a way to make sure the primary and secondary databases are the same. I'm just trying to find the best way to ensure constant availability via automatic failover in the event that the primary DB server goes offline.
Is normal failover clustering insufficient for this? Are you trying to do a multi-site cluster?
CEWII
By normal failover clustering, do you mean at the o/s level? Or is there something else in SQL, besides the "SAN-required" clustering that I'm missing?
September 28, 2011 at 11:28 am
sickpup (9/28/2011)
By normal failover clustering, do you mean at the o/s level? Or is there something else in SQL, besides the "SAN-required" clustering that I'm missing?
Yes, I mean SAN-required clustering.
There are other ways to accomplish high availability such as database mirroring with a witness that do not require either additional software (like SteelEye for Windows - Cluster Edition) or "shared" storage. Depending on how heavily this machine will be hit and whether it is physical or virtual you might be able to consider using iSCSI to provide shared storage.
But SQL clustering is pretty easy to setup provided you have shared storage.
CEWII
September 28, 2011 at 12:40 pm
Elliott Whitlow (9/28/2011)
sickpup (9/28/2011)
By normal failover clustering, do you mean at the o/s level? Or is there something else in SQL, besides the "SAN-required" clustering that I'm missing?Yes, I mean SAN-required clustering.
There are other ways to accomplish high availability such as database mirroring with a witness that do not require either additional software (like SteelEye for Windows - Cluster Edition) or "shared" storage. Depending on how heavily this machine will be hit and whether it is physical or virtual you might be able to consider using iSCSI to provide shared storage.
But SQL clustering is pretty easy to setup provided you have shared storage.
CEWII
Thanks for the info, all.
I guess a simpler way of putting this is - how can I get reliable failover functionality within SQL Server without having to share the storage? I just want a way for SQL to know that something is hosed up and to automatically switch to the new box, as gracefully as possible.
September 28, 2011 at 12:45 pm
Database mirroring comes to mind. The database on the second server is still unavailable (because it's in the restoring state), the mirroring keeps the two in sync.
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
September 28, 2011 at 1:13 pm
GilaMonster (9/28/2011)
Database mirroring comes to mind. The database on the second server is still unavailable (because it's in the restoring state), the mirroring keeps the two in sync.
Yeah, that was my first thought, too. But, after I learned more about the project, I realized we need to have the ability to write to the passive DB. The content management software is, essentially, performing the mirroring for us.
If we absolutely have to, we can maybe disable the content management updates to the passive DB, and just use mirroring within SQL. But, I want to see if there are any other options.
September 28, 2011 at 1:17 pm
sickpup (9/28/2011)
GilaMonster (9/28/2011)
Database mirroring comes to mind. The database on the second server is still unavailable (because it's in the restoring state), the mirroring keeps the two in sync.Yeah, that was my first thought, too. But, after I learned more about the project, I realized we need to have the ability to write to the passive DB. The content management software is, essentially, performing the mirroring for us.
If we absolutely have to, we can maybe disable the content management updates to the passive DB, and just use mirroring within SQL. But, I want to see if there are any other options.
I'd say mirroring is probably the right answer as well. Let SQL under the hood manage sync and use a witness for the auto-failover..
CEWII
September 28, 2011 at 1:35 pm
sickpup (9/28/2011)
GilaMonster (9/28/2011)
Database mirroring comes to mind. The database on the second server is still unavailable (because it's in the restoring state), the mirroring keeps the two in sync.Yeah, that was my first thought, too. But, after I learned more about the project, I realized we need to have the ability to write to the passive DB. The content management software is, essentially, performing the mirroring for us.
You need to either use a SQL technology to do the failover and let it manage the sync or, if the application manages the sync then the failover has to be something outside of SQL Server (app or DNS redirects, or something like 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
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply