Deploying ASP.NET Core with Azure SQL: A Step-by-Step Guide
In this post, we will see how to deploy an ASP.NET Core Web application which is getting data from an SQL server to show data on a web page to Azure App Service and Azure SQL Database.
Read on my Website | Read time: 5 mins
Introduction
In this article, we will see how to deploy an ASP.NET Core Web application which is getting data from an SQL server to show data on a web page to Azure App Service and Azure SQL Database.
Azure App Service is a fully managed Platform as a Service (PaaS) that enables you to build and deploy web applications, whereas Azure SQL is a cloud-based database service (PaaS) for hosting your relational databases onthe Azure cloud.
Sample Application
We have created a sample application and pushed it to GitHub. https://github.com/dotnetmirror/asp-net-core-sql-db-web-application/tree/master. Clone the application and run it on your local machine.
The sample application connects to the database table and creates, reads, updates, and deletes (CRUD operations) certification tables in the database. Below is the localhost of view of the page during debug mode.
Fig 1 - localhost view of ASP.NET Core Web App getting certificate data from local SQL Server
Now, let’s create an Azure App Service and an Azure SQL database on Azure and try to deploy the application. If you are new to the Azure portal, go through this video
step-by-step guide to creating a free Azure Account.
Create App Service
Connect to your Azure portal > Create Web App > Lets fill a few fields
• Subscription – select your subscription (Pay-As-you-Go)
• Resource Group – Create or select Existing ( I created dnm-aspnet-sql)
• Name - select a name for your web app (I picked up dnmaspcoresqldemo).
• Publish – Code
• Runtime Stack - .NET 6 (Our sample app is .NET 6 application). you can select your own based on stack.
• Operation System – Windows
• Region – South India (Prefer closer to you)
• Pricing Plan – I selected Free (it creates new service plan)
Remaining all options from deployment, networking, Monitoring, tags, you can ignore and create an application.
Once the App service is created, deploy the “Sample application” to the Azure App service using the Publish Profile option from Visual Studio or any other deployment methods. For more details, I created a full end-to-end demo about what Azure App service and how to deploy with Visual Studio and publish profile. Watch the video at
Create Azure SQL Database
Connect to your Azure portal > Create resource > Create SQL Database > Let’s fill a few fields
• Subscription – select your subscription (Pay-As-you-Go)
• Resource Group – select resource group which we used for App service(dnm-aspnet-sql)
• Database name – learning
• Server – Create new
o Enter server name (dnmdbserver)
o Location as “South India” (for better performance you have select App service and DB server at same location)
o Authentication – select SQL Authentication. Provide admin name and password. (Copy the password we will use to update the connection string later)
• Workload Environment – Development
• Computer + Storage – DTU Based 5 Basic
• Backup storage redundancy - LRS
Fig 2 - Create SQL Database - Azure Portal View
Once SQL server is service is deployed, connect to SQL Server and execute the script below from the file https://github.com/dotnetmirror/asp-net-core-sql-db-web-application/blob/master/SQLScript.sql
To connect to Azure SQL cloud data from local SQL Server Management Studio(SSMS), you have to enable your current working computer public IP. Also, select the checkbox “Allow Azure services and resources to access this service”, which enables the App service to connectto Azure SQL.
Fig 3 - Add Local IP to Azure SQL Networking to connect from local SSMS
Let’s check if SQL table script is inserted to Azure SQL Cloud database. From Azure Portal, open the created database > select “Query edition Option” > connect to the database using SQL Authentication > run the select statement to see inserted data. (Note - you can run insert script from here also instead of local SSMS)
Fig 4 - inserted script data available from Azure SQL DB
App Service and Azure SQL are deployed with scripts, now let’s try to update the connection string in the Azure App Service to connect SQL database on Azure SQL.
Get the connection string from Azure SQL
Open Azure > Open Azure SQL Database you created > Connection string from settings blade > copy ADO.NET connection string and you have to replace {your_password} with the password you used during SQL DB creation.
Fig 5 - Get connection string from Azure SQL DB
Add Connection string Azure App Service
Open the created web app in Azure App Service > Settings blade > Environment Variables > Connection strings > add a new one called “conStr” with Value as the connection string. > Save.
Fig 6 - Add connection string to Azure App Service via Environment Variables
Run the Application on Azure App service
Go to the created Web App in Azure App Service > Open the URL and you can see certification data coming from SQL server and the URL shows clearly it from Azure App Service. From below, if you look at URL, it is coming from Azure Web App and connected data from Azure SQL DB.
Fig 7 - Application running from Azure App Service and connected to Azure SQL DB
Delete Resources
If you do not need the created Azure resources, remove the Resource group. Otherwise, it incurs cost.
Conclusion
We have cloned a sample web application from GitHub, created an Azure App Service, and deployed the Web App to the App Service using Publish Profile.
Created an Azure SQL resource and executed a sample table and insert script data to get certificate data
Taken the connection string from Azure SQL DB and updated it in Azure App Service
Application running on Azure App Service, which is connected to Azure SQL DB.
References
Youtube Video
Wishing you a happy, healthy, and prosperous New Year!
this is great