Lab: Implementing Azure SQL Database-Based Applications
Student lab manual
Lab scenario
Adatum Corporation has a number two tier applications with .NET Core-based front end and SQL Server-based backend. The Adatum Enterprise Architecture team is exploring the possibility of implementing these applications by leveraging Azure SQL Database as the data tier. Given intermittent, unpredictable usage of the existing SQL Server backend and relatively high tolerance for latency built into the front-end apps, Adatum is considering the serverless tier of Azure SQL Database.
Serverless is a compute tier for individual Azure SQL Databases instances that automatically scales compute based on workload demand and bills for compute used per second. The serverless compute tier is also capable of automatically pausing databases during inactive periods when only storage is billed and automatically resumes databases when activity returns.
The Adatum Enterprise Architecture team is also interested in evaluating network-level security provided by the Azure SQL Databases, in order to ensure that it is possible to restrct inbound connections to specific ranges of IP addresses, in scenarios where the apps must be able to connect from its on-premises locations without relying on hybrid connectivity via Site-to-Site VPN or ExpressRoute.
To accomplish these objectives, the Adatum Architecture team will test Azure SQL Database-based applications, including:
-
Implementing serverless tier of Azure SQL Database
-
Implementing .NET Core console apps that use Azure SQL Database as their data store
Objectives
After completing this lab, you will be able to:
-
Implement serverless tier of Azure SQL Database
-
Configure .NET Core-based console apps that use Azure SQL Database as their data store
Lab Environment
Windows Server admin credentials
-
User Name: Student
-
Password: <YOUR PASSWORD>
Estimated Time: 60 minutes
Lab Files
- None
Exercise 1: Implement Azure SQL Database
The main tasks for this exercise are as follows:
-
Create Azure SQL Database
-
Connect to and query Azure SQL Database
Task 1: Create Azure SQL Database
-
From your lab computer, start a web browser, navigate to the Azure portal, and sign in by providing credentials of a user account with the Owner role in the subscription you will be using in this lab.
-
In the Azure portal, search for and select SQL database and, on the SQL databases blade, select + Create.
-
On the Basics tab of the Create SQL Database blade, specify the following settings (leave others with their default values):
Setting Value Subscription the name of the Azure subscription you will be using in this lab Resource group the name of a new resource group az30303a-labRG Database name az30303a-db1 -
Directly below the Server drop down list, select the Create new and, on the New server blade, specify the following settings and select OK (replace the <YOUR PASSWORD> placeholder with a password of your choice and leave other settings with their default values):
Setting Value Server name any valid, globally unique name Server admin login sqladmin Password <YOUR PASSWORD> Location the name of an Azure region where you can provision SQL databases -
Next to the Compute + storage label, select the Configure database link.
-
On the Configure blade, select Serverless, review the corresponding hardware configuration and auto-pause delay settings, leave the Enable auto-pause checkbox enabled, and select Apply.
-
Back on the Basics tab of the Create SQL Database blade, select Next: Networking >.
-
On the Networking tab of the Create SQL Database blade, specify the following settings (leave others with their default values):
Setting Value Connectivity method Public endpoint Allow Azure services and resources to access this server No Add current client IP address Yes -
Select Next: Security >.
-
On the Security tab of the Create SQL Database blade, specify the following settings (leave others with their default values):
Setting Value Enable Azure Defender for SQL Not now -
Select Next: Additional settings >.
-
On the Additional settings tab of the Create SQL Database blade, specify the following settings (leave others with their default values):
Setting Value Use existing data Sample -
Select Review + create and then select Create.
Note: Wait for the SQL database to be created. Provisioning should take about 2 minutes.
Task 2: Connect to and query Azure SQL Database
-
In the Azure portal, search for and select SQL database and, on the SQL databases blade, select the entry representing the newly created az30303a-db1 Azure SQL database.
-
On the SQL database blade, select Query editor (preview).
-
In the SQL Server authentication section, in the Password textbox, type the password of your choice (<YOUR PASSWORD>) and select OK.
-
In the Query editor (preview) pane, on the Query 1 tab, enter the following query and select Run:
SELECT TOP 20 pc.Name as CategoryName, p.name as ProductName FROM SalesLT.ProductCategory pc JOIN SalesLT.Product p ON pc.productcategoryid = p.productcategoryid;
-
Review the Results tab to verify that the query completed successfully.
Exercise 2: Implement a .NET Core console app that uses Azure SQL Database as their data store
The main tasks for this exercise are as follows:
-
Identify ADO.NET connection information of Azure SQL Database
-
Create and configure a .NET Core console app
-
Test the .NET Core console app
-
Configure Azure SQL database firewall
-
Verify the functionality of the .NET Core console app
-
Remove Azure resources deployed in the lab
Task 1: Identify ADO.NET connection information of Azure SQL Database
-
In the Azure portal, on the blade of the Azure SQL database you deployed in the previous exercise, in the Settings section, select Connection strings.
-
On the ADO.NET tab, note the ADO.NET connection string for SQL authentication.
Task 2: Create and configure a .NET Core console app
-
In the Azure portal, open the Cloud Shell pane by selecting on the toolbar icon directly to the right of the search textbox.
-
If prompted to select either Bash or PowerShell, select Bash.
Note: If this is the first time you are starting Cloud Shell and you are presented with the You have no storage mounted message, select the subscription you are using in this lab, and select Create storage.
-
From the Cloud Shell pane, run the following to create a new folder named az30303a1 and set it as your current directory:
mkdir az30303a1 cd az30303a1/
-
From the Cloud Shell pane, run the following to create a new app project file for a .NET Core-based app based on the desktop template:
dotnet new console
-
In the Cloud Shell pane, use the built in editor to open and modify the az30303a1.csproj file by adding the following XML element between the
<Project>
tags:<ItemGroup> <PackageReference Include="System.Data.SqlClient" Version="4.6.0" /> </ItemGroup>
-
Save and close the az30303a1.csproj file.
-
In the Cloud Shell pane, use the built in editor to open and modify the Program.cs file by replacing its content with the following code:
using System; using System.Data.SqlClient; using System.Text; namespace sqltest { class Program { static void Main(string[] args) { try { SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(); builder.ConnectionString="<your_ado_net_connection_string>"; using (SqlConnection connection = new SqlConnection(builder.ConnectionString)) { Console.WriteLine("\nQuery data example:"); Console.WriteLine("=========================================\n"); connection.Open(); StringBuilder sb = new StringBuilder(); sb.Append("SELECT TOP 20 pc.Name as CategoryName, p.name as ProductName "); sb.Append("FROM [SalesLT].[ProductCategory] pc "); sb.Append("JOIN [SalesLT].[Product] p "); sb.Append("ON pc.productcategoryid = p.productcategoryid;"); String sql = sb.ToString(); using (SqlCommand command = new SqlCommand(sql, connection)) { using (SqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) { Console.WriteLine("{0} {1}", reader.GetString(0), reader.GetString(1)); } } } } } catch (SqlException e) { Console.WriteLine(e.ToString()); } Console.WriteLine("\nDone. Press enter."); Console.ReadLine(); } } }
-
Leave the editor window open.
-
In the Azure portal, on the blade displaying the connection strings for the az30303a-db1 database, copy the ADO.NET connection string.
-
Switch back to the editor window and replace the placeholder
<your_ado_net_connection_string>
with the value of the connection string you copied in the previous step. -
In the connection string you copied into the editor window, replace the placeholder
{your_password}
with the password of your choice (<YOUR PASSWORD>). -
Save and close the Program.cs file.
Task 3: Test the .NET Core console app
-
From the Cloud Shell pane, run the following to compile the newly created .NET Core-based console app:
dotnet restore
-
From the Cloud Shell pane, run the following to execute the newly created .NET Core-based console app:
dotnet run
-
Note that the execution of the console app will trigger an error.
Note: This is expected, since the connection from IP address assigned to the virtual machine running the Cloud Shell session must be explicitly allowed.
Task 4: Configure Azure SQL database firewall
-
From the Cloud Shell pane, run the following to identify the public IP address of the virtual machine running the Cloud Shell session:
curl -s checkip.dyndns.org | sed -e 's/.*Current IP Address: //' -e 's/<.*$//'
-
In the Azure portal, on the blade displaying the connection strings for the az30303a-db1 database, select Overview and, in the toolbar, select Set server firewall.
-
On the Firewall settings blade, set the following entries and select Save:
Setting Value Rule name cloudshell Start IP the IP address you identified earlier in this task End IP the IP address you identified earlier in this task Note: Obviously this is meant for the lab purposes only, since that IP address will change after you restart the Cloud Shell session.
Task 5: Verify the functionality of the .NET Core console app
-
From the Cloud Shell pane, run the following to execute the newly created .NET Core-based console app:
dotnet run
-
Note that the execution of the console app will this time be successful and that it returns the same results as those displayed in the query editor within the Azure portal SQL database blade.
Task 6: Remove Azure resources deployed in the lab
-
From the Cloud Shell pane, run the following to list the resource group you created in this exercise:
az group list --query "[?starts_with(name,'az30303')]".name --output tsv
Note: Verify that the output contains only the resource group you created in this lab. This group will be deleted in this task.
-
From the Cloud Shell pane, run the following to delete the resource group you created in this lab
az group list --query "[?starts_with(name,'az30303')]".name --output tsv | xargs -L1 bash -c 'az group delete --name $0 --no-wait --yes'
-
From the Cloud Shell pane, run the following to remove the folder named az30303a1:
rm -r ~/az30303a1
-
Close the Cloud Shell pane.