Showing posts with label Linux. Show all posts
Showing posts with label Linux. Show all posts

Monday, February 19, 2018

Webinar: SQL Server on Linux tools: SQL Server Integration Services

Microsoft is hosting a SQL Server on Linux tools: SQL Server Integration Services webinar/town hall on March 1st, 2018 10:00-11:00 AM Pacific Time (UTC-5)

In this session, They will demonstrate how easy it is to install, run and monitor SSIS on Linux. They will also explain how you can use ODBC to connect common data sources on Linux. Specifically, they will cover:

  • Current system architecture
  • Supported/unsupported features
  • A live demo of SSIS installation on Linux
  • A live demo of how to use ODBC to connect data sources like MySQL and Oracle
  • Future roadmap of SSIS on Linux and other platforms

If you are interested in SSIS running on Linux, make sure to register for this webinar

The link to register is here:

Saturday, April 15, 2017

Security in SQL Server on Linux Microsoft Engineering Town Hall

On April 20, 2017 10:00-11:00 AM Pacific Time (UTC-7) Microsoft will host a Security in SQL Server on Linux Microsoft Engineering Town Hall

Here is what will be covered

Learn about recent developments and get answers to your questions at twice-monthly virtual town halls, where Microsoft connects you with the team behind SQL Server on Linux. Each session will begin with a new topic, but all questions about SQL on Linux are welcome. At this event, you’ll:

  • Engage with—and ask questions of—the SQL Server on Linux engineering team.
  • Learn about how SQL Server in Linux supports the same enterprise-grade security capabilities that customers rely on with SQL Server on Windows. Get an overview of how to secure SQL Server on Linux, with demos of popular scenarios.

You can register here:

Sunday, November 27, 2016

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?



Show only listening sockets

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

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)

       netstat  - Print network connections, routing tables, interface statis‐
       tics, masquerade connections, and multicast memberships

       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]

       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

Sunday, November 20, 2016

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 | sudo apt-key add -

Register the Microsoft SQL Server Ubuntu repository:

curl | 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 | sudo apt-key add -

Register the Microsoft Ubuntu repository:

curl | 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:
Install the tools:

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

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

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

Saturday, November 19, 2016

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  :-)

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:

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 > /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!

You can find R Services samples in the following links:

Additionally, we have a new website focused around easy to use "Getting Started" tutorials. There is one R sample there:

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

and a simple blogpost I wrote some time ago just in case it helps with the 101

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,

Tuesday, March 08, 2016

How to get SQL Server on Linux?

In order to get SQL Server on Linux you need to go to the following page: Click on the part where is says Sign up to stay informed and to apply for the preview. It will bring you to a signup page that looks like this

After you filled out all the information required you will have to wait for a reply. Be aware that currently the SQL Server on Linux preview is limited to Ubuntu or as a Docker image

Wednesday, October 03, 2007

LAMP + Porn = perfect Job?

Found this here:

Senior Software Engineer – Linux/Apache/Mysql/Perl

We are a small, San Francisco-based company seeking a lead software engineer/architect to take over development of our subscription-based adult video web site. You will work with our fabulously talented product manager, marketer, and engineering support to improve our service and take it to new levels of traffic and success.

You must be extremely skilled in the following technologies, so that you can both keep the current site running and extend it:

Linux (packaging systems, network configuration, debugging, tweaking)
Perl (in the form of command line scripts, cgi scripts, and complete libraries)
Apache (building and tweaking the 1.3 variants, and the mod_perl module)
MySQL (SQL and performance tuning)
Java (from servlets to applications; including Hibernate, Spring, Log4j and other open source java libraries/frameworks)

Additionally, you must be very familiar with the following technologies that you will encounter, or the ability to quickly come up to speed on them:


You possess the skill-sets of both a system administrator and programmer. You can set up and configure a server to make it do what you want; you can discover, configure and compile applications to make them do what you want; and you can write the code to customize and tie all these things together. There is no part of a system that you are afraid to venture into, nor do you find application writing boring or too complex.

You are a developer who can do anything that you put your mind too; and you live and breathe open source software. You are goals focused, and take great pride in completing and optimizing working applications. You can communicate with business people to find out what needs to be done and how it should be prioritized, and you have the confidence and skill to make it happen. You know the latest tools and technologies that are available that might replace what we’ve got. You actively seek this information and are always looking for better ways to do things.

Finally, you have no problem serving up gigabytes and gigabytes of pornography. In fact, you find the challenge of pushing out a sustained 3Gb/s of pornography to be an extremely interesting engineering problem.

Required skills: Perl/mod_perl

Sunday, February 11, 2007

Clippy Is Not Dead, Clippy Is Alive.....On Linux

So we all know that Clippy hasn't made it to Office 2007 but guess what? Clippy is not dead, it's alive! DR. FrankGNUstein has dug up Clippy from the cemetery near Redmond, Clippy was resting (in peace) near MS Bob and now only an empty grave can be found.
Below is a picture of Clippy on Linux, kind of creepy and if you look closely you can still see the scars. Now you know why Seattle had a blackout recently because DR. FrankGNUstein used a tremendous amount of power to resurrect Clippy

Click on the image to see a bigger image. And yes I will post some real SQL later today, it will be about ten hardly used functions in T-SQL (STUFF, NULLIF, PARSENAME, REVERSE, UNICODE and more.....)

And if you want to see more Linux Clippy images and a video as well then visit this link: