#availability group

#SQLServer Read-Only Routing for Availability Groups in Always On.

Not too long ago, I had a conversation with a friend about how SQL Server Always On Availability Groups and how they could be configured for Read-Only routing. This is a typical scenario when folks want to leverage their secondary replicas (nodes) for read-only workloads.

This allows you to build an active-active cluster where one node is primary (meaning read-write) and all nodes in the AG (or…

SQL Server 2017 – Deferred Name Resolution Bug.

This issue came up recently while in the midst of an upgrade to SQL Server 2017, but it’s actually not related to the upgrade and has been around all along on previous versions. So I took a moment to write this, cause a friend told me it would make a nice blog post.

Mad credit to @Kendra_Little for the help on this.

I had a SQL Server Agent job that fails on a secondary replica because it’s being…

New Post has been published on http://www.varindersandhu.in/2014/05/27/sql-server-alwayson-availability-groups/

SQL Server - AlwaysOn Availability Groups

Introduction

Microsoft introduced the AlwaysOn Availability Groups which is a high-availability and disaster-recovery solution that provides an enterprise-level solution in SQL Server 2012.

<!-- google_ad_client = "pub-2404605494811633"; google_alternate_color = "FFFFFF"; google_ad_width = 468; google_ad_height = 60; google_ad_format = "468x60_as"; google_ad_type = "text_image"; google_ad_channel =""; google_color_border = "FFFFFF"; google_color_link = "0000FF"; google_color_bg = "FFFFFF"; google_color_text = "000000"; google_color_url = "008000"; google_ui_features = "rc:6"; //-->

Using Database Mirroring for local high availability and combining it with Log Shipping for a disaster recovery solution is popular deployment architecture prior to SQL Server 2012. With SQL Server 2012, the Database Mirroring and Log Shipping solution can be replaced with an Availability Group solution with multiple secondaries.

AlwaysOn Availability Groups – Deployment Architecture

SQL Server – AlwaysOn Availability Groups

The whitepaper http://msdn.microsoft.com/en-us/library/jj191711.aspx provides architecture details and best practices for this solution.

 Enhancement to SQL Server 2014 AlwaysOn Availability Groups

Enhanced Availability for Read-Only Replicas: With SQL Server 2014, read workloads not impacted during network failures (or primary down, or cluster quorum loss). Also secondary replicas can be used for reporting and backup.

Increased Number of Replicas: Maximum number of secondaries increased from 4 to 8 (but Max 2 sync secondaries for high availability)

Add Azure Replica Wizard: On-premises SQL Server instances can use the new Windows Azure configuration options in the AlwaysOn Availability Group wizard to create one or more asynchronous secondary replicas on Windows Azure Infrastructure as a Service (IaaS) services.

Enhanced Diagnostics: SQL Server 2014 AlwaysOn Availability Groups diagnostic and troubleshooting message display has been improved.

Comparison of Failover Cluster Instances and Availability Groups

Description Nodes within an FCI Replicas within an availability group Uses WSFC cluster Yes Yes Protection level Instance Database Storage type Shared Non-shared Storage solutions Direct attached, SAN, mount points, SMB Depends on node type Readable secondaries No Yes Applicable failover policy settings

WSFC quorum

FCI-specific

Availability group settings

WSFC quorum

Availability group settings

Failed-over resources Server, instance, and database Database only

New Post has been published on http://www.varindersandhu.in/2014/05/27/sql-server-alwayson-availability-groups/

SQL Server - AlwaysOn Availability Groups

<!-- google_ad_client = "pub-2404605494811633"; google_alternate_color = "FFFFFF"; google_ad_width = 468; google_ad_height = 60; google_ad_format = "468x60_as"; google_ad_type = "text_image"; google_ad_channel =""; google_color_border = "FFFFFF"; google_color_link = "0000FF"; google_color_bg = "FFFFFF"; google_color_text = "000000"; google_color_url = "008000"; google_ui_features = "rc:6"; //-->

Introduction

Microsoft introduced the AlwaysOn Availability Groups which is a high-availability and disaster-recovery solution that provides an enterprise-level solution in SQL Server 2012.

Using Database Mirroring for local high availability and combining it with Log Shipping for a disaster recovery solution is popular deployment architecture prior to SQL Server 2012. With SQL Server 2012, the Database Mirroring and Log Shipping solution can be replaced with an Availability Group solution with multiple secondaries.

AlwaysOn Availability Groups – Deployment Architecture

SQL Server – AlwaysOn Availability Groups

The whitepaper http://msdn.microsoft.com/en-us/library/jj191711.aspx provides architecture details and best practices for this solution.

 Enhancement to SQL Server 2014 AlwaysOn Availability Groups

Enhanced Availability for Read-Only Replicas: With SQL Server 2014, read workloads not impacted during network failures (or primary down, or cluster quorum loss). Also secondary replicas can be used for reporting and backup.

Increased Number of Replicas: Maximum number of secondaries increased from 4 to 8 (but Max 2 sync secondaries for high availability)

Add Azure Replica Wizard: On-premises SQL Server instances can use the new Windows Azure configuration options in the AlwaysOn Availability Group wizard to create one or more asynchronous secondary replicas on Windows Azure Infrastructure as a Service (IaaS) services.

Enhanced Diagnostics: SQL Server 2014 AlwaysOn Availability Groups diagnostic and troubleshooting message display has been improved.

Comparison of Failover Cluster Instances and Availability Groups

Description Nodes within an FCI Replicas within an availability group Uses WSFC cluster Yes Yes Protection level Instance Database Storage type Shared Non-shared Storage solutions Direct attached, SAN, mount points, SMB Depends on node type Readable secondaries No Yes Applicable failover policy settings

WSFC quorum

FCI-specific

Availability group settings

WSFC quorum

Availability group settings

Failed-over resources Server, instance, and database Database only