Azure SQL Database

Introduction

 

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

 

  

  

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.

 

 

 

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. 

 

 

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. mydbsql200.database.windows.net 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.

 

 

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.

 

 

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.

CREATE TABLE [dbo].[empTable]
([Id] INT NOT NULL PRIMARY KEY IDENTITY,
[emp_id] INT NULL,
[name] NVARCHAR(50) NULL,
[education] NVARCHAR(50) NULL,
[email] NVARCHAR(50) NULL);

 

 

Step 7

 

Now create your any UI front end application and start using this sql database. 

SqlConnection con = new SqlConnection("Data Source=servername.database.windows.net;Initial
Catalog=mssql;User ID=username;Password=password");

SqlCommand cmd = new SqlCommand("insert into empTable(emp_id,name,education,email)
values('" + EnterEmpIdfromUI + "','" + TEnterNamefromUI  + "','" + EnteredEdufromUI
+ "','" + EnteredEmailfromUI + "')", con);

con.Open();

cmd.ExecuteNonQuery();

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.

 

 

 

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. 

 

Conclusion

 

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