Category Archives: AlwaysOn

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!