SQL Server Code,Tips and Tricks, Performance Tuning

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: https://info.microsoft.com/sql-server-on-linux-town-hall-sql-server-integration-services-registration.html

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: https://info.microsoft.com/sql-server-on-linux-town-hall-security-register.html

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
sudo /opt/mssql/bin/mssql-conf set tcpport 3000

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



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


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

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

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

In order to get SQL Server on Linux you need to go to the following page: https://www.microsoft.com/en-us/server-cloud/sql-server-on-linux.aspx. 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

Found this here:http://jobs.perl.org/job/6883

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


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:



C/C++
Python
MPlayer/FFmpeg
Lucene
Lighttpd
Perlbal




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
LAMP

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: http://vigor.sourceforge.net/screenshots/