Tag Archives: AlwaysOn

AlwaysOn – Endpoint Ownership

— By Ginger Keys

It is not uncommon for a DBA or other IT staff to set up AlwaysOn in a SQL environment and later leave the company. We ran into this recently with a client and were asked to delete the previous employee’s login from everything SQL related. We were able to remove the login from all databases and server roles, however we were not able to delete the login because it was the owner of an endpoint.

When creating an AlwaysOn Availability Group, you have the option of using the wizard or you can create it using TSQL statements. The wizard is very intuitive and easy to use and with the exception of a few settings you can specify, default configurations are deployed using this method. One of the default configurations is the endpoint owner. Whoever creates the AlwaysOn group is by default the owner of the endpoint.

This is generally not a problem…unless that person leaves the company and you need to delete the login! You will get an error message that says “The server principal owns one or more endpoint(s) and cannot be dropped (Microsoft SQL Server, Error: 15141)”.

To check and see who the owner of your endpoints are, run this statement:

USE master

GO

SELECT e.name as EndpointName,

sp.name AS EndpointOwner,

et.PayloadType,

e.state_desc

FROM sys.endpoints e

INNER JOIN sys.server_principals sp

ON e.principal_id = sp.principal_id

RIGHT OUTER JOIN ( VALUES ( 2, ‘TSQL’),

( 3, ‘SERVICE_BROKER’), ( 4, ‘DATABASE_MIRRORING’) )

AS et ( typeid, PayloadType )

ON et.typeid = e.type

The AlwaysOn endpoint will have the name Hadr_endpoint and will have a DATABASE_MIRRORING payload type as shown below.

Blog_20170803_1

If your AlwaysOn group has already been created and there is a domain login as the owner, you can change the ownership to sa. Run the following statement to make the change:

USE master

GO

ALTER AUTHORIZATION ON ENDPOINT::Hadr_endpoint TO sa

This will allow you to delete any login who might have owned the endpoint if its ever necessary.

If you are creating an AlwaysOn Availablitiy Group and want to use TSQL statements instead of the wizard, you have the ability to specify the endpoint owner. For complete instructions on how to set up the AlwaysOn group with TSQL click here https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/create-an-availability-group-transact-sql

In order to create the endpoint with a specific user, run the following statement:

CREATE ENDPOINT endpoint_mirroring

AUTHORIZATION loginname

STATE = STARTED

AS TCP (LISTENER_PORT = 5022)

FOR DATABASE_MIRRORING (

AUTHENTICATION = WINDOWS KERBEROS,

ENCRYPTION = SUPPORTED,

ROLE=ALL);

GO

In the statement above, if AUTHORIZATION is not specified with a SQL or Windows login, the caller will become the owner of the newly created endpoint. To use AUTHORIZATION and assign ownership to a login, the caller must have IMPERSONATE permission on the specified login.

Endpoints are a fundamental piece of SQL that allows a connection or point of entry into your SQL Server. Knowing who owns these endpoints and how to change the owner will potentially save you some headaches down the road in the event of IT staffing changes in your organization.

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!

 

AlwaysOn 2016 – Encrypted Databases and Performance

–By Ginger Keys

It is common knowledge that encrypting a database causes degraded performance in a SQL server. In an AlwaysOn environment performance can be even more sluggish because there is the extra element of data replication latency. How much difference does it really make? Of course the answer is “it depends” on your environment and your workload. I was curious to see for myself what kind of performance hit encryption would have on one of my test databases, so this post will look at CPU usage of an encrypted vs non-encrypted database.

Microsoft says that turning on TDE (Transparent Data Encryption) for a database will result in a 2-4% performance penalty, which is actually not too bad given the benefits of having your data more secure. There is even more of a performance hit when enabling cell level or column level encryption. When encrypting any of your databases, keep in mind that the tempdb database will also be encrypted. This could have a performance impact on your other non-encrypted databases on the same instance.

In a previous post I demonstrated how to add an encrypted database to an AlwaysOn group in SQL2016. In this article I will demonstrate the performance effects of having an encrypted database in your AlwaysOn Group compared to the same database not-encrypted.

I have 3 identical test databases I will use to look at performance metrics.

  • GKTestDB is TDE encrypted, and is part of the AlwaysOn group
  • GKTestDB2 is not encrypted, and not part of AlwaysOn group
  • GKTestDB3 is not encrypted, but is part of AlwaysOn group

Blog_20170713_1

There are some good open source tools for performing stress tests on your SQL database.   You can find one here that uses the AdventureWorks2014 database http://www.sqlballs.com/2016/05/sql-server-random-workload-generator.html. There is also one here and you can use this on any database https://github.com/ErikEJ/SqlQueryStress/wiki. I will be using the SQLQueryStress tool for my demonstration.

Step 1 – Test performance on non-encrypted DB not in AG

To measure performance metrics, create a User Defined Data Collector Set in Performance Monitor (Perfmon). There are many metrics that can be measured, but I will only be looking at CPU % Processor Time.

Blog_20170713_2

Download and open the SQLQueryStress tool, and create a statement to run against your database. In my test I will first look at the performance of running a select query for 5000 iterations on a database that has not been added to the AlwaysOn group, and has not been encrypted: (GKTestDB2)

Blog_20170713_3

Be sure to clear the buffers and cache before performing each stress test. Select your database, the server name, the number of iterations, the number of threads and the delay between queries in milliseconds.

Blog_20170713_4

Start your user defined Perfmon data collector set, then start the stress test in the SQLQueryStress tool.

Blog_20170713_5

At the end of each stress test you will need to manually stop your Perfmon collection.

Step 2 – Test performance on encrypted DB in the AlwaysOn Group

Now I will perform the same stress test to see performance on the identical but Encrypted database in the AlwaysOn group (GKTestDB). Be sure to clear the buffers and cache, and change the database in the SQLQueryStress tool.

Blog_20170713_6

Blog_20170713_7

Step 3 – Test performance on non – encrypted DB in the AlwaysOn Group

Just for curiosity sake, I’m also going to test the identical database that is not encrypted, but is included in the AlwaysOn group (GKTestDB3):

Blog_20170713_8

Blog_20170713_9

Step 4 – Compare results

I set the output files of my Perfmon results to land in a location on my local drive so that I can open up the results of each test and compare.

The CPU usage for the database not encrypted and not in my AlwaysOn group averaged 43% for the duration the test was run, as shown by the dark line on the graph below.

Not Encrypted / Not in AG database CPU usage:

Blog_20170713_10

The CPU usage for the identical database that is encrypted and is in the AlwaysOn group averaged 57.5% during the stress test as shown on the graph below. This is quite a bit more than the non-encrypted/non AG database, especially given the simple statement that was being run.

TDE Encrypted / Joined to AG Database CPU usage:

Blog_20170713_11

And finally, the CPU usage for the identical database that is not encrypted, but is included in my AlwaysOn group averaged 43.4%, which is not much different than the non-encrypted /non-AG database above.

Not Encrypted / Joined to AG Database CPU usage:

Blog_20170713_12

Blog_20170713_13

Conclusion

Having an encrypted database creates a noticeable CPU performance hit as compared to a non-encrypted database. Microsoft provides many options for protecting your data, transparent data encryption (TDE) being one of them. The benefits and assurance of securing your data outweigh the performance cost, however it may be useful to see how much of a performance hit your system will encounter prior to deciding which security options your organization will deploy.

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!

 

 

 

 

 

AlwaysOn 2016 – Encrypted Databases in an AG

–By Ginger Keys

In the AlwaysOn 2016 release, there are several enhancements that have improved manageability, scalability, and availability. One of the enhancements is the ability to add encrypted databases to an AlwaysOn group.

Encrypted databases were allowed in earlier versions of AlwaysOn, however they could not be accessed if there was a failover to a secondary replica because the primary and secondary replicas had different service master keys. So when the secondary would try to decrypt the database master key using its own service master key, the master key would fail to open because of an invalid password.

In SQL 2016 there is now access to the database on the secondaries in the event of failover since it is no longer a requirement for each replica to have the same Database master Key (DMK) and password on each instance. There is no need to back up the DMK from the primary and restore it to the secondary(s), it is only necessary that a DMK exist on each replica. As long as each replica has a DMK created, even without the same password, there will not be any encryption/decryption issues because of different keys.

Transparent Data Encryption (TDE) is a method of protecting your data ‘at rest’. There are several methods of protecting your data in addition to TDE, but this article will only discuss TDE encrypted databases in an AlwaysOn Availability Group. If your physical media (drives or backup tapes) are stolen, TDE will encrypt the sensitive data in the data and log files, and will also protect the keys used to encrypt the data with a certificate. TDE provides for real-time I/O encryption and decryption, but it does not protect data ‘in-flight’ flowing from the server to the end user. Also it does not protect data from a user who has privileges in the SQL instance.

The steps below demonstrate how to add an encrypted database to an AlwaysOn Availability Group

  1. Encrypt your database (if not already encrypted)
  2. Create/verify Database Master Key (DMK) on secondary replica(s)
  3. Create the TDE Certificate on secondary replica(s)
  4. Perform Full and Tlog backup of encrypted database on primary replica
  5. Add encrypted database to AlwaysOn group on primary replica
  6. Restore Full and Tlog backups on secondary replica(s)
  7. Join the encrypted database to the AlwaysOn group on secondary replica(s)

Step 1 – Encrypt Database

If your database has not already been encrypted, follow these steps to create a master key (DMK), backup the certificate, create a database encryption key (DEK), and enable encryption on the database on your primary replica.

–create DMK database master key

USE master

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘DMKP@ssw0rd’

GO

OPEN MASTER KEY DECRYPTION BY PASSWORD = ‘DMKP@ssw0rd’

GO

BACKUP MASTER KEY TO FILE = ‘C:\SQLRX\KeyBkup\SQLDMK.dmk’

ENCRYPTION BY PASSWORD = ‘G00dP@ssw0rd’

GO

 

–check to make sure master key is set up

SELECT b.name, a.crypt_type_desc

FROM sys.key_encryptions a

INNER JOIN sys.symmetric_keys b

ON a.key_id = b.symmetric_key_id

WHERE b.name = ‘##MS_DatabaseMasterKey##’;

GO

 

–create and backup certificate

CREATE CERTIFICATE TDECert WITH SUBJECT = ‘Cert used for TDE’;

GO

BACKUP CERTIFICATE TDECert

TO FILE = ‘C:\SQLRX\KeyBkup\Cert4TDE.cer’

WITH PRIVATE KEY

(

FILE = ‘C:\SQLRX\KeyBkup\TDEPvtKey.pvk’,

ENCRYPTION BY PASSWORD = ‘*DBA$RC00l’

);

GO

 

–create DEK database encryption key with certificate

USE GKTestDB

GO

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM   = AES_256 — AES_128 | AES_192 | AES_256 | TRIPLE_DES_3KEY

ENCRYPTION BY SERVER CERTIFICATE TDECert

GO

USE master

GO

 

enable TDE for the database

ALTER DATABASE GKTestDB SET ENCRYPTION ON

GO

 

–monitor encryption progress

SELECT db_name(database_id), encryption_state, percent_complete, key_algorithm, key_length

FROM sys.dm_database_encryption_keys

GO    

 

Step 2 – Verify Database Master Key on Secondary Replica(s)

On each secondary replica, verify that a Database Master Key (DMK) exists in the master database.

 

–check to make sure master key is set up

SELECT b.name, a.crypt_type_desc

FROM sys.key_encryptions a

INNER JOIN sys.symmetric_keys b

ON a.key_id = b.symmetric_key_id

WHERE b.name = ‘##MS_DatabaseMasterKey##’;

GO

If a record is returned, that means a DMK exists on the secondary instance. If no record is returned, then create a one as follows:

–create DMK database master key

USE master

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘DMKP@ssw0rd2’

GO

OPEN MASTER KEY DECRYPTION BY PASSWORD = ‘DMKP@ssw0rd2’

GO

BACKUP MASTER KEY TO FILE = ‘C:\SQLRX\KeyBkup\SQLDMK.dmk’

ENCRYPTION BY PASSWORD = ‘G00dP@ssw0rd’

GO

 

Step 3 – Create TDE Certificate on Secondary Replica(s)

On each secondary replica instance, create/restore the TDE Certificate from the server certificate and private key that you backed up on the primary. Copy the certificate as well as the private key from the primary over to a file path on the secondary(s). The following will install the certificate onto the secondary replica(s).

–create TDE Certificate from the certificate backed up on primary

CREATE CERTIFICATE TDECert

FROM FILE = ‘C:\SQLRX\KeyBkup\Cert4TDE.cer’

WITH PRIVATE KEY

(

FILE = ‘C:\SQLRX\KeyBkup\TDEPvtKey.pvk’,

DECRYPTION BY PASSWORD = ‘*DBA$RC00l’

)

Step 4 – Create Full & Tlog Backups on the Primary Replica

Create a full and tlog backup of the TDE encrypted database on the primary replica.

–create Full Backup of encrypted database on primary

BACKUP DATABASE [GKTestDB]

TO DISK = N’C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\GKTestDB.bak’

WITH NOFORMAT, NOINIT, NAME = N’GKTestDB-Full Database Backup’, SKIP, NOREWIND,NOUNLOAD, COMPRESSION, STATS = 10

GO

–create Tlog backup of encrypted database on primary

BACKUP LOG [GKTestDB]

TO DISK = N’C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\GKTestTlogBackup.trn’

WITH NOFORMAT, NOINIT, NAME = N’GKTestDB-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10

GO

Step 5 – Add Encrypted Database to AlwaysOn Group

On the primary replica instance, add the TDE encrypted database to the Availability Group. As of the current release of SQL Server 2016, you cannot do this yet through the wizard

Blog_20170525_1

Instead add the TDE database to your AlwaysOn group using TSQL:

USE master

GO

ALTER AVAILABILITY GROUP AGroup_SQLRx

ADD DATABASE GKTestDB

GO

Step 6 – Restore Full & Tlog Backups on Secondary Replica(s)

On each secondary replica instance, restore the full and tlog backups of the encrypted database with no recovery. Copy the backup files from the primary replica to a file location on your secondary and restore from that file path as follows:

–Restore Full backup of encrypted database on secondary with no recovery

USE [master]

RESTORE DATABASE [GKTestDB] FROM DISK = N’C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\GKTestFullBackup.bak’ WITH FILE = 2, NORECOVERY, NOUNLOAD, STATS = 5

GO

–Restore the Tlog backup of encrypted database on secondary with no recovery

RESTORE LOG [GKTestDB] FROM DISK = N’C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\GKTestTlogBackup.trn’ WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5

GO

Step 7 – Join the Encrypted Database to AlwaysOn Group on Secondary Replica

On each secondary replica instance, join the database to the availability group.

USE master

GO

ALTER DATABASE GKTestDB

SET HADR AVAILABILITY GROUP = AGroup_SQLRx

GO

Blog_20170525_2

Conclusion

After joining the encrypted database to the AlwaysOn group, you should now be able to fail over the group to the secondary replica and be able to access the database without any issues. It is common knowledge that encrypting a database causes degraded performance, so the next article will demonstrate the performance effects of having an encrypted database in your AlwaysOn Group.

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!

 

AlwaysOn 2016 – Direct Seeding

–By Ginger Keys

SQL Server 2016 provides some awesome new enhancements and features, especially in Enterprise Edition.  One of the new features relating to AlwaysOn is Direct Seeding.

Good stuff – No more time consuming backup and restore

  • Direct Seeding allows you to initialize the databases to your secondary replica(s) without having to configure a network share or perform backup and restore operations.  It will send the database directly over to the secondary replica without you needing to do anything.  Woo hoo!!

Gotchas – Some with regards to Large Databases

  • Direct seeding does not use compression by default, so adding a very large database to your AlwaysOn group could take a while and can use a lot of your network bandwidth.  Compression can be enabled by turning on Trace Flag 9567.   However, know that adding a large database with compression enabled will cause high CPU utilization during the seeding process.   For more info click here https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/tune-compression-for-availability-group .
  • Also during direct seeding the transaction log will not truncate, so in a highly transactional database your transaction log could potentially grow very large during this time.
  • For large databases being initialized to secondary replicas using direct seeding, be mindful of your network capacity (as the entire database will be sent across the network to the secondary(s)), workload amount which could bloat your transaction log, CPU pressure if you enable compression, and database size which will affect the seeding time.

You may want to use direct seeding during afterhours to prevent your Network or System Admin from hunting you down for the network hit that will happen.

If you are using SQL Server Management Studio version 17, Direct Seeding of your databases is now available in the GUI! You can download this version of SSMS here https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms .

Let’s Do This!

I will assume you already have your environment ready (i.e. failover cluster created and configured, SQL installed and configured, AlwaysOn enabled, replicas and logins configured, etc.). So from the New Availability Group Wizard, you will specify your AG name:

Blog_20170511_1

Just a side note… 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.

Next, select your database(s) to add to the AlwaysOn group:

Blog_20170511_2

Then add the replicas to be included in your AG and what type of failover (automatic or manual), data replication (synchronous or asynchronous), and whether they will allow for read only access:

Blog_20170511_3

Next click on the Endpoints tab and make sure URL and port numbers are correct:

Blog_20170511_4

Click on the Backup Preferences tab and specify how you want your backups to occur:

Blog_20170511_5

Click on the Listener tab and specify the Listener (DNS) name, port number and IP address:

Blog_20170511_6

And THIS is where you can specify Automatic (Direct) Seeding!

Blog_20170511_7

Click Next through the next screens and THAT’S IT!

Blog_20170511_8

You’re done, simple as that.

Blog_20170511_9

If you do not have SSMS v17 you can still initialize your databases to secondary replicas using direct seeding, but you will need to do this through TSQL from the primary replica:

CREATE AVAILABILITY GROUP []

FOR DATABASE db1

REPLICA ON ‘<*primary_server*>’

WITH (ENDPOINT_URL = N’TCP://<*primary_server*>.<*fully_qualified_domain_name*>:5022′,

FAILOVER_MODE = AUTOMATIC,

AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,

BACKUP_PRIORITY = 50,

SECONDARY_ROLE(ALLOW_CONNECTIONS = NO),

SEEDING_MODE = AUTOMATIC),

N'<*secondary_server*>’ WITH (ENDPOINT_URL = N’TCP://<*secondary_server*>.<*fully_qualified_domain_name*>:5022′,

FAILOVER_MODE = AUTOMATIC,

AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,

BACKUP_PRIORITY = 50,

SECONDARY_ROLE(ALLOW_CONNECTIONS = NO),

SEEDING_MODE = AUTOMATIC);

GO

For more information click here https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/automatically-initialize-always-on-availability-group .

Conclusion

SQL Server 2016 AlwaysOn gives you the ability to join databases to your AlwaysOn group and initialize them on your secondaries without having to backup and restore, or establish a network share. This ultimately saves much time and effort, but can take a while if your database is large. And using compression during the seeding process can save some time, but your CPU will take a performance hit. Also keep in mind that your transaction log will grow during this time as it will not truncate during the seeding process. Of course, be sure to test this first before doing it in your production environment!

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!

SQL 2016 Automatic Seeding of Availability Groups

–By Lori Brown

I came across something today that I did not know existed in AG’s, Automatic Seeding. This allows SQL to automatically create the secondary replicas for all databases in an AG. Pretty cool!! So if you set things up correctly when you set up your AG’s, you don’t have to worry about backing up databases and tlogs and taking them to the secondary replica, restoring and then getting the AG fully set up. It is a one stop shop. This is new in SQL 2016 only as far as I can tell.

https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/automatically-initialize-always-on-availability-group

Blog_20170420_1

You have to set up your AG by script but that is not too difficult.

—Run On Primary

CREATE AVAILABILITY GROUP [<availability_group_name>]

FOR DATABASE db1

REPLICA ON ‘<*primary_server*>’

WITH (ENDPOINT_URL = N’TCP://<primary_server>.<fully_qualified_domain_name>:5022′,

FAILOVER_MODE = AUTOMATIC,

AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,

BACKUP_PRIORITY = 50,

SECONDARY_ROLE(ALLOW_CONNECTIONS = NO),

SEEDING_MODE = AUTOMATIC),

N'<secondary_server>’ WITH (ENDPOINT_URL = N’TCP://<secondary_server>.<fully_qualified_domain_name>:5022′,

FAILOVER_MODE = AUTOMATIC,

AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,

BACKUP_PRIORITY = 50,

SECONDARY_ROLE(ALLOW_CONNECTIONS = NO),

SEEDING_MODE = AUTOMATIC);

GO

Of course you have to be aware that if you set this on an AG with large databases, this could cause an issue since SQL would be pushing an entire database across the network. There is trace flag 9567 that can help compress the data stream for AG’s using Automatic Seeding but there are some side effects of increased processor load that you need to be aware of.

https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/tune-compression-for-availability-group

We are setting up some new VM’s here at SQLRX and will be blogging later in much more depth on how this works.

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!

Synchronize Logins on AlwaysOn Replicas

–By Lori Brown   @SQLSupahStah

At one of my clients who has an AlwaysOn failover cluster, I noticed as I was checking up on things on the secondary replica that there were several fewer Windows and SQL logins on the secondary than on the primary. I really did not want to tediously compare the logins on each so instead came up with a script that can be run periodically to script out any logins that need to be synched up between the replicas. I actually found most of the script that I needed on the SQLSoldier’s blog (http://www.sqlsoldier.com/wp/) in a stored procedure that was intended to synch logins between mirrored partners. Since AlwaysOn is Mirroring on steroids I thought that it would work and it did. I did put my own touches on the proc by turning it into a standalone script that uses OPENROWSET to query the AG primary replica and also changed it from automatically executing the statements for missing logins to simply printing them into a script that can be applied as needed.

And now….here is what you came here for….

— Original logic from http://www.sqlsoldier.com/wp/sqlserver/transferring-logins-to-a-database-mirror

— Sync Logins to AlwaysOn Replicas

—     Inputs: @PartnerServer – Target Instance (InstName or Machine\NamedInst or Instname,port)

—     Output: All Statements to create logins with SID and Password for both Windows and SQL logins

—             Will also add logins to server roles

—     Person executing this must be sysadmin

—     Ad Hoc Distributed Queries must be enabled for this to work without setting up a linked server

/*

— Turn on Ad Hoc Distributed Queries so we don’t have to set up a linked server

sp_configure ‘show advanced options’, 1

GO

reconfigure with override

go

sp_configure ‘Ad Hoc Distributed Queries’, 1

GO

reconfigure with override

go

*/

 

Use master;

Go

Declare @MaxID int,

@CurrID int,

@PartnerServer sysname,

@SQL nvarchar(max),

@LoginName sysname,

@IsDisabled int,

@Type char(1),

@SID varbinary(85),

@SIDString nvarchar(100),

@PasswordHash varbinary(256),

@PasswordHashString nvarchar(300),

@RoleName sysname,

@Machine sysname,

@PermState nvarchar(60),

@PermName sysname,

@Class tinyint,

@MajorID int,

@ErrNumber int,

@ErrSeverity int,

@ErrState int,

@ErrProcedure sysname,

@ErrLine int,

@ErrMsg nvarchar(2048)

 

SET @PartnerServer = ‘InstName’

 

Declare @Logins Table (LoginID int identity(1, 1) not null primary key,

[Name] sysname not null,

[SID] varbinary(85) not null,

IsDisabled int not null,

[Type] char(1) not null,

PasswordHash varbinary(256) null)

 

Declare @Roles Table (RoleID int identity(1, 1) not null primary key,

RoleName sysname not null,

LoginName sysname not null)

 

Declare @Perms Table (PermID int identity(1, 1) not null primary key,

LoginName sysname not null,

PermState nvarchar(60) not null,

PermName sysname not null,

Class tinyint not null,

ClassDesc nvarchar(60) not null,

MajorID int not null,

SubLoginName sysname null,

SubEndPointName sysname null)

 

Set NoCount On;

 

If CharIndex(‘\’, @PartnerServer) > 0 — Check for Named Instance

Begin

Set @Machine = LEFT(@PartnerServer, CharIndex(‘\’, @PartnerServer) – 1);

End

Else If CharIndex(‘,’, @PartnerServer) > 0 — Check for Instance with port in connection string

Begin

Set @Machine = LEFT(@PartnerServer, CharIndex(‘,’, @PartnerServer) – 1);

End

Else

Begin

Set @Machine = @PartnerServer;

End

 

— Get all Windows logins from principal server

— using OPENROWSET and Windows Authentication

Set @SQL = ‘Select a.* From OPENROWSET (”SQLNCLI”, ”Server=’+@PartnerServer+‘;Trusted_Connection=yes;”, ”Select P.name, P.sid, P.is_disabled, P.type, L.password_hash

             From master.sys.server_principals P

             Left Join master.sys.sql_logins L On L.principal_id = P.principal_id

             Where P.type In (””U””, ””G””, ””S””)

             And P.name <> ””sa””

             And P.name Not Like ””##%””

             And CharIndex(””’ + @Machine + ‘\””, P.name) = 0”)as a;’

 

–print @SQL

 

Insert Into @Logins (Name, SID, IsDisabled, Type, PasswordHash)

Exec sp_executesql @SQL;

 

— Get all roles from principal server

— using OPENROWSET and Windows Authentication

Set @SQL = ‘Select a.* From OPENROWSET (”SQLNCLI”, ”Server=’+@PartnerServer+‘;Trusted_Connection=yes;”, ”Select RoleP.name as RoleName, LoginP.name as LoginName

             From master.sys.server_role_members RM

             Inner Join master.sys.server_principals RoleP

             On RoleP.principal_id = RM.role_principal_id

             Inner Join master.sys.server_principals LoginP

             On LoginP.principal_id = RM.member_principal_id

             Where LoginP.type In (””U””, ””G””, ””S””)

             And LoginP.name <> ””sa””

             And LoginP.name Not Like ””##%””

             And RoleP.type = ””R””

             And CharIndex(””’ + @Machine + ‘\””, LoginP.name) = 0”)as a;’

 

–print @SQL

 

Insert Into @Roles (RoleName, LoginName)

Exec sp_executesql @SQL;

 

— Get all explicitly granted permissions

— using OPENROWSET and Windows Authentication

Set @SQL = ‘Select a.* From OPENROWSET (”SQLNCLI”, ”Server=’+@PartnerServer+‘;Trusted_Connection=yes;”, ”Select P.name Collate database_default,

                    SP.state_desc, SP.permission_name, SP.class, SP.class_desc, SP.major_id,

                    SubP.name Collate database_default,

                    SubEP.name Collate database_default

             From master.sys.server_principals P

             Inner Join master.sys.server_permissions SP

             On SP.grantee_principal_id = P.principal_id

             Left Join master.sys.server_principals SubP

             On SubP.principal_id = SP.major_id And SP.class = 101

             Left Join master.sys.endpoints SubEP

             On SubEP.endpoint_id = SP.major_id And SP.class = 105

             Where P.type In (””U””, ””G””, ””S””)

             And P.name <> ””sa””

             And P.name Not Like ””##%””

             And CharIndex(””’ + @Machine + ‘\””, P.name) = 0”)as a;’

 

–print @SQL

 

Insert Into @Perms (LoginName, PermState, PermName, Class, ClassDesc, MajorID, SubLoginName, SubEndPointName)

Exec sp_executesql @SQL;

 

Select @MaxID = Max(LoginID), @CurrID = 1

From @Logins;

 

While @CurrID <= @MaxID

Begin

Select @LoginName = Name,

@IsDisabled = IsDisabled,

@Type = [Type],

@SID = [SID],

@PasswordHash = PasswordHash

From @Logins

Where LoginID = @CurrID;

 

If Not Exists (Select 1 From sys.server_principals

Where name = @LoginName)

Begin

Set @SQL = ‘Create Login ‘ + quotename(@LoginName)

If @Type In (‘U’, ‘G’)

Begin

Set @SQL = @SQL + ‘ From Windows;’

End

Else

Begin

Set @PasswordHashString = ‘0x’ +

Cast( As XML).value(‘xs:hexBinary(sql:variable(“@PasswordHash”))’, ‘nvarchar(300)’);

 

Set @SQL = @SQL + ‘ With Password = ‘ + @PasswordHashString + ‘ HASHED, ‘;

 

Set @SIDString = ‘0x’ +

Cast( As XML).value(‘xs:hexBinary(sql:variable(“@SID”))’, ‘nvarchar(100)’);

 

Set @SQL = @SQL + ‘SID = ‘ + @SIDString + ‘;’;

End

 

Print @SQL;

 

If @IsDisabled = 1

Begin

Set @SQL = ‘Alter Login ‘ + quotename(@LoginName) + ‘ Disable;’

Print @SQL;

End

End

Set @CurrID = @CurrID + 1;

End

 

Select @MaxID = Max(RoleID), @CurrID = 1

From @Roles;

 

While @CurrID <= @MaxID

Begin

Select @LoginName = LoginName,

@RoleName = RoleName

From @Roles

Where RoleID = @CurrID;

 

If Not Exists (Select 1 From sys.server_role_members RM

Inner Join sys.server_principals RoleP

On RoleP.principal_id = RM.role_principal_id

Inner Join sys.server_principals LoginP

On LoginP.principal_id = RM.member_principal_id

Where LoginP.type In (‘U’, ‘G’, ‘S’)

And RoleP.type = ‘R’

And RoleP.name = @RoleName

And LoginP.name = @LoginName)

Begin

Print ‘Exec sp_addsrvrolemember @rolename = ”’ + @RoleName + ”’, @loginame = ”’ + @LoginName + ”’;’;

End

Set @CurrID = @CurrID + 1;

End

 

Select @MaxID = Max(PermID), @CurrID = 1

From @Perms;

 

While @CurrID <= @MaxID

Begin

Select @PermState = PermState,

@PermName = PermName,

@Class = Class,

@LoginName = LoginName,

@MajorID = MajorID,

@SQL = PermState + space(1) + PermName + SPACE(1) +

Case Class When 101 Then ‘On Login::’ + QUOTENAME(SubLoginName)

When 105 Then ‘On ‘ + ClassDesc + ‘::’ + QUOTENAME(SubEndPointName)

Else End +

‘ To ‘ + QUOTENAME(LoginName) + ‘;’

From @Perms

Where PermID = @CurrID;

 

If Not Exists (Select 1 From sys.server_principals P

Inner Join sys.server_permissions SP On SP.grantee_principal_id = P.principal_id

Where SP.state_desc = @PermState

And SP.permission_name = @PermName

And SP.class = @Class

And P.name = @LoginName

And SP.major_id = @MajorID)

Begin

Print @SQL;

End

Set @CurrID = @CurrID + 1;

End

 

Set NoCount Off;

GO

 

/*

— Turn off Ad Hoc Distributed Queries

sp_configure ‘Ad Hoc Distributed Queries’, 0

GO

reconfigure with override

go

*/

On my test AG replicas, you can see that there are two users that are not present on the secondary. I need to add them so that if a failover occurs then there will not be any login failures.

blog_20170105_1

Using the script above on SQLSRVTST2, I get the following output and can use that to create my users with their correct permissions. I had to set the @PartnerServer variable to SQLSRVTST1 in the script and enable Ad Hoc Distributed Queries.

blog_20170105_2

……

blog_20170105_3

blog_20170105_4

The nice thing is that the password and SID’s are also scripted for you. There is no need to install sp_hexadecimal and sp_help_revlogin in master. If you have AG replicas with a lot of logins, this can be a real timesaver for you. Hope you enjoy and if you make any improvements, please share them with us!

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!

 

AlwaysOn 2016 – Maintenance

— by Ginger Keys

SQL Server AlwaysOn provides a fairly robust environment for disaster recovery; however you can never be too careful when it comes to technology and protecting your data. Regular maintenance routines (i.e. backups, etc.) need to be set up and performed on an ongoing basis, even when you have your databases in an AlwaysOn Availability Group.

AlwaysOn allows some maintenance routines to be performed on the secondary replicas, but determining which maintenance to perform on what replica can be somewhat confusing. We will discuss the following basic maintenance routines and where to execute them.

  • Backups
  • Integrity Checks
  • Index Maintenance
  • Update Statistics

Backups

Even though there is a copy of your database on the secondary replica(s), you should still continue to back up your databases and transaction logs on a regular basis.   Backups can be very resource intensive by putting strain on I/O and CPU especially when using compression. Because of this, offloading your backup operations to a secondary replica can greatly improve the performance of your primary production replica.

With an AlwaysOn group, you have several options of where to back up your databases as shown below. You can choose your backup preference when you set up your AlwaysOn group, or you can go back later and set your backup preferences through the AG properties.

blog_20161006_1

Prefer Secondary – backups should occur on a secondary replica except when the primary replica is the only replica online. In that case, the backup should occur on the primary replica. This is the default behavior.

Secondary Only – backups will happen only on a replica that is designated as a secondary, and will execute in the order of the backup priority you set. Backups will not occur on the primary replica.

Primary – backups will only occur on the replica currently designated as primary.

Any Replica – backups will occur on the replica with the highest backup priority setting.

If you perform backups on your primary replica, Full, Differential, and Tlog backups can be run normally. If you perform backups on a secondary replica, there are some differences to be aware of:

  • You must configure your AlwaysOn group to specify where you want your backups to occur. You can do this through the New Availability Group setup GUI (as shown in picture above), or by using TSQL script (for more info click here https://msdn.microsoft.com/en-us/library/ff878399.aspx). These backup preferences will apply to both Full and Tlog backups.
  • The automated backup preference setting has no impact on ad-hoc backups. Manual ad-hoc backups will occur on whatever replica you perform the backup.
  • If you create your backup jobs through the Maintenance Plan, create the jobs on all replicas that have any intention of potentially performing backups. The job will automatically include the scripting logic that calls and checks the sys.fn_hadr_backup_is_preferred_replica function, and will not execute if the replica is not the preferred backup location. The job will not fail, it simply won’t execute. And in the event of failover the job will already exist on the replica the AlwaysOn Group has failed over to, and will execute accordingly.
  • Full Backups run on secondary replicas are copy only backups. Regular backups are not allowed. Copy-only backups do not impact the log chain or clear the differential bitmap.
  • Differential backups are not allowed on secondary replicas.
  • Tlog Backups only support regular backups on a secondary replica (copy only is not allowed). Tlog backups can be taken on any replica regardless of synchronization mode, and the log chain will remain consistent.
  • If databases exist in your SQL instance that are not part of the AlwaysOn Group, create separate maintenance jobs for those databases apart from the AlwaysOn databases so that the sys.fn_hadr_backup_is_preferred_replica function is not invoked. This way your non-AlwaysOn databases will not get lumped in with the AlwaysOn databases. Having separate maintenance routines for separate groups of databases can get a little hairy, as well as managing the jobs that need to run on each replica…for excellent guidance on this click here https://blog.sqlrx.com/2015/10/08/alwayson-availability-groups-enable-or-disable-jobs-on-failover/
  • Since the instances on the AlwaysOn replicas do not share an msdb database backups will not show up in the msdb backup tables.  This is important to know in the event you have to perform audits, or need to research backup history.

 

Integrity Checks

Perform Integrity Checks on all replicas if possible.

There are many differing opinions in the IT world on where to run the DBCC CHECKDB in an AlwaysOn Group.   Some believe integrity checks should be run on the secondary only, some believe it should be run wherever your database backups are happening, and some believe it should be run on all replicas.

SQL allows integrity checks to be offloaded to any of the secondary replicas in order to prevent resource contention on the primary replica, which keeps performance optimized. This is a good idea in theory, however if there is corruption in the data files of the primary database (assuming you have offloaded your integrity checks to a secondary) it will not be detected by the integrity check.   Even though data in the databases is the same across all replicas, the actual data files reside on the separate disk subsystems of each replica. The integrity check runs against the data files on each disk subsystem, and if your hardware or a file is corrupt on one of your replicas the integrity check run on another replica will not catch it.

Corruption on a primary does not propagate to the secondary because complete database pages are not sent, only the tlog records which only describe the physical changes made to the pages…they don’t contain the actual pages. Performing an integrity check on a primary replica doesn’t detect corruption on a secondary and vise-versa.   They are separate databases with their own data files kept in sync through transaction logs that describe the physical changes.

If your organization is extremely sensitive to performance for your AlwaysOn group, we recommend you restore a copy of your database backups to a server not in your AlwaysOn group (like a test server) and run the integrity checks there. This will provide a consistency check of your data, will ensure your backups are viable, and will eliminate overhead of running CHECKDB on the primary.

Index Maintenance

Perform Index Maintenance on primary replica

Because secondary databases are read-only, index maintenance must be performed on the primary replica. Index maintenance generates large amounts of logged changes by design and consequently will result in large transaction logs being replicated to the secondaries. Also index maintenance can cause the primary log files to grow large because the log cannot be truncated until redo has completed the changes in all secondary replicas.

Some ways to reduce resource contention for index maintenance is:

  1. Schedule during off peak times if possible
  2. Break up maintenance into smaller phases, spread out over time period
  3. Use MAXDOP to limit the number of processors used for the operation
  4. Rebuild/reorganize based upon specified criteria:
    1. Ignore heaps or small tables
    2. Rebuild/reorganize based upon fragmentation levels acceptable to your environment

If you are fortunate enough to have a large maintenance window to be able to rebuild your indexes on a daily basis, there is not much need to update statistics as rebuilding indexes does this by design. The frequency of your maintenance routines will depend on each individual SQL environment. As a general rule with our clients, we rebuild indexes once per week and update statistics every day…but again, your environment may require something different.

Update Statistics

Update Statistics on the primary replica.

SQL Server creates statistics on database tables and indexes that gives the query optimizer information about the distribution of data. This allows SQL to compile the most efficient query plan in order to improve query performance and run the queries as quickly as possible.

In an AlwaysOn Group of databases, statistics that are created and updated on the primary replica are persisted to the secondary replicas as part of the data replication of the transaction logs records.

But what if you’re using your secondaries for read-only operations and the secondary databases need different statistics than the primary? Databases on the secondary replicas are restricted to read-only access so statistics cannot be created on them! If the secondary replicas cannot create statistics, then performance of read-only workloads can suffer.

Thankfully SQL will create and maintain statistics on the secondary replicas in tempdb by using the suffix _readonly_database_statistic appended to the name of the temporary statistic. This keeps it separate from the permanent statistics that have been sent over from the primary. These temporary statistics can only be created by SQL Server, and you need to allow auto update and auto creation of statistics.

For more information on SQL statistics, click here https://msdn.microsoft.com/en-us/library/ms190397.aspx

Conclusion

This article covers a high-level overview for performing some basic maintenance on your AlwaysOn databases. These are suggestions based on a simple AlwaysOn environment. There are always exceptions to these rules and circumstances where a system is set up in a more complex manner.   For example, you may want Tlog backups to occur on a different replica than the Full backups. You may have your AlwaysOn group also involved in log shipping or some other functionality that requires more or different maintenance routines.   There are methods to accommodate every environment, but is beyond the scope of this article. Hopefully these topics have given you a basic foundation for maintaining your AlwaysOn environment.

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!

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!

SQL Connections – Finding what fixed port SQL is listening on

— by Lori Brown  @SQLSupahStah

While trying to add a database to an existing AlwaysOn Availability Group (AG), I was having difficulty making a connection from the primary replica to the secondary replica.

Blog_20160212_1

The availability group was functioning but as I tried to add a database to it, my connection to the secondary replica was being rejected with the following error:

Blog_20160212_2

That was interesting since I know the replica is up.  So I also tested connecting from the primary to the secondary from Management Studio with the same result.  Normally when I see the message above I would immediately solve my connection issue by turning on the Named Pipes protocol in the Configuration Manager.  However, in this case the AG was functioning so it had to be communicating without Named Pipes being necessary and I did not have a maintenance window to restart SQL since this is a 24×7 shop.

I decided that there must be a port that SQL is using that is not the normal 1433.  So, I found the sys.dm_tcp_listener_states dmv that gave me the info that I was looking for.  In the below table, I could see that the AG listener was using port 5022 as it should and SQL was using 1434 and another port.  That had to be the port I was looking for!

SELECT * FROM sys.dm_tcp_listener_states

Blog_20160212_3

Since I had just taken over these boxes, I had no idea that they were not listening on the regular port so I confirmed my findings in the properties of the TCP/IP protocol for the instance.

Blog_20160212_4

When TCP Dynamic Ports has no value, SQL will listen on the TCP port you fill in for it.

Blog_20160212_5

Once I connected to the secondary replica in management studio, I was then able to connect to the secondary replica in the Add Database wizard for AlwaysOn.

More info on troubleshooting and setting ports can be found at:

sys.dm_tcp_listener_states – https://msdn.microsoft.com/en-us/library/hh245287.aspx

Configure a Server to Listen on a Specific TCP Port – https://msdn.microsoft.com/en-us/library/ms177440.aspx

Troubleshoot AlwaysOn Availability Groups Configuration – https://msdn.microsoft.com/en-us/library/ff878308.aspx

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!

AlwaysOn Availability Groups – Enable or Disable Jobs on Failover

— By Lori Brown  @SQLSupahStah

After reading up on AlwaysOn Monitoring and Alerting (https://blog.sqlrx.com/2015/08/27/alwayson-monitoring-and-alerting/), I was asked to come up with a way to automatically enable jobs that may only need to run on the primary replica of an availability group. Requirements were that on failover of an AlwaysOn Availability Group some jobs should be enabled on the primary replica and some jobs should be disabled on the secondary replica. After some thinking and research on the sys.dm_hadr_availability_group_states dynamic management view, I came up with a solution that uses a job category, a failover alert setup for error 1480 that is notated in the AlwaysOn Monitoring and Alerting post and some code in a job that is only run if the failover alert fires.

The sys.dm_hadr_availability_group_states dmv gives information on the state of availability groups that are running on the instance including the name of the instance that is the primary. https://msdn.microsoft.com/en-us/library/ff878537.aspx. Here is my query:

SELECT s.primary_replica

FROM sys.dm_hadr_availability_group_states s

JOIN sys.availability_groups ag ON ag.group_id = s.group_id

WHERE ag.name = ‘AGroup_Dev’

Blog_20151008_1

Since I can get at information showing if an instance is primary or secondary, all I have to do is find the jobs that should be enabled. The best way I could think of to do this is to assign a specific job category to jobs that need some type of action taken on them and query for jobs with the correct category on them.

SELECT j.name

FROM msdb.dbo.sysjobs j

WHERE EXISTS (SELECT c.category_id

FROM msdb.dbo.syscategories c

WHERE j.category_id = c.category_id

AND c.name = ‘SomeCategory’)

AND j.enabled = 0  — disabled but should be enabled on failover

ORDER BY j.name

Blog_20151008_2

All I would need to do is add some logic that would roll through (yes, sadly that means a cursor) each job returned by the query above and run sp_update_job to enable or disable it.

FETCH NEXT FROM agjobs INTO @jobname

— Enable AG jobs if primary

WHILE @@FETCH_STATUS = 0

BEGIN

SET @SQLString = ‘EXEC msdb.dbo.sp_update_job @job_name = ”’+@jobname+”’ , @enabled = 0′

EXEC sp_executesql @SQLString

— Get the next record

FETCH NEXT FROM agjobs INTO @jobname

END

— Close and deallocate the cursor

CLOSE agjobs

DEALLOCATE agjobs

So, now let’s put it all together and fill in the rest of the code…..

In our development server we are using the VM’s and instances that were used in the AlwaysOn Monitoring and Alerting post and the AGroup_Dev availability group that was set up. There are 3 instances: AlwaysOn1 is set as the primary, AlwaysOn2 is a secondary in synchronous mode, and AlwaysOn3 is a secondary in asynchronous mode. On all 3 instances we have the jobs AG Database Health and AG Replica Health. These jobs should only be enabled to run if the instance is the primary replica for AGroup_Dev.

First, create a new job category that will be assigned to any jobs that must have a specific action applied to it if a failover occurs. In my example below, I have created the job category AG1 on the AlwaysOn1 instance.

USE [msdb]

GO

— Create AG job category

DECLARE @ReturnCode INT

SELECT @ReturnCode = 0

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name = N’AG1′ AND category_class = 1)

BEGIN

EXEC @ReturnCode = msdb.dbo.sp_add_category @class = N’JOB’, @type = N’LOCAL’, @name = N’AG1′

END

GO

I also created job categories (AG2 & AG3) on AlwaysOn2 and AlwaysOn3 respectively. I did this so that I could keep track of what instance the category applies to. Once my job category is created, I then assigned it to the AG Database Health and AG Replica Health jobs.

Blog_20151008_3

Next, make sure that Error 1480 is set to generate an alert.

EXEC msdb.dbo.sp_add_alert

@name = N’AG Role Change (failover)’,

@message_id = 1480,

@severity = 0,

@enabled = 1,

@delay_between_responses = 0,

@include_event_description_in = 1;

GO

Now we can make the job that needs to query the dmv and enable or disable jobs depending on the value in the primary_replica column. I created the AlwaysOn Job Management job on each instance using the code below and simply changed the places where the job category is used to have the correct category name for the instance it is on.

— create job to run if alert 1480 fires

/****** Object:  Job [AlwaysOn Job Management]    ******/

BEGIN TRANSACTION

DECLARE @ReturnCode INT

SELECT @ReturnCode = 0

DECLARE @jobId BINARY(16)

EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N’AlwaysOn Job Management’,

@enabled=1,

@notify_level_eventlog=0,

@notify_level_email=0,

@notify_level_netsend=0,

@notify_level_page=0,

@delete_level=0,

@description=N’No description available.’,

@category_name=N'[Uncategorized (Local)]’,

@owner_login_name=N’sa’,

@job_id = @jobId OUTPUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object:  Step [Enable or Disable]    ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N’Enable or Disable’,

@step_id=1,

@cmdexec_success_code=0,

@on_success_action=1,

@on_success_step_id=0,

@on_fail_action=2,

@on_fail_step_id=0,

@retry_attempts=0,

@retry_interval=0,

@os_run_priority=0,

@subsystem=N’TSQL’,

@command=N’DECLARE @primreplica VARCHAR(100)

DECLARE @AGName VARCHAR(100)

DECLARE @jobname VARCHAR(128)

DECLARE @SQLString NVARCHAR(2000)         — Dynamic SQL string

SET @AGName = ”AGroup_Dev”  — must know ag name

SELECT @primreplica = s.primary_replica

FROM sys.dm_hadr_availability_group_states s

JOIN sys.availability_groups ag ON ag.group_id = s.group_id

WHERE ag.name = @AGName

IF UPPER(@primreplica) =  UPPER(@@SERVERNAME)

BEGIN

— PRIMARY

— Find all disabled AG jobs.

DECLARE agjobs CURSOR FOR

SELECT j.name

FROM msdb.dbo.sysjobs j

WHERE EXISTS (SELECT c.category_id

FROM msdb.dbo.syscategories c

WHERE j.category_id = c.category_id

AND name = ”AG1”)

AND j.enabled = 0  — disabled but should be enabled on failover

ORDER BY j.name

— Open the cursor

OPEN agjobs

FETCH NEXT FROM agjobs INTO @jobname

— Enable AG jobs if primary

WHILE @@FETCH_STATUS = 0

BEGIN

SET @SQLString = ”EXEC msdb.dbo.sp_update_job @job_name = ”””+@jobname+””” , @enabled = 1”

EXEC sp_executesql @SQLString

–PRINT @SQLString

— Get the next record

FETCH NEXT FROM agjobs INTO @jobname

END

— Close and deallocate the cursor

CLOSE agjobs

DEALLOCATE agjobs

END

ELSE

BEGIN

— SECONDARY

— Find all enabled AG jobs.

DECLARE agjobs CURSOR FOR

SELECT j.name

FROM msdb.dbo.sysjobs j

WHERE EXISTS (SELECT c.category_id

FROM msdb.dbo.syscategories c

WHERE j.category_id = c.category_id

AND name = ”AG1”)

AND j.enabled = 1  — enabled but should be disabled on failover

ORDER BY j.name

— Open the cursor

OPEN agjobs

FETCH NEXT FROM agjobs INTO @jobname

— Disable all AG1 jobs if secondary

WHILE @@FETCH_STATUS = 0

BEGIN

SET @SQLString = ”EXEC msdb.dbo.sp_update_job @job_name = ”””+@jobname+””” , @enabled = 0”

EXEC sp_executesql @SQLString

–PRINT @SQLString

— Get the next record

FETCH NEXT FROM agjobs INTO @jobname

END

— Close and deallocate the cursor

CLOSE agjobs

DEALLOCATE agjobs

END’,

@database_name=N’master’,

@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)’

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

COMMIT TRANSACTION

GOTO EndSave

QuitWithRollback:

IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

EndSave:

GO

I don’t have a schedule set up for the AlwaysOn Job Management job because I only want this to run if a failover alert is sent. All that is left to do is to make the 1480 failover alert run the AlwaysOn Job Management job.

— Make the alert run the Job management job

EXEC msdb.dbo.sp_update_alert @name=N’AG Role Change (failover)’,

@job_name=N’AlwaysOn Job Management’

GO

Blog_20151008_4

I did this for all instances that are in the AlwaysOn group AGroup_Dev, so now we need to test by failing over to each node and making sure that the jobs with the special category assigned are enabled or disabled.

Here are AlwaysOn1 and AlwaysOn2 before failover:

Blog_20151008_5

And here is how they look after failover:

Blog_20151008_6

As you can see the AG Database Health and AG Replica Health jobs are now disabled on AlwaysOn1 and are enabled on AlwaysOn2.

Of course the job code can be changed to accommodate more than 1 availability group and more than 1 job category. Be creative and have fun with it! Hope this helps anyone who needs this type of functionality for their AlwaysOn replicas.

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. We love to talk tech with anyone in our SQL family!