Learn How to Operate with Azure SQL Database

Azure SQL Database is a cloud database service which is 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.

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 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 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 by 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

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.