SSMS Running remotely

To run SSMS remotely:

runas /netonly /user:domain\username “C:\Program Files (x86)\Microsoft SQL Server\130\Tools\Binn\ManagementStudio\Ssms.exe”

E.g.

runas /netonly /user:WIN-P9OQ9GF24HV\justdave “C:\Program Files (x86)\Microsoft SQL Server\130\Tools\Binn\ManagementStudio\Ssms.exe”


SQL Server SSMS and SSDT now GA

SQL Server

SSMS now generally available

https://msdn.microsoft.com/en-us/library/mt238290.aspx

SQL Server Data Tools (SSDT) for Visual Studio 2015 is now generally available.

https://msdn.microsoft.com/en-us/library/mt204009.aspx

 


System.Transactions (and AlwaysOn Availability Groups)

With SQL Server 2016 AlwaysOn Availability Groups (on Windows Server 2016 TP4) supports MSDTC which can be used for transactions which access more than 1 database.

However there are Transactions which do not use MSDTC.

Within the .NET Framework there is System.Transactions namespace https://msdn.microsoft.com/en-us/library/system.transactions%28v=vs.110%29.aspx

As per https://msdn.microsoft.com/en-us/library/ms229978%28v=vs.110%29.aspx these implement a Transaction Manager which can handle transactions within SQL Server.

System.Transactions only SOMETIMES escalates to MSDTC!

“As long as the System.Transactions infrastructure handles..at most one durable resource that supports single-phase notifications, the transaction remains in the ownership of the System.Transactions infrastructure”

The question then is with SQL Server 2016 AlwaysOn Availability Groups (on Windows Server 2016 TP4) and a transaction which uses more than 1 database

a) Does SQL Server 2016 AlwaysOn Availability Groups support single-phase notifications?

b) With SQL Server 2016 AlwaysOn Availability Groups does more  than 1 single database count as 1 durable resource or more than 1 durable resource?

As per http://msdn.microsoft.com/en-us/library/ms229979.aspx tracing can be used to determine who promotes a transaction and why.


Checking SQL Server connectivity -PortQry/

Useful tools for checking SQL Server connectivity – PortQry and PortQryUI

http://www.smooth1.co.uk/mssql/portqry.html


SQL Server and Windows Cluster Shared Volumes

Introduction

Windows Cluster Shared Volumes are a disk volume which acts as a clustered filesystem i.e. read-write on all nodes.

In fact, in Disk Management on the cold side of the cluster,the Volume is tagged with ‘(CSVFS)’

SMB 3.0 is used behind the scenes

How do I setup a Cluster Shared Volume?

See http://www.smooth1.co.uk/installs/dbinstalls.html#3.1.14

What if a cluster node goes down?

No storage failover is needed when a node goes down

  • sql server has no cluster dependency on the disks anymore!

There is still a co-ordinator node for writes.

What if all storage paths to a node go down?

If this is the co-ordinator node, another co-ordinator node is chosen and i/o rediection occurs. See http://www.smooth1.co.uk/mssql/mssql_csv_iorediection.html

How do Cluster Shared Volumes work internally?

http://blogs.msdn.com/b/clustering/archive/2013/12/02/10473247.aspx

Cluster Shared Volume Diagnostics

http://blogs.msdn.com/b/clustering/archive/2014/03/13/10507826.aspx

Cluster Shared Volume Performance Counters

http://blogs.msdn.com/b/clustering/archive/2014/06/05/10531462.aspx

Cluster Shared Volume Failure Handling

http://blogs.msdn.com/b/clustering/archive/2014/10/27/10567706.aspx


SQL Server 2016 CTP 3.1

Plenty of new feaures in SQL Server 2016 CTP 3.1

DB Engine

Memory optimized table gain support for LOBs,unique indexs and indexes on nullable columns

CREATE TABLE dbo.A
(
A_No int IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,
A_Id INT NOT NULL,
A_Units INT NULL,
A_value INT,
A_Description NVARCHAR(MAX), — LOB Support
INDEX i1 NONCLUSTERED (A_Units), — Index on Nullable Column
INDEX i2 UNIQUE NONCLUSTERED (A_Value) — Unique Index
) WITH (MEMORY_OPTIMIZED=ON, DURABILITY=SCHEMA_ONLY);

Analysis Services Tabular

  • Tabular models can be upgraded to SQL Server 2016 (Compatability Level 1200)
  • In October a new modelling language was released for Tabular Models using JSON as part of SSDT fo Visual Studio 2015
  • The JSON document can now be editing using the Visual Studio JSON Editor that includes syntax highlighting and validation. The free Visual Studio Community Edition 2015 or higher is needed
  • Roles can be created for Tabular Models at Compatability Level 1200

BI

  • SQL Server PowerPivot and Reporting Services/Power View for SharePoint 2016, requires SharePoint Server 2016 Beta 2
  • Office Online Server Preview also needed, SharePoint Server no longer includes Excel Services
  • Excel Services, now called Excel Online Server, is now only available with Office Online Server.

Always On Encrypted – Generating Certificates and Column Encryption Key ENCRYPTED_VALUE

NOTE: This needs .NET Framework 4.6 to be installed!

To use Always On Encrypted we:

  • Create a certificate with required properties to be used with Always On Encryption
  • Create a column master key definition
  • Create a column encryption key using an encrypted value

How do we script this?

First we create a certificate in Windows, outside of SQL Server!

We use powershell to create a self-signed Certificate with the required options to be an certificate for Always On Encryption:

New-SelfSignedCertificate -CertStoreLocation Cert:\CurrentUser\My -DNSName “CN=Always Encrypted Certificate” -KeyUsage KeyEncipherment -TextExtension @(“2.5.29.37={text}1.3.6.1.5.5.8.2.2,1.3.6.1.4.1.311.10.3.11”) -provider “Microsoft Strong Cryptographic Provider”

which returns the Thumbprint property of the new Certificate (e.g. 18AD6F1E32BED9C299B9AE91FEB9AA0CEB87ABE9)

CertStoreLocation can be

  • Cert:\CurrentUser\My – User level
  • Cert:\LocalMachine\My – Machine level

As per https://technet.microsoft.com/en-us/library/hh848633.aspx the options include:

2.5.29.37 – Enhanced Key Usage includes

1.3.6.1.5.5.8.2.2 – IP security IKE intermediate

1.3.6.1.4.1.311.10.3.11 – Key Recovery

The Thumbprint can also be checked via certmgr.msc (CurrentUser Certificates) or certlm.msc (Local Machine Certifcates) or

dir Cert:\CurrentUser\My

dir Cert:\LocalMachine\My

Secondly in SQL Server we create a column master key definition using certificate provider and thumbprint for our certificate

As per https://msdn.microsoft.com/en-us/library/mt146393.aspx the provided key store provider is MSSQL_CERTIFICATE_STORE also a custom keystore provider can be created.

CREATE COLUMN MASTER KEY DEFINITION CMK1
WITH (
KEY_STORE_PROVIDER_NAME = N’MSSQL_CERTIFICATE_STORE’,
KEY_PATH = N’CurrentUser/My/18AD6F1E32BED9C299B9AE91FEB9AA0CEB87ABE9′
);

Thirdly we generate a column encryption key encrypted value using powershell

As per https://msdn.microsoft.com/en-gb/library/mt146372.aspx the plaintext value should be 256 bits (32 bytes)

$cmkprov = New-Object System.Data.SqlClient.SqlColumnEncryptionCertificateStoreProvider

$InBytes = New-Object Byte[] 32

$OutBytes = New-Object Byte[] 32

$RNG = New-Object System.Security.Cryptography.RNGCryptoServiceProvider

$RNG.GetBytes($InBytes,0,8)

[System.BitConverter]::ToString($InBytes)

// AC-78-F7-57-87-37-D7-B8-00-00-00-00-00-00-00-00-00-00-00-00-00-00-00-00-00-00-00-00-00-00-00-00

$OutBytes = $cmkprov.EncryptColumnEncryptionKey(“CurrentUser/My/18AD6F1E32BED9C299B9AE91FEB9AA0CEB87ABE9″,”RSA_OAEP”,$InBytes)

“0x” + [System.BitConverter]::ToString($OutBytes) -Replace ‘[-]’,”

// Join this into 1 long line and use to create the column encryption key

0x016E000001630075007200720065006E00740075007300650072002F006D0079002F0031003800610064003600660031006500330032006200650
06400390063003200390039006200390061006500390031006600650062003900610061003000630065006200380037006100620065003900124965
5EE838EC9BB949DB58103D9C77A0650825E8DE23007496279046759EA855CBC4B0D431061DCD0A0FCD7046DB4A55F8A6D3E83A773495299F33B6F07
3F73CD211A65933DACE945522E864AD7933D969944445566E5B90D63FF35F1AD874455C8770D9FE7A02586B49D843F99831B41036018835338D00CC
35F270D1C715A83FA08A3F5211428E5AA565846DBB1E977751E15A6F149043C71F08CC1B1C21B14DAAE641F17B9457BCCF2C0BC843A75FD8EC37D2F
443B91909316D3895D0660D8BB5C7F6B493884C898F9A021ED5A5BE298F06A2D714F538234F92A539DFA4611BE67DA846F6FC656A093FBE5BC70543
B5325A415953FAE66DC8E6DA29DEFB21BE14B3FFF24B37ACED94BD078922E7797A0ACE3F6182F8FCEFDE62C88E9DDF63F638B34148121DDF194657D
763C4BBFA2C748FBEE51C9FC6F6BBE1B440ED2E29FF2A99AD132587A23603D7E951F64C52FA88C30A798AADB5DBDF909E511B6CEBB29180BABFA154
4B6C700D44DBCBE90EB375CE6CE62C8AE906393F0671CA5921F6DB5BD7B6711F85B63FEEA08BBD3E371F1E1C02C147B47AF997E7BCCCC7EF558FB15
83414820DE11A4536EBC337D4A5EDE3F24DD816EEC11E96F1789670CF19E8AF63EABD423803E3E58CB63723E21C28ACC38B3DB98F14157E7AA94D66
EB5B045A232C3C7A0713A9E02E59A85F2FFBB521B6F2ECED1A407C76460A1A6655

Fourth we create the column encryption key using the encrypted value we calculated.

CREATE COLUMN ENCRYPTION KEY CEK1
WITH VALUES
(
COLUMN MASTER KEY DEFINITION = CMK1,
ALGORITHM = ‘RSA_OAEP’,
ENCRYPTED_VALUE = 0x016E000001630075007200720065006E00740075007300650072002F006D0079002F0031003800610064003600660031006500330032006200650064003900630032003900390062003900610065003900310066006500620039006100610030006300650062003800370061006200650039007083AC9696C6C9E594AC69EB68754C3C43F719EA7C18EFBAAB0B51F084E52678D38689AF0B055BEA5798DC64A15D9360D6183EB2111DD708D2AF2777CF683C88460DAE04F59C5B108EBA2C2421F8FC6DC0E8F5AF318D79CD52B0DAE03996214EA61447E44575D023E80DE66FEDA417F62B0502EE534350EEA59429BDDC99A1C97E78E1A5DFC702D35F4E92A8BE0E54515AC2574C2C2D1EB76E495B98E12973006B75990913B47EAA8B675852468F2ED18C20F7DC3B2E263D9378D97DC6B164D1119951DC015FECBDC4B44CF2D20D441B3ED843FFCF4DC2B6CAC3C8FCC131767ABF7CBED95B0D53F809F5CF7554B84900CAFADFBA5AA70EA99E183F213DDA7F91050CD9EC5FD3DC670734C20EE0761438C7803B6225F97D86EBB30451FE0D805C1C2A27A228C6EEF53ED0CC7668CB95B46D026E266651E102634241DEB66BC94500B2D8462E39713259101150C6D2EE7129F5668B301205580A1B4528011D875C706FAF13B658AC61B415CADEC887481B79C270A3DE7A66584123FE1FBFC89768A323210FAEA056F4AC1FFC5727B49581EC8F97F03186EDE5648E209FF0FD5E7A931080B1D69BA9A68D9D54640C13F0482C47F301F7DFD9EB31E96D3EC5466D7E734EEF48CDFE8FA540404C78506AB109A5378CD9D8DA7CB60B91F0A52A6F797624B2AFD26DF1C3A63F9FB5239F67DBB0819F4F54856F316A18244127BBA0AC2D
);

Now we can create a table and encrypt columns using this Column Encryption Key

CREATE TABLE dbo.Client
(
ClientId INT IDENTITY (1,1),
ClientAge INT
ENCRYPTED WITH (ENCRYPTION_TYPE = DETERMINISTIC,
ALGORITHM = ‘AEAD_AES_256_CBC_HMAC_SHA_256’,
COLUMN_ENCRYPTION_KEY = CEK1),
ClientSecret  NVARCHAR(200)
ENCRYPTED WITH (ENCRYPTION_TYPE = RANDOMIZED,
ALGORITHM = ‘AEAD_AES_256_CBC_HMAC_SHA_256’,
COLUMN_ENCRYPTION_KEY = CEK1),
);

The SQL Server Security blog entry where I asked about this is https://blogs.msdn.microsoft.com/sqlsecurity/2015/06/04/getting-started-with-always-encrypted/


Forcing garbage collection with in memory oltp

To force manual garbage collection:

  1. First we check the status of the checkpoint files:

select container_id,state_desc,lower_bound_tsn,upper_bound_tsn from sys.dm_db_xtp_checkpoint_files

Anything that is MERGE TARGET is pending a merge.

In SQL Server 2014 we can run:

sys.sp_xtp_merge_checkpoint_files database_name, @transaction_lower_bound, @transaction_upper_bound

to force a merge, manual merges are not possible in SQL Server 2016!

2. Once the merge is complete we have to run a checkpoint to ensure everything is persisted on disk.

3. Next we need to run a log backup otherwise dm_db_xtp_checkpoint_files.state_desc would be ‘WAITING FOR LOG TRUNCATION’

4. Finally we can force garbage collection using

sys.sp_xtp_checkpoint_force_garbage_collection [[ @dbname=database_name]


19th September 2015 SQL Saturday Denmark Questions.

What editions is replication in?

As per https://msdn.microsoft.com/en-us/library/cc645993.aspx#Replication replication is supported in Standard Edition, this does not include Oracle publishing or Peer to Peer transactional replication.

How can I read the transaction log?

To read the current logs:

SELECT * FROM fn_dblog (NULL, NULL)

The parameters to fn_dblog are StartLSN and EndLSN in 3 part decimal format

To read from a log backup:

SELECT * FROM
fn_dump_dblog (
NULL, NULL, N’DISK’, 1, N’D:\LogBackups\Justdave_Log1.bak’,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT);

The parameters are:

  • Start LSN starting with 0x
  • End LSN starting with 0x
  • Device Type ‘DISK’ or ‘TAPE’ deffault DISK
  • Seqnum – used to indentify a backup within a backup device, usually 1
  • Filename of log backup on disk
  • the name of a backup file in the default backup directory for the SQL Server instance
  • The remaining parameters can be used to specify multiple media families in a media set used for a log backup i.e. the filenames of the remaining stripes if more than 1 stripe was used for the log backup

Warning: As per http://www.sqlskills.com/blogs/paul/using-fn_dblog-fn_dump_dblog-and-restoring-with-stopbeforemark-to-an-lsn/

There is a bug where using these functions to read log information will result in a new hidden SQLOS scheduler being created and up to three threads, which will not go away (and will not be reused) until a server restart. This is fixed in 2012 SP2 and 2014.


Reoganizing SQL Server internal tables

How to reorganize the internal tables in SQL Server is not immediately obvious:

For example to see the internal tables used for Event Notification:

SELECT q.name AS queue_name
,q.object_id AS queue_id
,s.name AS internal_schema_name
,it.name AS internal_table_name
,it.object_id AS internal_table_id
FROM sys.internal_tables AS it
JOIN sys.service_queues  AS  q ON it.parent_id = q.object_id
JOIN sys.schemas AS s ON it.schema_id = s.schema_id
WHERE it.internal_type_desc = ‘QUEUE_MESSAGES’;

queue_name    queue_id    internal_schema_name    internal_table_name    internal_table_id
QueryNotificationErrorsQueue    1003150619    sys    queue_messages_1003150619    1019150676
EventNotificationErrorsQueue    1035150733    sys    queue_messages_1035150733    1051150790
ServiceBrokerQueue    1067150847    sys    queue_messages_1067150847    1083150904

alter index ALL on sys.queue_messages_1003150619 REORGANIZE;

Msg 1088, Level 16, State 9, Line 11
Cannot find the object “sys.queue_messages_1003150619” because it does not exist or you do not have permissions.

Find we have to connect using the DAC – connect as a sysadmin user changing the servername to start with admin:

alter index ALL on sys.queue_messages_1003150619 REORGANIZE;

— Msg 1914, Level 16, State 3, Line 1
— Index cannot be created on object ‘sys.queue_messages_1003150619’ because the object is not a user table or view.

So how do we reorgnize this then?

DBCC DBREINDEX(‘sys.queue_messages_1003150619’)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

NOTE: We included the schema name ‘sys’ in the command!

As per https://msdn.microsoft.com/en-gb/library/ms187799.aspx these internal tables are used for:

QUEUE_MESSAGES

XML_INDEX_NODES

FULLTEXT_CATALOG_FREELIST

FULLTEXT_CATALOG_MAP

QUERY_NOTIFICATION

SERVICE_BROKER_MAP

EXTENDED_INDEXES (such as a spatial index)

FILESTREAM_TOMBSTONE

CHANGE_TRACKING

TRACKED_COMMITTED_TRANSACTIONS