[MUSIC]
Hi, my name is David Salgado.
I work as a Product Manager on the data team.
And on this video, I'm gonna walk through the steps to create
an application with SQL Server on Linux.
As you can imagine, for your application, it doesn't matter if
you're hosting your data on SQL Server on Linux or on Windows.
At the end of the day, it's gonna just be a connection string.
And it doesn't matter if it's on the Cloud, if it's On-Premises,
it's just the changes on that connection string.
However, as the developer, you may want to have everything installed on
your machine, and that's what we are gonna walk you through.
How to Install, Money, Connect, and Deploy your
application assuming that OSX or Linux will be your primary OS.
And when I say installation, I mean that as for Windows,
we have had native installation packages.
Now, if you're a Linux user, you can find Suse, Red Hat or
Ubuntu packages.
So if you're an Ubuntu user,
you can just able to get MS SQL Server on Linux.
And of course, we have Docker Images for both Windows and Linux.
Once you have installed your database,
you will need to manage it.
And usually in Windows,
you do that using SQL Server Management Studio, SSMS.
You can still use SSMS from Windows to connect to a SQL Server on Linux
machine.
But we have two new tools that we want to show you.
One, this is a gonna, obviously, a Visual Studio Code extension.
And the other one is the Command Line tools for SQL Server.
Once you have installed and once you have created the database with all
the tables, we have the connection point.
Where you are gonna create that connection string, and
you are gonna use a specific driver to connect your application to
the database server.
We have connectors for all those languages and more.
And also, if you don't find the language that is your
personal preference, please let us know so we can work on it.
And after that, we will talk about where can you deploy your
application once it's finished.
On the demo today, we are gonna show you how to create a NodeJS
application that is gonna be hosted on a Docker Image on Linux.
And we are gonna use a couple of those management tools that I
told you about.
But also, I'm gonna show you a couple of different features of
SQL Server.
Because if we think about SQL Server as a Swiss army knife, when we store
and retrieve information, we are just using the toothpick.
We are not taking advantage of all the features that we have available.
So, especially now with this new version of SQL Server,
you will have access to all the programming features that were
available for Enterprise, Developer or Standard versions of the product.
But if you're using SQL Server 2016,
let me remind you that you have to install the Service Pack 1.
We announced it back in November.
And once you install it, you will have access to all the features for
all the editions of SQL Server.
Let's go to the Demo.
So we are gonna start by installing SQL Server in Linux.
In order to do that, we have several options.
And we have this side that lines all the options based on your
preferred language.
In this case, we are gonna work with NodeJS on macOS.
So when I click here, I have the instructions for my OS.
So I have already gone through those steps to save a little bit of time.
If I come here, I chose the Docker option.
So if I check my docker images,
we are gonna have sql-server-linux here.
I also have another images that have the server plus
tools plus connectors plus frameworks.
But on this case, I'm gonna use the base machine.
And I have already also a container created that I can show you here.
it's the SQL Server Linux base image.
And I have called it sql-employees, because I have already created
a database and I have already filled data with some information.
Let me start that container, docker start sql_employees.
And as soon as it starts, it's up now, we have to manage the database.
We have to insert the information, create indexes or
whatever we need to do.
And for that, as I told you before, we can use the command line tools,
mssql.
I'm gonna connect to a server that is localhost.
I'm gonna use the user sa with a password EgunOn17.
That means good morning in Basque, just for you to know.
And I'm gonna run this query, select @@version.
See, we are connecting to SQL Server.
But instead of using the common line tools,
I'm gonna use Visual Studio Code.
Because we have these extension that is free and
you can find on the Marketplace.
And with this extension,
when I'm on a SQL file that it's recognized as a SQL file,
as we can see below here, I have available those SQL commands.
Whoops, those SQL commands [LAUGH] that I can use to connect,
disconnect, execute queries and so on and so forth.
So when I click on Connect, I'm gonna
see a list with all the profiles that I have already created.
This is just telling the extension, where is my server,
which is the database that I want to use?
Which is the user?
Blah, blah, blah.
I have VideoAMP here.
Now it's supposed to be connected to VideoAMP.
So I can just select and run queries from Visual Studio Code.
This is the same one that we run from the command line.
And this is just another simple query to show you that we have some
data into the database.
So once we have seen this, the next step is connecting the application
that I have created with SQL Server in Linux.
On this case, we are using Node.
And with Node, you can use tedious and
tedious actually comes from T-D-S.
Is the acronym for
the protocol that you use [LAUGH] to connect to SQL Server.
And it's a CCS creating the connector, specifying the details of
the database that you are going to connect and done.
If I run this program, woo-hoo, let's make a small change here so
you see that it's actually happening.
node Helloworld.
Woo-hoo, connected, done.
Super easy.
However, we don't usually use the driver directly.
As a developer, we usually use an ORM.
If you're using .NET, you're using [INAUDIBLE] framework.
If you're using Node, you will probably use Sequelize.
So we have the same protocol here.
I use Sequelize, Sequelize underneath uses tedious.
I specify which are the datas for
the database that I'm gonna connect to.
And this is a super simple code that you can find on the website.
Creates a user, creates a task and
does a little bit of work with the user and the task.
Assigning it,
changing the data, changing the date, and deleting the task.
So if I run this sample, node ORMdemo,
here below you're gonna see that it worked.
And all the information that was sent to the console to the output.
And I also told you that I was gonna share a couple of features that
are not usually recognized by developers.
So that as a developer, maybe we don't know because we store
information, retrieve information, and done.
So, first of all, let's think about this JSON feature.
As developers, we run a query.
That query brings us back a table.
Then we go through the table.
We go back to the database to gather more information based on
the information that we have on the table.
So we have back and forth queries with a server until we
build a hierarchy that we need on the application side of things.
So instead of doing that, we can use either JSON or
XML and actually return hierarchies from the server.
So, we can better represent object and relationship between objects,
just with the information that is coming from the database.
So, as you can see, same query.
I'm just stating that I want to return information in JSON.
And this is the root node that I'm gonna use.
Let me execute it.
And of course this is gonna require you to make some changes on
the application side.
Because your application has to be ready to send and
receive JSON from the database.
But this is super convenient.
And as you can see here, this is just the information from the table
with the node that I have specified.
But I can always play a little bit more with the format that I'm
requiring the database to send me.
As you can see here, I have created the employee entity, I have created
the contact entity, and I can just use these on the application side.
Don't save.
And this was a feature that you can use,
but you will need to change something on your application side.
The next one, it's Columnstore indexes, and
you don't need to change anything on the application side.
First of all, let me create this table.
This is basically, as the name says,
a five million rows table and I'm gonna run this query.
I'm gonna run this query so
we see how much time does it take to calculate the sum
of one of those columns on a table with five million row.
I execute it.
And this is gonna be around 900 milliseconds.
I'm gonna execute it again so you see that this is pretty consistent,
900 milliseconds.
So now I'm gonna use this feature that has been available for
a long time on SQL Server,
but now on the latest versions, it has been improved.
I'm creating a CLUSTERED COLUMNSTORE INDEX
that is gonna help us with analytical workoads and
reducing the time and the space that we need for certain queries.
We see that it has been created.
I'm gonna do the same query again, measuring the time.
It was 900 millisecond.
It now is 17.
And I'm gonna do it again and again.
Seven milliseconds.
So as you can see, there is so many features available on SQL Server for
applications that both you can use from the application side of things
as a developer that is trying to get a little bit more productivity when
you're working against a database.
And also, on the server side, that you can just enable a few things,
like this columnstore index and you can get amazing on performance,
security, analytics, and so on and so forth.
And we are back to the slides.
And now that we have created, managed and
connected our application to SQL Server on Linux,
the only missing stage is deploying that application.
And based on your preferences, your environment, and your company,
you may want to decide to deploy to an On-Premises server,
like SQL Server or SQL Data Warehouse.
You can publish it to any kind of Docker based container
infrastructure like Kubernetes or Swarm.
Or you could publish it to the Cloud with those Window Azure Services or
maybe you have another Cloud preference or even your hoster.
I hope that this video was a good overview of what can you do with
SQL Server on Linux and all the steps that you need to go through to
install it, manage, connect, and deploy your applications.
If you need more information or want to find the sample code that we have
used, please browse aka.ms/sqldev.
You will find all the information there.
Thanks for watching this video.
[MUSIC]