Changing the SQL Server port on Linux

Today, I decided to see how I can change the port SQL Server is listening on



To see what port SQL Server is listening on, you can use the netstat command, here is what the output looks like when I run netstat -tulpn | grep sqlservr

I used grep to limit the output to lines that contain sqlservr only

Next, to change the port that SQL Server is listening on to 3000, I ran the following command

sudo /opt/mssql/bin/mssql-conf set tcpport 3000

I restarted SQL Server bu running the command systemctl restart mssql-server

Then I ran  netstat -tulpn | grep sqlservr again and as you can see the output now has 3000 as the port number

Now I tried using sqlcmd, I ran the following sqlcmd -S localhost -U SA

I got an timeout error

Finally I ran the sqlcmd command again but I added port number 3000, like this
sqlcmd -S localhost,3000 -U SA

As you can see, this worked and I could connect to SQL Server listening on port 3000 when supplying that to the sqlcmd command

How do we reset SQL Server back to the default port?
To reset SQL Server back to use the default port, run the command below

/opt/mssql/bin/mssql-conf unset tcpport

Here is a screenshot of running netstat, resetting the port, restarting SQL Server and running netstat again

You want to learn a little more?

We used tulpn as options in our netstat command. So what do all these options do?

[-t] 

tcp

[-u] 

udp

[-l]

Show only listening sockets

[-p]

Show the PID (process identifier) and name of the program to which each socket belongs.

[-n] 

Show numerical addresses instead of trying to determine symbolic host, port or user names.

On a Linux system to get help for a command, use man, this will bring up the user manual for that command

So if you run man netstat, you will see the following (I only pasted 1 page here, there are more)

man netstat

--------------------------

NETSTAT(8)                 Linux Programmer's Manual                NETSTAT(8)

NAME

       netstat  - Print network connections, routing tables, interface statis‐

       tics, masquerade connections, and multicast memberships

SYNOPSIS

       netstat  [address_family_options]  [--tcp|-t]   [--udp|-u]   [--raw|-w]

       [--listening|-l]     [--all|-a]     [--numeric|-n]    [--numeric-hosts]

       [--numeric-ports]           [--numeric-users]           [--symbolic|-N]

       [--extend|-e[--extend|-e]]  [--timers|-o] [--program|-p] [--verbose|-v]

       [--continuous|-c]

       netstat              {--route|-r}              [address_family_options]

       [--extend|-e[--extend|-e]]         [--verbose|-v]        [--numeric|-n]

       [--numeric-hosts] [--numeric-ports] [--numeric-users] [--continuous|-c]

       netstat {--interfaces|-i} [--all|-a] [--extend|-e[--extend|-e]] [--ver‐

       bose|-v]  [--program|-p]  [--numeric|-n]  [--numeric-hosts] [--numeric-

       ports] [--numeric-users] [--continuous|-c]

       netstat      {--groups|-g}       [--numeric|-n]       [--numeric-hosts]

       [--numeric-ports] [--numeric-users] [--continuous|-c]

 Manual page netstat(8) line 1/348 15% (press h for help or q to quit)


To see all my SQL Server on Linux posts, click here: SQL Server on Linux

You snooze you lose... I lost....

I was working on a post about the new columns sql_memory_model, sql_memory_model_desc columns in DMV sys.dm_os_sys_info. These columns allow DBAs to programmatically identify if Lock Pages in Memory (LPIM) privilege is in effect at the service startup time.

I was setting it up, had one instance running with pages locked in memory and the other one without. Took some screenshots, then I noticed the MSSQL Tiger Team published the post
Developers choice: Programmatically identify LPIM and IFI privileges in SQL Server

So really there was no need for my post anymore. But what do I do with my beautiful screenshots?

Well not someone who likes to waste stuff, I decided to write just a couple of sentences and put the screenshots in this post and then link to the post by the MSSQL Tiger Team

Here is the query I ran to see instant file initialization is enabled, I ran this against two instances, one a SQL Server 2016 Service Pack instance, the other a SQL Server vNext instance

SELECT instant_file_initialization_enabled,* 
FROM sys.dm_server_services

You can see the output in the pic below, the vNext instance does not have instant file initialization enabled, the 2016 SP1 instance does have instant file initialization enabled

To see if Lock Pages in Memory is in use, you can run the following query

SELECT sql_memory_model,sql_memory_model_desc
FROM sys.dm_os_sys_info

You can see the output in the pic below, the vNext instance does not use Lock Pages in Memory, the 2016 SP1 instance does use Lock Pages in Memory

The sys.dm_os_sys_info DMV also has a new column that SQL Server 2016 SP1 does not have.. process_physical_affinity


To get all the details about this stuff in the pictures go read the post
Developers choice: Programmatically identify LPIM and IFI privileges in SQL Server published by the MSSQL Tiger Team

What to get your friendly DBA this holiday season

You have a DBA that you want to get something for this holiday season, but you have a hard time figuring out what to get him?
Here are 3 choices that will make a good present

The first two have to do with the availability of SQL Server on Linux. This is in preview right now, but it will be here before you know it.
I can already see the job postings a year fron now...

Install SQL Server on Windows as well as Linux
Manage Linux and Windows servers

And of course...  5 years experience installing SQL Server vNext on Linux  :-)

You go to the interview, then someone asks what does touch File1 do?

You get my point, SQL Server DBAs will need to learn Linux and they will need to learn this fast.

If your DBA likes to read, as a first step, I would recommend the following two books

Unix and Linux: Visual QuickStart Guide (4th Edition)
How Linux Works: What Every Superuser Should Know 2nd Edition

The Visual QuickStart book is for the total beginner, the How Linux Works book will go into more detail. Either book is perfect if the person has never touched a *nix system before.
Both books are nicely formatted, clear and concise, once the person is done with one of these books, the person can get himself/herself one of those 1000 page tomes


The next item is a book by an author whose previous work I have read and enjoyed

High Performance SQL Server The Go Faster Book by Benjamin Nevarez

I noticed that Benjamin Nevarez's latest book is available now on the Apress site for $9.99 in epub and pdf format, this price is valid through November 29, 2016

I didn't read this book yet, so here is what the website has to say about this book


What You Will Learn
Understand SQL Server's database engine and how it processes queries
Configure instances in support of high-throughput applications
Provide consistent response times to varying user numbers and query volumes
Design databases for high-throughput applications with focus on performance
Record performance baselines and monitor SQL Server instances against them
Troubleshot and fix performance problems

Here is a breakdown of the chapters

How SQL Server Works
Pages 1-19

Analyzing Wait Statistics
Pages 21-43

The Query Store
Pages 45-69

SQL Server Configuration
Pages 71-84

tempdb Troubleshooting and Configuration
Pages 85-99

SQL Server In-Memory Technologies
Pages 101-129

Performance Troubleshooting
Pages 131-154

Indexing
Pages 155-180

SQL Server Storage
Pages 181-196

There you have it...one of these will make a nice gift for your friendly DBA....

BULK INSERT with a file containing linefeeds gotcha

I blogged about this before but someone ran into this issue again at work so I thought it made sense to revisit this.

I prefer to use BULK INSERT or bcp as much as possible, this is especially true if all I need to do is dump the file into a table. If there is a need to do complex stuff,  I will use SQL Server Integration Services or Informatica. These days files are generated by all kinds of systems, these can be Windows, *nix, Mac, Raspberry Pis, IoT systems and many other systems.

If you get an error importing one of these files, your first course of action is to open one of these files in something like Notepad++ or Editplus.  Notepad++ and Editplus have more functionality and are many times faster than notepad. The nice thing about either of these is that you can see control characters.

See how you can see the linefeeds here? You cannot do that in notepad

So let's say you get a file where the row terminator is a linefeed, how would you specify that as a row terminator in BULK INSERT?

You can try n which stands for newline

BULK INSERT SomeTable
 FROM 'D:\JunkdrawImportMe.txt'
 WITH (FIELDTERMINATOR = 't',
 FIRSTROW =2,
 ROWTERMINATOR = 'n')

Nope, that doesn't work, you get 0 rows inserted


You can try r which stands for carriage return

BULK INSERT SomeTable
 FROM 'D:\JunkdrawImportMe.txt'
 WITH (FIELDTERMINATOR = 't',
 FIRSTROW =2,
 ROWTERMINATOR = 'r')

Nope, that doesn't work either, you get 0 rows inserted


What about l for linefeed?

BULK INSERT SomeTable
 FROM 'D:\JunkdrawImportMe.txt'
 WITH (FIELDTERMINATOR = 't',
 FIRSTROW =2,
 ROWTERMINATOR = 'l')

You get an error

Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 1 (SomeDate).



What about if you try a CHAR(10) which is a linefeed

BULK INSERT SomeTable
 FROM 'D:\JunkdrawImportMe.txt'
 WITH (FIELDTERMINATOR = 't',
 FIRSTROW =2,
 ROWTERMINATOR = CHAR(10) )

You get this error

Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'CHAR'.



How about if you embed it by using Dynamic SQL

DECLARE @cmd varchar(1000)
SET @cmd = 'BULK INSERT SomeTable
FROM ''D:\JunkdrawImportMe.txt''
WITH ( FIELDTERMINATOR = ''t'',
 FIRSTROW =2,
 ROWTERMINATOR = '''+CHAR(10)+''')'
EXEC(@cmd)

The dynamic SQL solution works, that was my original answer to this.

What about if we use the hex code for line feed 0x0a?

BULK INSERT SomeTable
FROM 'D:\JunkdrawImportMe.txt'
WITH (FIELDTERMINATOR = 't',
FIRSTROW =2,
ROWTERMINATOR = '0x0a') --CHAR(10) or ‘l’ does not work 
-- 0x0a is the hex code for linefeed CHAR(10)

Bingo, that works as well.  I actually prefer this, who wants to muck around with Dynamic SQL if there is an easier way....

That's all, keep this in mind next time you get a file with a linefeed and you are struggling importing that file

Interesting SQL related links for the week of Nov 25, 2016

Here are some interesting articles I read and tweeted about this past week, I think you will like these as well. If you are bored this weekend, some of these might be good for you to read



Released: Public Preview for System Center Management Packs for SQL Server

We are getting ready to update the SQL Server Management Packs family. Please install and use this public preview and send us your feedback (sqlmpsfeedback@microsoft.com)! We appreciate the time and effort you spend on these previews which make the final product so much better.

Please download at:

Microsoft System Center Management Packs (Community Technical Preview 1) for SQL Server

Included in the download are:

• Microsoft System Center Management Pack for SQL Server 2008/2008 R2/2012/2014/2016 (6.7.10.0)
• Microsoft System Center Management Pack for SQL Server Replication 2008/2012/2014/2016 (6.7.10.0)
• Microsoft System Center Management Pack for SQL Server Analysis Services 2008/2012/2014/2016 (6.7.10.0)
• Microsoft System Center Management Pack for SQL Server Reporting Services 2008/2012/2014/2016 (6.7.10.0)
• Microsoft System Center Management Pack for SQL Server Dashboards (6.7.10.0)

SQL Server 2016 SP1: Known issues

The SQL Product team has identified the following known issues which the customers may encounter after installing SQL Server 2016 SP1. The following post will help the customers  plan and prepare their environment for the following known issues while installing SQL Server 2016 SP1.

The Future of Programming (Bob Martin) -- we, programmers, are going to have to grow up and define our profession

Index Scans: Not Nearly As Bad As You Might Think

Using our good old buddy the StackOverflow demo database, let’s look at the Users table. I want to get the first 10 users in the table ordered by Id, the primary clustered key, which is an identity field.


Ooo – clustered index scan – that’s bad, right?

Not so fast. Click on the plan to view it in PasteThePlan.com, and hover your mouse over the Clustered Index Scan:

Source Control in SQL Server Management Studio (SSMS)

In the latest generation of SQL Server Management Studio, we moved to the Visual Studio 2015 Isolated Shell. While this provides SSMS a modern IDE foundation for many functional areas, it also had some consequences. Specifically, the integration with source control systems in SSMS no longer works the way it did in SSMS 2014 and prior.  Previously, one could install the Visual Studio MSSCCI provider and then integrate with various source control systems. Visual Studio 2015 does not support MSSCCI so that is no longer an option to use in SSMS.

Of course, the good news is that Visual Studio 2015 includes TFS and Git source control integration. With the move to VS 2015 Isolated Shell, SSMS should be able to use these packages as well, right? The answer is…yes…but! The issue for SSMS is that the TFS source control integration package VS provides also includes the entire suite of TFS integration features. If we include this package by default, SSMS will have Team Explorer in its entirety which includes things such as work item tracking, builds, etc. This doesn’t fit in the overall experience SSMS is designed for, so we aren’t going to include this package as part of SSMS. The full TFS integrated experience is included as part of SQL Server Data Tools which is designed for a more developer-centric set of scenarios.

That said, if source code integration is an important aspect of how you use SSMS, you can enable the Visual Studio packages manually.

Why is My Query Faster the Second Time it Runs? (Dear SQL DBA Episode 23)

Dear SQL DBA,

Whenever I create a complex view, the first time I use it in a query, the query is slow. The same thing happens after I alter the view. What is the reason behind this?

This is a great question — because when you ask this question, you’re about to discover a lot of interesting, useful things about how SQL Server runs queries.

There are two ways that SQL Server can use memory to make a query faster the second time you run it. Let’s talk about how the magic happens.

Installing SQL Server on Linux, connecting and running queries from Linux

I decided to play around with installing SQL Server vNext on Linux today. I decided to pick the Ubuntu distribution. I created a VM in VirtualBox and then installed Ubuntu 16.04.   After that was done, it was time to install SQL Server.

All this stuff on this page runs on Linux, there are no windows components, if you have just a Linux desktop/server, you are all set


Here are the steps


Import the public repository GPG keys:

curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -


Register the Microsoft SQL Server Ubuntu repository:

curl https://packages.microsoft.com/config/ubuntu/16.04/mssql-server.list | sudo tee /etc/apt/sources.list.d/mssql-server.list


Run the following commands to install SQL Server:

sudo apt-get update
sudo apt-get install -y mssql-server



After the package installation finishes, run the configuration script and follow the prompts.


sudo /opt/mssql/bin/sqlservr-setup


Once the configuration is done, verify that the service is running:

systemctl status mssql-server

I myself like to run ps and then grep for mssql

ps -ef | grep  mssql

Ok so SQL Server is installed. In order to be able to run queries against SQL Server, we need to install the SQL Server tools

Open a new terminal window

Import the public repository GPG keys:


curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -


Register the Microsoft Ubuntu repository:

curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list | sudo tee /etc/apt/sources.list.d/msprod.list


Update the sources list and run the installation command:


sudo apt-get update 
sudo apt-get install mssql-tools


You will get a bunch of prompts, just follow it, say YES or y and you are set

Now let's run a command

Run this from a terminal window

sqlcmd -S localhost -U SA
Enter you password
Enter a simple query like SELECT @@version
Type GO, hit enter and voila, you should get results

Here is what it looks like

Here is also a video of the whole process, all of this was done in less than 5 minutes


Here is also a link to the official documentation

Install SQL Server: https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-setup-ubuntu
Install the tools: https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-setup-tools#ubuntu

Visual Studio Code

Now unless you want to spend the rest of your life in a command window, I suggest you install VS Code

Visit this page https://code.visualstudio.com/#alt-downloads

Now for me .deb didn't work, so I got the tarball (.tar.gz) and manually extracted it

Then I ran the Code application inside the VSCode-linux-x64 folder

The first thing we have to do is install the mssql extension

Click on View-->Extensions, type in mssql, click on the install button

Now open a new file and save it with a .sql extension

Click on View-->Command Palette

Type sqlman

Add the server localhost), db name, username (probably sa) and add the password, save this with a name so you will know what it is

Type some valid SQL, hit CTRL + SHIFT + E or right click and select execute query

You should see something like this

There you have it, SQL Server running on Ubuntu, VS Code running on Ubuntu returning data from the SQL Server instance


I did hit an issue with trying to run xp_readerrorlog, you can read about that here:
How to read the errorlog on SQL Server installed on Linux

If you want to install SQL Server on Docker on a Mac, take a look at Aaron Bertrand's post
VS Code on Mac meets SQL Server on Linux (in Docker)

To see all my SQL Server on Linux posts, click here: SQL Server on Linux

How to read the errorlog on SQL Server installed on Linux

I finally installed SQL Server on Linux, while I was messing around with some queries, I noticed the following command fails with an error

exec xp_readerrorlog

The error is

Started executing query at Line 1
Msg 22004, Level 16, State 1, Line 0 Failed to open loopback connection. Please see event log for more information.
Msg 22004, Level 16, State 1, Line 0 Error log location not found.
Total execution time: 00:01:01.34


So how do you then look at the log on Linux? I don't have SSMS installed on the host machine where the Linux Virtual Machine lives. SQLCMD gives the same error. I don't know if it even works from SSMS from a windows box.

Edit: so it looks like it works from SSMS

@DenisGobo SSMS reading SQL logs works. Also quick tested xp_readerrorlog 0, 1 - works fine as well.

— Andy Yun (@SQLBek) November 20, 2016

To answer this question, the errorlog is stored in this location  /var/opt/mssql/log. You need to be a superuser to access this location

So in my case, I can just execute the following from a terminal in Ubuntu

sudo cat /var/opt/mssql/log/errorlog

And it looks like this

There you have it, this is how you can look at the error log

To see all my SQL Server on Linux posts, click here: SQL Server on Linux

What we learned from the reddit SQL Server AMA thread on /r/sqlserver

The SQL Server AMA thread on reddit, has finished. I have put 8 most interesting answers here and then gave my take on it, the questions will be linked straight to the question on reddit. The answer will be in italics and my take on it will be below in regular font


What was the reasoning for having SQL Server for Linux run atop Drawbridge, rather than directly interfacing with Linux? It seams like it's not a true Linux port if NT Kernel emulation is required 

I guess you can always debate whether it is better to have an abstraction layer or do a full port. I am a practical person, and like to judge based on results. I myself am not on the Linux team, but am really impressed with what the team has managed to deliver thus far, both in terms of feature set and in terms of performance. A full port would have taken much longer and would have led to a code base that is much harder to maintain. 

I never heard of Drawbridge either. From the Microsoft Drawbridge research page: Drawbridge combines two core technologies: First, a picoprocess, which is a process-based isolation container with a minimal kernel API surface. Second, a library OS, which is a version of Windows enlightened to run efficiently within a picoprocess.

I really like the JSON support you guys added. Is JSON in SQL Server still evolving, or are you guys happy with where it is at?I would love to see a JSON data type, like XML support. Thanks!

We are collecting feedback and we will decide what would be the next step. Current NVARCHAR representation enables us to integrated JSON with in-memory, column store, does not require new types in drivers, etc. Also, some experiments show that text representation is even faster than XML type in some scenarios. The next step would be better integration with native modules that will speed up queries. We would like to know how your are using current JSON, what are limitations, and then we will decide what would be the next step.

It makes sense that they decided to use nvarchar since you can then use it with in-memory OLTP and Columnstore. We currently store json in some of our tables but we never search on it or manipulate it in the DB

How does making SQL Server available for Linux relate (or not relate) to Microsoft's famous "Embrace, Extend, Extinguish" strategy?

Two things - Satya Nadella, cloud economics - changed a lot of the way we think about things here at Microsoft now. The way I like to think of this strategy of bringing SQL Server to Linux is that it maps to Microsoft's new mission statement - "Empower every person and every organization to achieve more". SQL Server on Linux is about bringing the really powerful capabilities of SQL Server to everybody, not just those that are using Windows/Windows Server and seeing what people can do with it. It's inspiring for example to think about what the scientific or academic communities (which are oftentimes on Linux) could now achieve with SQL Server. Let's call it Microsoft's new 'Embrace, Enable, and Empower' strategy. :)

Haha I knew this would be asked, great answer also. We all remember the Ballmer Linux is a cancer quote, how things have changed

I am so sorry, but I just had to add this image  :-)

SSMS coming for Linux? If so when?

We do not plan to port SSMS "as is" to Linux/macOS for 2 main reasons: 1) SSMS on Windows depends on the VS2015 Isolated shell which is not available on Linux/macOS and 2) a direct port of SSMS will not align with the design guidelines & information architecture of apps that natively run on Linux/macOS (e.g. buttons, menus, etc. are different, the window chrome is different, etc.)

That said, we are working on a new set of multi-platform SQL client tools that DBAs and database developers can use on Linux/macOS/Windows. The new 'mssql' extension for Visual Studio Code is our first attempt in this area and we are using it to prove out the multi-platform SQL tools stack "backend" we've created.

We don't have specific dates yet but stay tuned for more details in the coming months. Meanwhile, you can try the mssql extension for VS Code on Linux/macOS/Windows from here: https://aka.ms/mssql-marketplace


We plan to have a cross-platform tool that will be Linux native. We don't have an exact date set but it's in the works for 2017. In the meantime, you can connect to SQL Server on Linux through the Visual Studio Code mssql extension as well as SSMS.

So it looks like there will be a cross-platform tool, maybe something like Visual Studio Code, make it web based. As long as it is not based on Java and has that nasty purple shiny look and feel. But in reality, all the people I worked with that used *nix always used windows and then would use PuTTY or something similar to SSH into the box and just do everything from the command line.

will SQL on Linux have an Express edition? (I think it should, to give a better alternative to all those LAMP proponents - though admittedly I think a number of switching to postgres, which is at least better than mysql)

Yes, it will! Cost = free.

This is nice, also it seems that when you license SQL Server in the future, the OS doesn't matter, if you get a 4 core license, you can install SQL Server on Linux or Windows, your choice

Is there any plan to make SQL Server work on Windows Server Nano?

This is something that we are looking into. It would require creating a new appx-style package for deployment. That in itself would be a good thing actually that could make it easier to install SQL Server (like the package based install for SQL Server on Linux) but it would be a very large development effort. Spending dev effort on that would take away from other things we would like to do. It would also mean that we end up having two different methods of patching SQL Server (.msp and appx) which could further complicate servicing. On the other hand, WS Nano is really well suited for SQL Server due to it's higher availability (less patching), smaller attack surface area, fewer services running, etc. Those are the trade offs we are thinking through, but no concrete plans one way or another right now. Let us know what you think we should do!

Installing SQL Server on Linux is really nice, here is what it looks like on redhat

sudo su

curl https://packages.microsoft.com/config/rhel/7/mssql-server.repo > /etc/yum.repos.d/mssql-server.repo

sudo yum install -y mssql-server

sudo /opt/mssql/bin/sqlservr-setup   #follow the prompts

systemctl status mssql-server #verify service is running

It also makes sense to run it on a server that is bare metal, no need for IE or printer service running.

Any chance we will ever get a step by step course that uses the developer edition of SQL server to walk someone through basic use cases of all the applications. For instance, creating a sample database, adding tables, keys. Scripting user permisions for them and encrypting them. Setting up the report server. Making a report. Building a cube, making a small data analysis setup. All in an easy to follow step by step? A basic use case example for r services.

Great minds think alike! We actually just this Wednesday posted our first set of developer tutorials for lots of different languages (node, python, C#, Java, and more) and platforms (macOS, Docker, Linux, and Windows). They cover how to get SQL Server installed, get some tools, get your first basic app going, and how to use some powerful features like columnstore. There is a tutorial for R language/services too. If that seems to be a popular thing, we'll keep going and add more to it. Check it out and spread the word! http://aka.ms/sqldev

You can find R Services samples in the following links:
https://gallery.cortanaintelligence.com/Collection/ML-Templates-with-SQL-Server-R-Services-1

https://msdn.microsoft.com/en-US/library/mt591993.aspx

Additionally, we have a new website focused around easy to use "Getting Started" tutorials. There is one R sample there:
https://www.microsoft.com/en-us/sql-server/developer-get-started/r


let me add a couple more R-related articles :)

https://github.com/Microsoft/sql-server-samples/tree/master/samples/features/r-services/Implementing%20Predictive%20Analytics

and a simple blogpost I wrote some time ago just in case it helps with the 101 https://medium.com/@davidsb/datascience-for-developers-build-your-first-predictive-model-with-r-a798f684752f

Is using the GPU for extra processing power on the horizon?

The trick with GPUs is balancing the cost of moving data onto the GPU vs the compute gains. While I can't comment on the future, we are always looking at ways to improve the performance of SQL Server and leverage the latest hardware to its fullest potential. I'd encourage you to take a look at the "SQL 2016 - It Just Runs Faster" blog series for examples of what the team has done recently.

Here are a few that I like:

• Improvements in columnstore indexes and batch processing (link)
• Automatic soft NUMA for large CPU system deployments (link)
• Hardware acceleration for encryption features like TDE by leveraging Intel's AES-NI instruction set (link)

We evaluate hardware advancements periodically for SQL Server. The new Microsoft ML library that is available with SQL Server vNext uses GPU for the neural net algorithms so you may see such capabilities in other areas of the product.

This is definitely an area we are looking at. But we don't have any concrete at the moment.

I remember when postgres did this, the performance improvement was impressive, you can read about that here PG-STROM

That is all, if you want to see all the questions and answers go here:  SQL Server AMA thread on reddit,

Interesting SQL related links for the week of Nov 18, 2016

Here are some interesting articles I read and tweeted about this past week, I think you will like these as well. If you are bored this weekend, some of these might be good for you to read


Massive Disruption Is Coming With Quantum Computing
Next year, we may see the launch of the first true quantum computers.

The implications will be staggering.

This post aims to answer three questions:

1. What are quantum computers?
2. What are their implications?
3. Who’s working on them?

New Showplan XML properties in SSMS October Release

Back in March we announced the availability of per-operator level performance stats for Query Processing (exposed in Showplan XML) with SQL Server 2014 SP2 and SQL Server 2016. However, SQL Server Management Studio (SSMS) did not expose this information, and so users had to look in the Showplan XML, in the context of the appropriate node and operator.

This caused some friction, and so we are happy to announce that in the latest (October) release of SSMS, these attributes are now readily available in the Properties window of an Actual Execution Plan, in the scope of each operator.

Microsoft signs deal to power data center entirely by wind power

You need a lot of energy to run a data center. Especially when your name is Microsoft, and you’re the biggest technology company on the planet. But this doesn’t necessarily have to be environmentally harmful.

The company just announced that it has inked deals with two wind farms, with the aim of entirely powering its Cheyenne, Wyoming data center from renewable sources.

Microsoft has contracted Bloom Wind farm in Kansas to provide 178 megawatts, and the Silver Sage and Happy Jack farms in Wyoming to provide an additional 59 megawatts.

Unable to drop a user in a database

A user called in for help because he wasn’t able to drop a user from a database. The error message is below

Msg 15136, Level 16, State 1, Line 2
The database principal is set as the execution context of one or more procedures, functions, or event notifications and cannot be dropped.


From the error, the logical starting point is sys.sql_modules because functions, stored procedures have entries in this catalog view. Additionally, it has a column called execute_as_principal_id

So I looked there but found no entries whose execute_as_principal_id is the same user id of the user this customer tried to drop. Next I started to look at source code and found out this error is raised in a couple of other places. It gave me the clue that I didn’t have to do source code research, TSQL is all I needed to solve the problem.

Connect(); // 2016

Videos from the Connect(); // 2016 event can be found here

Screenshot of some of these

SQL Server 2016 Service Pack 1 (SP1) released !!!

With cloud first strategy, the SQL Product Team has observed great success and adoption of SQL Server 2016 compared to any previous releases. Today, we are even more excited and pleased to announce the availability of SQL Server 2016 Service Pack 1 (SP1). With SQL Server 2016 SP1, we are making key improvements allowing a consistent programmability surface area for developers and organizations across SQL Server editions. This will enable you to build advanced applications that scale across editions and cloud as you grow. Developers and application partners can now build to a single programming surface when creating or upgrading intelligent applications, and use the edition which scales to the application’s needs.


In addition to a consistent programmability experience across all editions, SQL Server 2016 SP1 also introduces all the supportability and diagnostics improvements first introduced in SQL 2014 SP2, as well as new improvements and fixes centered around performance, supportability, programmability and diagnostics based on the learnings and feedback from customers and SQL community.


SQL Server 2016 SP1 also includes all the fixes up to SQL Server 2016 RTM CU3 including Security Update MS16–136.

SQL Server on Linux Documentation

SQL Server vNext CTP1 now runs on Linux! Learn about the core relational database capabilities in this release of SQL Server, and how to install, manage, secure, and develop for this new release.

• 

  Install: SQL Server on Ubuntu, Red Hat Enterprise Linux, or run the Docker image

• 

  Manage: SSMS, PowerShell, mssql-conf

• 

  Develop: Connectivity libraries, Visual Studio tools, tutorials

• 

  Download SSMS: Run on Windows to access, manage, and develop SQL Server on Linux

• 

  Download SSDT: Development tools for building SQL Server relational databases and SQL Azure databases

• 

  Install Visual Studio Code with the vscode-mssql extension: SQL Server support in VS Code

What's New in SQL Server vNext

SQL Server vNext represents a major step towards making SQL Server a platform that enables choices of development languages, data types, on-premises and in the cloud, and across operating systems by bringing the power of SQL Server to Linux, Linux-based Docker containers, and Windows.

You can find all the new stuff at that page

Now pay attention to the next two links... did you noticed it was getting colder since Wednesday? That is because Hell just froze over!!!!

A funny comment on slashdot
Microsoft joins the Linux foundation, Google joins the .NET foundation. What's next? Hillary joins the Trump Foundation?

What?

Google Cloud to join .NET Foundation Technical Steering Group

Google is pleased to be joining the Technical Steering Group of the .NET Foundation. .NET is a key component in the modern enterprise, and the Google Cloud Platform (GCP) team has worked hard to ensure that .NET has first-class support on Google’s infrastructure, including excellent infrastructure for Windows. For years, Google has offered .NET libraries for more than 200 of its cloud services. More recently, we’ve built native GCP support for Visual Studio and PowerShell.

Come again?

Microsoft Fortifies Commitment to Open Source, Becomes Linux Foundation Platinum 

Member

The Linux Foundation, the nonprofit advancing professional open source management for mass collaboration, today announced that Microsoft has joined the organization at a Platinum member during Microsoft’s Connect(); developer event in New York.

From cloud computing and networking to gaming, Microsoft has steadily increased its engagement in open source projects and communities. The company is currently a leading open source contributor on GitHub and earlier this year announced several milestones that indicate the scope of its commitment to open source development. The company released the open source .NET Core 1.0; partnered with Canonical to bring Ubuntu to Windows 10; worked with FreeBSD to release an image for Azure; and after acquiring Xamarin, Microsoft open sourced its software development kit. In addition, Microsoft works with companies like Red Hat, SUSE and others to support their solutions in its products.

Disable CREATE OR ALTER for DDL triggers (or fix it)

I filed this bug while working on the post What's new in SQL Server 2016: CREATE OR ALTER

On SQL Server 2016 SP1 and SQL Server vNext (neither available from drop down, so I picked 2016 RTM) When executing CREATE OR ALTER with a DDL trigger, it will run fine the first time, when you execute the script again, you will get an error like the following 

Msg 2714, Level 16, State 2, Procedure safety, Line 1 [Batch Start Line 77] There is already an object named 'safety' in the database. 

So either don't allow CREATE OR ALTER when the trigger is a DDL trigger or fix it so it doesn't throw the error if you run it more than once

And to finish it of, here are some of my blog posts about SQL Server vNext and SQL Server 2016 Service Pack 1 from this week

SQL Server 2016 SP1 released, SQL Server vNext available for download

Playing around with the new stuff in SQL Server vNext and SQL Server 2016 SP1

What's new in SQL Server 2016: CREATE OR ALTER

SQL Server AMA thread on reddit is live now

The SQL Server AMA thread on reddit is now live, you can start asking questions now and they will start answering Friday 11/18 at 9:30 PDT until 14:00 PDT.

One person already has a ton of questions, just look at this list, some of these would really make a nice addition.

SQL Server is a great product, thank you for it, but we need more from you.
And here's my question.

There are many issues on Connect that desperately need resolving, some of them are open for more than 10 years. My favorite is from Adam Machanic: https://connect.microsoft.com/SQLServer/feedback/details/252226/allow-enabling-and-disabling-of-a-columns-identity-property

There are also many pointed out by the SQL MVPs and experts for years, like these:https://sqlperformance.com/2013/04/t-sql-queries/filtered-indexes

I've collected some more stuff that is missing in SQL Server and should be implemented:

• using GPU for processing!
• native regular expression support! Don't force every user to implement their own CLR, just give us optimized regexes!
• Git support for SSMS (just like it's in Visual Studio)
• row constructors! - like in WHERE (orderdate, orderid) > (@orderdate, @orderid)
• order preserving clustered columnstore
• implement PERCENT and WITH TIES for OFFSET-FETCH (it's a standard feature) to align it with old TOP operator
• TOP() OVER operator - like SELECT TOP (3) OVER (PARTITION BY custid ORDER BY orderdate, orderid)
• NTH_VALUE window function
• FILTER clause for window functions - http://modern-sql.com/feature/filter, similar to Itzik's suggestion https://connect.microsoft.com/SQLServer/feedback/details/532474
• IS [NOT] DISTINCT FROM operator
• INTERSECT ALL in addition to INTERSECT
• EXCEPT ALL in addition to EXCEPT
• RANGE BETWEEN INTERVAL '3' MONTH PRECEDING AND CURRENT ROW ability for window frames
• WITH DROP_EXISTING (alternatively CREATE OR ALTER) for everything
• ALTER TABLE .. CHECK CONSTRAINT should be a no-op if the constraint is already trusted
• ORDER BY ... NULLS FIRST/LAST
• user interface (wizards, monitoring) for Service Broker
• column name in 'string or binary data would be truncated' message!
• dark theme for SSMS
• simple change of server collation on Windows (it's super-easy in SQL Server on Linux)
• multiple tempdb databases + one "official" tempdb backwards compatible
• In-Memory tempdb
• search engine similar to Elastic Search / Splunk
• ability to install In-Database R Services on FCI
• generally speaking better coverage of standard SQL features - T-SQL is for DEVELOPERS who are constantly pushed by management to be more effective - so finally make their lives easier

This list is by no means exhaustive. Most of them are just features desctibed in ISO/IEC SQL standard.

A lot of Connect feedback has been prematurely and improperly closed with various reasons: Won't fix, by design, some are active for ages. What I would ideally want from you is to review all unfixed items and decide again whether they're worth doing or not.

Like this one - 124 votes and closed/won't fix!

• https://connect.microsoft.com/SQLServer/feedback/details/728969

This one - 179 votes - active since 2007!

• https://connect.microsoft.com/SQLServer/feedback/details/254390/over-clause-enhancement-request-top-over

Some limitations are just silly, like the one requiring something to be a single statement in a batch. If I can bypass that requirement by putting my code in a dynamic SQL and do EXEC(string) then why SQL Server cannot do that for me??

And please fix this darn slow Connect site. Maybe a reindex and statistics update will do? :-)

Finally the question: do you acknowledge this? Will you fix all this? Any comments?