Tag Archives: Ginger Keys

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!

Is This Database Being Used?

— By Ginger Keys

During the migration of a client’s SQL environment to a new hosting provider it was questioned whether all of the databases needed to be migrated. Nobody seemed to know whether several of the databases were used or what they were for. One person suggested we just not move them, and see if anybody screamed or if it broke anything. Well, I guess that’s one way to find out if a database is being used. But there should be more definitive ways to do this, right?

There really is no straightforward way to determine this, but there are several things we can look at to get a better idea.

  • Look at any current connections
  • Capture login/connections over period of time
  • Observe index usage
  • Look at transaction count
  • Find dependencies with Jobs or other DBs

Current connections

sp_who2 is a well-known stored procedure that returns information about current users, sessions and processes. You can run exec sp_who2 to return all sessions belonging to the instance, or you can filter to return only the active sessions:

–Find active connections to the instance

USE master;

GO

EXEC sp_who2 ‘active’;

GO

Information about processes on your instance can also be derived from sys.sysprocesses. This system view will be deprecated in future releases of SQL. The info in this view returns data about both client and system processes running on the instance. The following statements will filter data relating to a specific database:

–Find number of active connections to database

USE master;

GO

SELECT DB_NAME(dbid) AS DBName,

spid,

COUNT(dbid) AS NumberOfConnections,

loginame,

login_time,

last_batch,

status

FROM   sys.sysprocesses

WHERE DB_NAME(dbid) = ‘AdventureWorks2016’ –insert your database name here

GROUP BY dbid, spid, loginame, login_time, last_batch, status

ORDER BY DB_NAME(dbid)

 

–Active Connections to Database with connecting IP address

SELECT

s.host_name,

s.program_name,

s.login_name,

c.client_net_address,

db_name(s.database_id) AS DBName,

s.login_time,

s.status,

GETDATE() AS date_time

FROM sys.dm_exec_sessions AS s

INNER JOIN sys.dm_exec_connections ASON s.session_id = c.session_id

INNER JOIN sys.sysprocesses ASON s.session_id = p.spid

WHERE DB_NAME(p.dbid) = ‘AdventureWorks2016’ –insert your database name here

 

Connections over time

It might be more beneficial to watch the connections to a database over a period of time instead of looking at the current connections only. In order to gather this data over time, you could create a trace through SQL Server Profiler. Simply run the trace, export it as a definition file, and import it into a table to query the results.   You can also create a SQL Server Audit to record successful logins, but these are at the server level, not the database level.   For a good explanation on how to perform both of these tasks click here https://mssqltalks.wordpress.com/2013/02/25/how-to-audit-login-to-my-sql-server-both-failed-and-successful/ . Depending upon how long you run the Profiler or Audit, these files can take up a considerable amount of space so be sure to monitor your disk space carefully.

 

Index Usage

Another way to see if your database is in use is to look and see if the indexes are being used. Information on index usage is held in the sys.dm_db_index_usage_stats table since the last server reboot, and can be queried using this statement which can be tailored to select the data you need.

SELECT

DB_NAME(database_id) DatabaseName,

last_user_seek,

last_user_scan,

last_user_lookup,

last_user_update

FROM sys.dm_db_index_usage_stats

WHERE db_name(database_id) = ‘AdventureWorks2016’ –insert your database name here

 

Blog_20170330_1

The statement above will show you the date and time the indexes for your database were last used. For the reverse of that, if you want to see which database have not had the indexes used since the last server reboot, run this statement:

SELECT name AS DatabaseName

FROM sys.databases

WHERE name NOT IN (‘master’, ‘tempdb’, ‘model’, ‘msdb’)

EXCEPT

SELECT DISTINCT

DB_NAME(database_id) AS DatabaseName

FROM sys.dm_db_index_usage_stats

ORDER BY 1

Blog_20170330_2

Transaction Count for the Database

Checking to see if the number of transactions are increasing for a database is another way to see if it is being used. You can query the sys.dm_os_performance_counters for Transactions/sec and run this several times to see if the count is increasing or not. Or you can open Perfmon and watch it there as well.

–Transaction count increasing?

SELECT *

FROM sys.dm_os_performance_counters

WHERE counter_name LIKE ‘Transactions/sec%’

AND instance_name LIKE ‘AdventureWorks2016%’ –insert your database name here

GO

Blog_20170330_3

–I waited a few minutes and executed the select statement again

Blog_20170330_4

Database Dependencies

Occasionally other databases or linked servers will connect to your database.   To see objects in your database that are referenced by other databases, run this statement:

SELECT OBJECT_NAME (referencing_id) AS referencing_object,

referenced_database_name,

referenced_schema_name,

referenced_entity_name

FROM sys.sql_expression_dependencies

WHERE referenced_database_name IS NOT NULL

AND is_ambiguous = 0;

 

For finding object referencing linked servers use this

SELECT OBJECT_NAME (referencing_id) AS referencing_object,

referenced_server_name,

referenced_database_name,

referenced_schema_name,

referenced_entity_name

FROM sys.sql_expression_dependencies

WHERE referenced_server_name IS NOT NULL

AND is_ambiguous = 0;

Database dependencies can be a very in-depth topic, and the statements above are only meant for high-level information about connections to your database. For more information about this topic click here https://www.mssqltips.com/sqlservertip/2999/different-ways-to-find-sql-server-object-dependencies/ .

Conclusion

There are countless ways to determine if your database is being used. Other methods that could be used is to see if there are execution plans are in the cache referencing the database, see if reads/writes are happening, look at lock_acquired events, and many other methods I haven’t thought of. The methods outlined above provide a useful starting point to investigate who/what is connecting and if your database is active or not. Yes, you can just take the database offline or detach, or even delete it and see if anyone fusses. But it’s much more prudent to take a look around at some simple things to make that determination.

Feel free to comment with other methods you have used….we would love to hear from you. 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!

 

 

Login Failures Bloating SQL Server Log

–By Ginger Keys

After migrating a client’s SQL instances to VMs from physical servers, I noticed the following week that the SQL Server Log files had tons of failed login attempts from what looked like an application. These attempts were happening several times per second, and had totally bloated the SQL Server logs.

Blog_20170316_1

This is not desirable because 1) obviously something should not be attempting and failing to connect that much, and 2) it makes it super cumbersome to monitor more critical messages in the SQL logs when so many login errors are bloating the log. Too many failed login attempts could mean that the application has not been set with the correct permissions to access the databases needed, or it could be an indication of a hacking attempt.

I ran the script below to determine how many failed login attempts had occurred in the last 7 days, and the count came back with over 3 million failed attempts!

— Create a temporary table

CREATE TABLE #FailedLogins

(Logdate DATETIME,

Process VARCHAR(20),

Text VARCHAR(4000))

 

— Insert data from SQL Server Log into temp table

INSERT INTO #FailedLogins

EXEC xp_readerrorlog 1, 1, N’Login Failed’, N’LoginName’,‘20170309’,‘20170316’

 

— For more info about xp_readerrorlog click here https://sqlandme.com/2012/01/25/sql-server-reading-errorlog-with-xp_readerrorlog/

  –Count number of occurrences

SELECT COUNT(Text) AS LoginFailures, Text

FROM #FailedLogins

GROUP BY TEXT

 

–Drop temp table

DROP TABLE #FailedLogins

 

As it turns out it was a legacy system moved from the old environment that needed to be adjusted to connect to the new environment. I didn’t exactly get to put on my superwoman cape and solve the problem, but at least I was able to identify the issue and direct it to the appropriate IT staff to correct.

As part of your everyday duties as a DBA it is prudent to monitor your SQL Server Logs to catch hacking attempts or malicious activity with your instance, and also to look for errors that could be occurring. By default, SQL contains 7 error log files (one current, and six archives). Error logs contain critical information about your SQL server. A new log is created each time the SQL service starts, and the oldest archived log gets deleted. For security reasons it is best practice to keep a large number of error logs, depending on the space you have available. Someone with access to your SQL server could execute sp_cycle_errorlog and regenerate the logs at a rate that could ‘delete’ their malicious activity or cover up the fact that they have gained access to your system. If you have highly sensitive data or stringent auditing requirements you can increase the number of retained error logs up to 99, just be sure to monitor your disk space.

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!

Moving Non-clustered Indexes To New Data File

-by Ginger Keys

It is not uncommon for database files to grow to the point you need more disk space. But what if it’s not possible or not in the budget to add more space? There are several creative options you have to move or shrink files, or delete unneeded items from the disk. One solution we recently implemented with a client was to move the indexes of a large database to another drive.

Create File/Filegroup

Only non-clustered indexes can be moved, and they must reside in a different filegroup from the primary filegroup. If you don’t already have a separate file and filegroup created, you must do this first. Make sure you have created a folder path on your system to the drive that will be holding your index files.

blog_20170209_1

Get Space Requirement for Indexes

Determine the non-clustered indexes you have in your database and their size by running this script to ensure you have enough space allocated on the target drive to house the index files.

USE AdventureWorks2016

GO

SELECT

OBJECT_NAME(i.OBJECT_ID) AS TableName,

i.name AS IndexName,

8 * SUM(a.used_pages) AS ‘Indexsize(KB)’

FROM sys.indexes AS i

JOIN sys.partitions AS p ON p.OBJECT_ID = i.OBJECT_ID AND p.index_id = i.index_id

JOIN sys.allocation_units AS a ON a.container_id = p.partition_id

JOIN sys.tables AS t ON i.object_id = t.object_id

WHERE i.type_desc = ‘NONCLUSTERED’

GROUP BY i.OBJECT_ID,i.index_id,i.name

ORDER BY OBJECT_NAME(i.OBJECT_ID),i.index_id

blog_20170209_2

Create and Execute Stored Procedure

The following script will create a stored procedure called MoveIndexToFileGroup that will move an index from one file group to another, and even supports the INCLUDE columns. Run this script against the master database to create the stored procedure: https://gist.github.com/dalenewman/6377911

In order to get a list of tables for your database, run the following script.

Use AdventureWorks2016 –use your database name

GO

SELECT * FROM INFORMATION_SCHEMA.TABLES

blog_20170209_3

After the above stored procedure is created and you have gotten a list of schemas/tables/indexes you want to move, execute the following

EXEC MoveIndexToFileGroup

@DBName = ‘AdventureWorks2016’, –your database name

@SchemaName = ‘HumanResources’, –schema name that defaults to dbo

@ObjectNameList = ‘Shift,  

Department,

                    EmployeeDepartmentHistory,

                    EmployeePayHistory,

                    Employee,

                    JobCandidate’, –a table or list of tables

@IndexName = NULL, –an index or NULL for all of them

@FileGroupName = ‘INDEX’; –the target file group

 

Your indexes will appear in the target destination you created.

blog_20170209_4

Conclusion

There are many useful reasons for moving your non-clustered indexes to a separate drive…in this case ours was simply to create more space for the drive holding the data files. Whatever your reason, the steps above should give you a simplistic way to achieve this.

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 Server Security – Controlling Access

— by Ginger Keys

Installing SQL following best practices is an important first step in securing your SQL server. The next step in securing your SQL server is to decide who can access the SQL instance, what databases and other objects they need access to, and what kind of permission to give them to those objects. So what is involved in securing access to your SQL server? We will go over the basic components, and discuss best practices to help keep your server secure.

blog_20161020_1

There are three main areas of security within your SQL server instance.

  • Principals – these are the persons or entities needing to access your SQL server.
  • Securables – these are the databases, objects, and other resources in your SQL server instance.
  • Permissions – these determine which resources a principal can access and what actions they are allowed to perform.

There are also some other important areas to consider in controlling access to your SQL server:

  • Schemas and Ownership

 

  • SQL server service accounts
  • Administrative accounts

 

Principals

Principals are persons, applications, and entities that can be authenticated to access the SQL server resources. In SQL server, principals include logins, users, and roles.

LOGINS – Principals require a login in order to connect to the SQL server. Logins are at the server level only, and provide for the entry point, or the initial connection to the server. Logins are validated against the master database and the connection is to the instance, not the databases or other components.   SQL supports two methods for authenticating logins: Windows and SQL authentication. Mixed mode authentication allows for the use of either Windows or SQL logins.

Windows logins can include individual users, groups, domains accounts, or local accounts. Group accounts are granted login access to all logins that are members of the group. SQL relies on Windows to authenticate these accounts.

SQL logins are specific to the instance, and are stored in SQL, with the username and hash of the password in the master database. SQL uses internal authentication to validate login attempts. This type of login may be necessary for users not associated with a Windows domain.

Best Practices for Logins:

  • Use Windows authentication whenever possible
  • create Windows groups in Active Directory set with appropriate access/permissions to the SQL server, then add individual users to the appropriate groups
  • Do not use SQL authentication for logins if possible –when SQL Server logins are used, SQL Server login names and passwords are passed across the network, which makes them less secure
  • Audit SQL Server failed login attempts to monitor for possible hacking activity

USERS – Once logged in to a SQL instance, a user account is necessary in order to connect to a database and its components. Users are created within a database, and mapped back to the server login.

User accounts that are not mapped to a login account are known as orphaned users. An exception is contained database users; they do not need to map to a login.

Guest user – this account is a built-in account in SQL server, and is disabled in new databases by default. The guest user allows a login to access databases without being mapped to a specific database user, and it inherits the ‘public’ database role with its permissions.

dbo user – this account has implied permissions to perform all activities in the database. Any principals belonging to the sysadmin fixed server role are mapped to the dbo user account automatically. The dbo user is in every database and is a member of the db_owner database role.

Best Practices for Users:

  • Disable the guest user in every user database (not system DBs)
  • If you must use the guest account, grant it minimum permissions

ROLES – Roles exists at both the server and database level. Permissions can be assigned to a role which makes it more efficient to manage principals’ access to securables. Permissions are given to roles, then logins and users can be added to (or removed from) roles.

Server roles – server level roles can be fixed or user defined. Members of server roles have permissions to sever-level securables, and cannot be changed or revoked. Logins can be assigned to fixed server roles without having a user account in a database.     

For complete list and description of server roles click here https://msdn.microsoft.com/en-us/library/ms188659.aspx

Database roles – These roles have a pre-defined set of permissions. Logins must be mapped to database user accounts in order to work with database objects. Database users can then be added to database roles, inheriting any permission sets associated with those roles.

For complete list and description of database roles click here https://msdn.microsoft.com/library/ms189121.aspx

Public role – The public role is contained in every database including system databases. It cannot be dropped and you can’t add or remove users from it. Permissions granted to the public role are inherited by all users because they belong to the public role by default.

Best Practices for Roles:

  • Be very cautious when adding users to fixed server roles:
    • Do not add principals to the sysadmin role unless they are highly trusted.
    • Membership in the securityadmin role allows principals to control server permissions, and should be treated with the same caution as the sysadmin role.
    • Be very cautious in adding members to the bulkadmin role. This role can insert data from any local file into a table, which could put your data at risk. For more information click here https://msdn.microsoft.com/library/ms188659.aspx

 

 

  • Grant public role only the permissions you want all users to have, and revoke unnecessary privileges.

Securables

SQL Server securables are the resources that can be accessed by a principal. SQL server resources operate within a hierarchy, with the server at the top of the hierarchy. Below the server instance lies the databases, and below the databases are a collection of objects (schemas, tables, views, etc.). Access to securables is controlled by granting or denying permissions to principals, or by adding or removing principals (logins and users) to roles which have access. All securables have an owner. The owner of a securable has absolute control over the securable and cannot be denied any privilege. Server level securables are owned by server principals (logins), and database level securables are owned by database principals (users).

blog_20161215_1

Permissions:

Permissions determine the type of access granted on a securable to a specific principal and what tasks a principal can perform on securables. The TSQL permission statements are

GRANT

REVOKE

DENY

Granting permission to a principal removes any DENY or REVOKE on that securable. A permission given at a higher scope in the hierarchy that contains that securable will take precedence over the lower level permission statement. Database level permissions only apply to that specific database.

Owners of securables can grant permissions on the objects they own, and any principal with CONTROL permissions can grant permissions on that securable as well.

Best Practices for Permissions:

  • Always use the principal of least privilege, which limits users by granting the minimum permissions necessary to accomplish a task. For more information click here https://msdn.microsoft.com/library/ms191291.aspx
  • Document any elevated user permission and request managerial approval.
  • When developing an application use a least-privileged user account (LUA), which may be more difficult – but will eliminate the temptation to grant elevated privileges as a quick fix when an end user cannot perform certain tasks that the administrative developer could. Granting elevated permissions to users in order to acquire lost functionality after the development phase can leave your application vulnerable to attack.
  • Grant permissions to roles rather that to users. It is easier to add and remove users from roles, than to manage separate permission sets for individual users.
  • Don’t grant individual users access to SQL Server, instead create groups for specific servers with specific permissions, and add individual users to the appropriate groups.

Ownership & Schemas 

Owners of objects have irrevocable permissions to administer those objects. The owner of a securable has absolute control over the securable and cannot be denied any privilege. You cannot remove privileges from an object owner, and you cannot drop users from a database if they own objects in it. Server level securables are owned by server principals (logins), and database level securables are owned by database principals (users).

A schema is a named container for similar database objects, and can be owned by any principal. You can assign security rules for a schema which will then be inherited by all objects in that schema.

Objects can be moved between schemas, and schema ownership can be transferred between principals.

Best Practices for Schemas:

  • In your TSQL, refer to the objects using a fully qualified name. At the very least, use the schema name followed by the object name, separated by a period (.). Example: databasename.schemaname.tablename.
  • Use synonyms to obfuscate the schema ownership of objects.
  • Use schemas to protect the base database object from being altered or removed from the database by users without sysadmin permissions.
  • Use schemas to combine related, logical entities to reduce administration overhead.
  • For more info – https://technet.microsoft.com/en-us/library/dd283095(v=sql.100).aspx

SQL Server Service Accounts 

Depending on what features and components you decide to install, SQL Server has several services that are used to manage authentication of SQL Server with Windows. These services need user accounts associated with them to start and run the services. The service accounts should have exactly the privileges that it needs to do its job and no more. For a list of all available SQL services click here https://msdn.microsoft.com/en-us/library/ms143504.aspx .

Best Practices for Service Accounts:

  • Assign service accounts low-level permissions using the principle of least privilege.
  • Don’t use built-in Windows system accounts (e.g., Network Service, Local System) for SQL Server service accounts – the built-in system accounts inherit certain elevated rights in Active Directory that aren’t required by SQL Server.
  • Use a Managed Service Account (MSA) if resources external to the SQL Server will be used.
  • When MSA are not possible, use a specific low-privilege non-user domain account.
  • Use separate accounts for different SQL Server services. The service accounts should not only be different from one another, they should not be used by any other service on the same server.
  • SQL Server Agent account is the only service account that requires membership in the systems admin server role.
  • Do not grant additional permissions to the SQL Server service account or the service groups – SQL Server installation program will grant them the local rights that they need during the installation.
  • Always use SQL Server Configuration Manager to change service accounts or passwords.
  • Don’t add service accounts to the Local Administrator group
  • Use a service account for applications, and restrict the applications access only to data required – not every object in the database

Administrative Accounts

sa Account – SQL Server creates this server level login during installation by default. The sa account has full administrative rights in your SQL Server instance, and is well known and often targeted by hackers. The sa login maps to the sysadmin fixed server role, which has irrevocable administrative privileges on the whole server.

BUILTIN\Administrators – All members of the Windows BUILTIN\Administrators group are members of the sysadmin role by default. This built in group is no longer included in SQL Server 2008 and later.

Best Practices for Administrative Accounts

  • Disable or rename the sa account – make sure another account exists with admin privileges before doing this.
  • If you must use the sa account, assign a strong password and enforce password policies
  • Do not use the sa account for day-to-day administration or logging on to the server remotely
  • Do not allow applications to use the sa account to connect to SQL
  • Remove the BUILTIN/Administrators group from the SQL Server Logins if it’s present in your instance.
  • Restrict users with system admin privileges, using server roles instead.
  • Restrict members of local administrator group. Limit the administrative access only to those who really require it.
  • Assign every administrator a named login, and don’t allow shared logins so that you can identify the users behind each and every database change.
  • Document all users with administrative rights, and any elevated user permissions

Conclusion

Controlling access to your SQL Server should be well thought out ahead of time. Every organization needs to have an information security policy in place, and the role of the DBA is to enforce these policies and protect the SQL Server instances along with the data in them. The suggestions provided above give a basic guideline for controlling who has access to your SQL Server. Utilizing windows groups, database roles, applying the principle of least privilege, and implementing the other suggestions above can be instrumental in keeping your SQL environments safe.

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 Server Memory In Task Manager – Tell Your Manager to Not Worry!

-by Ginger Keys

blog_20160929_1We are going to re-promote this subject because it is still so relevant. All too often we run into people who are in a panic after looking at Task Manager to try to diagnose SQL stress. They seem to become very worried when looking at how much memory SQL uses not realizing that is as expected.

Task Manager is just that, a task manager. If you are looking at it to get accurate values for memory (RAM) usage or CPU time, you will get not-so-accurate information. False alarms. Information that doesn’t exactly mean what it implies.

Blog_20150507_2What I usually see when I look at Task Manager is that CPU is low, disk usage is low, and memory used is high – which is what we expect on a SQL machine.  SQL will use as much memory as it can get, to improve performance by caching tons of data in memory in case it needs it, to avoid having to go to disk.  It will immediately release any memory to the OS if needed.  SQL is a memory hog, and will hold onto all the memory we allocate to it, whether it is actually using it or not.  And this is what we want it to do!

SQL will take all the memory that is allocated to it when it is needed, store data and execution plans in the buffer, and when it is through using it, it doesn’t release it…it caches that data into memory until either 1) SQL needs it again, or 2) Windows OS requests the memory.

This isn’t an indication of performance stress…it’s just showing that SQL has reserved all the memory that we told it to use (through setting min and max memory on the SQL instance properties [1]).  SQL doesn’t let go of memory unless the server comes under memory pressure (like if other apps need memory and Windows sends out a memory pressure notification).  As long as the OS doesn’t need to use it for anything, SQL will hold on to it for future use.

The default memory setting when you install SQL is for it to use all of the memory. When windows asks SQL to give it back, SQL gives it back in huge amounts. And then proceeds to build back up again to maximum amount. This is not optimum for stability or performance, and it is highly recommended to set the limit in the Max Memory Setting as a best practice. That way Windows OS is always guaranteed to have a certain amount.

Click here for more info on setting Max Memory: https://sqlserverperformance.wordpress.com/2009/10/29/suggested-max-memory-settings-for-sql-server-20052008/

Another good reference for setting Max Memory for SQL is here: http://www.sqlservercentral.com/blogs/glennberry/2009/10/29/suggested-max-memory-settings-for-sql-server-2005_2F00_2008/

Blog_20150507_3

And when looking at Resource Monitor, it is a mistake to use the Available and Free metrics as the key indicators for determining overall system health [2]. The Available MB of memory is comprised of both the Standby and the Free… the stuff in Standby is what Windows has put in the cache (but can be booted out if needed), and Free means it is not even being used at all. The combination is what Windows calls Available. So there is actually data in the cache that Windows says is available. Some of the categories in task manager/resource monitor can be very confusing and even misleading, which is why we shouldn’t use these metrics alone to decide if we are in danger of being out of memory.

Instead of only relying on task manager or resource monitor, we really need to look at other metrics to find out if there is a memory related performance issue on the machine.

Some of the metrics to pay attention to are:

  • Page Life Expectancy
  • Available Bytes
  • Buffer Cache Hit Ratio
  • Target & Total Server Memory
  • Memory Grants Pending
  • Pages/sec (Hard Page Faults)
  • Batch Requests/sec & Compilations/sec

Some of these metrics can be queried from TSQL, or can be observed with Perfmon (Windows Performance Monitor – located in Administrative Tools).

 Page Life Expectancy (PLE)

Found in Perfmon at SQL Server: Buffer Manager – Page Life Expectancy, this counter measures the average amount of time unlocked data pages are staying in the buffer pool.   During peak production hours this number may dip into lower numbers, but we generally want this number to remain above 300 seconds (so data is staying in the cache for 5 minutes or longer). The longer data stays in the buffer, the less likely SQL will have to go to disk for I/O operations.

There is an interesting occurrence with page life expectancy… when SQL really does run out of memory, PLE drops very quickly…but it grows back very slowly. You’re probably not still having memory issues during the coming back stage, even though it may look like its struggling. If PLE stays down however, then you have a problem.   So be careful, because PLE can be misleading when it takes a drop… there’s a difference between it going down & staying down (you have a memory problem), and going down once and crawling back up (which is normal). If it stays down below 300 seconds consistently, you may need to add more RAM.

You can monitor this through Perfmon, or run the following query:

SELECT [cntr_value]

FROM sys.dm_os_performance_counters

WHERE [object_name] LIKE ‘%Buffer Manager%’

AND [counter_name] = ‘Page life expectancy’

Available MBytes

Found in Perfmon as Memory: Available MBytes, this counter detects how much memory is available for use, and whether Windows is experiencing memory pressure. We generally start investigating if this number consistently goes below 500MB, which would indicate low memory, and Windows may start memory management operations. If Available Mbytes is consistently low, you may need to add more RAM.

This counter cannot be queried through TSQL, it has to be observed through Perfmon only.

Buffer Cache Hit Ratio

Found in Perfmon as SQL Server: Buffer Manager: Buffer Cache Hit Ratio. This counter averages (since the last restart of your SQL instance) how often SQL Server goes to the buffer pool to get data, and actually finds that data in memory, instead of having to go to disk. We want to see this ratio high in OLTP servers – around 90-95%. The higher the ratio, the less often SQL has to go to disk for I/O operations, which translates into better performance for your end users. If this number is consistently below the 90% mark, you may need to add more RAM to your server to boost performance.

SELECT [cntr_value]

FROM sys.dm_os_performance_counters

WHERE [object_name] LIKE ‘%Buffer Manager%’

AND [counter_name] = ‘Buffer cache hit ratio’

 Target & Total Server Memory

Found in Perfmon at SQL Server: Memory Manager – Total Server Memory, and SQL Server: Memory Manager – Target Server Memory. The Total Server Memory is the current amount of memory that SQL Server is using.  The Total Server memory will be quite a bit lower than the Target memory during the initial buffer pool ramp up… it is trying to populate the cache and get pages loaded into memory.  Performance might be a little slower during this time since more disk I/O is required, but this is normal.  After it ramps up, and normal operations resume, Total Server Memory should be very close to Target Server Memory. (The ratio should be close to 1). If Total Server Memory does not increase much, but stays significantly less than Target, this could indicate a couple of things…

1) You may have allocated much more memory than SQL can use – SQL could cache the entire databases into memory, and if the databases are smaller than the amount of memory on the machine, the data won’t take up all the space allocated to SQL. So in this case Total Memory (actually memory being used by SQL) will never reach Target Memory (amount allocated to SQL). Or,

2) SQL cannot grow the buffer pool because of memory pressure from outside of SQL. If this is the case, you need to either increase the Max Server Memory, or add more RAM to boost performance.

SELECT [cntr_value]

FROM sys.dm_os_performance_counters

WHERE [object_name] LIKE ‘%Memory Manager%’

AND [counter_name] IN (‘Total Server Memory (KB)’,’Target Server Memory (KB)’)

 Memory Grants Pending

Found in Perfmon as SQL Server: Memory Manager – Memory Grant Pending, this counter measures the total number of SQL processes waiting for a workspace memory grant. The general recommendation for this measurement should be 1 or less. Anything above 1 indicates there are SQL processes waiting for memory in order to operate.

Memory grants pending could be due to bad queries, missing indexes, sorts or hashes. To investigate this, you can query the sys.dm_exec_query_memory_grants view, which will show which queries (if any) that require a memory grant to execute [5].

If the Memory Grants Pending are not due to the above mentioned conditions, then more memory should be allocated to SQL Server by adjusting Max Server Memory. Adding more RAM should be the last resort in this case.

SELECT [cntr_value]

FROM sys.dm_os_performance_counters

WHERE [object_name] LIKE ‘%Memory Manager%’

AND [counter_name] = ‘Memory Grants Pending’

Pages/sec (Hard Page Faults)

Found in Perfmon as Memory: Pages/sec, this measures the number of pages read from or written to disk. Pages/sec is the sum of Pages Input/sec and Pages Output/sec and reports the number of requested pages that were not immediately available in RAM and had to be read from the disk (resulting in hard page faults). A Hard Page Fault occurs when windows has to use the swap file on the disk [3]. It’s when the address in memory that’s part of a program is no longer in main memory, but has been instead swapped out to the paging file, making the system go looking for it on the hard disk. Since disk storage is much slower than RAM, performance can be greatly degraded.

We want to see the Page/sec number below 50, and closer to 0. If you see a high value of Pages/sec on a regular basis, you might have performance degradation, but not necessarily. A high Pages/sec value can happen while doing database backups or restores, importing or exporting data, or by reading a file mapped in memory [4].

Because a hard fault doesn’t necessarily indicate a critical error condition depending upon what’s normal for your environment, it’s a good idea to measure a baseline value, and monitor this number based on that. If the values are consistently higher that your baseline value, you should consider adding more RAM.

Batch Request & Compilations

SQL Server: SQL Statistics – Batch Request/Sec. This is the number of incoming queries

SQL Server: SQL Statistics – Compilations/Sec. This is the number of new executions plans SQL had to build

If Compilations/sec is 25% or higher relative to Batch Requests/sec, SQL Server is putting execution plans in the cache, but never actually reusing them.  Your valuable memory is being used up to cache query execution plans that will never be used again – instead of caching data.  This is bad. We don’t want this to happen. And a high Compilation/sec value (like over 100) indicates there are a lot of Ad-Hoc (one-hit-wonder) queries being run. You can enable the “optimize for ad hoc” setting if this is the case, and this will put the execution plan in the buffer, but only after the second time it has been used.

To query these metrics with TSQL:

SELECT [cntr_value]

FROM sys.dm_os_performance_counters

WHERE [object_name] LIKE ‘%SQL Statistics%’

AND [counter_name] = ‘Batch Requests/sec’

SELECT [cntr_value]

FROM sys.dm_os_performance_counters

WHERE [object_name] LIKE ‘%SQL Statistics%’

AND [counter_name] = ‘SQL Compilations/sec’

To get the ratio of Compilations to Batch Requests:

SELECT ROUND (100.0 *

(SELECT [cntr_value]

FROM sys.dm_os_performance_counters

WHERE [object_name] LIKE ‘%SQL Statistics%’

AND [counter_name] = ‘SQL Compilations/sec’)

/

(SELECT [cntr_value]

FROM sys.dm_os_performance_counters

WHERE [object_name] LIKE ‘%SQL Statistics%’

AND [counter_name] = ‘Batch Requests/sec’)

,2) as [Ratio]

 

Looking at the counters in Perfmon (Report View) below, we can see that this particular server is in a healthy state, with over 11GB of Available Bytes, no page faults (so I/O is operating from buffer, not disk), Buffer cache with 100% ratio, PLE of 21,515 seconds (that’s almost 6 hours data spent in the buffer), no pending memory grants (so no SQL process is having to wait for memory), ample Total memory, and low compilation ratio (11%) compared to all the queries being run.

Blog_20150507_4

These measurements should be observed and understood, instead of using Task Manager to make critical decisions about resources on your SQL Server.

If it is decided to make changes based upon only what we see in Task Manager, and we reduce the amount of memory SQL can use, and it happens to need more to execute processes, it will have to read/write from disk, which will degrade the performance.  Reducing the amount of memory SQL can use will slow performance and will most likely increase complaints from end users. The lower you set your Maximum Server Memory, the lower your performance will go.

No matter how much memory you put in a system, SQL Server will use all it can get until its caching entire databases in memory and then some.  This isn’t an accident. SQL Server caches data in memory.  On purpose. That means it is healthy, and enhancing performance for your end users.

Now send a link to this blog post to your favorite person on staff who needs to be educated about Task Manager and SQL Server. You will be glad you did!

References

[1] “Glenn Berry’s SQL Server Performance” https://sqlserverperformance.wordpress.com/2009/10/29/suggested-max-memory-settings-for-sql-server-20052008/

[2] “Use Resource Monitor for Memory Monitoring” by Scott Lowe.   http://www.techrepublic.com/blog/the-enterprise-cloud/use-resource-monitor-for-memory-monitoring/

[3] “Swap File”. TechTarget. http://searchwindowsserver.techtarget.com/definition/swap-file-swap-space-or-pagefile

 

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!

Log File is Too Big!

–by Ginger Keys

Recently I had a client who, after speaking with their software vendor, became overly fearful of their database log files growing too big and taking up all of the disk space on their server. The software vendor is apparently used to having clients that don’t have a DBA on staff, and made some recommendations that would not have allowed my client to recover data to point in time (i.e. switch to Simple recovery, shrink the log file, switch back to Full recovery). I informed my client that this is a very bad idea since they needed to be able to recover as much data as possible if something happened to the database, and promptly undid these changes. I decided to take a look around their systems, and saw the log file for one of the production databases was very large… but there were valid reasons it needed to be that large.

Hopefully you will never have to deal with an ill-informed vendor like this, but if you should find that your log file has grown very big, there are usually some common reasons for this.

Most common reasons:

  1. Your database is set to Full Recovery and no Tlog backups are happening
  2. Large inserts or deletes are happening (and it really needs to be that big)
  3. Long running transactions are running (index maintenance or bulk import)

It’s important to have enough room in your log file for SQL to do its work. Transactions in your database (i.e. changes to data) are logged and these records are written sequentially to the log file. As soon as the transaction has committed, a checkpoint has written the changes to disk, or a backup of the tlog has occurred, these records are no longer needed and SQL will mark the log files ready for re-use.   The most ideal situation is to size your log file correctly from the beginning.

Think of your log file as being like a reusable grocery bag that you use and fill each week.

Blog_20160714_1

When you take the groceries out of the bag (truncate), the size of the bag is still the same, and it’s ready for more groceries (same amount) to fit in. Shrinking the bag will make it smaller, and assuming you need to put the same amount of groceries in the following week, you will have less storage space. The groceries won’t fit, so don’t shrink your grocery bag if you know you need the space!

The obvious solution to prevent log files from growing too large is managing it correctly on the front end (i.e., planning correctly for data and log space, and recovery requirements), but sometimes events occur that require us to be reactive instead of proactive. It happens to the best of us!

Solutions

1. If your database is set to Full Recovery and no Tlog backups are happening

  •  Set to Simple recovery – do this only if you don’t care about point-in-time recovery in the event of disaster. This would most likely used on a test or dev database, and is also suitable for data warehouses with read-only data.
  • Leave in Full recovery, but schedule tlog backups on a frequent basis depending upon your tolerance for possible data loss. In a perfect world, production databases should be in Full Recovery mode for disaster recovery reasons. Recovery model and frequency of backups are always going to depend on a business’ tolerance for data loss. Backing up your log file once every day or three won’t really help with your log size growth, especially in a highly transactional database because the log file will continue to grow until the log is backed up. (I have clients that back up Tlogs every 5 minutes, to every 2 hours…just depends on your business needs).

2. If large inserts or deletes are happening, and it really needs to be that big

  •  Consider breaking up large transactions into smaller batch jobs
  • Consider switching to Bulk Logged Recovery during the large bulk operations. Switching between Full and Bulk Logged will not break the transaction log backup chain.
  • Properly size your log file. For large transactions SQL has to be able to see logs back to the beginning of the transaction in case something goes wrong and it needs to recover. The size of your log file should accommodate the largest transaction, or the largest sum of simultaneous transactions that regularly occur.
  • Properly size the autogrowth of your log file. When your log needs more room for large transactions, and the auto growth size is set in too small increments, too many Virtual Log Files (VLFs) will be created which takes more time to perform database recovery. In order to see how many VLFs are in your database, check how many records are returned after running this statement:

DBCC LOGINFO

As a common rule of thumb, VLFs should be less than 500, depending on the size of your database. If VLFs greatly exceed this amount, take these steps:

1.Take backup of your log file (may need to do this twice)

2.Shrink the log file

USE MyDatabase;

GO

DBCC SHRINKFILE (MyDatabase_log, 1);

GO

— shrink log file as small as it can go, preferably close to 1MB

— may have to shrink it gradually, in smaller chunks

3.Re-grow the log file to a practical size, which should accommodate the largest transaction, or the largest sum of simultaneous transactions that regularly occur. *Depending on the size you want your log file to be, consider growing your log file in multiple chunks. Kimberly Tripp recommends growing your log file in 8GB chunks…read this for more info: http://www.sqlskills.com/blogs/kimberly/transaction-log-vlfs-too-many-or-too-few/

 USE MyDatabase

GO

ALTER DATABASE MyDatabase

MODIFY FILE

(NAME = MyDatabase_log, SIZE = 8000MB)

– grow the log in multiple chunks (i.e. 8GB, then 16GB, etc)

4.Set auto grow size to larger chunks of space, so less VLFs are created during auto grows

USE [master];

GO

ALTER DATABASE MyDatabase

MODIFY FILE

(NAME = MyDatabase_log, FILEGROWTH = 1000MB);

GO

3. If Long running transactions are running (index maintenance or bulk inserts)

  •  Again, properly size your log file. (See steps above). Log files should be created at the desired size when the database is created, rather than allowed to slowly grow over time. However if this was not done initially, then shrinking the log file and manually re-growing it to the desired size is your next option.
  • If possible, consider breaking up large transactions into smaller batch jobs

 

Conclusion

Being proactive in managing your SQL environment is always recommended for preventing problems, but occasionally things happen which require us to deal with issues. Instead of creating your database log file with a small amount of space and allowing it to automatically grow in small amounts, create it with enough space from the start, so that it only grows on rare occasions. Properly managing your database file sizes and auto growth will help ensure your disk space doesn’t run out, and also it will help improve the performance of your database.

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!