Azure SQL Database


Azure SQL database uses platform as a service, which is an intelligent, scalable, cloud database service that provides the broadest SQL Server engine compatibility. You can accelerate your on-premises SQL Server migrations without changing the application code with Managed Instance. Always have the latest SQL Server capabilities in the cloud with an evergreen SQL database that requires no patching or upgrading from you.
This article is divided into two parts, the first part is more about concepts and the second is hands on in Azure portal t0 create SQL database and using in .net applications with front end HTML.

Azure SQL Database 

Azure SQL database is used as platform as a service but it can be used as infra as service if you plan to use SQL database using an Azure virtual machine. But you will still be responsible for managing the server, like backing the DB etc if you use it as Iaas.
In Pass, you don’t need to worry about maintenance and availably and security, that is managed by Azure, a managed service where Microsoft patches all security inside of it and keeps it  up to date.
It has two purchasing models,
  • vCore – pay money by total by number of cpu cores used.
  • DTU – data base transaction unit, which measures based on the number of transactions done in the DB.
Azure SQL data has the following deployment options.
  • Managed instance – server in which SQL DB can be deployed, does not mange server.
  • Single DB – single SQL DB where you can go to store data, it comes with a logical server where managed instance comes with virtual network like VPN, gateway etc. managed by Azure.
  • Elastic pol – it's suitable for storing data by the concept of auto scaling.

Choosing the right SQL option in Azure

Azure SQL Database

Access Azure SQL database using .net

This demo is about creating a SQL server Database and working with it using an application. The setup includes SQL server creation, configuring firewall, connecting to SQL database and inserting data to it.
Step 1
In the Azure portal, go to + Create a resource -> Database -> SQL Database.
Azure SQL Database 
Step 2
Give a unique name and choose a subscription and a resource group. Let it be a blank database. Create a server with a name and username and password as denoted in the below given image and deploy the database. 
Azure SQL Database
Change your DTU and vCore options by clicking on the "Configure database" option, once the DB is created, note the servername on overiview tab; i.e. in my case. That will be used later to connect this Azure DB from Visual Studio.
Step 3
Configuring Firewall, in the overview bladed of the database, click on the set server firewall option to configure access to the server.
Azure SQL Database
After that click on the + Add client IP option to add your machine's IP to have access to the server. If in case you want to allow access for an entire organization, you can manually add the IP ranges as allowed in the address in the option denoted below. After adding the IP, click on save button. Do not forget to switch on "allow azure services and resources"
Step 4
Once after your login, all the resources in your subscription will be displayed in the server explorer pane. There, right click on Data Connections and choose Add Connection.
Once after your login, all the resources in your subscription will be displayed in the server explorer pane. There right click on Data Connections and choose Add Connection.
Step 5
You can find the address in the overview page of your SQL Database that you created in Azure portal. Next choose SQL Server Authentication for authentication and enter the username and password of your SQL server. Next choose the database that you created in the Azure portal from the dropdown list. Make sure to select the appropriate options. If you miss any, you won’t be able to execute the demo correctly. After choosing all the options, click on Ok button.
Azure SQL Database
Step 6
Add a new table in the database. You will now see a querying table. In there, enter the query to create a database table. After that click on execute button in the top left corner to execute the script. After the successful execution of the script you will get an output stating that the table is created. The code is as follows.
  1. CREATE TABLE [dbo].[empTable]       
  3. [emp_id] INT NULL,       
  4. [name] NVARCHAR(50) NULL,       
  5. [education] NVARCHAR(50) NULL,       
  6. [email] NVARCHAR(50) NULL);     
Azure SQL Database 
Step 7
Now create your any UI front end application and start using this sql database. 
  1. SqlConnection con = new SqlConnection("Data;Initial   
  2. Catalog=mssql;User ID=username;Password=password");    
  4. SqlCommand cmd = new SqlCommand("insert into empTable(emp_id,name,education,email)   
  5. values('" + EnterEmpIdfromUI + "','" + TEnterNamefromUI  + "','" + EnteredEdufromUI   
  6. "','" + EnteredEmailfromUI + "')", con);    
  8. con.Open();    
  10. cmd.ExecuteNonQuery();    
  12. con.Close();    
Viewing Inserted Data
Go to solution explorer in the view menu. Under the table menu, right click on the table that you created and choose Show Table Data. You will be shown a table that holds your data in azure SQL database.
Azure SQL Database
Azure SQL Database
You will notice how schema and tables are generated and data is instered in the database which is managed by the Azure service as Paas. 


Here, we learned about Azure SQL database with concepts and hands on Azure portal and consuming in .NET application.