November 1, 2017 at 11:12 am
Based on the discussions that we had internally we initially decided to move with a SQL FCI for our non critical databases so that we would have hardware level redundancy. However, now the issue arises where in the project manager says that one of their azure VM's are in main campus and the other VM is in the DR site...so shared storage is not possible. Basically, as per him it would defeat the purpose of having them in separate locations on separate hardware..so he is overruling SQL FCI as an option. Now, the requirement is to have 2 SQL Servers with their own storage that we can fail over in an emergency or for maintenance.
My question at this point is why we can't have a shared storage between 2 different sites? Also is it feasible and recommended to have more than 30+ databases into AlwaysOn in case if we want to explore that route. Is there any other option if not that.
November 1, 2017 at 1:08 pm
ffarouqi - Wednesday, November 1, 2017 11:12 AMBased on the discussions that we had internally we initially decided to move with a SQL FCI for our non critical databases so that we would have hardware level redundancy. However, now the issue arises where in the project manager says that one of their azure VM's are in main campus and the other VM is in the DR site...so shared storage is not possible. Basically, as per him it would defeat the purpose of having them in separate locations on separate hardware..so he is overruling SQL FCI as an option. Now, the requirement is to have 2 SQL Servers with their own storage that we can fail over in an emergency or for maintenance.
My question at this point is why we can't have a shared storage between 2 different sites? Also is it feasible and recommended to have more than 30+ databases into AlwaysOn in case if we want to explore that route. Is there any other option if not that.
You could. It would defeat the whole purpose of having two different sites because if that shared storage burns, both sites burn with it. Don't do the shared storage thing even if you find a way to do it.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 1, 2017 at 1:15 pm
ffarouqi - Wednesday, November 1, 2017 11:12 AMMy question at this point is why we can't have a shared storage between 2 different sites?
Latency. Whichever site the storage isn't in would add the entire round trip time to all reads and writes from the storage. Unless you want your expensive server storage to be slower than an old floppy drive, that's not a good idea.
Plus, the point of multiple sites is redundancy. If there's shared storage, then whichever site has that storage is the only one that can work alone, if that site fails, the second site is useless.
Now it is possible to do SAN replication, so that storage between two data centers is identical, but it's not trivial to implement, and it'll be a solution sold by your SAN vendor.
For two independent sites, I'd be looking strongly at AGs, not clustering.
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
November 1, 2017 at 1:19 pm
Agree with Gail and Jeff
I currently have 1.5 TB of data across 36 databases in an AG. There is 1 primary and 2 secondaries, all in different physical locations.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
November 1, 2017 at 1:23 pm
GilaMonster - Wednesday, November 1, 2017 1:15 PMffarouqi - Wednesday, November 1, 2017 11:12 AMMy question at this point is why we can't have a shared storage between 2 different sites?
Latency. Whichever site the storage isn't in would add the entire round trip time to all reads and writes from the storage. Unless you want your expensive server storage to be slower than an old floppy drive, that's not a good idea.
Plus, the point of multiple sites is redundancy. If there's shared storage, then whichever site has that storage is the only one that can work alone, if that site fails, the second site is useless.Now it is possible to do SAN replication, so that storage between two data centers is identical, but it's not trivial to implement, and it'll be a solution sold by your SAN vendor.
For two independent sites, I'd be looking strongly at AGs, not clustering.
Excuse my ignorance but correct me if I am wrong. I did not understand the sentence "If there's shared storage, then whichever site has that storage is the only one that can work alone, if that site fails, the second site is useless". I am under the impression that since it is a shared storage won't it fail over to the other site and start serving the traffic instead of going down along with the first site. How that would make the second site useless. I assume that is how clustering works right?
November 1, 2017 at 1:55 pm
ffarouqi - Wednesday, November 1, 2017 1:23 PMGilaMonster - Wednesday, November 1, 2017 1:15 PMffarouqi - Wednesday, November 1, 2017 11:12 AMMy question at this point is why we can't have a shared storage between 2 different sites?
Latency. Whichever site the storage isn't in would add the entire round trip time to all reads and writes from the storage. Unless you want your expensive server storage to be slower than an old floppy drive, that's not a good idea.
Plus, the point of multiple sites is redundancy. If there's shared storage, then whichever site has that storage is the only one that can work alone, if that site fails, the second site is useless.Now it is possible to do SAN replication, so that storage between two data centers is identical, but it's not trivial to implement, and it'll be a solution sold by your SAN vendor.
For two independent sites, I'd be looking strongly at AGs, not clustering.
Excuse my ignorance but correct me if I am wrong. I did not understand the sentence "If there's shared storage, then whichever site has that storage is the only one that can work alone, if that site fails, the second site is useless". I am under the impression that since it is a shared storage won't it fail over to the other site and start serving the traffic instead of going down along with the first site. How that would make the second site useless. I assume that is how clustering works right?
You have 2 restaurants but only 1 kitchen which takes care of both restaurants. What happens if the kitchen burns down? Yes, you will have to shut down both restaurants. Same works with the shared storage scenario.
November 1, 2017 at 2:02 pm
ffarouqi - Wednesday, November 1, 2017 1:23 PMGilaMonster - Wednesday, November 1, 2017 1:15 PMffarouqi - Wednesday, November 1, 2017 11:12 AMMy question at this point is why we can't have a shared storage between 2 different sites?
Latency. Whichever site the storage isn't in would add the entire round trip time to all reads and writes from the storage. Unless you want your expensive server storage to be slower than an old floppy drive, that's not a good idea.
Plus, the point of multiple sites is redundancy. If there's shared storage, then whichever site has that storage is the only one that can work alone, if that site fails, the second site is useless.Now it is possible to do SAN replication, so that storage between two data centers is identical, but it's not trivial to implement, and it'll be a solution sold by your SAN vendor.
For two independent sites, I'd be looking strongly at AGs, not clustering.
Excuse my ignorance but correct me if I am wrong. I did not understand the sentence "If there's shared storage, then whichever site has that storage is the only one that can work alone, if that site fails, the second site is useless". I am under the impression that since it is a shared storage won't it fail over to the other site and start serving the traffic instead of going down along with the first site. How that would make the second site useless. I assume that is how clustering works right?
If you set up your laptop, and clustered it with another laptop in a different city, how can that spinning disk be moved to the different city?
Shared storage indicates that more than one server uses the SAME storage.
Assume that you have a SAN, It lives in New York with node 1. You set up Node 2 in San Francisco. The storage is still in New York. If the cluster fails over to node 2, it will have to read the data from New York.
If the building that contains the SAN is unavailable, node 2 can no longer access that. It then is useless.
There is no mechanism to transfer data in clustering.
Availability Groups have independent storage. The data lives in BOTH places.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
November 1, 2017 at 2:19 pm
Michael L John - Wednesday, November 1, 2017 2:02 PMffarouqi - Wednesday, November 1, 2017 1:23 PMGilaMonster - Wednesday, November 1, 2017 1:15 PMffarouqi - Wednesday, November 1, 2017 11:12 AMMy question at this point is why we can't have a shared storage between 2 different sites?
Latency. Whichever site the storage isn't in would add the entire round trip time to all reads and writes from the storage. Unless you want your expensive server storage to be slower than an old floppy drive, that's not a good idea.
Plus, the point of multiple sites is redundancy. If there's shared storage, then whichever site has that storage is the only one that can work alone, if that site fails, the second site is useless.Now it is possible to do SAN replication, so that storage between two data centers is identical, but it's not trivial to implement, and it'll be a solution sold by your SAN vendor.
For two independent sites, I'd be looking strongly at AGs, not clustering.
Excuse my ignorance but correct me if I am wrong. I did not understand the sentence "If there's shared storage, then whichever site has that storage is the only one that can work alone, if that site fails, the second site is useless". I am under the impression that since it is a shared storage won't it fail over to the other site and start serving the traffic instead of going down along with the first site. How that would make the second site useless. I assume that is how clustering works right?
If you set up your laptop, and clustered it with another laptop in a different city, how can that spinning disk be moved to the different city?
Shared storage indicates that more than one server uses the SAME storage.Assume that you have a SAN, It lives in New York with node 1. You set up Node 2 in San Francisco. The storage is still in New York. If the cluster fails over to node 2, it will have to read the data from New York.
If the building that contains the SAN is unavailable, node 2 can no longer access that. It then is useless.There is no mechanism to transfer data in clustering.
Availability Groups have independent storage. The data lives in BOTH places.
Why would someone keep the SAN in New York? Does not make sense to have the storage as part of these sites. How things used to work prior AlwaysOn?
November 1, 2017 at 2:23 pm
Syed Razi - Wednesday, November 1, 2017 1:55 PMffarouqi - Wednesday, November 1, 2017 1:23 PMGilaMonster - Wednesday, November 1, 2017 1:15 PMffarouqi - Wednesday, November 1, 2017 11:12 AMMy question at this point is why we can't have a shared storage between 2 different sites?
Latency. Whichever site the storage isn't in would add the entire round trip time to all reads and writes from the storage. Unless you want your expensive server storage to be slower than an old floppy drive, that's not a good idea.
Plus, the point of multiple sites is redundancy. If there's shared storage, then whichever site has that storage is the only one that can work alone, if that site fails, the second site is useless.Now it is possible to do SAN replication, so that storage between two data centers is identical, but it's not trivial to implement, and it'll be a solution sold by your SAN vendor.
For two independent sites, I'd be looking strongly at AGs, not clustering.
Excuse my ignorance but correct me if I am wrong. I did not understand the sentence "If there's shared storage, then whichever site has that storage is the only one that can work alone, if that site fails, the second site is useless". I am under the impression that since it is a shared storage won't it fail over to the other site and start serving the traffic instead of going down along with the first site. How that would make the second site useless. I assume that is how clustering works right?
You have 2 restaurants but only 1 kitchen which takes care of both restaurants. What happens if the kitchen burns down? Yes, you will have to shut down both restaurants. Same works with the shared storage scenario.
Why won't be having redundancy for the kitchen (some raid levels or storage replication) if that is a single point of failure. You are leaning towards the worst case scenario. I can assume the same for AlwayOn if both the sites burnt down at the same time. Possibilities are limitless.
November 1, 2017 at 2:41 pm
ffarouqi - Wednesday, November 1, 2017 2:19 PMMichael L John - Wednesday, November 1, 2017 2:02 PMffarouqi - Wednesday, November 1, 2017 1:23 PMGilaMonster - Wednesday, November 1, 2017 1:15 PMffarouqi - Wednesday, November 1, 2017 11:12 AMMy question at this point is why we can't have a shared storage between 2 different sites?
Latency. Whichever site the storage isn't in would add the entire round trip time to all reads and writes from the storage. Unless you want your expensive server storage to be slower than an old floppy drive, that's not a good idea.
Plus, the point of multiple sites is redundancy. If there's shared storage, then whichever site has that storage is the only one that can work alone, if that site fails, the second site is useless.Now it is possible to do SAN replication, so that storage between two data centers is identical, but it's not trivial to implement, and it'll be a solution sold by your SAN vendor.
For two independent sites, I'd be looking strongly at AGs, not clustering.
Excuse my ignorance but correct me if I am wrong. I did not understand the sentence "If there's shared storage, then whichever site has that storage is the only one that can work alone, if that site fails, the second site is useless". I am under the impression that since it is a shared storage won't it fail over to the other site and start serving the traffic instead of going down along with the first site. How that would make the second site useless. I assume that is how clustering works right?
If you set up your laptop, and clustered it with another laptop in a different city, how can that spinning disk be moved to the different city?
Shared storage indicates that more than one server uses the SAME storage.Assume that you have a SAN, It lives in New York with node 1. You set up Node 2 in San Francisco. The storage is still in New York. If the cluster fails over to node 2, it will have to read the data from New York.
If the building that contains the SAN is unavailable, node 2 can no longer access that. It then is useless.There is no mechanism to transfer data in clustering.
Availability Groups have independent storage. The data lives in BOTH places.Why would someone keep the SAN in New York? Does not make sense to have the storage as part of these sites. How things used to work prior AlwaysOn?
Ok, it's prettu clear you really have no understanding of clustering and shared storage. Can you describe the scenario you had in mind, in terms of server locations and storage locations?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
November 1, 2017 at 4:12 pm
Michael L John - Wednesday, November 1, 2017 2:41 PMffarouqi - Wednesday, November 1, 2017 2:19 PMMichael L John - Wednesday, November 1, 2017 2:02 PMffarouqi - Wednesday, November 1, 2017 1:23 PMGilaMonster - Wednesday, November 1, 2017 1:15 PMffarouqi - Wednesday, November 1, 2017 11:12 AMMy question at this point is why we can't have a shared storage between 2 different sites?
Latency. Whichever site the storage isn't in would add the entire round trip time to all reads and writes from the storage. Unless you want your expensive server storage to be slower than an old floppy drive, that's not a good idea.
Plus, the point of multiple sites is redundancy. If there's shared storage, then whichever site has that storage is the only one that can work alone, if that site fails, the second site is useless.Now it is possible to do SAN replication, so that storage between two data centers is identical, but it's not trivial to implement, and it'll be a solution sold by your SAN vendor.
For two independent sites, I'd be looking strongly at AGs, not clustering.
Excuse my ignorance but correct me if I am wrong. I did not understand the sentence "If there's shared storage, then whichever site has that storage is the only one that can work alone, if that site fails, the second site is useless". I am under the impression that since it is a shared storage won't it fail over to the other site and start serving the traffic instead of going down along with the first site. How that would make the second site useless. I assume that is how clustering works right?
If you set up your laptop, and clustered it with another laptop in a different city, how can that spinning disk be moved to the different city?
Shared storage indicates that more than one server uses the SAME storage.Assume that you have a SAN, It lives in New York with node 1. You set up Node 2 in San Francisco. The storage is still in New York. If the cluster fails over to node 2, it will have to read the data from New York.
If the building that contains the SAN is unavailable, node 2 can no longer access that. It then is useless.There is no mechanism to transfer data in clustering.
Availability Groups have independent storage. The data lives in BOTH places.Why would someone keep the SAN in New York? Does not make sense to have the storage as part of these sites. How things used to work prior AlwaysOn?
Ok, it's prettu clear you really have no understanding of clustering and shared storage. Can you describe the scenario you had in mind, in terms of server locations and storage locations?
I think you are not understanding my point or may be I am not coming across correctly. Unfortunately, it is not my lack of knowledge around clustering but more to do with your understanding. Could you please make me understand what do you mean when you say that the building that contains the SAN is unavailable. Is the SAN building going to be in New York as well for it to go down along with the server? just want to understand. Anyways, to reiterate my point we have 2 nodes/VM's in Azure. One for e.g let's say in New York and the other one in say San Francisco. Now, I need to connect the 2 using shared storage for which the data center may or may not be in the same location. Kindly, let me know if there is any other way to achieve HA/DR without using AlwaysO On.
November 1, 2017 at 6:11 pm
Okay, sounds like you are using Azure, in other words your servers are in the cloud. That also means the data is in the cloud. You should be looking at how Azure can help you with this problem. I can't as I can barely spell Azure at the moment.
Everyone else is looking at this as if your servers are on premise where shared storage would not work between geographically dispersed servers.
November 2, 2017 at 3:30 am
ffarouqi - Wednesday, November 1, 2017 11:12 AMAlso is it feasible and recommended to have more than 30+ databases into AlwaysOn in case if we want to explore that route. Is there any other option if not that.
I've seen a system with 6 AGs covering well over 500 databases (between them, that is, rather than 500 DBs each)... So, yes, it's feasible. Whether it's recommended in your circumstance depends on the exact detail of your circumstance.
Thomas Rushton
blog: https://thelonedba.wordpress.com
November 2, 2017 at 5:44 am
If you want to use shared storage in Azure between VMs then you will need to use a third party tool or use storage spaces direct (S2D) which is available in Windows 2016 Datacentre. That said S2D is only supported in a single Azure region - due to latency issues which others have mentioned.
If you want HA across multiple azure regions then you need to use AG. If you want HA between on-premise and azure (as you mention azure and a main campus) then 100% you need to use AG.
November 2, 2017 at 6:54 am
ffarouqi - Wednesday, November 1, 2017 4:12 PMMichael L John - Wednesday, November 1, 2017 2:41 PMffarouqi - Wednesday, November 1, 2017 2:19 PMMichael L John - Wednesday, November 1, 2017 2:02 PMffarouqi - Wednesday, November 1, 2017 1:23 PMGilaMonster - Wednesday, November 1, 2017 1:15 PMffarouqi - Wednesday, November 1, 2017 11:12 AMMy question at this point is why we can't have a shared storage between 2 different sites?
Latency. Whichever site the storage isn't in would add the entire round trip time to all reads and writes from the storage. Unless you want your expensive server storage to be slower than an old floppy drive, that's not a good idea.
Plus, the point of multiple sites is redundancy. If there's shared storage, then whichever site has that storage is the only one that can work alone, if that site fails, the second site is useless.Now it is possible to do SAN replication, so that storage between two data centers is identical, but it's not trivial to implement, and it'll be a solution sold by your SAN vendor.
For two independent sites, I'd be looking strongly at AGs, not clustering.
Excuse my ignorance but correct me if I am wrong. I did not understand the sentence "If there's shared storage, then whichever site has that storage is the only one that can work alone, if that site fails, the second site is useless". I am under the impression that since it is a shared storage won't it fail over to the other site and start serving the traffic instead of going down along with the first site. How that would make the second site useless. I assume that is how clustering works right?
If you set up your laptop, and clustered it with another laptop in a different city, how can that spinning disk be moved to the different city?
Shared storage indicates that more than one server uses the SAME storage.Assume that you have a SAN, It lives in New York with node 1. You set up Node 2 in San Francisco. The storage is still in New York. If the cluster fails over to node 2, it will have to read the data from New York.
If the building that contains the SAN is unavailable, node 2 can no longer access that. It then is useless.There is no mechanism to transfer data in clustering.
Availability Groups have independent storage. The data lives in BOTH places.Why would someone keep the SAN in New York? Does not make sense to have the storage as part of these sites. How things used to work prior AlwaysOn?
Ok, it's prettu clear you really have no understanding of clustering and shared storage. Can you describe the scenario you had in mind, in terms of server locations and storage locations?
I think you are not understanding my point or may be I am not coming across correctly. Unfortunately, it is not my lack of knowledge around clustering but more to do with your understanding. Could you please make me understand what do you mean when you say that the building that contains the SAN is unavailable. Is the SAN building going to be in New York as well for it to go down along with the server? just want to understand. Anyways, to reiterate my point we have 2 nodes/VM's in Azure. One for e.g let's say in New York and the other one in say San Francisco. Now, I need to connect the 2 using shared storage for which the data center may or may not be in the same location. Kindly, let me know if there is any other way to achieve HA/DR without using AlwaysO On.
In a Windows failover clustering setup with physical (or virtual hosts) the SAN storage needs to be in the same physical location. There are limitations on distance with iSCSI as well as fibre. SO, if one physical location is not available, the server in the secondary location will not be able to access the SAN.
Physical devices cannot be in two places at once. The single kitchen for two restaurants analogy is absolutely spot-on.
An Azure VM cluster is a completely different setup. You would build two machines, in an active/passive setup. Each node needs at least two data disks, and these are setup as a cluster shared volume. S2D syncs the data between the two servers. They must live in the same location, so you cannot have one server in US east, and another in US East 2.
We have never really seen much use for this setup. The value versus the cost is not there.
So, based upon your original question, the answers still stand.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply