This is part 22 of azure tutorial. In this video, we'll discuss, how to deploy a web application
with SQL database to azure. There are several approaches to this. Which approach you choose,
really depends on the data access framework you're using in your web application. For example,
if you're using entity framework code first approach, you can write code and execute SQL
migrations to create database, database objects like tables, views, stored procedures etc. You can
also seed the database with data. What if you are not using entity framework in your application?
Again, there are several approaches to migrate your SQL database to azure. In this video,
we'll migrate a web application that uses SQL server as the database and ADO.NET
as the data access technology. In our next video, we'll discuss how to migrate a web application
that uses SQL server as the database and entity framework as the data access technology. Now,
this is the application that we'll be deploying to azure. As you can see, it displays a list of
inspirational quotes. These quotes are being retrieved from a SQL server database.
Here is the project source code in visual studio. At the moment, we are looking at web.config file
and here is the database connection string. By looking at this connection string, you can say I'm
using SQL server local db in this project, but you can also use a full-blown SQL server if you want
to. It doesn't really matter, the process is still the same. Now, this is the web form that retrieves
the list of quotes from a SQL server database and displays them. To display the quotes, we're using
repeater control and if we take a look at the code behind file, we have the classic ADO.NET
code. Within this GetQuotes() method, as you can see, the first thing that we are doing
is, using configuration manager class to read the database connection string from web.config file
and then we are creating a sql connection object using that connection string and then our sql
command object and this is a standard and straightforward sql query - Select quote from
QuotesDB, and if I execute this query on my local machine, we have five quotes within the database
table and those are displayed right here on the web page. So, this is the sql query that we want
to execute. We open the connection. Execute the query and while we are looping through each quote,
we are adding the quote text to this List variable and finally returning that list and
that list is being set as the data source for the repeater and calling the DataBind() method. So,
this is the web application that we want to deploy to azure. In our previous video,
we discussed how to deploy a web application that does not use database to azure. Now, the process
to deploy a web application that uses database is also very similar. The only difference is,
you have to perform two more additional steps . So, if you haven't checked out our previous video,
please check out that video. I'll include the link for that video in the description
of this video. There are three simple and straightforward steps to deploy a web application
with SQL server database to azure. The first step is to deploy the web application itself. Next,
create SQL server in azure and finally create the database objects like tables, stored procedures
etc and seed the database tables with initial data. The first step that is deploying the web
application itself is very identical to what we have learnt in our previous video. So,
in order to avoid repetition and in the interest of time, I'm going to refer you to that video. As
I said earlier, I'm going to include the link for that video in the description of this video. Now,
let's deploy this web application to azure. For that, right click on the project name
and then select this option - "Publish". We are on the "Publish" window. We want to deploy this
web application to azure. For that, select your app service from the drop down list right here.
We discussed, how to create app service and app service plan in azure right from within visual
studio in our previous video. So, select your app service and then hit this "Publish" button
to deploy this web application to azure. There we go, we have server error - No such host is known,
and if we take a look at the exception, we actually have a sql exception. We have a problem
establishing a connection to SQL server. Why? because we don't have SQL server in azure. So,
our obvious next step is to create sql server in azure. Again, there are several ways to do that.
We can either create the sql server directly in the azure portal or we can do that from visual
studio. Let's do it from visual studio. We are back on the "Publish" window in visual studio.
What we want to do now is, add a sql server dependency for this web application project.
For that, click this "Add" button. So, we want to add a new service dependency. As you can see,
we can add several different dependencies. What we want to add right now is, Azure SQL Database
dependency, select that and click Next. From this drop-down list, select your azure subscription.
If you have a sql server already configured in azure, that server will be listed here and if
you want to use that server you simply select that server from the list. In our case, we don't have
any SQL server configured yet. We want to create a new sql server. So, let's click this "+" sign,
provide a name for your database. I'm going to name it QuotesDB and then select
your azure subscription and a resource group. To be able to create a database,
we first need a database server. We don't have any database server in azure yet. So,
first let's create a new database server. For that, click on this "New" link. Provide
a name for the database server. I'm going to leave the default name and the location
where you want to create this database server and then an administrator username, password,
confirm password and then click - OK. On this window, click "Create". As you can see,
both our SQL server and the database are being created in azure. There we go, both our database,
as you can see, the database name is QuotesDB and the sql server itself, the server name
is "quotesdbdbserver" are created. Click Next. On this screen, provide a name
for your database connection string. I'm going to name it "QuotesDBConnectionString" and here
is the important bit to keep in mind. This is the connection string that will be used
by our application deployed in azure to connect to our sql server instance in azure. Obviously,
for that to happen, we also need to provide the username and password and then you can
see the connection string value if you want to by clicking this - "Show Secret" button. You can also
copy it to clipboard by clicking this button and then you can also specify where you want to save
this connection string - Azure App Settings or Key Vault. If you're new to these two concepts,
we'll discuss them in our upcoming videos. Click Next. On this screen,
we see the two nuget packages that will be added to our project. Finally click "Finish".
There we go, azure sql server database dependency is successfully configured. Click the "Close"
button. At this point, notice what happens when we refresh this web page. We have a different error,
again a sql exception - Invalid object name "Quotes". It's trying to retrieve the list
of quotes from the Quotes database table, but it can't find that table and that makes sense because
in azure we only created the sql server and database, we did not create the required database
objects, like in this case, the "quotes" database table. So, the third and final step is to create
the quotes table and populate it with initial data. Again, there are several ways to do this.
We can either use sql server management studio on our local machine and connect to sql server in
azure and execute queries to create the required database objects. Even the azure portal has got an
inbuilt query editor. We can use that as well if we want to. We'll look at both the options. First,
sql server management studio. We want to connect to sql server in azure. So,
within object explorer, right click and then select this option - "Connect".
We need to specify the name of our sql server in azure. We can find that in the azure portal.
Here is our resource group. Click on that. These are all the resources in our resource group and
here is our sql server. Click on that. You can find our SQL server fully qualified name right
here. Click this button, to copy it to clipboard. Paste it here, within SQL server management studio
and we want to use SQL server authentication to connect. Specify the username and password
and then click - "Connect". We have an error. For security reasons, by default, our sql server
instance in azure is locked down and it will not allow any connections to it. If you want
to be able to connect from your local machine and execute sql queries, you have to create a
new firewall rule that enables access. We can do that either from sql server management studio by
logging in to azure or we can do that from the azure portal. Let's do it from the azure portal.
At the moment, we are on the "Overview" blade. Scroll down and under "Security" click this
option - "Firewalls and Virtual Networks". Now, here you can see my local machine IP address. So,
we want to allow this machine to be able to connect to our sql server in azure. So, for that,
we need to add a new firewall rule. Click this - "Add client IP" button. Notice, it added a
new firewall rule which allows connection from my local machine. Click "Save". Let's try to connect
again from sql server management studio. There we go, we are connected, and if we expand databases
we can see our QuotesDB database in azure. Let's right click on this and launch a new query editor.
Let me paste the queries that we want to execute. First, we want to create the table itself. Let's
execute the "create table" query. We have the database table created. So, let's reload this page
and see what happens. We don't have an error now, but we don't see any quotes and that makes sense
because, our "quotes" table in azure is empty. We want to execute our "Insert" queries. Let's do
that from query editor in the azure portal. At the moment, we are looking at our sql server instance
in azure. This sql server instance has got only one database and to see that database, click
on this option - SQL Databases. So, here is our database and click on that. We want to get to the
query editor, so click on this option. As you can see it's still in preview, but it works great.
Obviously, to be able to connect, you need to provide the login username and password.
There we go, we are connected. Let me paste the insert queries and click this "Run" button to
execute. Successfully executed. Let's now reload our web page. There we go.
We see the list of quotes as expected. That's it in this video. Thank you for listening.