Azure Web App. and Azure SQL

Azure offers a treasure house of SaaS, IaaS, and PaaS services. The most popular and basic bread and butter tools for a developer to get to know are the Azure Web App. and Azure SQL.

Azure Web App.

Azure Web App. is an HTTP-based Azure service that allows the building and hosting of web-based applications written in various languages and platforms such as the .NetCore (2.1 to 6)/Framework (3.5,4.8)/C#, Java (7 – 11), PHP (7.3 -8), Python (3.6 – 3.9), Ruby (as of this writing – MS might plan to add more later, I think) without thinking about any infrastructure to host them.

Setting up a sample Azure web. app

To set up a sample Azure Web App., log in to your Azure free account on Azure portal. On the Home page or Dashboard, click on the ‘Create a resource’ icon. You will see the various resources that you can create on the left-hand menu bar and the most popular ones on the right main information page. Click ‘Create’ under ‘Web App’. Make below settings such as the Web App. name, resource group etc. Make sure to set the free plan under the ‘Sku and Size’ section.

Once you are done with the settings, click ‘Review + Create’, and you will see the below preview. Make sure the details are correct and then click ‘Create’.

Clicking ‘Create’ will kick start the creation and deployment of the resource where you can later host your .NetCore web app. You will see the below message once the deployment is complete.

Click on ‘Go to resource’ to see the details of the Web App. resource that was just created –

Publishing your Web app. on Azure Web App. service

Once your Azure Web. App. service is created and set up, you can now deploy your desired web application on it for the outside world to use, right from your IDE – eg: Visual Studio.

To do this, go to your web application (in this case, it is a sample .NET5 app) and go to Publish section. Select Azure as shown below –

Cick Next.

Click Next and you will see below screen with details filled based on your Azure/MS account that you are signed on to. In this case, we are using MS learning sandbox account and that instance is selected to deploy the application to –

Click on your Web app. service instance and click ‘Finish’. Once successfully setup, you will see below status –

Click ‘Publish’ to deploy your application. Once successfully published, you will see below status –

You can now click on your Web App. link shown above to see your web application in action!

Azure SQL

Azure SQL is a good alternative to maintaining a full-blown SQL server instance/s on your own even on an Azure VM instance. It provides the complete PaaS solution where you can host your SQL DB/s and scale it up automatically based on your app. usage. In addition, you don’t ever have to worry about OS/Server patches, security, or anything operationally related.

Azure generally offers two options for you based on your scaling needs. DTU (Database Transaction Units) model allows you to choose fixed storage, IO, compute setup where your individual DB instances can grow within based on their individual usage. If you have multiple DBs you can choose the eDTUs (elastic DTU) option. The second option is called vCore option where you have control over each piece i.e you can choose how much computing power you need keeping the IO and storage unchanged. For example, you might have an AI-based web application that uses heavy AI computing that is bound to change often but your DB transactions and storage are more or less stable. Either option lets you create logical server units where you will house your DBs.

Creating and setting up Azure SQL

Go to Azure portal and Click on ‘Create Resource’. Then Click on link ‘Create’ under SQL Database –

Fill in the appropriate information for Subscription and resource name. Provide details for the DB server as well –

Click on ‘Ok’ on the DB server config. screen and the DB server will be populated. Now Click ‘Compute + Storage’ option and select below option for a simple DB solution –

Leave defaults for everything else on the other tabs and click ‘Review+Create’. You should see below –

Now Click ‘Create’ to create your Azure SQL logical container for the specified DTU. Once the Azure SQL is created successfully, you will see below status page.

The above dashboard shows that your DB server oldb123 is ready to use You can use Visual Studio or SQL Server Management Studio to connect to and manage the DB server to create and manage your DB objects. Another option is to use Azure Cloud Shell, which is basically a Linux shell specifically created for you housing your logical DB server. You can connect to it just like a Linux server and can use az, the Azure CLI command to pass in piped commands to sqlcmd command to interact with the DB server. Let see some sample commands to interact with your DB server.

1. Configure azure group – Use below command to configure your azure group –

@Azure:~$ az configure –defaults group=learn-9c558143-bc5b-47e8-a95f-1a8f9fc69dde sql-server=oldb123

2. Listing the DBs – Use below command to list the databases on your logical server instance –

@Azure:~$ az sql db list | jq ‘[.[] | {name: .name}]’
[
{
“name”: “master”
},
{
“name”: “oldb123”
}
]

‘jq’ is the command that we use to convert the piped json output of az db list command and just return the values with matched values. So in our case, the command returns the DB that we created – oldb123.

3. Connecting to your DB

Use below command to get the connection string first –

az sql db show-connection-string –client sqlcmd –name oldb123

Issuing the above command will return “sqlcmd -S tcp:oldb123.database.windows.net,1433 -d oldb123 -U -P -N -l 30”. This will be the connection string for your DB. Execute this command on the CLI supplying the username and password that you created while setting up the DB in the above steps.

@Azure:~$ sqlcmd -S tcp:oldb123.database.windows.net,1433 -d oldb123 -U oldb123usr -P ‘<password>’
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Cannot open server ‘oldb123’ requested by the login. Client with IP address ‘xx.xx.xx.xx’ is not allowed to access the server. To enable access, use the Windows Azure Management Portal or run sp_set_firewall_rule on the master database to create a firewall rule for this IP address or address range. It may take up to five minutes for this change to take effect..

If the above error occurs, you will need to add your client IP on the Azure SQL instance firewall. Go to your Azure SQL dashboard and click ‘Set server firewall’ icon –

On the page that follows next, Click on ‘Add Client IP’ and add the above IP that came with the error as shown below –

Keep all default settings and leave Start / End IP the same. Click ‘Save’. Now, execute the command above to connect to the DB –

@Azure:~$ sqlcmd -S tcp:oldb123.database.windows.net,1433 -d oldb123 -U oldb123usr -P ‘Abc!2345’
1>

You can see that now you are connected to the sqlcmd shell where you can execute your sql commands. Lets create a table and run some DMLs.

Execute the below command to create table Employee –

create table Employee (EmpID int, Payroll# int, LastName varchar(255), FirstName varchar(255));
2> GO

Now lets insert some rows in it –

insert into Employee (EmpID,Payroll#,LastName,FirstName) values (1,345, ‘First’,’Last’)

Run below command to see the row you just inserted –

select * from Employee;
2> GO
EmpID Payroll# LastName FirstName

---

      1         232 First                                                Last

(1 rows affected)

That’s it. You have just learned how to create a basic Azure Web app. service and host a sample web application on it and also how to create an Azure SQL instance and create and manage your database. Enjoy!