CLR strict security

There is a new configuration option in SQL Server 2017 called CLR strict security and it is enabled by default.

Essentially when enabled, it treats SAFE and EXTERNAL_ACCESS assemblies as if they were marked UNSAFE and any assemblies that are not signed will fail to load.

Microsoft recommends that all assemblies be signed by a certificate or asymmetric key with a corresponding login that has been granted UNSAFE ASSEMBLY permission in the master database.

-- to disable
EXEC sys.sp_configure N'show advanced options', N'1';
RECONFIGURE WITH OVERRIDE;
EXEC sys.sp_configure N'clr strict security', N'0';
RECONFIGURE WITH OVERRIDE;
EXEC sys.sp_configure N'show advanced options', N'0';
RECONFIGURE WITH OVERRIDE;
GO

-- to re-enable
EXEC sys.sp_configure N'show advanced options', N'1';
RECONFIGURE WITH OVERRIDE;
EXEC sys.sp_configure N'clr strict security', N'1';
RECONFIGURE WITH OVERRIDE;
EXEC sys.sp_configure N'show advanced options', N'0';
RECONFIGURE WITH OVERRIDE;
GO

Further information can be found here.

Quick look at resumable indexing in SQL Server 2017 CTP2.0

Allow for index to be paused:
ALTER INDEX NCI_c1 on t1 REBUILD WITH (ONLINE=ON, MAXDOP=1, RESUMABLE=ON);
 
View current state using:
SELECT * FROM sys.index_resumable_operations;
image1
Pause the index operation:
ALTER INDEX NCI_c1 on t1 PAUSE;
 
The state_desc now shows PAUSED:
SELECT * FROM sys.index_resumable_operations;
image2
To resume the index operation:
ALTER INDEX NCI_c1 on t1 RESUME;
 
To abort the index operation:
ALTER INDEX NCI_c1 on t1 ABORT;
 
Full command syntax and restrictions can now be found here

SQL Server vNext = SQL Server 2017

Yes SQL Server vNext officially has the name SQL Server 2017.
 
CTP 2.0 is now available to download and further details on the announcement can be found here
 
Self tuning databases? Sounds interesting or possibly a cause for concern. Hopefully these features have an off switch in case SQL starts optimizing in the wrong direction :)
 
“New in SQL Server 2017, we’re adding the Adaptive Query Processing family of intelligent database features. These features automatically keep database queries running as efficiently as possible without requiring additional tuning from database administrators.”
 
What does surprise me is the rate of new version releases.
 
Progress is good, but SQL Server 2012 -> 2014 -> 2016 -> 2017, this really starts to raise a dilemma for businesses/developers/DBAs as to which version should they be using/developing/training for. Throw Linux and the cloud into the mix and well it just gets even more interesting.
 
Businesses like stability and these new versions can introduce risk which is exactly what businesses want to avoid.
 
To me, this release cycle has to slow down or it could end badly for all parties. Although in saying that; DBAs would become more in demand as resolving incompatibility and version issues are likely to significantly increase.

Some fun with TSQL

As a bit of fun, the following code was run in SSMS with:

  • Results to Text
  • ‘Include column headers in the result set’ unticked
SET NOCOUNT ON
DECLARE @i int;
SET @i = 1;
WHILE (@i < 20)
BEGIN
SELECT '                     '
SET @i = @i +1
END
SELECT '          *          '
SELECT '         ***         '
SELECT '        *****        '
SET @i = 1;
WHILE (@i < 20)
BEGIN
SELECT '                     '
SET @i = @i +1
END

akawn

Bytes Per Cluster setting for Linux?

Currently at this time there is no information as to what/how the Linux drives should be formatted for optimal SQL Server performance. Hopefully this information will become available prior to the scheduled product release later this year.
 
It’s generally well known that on Windows you’d typically set the Bytes Per Cluster to 65536 for SQL Server databases or 32768 for SQL Server Analysis Services.
 
This can be checked using fsutil fsinfo ntfsinfo i.e. fsutil fsinfo ntfsinfo d:
 
Below shows a C & D drive being checked with the C set to default 4096.
image1
If you are interested in how the Linux disks are formatted, you could try use the following (steps were performed on Ubuntu)
 
Firstly identify the available drives using sudo fdisk -l
image2
Secondly for each drive review the settings using sudo tune2fs -l i.e. sudo tune2fs -l /dev/sda1
image3

Hold your horses, which SNAC do you need?

Over on SQL Server Release Services, they’ve released details on the various SQL Server Native Client (SNAC) downloads currently available.
 
Talk about complexity; but hopefully it will help when you are next in need of a SNAC.
 
On a side note ‘Microsoft has announced the deprecation of the SQL Server Native Client OLE DB provider, and that SQL Server 2012 is the last version of SQL Server to support the provider’ .
 
Therefore you should be looking towards use of ODBC moving forward.

How to populate a variable using a CTE

The following table called t1 is used for the examples:
 
image1
 
If the CTE (Common Table Expression) returns a single value then you could use:

--variable
DECLARE @v char(1);

--CTE
;WITH CTE
AS
(
	SELECT c2
	FROM t1
	WHERE c1=2
)
SELECT @v = c2
FROM CTE;

--verify correct
SELECT @v AS the_value;

image2
If the CTE will return multiple values, and say you only want the first descending ordered one, then you could use:

--variable
DECLARE @v char(1);

--CTE
;WITH CTE
AS
(
	SELECT c2
	FROM t1	
)
SELECT TOP 1 @v = c2
FROM CTE
ORDER BY c2 DESC;

--verify correct
SELECT @v AS the_value;

image3
 
You can also store CTE values into table variables, but I’ll cover that in another post.