![Edwin M Sarmiento - SQL Server HA and DR](/img/default-banner.jpg)
- 99
- 330 034
Edwin M Sarmiento - SQL Server HA and DR
Приєднався 7 січ 2012
Edwin M Sarmiento is the Managing Director of 15C, a consulting and training company that specializes in designing, implementing and supporting SQL Server infrastructures. He is a 12-year former Microsoft Data Platform MVP and Microsoft Certified Master from Ottawa, Canada (but he’s originally from the Philippines) specializing in high availability, disaster recovery and system infrastructures running on the Microsoft server technology stack. His background in Unix has taken him to the world of DevOps and Docker to containerize SQL Server. He is very passionate about technology but has interests in music, professional and organizational development, leadership and management matters when not working with databases.
If you are looking into starting a career in tech but don't know how, visit www.youtube.com/@_edwinmsarmiento
If you are looking into starting a career in tech but don't know how, visit www.youtube.com/@_edwinmsarmiento
Successfully Implement SQL Server Always On Availability Groups with Failover Clustered Instances
This is a walk-through of building a SQL Server Always on Availability Group on top of a Failover Clustered Instance to achieve both local high availability and remote disaster recovery.
This is a complex architecture. Make sure you include everyone involved in designing, implementing, and managing it during the planning phase.
And while this video is less than an hour long, nobody builds this kind or architecture in less than an hour. Proper planning and implementation takes days and involves engineers with intermediate-level skills.
NOTE: I no longer recommend this architecture due to the complexity of the implementation. However, there are so many implementations like this in customer environments that were not done properly.
Download the Cluster Preparation Check list from here
learnsqlserverhadr.com/clusterprepchecklist
This is a complex architecture. Make sure you include everyone involved in designing, implementing, and managing it during the planning phase.
And while this video is less than an hour long, nobody builds this kind or architecture in less than an hour. Proper planning and implementation takes days and involves engineers with intermediate-level skills.
NOTE: I no longer recommend this architecture due to the complexity of the implementation. However, there are so many implementations like this in customer environments that were not done properly.
Download the Cluster Preparation Check list from here
learnsqlserverhadr.com/clusterprepchecklist
Переглядів: 527
Відео
Installing a SQL Server Failover Clustered Instance (SQL Server 2022 Edition) - Part 3
Переглядів 4692 місяці тому
This is an update to the video on SQL Server failover clustering installation. We will build a traditional 2-node SQL Server 2022 failover clustered instance on Windows Server 2022 - from start to finish. Part 3 is all about installing patches and updates on a SQL Server 2022 failover clustered instance. Make sure you watch until the end. I've included the process on how to slipstream SQL Serve...
Installing a SQL Server Failover Clustered Instance (SQL Server 2022 Edition) - Part 2
Переглядів 1,1 тис.3 місяці тому
This is an update to the video on SQL Server failover clustering installation. We will build a traditional 2-node SQL Server 2022 failover clustered instance on Windows Server 2022 - from start to finish. Part 2 is all about installing a SQL Server 2022 failover clustered instance and adding nodes. Part 1 is available here: ua-cam.com/video/ams7V7CzpqE/v-deo.html Part 3 is available here: ua-ca...
Installing a SQL Server Failover Clustered Instance (SQL Server 2022 Edition) - Part 1
Переглядів 2,1 тис.3 місяці тому
This is an update to the SQL Server failover clustering installation. We will build a traditional 2-node SQL Server 2022 failover clustered instance on Windows Server 2022 - from start to finish. Part 1 is all about installing, creating, and configuring the Windows Server 2022 failover cluster. Part 2 is available here: ua-cam.com/video/HTIFAUpewWM/v-deo.html Part 3 is available here: ua-cam.co...
What SQL Server DBAs Need to Know About Docker Containers
Переглядів 3893 роки тому
What SQL Server DBAs Need to Know About Docker Containers
Getting Started with Linux for the SQL Server DBA Edwin M Sarmiento
Переглядів 4635 років тому
Getting Started with Linux for the SQL Server DBA Edwin M Sarmiento
Designing SQL Server AlwaysOn Availability Groups Topology
Переглядів 8 тис.5 років тому
Designing SQL Server AlwaysOn Availability Groups Topology
Setup and Configure Windows Server 2016 Failover Cluster Without Active Directory
Переглядів 34 тис.5 років тому
Setup and Configure Windows Server 2016 Failover Cluster Without Active Directory
The Real Secret to Successfully Upgrade SQL Server Environments
Переглядів 1,1 тис.6 років тому
The Real Secret to Successfully Upgrade SQL Server Environments
Creativity in Scarcity: Behind The Scenes of How I Created My Online Courses
Переглядів 3747 років тому
Creativity in Scarcity: Behind The Scenes of How I Created My Online Courses
The Most Important Concept In Windows Failover Clustering
Переглядів 19 тис.7 років тому
The Most Important Concept In Windows Failover Clustering
Different Types of Microsoft High Availability Technologies
Переглядів 26 тис.8 років тому
Different Types of Microsoft High Availability Technologies
Installing and Configuring SQL Server 2012/2014 Reporting Services in SharePoint-Integrated Mode
Переглядів 8 тис.8 років тому
Installing and Configuring SQL Server 2012/2014 Reporting Services in SharePoint-Integrated Mode
Unexpected SQL Server Backups Break Your Disaster Recovery Strategy
Переглядів 3528 років тому
Unexpected SQL Server Backups Break Your Disaster Recovery Strategy
Effects of a Full SQL Server Transaction Log
Переглядів 4,6 тис.8 років тому
Effects of a Full SQL Server Transaction Log
The Truth About SQL Server Backup Verification Using The GUI
Переглядів 8918 років тому
The Truth About SQL Server Backup Verification Using The GUI
Factors Affecting SQL Server Database Recovery
Переглядів 3498 років тому
Factors Affecting SQL Server Database Recovery
What Multibase Differential Backups Are and How To Deal With Them
Переглядів 2728 років тому
What Multibase Differential Backups Are and How To Deal With Them
Installing and Configuring a Multi Subnet SQL Server (2012 and higher) Failover Clustered Instance
Переглядів 16 тис.8 років тому
Installing and Configuring a Multi Subnet SQL Server (2012 and higher) Failover Clustered Instance
Proactively Identify & Deal with SQL Server Database Corruption
Переглядів 1,2 тис.9 років тому
Proactively Identify & Deal with SQL Server Database Corruption
Installing a SQL Server Failover Clustered Instance
Переглядів 104 тис.9 років тому
Installing a SQL Server Failover Clustered Instance
Build a Personal Hyper-V Lab with Windows Server 2012 R2
Переглядів 15 тис.9 років тому
Build a Personal Hyper-V Lab with Windows Server 2012 R2
SQL Server Database Recovery Techniques
Переглядів 4,9 тис.9 років тому
SQL Server Database Recovery Techniques
The Lion, The Switch and the Wardrobe
Переглядів 1809 років тому
The Lion, The Switch and the Wardrobe
BIA, RPO, RTO, SLA, etc. Acronyms That Need To Drive HA/DR Projects
Переглядів 9 тис.9 років тому
BIA, RPO, RTO, SLA, etc. Acronyms That Need To Drive HA/DR Projects
Non-Technology Aspects of High Availability and Disaster Recovery
Переглядів 1959 років тому
Non-Technology Aspects of High Availability and Disaster Recovery
which udemy course included this training pls?
Did this in 1999 Advanced Server 2000 and SQL7 clustering, BLAST from the PAST
Wolfpac with Sphinx, for those who still remember the codenames 🙂 The principles and concepts remain the same...despite all the hype of whatever is new in the industry
just in time. I needed this info and it was 2 weeks ago! Subbed now :) Need to watch more of your videos.
what happens to the tlogs if the replica is offline? does it grow forever ?
This happens 🙂 ua-cam.com/video/BSS4G087UeM/v-deo.html I used this example to demonstrate the effects of tlogs growing and getting full until it can no longer do so. The application may throw an error that doesn't explicitly tell you it's a SQL Server problem.
@@EdwinMSarmiento what can be done to reduce the size of it in this case?
@@AnandNarine Monitor and take precautionary measures to make sure the tlog files do not grow to a size that fills up the disk. That really is all there is to it.
Bro im becoming your fans , this tutor really helpful , subscribed and please do another good job again for sql server 🎉🎉🎉
hi. you mentioned providing a link to the cluster preparation checklist spreadsheet?
The link should be on the description learnsqlserverhadr.com/clusterprepchecklist
Can SQL cluster work on a workgroup environment instead of an Active Directory environment? Thank you. Btw, cannot find the link to download the checklist, thank you.
> Can SQL cluster work on a workgroup environment instead of an Active Directory environment? If you're referring to a SQL Server failover clustered instances, it's not supported. Only SQL Server Always On Availability Groups work with workgroup clusters as of SQL Server 2022. Here's a video on how to install and configure a Windows Server Failover Cluster without Active Directory ua-cam.com/video/jDvqP1372Lo/v-deo.html
@@EdwinMSarmiento Thank you very much for the reply.
Can CUs be applied by just pausing a node and draining the roles and vice versa?
I do not recommend the node drain feature for SQL Server. It wasn't designed with SQL Server in mind. It's for Hyper-V workloads
Love the incredible detail you provide. Thanks for taking the time to share this with us!
Thanks for taking the time to go through each step! Extremely helpful for me! I'm going through a two-node cluster setup.
Pls make video on always on high availability also.
Have a look at this ua-cam.com/video/DmH81mGGZwg/v-deo.html
@@EdwinMSarmiento can you make a video on how to configure the on always on high availability and it failover. What is the different between the normal HA and the always on HA, thanks.
@@jeffreyooi1971 Are you referring to this? ua-cam.com/video/A8lSl8nO1r4/v-deo.html
@@EdwinMSarmiento is it the same configuration or setting apply to SQL server 2022 too as the video is 12 years ago, thanks.
@@jeffreyooi1971 There have been a lot of configuration changes since SQL Server 2012. And these subtle changes can cause a major outage if not done properly. I'll release an updated video on installing and configuring SQL Server Always On Availability Groups
i see is a pretty old video, but i have a question.... why use dns and not edit a hosts file ? i wll have less dependency ... you depend from DNS in this way.... is still the same with 2022 ?
> why use dns and not edit a hosts file Scalability. When you have more than 2 nodes to manage, you have to think of how you can do more with less.
Found this from a post from Erik Darling. A bit late yet great discovery
This video deserves all the awards.
please release the audio format in Google Podcasts & other audio channels.
Don't stop. You have a quality cintent
Thanks
Thanks for you , Really Nice
'Promosm'
please release the audio format in below podcasts channels as it helps listening while commuting/working. thanks
Thanks for sharing 💯
- all questions are based on: a. Stand Alone Servers b. No shared storage c. All are on the same subnet (except for DR) 1. How would you do HA & DR for SQL 2019/2022 in docker containers, and have AlwaysOn AG /w Listener? 2. Can you have HA & DR on 2/3 Windows Server 2022 Core + SQL Server 2022, With AlwaysOn AG /w Listener? 3. Can you have HA with just SQL Server 2022 docker containers with AlwaysOn AG /w Listeners? 4. How do you do failover for HA & DR on SQL Server 2022 in docker with ALwaysOn AG /w Listener? Please & Thanks.
My responses below: 1) Why do you need to run Always On Availability Groups on Docker containers? 2) Yes 3) Same as #1, why do you need it to be on Docker containers? 4) Same as #1, why do you need it to be on Docker containers?
@@EdwinMSarmiento it should be the cheapest way to have HA is terms of resource. containers are easy, but unless you are using shared storage to start up a new host you will have data loss. Plus, I just wanted to know if it's possible. With AG the data should stay synced, that way you don't have to worry about the container if one goes down, just auto start a new one & sync the data.
@@fbifido2 What are your HA goals? RPO? RTO? It is possible to do what you want. But without having a clear HA goal - RPO/RTO/SLA + the apps connecting to the DB - what's the point? Start with defining the goal as clearly as you possibly can
🤣 *Promosm*
Great work. Thank you.
Excellent explanation. A teacher at work!
So true, you are spot on.
I have one question. We have 3 nodes configured, 2 of which are in the primary datacenter and 1 in the disaster recovery datacenter. All of the systems are set up with Windows 2019 and SQL 2019, and the primary datacenter's DB is configured with synchronous data transfer while the DR is configured with asynchronous data transfer. My issue is: Should we use Azure storage or where can we put the Witness server?
Check your email 🙂
Thanks
This is the hammer in the right nail. I really appreciate your inside on this subject. I hate the idea of joining Hyper-V host to a Domain. I have been looking for a solution to this. I know I will loose some Hyper-V functionality like VM Replicas and Live Migration. But that's is an acceptable lost to me because I haven't had the need for either so far after 10 years using hyper-v.
Edwin this video is SHOW, congratulations about this technical profile and experience with SQL Server FC
I still don’t understand why someone have synchronous node on DR site or different data centre having different subnet , what do you recon Edwin, why it would be a case ? What advantage or disadvantage we could have
This is typically a side effect of not being clear on the goal and not understanding the capabilities and limitations of the technology. There are more disadvantages over advantages of having a synchronous replica on a DR site. At the end of the day, they're all irrelevant unless the goal is well defined.
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/
Does SILO = Unicorn I.T? I mean all in one?
In large organizations, there are different specialized teams (silos) that deal with specific areas of the infrastructure - sysadmins/server admins take care of the server/OS, network admins take care of anything related to the network, and DBAs take care of database-specific tasks. So, when deploying a complex solution like SQL Server Always On Availability Groups, there will be multiple teams working together to deploy it in production. However, not every team knows how all the pieces of the solution fit together.
Is there any security caveat using WSFC without Domain Controller?
You need to understand why organizations implement Active Directory in the first place. They want a centralized directory service to allow connecting users with network services. Security is just one aspect of Active Directory. So, when companies decide they don't want to use Active Directory, they must have a valid reason for doing so.
Yow! deserve nyo po more subs
Thank you for this very detailed and thorough explanation.
Practical Life advice for any situation👍🏽
Brilliant, Thanks for video
Glad you liked it!
thx for the info you provide, I like your teaching/explaining method. its hard finding someone that can resonate with for these things. from a hopefully future dba.
You're very welcome
Hi
Thank you very much
Great explanation ! its was very clear
snow was hard cold
Thanks for sharing your knowledge. Please keep posting stuff regularly!! Please fix that Microphone issue.
I reached out to StreamYard and asked about it. It's more than just a mic issue as I'm using a high-end mic system.
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.
Thanks it is Wroth
I have a 2 node multi subnet sql cluster everything was installed and configured successfully but am not able to connect to sqlcluster from the other node...like if sql services is running on node1 i am unable to connect the cluster by its network name from ssms from node2 and vice versa...its only connecting on the node were the sql services are running...any idea why?
This requires more than a simple response on a comment. There are so many things going on in a multi-subnet cluster configuration to identify what exactly is preventing this
In case someone will be doing the same but without DNS server - add Node names with their IPs to hosts file (both nodes). In ethernet adapter settings add your gateway as primary DNS server. Also add DNS suffix in changing Computer Name section.
Do we need a load balancer for NLB cluster?
The Windows NLB cluster is in itself the load balancer. It serves both purposes - load balancing and high availability. It's unlike the typical network load balanced appliance where it only provides load balancing.
really nice explanation with all.
LOVE THIS VID!!!!