[MUSIC]
Hi, my name is Tara and I'm a Senior Technical Product Manager with
the Cloud and Enterprise Data Division.
Today we are here to talk about how to develop applications using
SQL Server on Linux, Entity Framework, Azure and DevOps.
As you can see, most of the key features of SQL Server 2016
are included in SQL Server on Linux.
Such as the new security features for, say,
transparent data encryption, always encrypted, row level security.
And also the advanced features like in memory tables, columnstore,
and more.
Functionality that you would expect to be there will be there,
full text search, SQL Server agent, replication, and more.
Microsoft and third-party tools running on Windows work just fine
when connected to SQL Server on Linux.
And the best part, we have native command line tools for Linux, too.
All the applications work in various languages like C sharp, C++,
Java Python, Note, PHP, and
more, just work fine when connecting to SQL Server on Linux.
In short, this is the same SQL Server you know, but
now it's native to Linux.
All right, before we jump into the demo,
I want to show you quickly the schematics.
As you can see, this is a microservices architecture and
the customer facing applications are all pointing to Azure SQL database.
About the scenario.
BikeSharing360 is expanding at a rapid pace and
one of the criterias for BikeSharing 360 success is to validate
the compliance and performance of the hosted application.
To do so, they wish to test the application in house.
Let's take a close look at how BikeSharing 360 puzzles is
developing the application for achieving higher scale and
security through this demo.
As mentioned, to test the application in house BikeSharing 360
has chosen Linux and SQL Server as the platform for local development.
We have a SQL container with the application database for
BikeSharing 360 preinstalled as you see on your screen.
You can also install SQL Server on a Linux, in a VM,
in less than 60 seconds, on bare metal or in a docker container,
as you would have seen in one of the earlier videos in the series.
All right, let me show what I've done here.
I have get cloned the complete repo for BikeSharing 360 to my local
environment and I'm using VS Code to develop and deploy this application.
If you notice carefully, the code is based on entity framework,
that is an object relational mapper that enables .NET
developers to work with relational data using domain specific objects.
It eliminates the need for
most of the data access code that developers usually need to write.
Okay, let's go ahead and run the application.
I'm going back to my terminal and I'm just gonna go and
run, My application right here.
Now as you could see that I'm running this application,
my localhost and my Database is bikesharing-rides-inmemory.
Okay, there's certain challenges which BikeSharing 360 is
facing at the moment.
The widget for showing the total trip distance in the app is slowing
down the load time of the home page.
We won't test this out using a Rest API client.
All right, let me just go ahead and pull the Rest API client up.
I'm gonna click on API rides distance, and
what it's internally doing is, is securing a T-SQL query that
calculates the total distance traveled by all bike riders so far.
Since the data is huge, this calculation takes awhile.
Let's go run this.
Okay, so we took around three seconds, right?
Let's also look at the details here,
and if you see here, the wait time is around that much, right?
So we waited a long time to make that connection.
I think, if we're being more realistic, let's run this again so
the data is now in the cache, and we should see a better result against
the database in the table when we run this query.
That's more realistic, so we took around 477 milliseconds to run this
particular query and the total distance has now been calculated.
And as you can see this is an analytical query and
it needs some more theoretical processing in the back end.
Okay, now as we saw this took a bit of awhile
the question is how do we solve this challenge.
Let me show you some cool stuff.
For the analysis that we have been doing on how we can make it better.
Let me just minimize these windows quickly.
We figured out that inMemory could be a great, great, great option for
us to resolve the query performance time.
We can fix the slow query time by making the rides table and InMemory
table in SQL Server and also by adding clustered columnstore index.
This is the query which we just saw is an unequal query, and the ride
distance column is a numerical column and we wanna do a sum on it.
Great candidate for CCI, or columnstore index.
The great news is that I wrote the code in entity framework that makes
enabling InMemory super, super easy.
Just a single line of code and we have the tables in memory.
Let's do that.
So right here, that's it.
And once you run this application the tables are gonna be in memory.
To save time in this particular demo,
we have already migrated the table to InMemory.
But this is exactly what you need to do behind the scenes in your code
to get this table into memory.
All right, let's go back to the application and
run that app once again.
I'm gonna start this, leave this out it, and I'm gonna run this again.
And we're gonna do the same thing with just the displaced tables.
Let's go back to the Rest API client and
see how much difference it has made since we have added in memory and
columnstore index to this rides table.
So I'm gonna go and pick up the Ride API we're gonna be using here.
And we'll go to the same thing.
So first one certainly it's gonna take awhile.
But still around two and
a half seconds and we waiting on making the connection.
So the realistic picture will be once we run the square once again,
or call the Rest API client against the table once again.
Look at that, 28 milliseconds, almost 20 to 30 times faster
performance, compared to what we got on this table.
Awesome, isn't it?
This is the wow factor.
What we need to still do here is the second part of the puzzle, right,
and solve that.
The second part of the puzzle or
challenge here is we have some security concerns.
Let's go and look at the security concern from this Rest API,
again, as the application's running.
Every time the customer uses the application they have to kind of go
through a custom for filing.
We have a profiles API and we want to check how their data looks like
when kind of comes to the database.
So let's have a look at that.
And as you can see the data is showed in clear text.
There's some great, great,
great opportunities here for us to add some features like
always encrypted which keeps your data at rest and transit secure.
But also the application layer, there are concepts or
technologies or features like dynama data masking roll over
security which is available to you today on sequel server on Linux.
Lets pick one of them.
I just want to go and mask the email addresses of all these customers
because I think that's not compliant with the business requirements of
BikeSharing 360.
How do we do this?
It's super simple.
Let's go back to Visual Studio code, and
we'll keep the application running as is.
And we have dynamic data masking.SQL statement, and
how do you enable this?
You alter the table profiles, and then you alter the column email and
it only have an function,
and there's some partial functions as well.
And for this particular scenario, it's e-mail.
Let's go and run this.
Best part with Visual Studio code, you have the Visual Studio SQL
extensions, and you can run the query from the Visual Studio code.
So, we're just gonna go and
run this against the InMemory tables, Database.
And that should run the query, which is awesome.
Let's go back to the Rest API client.
As you can see, I'm not re-running the app, or
there's no change I have to do on the application layer itself.
I'm just gonna go ahead and run this query once again, and voila,
have a look at that, beautiful.
Now I have got the email field masked, and there could be different
fields depending on what is considered as a PII information for
the organization and it can mask the data set based on that.
Great, now we have done so much.
The next thing we wanna do here is to achieve and
deploy this whole application changes to my production, right?
Now that I'm pretty happy with the application,
let's deploy this application to a staging environment in the cloud.
The first step is to generate entity framework migrations script,
let me show you how to do that.
So we're gonna come back to the terminal.
I'm just gonna go and stop the application, clear this out a bit,
and we're gonna run dotnet ef migrations script>sql/migrate.sql.
This is gonna create the complete migration script we need to be
deployed to our production of staging environments.
Okay, once I run this now you could see migrate.sql shows up
here in my lists here, right.
So let's have a closer look at this quickly, just close one or
two of these windows, that's fine.
All right, now if you see this, this is the complete migration script
where DDM or Dynamic Data Masking, InMemory and CCI, right?
We're pretty much good to go, and migrate this to our
SQL Server in the cloud, or I should say, SQL database in the cloud.
But before that, we need to go commit this to our VSTS repo, right?
So let's go back and look at our git repo here.
And as you can see that the changes are not showing up.
I'm just gonna go and push a commit.
And that will just enable my CICD pipeline or my DevOps pipeline, and
that will start implementing those changes back into my production.
Before we go live, there's one thing I want to test and
that is to ensure that my staging looks perfectly fine, right,
with the changes we have made.
So let's go and do some quick checks here.
What I'm wanna do is I'm just gonna go and
export my ASPNETCORE_ENVIRONMENT=stage.
And I'm gonna run this application once again.
And now you will see that this is connecting to my Azure SQL database
back in the cloud, right?
Just a flip of connection string and
we're pretty much good to go and test our application.
So let's just ensure that this works.
I'm just gonna go back to that same Rest API client.
And I'm gonna run this query or I should just invoke this Rest API
again and see if the data shows up exactly the way it's showing up now.
Which means the dynamic data masking is enabled,
InMemory is in the loop and Columnstore index as well.
But let's just test one of the features.
So let's go and send this query.
And look at that, beautiful.
So even in the cloud,
same dataset, same dynamic data masking was applicable.
So, whether you are in SQL Server on Linux,
SQL Server on Windows, SQL DB in the cloud, the code basis is the same.
We don't make application changes, but every single change, it may come
one single SQL technology, it just kind of replicates across, right?
Beautiful, okay so in a short while, we saw pretty major things.
One, how Entity Frameworks works, works with SQL Server on Linux.
Second, how do you enable advanced features into the application?
And third, how by changing a single flip of the connection string of
data is still the same and
the changes you made are still showing up in the cloud?
All right, so we just saw how we develop applications using entity
framework, SQL Server on Linux, DevOps and Azure, go and
gitclone the following repositories.
And if you wish to know more about SQL Server on Linux,
go to our landing page.
Thank you so much for watching, have a good day.
[MUSIC]