October 7, 2013 at 10:00 am
I have had a google around how to set these up and it seems fairly simple, I am just trying to get my head around the storage side.
We know it can't be shared storage as both nodes (in a two node cluster) need to have access at the same time.
On a windows 2008 server, how would you set the storage up? Would you use a file share as the witness? And you could have one of the SQL instances taking the data load etc and the replica doing the reporting, backups etc?
On a windows 2012 Server how would it work?
October 7, 2013 at 10:09 am
and does the Quorum act as the "witness" like database mirror requires?
October 7, 2013 at 12:39 pm
Storage is just disks (or LUNs, or whatever). Nothing special about it at all. That's a big part of what makes it attractive. You don't have to have an specifics whatsoever. You need to have a disk where the data can go. That's it. Just like any other database. Now, you will have to make it so the service has access to location, but that's it.
You can have the secondary be read only for reporting. You can do backups from the secondaries, but that gets a little strange. You can't do full backups from the secondaries except for COPY_ONLY backups. But, you can do log backups from any of the secondaries or the primary and then use the logs from any of the servers to restore to a point in time (as long as you have the complete log chain).
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
October 8, 2013 at 11:25 am
SQLSteve (10/7/2013)
I have had a google around how to set these up and it seems fairly simple, I am just trying to get my head around the storage side.We know it can't be shared storage as both nodes (in a two node cluster) need to have access at the same time.
On a windows 2008 server, how would you set the storage up? Would you use a file share as the witness? And you could have one of the SQL instances taking the data load etc and the replica doing the reporting, backups etc?
On a windows 2012 Server how would it work?
I have a (4) two node AlwaysOn clusters, it takes a minute to get your head around it and I still struggle a little however your asking about disks. I set single storage on both nodes, E:\ - Apps, D:\ - Data, L:\ - Logs, T:\ - tempdb. Q:\ on the NodeA for fail over. I tried the file share witness Quorom, no dice the disk worked better for me. The secondary server in the AlwaysOn config is supose to take the load for backups and reporting. Backups, this make my head spin. It is your preference where they happen. If the DB is not used 24\7, like online banking, then do them on the primary after hours.
MCSE SQL Server 2012\2014\2016
October 9, 2013 at 5:44 am
I have a (4) two node AlwaysOn clusters, it takes a minute to get your head around it and I still struggle a little however your asking about disks. I set single storage on both nodes, E:\ - Apps, D:\ - Data, L:\ - Logs, T:\ - tempdb. Q:\ on the NodeA for fail over. I tried the file share witness Quorom, no dice the disk worked better for me. The secondary server in the AlwaysOn config is supose to take the load for backups and reporting.
This is a questionable path regarding your quorum because if NodeA goes down, your cluster will go down because you won't have node majority.
Microsoft recommends a file share witness on a 3rd server for this situation. Here is some information that they sent me when I was setting up a two node "Always On" 2012 cluster.
When using the Node and File Share Majority mode, review the following recommendations for the file share witness.
• Use a Server Message Block (SMB) share on a Windows Server 2003 or Windows Server 2008 file server.
• Make sure that the file share has a minimum of 5 MB of free space.
• Make sure that the file share is dedicated to the cluster and is not used in other ways (including storage of user or application data).
• Do not place the share on a node that is a member of this cluster or will become a member of this cluster in the future.
• You can place the share on a file server that has multiple file shares servicing different purposes. This may include multiple file share witnesses, each
one a dedicated share. You can even place the share on a clustered file server (in a different cluster), which would typically be a clustered file server
containing multiple file shares servicing different purposes.
• For a multi-site cluster, you can co-locate the external file share at one of the sites where a node or nodes are located. However, we recommend that you
configure the external share in a separate third site.
• Place the file share on a server that is a member of a domain, in the same forest as the cluster nodes.
• For the folder that the file share uses, make sure that the administrator has Full Control share and NTFS permissions.
• Do not use a file share that is part of a Distributed File System (DFS) Namespace.
Steve
October 9, 2013 at 7:12 am
It has rolled, it works either way, it's more a preference not a best practice
MCSE SQL Server 2012\2014\2016
October 9, 2013 at 7:36 am
I'm glad it's working for you. It's difficult making changes when systems are rolled into production and working.
I would add that it probably is a best practice to use a 3rd server/witness to establish majority in an Always On cluster 2-node cluster. I'm not sure how many people would argue that it's not a more solid cluster configuration. The information I posted was directly from a MS ticket.
Steve
October 9, 2013 at 10:26 am
MS does not support 3 Node Virtual machines thus mine is a 2 node...
MCSE SQL Server 2012\2014\2016
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply