1 / 24

Alwayson Availability Groups Krishna Deepak Founder - KDSSG

Alwayson Availability Groups Krishna Deepak Founder - KDSSG. Who Am I…. I am Krishna Deepak with 13+ years of experience in SQL Server Data Platform. Founder of K nowledged D edicated S QL S erver G roup (KDSSG) a SQL Server

bcruz
Download Presentation

Alwayson Availability Groups Krishna Deepak Founder - KDSSG

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Alwayson Availability Groups Krishna Deepak Founder - KDSSG

  2. Who Am I… • I am Krishna Deepak with 13+ years of experience in SQL Server Data Platform. • Founder of Knowledged Dedicated SQL Server Group (KDSSG) a SQL Server • Community. And also Principal Speaker in all KDSSG Community Events. • My DBA passion originated with Oracle 8/8i and in parallel explored SQL Server 2000, ever since have been dedicated to SQL Server with versions (2000, 2005, 2008, 2008R2, 2012, 2014 and dog feeding 2016). • Major skillset areas are HA/DR Designing and Implementations, Advanced Backup and Recovery, Upgradations & Migrations, Performance Tuning and Optimization, MSBI Stack. • Additionally I work on Microsoft Windows, PowerShell and Virtualization. I am Microsoft Certified Technology Specialist, MCITP and ITIL V3 Certified. Reachable at: https://www.linkedin.com/pub/krishna-deepak/64/55/b6a https://www.facebook.com/krishna.deepak.946 kd@kdssg.com Twitter: #kd_kdssg

  3. Focus Test

  4. Count number of F’s… Finally AlwaysOn evolved as finished product after years of research from Microsoft combined with experience of many years.

  5. Count number of F’s… Finally AlwaysOn evolved as finished product after years of research from Microsoft combined with experience of many years.

  6. Count number of F’s… Finally AlwaysOn evolved as finished product after years of research from Microsoft combined with experience of many years.

  7. Agenda • Alwayson is not? • Overall HA/DR Technologies Available • HA/DR solutions from other vendors • Best DR solution from Microsoft (Alwayson) • Availability Groups • Architecture • Internals • Flexible Failover Policy • Yes/No/What Happens Questions • Summary

  8. AlwaysOn is Not? • AlwaysOn is NOT Availability Groups • AlwaysOn = ( Failover Clustering , Availability Groups) • Availability Groups is NOT EQUAL to DB Mirroring

  9. AlwaysOn Is? • AlwaysOn Failover Clustering (FCI) • AlwaysOn Availability Groups

  10. Overall HA/DR Technologies You Know Clustering (Single Site WFSC) Clustering (Multi Site WFSC) Log Shipping Log Shipping Delayed Restore DB Mirroring Replication SAN Level Block Replication

  11. Overall HA/DR Technologies You Know

  12. Overall HA/DR Technologies You Know

  13. HA/DR Configuration DR HA Backups

  14. Architecture

  15. Internals As you can see the FailureConditionLevel is 3 which is in the middle of the available levels 1-5 for non-shared disk deployments (please note that the failure condition levels for shared disk deployments range from 0-5). How failure detection is done: It got changed quite a bit compared to earlier SQL Server releases. As with the shared cluster scenario (FCI), in the non-shared cluster scenario, the Windows resource DLL is the one instance ultimately triggering failovers based on the health of the AlwaysOn configuration. The Windows side resource DLL is complemented by the SQL Server side Resource DLL which contains all the details on the health checks to be executed and on deciding on whether to initiate a failover based on the test results it gets when checking the health of the principal replica.

  16. Internals The SQL Server resource DLL and Windows Side resource DLL are communicating over a set of interfaces. On the SQL Server side, we radically changed the tests we evaluate health of SQL Server primary replicas. Here is roughly how it works: The SQL Server resource DLL is establishing a lasting connection to SQL Server It calls a stored procedure sp_server_diagnostics with a repeat interval The resource DLL now expects the result set being sent in the interval set. Based on the result sets hitting time out thresholds and the issues reported in the result sets of the sp_server_diagnostics procedure and the FailureConditionLevel, the resource DLL now decides whether the primary replica is healthy or not

  17. Internals 5 – Failover/restart on any qualified failure conditions Query Processing errors 4 – Failover or restart on moderate SQL Server errors Resource errors 3 – Failover or restart on critical SQL Server errors System errors 2 – Failover or restart on server unresponsive No response from sp_server_diagnostics 1 – Failover or restart on server down Service is down 0 – No Automatic Failover or restart

  18. Questions? No Questions? Yes Questions? What happens Questions?

  19. No Questions? Can we have AG created across multiple domains? No Can we have AG created against multiple clusters in the same domain? No Can we have 2 SQL Instances (Replicas) running on the same node, being part of the same AG? No Can we have AG created on 3rd party cluster solutions? No Is there a particular quorum model to create the cluster for AG? No, Use the best quorum to keep your cluster online. Can we replicate system databases in AGs? No

  20. No Questions? Do we support Cross database transactions in an AG? No, it is not possible Can we have Availability Groups for SQL 2012 32 bit running Windows 64 bit platforms? No, it is not supported Do we require some kind of shared storage to create an AG? No, its optional however Can we combine AG and DB Mirroring? No, They do not interoperate

  21. Yes Questions? Can we have AGs created across VMs on same physical host? Yes of course, and thats how my demo works Can we use same service accounts for all replicas? Yes if you want Kerberos Authentication to the listener Do we need to use the same disk structure across all the availability replicas? Not needed but recommended because of Add file operations. Can we combine AG and Replication? Yes, but Distributor is not supported

  22. Yes Questions? Can we combine AG and Log Shipping? Yes it shouldn’t stop Can we create a DB snapshot on a secondary replica? Yes Can we create a Cname\Alias for the AG listener? Yes and also you can manually create an SPN for it to force using Kerberos

  23. What Happens Questions? What happens when 2 different secondary replicas backup the transaction log at the same time? Only 1 replica can backup the log at a time, the other replica will be blocked waiting for backup lock resource What happens when 2 different replicas run CHECKDB at the same time? No issues What is the connection string item which distinguishes an application that it is intended for read only operations? Does ReadOnly routing do load balancing? No, It doesn’t. it only does routing . You need to configure your own solution for load balancing.

  24. Thank you for attending Tech Unite

More Related