Home
/
Blog
/
Learn How to Operate with Azure SQL Database

Learn How to Operate with Azure SQL Database

Azure SQL Database is a cloud database service that is a high-performance, scalable, consistent, and secure database that can be used to develop data-driven applications. This general-purpose relational database can be used for different structures: for example, relational data, JSON, and XML.

A Simple Table
Azure SQL – Analysis and Transactional Processing
Azure SQL Service Tiers
Threat Detection
How To Query a database in Azure SQL Database?
How to Create a routing table for the traffic in Azure Firewall?
Conclusion

There’s an additional benefit to using Azure, which comes from the fact that it is coded, maintained, and administered by Microsoft. While operating inside Azure’s subscription-only environment could be seen as a negative for people who like the lower-cost advantages of open source SQL options, it’s important to note that open-source is good for people who want to learn, want to operate at low cost, and who don’t mind putting time into creating and propagating fixes for problems or usage-cases which they might be the first person to have encountered. Azure, by contrast with this, is a full Software as a Service (SaaS) option, with fully-developed support and ongoing development; albeit, of course, for a cost.

The real time-saver and service positive of Azure, though, is the cloud-first strategy of Microsoft. With this strategy, the cutting-edge capacities of SQL Server are released initially to SQL Database, and later to SQL Server itself. This scheme offers users the ability to use the latest SQL Server version, with the additional (and considerable) positive that the entire patching and updating process is handled by Microsoft.

When you’re learning to operate in a new coding environment, a few examples can help set the context and get you started, so in this post, we’re going to go over some of the basics you’ll need for operating in Azure SQL.

A Simple Table

First, let’s begin by creating a simple table for the Azure SQL database:

CREATE TABLE Individual
(
Individual_ID  INT IDENTITY PRIMARY KEY,
First_Name  NVARCHAR(55) NOT NULL,
Middel_Name NVARCHAR(15),
Last_Initial  NVARCHAR(55) NOT NULL,
Birth_Date  DATE NOT NULL
)

Azure SQL – Analysis and Transactional Processing

Moving on from this, it’s a good idea to learn the Azure SQL database’s processing capacity. Together with dynamically scalable performance, Azure SQL database offers alternatives for both extreme analytic analysis and reporting and extreme transactional processing. Examples of these functions include columnstore indexes (which store, retrieve and handle data via a columnar data format) and in-memory OLTP (online transaction processing). In the following code, we see that for using these, the MEMORY_OPTIMIZED = ON clause and CREATE TABLE statements are essential:

CREATE TABLE [SalesLT].[SalesOrderHeader_inmem](
[SalesOrderID] int IDENTITY NOT NULL PRIMARY KEY NONCLUSTERED ...,
...
) WITH (MEMORY_OPTIMIZED = ON);

While running the above T-SQL script, if an error occurs, then it means that you should check whether the database supports In-Memory. We can test this with a simple script (below): if the result of the following T-SQL script is ‘1’, it indicates that In-Memory is supported, but if the outcome is ‘0’, then In-Memory is not supported.

SELECT DatabasePropertyEx(DB_Name(), 'IsXTPSupported');

Azure SQL Service Tiers

Azure SQL is available as both a single database and through an elastic database pool approach, depending on the size of your team, and the complexity and longevity of your needs, as well as your budget level.

For single as well as pooled databases, 4 service tiers are offered by Azure SQL database: are Basic, Standard, Premium, and Premium RS. Besides this, various performance stages (database transaction units) and storage choices to deal with the different amount of work and data sizes are presented within each service.

Threat Detection

Security in database management is vital. If it’s worth storing in a database, it’s worth protecting, and Azure SQL database’s Threat Detection functionality is an important thing to understand when operating with the Azure SQL environment. Threat Detection offers a further level of security intelligence, enabling clients to identify and reply to likely threats as they come about by placing security warnings on anomalous actions. Moreover, Azure SQL Database transparent data encryption enables protection against the risk of malicious action by carrying out real-time encryption and decryption of the database, providing linked standbys, and running inactive transaction log files; in addition, it requires no modification to the application.

Database encryption can be achieved by running the following statements:

-- Allow encryption
ALTER DATABASE [Northwind] SET ENCRYPTION ON;
GO

And to decrypt the database, execute the following code:

-- Disallow encryption
ALTER DATABASE [Northwind] SET ENCRYPTION OFF;
GO

How To Query a database in Azure SQL Database?

The procedure of writing SQL queries in the Azure SQL database is simple. The following are the steps to connect to the database.

In the menu > select SQL databases > Now, select your choice of database.
Next, in the menu, select Query editor and then go to the Login page. The login page is under the Active Directory authentication label. After this, select the Continue as<your user or group ID> button. If the page intimates that you have not logged in, you may require to refresh it.

The following example queries should run successfully against the Emp database.

SELECT TOP 20 emp.Ename as EmployeeName, i.name as ItemName
FROM Sales.ItemCategory ic
JOIN Sales.Item i
ON emp.itemcategoryid = i.itemcategoryid;

The query editor utilizes ports 443 and 1443 to interact. Always make sure you have allowed HTTPS movement on these ports. You will also require to attach your IP address to the server’s enabled firewall commands to obtain the databases.

How to Create a routing table for the traffic in Azure Firewall?

By default, Azure routes movement between all subnets. In Azure users can create their routes to modify Azure’s routing. The information to develop system routes is necessary if, for example, users want to route movement between subnets within a system virtual device. For this, simply go to Azure Portal and follow the given steps:

Create a route table.
Next, with the help of the Azure Dashboard, create a route.
After this, create a virtual network with various subnets and then connect a route table to a subnet to route traffic from one subnet to another.

Finally, users can create their paths to route the devices in the subnet so they can utilize the Azure Firewall. Microsoft Azure Virtual Networks enables users to manage the flow of cloud-based network movement. One of the cases of system Route Tables is to divert all network movement within a Virtual App that is liable for securing the security of network movement.

For instance, Routers investigate the destination IP address of a received packet and perform routing protocols individually. To identify which interface the receptacle will be carried, routers use routing tables. A routing table lists all networks for which plans are recognized. Each router’s routing table is complex and stored in the RAM of the computer. Now, when a router receives a packet that needs to be transferred to a host device on a separate system, it examines its destination IP address and browses the routing data received in the routing table.

Conclusion

Operating with Azure SQL Database provides an array of benefits over the conventional SQL Server execution. It not only decreases management overheads, and improves operating efficiency, but also gives common functionality, provides great accessibility functionality, and speeds up development time.

While the multi-tenant and abstracted nature of the Azure SQL Database means that it requires a higher level of technical capacity to operate, and the fact that it is a curated SaaS product means it is not the low-cost option, its functionality and adaptability – not to mention it’s impressive complete compatibility with SQL Server – mean that Azure SQL Database is a great environment to operate within.