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.
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 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 when 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
Client Layer
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.
Service Layer
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.
Platform Layer
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.
Infrastructure Layer
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.
Conclusion
Azure SQL is a cloud-based relational database service offered by Microsoft Azure. It provides a reliable, secure, and scalable solution for storing and managing data in the cloud.
One of the key benefits of using Azure SQL is its flexibility. It allows users to choose between different deployment options, including single databases, managed instances, and elastic pools, based on their specific needs.
Another advantage of Azure SQL is its high availability and disaster recovery capabilities. It offers built-in features like automatic backups, point-in-time restore, and geo-replication to ensure that data is always available and protected against any possible data loss or downtime.
In addition, Azure SQL offers robust security features to help protect data from unauthorized access, including data encryption, network security, and authentication and authorization mechanisms.
Overall, Azure SQL is a powerful and comprehensive database solution that can help organizations of all sizes to easily manage and scale their data needs in the cloud.
Leave a Reply