Windows Server Core And SQL Server

Slava Murygin walks through running SQL Server on Windows Server core.  First, the steps for installing SQL Server at the command line:

There are three reasons why Windows Server with Core is better than Server with a GUI:
1. Security: Core has less features. That means less surface for intruders to attack.
2. Management: No unnecessary updates and planned reboots.
3. Resource consumption: Less memory usage and less CPU overhead

Even if you aren’t using Windows Server Core, it’s a good idea to be able to script out your SQL Server installation so you can automate it later (or at least so you don’t forget options and have some servers look different than others).

Second, Slava notes that certain features are not compatible with Windows Server core:

The following error occurred:
You have selected a feature that is not supported on Windows Server Core.
The supported features on Windows Server Core are:
Database Engine Services,
SQL Server Replication,
Full-Text and Semantic Extractions for Search,
Analysis Services,
Client Tools Connectivity,
Integration Services,
and SQL Client Connectivity SDK

That list is a bit more limiting than I’d like, to be honest.

Scripting SQL Server Objects With Powershell

Sander Stad provides a script to export SQL Server objects using Powershell:

Scripting SQL Server objects manually through the SQL Server Management Studio can be a tedious task. Last week I published a script to export database objects with PowerShell. I wanted to take this a little further and create a solution to export SQL Server objects as well.

Because this would be a nice addition to the PSSQLLibmodule, this function is also included in the library from today.

I haven’t tried using this cmdlet yet, but it does look handy.

Credentials And Proxies

Kenneth Fisher shows how to use credentials and proxies to run external objects (like SSIS packages and Powershell scripts) through the SQL Server Agent:

There are purposes for credentials other than a proxy, but for our purposes you are just going to enter an AD username and password. Just to be even more clear, this is an AD/Windows user. Not a sql server login.

In Object Explorer: ServerName -> Security -> Right click on Credentials and select New Credential -> Fill in the Name, Identity and Password fields.

Kenneth’s getting fancy with animated GIFs, and gives us a good walkthrough of this aspect of SQL Agent security.


Ayman El-Ghazali talks Polybase:

HDFS is a distributed file system that works differently than what we’re used to in the Windows OS side of things; the general principle is to use cheap commodity hardware that replicates data in order to account for availability and to prevent loss of data. With that in mind, it makes a great use case to store a lot of data cheaply for archiving purposes or can be used to store large quantities of data that been to be processed in large quantities as well.

For more information please visit:

Now if you want to try it out for yourself, make sure you install the PolyBase Engine (from the SQL Server setup) and feel free to try the modified code sample below.

Polybase is, without a doubt, my favorite SQL Server 2016 feature.  I am excited to put this through its paces in a production environment.

Primary Keys On TVPs And Plan Forcing

Michael J. Swart notes that you cannot force query plans if you’re using a user-defined table type with a non-named primary key constraint:

When defining table variables, avoid primary key or unique key constraints. Opt instead for named indexes if you’re using SQL Server 2014 or later. Otherwise, be aware that plan forcing is limited to queries that don’t use these table variables.

Helpful advice when dealing with user-defiened table types.  Read the whole thing.

Registered Servers

Erik Darling shows how to set up registered servers in Management Studio:

And if you hip and hop over to the Connection Properties tab, you can set all sorts of nifty stuff up. The biggest one for me was to give different types of servers different colored tabs that the bottom of SSMS is highlighted with. It’s the one you’re probably looking at now that’s a putrid yellow-ish color and tells you you’re connected and that your query has been executing for three hours. Reassuring. Anyway, I’d use this to differentiate dev from prod servers. Just make sure to choose light colors, because the black text doesn’t show up on dark colors too well.

In team environments, I’m more a fan of Central Management Servers.

AcquireConnection Failures

Ginger Grant explains what to do when you get AcquireConnection errors:

AcquireConnection method call to the connection manager failed with error code 0xC0202009/0xC020801C

Try as I might, the only thing I was able to do after an hour was periodically change the error code from 0xC0202009 to 0xC020801C. Nothing I did worked. I created a new connection, created a new OleDB Source, changed the Run64BitRuntime to False in Configuration Properties in the Debugging Section of the project execution, set the Data Flow task DelayValidation from False to True. None of these various suggestions that I got from various websites worked at all.  I thought about changing the SSIS Service ID’s execution properties, but since I was running in Debug mode I determined that this would not make any difference, so I abandoned that idea. Nothing worked. The only thing I was able to do was change the error code, not eliminate it. I could log into SQL Server with the same ID and password in my package and run the simple query in the data flow task and return data.  I could preview the data, what I couldn’t do is execute the SSIS package.  Out of desperation I rebooted, which also did nothing.

SSIS security issues.  Gotta love ’em.

Arithmetic With NULL

Kevin Feasel



Kennie Nybo Pontoppidan looks at how NULL values complicate arithmetic operations:

If NULL is used in an in-row expression, the full expression will be NULL. This makes sense for most arithmetic operations, because NULL means unknown.

Here is an example with NULL plus/minus ONE:

There’s a consistency to NULL operations, but it’s sometimes a weird consistency.

Infrastructure Trends

Glenn Berry reports on hardware trends:

Right now, you can purchase extremely capable, high performance server processors with physical core counts between four and twenty-two cores per processor. I am referring to the current 14nmIntel Xeon E5-2600 v4 (Broadwell-EP) and the 22nm Intel Xeon E7-8800 v3 (Haswell-EX) families that both use high bandwidth DDR4 memory.

On March 31, 2016, Intel released the 14nm Xeon E5-2600 v4 family (Broadwell-EP) for two-socket servers. This is a Tick release, building on the current Haswell microarchitecture that has up to 22 physical cores and DDR4 2400 support. This processor will work in existing model servers such as the Dell PowerEdge R730 with a BIOS update, which means that there will be less delay before they are actually available for sale.

A lot of companies aren’t interested in The Cloud, so it’s good to know that hardware vendors are keeping up with on-premises demands.

Increased NCI Key Size

Jos de Bruijn notes that non-clustered index keys may be larger in SQL Server 2016 and Azure SQL Database:

SQL Server 2016 and Azure SQL Database have increased the maximum size for index keys with nonclustered indexes. The new maximum key size for nonclustered indexes is 1700 bytes. The maximum key size for clustered indexes remains 900 bytes.

95-99% of the time, we don’t want to get anywhere near 900 bytes (much less 1700 bytes), but we all have that one edge case.


October 2018
« Sep