SQL Server 2016 AlwaysOn Availablility Enhancements

In this Article I have listed out SQL Server 2016 AlwaysOn Availability Enhancements.

SQL Server 2016 [CTP 3.2] SQL Server 2016 CTP Standard Edition now supports AlwaysOn Basic Availability Groups.

AlwaysOn Basic Availability Groups replaces the deprecated Database Mirroring feature for SQL Server 2016 Standard Edition. 

AlwaysOn Basic Availability Groups provide a high availability solution for SQL Server 2016 Standard Edition or higher.

To create a basic availability group, use the CREATE AVAILABILITY GROUP transact-SQL command and specify the WITH BASIC option (the default is ADVANCED). 

A basic availability group supports a fail-over environment for a single database. It is created and managed much like traditional (advanced) AlwaysOn Availability Groups (SQL Server) with Enterprise Edition. 

Basic availability groups enable a primary database to maintain a single replica. This replica can use either synchronous-commit mode or asynchronous-commit mode

Limitations of Basic availability groups

• Limit of two replicas (primary and secondary).

• No read access on secondary replica.

• No backups on secondary replica.

• No support for replicas hosted on servers running a version of SQL Server prior to SQL Server 2016 Community Technology Preview 3 (CTP3).

• No support for adding or removing a replica to an existing basic availability group.

• Support for one availability database.

• Basic availability groups cannot be upgraded to advanced availability groups. The group must be dropped and re-added to a group that contains servers running only SQL Server 2016 Enterprise Edition. 

Load-balancing of read-intent connection requests is now supported across a set of read-only replicas. The previous behavior always directed connections to the first available read-only replica in the routing list. 

The number of replicas that support automatic failover has been increased from two to three.

Group Managed Service Accounts are now supported for AlwaysOn Failover Clusters. 

AlwaysOn Availability Groups supports distributed transactions and the DTC on Windows Server 2016.

You can now configure AlwaysOn Availability Groups to failover when a database goes offline. This change requires the setting the DB_FAILOVER option to ON

SQL Server [CTP 2.2] AlwaysOn now supports encrypted databases. The Availability Group wizards now prompt you for a password for any databases that contain a database master key when you create a new Availability Group or when you add databases or add replicas to an existing Availability Group.

Reference: BOL

Regards,

Ganapathi varma

Senior SQL Engineer, MCP

Linkedin

Email: Gana20m@gmail.com

What's New in SQL Server 2016, Community Technology Preview 3 (CTP 3.1)

What's New in SQL Server 2016

Benefits of SQL Server 2016:

• Enhanced in-memory performance provides up to 30x faster transactions, more than 100x faster queries than disk-based relational databases and real-time operational analytics.
• New Always Encrypted technology helps protect your data at rest and in motion, on-premises and in the cloud, with master keys sitting with the application, without application changes.
• Stretch Database technology keeps more of your customer’s historical data at your fingertips by transparently stretching your warm and cold OLTP data to Microsoft Azure in a secure manner without application changes.
• Built-in advanced analytics provide the scalability and performance benefits of building and running your advanced analytics algorithms directly in the core SQL Server transactional database.
• Business insights through rich visualizations on mobile devices with native apps for Windows, iOS and Android.
• Simplify management of relational and non-relational data by querying both with T-SQL using PolyBase.
• Faster hybrid backups, high availability and disaster recovery scenarios to back up and restore your on-premises databases to Microsoft Azure and place your SQL Server AlwaysOn secondaries in Azure.

Microsoft reference: [Click Here]



For more updates:
Subscribe for Blog posts [Click Here]
Follow FB Page [Click Here]
Follow FB Group [Click Here]
For Suggestion & Feedback mail us at gana20m@gmail.com

Announcing SQL Server Management Studio - November 2015 Release

For Summary On Improvements In the Latest Release
Please go through the below link.

Summary On Improvements In SSMS Nov 2015


To Download SSMS November 2015 Preview

Download Link

Database Mirroring Error 1418

Database Mirroring Error 1418

Hello Everyone.

This post will be helpful in resolving the error 1418 that we may come across when tried to configure Database Mirroring between Primary and Secondary SQL Servers.

Error:

The server network address “%.*ls” cannot be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational.

To resolve this, we will have to restart the endpoints on the Principal (Primary) Server and proceed with enabling Database Mirroring again.

Please execute the below to check the Mirroring Endpoint Name and status of endpoints.

SELECT * FROM sys.endpoints

WHERE type = 4

                                        

Recycle the endpoint on the database mirror. To do this, follow these steps:

On the principal database, execute the following SQL script to stop the endpoint:

ALTER ENDPOINT <Endpoint Name> STATE=STOPPED

Execute the following SQL script to restart the endpoint:

ALTER ENDPOINT <Endpoint Name> STATE=STARTED

Note: If communication between the endpoints does not restart after you execute the scripts, execute the scripts on the database mirror. 

However, the database may enter a "Suspended" state after you do this. If this issue occurs, execute the following SQL script:

ALTER DATABASE <Database Name> SET PARTNER RESUME

        

Cumulative update package 9 for SQL Server 2012 SP2

Microsoft just announced Cumulative update package 9 for SQL Server 2012 SP2

Microsoft reference: [Click Here]

For more updates:
Subscribe for Blog posts [Click Here]
Follow FB Page [Click Here]
Follow FB Group [Click Here]
For Suggestion & Feedback mail us at gana20m@gmail.com

Add Dependencies to a SQL Server Resource in Windows 2003

Read My Article "Here" 

For more updates:
Subscribe for Blog posts [Click Here]
Follow FB Page [Click Here]
Follow FB Group [Click Here]
For Suggestion & Feedback mail us at gana20m@gmail.com

How to Add a Drive as a Clustered Physical Disk Resource in Windows

Read My Article "Here" 

For more updates:
Subscribe for Blog posts [Click Here]
Follow FB Page [Click Here]
Follow FB Group [Click Here]
For Suggestion & Feedback mail us at gana20m@gmail.com