I rarely comment but this is excellent work. Setting up sql clustering is straightforward but managing it needs an in-depth understanding and this video answers a lot of points.
Thank you for the feedback. This is the main reason why I created my online course Windows Server Failover Clustering for the Smart SQL Server DBAs. I've struggled with the complexity of managing these systems for a long time www.LearnSQLServerHADR.com
Either you use clustered shared volumes if you are on SQL Server 2014 and higher or create another cluster for the other instances to accommodate the drive letters in the alphabet. A more important question is, "why do you have so many instances in the cluster?"
The SQL Engine - or the database engine - is the service that's responsible for the SQL Server relational database management system (RDBMS) while the SQL Server Agent is the service responsible for running the scheduled administrative tasks or jobs
Really worth watching this video, I liked the way you explain and demonstrate, highly appreciable! When can you do us a favor by preparing similar for SQL Server 2016?
You can follow along using the steps outlined in this series of articles www.mssqltips.com/sqlservertip/4769/stepbystep-installation-of-sql-server-2016-on-a-windows-server-2016-failover-cluster--part-1/
Thanks for the video Edwin. Why would you want to create the tempdb in local drives? This is because you said during failover, it will be recreated in new server. Wouldn't all queries with temp tables lose their data when the secondary server is online?
Having the tempdb in the local drive is just an option. Most people still store the tempdb on shared storage for failover clustered instance deployments.
Great work!! If I do this method, can I manage the CPU and Memory allocation? I have 20 Core in one node, and I want to license it for 12 Core only. I want to use the server only with 125GB memory from 192GB. Can I install the SQL next to the Hyper-V VM-s on same node?
I'm trying to understand what you mean by installing SQL Server next to the Hyper-V VMs on the same node. Are you saying you want to run a Hyper-V cluster and install SQL Server failover clustered instance as well? If this is the case, I won't recommend it. Note that your Hyper-V VMs and your SQL Server failover clustered instances have different service level agreements (SLAs) and you don't want one workload affecting the other. The Hyper-V VMs can benefit from LiveMigration should you have resource contention on one of the nodes but SQL Server will have to failover (stop and restart) which will cause downtime
This is an organizational decision, not a technical one. In small organizations, one person controls the failover cluster - both SQL Server and Windows. In large organizations, segregation of duties dictate that different teams manage the SQL Server failover cluster and the Windows Server Failover Cluster
Hello, I have a lab similar to yours but I have two questions. In my lab I have 3 networks, one for domain, other for iscsi and other for cluster ( i guess heartbeat). Why do u put the cluster on the same network of your public nodes? shouldn't be on the same network of the hearbeat? The second question is, if I have a shared storage and the 2 node are getting data from it, when a failure occurs, the second node will have the same database information (tables and that) or its necessary to replicate ? Thanks alot!
The cluster I built in the video has 3 networks - one for public/Active Directory domain, one for heartbeat/internal communications and one for iSCSI. Windows Server 2012 (and higher) Failover Cluster will use all of the available network to send and receive heartbeat/internal communications to and from all cluster nodes unless you specifically tell it to not allow cluster communication thru the network like the iSCSI network. Even the public/Active Directory domain network will be used to send and receive heartbeat/internal communications to and from all cluster nodes. The concept of shared storage is that every node in the cluster has access to the disks but only one node at a time can write to it. When one of the nodes fail, the other will still have access to the storage. No need to do storage level replication in this configuration.
nice video however i guess for both windows and sql cluster we need private ip and not public ip 1 public for windows clustering 1 public for sql clustering
@@Abdulhafeezkalsekar That depends on your design and architecture. And it would take more than a simple answer. Because your definition of "normal" may not be something that would meet your company's recovery objectives (RPO/RTO) and service level agreements. :-)
Really great work Edwin. I enjoy it so much. Thanks a lot. I am using windows server 2012 R2 Standard. The thing is i am unable to find the option "node and disk witness" or "node and file share witness". It only shows that current vote=1 for each node and disk witness/file share witness. There is nothing witness type "node and disk witness" or "not and file share witness". Is it because of my Windows version is Standard?
I'm not sure if this has something to do with Standard Edition. But you can try using Windows PowerShell to perform this task technet.microsoft.com/en-us/library/hh847275.aspx
I already try out Datacenter version with same result and really cannot find an option Node and File share witness. Did perform by PowerShell but the status in Failover Manager is still "Witness: File Share witness" which is very strange. Thanks
What specifically do you want to learn about active/active and/or active/passive? This video is an example of an active/passive SQL Server failover clustered instance.
SQL clustering / failover. I have query: 1) I need 4 SQL Server can access sing Data store and databases in replication mode. 4 SQL I can cluster togather (2 per site cross other 2 at other site under singe vcenter) but logically we define seperate site. I dont assigned luns but I have big 5TB Lun active/active mirroring and I am giving all VM 500GB each as D: drive partition volume from that 5TB Lun. All SQL VM have 500GB from same 5TB. so how I ensure that independent 500GB volumes each can be a part of SQL cluster and access same database if any of that server fails or crashed? any idea. If I can creat SQL_Lun1 2TB and that SQL_Lun1 give virtual RDM's volume to all SQL that will good idea but for that I have to make space and create lun, because I am asking question, I dont have more space to create luns, if I create luns I have to destroy lun and recreate space. But if any idea or technique to do volume partition to partition clustering than that helps. Any idea.
There isn't a supported way (yet) to use a VMDK file as a shared storage in a Windows Server Failover Cluster as a guest inside VMWare. You can use an iSCSI target to accomplish this goal.
that's true I can use Raw Mapping of that iSCSI target, but if I can do VMDK as failover how DFS feature works before in clusters that helps to use lots of unwanted disk space we have on already used Luns which I can't degrade it. I have to create multiple Luns for multiple SQL Databases for my environment thats headache.
Thank you Edwin for the video. actually I'm seeking your help in min 33:00. once i click next it says "volume does not belong to cluster group" however the storages that i select are shared cluster storages any idea?
+Edwin M Sarmiento appreciate your support. actually after validating my cluster and run the report it ends up with no errors and the SQL wizard says "The Volume that contains SQL Server data directory G:\MSSQL11.MSSQLSERVER\MSSQL\Data does not belong to the cluster group "
+Raed Alsafrani Have you tried putting the shared drive in maintenance mode and reformatting it prior to running the SQL Server cluster installation process?
+Edwin M Sarmiento i putted them on a maintenance mode and reformatting them and didn't work. actually i have a two nodes and my storages connected via ISCSI I don't know if ISCSI needs a different installation??
+Raed Alsafrani Can you confirm that the iSCSI storage that you are using supports shared disks and SCSI-3 persisted reservations/ What product are you using for your iSCSI storage?
Hi. If we have two named instances in a active-passive cluster, can I send one of the instances to the passive node, while the other remains on the first one?
Yes, this can be done by configuring the Preferred Owner policy on the SQL Server clustered resource, assigning one of the instances to one of the nodes. But you have to be careful with this in terms of licensing because you now need two SQL Server licenses instead of one. I just launched my latest online course Windows Server Failover Clustering (WSFC) for the Smart SQL Server DBA that covers this topic in much detail. Visit LearnSQLServerHADR.com for more information
I avoid using the term active-active because it creates the impression that SQL Server can run on multiple nodes at the same time and respond to client requests. The reality is that a SQL Server instance runs on "only one node in the failover cluster" at any given point in time. In a t2o-instance, two-node failover cluster, one instance runs on one node while the other instance runs on the other.
That is correct. Which is also why I said you need to evaluate this configuration against your existing SQL Server licensing. Because this is now technically "TWO" active-standby SQL Server failover clustered instances. For more information about how to configure the Preferred Owner policy for the SQL Server resource group, you can check out my latest online course Windows Server Failover Clustering (WSFC) for the Smart SQL Server DBA LearnSQLServerHADR.com
I rarely comment but this is excellent work. Setting up sql clustering is straightforward but managing it needs an in-depth understanding and this video answers a lot of points.
Thank you for the feedback. This is the main reason why I created my online course Windows Server Failover Clustering for the Smart SQL Server DBAs. I've struggled with the complexity of managing these systems for a long time www.LearnSQLServerHADR.com
Thanks a TON. Very useful video.
thank you. it's a very good post
Thank you!
Thanks for you , Really Nice
Nice tutorial. If you have 30 instances in a cluster, how you configure the storage? if you are using drive letters it is not enough.
Either you use clustered shared volumes if you are on SQL Server 2014 and higher or create another cluster for the other instances to accommodate the drive letters in the alphabet.
A more important question is, "why do you have so many instances in the cluster?"
I am new to SQL. may I know what is the difference between SQL agent service and the SQL Engine?
The SQL Engine - or the database engine - is the service that's responsible for the SQL Server relational database management system (RDBMS) while the SQL Server Agent is the service responsible for running the scheduled administrative tasks or jobs
Great explanation ! its was very clear
Really nice! thank you for your effort in doing this! we all appreciate it
Edwin this video is SHOW, congratulations about this technical profile and experience with SQL Server FC
do you have a Database mirroring tutorial?
Have a look at the Database Mirroring articles here: www.mssqltips.com/sql-server-tip-category/64/database-mirroring/
Really worth watching this video, I liked the way you explain and demonstrate, highly appreciable! When can you do us a favor by preparing similar for SQL Server 2016?
You can follow along using the steps outlined in this series of articles www.mssqltips.com/sqlservertip/4769/stepbystep-installation-of-sql-server-2016-on-a-windows-server-2016-failover-cluster--part-1/
Thank you sir. I used your tutorial to validate/ install/configure SQL cluster on our environment. Great job.
Thanks it is Wroth
Thank you for this very detailed and thorough explanation.
Thanks for the video Edwin. Why would you want to create the tempdb in local drives? This is because you said during failover, it will be recreated in new server. Wouldn't all queries with temp tables lose their data when the secondary server is online?
Having the tempdb in the local drive is just an option. Most people still store the tempdb on shared storage for failover clustered instance deployments.
Great work!! If I do this method, can I manage the CPU and Memory allocation? I have 20 Core in one node, and I want to license it for 12 Core only. I want to use the server only with 125GB memory from 192GB. Can I install the SQL next to the Hyper-V VM-s on same node?
I'm trying to understand what you mean by installing SQL Server next to the Hyper-V VMs on the same node. Are you saying you want to run a Hyper-V cluster and install SQL Server failover clustered instance as well? If this is the case, I won't recommend it. Note that your Hyper-V VMs and your SQL Server failover clustered instances have different service level agreements (SLAs) and you don't want one workload affecting the other. The Hyper-V VMs can benefit from LiveMigration should you have resource contention on one of the nodes but SQL Server will have to failover (stop and restart) which will cause downtime
Thank for this video i have learnt a lot from, not only this video but from your videos
Thank you so much @Edwin great post!
if I want to configure the SQL Clustering on-premises on the physical server, are these steps work for that. ?!
These steps will work for physical servers. You just need to properly configure the hardware - cluster nodes, shared storage, networking, etc.
really nice explanation with all.
Hi, excellent video tutorial but I have a question: who admin or controls the cluster? ( sql or windows)
Thanks, regards.
This is an organizational decision, not a technical one. In small organizations, one person controls the failover cluster - both SQL Server and Windows. In large organizations, segregation of duties dictate that different teams manage the SQL Server failover cluster and the Windows Server Failover Cluster
Hello, I have a lab similar to yours but I have two questions. In my lab I have 3 networks, one for domain, other for iscsi and other for cluster ( i guess heartbeat). Why do u put the cluster on the same network of your public nodes? shouldn't be on the same network of the hearbeat?
The second question is, if I have a shared storage and the 2 node are getting data from it, when a failure occurs, the second node will have the same database information (tables and that) or its necessary to replicate ?
Thanks alot!
The cluster I built in the video has 3 networks - one for public/Active Directory domain, one for heartbeat/internal communications and one for iSCSI.
Windows Server 2012 (and higher) Failover Cluster will use all of the available network to send and receive heartbeat/internal communications to and from all cluster nodes unless you specifically tell it to not allow cluster communication thru the network like the iSCSI network. Even the public/Active Directory domain network will be used to send and receive heartbeat/internal communications to and from all cluster nodes.
The concept of shared storage is that every node in the cluster has access to the disks but only one node at a time can write to it. When one of the nodes fail, the other will still have access to the storage. No need to do storage level replication in this configuration.
Great tutorial, thank you very much
Really Nice Tutorial!!! Thanks
nice video however i guess for both windows and sql cluster we need private ip and not public ip
1 public for windows clustering
1 public for sql clustering
You would need a public/production IP address for both SQL Server and Windows cluster
@@EdwinMSarmiento so in normal window and SQL cluster we will need 2 or 3 public IP. For each node
@@Abdulhafeezkalsekar That depends on your design and architecture. And it would take more than a simple answer. Because your definition of "normal" may not be something that would meet your company's recovery objectives (RPO/RTO) and service level agreements. :-)
OMG! Thank you so much! You are a life-saver!
I really appreciate this video Edwin! Thanks so much.
Glad it was helpful!
Really great work Edwin. I enjoy it so much. Thanks a lot.
I am using windows server 2012 R2 Standard. The thing is i am unable to find the option "node and disk witness" or "node and file share witness". It only shows that current vote=1 for each node and disk witness/file share witness. There is nothing witness type "node and disk witness" or "not and file share witness". Is it because of my Windows version is Standard?
I'm not sure if this has something to do with Standard Edition. But you can try using Windows PowerShell to perform this task technet.microsoft.com/en-us/library/hh847275.aspx
I already try out Datacenter version with same result and really cannot find an option Node and File share witness. Did perform by PowerShell but the status in Failover Manager is still "Witness: File Share witness" which is very strange. Thanks
Awesome VideoTutorial!!!
Hats Off to you Sir..
Great Vid tut and Very Fine Explanation.
Thanks for watching
wow thank you
Thank you so much, the presentation is easy to understand. do you have tutorial on active active and active passive nodes?
What specifically do you want to learn about active/active and/or active/passive? This video is an example of an active/passive SQL Server failover clustered instance.
Super....
Really great work Edwin, to the point and concise.
Thank you, sir, for watching.
Thanks for great sharing. Your explanation was great and detail!
Thanks for watching.
SQL clustering / failover. I have query: 1) I need 4 SQL Server can access sing Data store and databases in replication mode. 4 SQL I can cluster togather (2 per site cross other 2 at other site under singe vcenter) but logically we define seperate site. I dont assigned luns but I have big 5TB Lun active/active mirroring and I am giving all VM 500GB each as D: drive partition volume from that 5TB Lun. All SQL VM have 500GB from same 5TB. so how I ensure that independent 500GB volumes each can be a part of SQL cluster and access same database if any of that server fails or crashed? any idea. If I can creat SQL_Lun1 2TB and that SQL_Lun1 give virtual RDM's volume to all SQL that will good idea but for that I have to make space and create lun, because I am asking question, I dont have more space to create luns, if I create luns I have to destroy lun and recreate space. But if any idea or technique to do volume partition to partition clustering than that helps. Any idea.
I'm not sure what you are trying to accomplish here. What is your primary goal in this design?
my mean , I can use vmdk storage assign to VM that partition as d:\ drive in SQL Cluster? that vmdk partition size is 500GB get from 5TB Lun.
There isn't a supported way (yet) to use a VMDK file as a shared storage in a Windows Server Failover Cluster as a guest inside VMWare. You can use an iSCSI target to accomplish this goal.
that's true I can use Raw Mapping of that iSCSI target, but if I can do VMDK as failover how DFS feature works before in clusters that helps to use lots of unwanted disk space we have on already used Luns which I can't degrade it. I have to create multiple Luns for multiple SQL Databases for my environment thats headache.
Great work ! Edwin
Highly appreciated
Thank you, sir, for watching.
Very well explained. Thanks a lot for these trainings
Thanks for watching
what if I use FC instead of iSCI?
FC or iSCSI works so long as the underlying storage supports SCSI-3 Persistent Reservation
Great presentation !!
Waiting for Geo-Clusters demo ..
harsh mishra It's now available :-)
Hi Edwin, excellent video for a beginner. can I have the links to access Geo-clusters demo.Thanks
+Naveen Rachapally Here it is ua-cam.com/video/bEp0rmy1314/v-deo.html
Thank you Edwin for the video. actually I'm seeking your help in min 33:00. once i click next it says "volume does not belong to cluster group" however the storages that i select are shared cluster storages any idea?
+Raed Alsafrani What does your Failover Cluster Validation Wizard say? Did it validate the storage properly?
+Edwin M Sarmiento appreciate your support. actually after validating my cluster and run the report it ends up with no errors and the SQL wizard says "The Volume that contains SQL Server data directory G:\MSSQL11.MSSQLSERVER\MSSQL\Data does not belong to the cluster group "
+Raed Alsafrani Have you tried putting the shared drive in maintenance mode and reformatting it prior to running the SQL Server cluster installation process?
+Edwin M Sarmiento i putted them on a maintenance mode and reformatting them and didn't work. actually i have a two nodes and my storages connected via ISCSI I don't know if ISCSI needs a different installation??
+Raed Alsafrani Can you confirm that the iSCSI storage that you are using supports shared disks and SCSI-3 persisted reservations/ What product are you using for your iSCSI storage?
thank you. ure my hero
Thank you, sir, for watching
Excellent Presentation
Thank you, sir, for watching.
Thank you Edwin. Exellent work. I have learned so much.
کلکپ0ککککککپ
پپپلپپہککک0
اپکاکککپپہلےکیککییپ0ککییلکی
کککیکیکیکیککک0ککیہکیکیککککککککپ
Indeed gr8 job
Thanks for watching
Great tutorial!
Thanks for watching
Hi. If we have two named instances in a active-passive cluster, can I send one of the instances to the passive node, while the other remains on the first one?
Yes, this can be done by configuring the Preferred Owner policy on the SQL Server clustered resource, assigning one of the instances to one of the nodes. But you have to be careful with this in terms of licensing because you now need two SQL Server licenses instead of one.
I just launched my latest online course Windows Server Failover Clustering (WSFC) for the Smart SQL Server DBA that covers this topic in much detail. Visit LearnSQLServerHADR.com for more information
But in that case the cluster is no longer an ative-passive. He becomes an ative-ative cluster, right?
Thanks for the reply.
I avoid using the term active-active because it creates the impression that SQL Server can run on multiple nodes at the same time and respond to client requests. The reality is that a SQL Server instance runs on "only one node in the failover cluster" at any given point in time. In a t2o-instance, two-node failover cluster, one instance runs on one node while the other instance runs on the other.
So, in my scenario it continues to be a cluster ative-passive, even with each node running diferent instances.
Thanks for the explanation.
That is correct. Which is also why I said you need to evaluate this configuration against your existing SQL Server licensing. Because this is now technically "TWO" active-standby SQL Server failover clustered instances.
For more information about how to configure the Preferred Owner policy for the SQL Server resource group, you can check out my latest online course Windows Server Failover Clustering (WSFC) for the Smart SQL Server DBA LearnSQLServerHADR.com