Tuesday, December 20, 2016

Four newest T-SQL features.

In the very beginning I want to warn you:
These new language features are not available in the SQL Server product yet. Only in Community Technical Preview. Moreover, the Community Technical Preview (CTP 1.1) is available for Linux Edition of SQL Server.

Official notes on the product: https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-whats-new

So, here they are:

Four newest T-SQL features:

#1. Trimming.

Count, how many times in your SQL programming life you've used the construction "RTRIM(LTRIM(...))" to get rid of empty spaces at the beginning and at the end of a string.
How many times you have wished to have a simple function "TRIM" to do the job?
And here it is:

DECLARE @C CHAR(10) = '   ABC    ';
PRINT 'My new nice Trimming: "' + TRIM(@C) + '"';

#2. String Concatenation.

How do you usually do string concatenation in T-SQL?
Probably something like this:
PRINT @A + ';' + @B + ';' + @C;

Now you can do it another way, using function "CONCAT_WS":
PRINT CONCAT_WS(';', @A, @B, @C);

You might ask me "Why do we need this?". My guess would be "-To simplify the invasion."
In other words make a migration from MySQL to SQL Server less painful.

#3 String Aggregation.

That is the really good one.
Since Microsoft introduced XML support in SQL Server, the most common string concatenation technique was use of "XML PATH('')" like this:

(SELECT ', ' + name FROM master.sys.tables

Now you can aggregate your strings by using function "STRING_AGG":
SELECT STRING_AGG(name, ', 'FROM master.sys.tables;

Edit: 2017-01-26
Now you can aggregate your strings by using function "STRING_AGG" with ordering by certain field:
FROM master.sys.tables;

#4 Translation.

Microsoft introduced new T-SQL function "TRANSLATE", which simply replaces characters within a string. For a string you want to do a replacement for you have to specify two series of characters: one is "originals" and second is "replacements". Both these series must have equal length.

PRINT TRANSLATE ( 'This is my unencrypted message',
'mnopqrstuvwxyzabcdefghijkl' );

The result will be "ftue ue yk gzqzodkbfqp yqeemsq".
If we replace the original string by the result and switch second and third parameters we can get our original string as a result:

PRINT TRANSLATE 'ftue ue yk gzqzodkbfqp yqeemsq',
'abcdefghijklmnopqrstuvwxyz' );

Looks awesome, isn't it?
However, the restriction that second and third parameters are must be same size makes this function not usable for conversion of text for XML, where we have to replace special 5 XML characters: "&'<">".
Also, if we want to keep consistency of capitalization, our database must be in case sensitive collation.

Friday, December 16, 2016

SQL Server on Linux in 2016

This Blog post highlights only current state and future expectations of Microsoft's "SQL Server on Linux" project in EOY 2016.


As you've already heard, Microsoft had made a daring move and decided to go to the "Enemy" territory with their flagship product SQL Server.
That is actually not the first attempt of Microsoft to go with Linux. The most known known first invasion was with Linux based Hadoop clusters in Microsoft Azure: https://docs.microsoft.com/en-us/azure/hdinsight/hdinsight-hadoop-linux-tutorial-get-started

So the current timeline for SQL On Linux looks like this:
- March, 2016 - first public announcement at a conference in NYC and starting it's private preview and testing;
- November, 2016 - After several monthly releases preview became public with CTP 1 (Community Technology Preview);
- Middle 2017 - Production market target for the product;

Why SQL Server on Linux? Our expectations.

At first will take a look at the pricing of one mid-size SQL Database server:
Windows 2016 Datacenter license cost (16 cores):           $6,155
SQL Server 2016 Enterprise license cost (16 cores):        $114,048
Total cost for 16 cores :                                                    ~$120K

I fill like it is pretty expensive. What do you think?


So, businesses currently have following "free"* alternatives based on Linux:

* I put word "free" in quotes because it does not include all associated risks.

Main risks of "free" solutions:

1. Security.
2. Scalability.

If you are small company, which does not store any customers' data in a database you might be OK with security. However, if you expect your line of business to grow 10 times in the next 5 years?
Will current free Linux databases be sustainable enough to support your database then?

Think about all development effort you put in free database product and about an effort to migrate that database to enterprise level with Oracle or Microsoft.

So, the solution is simple: Go with SQL Server on Linux!

SQL Server on Linux provides all necessary security and it is scalable to the enterprise level without any extra-migration programming effort.


(Disclaimer: I'm not working for Microsoft and do not have a Crystal Ball. All my expectations are only MINE and can go as knowledgeable suggestions. You can disagree or agree and support me)

So, I'd expect Microsoft to provide free version of SQL Server on Linux (Express/Small Business) with following limitations:
  1. CPU - up to 4 cores;
  2. Memory - up to 16 Gb;
  3. DB Size - up to 100 Gb;

I know, adepts of Microsoft will say: "-We can't give such a wonderful product for free!".

For that I have a justification from Oracle:

In year of 2000, when Microsoft gave away free SQL Server Desktop edition called MSDE, the limit in database size was 2GB!
Look at the diagram and compare it with 2016 limit in 10 GB.
So, if we are targeting going into production in 2017 with most of customer project deployments started in 2018, current SQL Express limitations will be absolute No-No for any business case scenario.

That might be against Microsoft line of business to give away free business product, but that move would make SQL Server #1 database in the world. People would forget about any other free alternatives.

I think only one argument might stop Microsoft from following that scenario. As I mentioned earlier, domination of Microsoft in the Database segment of the world market would make it a monopoly with all following consequences. 

Currently Supported features:

Database engine:

  • In-Memory OLTP; 
  • Updatable Columnstore Indexes; 
  • JSON Support;
  • Query Store; 

High Availability:

  • Failover clustering;


  • Only SQL Server authentication is currently available; 
  • Row Level Security; 
  • Dynamic Data Masking;


  • SQL Server Service;

SQL Server on Linux Target Audience

If my expectations become true in the middle of 2017 than here is a small list of beneficiaries: 
  • Small businesses – Low cost;
  • New businesses – Low cost with big potential growth;
  • Enterprise businesses – Reduced cost on multiple servers;
  • SQL Server oriented teams;

In the nearest future I'll cover some technicals of SQL Server on Linux.
So, please follow my blog and expect more.

Tuesday, September 13, 2016

Configuring Windows Firewall for SQL Server using PowerShell

You can configure Windows Firewall using GUI or you can ask your sysadmin to do it, but what if you do not have Sysadmin or GUI?
Also, if you have to document your configuration, the PowerShell scripts that exactly what you need.
As it was said: "One line of PowerShell worth 15 GUI screen shoots".

The very first script I want to introduce is the collection of Firewall basic information.
That Script does not collect ALL information, only the most common for handling SQL Server requests.

Description: Returns list of firewall rules, which are protocol set as "TCP" or "UDP" or have set associated application.
That script has the purpose of identifying any relevant rule in the long list.
For instance you might already have a SQL Server related rule, but it is disabled or addresses wrong port or even blocking a port. And you definitely do not want to have collisions nor duplicates.

The Script has list of adjustable filters:
$Direction - Direction of firewall rule: Inbound or Outbound;
$Action - Action rule performs: Allow or Block;
$Enabled - Status of a rule: Enabled - True or False;
$RuleGroup - Group rule has been assigned. By default script uses "$Null" variable, which filters all rules without assigned group. However you can specify a group a name if necessary;
$DisplayName - Name of a rule. By default I use an expression "*SQL*" to search for rules which have word "SQL" in their name. To retrieve all rules us "*". To retrieve any particular rule use rule name.

$Direction = "Inbound"  # Options: "Inbound"/"Outbound"
$Action = "Allow"       # Options: "Allow"/"Block"
$Enabled = "True"       # Options: "True"/"False"
$RuleGroup = $Null      # Options:  $Null / Gropup Name
$DisplayName = "*SQL*"  # Option:  "*SQL*" / "*" / Any regular expression

$Firewall = @()
$Rules = Get-NetFirewallRule |`
       where { $_.Direction -eq $Direction} |`
       where { $_.Action -eq $Action} |`
       where { $_.Enabled -eq $Enabled} |`
       where { $_.RuleGroup -eq $RuleGroup} |`
       where { $_.DisplayName -like $DisplayName} |`
       Select DisplayName, RuleGroup, InstanceID, Enabled, Direction, Action
foreach ($App in Get-NetFirewallApplicationFilter | Select Program, InstanceID)
       $InstanceID = ($App | Format-Table -Property InstanceID -HideTableHeaders | Out-String).Trim()
       $Rule = $Rules | Where { $_.InstanceID -eq $InstanceID}
       $DisplayName = ($Rule.DisplayName + "").Trim()
       If ($DisplayName -ne ""  -or $DisplayName -like "*SQL*" ) {
              $Program = ($App | Format-Table -Property Program -HideTableHeaders | Out-String).Trim()
              $Ports = Get-NetFirewallPortFilter | Where { $_.InstanceID -eq $InstanceID}

              If ($Ports.Protocol -eq "TCP" -or $Ports.Protocol -eq "UDP" -or $Program -ne "Any") {
                     $Firewall += New-Object -TypeName PSObject -Property @{
                           DisplayName = $DisplayName
                           RuleGroup = $Rule.RuleGroup
                           Direction = $Rule.Direction
                           Action = $Rule.Action
                           Enabled = $Rule.Enabled
                           Protocol = $Ports.Protocol
                           LocalPort = $Ports.LocalPort
                           RemotePort = $Ports.RemotePort
                           RemoteAddress = (Get-NetFirewallAddressFilter | where { $_.InstanceID -eq $InstanceID}).RemoteAddress
                           Program = $Program
                           InstanceID = $InstanceID
$Firewall | Format-table -AutoSize -Property DisplayName, RuleGroup, Direction, Action, Enabled, Protocol, LocalPort, RemotePort, RemoteAddress, Program

You just have to copy paste that script, maybe do little filter adjustments and paste it into PowerShell window, which is supposed to run under Administrator's privileges.

Here is what you should expect:

If you want to research one of the returned rules alone you can run following script right after by just specifying rule's DisplayName like this:

$DisplayName = "SQ Test 1"
$Firewall | Where { $_.DisplayName -eq $DisplayName } |`
Format-List -Property DisplayName, RuleGroup, Direction, Action, Enabled, Protocol, LocalPort, RemotePort, RemoteAddress, Program, InstanceID

Result of that query returns very important "InstanceID", which you potentially can use to address some informational pieces of the Firewall Rule.

Now, when we identified existing rules or did not find appropriate one we can start doing changes:

Create a new rule

New-NetFirewallRule -DisplayName "SQL Server Access via Port 1433" -Direction Inbound -Action Allow -Protocol TCP –LocalPort 1433 -Description "SQL Server Port access Rule"

That script creates a new rule, which allows inbound connections via default SQL Server port 1433.

Enable/Disable rule

There are 2 methods to Enable/Disable rules: Using "Enable-NetFirewallRule" and "Disable-NetFirewallRule" or just by universal "Set-NetFirewallRule":

Enable-NetFirewallRule -DisplayName "SQL Server Access via Port 1433"
Disable-NetFirewallRule -DisplayName "SQL Server Access via Port 1433"

Set-NetFirewallRule -DisplayName "SQL Server Access via Port 1433" -Enabled True
Set-NetFirewallRule -DisplayName "SQL Server Access via Port 1433" -Enabled False

Edit Rule

The most common changes to a rule can be changing a Protocol, Port Number or list/range of remote computers, which can go through that rule:

Set-NetFirewallRule -DisplayName "SQL Server Access via Port 1433" -Protocol "UDP" -LocalPort 1434
Set-NetFirewallRule -DisplayName "SQL Server Access via Port 1433" -RemoteAddress "",""

You also can change "Profile" with values: Any, Domain, Private, Public, or NotApplicable;
If you are really want to secure your server you can dig into settings of "Authentication", "RemoteUser", "RemoteMachine", but to do so you'd need more advanced technics to access Active Directory and that might be area you do not want to go.

Delete rule

Obviously, if you created rule by mistake, or have a rule, which should not exist you can delete it by following:

Remove-NetFirewallRule -DisplayName "SQL Server Access via Port 1433"

After each command you can re-run the very first reporting script to make sure the changes you've made are correct.
Be aware that all changes will take immediate effect and you do not need to restart the server or even a service.

And that is it. Not a lot and pretty simple. Much easier for understanding and making any corrections than using GUI with all extra and often unnecessary features.