TSQL Date Time Formats

Extracting date time formats can be a challenge.

Below are some common formats which I see used regularly:

TSQL	Format
`SELECT CONVERT(char(8),GETDATE(),112)`	yyyymmdd
`SELECT CONVERT(char(8),GETDATE(),108)`	hh:mm:ss
`SELECT REPLACE(CONVERT(char(8),GETDATE(),108),':','')`	hhmmss
`SELECT CONVERT(char(23), GETDATE(), 121)`	yyyy-mm-dd hh:mm:ss.fff
`SELECT CONVERT(char(19), GETDATE(), 121)`	yyyy-mm-dd hh:mm:ss
`SELECT CONVERT(char(10), GETDATE(), 121)`	yyyy-mm-dd
`SELECT REPLACE(REPLACE(REPLACE(CONVERT(varchar(19), GETDATE(), 121),'-',''),':',''),' ','_')`	yyyymmdd_hhmmss
`SELECT REPLACE(CONVERT(varchar(19), GETDATE(), 121),'-','')`	yyyymmdd hh:mm:ss

Discover Unicode Characters Available in SQL Server

The following query will help you look for that obscure report character, or possibly just stir your curiosity as to what is available via TSQL.

SET NOCOUNT ON;
-- table variable to hold results
DECLARE @t TABLE (code_number int,code_character nvarchar(3));
-- variable for numbers to check
DECLARE @i int;
--insert into @t
SET @i = 1;
WHILE @i < 66000
BEGIN
INSERT INTO @t (code_number,code_character)
SELECT @i, nchar(@i);
SET @i = @i + 1;
END
--get the results
SELECT * FROM @t 
WHERE 1=1
AND code_character IS NOT NULL
AND code_character <> '';

SQL Server Premium Assurance

With Extended support for SQL Server 2008/R2 ending on 9th July 2017 Microsoft has announced a new Premium Assurance support offering.

The Premium Assurance will allow for Security updates and bulletins rated ‘critical’ and ‘important’ for an additional 6 years after Extended support ends.

_{(Source: Microsoft)}

Eligible SQL Server products currently are:

SQL Server 2008/R2 Standard, Datacenter, and Enterprise
SQL Server 2012 Standard and Enterprise
SQL Server 2014 Standard and Enterprise
SQL Server 2016 Standard and Enterprise
Key notes include:
SQL Server Premium Assurance is purchased as an Add-ons for an ‘Active’ Software Assurance on the SQL Server.
SQL Server Premium Assurance offering must be purchased for each SQL Server to be covered before the version of SQL Server goes out of support i.e to support SQL Server 2008 version, you’ll have upto June 2019 to purchase the offering.
Software Assurance must have been purchased using one of the four licensing programs and enrollments: Enterprise Agreement, Enterprise Agreement Subscription, Enrollment for Education Solutions, and Server and Cloud Enrollment. If not, then Premium Assurance will not be an option for you.
Existing Client Access Licenses (CALs) must be covered by an active Software Assurance license, but SQL Server Premium Assurance Add-on licenses for CALs are not required.
You may reduce licenses or drop the offering completely at the time of your Software Assurance renewal only.
When you purchase Premium Assurance dictates what you’ll pay as shown in the below table:

_{(Source: Microsoft)}

Is it worth it? Possibly for shops that can’t move from SQL Server 2008/R2 and require piece of mind and/or to meet regulatory compliance.

What’s up with SQL Server 2016 Evaluation Edition?

I’m not sure if you have noticed, but when you install SQL Server 2016 Evaluation Edition you are offered to install one of the following free editions – Evaluation, Developer or Express.

This raises the question why would you want to use Evaluation Edition?

Evaluation Edition has a 180 day expiration while Developer Edition has no expiration:

Developer Edition has the same supported version upgrade paths as Evaluation Edition:

Ref: https://msdn.microsoft.com/en-nz/library/ms143393.aspx

As per Developer Edition, the Evaluation Edition licensing indicates that Production workloads are not permitted:

Now there may be a good reason(s) to use Evaluation Edition instead of Developer Edition to get you going, but I just can’t think of any at this stage.

Don’t ask why ‘SQL Server 2016 Business Intelligence’ is showing as an option :) Possibly it will be revived in some form as an upgrade path for existing SQL Server 2014 Business Intelligence users.

AlwaysOn Availability Groups Designs

Here are some thoughts on how AlwaysOn Availability Groups on either a single or multi-subnet should be designed if 2 hosts are used.

Only the Host IP address is manually set in Network Connections on the Host and it typically has NIC teaming for redundancy.

The AG Listener and WSFC IP addresses can be thought of as virtual and are automatically created in DNS as part of their setup.

SQL Server 2016 Retirement of x86 (32 bit) Instance Features

The current SQL Server 2016 Release Notes indicate that the SQL Server x86 (32 bit) features have come to an end.

This step was inevitable and was just a matter of when, rather than if, it would occur.

Retirement of x86 Instance Features

The x86 (32 bit) version of SQL Server 2016 Setup no longer includes instance features. The following Instance Features are now available only in the x64 (64 bit) version of SQL Server Setup:

•Database Engine

•Analysis Services

•Reporting Services – Native Mode

The x86 Setup is still available for installing tools and other shared Features.

Instance features installed from previous CTP releases of SQL Server 2016 will no longer be updated. It is recommended that previous CTP x86 installations are uninstalled and Instance Features are reinstalled using the x64 version of SQL Server 2016 Setup. Previous x86 installations with only Shared Features will continue to upgrade without issue.

WMI Performance Adapter service

You may see the following error events regularly in your Windows Application Event Log:

The Open Procedure for service “WmiApRpl” in DLL “C:\Windows\system32\wbem\wmiaprpl.dll” failed. Performance data for this service will not be available. The first four bytes (DWORD) of the Data section contains the error code.

The Open Procedure for service “BITS” in DLL “C:\Windows\System32\bitsperf.dll” failed. Performance data for this service will not be available. The first four bytes (DWORD) of the Data section contains the error code.

One possible solution to resolve them is to check the WMI Performance Adapter service. If this service disabled, change it to have a start type of Manual and see if that resolves the issue. It is noted that even with this service enabled, you may still see these errors shortly after a restart of the server.

SQL Server and Transparent Data Encryption (TDE)

The following walks through:

Setting up Transparent Data Encryption (TDE) for a database
Restoring a TDE database
Removing TDE from a database
Removing TDE created objects

Note: This feature is currently only available with SQL Server Enterprise Edition, so you may never get to use it.

If this is your first TDE setup on a SQL Server it is best that you set aside 4 strong passwords for the various objects we will interact with through this example e.g.

Service Master Key backup file: br_y2s6tR?3taket?3@uvUk6
Database Master Key: X2t$Ay&7rapHA!?jE3uW3taJ
Database Master Key backup file: f3av#vamEVemesaMuWr7sWuP
Certificate Private Key backup file: te5pepUr*n$PhexE2!a2r@#r

Note: The only password above you will always require to restore a TDE database on another instance is the Certificate Private Key backup file password. I’m covering the others as you never know when you may need them.

If you already have TDE databases on your instance you will likely not need to create any further passwords.

Setting up Transparent Data Encryption (TDE) for a database

Firstly it is good to know that TDE typically relies on:

a Database Encryption Key (one key is created in each TDE database) and is associated with;
a Certificate (only one is created for the whole instance and exists in the master database), this Certificate is associated with;
a Database Master Key (only one is created for the whole instance and exists in the master database) and this Database Master Key relies on;
a Service Master Key (also only one for the whole instance and it exists in the master database).

Knowing the above and as a best practice, let’s make sure we backup the Service Master Key which I suspect you may never have done before.
Every SQL Server instance has a Service Master Key as shown below:

SELECT * FROM master.sys.symmetric_keys;

To backup the Service Master Key you would use:

BACKUP SERVICE MASTER KEY TO FILE = 'C:\SQLBackups\TDE\SQL01D_service_master_key'
ENCRYPTION BY PASSWORD = 'br_y2s6tR?3taket?3@uvUk6';

Next we will check if a Database Master Key exists in the master database using the following code and as shown below it does not exist:

SELECT * FROM master.sys.symmetric_keys;

If you have an entry called ##MS_DatabaseMasterKey## you can skip this step.

As in this case it does not exist we will create it using the following and we can now see the Database Master Key in the master database:

USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'X2t$Ay&7rapHA!?jE3uW3taJ';
SELECT * FROM master.sys.symmetric_keys;

Now we know it exists and as best practice let’s make a backup of the key:

USE master;
BACKUP MASTER KEY TO FILE = 'C:\SQLBackups\TDE\SQL01D_master_key'
ENCRYPTION BY PASSWORD = 'f3av#vamEVemesaMuWr7sWuP';

Now to create the TDE Certificate in the master database which will be associated with the Database Master Key:

USE master;
CREATE CERTIFICATE SQL01D_Certificate WITH SUBJECT = 'SQL01D Certificate', EXPIRY_DATE = '20200131';
SELECT * FROM master.sys.certificates;

As this Certificate is typically the only object, besides the TDE database, you will need to recover on another instance, you will need to definitely create a backup of it and store it safely for future use.

As shown below this will create 2 files and you will require them both to restore the Certificate:

USE master;
BACKUP CERTIFICATE SQL01D_Certificate TO FILE = 'C:\SQLBackups\TDE\SQL01D_certificate.cer'
WITH PRIVATE KEY ( FILE = 'C:\SQLBackups\TDE\SQL01D_certificate.pvk'
,ENCRYPTION BY PASSWORD = 'te5pepUr*n$PhexE2!a2r@#r');

Now to create a Database Encryption Key for our database which will use TDE:

USE AdventureWorks2014;
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE SQL01D_Certificate;

SELECT DB_NAME(database_id) AS database_name, encryption_state 
FROM sys.dm_database_encryption_keys; -- encryption_state should say 1 (Unencrypted)

Finally we enable TDE encryption in the database. As shown below, the tempdb database will also be encrypted automatically:

USE AdventureWorks2014;
ALTER DATABASE AdventureWorks2014 SET ENCRYPTION ON;

-- encryption_state 1 = Unencrypted, 2 = Encryption in progress, 3 = Encrypted
SELECT DB_NAME(database_id) AS database_name, encryption_state, percent_complete 
FROM sys.dm_database_encryption_keys;

For large databases in may take some time for the database to become fully encrypted. The progress can be checked with:

-- encryption_state 1 = Unencrypted, 2 = Encryption in progress, 3 = Encrypted
SELECT DB_NAME(database_id) AS database_name, encryption_state, percent_complete 
FROM sys.dm_database_encryption_keys;

Restoring a TDE database

Now you have a TDE enabled database and the time arrives to restore the database onto another instance.

You are going to need 4 things to restore your database:

The database backup file(s) to restore the database
The Certificate cer file
The Certificate pvk file
The password used to encrypt the Certificate Private Key backup file

Firstly check that the Database Master Key exists in the master database.

As below, only a Service Master Key exists so we will need to create a Database Master Key:

SELECT * FROM master.sys.symmetric_keys;

If you have a Database Master Key you can skip this step. The password should be strong and backup the Database Master Key once created as previously demonstrated. The password does not have to be same as the original server.

USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'BanetrAje&$ATReMuspA*U2e';
SELECT * FROM master.sys.symmetric_keys;

Copy the Certificate cer and pvk files to the location which holds your database mdf files. You could place these files anywhere your SQL Server can access them, but this location should typically always work.

Now restore the Certificate:

USE master;
CREATE CERTIFICATE SQL01D_Certificate
FROM FILE ='C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\SQL01D_certificate.cer'
WITH PRIVATE KEY(FILE='C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\SQL01D_certificate.pvk'
,DECRYPTION BY PASSWORD='te5pepUr*n$PhexE2!a2r@#r');

SELECT * FROM master.sys.certificates;

Finally the TDE database can be safely restored and TDE will still be in use:

-- encryption_state 1 = Unencrypted, 2 = Encryption in progress, 3 = Encrypted
SELECT DB_NAME(database_id) AS database_name, encryption_state, percent_complete 
FROM sys.dm_database_encryption_keys;

Removing TDE from a database

For whatever reason, you now need to remove TDE from a database.

Firstly, disable encryption on the database:

USE AdventureWorks2014;
ALTER DATABASE AdventureWorks2014 SET ENCRYPTION OFF;

-- encryption_state 1 = Unencrypted, 3 = Encrypted, 5 = Decryption in progress
SELECT DB_NAME(database_id) AS database_name, encryption_state, percent_complete 
FROM sys.dm_database_encryption_keys;

This may take some time and you can continue once the database has an encryption state of 1 (Unencrypted).

USE AdventureWorks2014;

-- encryption_state 1 = Unencrypted, 3 = Encrypted, 5 = Decryption in progress
SELECT DB_NAME(database_id) AS database_name, encryption_state, percent_complete 
FROM sys.dm_database_encryption_keys;

Once the database is fully decrypted you lastly need to remove the Database Encryption Key using:

USE AdventureWorks2014;
DROP DATABASE ENCRYPTION KEY;

-- encryption_state 1 = Unencrypted, 3 = Encrypted, 5 = Decryption in progress
SELECT DB_NAME(database_id) AS database_name, encryption_state, percent_complete 
FROM sys.dm_database_encryption_keys;

Once TDE has been removed from the last database, you will notice that tempdb is still encrypted.The tempdb database will only be decrypted when the SQL Server instance is restarted.

Removing TDE created objects

To tidy up a SQL Server instance once TDE is no longer be used, the following steps are used.

Note: Ensure you have a secure copy of any created TDE Certificate(s) in case you need to restore a previously TDE encrypted backup. Backups were covered previous.

First identify the Certificate to remove using:

SELECT * FROM master.sys.certificates;

Remove the Certificate using:

USE master;
DROP CERTIFICATE SQL01D_Certificate;
SELECT * FROM master.sys.certificates;

Finally identify and drop the Database Master Key if you know it is no longer used.

Below shows the Database Master Key exists in the master database:

SELECT * FROM master.sys.symmetric_keys;

The following removes the Database Master Key and leaves the Server Master Key which remains on the instance:

USE master;
DROP MASTER KEY;
SELECT * FROM master.sys.symmetric_keys;

SQL Server and Kerberos

As a DBA you’ll be wanting to promote the use of Kerberos authentication rather than NTLM authentication.

DBAs don’t have to be concerned with the whole Kerberos chain, rather you’ll primarily need to ensure your instances are able to service Kerberos authentication requests.

Let’s get started.

Using Sql Server Configuration Manager, look at the Protocols being used by your instance(s).

As shown below, this server has 2 instances, a default and a named instance called NI1.

Both instances are configured to listen on Shared Memory, Named Pipes and TCP/IP.

Verify which TCP/IP port the instance is listening on.

In this example the default instance is listening on TCP port 1433.

The named instance NI1 is listening on TCP port 2433.

NOTE: For named instances you’ll want to keep away from using TCP Dynamic Ports as a restart of the SQL Server service may impact your Kerberos authentication.

Next, check which service account the instances are using for the SQL Server service.

Below we can see the default instance is using MYDOMAIN\zSQL01D_SS and the named instance NI1 is using MYDOMAIN\zSQL01D_SS1.

Kerberos relies on a Service Principal Name (SPN) to identify the SQL Server instance.

You would use the following syntax to verify if these services have a SPN associated.

setspn /L MYDOMAIN\zSQL01D_SS
setspn /L MYDOMAIN\zSQL01D_SS1

NOTE: I’m using a / (forward slash) instead of – (dash) as you may find you run into issues if you copy/paste a setspn command using a dash.

As shown, currently the accounts don’t have any SPNs associated so Kerberos authentication cannot be used.

To verify this, I’ve RDPed to another server SQL02D and then connected to the default instance on SQL01D using the TCP/IP protocol and run the following command:

SELECT net_transport,auth_scheme
FROM sys.dm_exec_connections
WHERE session_id = @@spid

TIP: Using tcp: in front of the instance name forces TCP/IP and hence the connection below shows the use of tcp:SQL01D

TIP: Using np: in front of the instance name forces use of Named Pipes and hence the connection below shows the use of np:SQL01D

So, we know Kerberos is not available and we are using NTLM.

To add a SPN to the service accounts, you’ll need a Domain Administrator to run the following commands.

NOTE: The Domain Administrator, does not need to be on the SQL server as the command simply registers the SPN in AD against the service account.

We’ll start with setting the most common TCP/IP protocol SPN.

setspn /S MSSQLSvc/SQL01D.MyDomain.local:1433 MYDOMAIN\zSQL01D_SS
setspn /S MSSQLSvc/SQL01D.MyDomain.local:2433 MYDOMAIN\zSQL01D_SS1

Once the Domain Administrator has run the above commands you can verify that you now have a SPN associated with the service accounts.

setspn /L MYDOMAIN\zSQL01D_SS
setspn /L MYDOMAIN\zSQL01D_SS1

NOTE: You don’t have to restart the SQL Server service for the SPN to come into effect.

Now if we retest a TCP connection you should see KERBEROS authentication being used.

SELECT @@SERVERNAME AS instance,net_transport,auth_scheme
FROM sys.dm_exec_connections
WHERE session_id = @@spid

The Named Pipes will still show NTLM.

Now to enabled Kerberos for Named Pipes the Domain Administrator will need to run the following:

setspn /S MSSQLSvc/SQL01D.MyDomain.local MYDOMAIN\zSQL01D_SS
setspn /S MSSQLSvc/SQL01D.MyDomain.local:NI1 MYDOMAIN\zSQL01D_SS1

Once the Domain Administrator has run the above commands you can verify the SPN exist.

setspn /L MYDOMAIN\zSQL01D_SS
setspn /L MYDOMAIN\zSQL01D_SS1

NOTE: Again no restart of the SQL Server service is required for the instance to take advantage of the SPN.

Now when you retry your Named Pipes test you should see Kerberos in use.

So as above, enabling Kerberos authentication is a fairly straight forward process.

Finally, if you find that a SPN needs to be removed for some reason i.e. a port change, the setspn command S is simply replaced with a D and again this has to be run by a Domain Administrator e.g.

setspn /D MSSQLSvc/SQL01D.MyDomain.local:2433 MYDOMAIN\zSQL01D_SS1

Using PowerShell to verify if a remote TCP port is accessible

A handy tip for any DBA out there who would like to check if a remote TCP port can be connected to but they don’t have access to telnet.

The catch is that you require a minimum of Windows 8.1, Windows PowerShell 4.0 or Windows Server 2012 R2. Also, notice I didn’t say check a remote UDP port.

You can check your PowerShell version using:

$PSVersionTable.PSVersion

Simply open a PowerShell prompt and using Test-NetConnection specify the destination server and TCP port e.g.

Test-NetConnection -ComputerName SQLServer01 -Port 1433

If that is too long to type, you can use the alias:

TNC -ComputerName SQLServer01 -Port 1433

If you don’t want all that information use:

TNC -ComputerName SQLServer01 -Port 1433 -InformationLevel Quiet

You may receive a Ping TimedOut warning but the TcpTestSucceeded is True, which seems confusing.

This simply means that the ICMP Ping is being blocked. Check with your System Administrator if they would like Ping capability enabled.