Monthly Archives: August 2016

AlwaysOn 2016 – New and Improved

–by Ginger Keys

 

AlwaysOn Availability Groups made its debut in SQL Server 2012 as a new feature that enhanced the existing technologies of database mirroring and failover clustering to provide an option for high availability and disaster recovery. AlwaysOn gives you the ability to scale out several secondary Servers (replicas) for high availability or disaster recovery purposes. The secondaries are usable for read-only operations (like reporting) and maintenance tasks (like backups), and AlwaysOn provides for zero data loss protection and automatic page repair.

Since the AlwaysOn 2012 release, there have been several enhancements that have improved manageability, scalability, and availability. This article will discuss some of these improvements and why they’re important.

Some of the enhancements to the AlwaysOn feature in SQL Server 2016 are only available if your SQL server is running on the Windows Server 2016 platform. However, if you are still on Windows Server 2012 R2 there are still many robust improvements that are available.

Some of the new features and enhancements for AlwaysOn 2016 are:

  • More failover targets
  • Better log transport performance
  • Load balancing for readable secondaries
  • DTC support
  • Database-level health monitoring
  • Group Managed Service Account (gMSA) support
  • Basic Availability Groups
  • BI enhancements
  • Non-domain replicas
  • Encrypted database support
  • SSIS catalog support

 More Failover Targets

In AlwaysOn 2012 and 2014, you were allowed a maximum of two replicas to designate for automatic failover. AlwaysOn 2016 allows three replicas for automatic failover. You must have synchronous data replication and automatic failover set between the primary and the secondaries.

Automatic failover is generally used to support high availability, and because of synchronous data flow there is near zero data loss in the event of failover.

Blog_20160818_1

Better Log Transport Performance

The increased use of solid-state disks (SSDs) has provided users with high-speed hardware, enabling very fast throughput. This however, can be overwhelming to a system trying to write transactions to a secondary server. Because of this, Microsoft has revamped the data synchronization process for AlwaysOn, streamlining the pipeline so that there is better throughput and also less stress on the CPU. Bottlenecks are most likely to occur during the Log Capture and Redo steps. Previously the log-capture and the redo steps used a single thread to process the logs, but now these steps use multiple threads and run in parallel, which greatly improves performance.

The steps of data replication are illustrated below.

Transaction Occurs –> Log Flush –> Log Capture –> Send –> Log Received –> Log Cached –> Log Hardened –> Acknowledgement Sent –> Redo

Blog_20160818_2

Load Balancing for Readable Secondaries

One of the great features of using AlwaysOn is the ability to use the secondary replicas for read only operations. Prior to AlwaysOn SQL2016, the listener would direct any read-only requests to the first available replica, even though you might have several secondary replicas available, and you might have preferred the read operations go to secondary #3 or #4, instead of #2. Now in SQL2016 the list of readable secondaries are presented to the listener in a round-robin fashion. By executing TSQL statement similar to the following, the workload will adjust in the event of failover:

— designate server with read-only access in Secondary status

ALTER AVAILABILITY GROUP AGSQL2016

MODIFY REPLICA ON N’SQLSRVTST1′

WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));

 

ALTER AVAILABILITY GROUP AGSQL2016

MODIFY REPLICA ON N’SQLSRVTST2′

WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));

 

ALTER AVAILABILITY GROUP AGSQL2016

MODIFY REPLICA ON N’SQLSRVTST3′

WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));

 

— provide read-only routing URL

ALTER AVAILABILITY GROUP AGSQL2016

MODIFY REPLICA ON N’SQLSRVTST1′

WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N’TCP://SQLSRVTST1.domain.com:1433′));

 

ALTER AVAILABILITY GROUP AGSQL2016

MODIFY REPLICA ON N’SQLSRVTST2

WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N’TCP://SQLSRVTST2.domain.com:1433′));

 

ALTER AVAILABILITY GROUP AGSQL2016

MODIFY REPLICA ON N’SQLSRVTST3′

WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N’TCP://SQLSRVTST3.domain.com:1433′));

 

— designate priority of read-only routing lists for each server in primary status

ALTER AVAILABILITY GROUP AGSQL2016

MODIFY REPLICA ON N’SQLSRVTST1′

WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(‘SQLSRVTST3’,‘SQLSRVTST2’,‘SQLSRVTST1’)));

 

ALTER AVAILABILITY GROUP AGSQL2016

MODIFY REPLICA ON N’SQLSRVTST2′

WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(‘SQLSRVTST1’,‘SQLSRVTST3’,‘SQLSRVTST2’)));

GO

 

ALTER AVAILABILITY GROUP AGSQL2016

MODIFY REPLICA ON N’SQLSRVTST3′

WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(‘SQLSRVTST2’,‘SQLSRVTST1’,‘SQLSRVTST3’)));

GO

 

In addition to configuring your availability group routing lists, you must also ensure that the client’s application connection string use an application intent of read-only in the connection string when connecting to the AG listener. If this is not set in the client application connection string, the connection will automatically be directed to the primary replica. Following is an example of a connection string:

Server=tcp:AGListener,1433; Database=AdventureWorks;IntegratedSecurity=SSPI; ApplicationIntent=ReadOnly

Also it’s best not to mix synchronous and asynchronous replicas in the same load balance group.

DTC Support

*Only available with Windows Server 2016 or Windows Server 2012 R2 with update KB3090973

Distributed Transaction Coordinator (DTC) is necessary if your client application needs to perform transactions across multiple instances.   DTC is part of the operating system, and ensures consistency when your database engine makes multi-server transactions. Applications can use DTC when they connect to the database engine, or can be started through TSQL by using the BEGIN DISTRIBUTED TRANSACTION command.

USE AdventureWorks2012;

GO 

BEGIN DISTRIBUTED TRANSACTION;

— your tsql statement here

DELETE FROM AdventureWorks2012.HumanResources.JobCandidate

WHERE JobCandidateID = 13;

GO 

COMMIT TRANSACTION;

GO

 

Not only can your application perform transactions between multiple SQL Server instances, but also between other DTC compliant servers such as WebSphere or Oracle.

DTC is not supported in AlwaysOn 2014 and earlier. You cannot add DTC support to an already existing AlwaysOn availability group. For complete information click here https://msdn.microsoft.com/en-us/library/mt748186.aspx .

To implement this in AlwaysOn 2016, Availability Groups must be created with the CREATE AVAILABILITY GROUP command and the WITH DTC_SUPPORT = PER_DB clause.

CREATE AVAILABILITY GROUP AGSQL2016

WITH (DTC_SUPPORT = PER_DB)

FOR DATABASE [Database1, Database2, Database3]

REPLICA ON

‘SQLSRVTST1’ WITH — substitute node name

(

ENDPOINT_URL = ‘TCP://SQLSRVTST1.<domain>:7022’,

AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,

FAILOVER_MODE = AUTOMATIC

),

‘SQLSRVTST2’ WITH — substitute node name

(

ENDPOINT_URL = ‘TCP://SQLSRVTST2.<domain>:7022’,

AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,

FAILOVER_MODE = AUTOMATIC

);

GO

 

The clauses in the TSQL script above are only a partial list of available options, just for demonstration purposes. For more information on creating availability groups click here https://msdn.microsoft.com/en-us/library/ff878399.aspx .

 Database-Level Health Monitoring

In earlier versions of AlwaysOn (SQL 2012 & 2014), failover would occur if there was a problem with the health of the instance. If one of your databases was in trouble however, the AlwaysOn group would not fail over as long as the instance was okay. So if you had a database that was offline, suspect or corrupt, no failover would occur. In AlwaysOn 2016, failover will occur not only if the instance is in trouble, but also if one or more of your databases is in trouble. This is not a default setting however. You have to specify this when creating your AlwaysOn group, by selecting the Database Level Health Detection checkbox in the setup wizard.

Blog_20160818_3

You can also adjust the setting for what triggers a database failover, by setting the property value in the Failover Condition Level properties. You can increase or reduce the values from the default level if necessary. For more information click here https://msdn.microsoft.com/en-us/library/ff878667.aspx.  

Group Managed Service Account (gMSA) Support

In SQL Server 2012, Microsoft added the gMSA enhancement so that service account passwords can be more easily managed. You can now create a single service account for your SQL Server instances, manage the password in Active Directory, and also delegate permissions to each of your servers. This feature can be useful for AlwaysOn groups because passwords and permissions to access certain resources, like shared files, can be managed for one account instead of each instance individually. Using a gMSA is also more secure than using a regular domain user account to manage services in your AG.

Basic Availability Groups

AlwaysOn Basic Availability Groups (BAG) are available with SQL Server 2016 Standard Edition. The functionality is generally the same as database mirroring (which has been deprecated). BAGs provide a failover environment for only one database, there can only be two replicas in the group, replication can be synchronous or asynchronous, and there is no read access and no backups on the secondary.   To create a Basic Availability Group, use the CREATE AVAILABILITY GROUP TSQL command and specify WITH BASIC.   Below is a partial script (again for demonstration purposes) of creating a Basic Availability Group. For more detailed information click here https://msdn.microsoft.com/en-us/library/ff878399.aspx .

 

CREATE AVAILABILITY GROUP BAGSQL2016

WITH (AUTOMATED_BACKUP_PREFERENCE = PRIMARY,

BASIC,

DB_FAILOVER = OFF,

DTC_SUPPORT = NONE)

FOR DATABASE [Database1, Database2, Database3]

REPLICA ON

‘SQLSRVTST1’ WITH — substitute node name

(

ENDPOINT_URL = ‘TCP://SQLSRVTST1.<domain>.com:5022’,

FAILOVER_MODE = AUTOMATIC,

AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,

SECONDARY_ROLE (ALLOW_CONNECTIONS = NO)

),

‘SQLSRVTST2’ WITH — substitute node name

(

ENDPOINT_URL = ‘TCP://SQLSRVTST2.<domain>.com:5022’,

FAILOVER_MODE = AUTOMATIC,

AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,

SECONDARY_ROLE (ALLOW_CONNECTIONS = NO)

)

GO

BI Enhancements

By using AlwaysOn Availability Groups, a data warehouse workload can be offloaded to a single readable secondary replica — or even across multiple readable secondary replicas — leaving the resources on the primary replica to efficiently support the mission-critical business process. Reporting and data analysis can be very resource intensive on a server, so offloading to a non-production server can enhance overall performance. Another added benefit is that because Microsoft revamped the data synchronization process, there is very low latency on the data feeding the data warehouse, so that near real-time analytics can be a reality.

Domain Replicas No Longer Necessary

*Only available with Windows Server 2016

Most companies operate in the context of a single Active Directory domain, but some organizations are set up with multiple domains and could benefit from spreading an AlwaysOn Group across domains so that multiple servers can host DR replicas. And then there are other organizations that operate without Active Directory domains at all.

With Windows Server 2016 operating system, WSFC does not require cluster nodes be in same domain, or in any domain at all (it can be in a workgroup). SQL Server 2016 is now able to deploy AlwaysOn Availability Groups in environments with:

  • All nodes in a single domain
  • Nodes in multiple domains with full trust
  • Nodes in multiple domains with no trust
  • Nodes in no domain at all

This gives improved flexibility by removing domain specific constraints for SQL clusters. For more info click here https://blogs.msdn.microsoft.com/clustering/2015/08/17/workgroup-and-multi-domain-clusters-in-windows-server-2016/

Encrypted Database Support

Encrypted databases were allowed in earlier versions of AlwaysOn, however they couldn’t be added using the New Availability Group wizard, and they could not be accessed if there was a failover to a secondary replica. In SQL Server 2016, it is possible to add an encrypted database via the wizard, and there is now access to the database in the event of failover. This is because during the creation of the Availability Group, the wizard executes sp_control_dbmasterkey_password for each replica, and consequently creates the credentials associated with the database master key for each instance. In the event of failover SQL will search for the correct credentials until it is able to decrypt the database master key.

There are some restrictions with adding an encrypted database to an AlwaysOn group. For more information on how to make an encrypted database eligible to add to an availability group, click here https://msdn.microsoft.com/en-us/library/hh510178.aspx .

SSIS Catalog Support

In SQL Server 2016, you can add your SSIS catalog (SSISDB) and its contents (projects, packages, etc.) to an AlwaysOn Availability Group in SQL2016 like any other database, in order to enhance high availability and disaster recovery.

There are some special prerequisites and configurations for adding the SSISDB to an AlwaysOn group. For more information click here https://msdn.microsoft.com/en-us/library/mt163864.aspx

Conclusion

Many new features and enhancements have been made to SQL Server 2016. This article discussed basic information about some of the enhancements to the AlwaysOn feature. The latest version of AlwaysOn Availability Groups 2016 has improved functionality, scalability, and manageability, and continues to be a robust enhancement for high availability and disaster recovery.

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!

My First SQL 2016 Management Studio Update

I opened my local SSMS today and found my first notification of an update to SQL 2016 Management Studio today. Here is what it looks like:

Blog_20160811_1

When you click on update it takes you to MSDN to download the update.

Blog_20160811_2

Apparently this one is a hotfix. Note that you do not have to download the update and if you have SSMS set to automatically check for updates you will get this notification every time you open SSMS.

I closed SSMS and installed….

Blog_20160811_3

Blog_20160811_4

No reboot was required and I could use SSMS immediately after the install was complete. The install took only a few minutes. The download took several minutes but was not an issue.

Hope your SSMS update goes well!! Let us know if you encounter issues and any solutions you may have!

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!

 

SQLRx Presenting at SQL Saturday Baton Rouge 2016

SQLRx (@SQLRX) is proud to announce that two of our resident SQL Experts will be presenting sessions at SQL Saturday #515 in Baton Rouge, LA on August 6, 2016. If you have not signed up to attend, you are going to miss out!!

Blog_20160804_4

http://www.sqlsaturday.com/515/eventhome.aspx

Lori Brown (@SQLSupahStah) will give a lesson on how to be an Actively Proactive DBA. Come have fun and learn at her session, Actively Proactive – Straightforward Ways to Manage and Diagnose SQL. Attendees will be taken through the things a proactive DBA does and will also be treated to some quick ways to troubleshoot like a SQL Super Star!

Blog_20160804_1

Scripts will be given out to all who download the presentation materials. Come and get ‘em!!

Blog_20160804_2

Jeff Schwartz will presenting Rx for Demystifying Index Tuning Decisions. Jeff will cover ways to find poor performing queries and tuning them using various methods including execution plans, usage statistics and missing indexes.

Blog_20160804_3

Since Jeff has been tuning things for almost 40 years, he can show you some things that will be extremely useful and will help you tune like a pro.

If you are in Baton Rouge this weekend come by and visit with us! SQLRx is a sponsor of SQL Saturday Baton Rouge 2016 and will have a booth where you can come sit down with Lori or Jeff and talk tech. We will also be handing out goodies as well as giving away a 2 TB external drive at the end of the day. Also, one lucky attendee will win a free Ultra Light Performance Assessment from SQLRx at one of our presenter’s sessions. We will tell that lucky winner about their SQL performance for free….Winner, Winner, Chicken Dinner!!

Hope to see you in Baton Rouge!