Azure SQL Database
Microsoft is enhancing its Azure SQL Database by adding various functionalities to make it more intelligent than it already is. The latest among them is Hyperscale in vCore-based purchasing model that supports a database of size 100 TB! Database restoration is quick and offers instantaneous backups. Transaction time is faster regardless of the size of data which is a pretty good deal. Apart from the size, another feature, Geo-replication supports the creation of replications of your database and storing it in a different geographical location. The best part is, you just need to edit in one database and leave the rest to SQL Azure.
In this blog, you will see how to work with Microsoft SQL Server Management to create a database and tables within it and write some queries to manipulate data. It is better if you know how to work on SQL Server beforehand. If you want to brush up your skills in SQL, here is a video to help you out:
Before moving on, here are the topics that will be covered in this blog:
- What is Microsoft Azure SQL Database?
- Azure SQL vs. SQL Server
- SQL Azure Architecture
- Microsoft Azure SQL Database Pricing
- Azure SQL Database Hands-on
Some of the other intelligent features (as mentioned earlier) include dynamic data masking, automatic tuning, SQL threat detection, etc.
Now that you have got a rough idea about Microsoft Azure SQL Database, let us go in depth and understand what Microsoft Azure SQL Database is.
What is Microsoft Azure SQL Database?
Microsoft Azure SQL Database is a relational database-as-a-service that is reliable and secure, and it gives a high performance without having to worry about any infrastructure.
It supports relational, JSON, XML, and spatial data structures.
Microsoft Azure SQL Database has three deployment options:
- Single database
- Elastic pool
- Managed instances
This blog will be talking about the single database, and toward the end it will show you how to create a single database and write some queries.
When talking about databases, how is it possible not to talk about a server? Microsoft has its own on-premise SQL Server apart from Azure SQL Server. Quite confusing, right? Let us see the differences between the two to gain some clarity.
Azure SQL vs. SQL Server
Azure SQL is a relational database platform that is present in the cloud where users can host the data and use it as a service. You can pay for what you have used, like all other cloud services. Azure SQL is built based on the SQL Server and hence it can be quite confusing while making a choice between the two as they share similar qualities. Despite being familiar, there are pretty evident differences that can help you decide which one to choose.
Let us compare the two.
|Azure SQL||SQL Server|
|One database can host several databases from different customers.||Databases are the only objects on the server.|
|It uses the Tabular Data Stream (TDS) protocol.||It uses TCP/IP protocol for communication.|
|Direct communication is not possible due to the complex architecture.||Direct communication can happen.|
|Management and administration are easy.||It is difficult to set up and administer.|
|It is easy to use as you do not need any physical hardware.||Working with a physical system is tiring.|
|It involves automatic backup.||It involves manual scheduling of backup.|
Now that you have seen the differences between Azure SQL and SQL Server, which one will you opt for?
If you are worried about your data being vulnerable in the cloud, here is why Azure SQL should be your choice:
To ward off any threat to customer data, firewalls prevent access to the database server unless it is explicitly granted based on IP address. How to configure the firewall is shown in the hands-on part.
Access is given based on a user’s roles after a thorough authentication of the user.
Azure SQL supports two types of authentication:
- SQL authentication
- Azure Active Directory authentication
With advanced threat protection, it detects any anomaly in accessing or while using the database.
In addition to all these layers, Information Protection ensures that the data is encrypted while in use and in motion.
You can be sure that your data is going nowhere if it is in Microsoft SQL Azure Database. Details on how to secure Azure SQL is will be explained in the next section with its architecture.
SQL Azure Architecture
There are four layers in Azure SQL Architecture:
- Client Layer
- Service Layer
- Platform Layer
- Infrastructure Layer
Now, let us briefly understand each layer one by one.
To be able to access SQL Database, the client layer acts as an interface for applications. It includes SQL Server tools, Open Database Connectivity (ODBC), ADO.NET, and Hypertext Preprocessor (PHP).
Tabular Data Stream (TDS) transfers data between applications and SQL Databases and also communicates with applications. Hence, ADO.NET and ODBC can connect to SQL without any additional demands.
The next layer in the architecture is the service layer, which is in between the platform and the client layers, that acts as a doorway between the two. As you can see in the diagram, provisioning, billing, and routing connections come under this layer. It validates Microsoft Azure SQL Database requests and authenticates a user. Also, it establishes a connection between the client and the server and routes packets through this connection.
This layer has systems (data nodes) that host the actual Azure SQL Server in the data center. Each SQL Database is stored in one of the nodes and is replicated twice across two different physical servers. Azure SQL makes sure that multiple copies of servers are kept within the Azure Cloud. It also ensures that the copies are synchronized when clients manipulate their data on them.
This is the first layer from the bottom of the architecture and is responsible for the administration of the OS and the physical hardware.
So, these were the layers and the architecture of Azure SQL. Let us now look at the pricing of Azure SQL Service.
In this section, a single database will be discussed without touching managed instances and elastic pools.
For a single database, the vCore-based purchase model is the best to opt because it is flexible and allows you to scale memory and storage based on your needs. Using the serverless compute tier optimizes price and performance.
For 1 GB, you are charged with Rs.9.1213/month, but you can scale up your space up to 512 GB.
For weekly backups, your database is copied to RA-GRS and it will cost you Rs.15.864/month.
The pricing model will become even clearer in the next section. So, let us quickly move on to it.
Microsoft Azure SQL Database Hands-on
The initial steps have been skipped which include creating an Azure account and logging in. This section begins with creating a database and writing simple queries in the portal, and then it will talk about how to create a new database and a table with values and how to do some operations on the data using Microsoft Server SQL Management.
Step 1: Login to the portal first and search for Database under services
1. Select SQL Databases under Database service
2. Select Add to create a new database
1. If your account is on the free trial, please select Free Trial under Subscription
2. If you do not have any Resource group created, click on Create new
1. You will have to name your database. Here, it is named as myDatabase
2. After naming your database, you will have to create a server. Once you click on Create new, you will be prompted to set up some details, which will be explained in Step 5. Once done, close the pop-up window and continue with further steps
3. You will not need the elastic pool and, hence, select No
4. Once you are done creating a server, you will be able to click on the Configure database, where you will have to select pricing options
1. Enter the username as per your choice
2.Enter a strong password containing uppercase alphabets, special characters, and numbers
3. Confirm your password by re-entering it
4. Select the location of your server. Southeast Asia has been selected here
5. Tick the box for Azure Services to access the server
Step 6: After completing Step 5, go to the 4th point of Step 4 for pricing and storage options. You will be redirected where you can configure according to your convenience. Select vCore as shown below:
Step 7: After selecting vCore, you can see options like Compute tier which has two options. Select Serverless and you can select the size of your data from 1 GB to 512 GB. Here, it is kept at the minimum level, 1 GB, and it will cost Rs.11.86 from Rs.13,300 credits which you get for a free account
After configuring your settings, click on preview under Preview Terms.
1. Tick the box
2. Select OK
Step 8: Your changes will be saved, and you will be redirected to the screen with options as in Step 4, but with all the details filled in. Select Next: Additional settings > which is at the bottom and do the following steps:
1. Under Use existing data, select Sample because you will be working with the sample data that is already present (and will create your own later)
2. Select Not now for enable ADVANCED DATA SECURITY
3. Click on Review + create
You can see a pop up that has your configurations and select Create. Wait for some time as it takes a little long for deployment.
Step 9: Once done with the deployment, when you go to SQL Databases, you can see your database that has been created. Select your database and do the next step
Step 10: Now that you have your database created, write a query on your data. Click on Query editor, and you will be prompted to give your log-in details that you set in Step 5
Step 11: After you login with the right credentials, you can see the database name on the left, expanding which you can see the option Tables
1. Expand Tables to see the tables in your database
2. Expand the table SalesLT.Customer, for example, to see the columns, so that you can write a query
1. Here, the first query is to print the CustomerID from the SalesLT.Customer table.
2. To execute the query, click on Run. You can see your result below
3. Note down one CustomerID, for example 12, and write another query to print the first name of the customer with the ID 12.
1. To see the entire table, use this: Select * from SalesLT.Customer;
2. Run the command
3. Select the option Export data as .CSV which is present right above the query window
1. Copy your server name for future reference
2. Click on the Set server firewall
1. Click on Add client IP
2. You can see the details once you do the first step
3. Save the configuration
Step 15: Now create your own table using Microsoft SQL Server Management.
Once you have installed the software, you have to connect it to your server. For doing that, open the application and you will see a pop-up asking you to enter credentials.
1. Enter the server name that you copied and kept earlier
2. Under the Authentication drop-down menu, select SQL Server Authentication
3. Enter the username that you specified in the portal
4. Enter the correct password and select Connect
Step 16: After getting connected, you can see your server name on the left-hand side. Right-click on the name of your server and select New Query
Step 17: Now write a query to create a new database on your server.
You can see the center screen which is meant for writing queries. To execute it, select Execute which is above the editor. Once your query gets executed, you can see your new database name on the left bar. In this case, the new database is new1
Step 18: Let us now create a table inside your database. Let us create a simple table with the name ‘customer’ with columns customerID, first name, and address. You can see the results below the editor
Step 19: When you expand your database, you can see the table that you have created. And, expand the table to see the columns
A table without entries does not look good. So, let us insert a row
Step 20: Now that you have one entry, you can write some queries on it. As did in the portal, let us print the first name of the customer whose ID is 1
Step 21: If you want to add more data and write queries, please go ahead.
But make sure that before logging out you delete your resources to save credits
1. Go to Resource groups
2. Select your resource group
3. Select Delete by clicking on the three dots
You will have to re-enter the name of your resource group to confirm the deletion.
With this, you have reached the end of this blog, and hopefully, you know by now how to work with Microsoft SQL Azure Database.
Microsoft Azure provides certifications in these three domains:
Now that you have learned Microsoft Azure SQL Database, you can probably go and get certified in any of these domains. Intellipaat provides a range of courses for you to learn from experts. In case you want to become a certified professional in Azure, here are the certification courses that might interest you:
- Best Microsoft Azure Training for Administration (AZ-103 = AZ-100 and AZ-101)
- Microsoft Azure Developer Associate AZ-203 Certification Training Course
- Microsoft Azure Solutions Architect Certification Training (AZ 300 – 301)