Interview Questions

SQL Server 2019 Always On Availability Groups new features – Whats new

SQL Server 2019 Always On Availability Groups

If you are a DBA or a Database architect, our article on SQL Server 2019 Always On Availability Groups should be of interest to you. SQL Server availability groups were first introduced with SQL Server 2012 enterprise edition. From thereon, the features for DR (disaster recovery) and HA (high-availability) have evolved to address various pain points.

While there were steady incremental updates for all the SQL Server versions released after 2012, the most significant feature update, in my opinion, was the introduction of “Basic availability groups.” It enabled customers with SQL Server Standard edition to use availability groups feature with some manageable restrictions. It also introduced the ability to deploy availability groups in a workgroup.

SQL Server 2017 introduced Linux & docker support. This enabled users to enable ‘availability groups’ on Linux based SQL Server deployments. It helps enterprises to diversify their deployment infrastructure investments. So, what’s changed, evolved or new in SQL Server 2019 availability groups? Read to find out more.

SQL Server 2019 Always On Availability Groups

All editions of SQL Server have evolved over time and SQL Server 2019 is no exception. The 2019 edition is based on the following high-level features:

Integrating all data Managing all data Analyzing all data
Combine data from many sources without moving or replicating it – eliminate ETL, access current data, maintain security SQL Server can now read and write to HDFS Perform analytics over structured and unstructured data in real time
Scale-out data marts cache data to boost performance Store high volume data in a data lake and analyze it easily using either T-SQL or Spark Easily feed integrated data from many sources to your model training
Management services, admin portal, and integrated security make it all easy to manage Ingest and prepare data and then train, store, and operationalize your models all in one system

As you can see from the above table, SQL Server now supports HDFS, scale-out of data marts and talk to external systems like Teradata through Open database connectivity. This brings in additional complexity from the administrators perspective. However, supporting open frameworks like Hadoop & unstructured data is a necessary step to the future. So, with this in mind, let us look at some of the high-level features of availability groups in SQL Server 2019.

  • Up to 5 synchronous commits to replicas in a SQL server 2019 availability group. This means that you can have a 4 + 1 setup, where there are 1 primary server and 4 synchronous commit replicas. Since all databases are in synchronous availability group that forms a quorum, you can safely setup automatic failover with zero-data-loss.
  • Availability Groups now supports Kubernetes as an orchestration layer in the 2019 edition
  • You can now connect to any available replicas and would be automatically redirected to available replicas based on a read or write request. This would obviously depend on the configuration that you need to set up at the time of creation.
SQL Server 2017 – Availability Groups Features SQL Server 2019 Availability Groups Features
  • Cross-database transactions support across availability groups
  • Cluster less support
  • Minimum Replica Commit Availability Groups setting
  • Windows-Linux cross-OS migrations
  • AG support for Kubernetes
  • Five Sync Replica support (4+1)
  • Automatic Read/Write routing to Primary

Conclusion

That is all we have for now for the new features of SQL Server 2019 Always On Availability Groups. Do let us know your thoughts and suggestions in the comments section below. Thanks for visiting!

Disclaimer: The Questions and Answers provided on https://www.gigxp.com are for general information purposes only. We make no representations or warranties of any kind, express or implied, about the completeness, accuracy, reliability, suitability or availability with respect to the website or the information, products, services, or related graphics contained on the website for any purpose.

What's your reaction?

Excited
0
Happy
0
In Love
0
Not Sure
0
Silly
0

You may also like

Comments are closed.