Category 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!