Azure SQL Database – General Overview – Refresh Part 1

I see the other contributions as a personal refresh of my knowledge of the Azure SQL Database and all its numerous features. After a long time, it was necessary to deal with it in detail again and show my own experience with learning that “writing down” helps remember the content and essential points. So I’ll just use the next (albeit perhaps shorter) blog posts to let you take part in my “refresh.” This is also relevant because the Microsoft product group has set the pace during the past few months and has presented numerous new features or optimizations to the existing ones. (Thanks for that ;-))

Understanding Azure SQL Database

Basically, it is about the Azure service, which provides us with a SQL Server database. There are numerous options for deployment or in the form. Starting with selecting the required service (aka “What do I or my application need?”). Continuing with “How many databases does your (new or existing) application really need?” If it is a further development of your own, you may still influence certain functionalities; this is hardly possible with existing in-house developments or purchased products. Example VMWare … here as a rule, at least 3 databases are required, with Sharepoint even more, with a CRM system may be only one.

But to understand, it is not about, for example, that my client-capable CRM creates a separate database for each client, but instead that only one (!) database is required per client, so no “cross-database” queries are used.

If you should make a statement here, you are one step further. You can at least once decide that Azure SQL Database is the right product. Now comes perhaps the most challenging point, but which (under certain circumstances) is good retrospectively can be adjusted.

Microsoft provides us with a Platform-as-a-Service database service in Azure based on the last stable version SQL Server. We as administrators don’t have to worry about a lot here, as Microsoft already does a lot for us, such as upgrades, backup, or monitoring. I will come back to the individual points later. We don’t have to worry about “high availability” either. All Azure SQL Database Services (depending on SKU and deployment) have at least 99.99% availability and can therefore also be used for critical and high-performance business applications.

As we know it from SQL Server, relational and non-relational data structures can also be used in the cloud, also in-memory technologies.

Choosing options for your requirements

The Azure Service SQL Database can be used in different versions and performance classes to meet almost every requirement.

  • Single or pool database
  • Hyperscale
  • Serverless

Each individual deployment can still be divided into the various performance classes, which I will explain in more detail later. Aspects such as CPU, RAM, database size, and service life play a role here to make an optimal decision.

Azure SQL Database - Representation of the Azure Portal - Selection of the different services

Deployment differences of an Azure SQL Database

If you decide that you only need one database, then the Azure SQL Single-DB (or Singleton) is the optimal one. Azure provides a single database in a shared environment, which is so isolated or isolated that you don’t have to worry about something happening here (i.e., no external access from outside your own database). Each Azure Single DB receives its own compute resources and can then use them exclusively.

For example, suppose you have several small databases with different loads at different times. In that case, you can choose an Azure SQL Elastic Pool. Several individual databases are configured in a pool, which can then share the shared compute resources depending on usage and workload.

Depending on the application’s requirements or the business, both single and elastic pool databases can be dynamically scaled to the workload to be processed.

Microsoft differentiates between serverless and server-based in these performance classes, with server-based also being subdivided into “General Purpose / Universal” and “Business Critical / Company-critical” and further into the Compute’s possibilities -Select resources in vCore- or DTU-based. There is currently no serverless deployment for the elastic pools, but there is a distinction between GeneralPurpose and BusinessCritical and the division into DTU (Data Transaction Units) and vCores.

Azure SQL Database - Representation of the Azure Portal - Selection of the performance classes of Elastic Pools

I’ll get to the availability, monitoring, and backup of Azure SQL databases in one of the other blog posts.

Azure SQL – Change your Skills – DataSaturday #6 Malta

Yesterday I was able to give my speech as part of DataSaturday #6 in Malta. It was about changes in the life of a DBA when he is confronted with the fact that his SQL servers and/or databases should be migrated to the Microsoft Cloud Azure. With this session, I wanted to minimize the fears and show that Azure SQL is “only” a SQL server or an SQL database.

In contrast to all other events, Dennes (and his team) had come up with something new in the process. The session did not have 60 minutes but 90 minutes. The first 15 minutes were used as an interview or introduction, the host introduced the speaker, a small video was produced for this, and individual questions were prepared on the respective curriculum vitae of the speaker. Then the speech took place over a full 60 minutes, followed by another 15 minutes for Q&A from the participants, or the host could ask the relevant questions himself. A successful solution led the talk more like a conversation and reduced the “pressure” a little, and I (hopefully also as a participant) felt more part of it.

My host was Deepthi Goguri; I had a lot of fun holding this session with her. Even if this wasn’t my first lecture in English, I am still a little more nervous … I find the right words at the right moment, I pronounce everything correctly … Thanks to the calm and friendly nature of Deepthi, I became more relaxed and could prepare myself a little better for the session. Didn’t help to get over the fact that I wanted to tell and show more than I had time; I have to work on it urgently! Two years ago, I talked to Rob Sewell that I primarily wanted to leave my comfort zone => not only to give lectures in German but start with English and whether he could support me in my start – as a mentor, so to speak. And then Corona came, and everything turned out differently … I just jumped and dared … which brings us back to the topic. 😉

It is just a SQLServer - dont be afraid of moving you databases into Azure

Just dare and try

Deepthi asked me yesterday what I would recommend to the participants if the client or employer wanted to start migrating to the cloud. I can only recommend anyone interested not to be afraid, just because the SQL Server is no longer in your own data center. Still, now in a Microsoft data center, this does not mean that Microsoft will take over all the work and you – as the database administrator – has nothing more to do anymore… ok, you have to learn something new and think a little bit different, but it’s still a SQL Server with its databases!
The first steps could be the following:

  • create an Azure Free Account
  • just deploy a free Linux VM and try out how to install a SQL server there 
  • or an Azure SQL Database to see which steps you have to take to achieve this 
  • and of course, all the great resources of Microsoft Learn – Learning Paths and Modules with lots of content and exercises
    Azure Fundamentals – AZ-900
    Azure Data Fundamentals – DP-900
  • If you have additional questions about the particular topics, you can also read the handy documentation, which often helps me better understand specific issues.

Various learning guides for Azure SQL available

Microsoft – mainly in Anna Hoffman and Bob Ward – also provides many interactive learning materials that can be formed in workshops, labs, or YouTube videos! There is material for several weeks of continuous learning about the SQL Server and/or Azure SQL … regardless of whether Azure SQL Database or the Managed Instance or as a SQL Server VM in Azure. Here you just have to start and try it out, don’t be afraid … it’s not magic. It’s actually quite simple because you can put everything together yourself as you need it. 😉

Microsoft Learn: Azure SQL fundamentals learning path
aka.ms/azuresqlfundamentals
Select the Azure SQL Workshop
aka.ms/sqlworkshops
How to choose tool
aka.ms/chooseazuresql
Azure SQL documentation
aka.ms/azuresqldocs
More videos from our team
aka.ms/dataexposed

Change your Skills - Learning - Ressourcen-Overview

Here are my slides from this talk:
Azure SQL – Change your skills to become a cloud DBA

And if you want to read something about Azure SQL Database, you can, of course either do so in my blog here or in the relatively new blog post on Azure SQL by Deepthi. 😉

Pause / Resume / Backup an Azure SQL Database with Powershell – Part 3

Many services in Azure allow certain cost savings through automation, this can also be achieved with the Platform-as-a-Service “Azure SQL Database”, this is what this blog post is all about. Quite as simple as e.g. with the Azure Analysis Service it is not because actually there is no pause-resume functionality – here comes the backup into place, that’s what I’ll talk about first.

Azure SQL Database and the Backup topic

Before we dive deeper into the Azure SQL Database and its pause-resume functionality, we first have to take a brief look at the topic of backup, which is not quite unimportant in this context. A big advantage of Azure SQL Database against a SQL Server (on premise or Iaas) in any case is … you do not have to explicitly worry about the backup since this is created automatically. According to the database size and number of data changes, Microsoft automatically backs up the databases at regular intervals! That means that the costs for an extra backup software or even the development of own services are already gone. Depending on Performance-Level, there are different backup retention times:

  • at the basic level the retention time is 7 days.
  • at the standard level the retention time is 35 days.
  • at the premium level the retention time is 35 days.

When will my database be backed up? Also there is an answer …
The full backup takes place every week (unfortunately) at non-fixed times, differential backups are generally created every few hours and depending on the data volume, the transaction logs are saved every 5-10 minutes. A first full backup is created within the first 30 minutes after the database is created. These backups are kept according to the service tier(see above) (cost savings: you do not need an extra storage account because the backup is already included in the price). If you want to keep your backup for more than 35 days, you have the option of activating a “Long-Time Retention-Backup”. This requires a further storage account, where the backups are stored in parallel and permanently.

Pause and Resume for cost savings

This functionality is unfortunately not available with Azure SQL Database … How can I still benefit with cost savings if I would like to use this platform-as-a-service… Of course, as already explained in another blog post changing the database performance to prevent occurring load peaks. But we would really like to achieve certain cost savings with the migration to the cloud … if a department only works during the day (8am-8pm), then I need this database(s) only during the night… So why you cannot stop those services, since you only pay when the database is online?

For this scenario, the department needs the database only during the day, there is actually no solution, but a workaround, dropping the database in the evening and restoring the next morning via “Create Database from Backup” helps. This procedure has been implemented by Microsoft extremely pleasantly and does not mean a lot of effort.

# Dropping DB to stop costs
Get-AzureRmSqlDatabase -ResourceGroupName $resourcegroupname -ServerName $servername -DatabaseName $databasename -ev notPresent -ea 0
if ($notPresent) {
    Write-Host $databasename "already deleted" 
} else {
    Remove-AzureRmSqlDatabase -ResourceGroupName $resourcegroupname -ServerName $servername -DatabaseName $databasename
}

Please note that you only delete/remove the database and not the logical server, because the backup history is stored on that logical SQL server. In order to restore this database, you’ll need the date of the last backup database. When you restore the database on the following morning, you’ll directly use this backup time to perform a restore. In Powershell, you can combine these activities very easily.

$deleteddatabase = Get-AzureRmSqlDeletedDatabaseBackup -ResourceGroupName $resourcegroupname -ServerName $servername #-DatabaseName $databasename
$deleteddatabase
# Do not continue until the cmdlet returns information about the deleted database.

Restore-AzureRmSqlDatabase -FromDeletedDatabaseBackup `
    -ResourceGroupName $resourcegroupname `
    -ServerName $servername `
    -TargetDatabaseName $databasename `
    -ResourceId $deleteddatabase.ResourceID `
    -DeletionDate $deleteddatabase.DeletionDate `
    -Edition "Standard" `
    -ServiceObjectiveName "S0"

You can find more sample scripts on backup / restore here => https://docs.microsoft.com/de-de/azure/sql-database/scripts/sql-database-restore-database-powershell

Resize an Azure SQL Database with Powershell – Part 2

In the first part of my Azure SQL Database Powershell Automation series I showed how to create the resource group, the logical SQL Server and the corresponding database, I would like to show you how to implement a resize of the performance level of this database.
Why should I want to resize a database? Upwards – so more power can be understood, but why again downsizing? So I start with the respective reasons for an adjustment of the selected performance class.

What power do I need when?

Let’s take an example… A larger company rents the workspace and is only active in Germany, has an application for the management / booking their meeting rooms or workplaces. This application is used by the ladies at the reception only during the “opening hours” intensively and outside these times occasionally by coworkers. Basically, the receptionists need their application and thus the database only between specific times, for example, 7 – 20 o’clock, the rest of the day the database remains almost unused …
So what is close to making this database “faster” during the day? On-prem is unfortunately not possible since you can not assign a single database so easily further resources.

Another example is evaluations or processing, where operational concerns can vary greatly when the end of the month ends with more computing power in the Azure SQL Database so that the data can be processed and deployed as fast as possible.

  • Depending on application usage
  • Depending on operational requirements
    • nightly processing
    • monthly financial statements
    • Year-end rally

Azure SQL Database

What do we need for a resize of the Azure SQL Database

  • A resource group or its name
  • A name for the logical SQL Server (which must be unique)
  • A database name
  • The new performance level (DTU)

The login to Azure and the selection of to-use Subscription I leave here outside forwards and start the actual script.
Again, I start with the definition of the necessary variables (see above):

# Set the resource group name for your server
$resourcegroupname = "RG-AzureSQLDatabase-Demo"
# Set logical server name
$servername = "server-sqldbdemo"
# The sample database name
$databasename = "db-sqldbdemo"
# Set new performance-level
$newdbDTUsize = "S3"

Now we can resize the Azure SQL Database

This is much easier than rebuilding a database because we need fewer variables (see above) and only one command line for execution. But as I’m careful I also ask if the database exist to ensure that the script does not make any “nonsense”.

# Resize Azure SQL Database to new performance-level
Get-AzureRmSqlDatabase -ResourceGroupName $resourcegroupname -ServerName $servername -DatabaseName $databasename -ev notPresent -ea 0
if ($notPresent) {
    Write-Host $databasename "doesn't exist" 
} else {
    Set-AzureRmSqlDatabase -ResourceGroupName $resourcegroupname -ServerName $servername -DatabaseName $databasename -Edition "Standard" -RequestedServiceObjectiveName $newdbDTUsize
}

As the before-and-after comparison shows, a resize is possible without problems and lasts only a few moments.

Vorher-Nachher-Resize Azure SQL Database

First steps with Azure SQL Database and Powershell – Part 1

In addition to the ability to deploy a virtual server on a SQL server in Azure, there is also the “simpler” method to deploy only one database on demand => the Azure SQL Database. This database-as-a-service is also very good and easy to use for a variety of applications, although there are a lot of options you should think about before.

  • is a simple database enough
  • rather an elastic pool
  • In which region to deploy
  • or rather geo-redundant
  • is the “supplied” backup is enough
  • or must have to have a “Long Time Retention” backup
  • Who accesses the database from where?
  • What level of performance do I need for the database or the Elastic Pool?

An overview of the individual possibilities and their use for the respective solution, as well as a larger number of answers to many questions, can be found in the documentation of Microsoft => https://docs.microsoft.com/en-us/azure/sql-database/

Start with a simple Azure SQL Database

An important point in providing an Azure SQL Database is that you not only need a database but also a logical SQL Server (=> listener endpoint), without this “shell” you can not host databases.

In my example, we use Powershell to create a default database, of course, using a script, so that we can create the server and its database, or just the database, the same default way as needed.

What do we need to create an Azure SQL Database

  • a resource group or its name
  • a location/region for the resource group
  • admin-username and password
  • a name for the logical SQL Server (which must be unique)
  • a database name
  • typically also the IP addresses/ranges, which can access it (the own IP suffices for the first access)

The login to Azure and the selection of to-use Subscription I leave here outside forwards and start the actual script. My first script starts with the definition of various variables (see above):

# Set the resource group name and location for your server
$resourcegroupname = "RG-AzureSQLDatabase-Demo"
$location = "west europe"
# Set an admin login and password for your server
$adminlogin = "dbadmin"
$password = "DemoPwd@2017"
# Set server name - the logical server name has to be unique in the system
$servername = "server-sqldbdemo"
# The sample database name
$databasename = "db-sqldbdemo"
# The ip address range that you want to allow to access your server
$clientIP = (Invoke-WebRequest ifconfig.me/ip).Content
$startip = $clientIP
$endip = $clientIP

Create the logical server and the Azure SQL Database using sample data

Now – as in almost all my scripts – first the query whether the resource group is already exisitiert, if not it is created. After the resource group of logical servers coming next in which we can integrate in the end our Azure SQL Database. We assign the credentials from adminlogin and password to the logical server so that the server and the databases are protected. Speaking protected, the firewall of the server we need to open external access, this I ermittel an additional function and an external service my own public IP address. With this IP address, we are now configuring the logical SQL server, and finally, we check if the desired database already exists, if not, with the desired parameters.

# Create a resource group
Get-AzureRmResourceGroup -Name $resourcegroupname -ev notPresent -ea 0
if ($notPresent) {
  $resourcegroup = New-AzureRmResourceGroup -Name $resourcegroupname -Location $location
} else {
  Write-Host $resourcegroupname "already exists"
}

# Create a server with a system wide unique server name
Get-AzureRmSqlServer -ResourceGroupName $resourcegroupname -ServerName $servername -ev notPresent -ea 0
if ($notPresent) {
  $server = New-AzureRmSqlServer -ResourceGroupName $resourcegroupname `
  -ServerName $servername `
  -Location $location `
  -SqlAdministratorCredentials $(New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $adminlogin, $(ConvertTo-SecureString -String $password -AsPlainText -Force))
} else {
  Write-Host $servername "already exists"
}

# Create a server firewall rule that allows access from the specified IP range
Get-AzureRmSqlServerFirewallRule -ResourceGroupName $resourcegroupname -ServerName $servername -FirewallRuleName "AllowedIPs" -ev notPresent -ea 0
if ($notPresent) {
  $serverfirewallrule = New-AzureRmSqlServerFirewallRule -ResourceGroupName $resourcegroupname `
  -ServerName $servername `
  -FirewallRuleName "AllowedIPs" -StartIpAddress $startip -EndIpAddress $endip
} else {
  Write-Host "FirewallRule already exists"
}

# Create a blank database with an S0 performance level
Get-AzureRmSqlDatabase -ResourceGroupName $resourcegroupname -ServerName $servername -DatabaseName $databasename -ev notPresent -ea 0
if ($notPresent) {
  $database = New-AzureRmSqlDatabase -ResourceGroupName $resourcegroupname `
  -ServerName $servername `
  -DatabaseName $databasename `
  -RequestedServiceObjectiveName "S0"
} else {
  Write-Host "Database" $databasename "already exists"
}

Now we can work from our workstation to use SQL Server Tools – e.g. SQL Server Management Studio – connecting to this database and try to link the application and run some tests.