Azure serverless sql

Azure serverless sql DEFAULT

Serverless SQL pool in Azure Synapse Analytics

Every Azure Synapse Analytics workspace comes with serverless SQL pool endpoints that you can use to query data in the Azure Data Lake (Parquet, Delta Lake, delimited text formats), Cosmos DB, or Dataverse.

Serverless SQL pool is a query service over the data in your data lake. It enables you to access your data through the following functionalities:

  • A familiar T-SQL syntax to query data in place without the need to copy or load data into a specialized store.
  • Integrated connectivity via the T-SQL interface that offers a wide range of business intelligence and ad-hoc querying tools, including the most popular drivers.

Serverless SQL pool is a distributed data processing system, built for large-scale data and computational functions. Serverless SQL pool enables you to analyze your Big Data in seconds to minutes, depending on the workload. Thanks to built-in query execution fault-tolerance, the system provides high reliability and success rates even for long-running queries involving large data sets.

Serverless SQL pool is serverless, hence there's no infrastructure to setup or clusters to maintain. A default endpoint for this service is provided within every Azure Synapse workspace, so you can start querying data as soon as the workspace is created.

There is no charge for resources reserved, you are only being charged for the data processed by queries you run, hence this model is a true pay-per-use model.

If you use Apache Spark for Azure Synapse in your data pipeline, for data preparation, cleansing or enrichment, you can query external Spark tables you've created in the process, directly from serverless SQL pool. Use Private Link to bring your serverless SQL pool endpoint into your managed workspace VNet.

Serverless SQL pool benefits

If you need to explore data in the data lake, gain insights from it or optimize your existing data transformation pipeline, you can benefit from using serverless SQL pool. It is suitable for the following scenarios:

  • Basic discovery and exploration - Quickly reason about the data in various formats (Parquet, CSV, JSON) in your data lake, so you can plan how to extract insights from it.
  • Logical data warehouse – Provide a relational abstraction on top of raw or disparate data without relocating and transforming data, allowing always up-to-date view of your data. Learn more about creating logical data warehouse.
  • Data transformation - Simple, scalable, and performant way to transform data in the lake using T-SQL, so it can be fed to BI and other tools, or loaded into a relational data store (Synapse SQL databases, Azure SQL Database, etc.).

Different professional roles can benefit from serverless SQL pool:

  • Data Engineers can explore the lake, transform and prepare data using this service, and simplify their data transformation pipelines. For more information, check this tutorial.
  • Data Scientists can quickly reason about the contents and structure of the data in the lake, thanks to features such as OPENROWSET and automatic schema inference.
  • Data Analysts can explore data and Spark external tables created by Data Scientists or Data Engineers using familiar T-SQL language or their favorite tools, which can connect to serverless SQL pool.
  • BI Professionals can quickly create Power BI reports on top of data in the lake and Spark tables.

How to start using serverless SQL pool

Serverless SQL pool endpoint is provided within every Azure Synapse workspace. You can create a workspace and start querying data instantly using tools you are familiar with.

Make sure that you are applying the best practices to get the best performance.

Client tools

Serverless SQL pool enables existing SQL ad-hoc querying and business intelligence tools to tap into the data lake. As it provides familiar T-SQL syntax, any tool capable to establish TDS connection to SQL offerings can connect to and query Synapse SQL. You can connect with Azure Data Studio and run ad-hoc queries or connect with Power BI to gain insights in a matter of minutes.

T-SQL support

Serverless SQL pool offers T-SQL querying surface area, which is slightly enhanced/extended in some aspects to accommodate for experiences around querying semi-structured and unstructured data. Furthermore, some aspects of the T-SQL language aren't supported due to the design of serverless SQL pool, as an example, DML functionality is currently not supported.

  • Workload can be organized using familiar concepts:
  • Databases - serverless SQL pool endpoint can have multiple databases.
  • Schemas - Within a database, there can be one or many object ownership groups called schemas.
  • Views, stored procedures, inline table value functions
  • External resources – data sources, file formats, and tables

Security can be enforced using:

  • Logins and users
  • Credentials to control access to storage accounts
  • Grant, deny, and revoke permissions per object level
  • Azure Active Directory integration

Supported T-SQL:

  • Full SELECT surface area is supported, including a majority of SQL functions
  • CETAS - CREATE EXTERNAL TABLE AS SELECT
  • DDL statements related to views and security only

Serverless SQL pool has no local storage, only metadata objects are stored in databases. Therefore, T-SQL related to the following concepts isn't supported:

  • Tables
  • Triggers
  • Materialized views
  • DDL statements other than ones related to views and security
  • DML statements

Extensions

In order to enable smooth experience for in place querying of data residing in files in data lake, serverless SQL pool extends the existing OPENROWSET function by adding following capabilities:

Query multiple files or folders

Query PARQUET file format

Query DELTA format

Various delimited text formats (with custom field terminator, row terminator, escape char)

Cosmos DB analytical store

Read a chosen subset of columns

Schema inference

filename function

filepath function

Work with complex types and nested or repeated data structures

Security

Serverless SQL pool offers mechanisms to secure access to your data.

Azure Active Directory integration and multi-factor authentication

Serverless SQL pool enables you to centrally manage identities of database user and other Microsoft services with Azure Active Directory integration. This capability simplifies permission management and enhances security. Azure Active Directory (Azure AD) supports multi-factor authentication (MFA) to increase data and application security while supporting a single sign-on process.

Authentication

Serverless SQL pool authentication refers to how users prove their identity when connecting to the endpoint. Two types of authentication are supported:

  • SQL Authentication

    This authentication method uses a username and password.

  • Azure Active Directory Authentication:

    This authentication method uses identities managed by Azure Active Directory. For Azure AD users, multi-factor authentication can be enabled. Use Active Directory authentication (integrated security) whenever possible.

Authorization

Authorization refers to what a user can do within a serverless SQL pool database, and is controlled by your user account's database role memberships and object-level permissions.

If SQL Authentication is used, the SQL user exists only in serverless SQL pool and permissions are scoped to the objects in serverless SQL pool. Access to securable objects in other services (such as Azure Storage) can't be granted to SQL user directly since it only exists in scope of serverless SQL pool. The SQL user needs to use one of the supported authorization types to access the files.

If Azure AD authentication is used, a user can sign in to serverless SQL pool and other services, like Azure Storage, and can grant permissions to the Azure AD user.

Access to storage accounts

A user that is logged into the serverless SQL pool service must be authorized to access and query the files in Azure Storage. serverless SQL pool supports the following authorization types:

  • Shared access signature (SAS) provides delegated access to resources in storage account. With a SAS, you can grant clients access to resources in storage account, without sharing account keys. A SAS gives you granular control over the type of access you grant to clients who have the SAS: validity interval, granted permissions, acceptable IP address range, acceptable protocol (https/http).

  • User Identity (also known as "pass-through") is an authorization type where the identity of the Azure AD user that logged into serverless SQL pool is used to authorize access to the data. Before accessing the data, Azure Storage administrator must grant permissions to Azure AD user for accessing the data. This authorization type uses the Azure AD user that logged into serverless SQL pool, therefore it's not supported for SQL user types.

  • Workspace Identity is an authorization type where the identity of the Synapse workspace is used to authorize access to the data. Before accessing the data, Azure Storage administrator must grant permissions to workspace identity for accessing the data.

Access to Cosmos DB

You need to create server-level or database-scoped credential with the Cosmos DB account read-only key to access Cosmos DB analytical store.

Next steps

Additional information on endpoint connection and querying files can be found in the following articles:

Sours: https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/on-demand-workspace-overview

Azure SQL Database serverless

APPLIES TO: Azure SQL Database

Serverless is a compute tier for single databases in Azure SQL Database that automatically scales compute based on workload demand and bills for the amount of compute used per second. The serverless compute tier also automatically pauses databases during inactive periods when only storage is billed and automatically resumes databases when activity returns.

Serverless compute tier

The serverless compute tier for single databases in Azure SQL Database is parameterized by a compute autoscaling range and an auto-pause delay. The configuration of these parameters shapes the database performance experience and compute cost.

serverless billing

Performance configuration

  • The minimum vCores and maximum vCores are configurable parameters that define the range of compute capacity available for the database. Memory and IO limits are proportional to the vCore range specified. 
  • The auto-pause delay is a configurable parameter that defines the period of time the database must be inactive before it is automatically paused. The database is automatically resumed when the next login or other activity occurs. Alternatively, automatic pausing can be disabled.

Cost

  • The cost for a serverless database is the summation of the compute cost and storage cost.
  • When compute usage is between the min and max limits configured, the compute cost is based on vCore and memory used.
  • When compute usage is below the min limits configured, the compute cost is based on the min vCores and min memory configured.
  • When the database is paused, the compute cost is zero and only storage costs are incurred.
  • The storage cost is determined in the same way as in the provisioned compute tier.

For more cost details, see Billing.

Scenarios

Serverless is price-performance optimized for single databases with intermittent, unpredictable usage patterns that can afford some delay in compute warm-up after idle usage periods. In contrast, the provisioned compute tier is price-performance optimized for single databases or multiple databases in elastic pools with higher average usage that cannot afford any delay in compute warm-up.

Scenarios well suited for serverless compute

  • Single databases with intermittent, unpredictable usage patterns interspersed with periods of inactivity, and lower average compute utilization over time.
  • Single databases in the provisioned compute tier that are frequently rescaled and customers who prefer to delegate compute rescaling to the service.
  • New single databases without usage history where compute sizing is difficult or not possible to estimate prior to deployment in SQL Database.

Scenarios well suited for provisioned compute

  • Single databases with more regular, predictable usage patterns and higher average compute utilization over time.
  • Databases that cannot tolerate performance trade-offs resulting from more frequent memory trimming or delays in resuming from a paused state.
  • Multiple databases with intermittent, unpredictable usage patterns that can be consolidated into elastic pools for better price-performance optimization.

Comparison with provisioned compute tier

The following table summarizes distinctions between the serverless compute tier and the provisioned compute tier:

Serverless computeProvisioned compute
Database usage patternIntermittent, unpredictable usage with lower average compute utilization over time.More regular usage patterns with higher average compute utilization over time, or multiple databases using elastic pools.
Performance management effortLowerHigher
Compute scalingAutomaticManual
Compute responsivenessLower after inactive periodsImmediate
Billing granularityPer secondPer hour

Purchasing model and service tier

SQL Database serverless is currently only supported in the General Purpose tier on Generation 5 hardware in the vCore purchasing model.

Autoscaling

Scaling responsiveness

In general, serverless databases are run on a machine with sufficient capacity to satisfy resource demand without interruption for any amount of compute requested within limits set by the max vCores value. Occasionally, load balancing automatically occurs if the machine is unable to satisfy resource demand within a few minutes. For example, if the resource demand is 4 vCores, but only 2 vCores are available, then it may take up to a few minutes to load balance before 4 vCores are provided. The database remains online during load balancing except for a brief period at the end of the operation when connections are dropped.

Memory management

Memory for serverless databases is reclaimed more frequently than for provisioned compute databases. This behavior is important to control costs in serverless and can impact performance.

Cache reclamation

Unlike provisioned compute databases, memory from the SQL cache is reclaimed from a serverless database when CPU or active cache utilization is low.

  • Active cache utilization is considered low when the total size of the most recently used cache entries falls below a threshold for a period of time.
  • When cache reclamation is triggered, the target cache size is reduced incrementally to a fraction of its previous size and reclaiming only continues if usage remains low.
  • When cache reclamation occurs, the policy for selecting cache entries to evict is the same selection policy as for provisioned compute databases when memory pressure is high.
  • The cache size is never reduced below the min memory limit as defined by min vCores, that can be configured.

In both serverless and provisioned compute databases, cache entries may be evicted if all available memory is used.

When CPU utilization is low, active cache utilization can remain high depending on the usage pattern and prevent memory reclamation. Also, there can be other delays after user activity stops before memory reclamation occurs due to periodic background processes responding to prior user activity. For example, delete operations and Query Store cleanup tasks generate ghost records that are marked for deletion, but are not physically deleted until the ghost cleanup process runs. Ghost cleanup may involve reading additional data pages into cache.

Cache hydration

The SQL cache grows as data is fetched from disk in the same way and with the same speed as for provisioned databases. When the database is busy, the cache is allowed to grow unconstrained up to the max memory limit.

Auto-pausing and auto-resuming

Auto-pausing

Auto-pausing is triggered if all of the following conditions are true for the duration of the auto-pause delay:

  • Number of sessions = 0
  • CPU = 0 for user workload running in the user resource pool

An option is provided to disable auto-pausing if desired.

The following features do not support auto-pausing, but do support auto-scaling. If any of the following features are used, then auto-pausing must be disabled and the database will remain online regardless of the duration of database inactivity:

Auto-pausing is temporarily prevented during the deployment of some service updates which require the database be online. In such cases, auto-pausing becomes allowed again once the service update completes.

Auto-pause troubleshooting

If auto-pausing is enabled, but a database does not auto-pause after the delay period, and the features listed above are not used, the application or user sessions may be preventing auto-pausing. To see if there are any application or user sessions currently connected to the database, connect to the database using any client tool, and execute the following query:

Tip

After running the query, make sure to disconnect from the database. Otherwise, the open session used by the query will prevent auto-pausing.

If the result set is non-empty, it indicates that there are sessions currently preventing auto-pausing.

If the result set is empty, it is still possible that sessions were open, possibly for a short time, at some point earlier during the auto-pause delay period. To see if such activity has occurred during the delay period, you can use Azure SQL Auditing and examine audit data for the relevant period.

The presence of open sessions, with or without concurrent CPU utilization in the user resource pool, is the most common reason for a serverless database to not auto-pause as expected.

Auto-resuming

Auto-resuming is triggered if any of the following conditions are true at any time:

FeatureAuto-resume trigger
Authentication and authorizationLogin
Threat detectionEnabling/disabling threat detection settings at the database or server level.
Modifying threat detection settings at the database or server level.
Data discovery and classificationAdding, modifying, deleting, or viewing sensitivity labels
AuditingViewing auditing records.
Updating or viewing auditing policy.
Data maskingAdding, modifying, deleting, or viewing data masking rules
Transparent data encryptionViewing state or status of transparent data encryption
Vulnerability assessmentAd hoc scans and periodic scans if enabled
Query (performance) data storeModifying or viewing query store settings
Performance recommendationsViewing or applying performance recommendations
Auto-tuningApplication and verification of auto-tuning recommendations such as auto-indexing
Database copyingCreate database as copy.
Export to a BACPAC file.
SQL data syncSynchronization between hub and member databases that run on a configurable schedule or are performed manually
Modifying certain database metadataAdding new database tags.
Changing max vCores, min vCores, or auto-pause delay.
SQL Server Management Studio (SSMS)Using SSMS versions earlier than 18.1 and opening a new query window for any database in the server will resume any auto-paused database in the same server. This behavior does not occur if using SSMS version 18.1 or later.

Monitoring, management, or other solutions performing any of the operations listed above will trigger auto-resuming.

Auto-resuming is also triggered during the deployment of some service updates that require the database be online.

Connectivity

If a serverless database is paused, then the first login will resume the database and return an error stating that the database is unavailable with error code 40613. Once the database is resumed, the login must be retried to establish connectivity. Database clients with connection retry logic should not need to be modified. For connection retry logic options that are built-in to the SqlClient driver, see configurable retry logic in SqlClient.

Latency

The latency to auto-resume and auto-pause a serverless database is generally order of 1 minute to auto-resume and 1-10 minutes after the expiration of the delay period to auto-pause.

Customer managed transparent data encryption (BYOK)

If using customer managed transparent data encryption (BYOK) and the serverless database is auto-paused when key deletion or revocation occurs, then the database remains in the auto-paused state. In this case, after the database is next resumed, the database becomes inaccessible within approximately 10 minutes. Once the database becomes inaccessible, the recovery process is the same as for provisioned compute databases. If the serverless database is online when key deletion or revocation occurs, then the database also becomes inaccessible within approximately 10 minutes in the same way as with provisioned compute databases.

Onboarding into serverless compute tier

Creating a new database or moving an existing database into a serverless compute tier follows the same pattern as creating a new database in provisioned compute tier and involves the following two steps.

  1. Specify the service objective. The service objective prescribes the service tier, hardware generation, and max vCores. For service objective options, see serverless resource limits

  2. Optionally, specify the min vCores and auto-pause delay to change their default values. The following table shows the available values for these parameters.

    ParameterValue choicesDefault value
    Min vCoresDepends on max vCores configured - see resource limits.0.5 vCores
    Autopause delayMinimum: 60 minutes (1 hour)
    Maximum: 10080 minutes (7 days)
    Increments: 10 minutes
    Disable autopause: -1
    60 minutes

Create a new database in the serverless compute tier

The following examples create a new database in the serverless compute tier.

Use Azure portal

See Quickstart: Create a single database in Azure SQL Database using the Azure portal.

Use PowerShell

Use Azure CLI

Use Transact-SQL (T-SQL)

When using T-SQL, default values are applied for the min vcores and autopause delay. They can later be changed from the portal or via other management APIs (PowerShell, Azure CLI, REST API).

For details, see CREATE DATABASE.

Move a database from the provisioned compute tier into the serverless compute tier

The following examples move a database from the provisioned compute tier into the serverless compute tier.

Use PowerShell

Use Azure CLI

Use Transact-SQL (T-SQL)

When using T-SQL, default values are applied for the min vcores and auto-pause delay. They can later be changed from the portal or via other management APIs (PowerShell, Azure CLI, REST API).

For details, see ALTER DATABASE.

Move a database from the serverless compute tier into the provisioned compute tier

A serverless database can be moved into a provisioned compute tier in the same way as moving a provisioned compute database into a serverless compute tier.

Modifying serverless configuration

Use PowerShell

Modifying the maximum or minimum vCores, and autopause delay, is performed by using the Set-AzSqlDatabase command in PowerShell using the , , and arguments.

Use Azure CLI

Modifying the maximum or minimum vCores, and autopause delay, is performed by using the az sql db update command in Azure CLI using the , , and arguments.

Monitoring

Resources used and billed

The resources of a serverless database are encapsulated by app package, SQL instance, and user resource pool entities.

App package

The app package is the outer most resource management boundary for a database, regardless of whether the database is in a serverless or provisioned compute tier. The app package contains the SQL instance and external services like Full-text Search that all together scope all user and system resources used by a database in SQL Database. The SQL instance generally dominates the overall resource utilization across the app package.

User resource pool

The user resource pool is an inner resource management boundary for a database, regardless of whether the database is in a serverless or provisioned compute tier. The user resource pool scopes CPU and IO for user workload generated by DDL queries such as CREATE and ALTER, DML queries such as INSERT, UPDATE, DELETE, and MERGE, and SELECT queries. These queries generally represent the most substantial proportion of utilization within the app package.

Metrics

Metrics for monitoring the resource usage of the app package and user resource pool of a serverless database are listed in the following table:

EntityMetricDescriptionUnits
App packageapp_cpu_percentPercentage of vCores used by the app relative to max vCores allowed for the app.Percentage
App packageapp_cpu_billedThe amount of compute billed for the app during the reporting period. The amount paid during this period is the product of this metric and the vCore unit price.

Values of this metric are determined by aggregating over time the maximum of CPU used and memory used each second. If the amount used is less than the minimum amount provisioned as set by the min vCores and min memory, then the minimum amount provisioned is billed. In order to compare CPU with memory for billing purposes, memory is normalized into units of vCores by rescaling the amount of memory in GB by 3 GB per vCore.
vCore seconds
App packageapp_memory_percentPercentage of memory used by the app relative to max memory allowed for the app.Percentage
User resource poolcpu_percentPercentage of vCores used by user workload relative to max vCores allowed for user workload.Percentage
User resource pooldata_IO_percentPercentage of data IOPS used by user workload relative to max data IOPS allowed for user workload.Percentage
User resource poollog_IO_percentPercentage of log MB/s used by user workload relative to max log MB/s allowed for user workload.Percentage
User resource poolworkers_percentPercentage of workers used by user workload relative to max workers allowed for user workload.Percentage
User resource poolsessions_percentPercentage of sessions used by user workload relative to max sessions allowed for user workload.Percentage

Pause and resume status

In the Azure portal, the database status is displayed in the overview pane of the server that lists the databases it contains. The database status is also displayed in the overview pane for the database.

Using the following commands to query the pause and resume status of a database:

Use PowerShell

Use Azure CLI

Resource limits

For resource limits, see serverless compute tier.

Billing

The amount of compute billed is the maximum of CPU used and memory used each second. If the amount of CPU used and memory used is less than the minimum amount provisioned for each, then the provisioned amount is billed. In order to compare CPU with memory for billing purposes, memory is normalized into units of vCores by rescaling the amount of memory in GB by 3 GB per vCore.

  • Resource billed: CPU and memory
  • Amount billed: vCore unit price * max (min vCores, vCores used, min memory GB * 1/3, memory GB used * 1/3)
  • Billing frequency: Per second

The vCore unit price is the cost per vCore per second. Refer to the Azure SQL Database pricing page for specific unit prices in a given region.

The amount of compute billed is exposed by the following metric:

  • Metric: app_cpu_billed (vCore seconds)
  • Definition: max (min vCores, vCores used, min memory GB * 1/3, memory GB used * 1/3)
  • Reporting frequency: Per minute

This quantity is calculated each second and aggregated over 1 minute.

Minimum compute bill

If a serverless database is paused, then the compute bill is zero. If a serverless database is not paused, then the minimum compute bill is no less than the amount of vCores based on max (min vCores, min memory GB * 1/3).

Examples:

  • Suppose a serverless database is not paused and configured with 8 max vCores and 1 min vCore corresponding to 3.0 GB min memory. Then the minimum compute bill is based on max (1 vCore, 3.0 GB * 1 vCore / 3 GB) = 1 vCore.
  • Suppose a serverless database is not paused and configured with 4 max vCores and 0.5 min vCores corresponding to 2.1 GB min memory. Then the minimum compute bill is based on max (0.5 vCores, 2.1 GB * 1 vCore / 3 GB) = 0.7 vCores.

The Azure SQL Database pricing calculator for serverless can be used to determine the min memory configurable based on the number of max and min vCores configured. As a rule, if the min vCores configured is greater than 0.5 vCores, then the minimum compute bill is independent of the min memory configured and based only on the number of min vCores configured.

Example scenario

Consider a serverless database configured with 1 min vCore and 4 max vCores. This configuration corresponds to around 3 GB min memory and 12 GB max memory. Suppose the auto-pause delay is set to 6 hours and the database workload is active during the first 2 hours of a 24-hour period and otherwise inactive.

In this case, the database is billed for compute and storage during the first 8 hours. Even though the database is inactive starting after the second hour, it is still billed for compute in the subsequent 6 hours based on the minimum compute provisioned while the database is online. Only storage is billed during the remainder of the 24-hour period while the database is paused.

More precisely, the compute bill in this example is calculated as follows:

Time IntervalvCores used each secondGB used each secondCompute dimension billedvCore seconds billed over time interval
0:00-1:0049vCores used4 vCores * 3600 seconds = 14400 vCore seconds
1:00-2:00112Memory used12 GB * 1/3 * 3600 seconds = 14400 vCore seconds
2:00-8:0000Min memory provisioned3 GB * 1/3 * 21600 seconds = 21600 vCore seconds
8:00-24:0000No compute billed while paused0 vCore seconds
Total vCore seconds billed over 24 hours50400 vCore seconds

Suppose the compute unit price is $0.000145/vCore/second. Then the compute billed for this 24-hour period is the product of the compute unit price and vCore seconds billed: $0.000145/vCore/second * 50400 vCore seconds ~ $7.31.

Azure Hybrid Benefit and reserved capacity

Azure Hybrid Benefit (AHB) and reserved capacity discounts do not apply to the serverless compute tier.

Available regions

The serverless compute tier is available worldwide except the following regions: China East, China North, Germany Central, Germany Northeast, and US Gov Central (Iowa).

Next steps

Sours: https://docs.microsoft.com/en-us/azure/azure-sql/database/serverless-tier-overview
  1. Scatter frequency
  2. Snappa facebook cover
  3. Lg optmus
  4. Live score pro
  5. Judaica ave m

Going Serverless with Azure SQL Database

By: John Miner   |   Updated: 2019-12-20   |   Comments   |   Related: > Azure


Problem

There are certain business workloads that might need a SQL Server database for a limited period of time.  After data processing, the need for having the database online declines.

One use case that comes to mind is the payment of vendors for raw goods.  A medium size manufacturing company might receive multiple shipments from a vendor during the month.  However, calculating the total bill for a given period and making one payment to the vendor might happen twice a month.

How can we create an Azure SQL database that keeps in mind cost for an intermittent relational database need?

Solution

Microsoft has a new virtual core offering of Azure SQL database called serverless.  This offering optimizes the price-performance tradeoff and simplifies performance management for databases with intermittent and unpredictable usage.  Since this offering allows for auto-scaling, workloads with CPU usage that peaks and valleys can benefit for this offering.

Business Problem

Our boss has asked us to look into the new virtual core offerings of Azure SQL database.  Since we have a couple internal workloads that have intermittent usage patterns, we are going to start our investigation with the serverless offering.

We need to choose a simple problem that can be solved on our local laptop and in the cloud.  This will give a performance comparison of the new offering.  This proof of concept will use the math database schema which calculates prime numbers from 1 to N.  In our case, we want to know how many primes numbers exist between 1 and 5 million.  We want to execute 20 asynchronous jobs to accomplish this task as fast as possible.

The rest of the article will show you how to deploy an Azure SQL database (serverless edition) using the math database schema.  Comparing cloud to on premise execution times allows us to gauge how fast the Azure virtualized environment is.

Deploy Server and Database

The deployment of the server and database is almost the same as the database transaction unit (DTU) version that we are accustomed to.  I will point out the differences and highlight any key points in the screen shot images shown below.

First, we need to deploy an Azure SQL database using the Azure portal.  I will be using a subscription that I only have full rights to a resource group named jfm_x_001.  Clicking the add button when viewing the resource group will allow me to search the Azure Market place for the database service.

Azure Serverless Database - Create new server in given location with selected password.

The above image shows that a new database server named svr4tips2019 will be deployed in the East US region.  I might want to have other Azure services such as Azure Data Factory work with this data.  Click the appropriate check box to make this happen.

Second, we need to choose the settings of the db4tips2019 database.  By default, Microsoft wants all customers to start using the new v-Core offerings.  Click on the configure database to see the full set of available options.

Azure Serverless Database - Use configure database dialog to choose general, serverless tier.

The original offerings of Azure SQL database using the Basic, Standard and Premium tier can be found by clicking the topmost left button.  The image below shows the screen that most Data Architects have seen for the last 5 years.  To see the new offerings, click v-Core purchasing topmost right button.

What is the main different between the offerings?

The historical offerings are a shared architecture in which you are not provisioned any dedicated CPU and memory.  The new offerings allow for the selection of the number of CPUs.  Memory is allocated as a multiplier of the cores depending upon the generation of the central processing unit (CPU).  I will go into more details in my next article on the General Purpose offering of Azure SQL database.

Azure Serverless Database - Traditional DTU offerings with Basic, Standard and Premium tiers.

There are three new offerings under the v-Core based purchasing model.  For our proof of concept, we want to choose the General purpose offering and the serverless compute tier.

Azure Serverless Database - New v-core offerings using compute generations.

We will be using four cores to match the processing power on my laptop.  Please notice that the image below shows the ability to choose the min and max values for cores used by service.  This is where scaling comes into play.

Azure Serverless Database - Serverless tier allows for both min and max cores to be used.

Like all v-Core offerings, the amount of memory is dictated by the number of cores and the size of the log file is dictated by the data file size.  One key observation is that the service can be paused after a given amount of time.

Azure Serverless Database - The CPU select dictates the memory foot print.  The database size dictates the log file size.

It is import to note that the above image shows this service is in preview.  When the images were taken for the article about 6 months ago, this was true.  Today, the service has general availability.  Also, the limitations of 4 cores and 1 TB of file space has been increased to a maximum of 16 cores, 48 GB of memory and 3 TB of file space.

Azure Serverless Database - Review settings before creating the database.

Most deployments from the portal have a review and create pane.  This allows the user to go back to various options if they are incorrect.  Click the create button to deploy the serverless database.

I do like the new deployment details pane.  It shows what is being deployed and the status of the deployment.  From the image below, we can see a server, database and firewalls were deployed.  What is interesting is the storage account which was deployed to support vulnerability assessments and alert policies.  For some reason, it is listed three times in our deployment log.

Azure Serverless Database - The deployment action from the portal lists all the objects that are deployed.

At the end of the day, we are just interested in the components that were deployed.  We have listed below an Azure logical SQL Server named svr4tips2019 and an Azure Serverless database named db4tips2019.

Azure Serverless Database - Both a server and database have been deployed.

In a nutshell, deploying the new serverless offering requires a few more choices from the user to complete than the older offerings.

Math database schema

One of the nice things about Azure SQL database is that it is secured during deployment.  That means we have to enable the firewall to allow our database tool of choice to connect.  Please see image below for details.

Azure Serverless Database - Add a client ip rule to the firewall is necessary for access.

I am still using SQL Server Management Studio (SSMS) for DBA tasks.  You might be using either Visual Studio database projects, Azure Data Studio, or a third-party product.  The image below shows a typical login using the account I created during deployment of the logical server.

Azure Serverless Database - Logging into the database using SSMS.

The enclosed T-SQL script named "make-prime-number-database.sql" will create the math database schema.  Inside the script is a statement that displays the objects that were created.  Your results should match the image below.

Azure Serverless Database - Database objects that are part of Math schema.

The code below is extracted from a T-SQL file named "calculate-prime-numbers.sql".  It will find and store primes numbers with a range of a given N to N + 250000 - 1.

-- Local table variable DECLARE @MY_TABLE TABLE ( [MY_ID_VALUE] [int] ); -- Create a new control card record INSERT INTO [DBO].[TBL_CONTROL_CARD] OUTPUT INSERTED.MY_ID_VALUE INTO @MY_TABLE VALUES ('Look for prime numbers.', default); -- Find start/end points DECLARE @VAR_ALPHA, @VAR_OMEGA BIGINT; SELECT @VAR_ALPHA = MY_ID_VALUE - 250000 FROM @MY_TABLE; SELECT @VAR_OMEGA = MY_ID_VALUE - 1 FROM @MY_TABLE; -- Find prime numbers based on control card record EXEC [DBO].[SP_STORE_PRIMES] @VAR_ALPHA, @VAR_OMEGA;

To finish our proof of concept, we need a way to call this T-SQL script 20 times in a asynchronous manor.  We can use a plain batch file named "run.cmd" to accomplish this task.  The code inside the batch file is shown below.

-- Change directory CD "C:\Azure SQL Database\" -- Execute file via sqlcmd FOR /L %%A IN (1,1,20) DO ( start cmd /c sqlcmd -S tcp:svr4tips2019.database.windows.net^ -U jminer -P InsertPasswordHere -d db4tips2019 -i "calculate-prime-numbers.sql"

Execute the batch command to search for prime numbers now.

Remote Database Results

Many of the components of the SQL Server stack have been split into separate install programs.  If you have an older version of SQL Server Management Studio, you might see the following error message.

Azure Serverless Database - Older versions of SSMS might not recognize newer v-core offerings.

This means the software does not recognize the new v-Core offerings for Azure SQL database.  Please download and install the latest version of the software to eliminate this issue.

The rows in the table named TBL_CONTROL_CARD keep track of all the separate executions.  We can see that 20 threads (command windows) were executed from the batch file within 3 seconds.  See image below for details.

Azure Serverless Database - Executing code in the cloud.

Our prime numbers are stored in a table called TBL_PRIMES and a summary query is seen below.

Azure Serverless Database - Summary statistics for cloud execution.

Each record has a time of discover.  If we aggregate the data, we can calculate the start time, the end time, the total elapsed time and the total number of prime numbers found.  Looking at the results from the above image, we can see that the execution took 6 minutes and 12 seconds to complete.

Azure Serverless Database - The serverless offering is bills CPU and storage separately.

Billing for an Azure Serverless Database is a combination of storage and compute.  It is important that the auto pause feature be set to a low value if possible, for best savings.

In the above image, we can see that 13.56K v-Core seconds have been used.  The subscription only gets charged when processing occurs.  Once the CPU is paused, only the storage cost is charged.  Using the cost numbers from the deployment window (see 5th image) times the number of v-Core seconds, we can see a charge of 94 cents was incurred during this hour.  Let us turn our attention now to a similar test using on premise hardware.

Local Database Results

If no one let you in on the secret, hardware is the king when it comes to speed.  Many of the Azure offerings are using remote storage.  These offerings can’t compete with the raw power of unabstracted solid-state disks.

Azure Serverless Database - Executing code on premise.

The above image shows a similar execution of the command windows for 20 jobs.

Azure Serverless Database - Summary statistics for on premise execution.

There are some major differences in the results.  At least we have found the same number of PRIME numbers in both executions.  This result shows that our algorithm is executing the calculations correctly.

However, we can start 20 command windows in 2 seconds using an on-premise local server.  It takes an additional 1 second when we use an Azure remote server.   This is due to the network delay between the source and target.   The total execution time is now 3 minutes and 55 seconds.  That is a savings of 2 minutes and 17 seconds.  The local server is 45.14% faster in executing the same code.

Azure Serverless Database - Using task manager to track laptop usage.

The above image shows the configuration of my laptop that was used in the bake off.  Both the local and remote databases have 4 processors that are hyper threaded.  The laptop has 16 GB of memory for all processes including the operating system.  The Azure Serverless database is capped at 12 GB of memory.  The major difference between the two systems is the speed of the attached disk versus the remote disk.

My laptop is 2 years old using a Samsung SSD 850 EVO local SSD.  The chart below was copied from a white paper authored by Calypso Systems CEO, Eden Kim and published by SNIA SSSI.  I did a quick disk speed check using the tech net utility named diskspd.exe.  The laptop clocked in at 60K IOPS for the read test.  I am sure the write test will be around 40K IOPS.  Attached storage in Azure has performance that range from 120 to 5000 IOPS for premium SSD for given the database size.

To recap, attached storage in Azure has lower IOPS than a typical SSD that might be found in your computer.  The fastest storage is one that is directly attached to the motherboard.  We can see that the PCIe disks have impressive speeds.

Azure Serverless Database - Physical hardware rules over virtual hardware.

One last observation to mention is the new Azure v-Core database have three files.  Typically, we see a log and data file per each database.  Something extra is going on here to support the implementation of the General Purpose, serverless tier database.

Azure Serverless Database - Interesting find that a filestream file existing in this offering.

The bake off between in-cloud and on-premise is now complete.  If you do not mind managing your own hardware and software, then a bare metal install of the database engine will give the best performance.  If you have a database with intermittent and unpredictable usage, an Azure Serverless database might meet your needs at a reasonable price.

Summary

There are many business cases in which a relational database needs to be available intermittently.  If your application can handle a slow startup of the server from a paused condition, then the Serverless offering might be a fit.  This will save you money since no compute charges will be incurred during inactivity.  Please review the Azure Serverless documentation closely.  Certain features such as geo-replication cancel out the ability to pause the server.

Other applications might experience a peak in usage during a busy time of year.  For instance, an online pizza delivery business might see a major uptick in business during Super Bowl weekend.  If you are using Azure Serverless database, the load balancer will scale the number of CPUs to handle the unexpected load.  Just remember, there will be a momentary drop of connections when resources are added or subtracted.  This means your application must be able to handle retries for a downed database service.

Both auto scaling and auto pausing are nice features of the Azure Serverless database offering.

I believe that Azure SQL database is cost effective for small to medium size work loads that do not need to low latency and high performance.  For large work loads, there are other offerings to consider.  I will be talking about the Business Critical offering of Azure SQL database in the future.  It has attractive performance at an unattractive price.  Unfortunately, you will pay a lot of money for a database service that is capped at a storage size of 4 TB.

If you really need extreme performance and have a team to manage both the infrastructure and the database, some amazing numbers can be seen with today’s hardware in a hosted data center.

In summary, I spend most of my time deploying and managing Azure SQL databases because they are cost effective for over 90% of the use cases that I see.  For the other 10%, consider the other alternatives.  This includes using memory like Apache Spark or distributing data like No SQL.  Next time, I will be talking about the General purpose version of Azure SQL database.

Next Steps
  • General purpose version of Azure SQL Database
  • Business critical version of Azure SQL Database
  • Going hyperscale with Azure SQL Database
  • Why choose Managed Instance over other SQL offerings?





get scripts

next tip button



About the author
MSSQLTips author John MinerJohn Miner is a Data Architect at Insight Digital Innovation helping corporations solve their business needs with various data platform solutions.

View all my tips


Article Last Updated: 2019-12-20
Sours: https://www.mssqltips.com/sqlservertip/6273/going-serverless-with-azure-sql-database/
Optimize price-performance using Azure SQL Database serverless - INT122A

If you're building a new application in Azure and want to use a "serverless" approach, what should you use as a database? Obviously, one of the key goals of "serverless" is to avoid having to manage your own servers, so the classic "IaaS" approach of installing a database on a Virtual Machine isn't a good fit. But there are still plenty of great options. I talked about this in my "Building Serverless Applications in Azure" course on Pluralsight, but things have moved on a bit since then so I thought it was worth revisiting the topic.

As I see it, in Azure there are three main database options to choose between:

  • Relational databases - Azure SQL Database being the most obvious choice here
  • Document database - Azure Cosmos DB is Azure's offering in this space
  • The budget option (or "poor man's" database) - You can also use Azure Storage as a primitive database for minimal cost

Relational Databases

For many (if not most) software developers, relational databases are the most familiar, and they are often our go-to option for storing data. They have the advantage of allowing very flexible queries and joins between related entities (hence the name), but do require the schema to be designed up front, and modifying that schema requires some kind of migration to be performed.

Azure offers a choice of relational databases. The main one is Azure SQL Database, which is essentially a fully managed SQL Server in a PaaS offering. But there is also Azure Database for MySQL, Azure Database for MariaDB, and Azure Database for PostgreSQL available if you are more comfortable with working with one of those databases.

Azure SQL Database is a great choice for a serverless application if you do decide that a relational database is the right choice for you. It's really easy to create one and there are several pricing tiers to support everything from a very small and cheap test system, all the way up to a powerful large-scale production system.

Azure SQL Database makes it really easy to enable key features for production scenarios such as encryption at rest with customer managed keys, backing up (with point-in-time restore), and replication to another region. It even comes with a superb query performance insights blade in the Portal that can tell you which of your queries are performing poorly and what indexes could improve them.

One disadvantage of going for a relational database in a serverless Azure application is that it is a little bit trickier to use from Azure Functions. There aren't built-in bindings like there are for Cosmos DB or Azure Storage, so you need to write your own Entity Framework code to access the database.

Another interesting recent development is that there is now a "serverless" pricing tier for Azure SQL Database. This essentially means that if your database is idle for a certain period (at least an hour) it can hibernate to save you money. It can also automatically scale itself up (within predefined limits) to respond to additional load. This might sound perfect for any serverless application but it does come with some caveats.

First, if your database has gone to sleep, there will be a fairly significant "cold start" penalty to wake it up (resuming takes up to a minute). And secondly, if your database never goes to sleep, then this option can work out more expensive. So beware of having scheduled jobs that run every hour with this approach, as your database will never go to sleep.

Document Databases

Document databases are in many ways a perfect fit for serverless architectures. Because you don't need to predefine your schema up front, they allow you to rapidly iterate and evolve your application over time with minimal fuss. Azure Functions come with some built-in bindings to simplify the code needed to read and store data in a document database.

Although Azure only offers a single document database offering - Cosmos DB, it is an extremely flexible and powerful database. It even supports a variety of different APIs including allowing you to use (for example) the MongoDB API if you're more familiar with that.

One of the most interesting features of Cosmos DB for serverless applications is its concept of a "change feed". This allows you to easily create an Azure Function that can "subscribe" to all changes to documents in a collection. This makes it really easy to generate "materialized views" that allow you to optimize performance and reduce costs of queries.

When Cosmos DB originally came out, the pricing model scared a lot of developers off - the cheapest possible database was three times the cost of the cheapest Azure SQL Database. But things have improved greatly.

Firstly, there is a free tier - allowing you to use a certain amount of resources for free each month which is great for testing and experimenting.

Secondly, Microsoft recently announced a serverless pricing model where the billing will only be based on storage and operations provisioned and could be a good choice for spiky workloads.

Thirdly, you can scale Cosmos DB up and down on the fly, and there is even an "auto-scale" feature that will intelligently scale up and down to save money during idle periods, while meeting demand during peak times.

Using Azure Storage as a poor man's database

Some serverless applications have very simple storage requirements. Maybe you don't often update data, or maybe you don't need rich querying capabilities, and can just look things up by their id.

Azure Storage offers very cheap ways of storing data. For example you could just store data in blobs as JSON or XML files. Or you could use Table Storage, which allows you to store simple table-based documents with a composite key of a "row key" and a "partition key". I've used both options for several small websites and microservices which simply didn't need the cost or complexity of a full database.

This approach can be a great starting point for a proof-of-concept app, and you can graduate later to a "proper" database as your needs change.

The Hybrid Approach

Of course, there's no reason why you have to pick just one of the above options. Especially if you are using a microservices architecture, each microservice can take it's own approach, using the one most appropriate database for the type of data you are storing.

In fact, you may find tht the best approach is hybrid, adding in services like Azure Cognitive SearchAzure Redis Cache, Blob Indexer. So don't feel that you have to pick just one database type for storing all the data in your serverless application.

Sours: https://markheath.net/post/azure-serverless-database

Sql azure serverless

Should I use serverless for all my Azure SQL Databases?

When I first heard about the new serverless compute tier, I wondered if I could use it for all my Azure SQL databases to save my clients some money. (Spoiler alert, you can’t 🙂 )

But first things first, what the heck is serverless? Azure SQL Database serverless is a new compute tier made for single databases. This new model automatically scales the compute based on the actual workload per second. This means that you only have to pay for the compute resources (memory & vCores) you use. Furthermore, serverless has an autopause function. It will pause your database when it remains inactive for a period of minimum 1 hour. For a paused database, only the storage is charged.

 

Only paying what you use, it does seem tempting doesn’t it? Still, there are reasons why provisioned tiers might be a better fit for your workload. First of all, as per writing only general purpose is supported in the serverless compute tier. If you are using business critical and your workload needs the improved I/O performance then serverless probably won’t be a good fit for you. Secondly, you only receive 3GB of memory per vCore whereas you receive 5,5 or 7GB per vCore in the provisioned tiers, depending on the generation of CPU you choose. Besides, you can maximally assign 16 vCores to serverless.

It’s also possible that you experience some delay in the ramp-up of the compute power after idle periods. In extreme circumstances, it’s even possible that the underlying host cannot provision enough resources within a few minutes. In that case “load balancing” will automatically occur. During load balancing the database remains online except for a brief period at the end of the process when all connections are dropped. This behavior is comparable with resizing a database in any provisioned tier.

Lastly, after an auto-pause the first connection to the database will fail.

Memory management

Although the beforementioned points are all valid, it isn’t what would stop me from using serverless. The first possible obstruction lies in the way that SQL Server manages its memory. As we’ve all learned when installing an on premise SQL Server instance, you should specify the memory boundaries and let the SQL OS manage its own memory. Traditionally SQL Server isn’t very keen on releasing memory to the OS. When using serverless databases on the other hand, memory is reclaimed from the database more frequently. This process is triggered whenever cache and CPU utilization is low. Memory entries to free up are chosen the same way as for provisioned databases when memory pressure is high.

This can result in increased disk I/O and query plan compilations after a period of low usage. The total cache size will never go below its minimum size. This boundary is defined by the configured vCore range. It’s important to realize that despite the impact on performance, this behavior certainly has its merits. The sooner memory is released, the less there will be charged.

To test this mechanism I started an identical workload on a serverless and on a provisioned database. The serverless database was configured to use between 0,5 and 1 vCore. Afterwards I monitored the size of the buffer pool. As you can see, after approximately 10 minutes the serverless’ buffer pool was reduced to 1GB. The buffer pool of the provisioned database remained stable, no datapages were flushed from cache.

 

  https://docs.microsoft.com/en-gb/azure 1

Pricing

The main goal of using serverless instead of provisioned database is to reduce cost. It’s very important to gain knowledge about your workload before switching to serverless. The storage cost is the same as for provisioned databases. The compute cost on the other hand is calculated based on the vCores and memory used:

  • When the usage is below the minimum boundary, the cost is calculated based on this minimum boundary.
  • When the usage is between the minimum and maximum boundary, the actual usage of the vCores and memory is used (whichever is higher).
  • When the database is paused, only storage is charged.

At first sight it would seem that, unless your database is constantly using all available resources, serverless would always be the cheapest option. There is a catch though… Serverless compute resources are twice the price of provisioned compute resources. In other words, a constant high load would cost more in serverless than it would in provisioned. The use cases you are looking for are single databases with an intermittent and unpredictable load followed by periods of lower utilization or inactivity. Typical examples are databases used for dev environments.

Although the serverless compute tier isn’t a fit for each database, it certainly has its use cases. I’ve used it a couple of times and it can save you a serious amount of money. Just keep in mind that there might be a negative impact on performance and that you must first examine your workload before choosing for serverless.

Sours: https://kohera.be/azure-cloud/should-i-use-serverless-for-all-my-azure-sql-databases/
Working with Azure SQL DB Serverless with Carlos Lopez

Azure SQL Database is an Infrastructure-as-a-Service component for migrating your on-premises SQL Server to cloud infrastructure. We always look for performance, cost, and scaling resources while we plan resources in the cloud. Many times, we do not want our databases running 24*7*365. Suppose you have a development or training database. Your developers work during the daytime on the weekdays. If you deploy an Azure SQL Database, you don’t get an option to stop it. You get charged for it whether you use it actively or not.

No option to pause or stop azure database

Similarly, you might have specific workloads such as batch data load process that requires high compute resources for limited hours. In that case, we do not want to configure a high compute azure SQL database and pay for higher resources all the time.

Azure SQL Database Serverless compute resources for a single database with the automatic scaling feature. It automatically scales up and down resources based on your workload requirements. If you do not have active connections for specific hours, it automatically pauses databases. Usually, while the serverless database runs, you get charged for storage and per second compute resource usage. Once the database is in paused status, it charges you only for storage, no bills charged for computing resources.

Cost comparison of Azure SQL Database in Serverless and Provisioned compute tier

Once we start deploying the azure database, by default, it configures a general-purpose compute tier with 2vCores and 32 GB storage.

Cost comparison

Click on the configure database, and you get Provisioned, and Serverless compute tiers. In the Provisioned tier, your price is dependent on the VCores and max storage. As shown in the below image, it costs you 26660.08 INR estimated cost per month.

Provisioned, and Serverless compute tiers.

In case you increase the vCores and data max size, you get a higher estimated cost per month.

vCores and data max size

Now, let’s check the estimated cost for Serverless computing. Here, you define min and max vCores. For the below min 0.75 vCores and max 1 vCores, it automatically selects 2.02 GB min and 3 GB max memory.

In the estimated price, you get the following estimates:

  • Estimated storage cost per month
  • Estimated compute resource cost per second

Serverless computing

You can change the min and max vCores and max storage to estimate database cost in a serverless architecture.

Compute bills in Azure database serverless architecture

As shown above, Azure SQL Database serverless architecture charges you for the total number of seconds you have active workload. Now, suppose you have done the following configurations for the serverless database.

  • Min vCores: 1
  • Max vCores: 4
  • Minimum memory: 3 GB
  • Maximum Memory: 12 GB
  • Max data storage: 200 GB

You get the estimated price for running workload as below.

Compute bills in Azure database serverless architecture

As per Microsoft docs, the serverless SQL database uses the following formula for billing.

Amount billed: vCore unit price * max (min vCores, vCores used, min memory GB * 1/3, memory GB used * 1/3)

In the following table, we estimate the number of seconds vCores seconds you get billed.

Active Duration

vCores used per second

GB Memory used each second

Calculations for billed vCores

Billed vCores seconds

1 minute

(60 seconds)

1.5 ( Greater than min vCores)

3 ( Less than max Memory GB)

(1.5 vCore *( 3 GB /3 GB)) * 60 seconds= 90 seconds

90 seconds

1 minute

(60 seconds)

4 ( max vCores)

9 ( Less than max memory)

4 ( max vCores) * 60 Seconds = 240 seconds

240 seconds

1 minute

(60 seconds)

0.5 (less than min vCores)

12 ( max memory)

12 (max memory) /3 GB * 60 seconds = 240 seconds

240 seconds

1 minute

(60 seconds)

0.5(less than min vCores)

9( Less than max memory)

(1 (min vCore) * 9 GB( memory used) /3 GB )* 60 seconds= 180 seconds

180 seconds

1 hour( 3600 seconds)

0.5(less than min vCores)

9( Less than max memory)

(1 ( min vCore) * 9 ( Memory used) /3 GB )* 3600 seconds= 3600 seconds

3600 seconds

Auto-pause delay

In the Azure Serverless architecture, the database automatically pauses if it is inactive during the specified duration. By default, it is enabled and set to 1 hour (3600 seconds).

Auto-pause delay

  • Minimum auto-pause: 60 minutes (1 hour)
  • Maximum auto-pause duration 10080 minutes (7 days)

It automatically triggers the auto-pause mechanism if it satisfies the following conditions:

  1. Number of sessions : 0

AND

  1. CPU = 0 for any user workload

However, exceptions are always there. If you use the following azure SQL database features, the database always remains online irrespective of auto-pause delay configurations.

  • Long-term backup retention (LTR)
  • Geo-replication
  • SQL data sync
  • Elastic jobs
  • DNS aliasing

Once the azure database is in pause status, it resumes automatically in the following conditions:

  • Database connection
  • database export or copy
  • Viewing auditing records
  • Viewing or applying performance recommendation
  • Vulnerability assessment
  • Modifying or viewing data masking rules
  • View state for transparent data encryption
  • Modification for serverless configuration such as max vCores, min vCores, or auto-pause delay

Let’s implement an Azure SQL Database in the serverless architecture. In the configure database of creating SQL database page, define a minimum and maximum vCores, storage and auto-pause delay as per your requirement.

Minimum and maximum vCores

Click Apply, and it returns to create a database page. As we can see, it creates general-purpose Serverless Gen 5 with a maximum of 4 vCores and 10 GB of data storage.

create a database page

On the review page, you can review serverless database configuration and cost. If you find any configuration issues, you can go back and correct them.

serverless database configuration

Click Create and within a few minutes, your azure SQL database with serverless architecture is available.

Note down server name

Connecting to a serverless database is similar to a provisioned SQL database. Copy the Server name from the azure portal and paste it in the SSMS connection window.

We can verify database service level objective, edition and maximum size from the Configure SLO page of database properties. Here, you need to sign in with your Azure credentials.

Verify DB SLO

Serverless compute tier behavior

In the below graph, we can observe the vCores and their usage for calculating your azure database cost.

  • It has a minimum of 1 vCore and 4 maximum vCores
  • At 8 AM, we started the database load, and it gradually increases and becomes inactive at 10 PM
  • Due to inactive database connections, it gets paused between 00:00 to 04:00 hrs
  • At 4 AM, you connected to the database again, and it resumes the database
  • The green bars show the vCores billed and you can note that from 00:00 to 04:00 you do not get charged for vCores

compute tier behaviour

Image reference: Microsoft docs

Configure a Serverless Azure SQL Database using Azure CLI

Previously, we explored Azure portal configurations for creating a SQL database in Serverless configuration. You can use Azure CLI or Azure PowerShell or SSMS as well to create the database. You can refer to the article, An Overview of the Azure Cloud Shell for Azure CLI configuration.

For example, in the below CLI command, we define resource name, Azure server, database name, compute tiers as General purpose, Gen5 with minimum vCPU 0.5, maximum vCPU 2 , Serverless compute model with auto-pause delay of 720 minutes.

$resourceGroupName='azuresqldemo'

$serverName='azuredemoinstance'

$databaseName='myserverlessazuredb'

az sql db create-g$resourceGroupName-s$serverName-n$databaseName`

  -eGeneralPurpose-fGen5--min-capacity0.5-c2--compute-model Serverless--auto-pause-delay720

In the output, it returns database properties in JSON format.

SQL Database using Azure CLI

Configure a Serverless Azure SQL Database using PowerShell

Similarly, we can use New-AzSqlDatabase cmdlet with parameters similar to the azure CLI command.

$resourceGroupName='azuresqldemo'

$serverName='azuredemoinstance'

$databaseName='myserverlessazuredb_1'

New-AzSqlDatabase-ResourceGroupName$resourceGroupName-ServerName$serverName-DatabaseName$databaseName`

-ComputeModel Serverless-Edition GeneralPurpose-ComputeGeneration Gen5`

-MinVcore0.5-MaxVcore2-AutoPauseDelayInMinutes720

SQL Database using Azure PowerShell

Refresh Azure portal for SQL databases, and it reflects the newly created azure SQL database with serverless computing.

Refresh Azure portal

Create a database using T-SQL for serverless computing

If you have already connected to Azure SQL Server, you can use the CREATE DATABASE command to deploy a database with serverless computing.

In the below T-SQL, we defined the edition as General Purpose and service objective as GP_S_Gen5_1. It creates a database with default values for min vCores and auto-pause delay. Therefore, you should use Azure portal, Azure CLI or PowerShell for creating a database in serverless compute with custom configurations.

CREATEDATABASESampledatabase

    (EDITION='GeneralPurpose',SERVICE_OBJECTIVE='GP_S_Gen5_1');

As shown below, it has a default auto-pause delay of 1 hour.

Verify Auto-pause delay

It has a minimum of 0.5 vCores and 1 maximum vCores using the T-SQL script.

Verify minimum and maximum vCores

Essential points about the Serverless compute model of the azure database

  • You should use SSMS version 18.1 or higher for working with the Azure SQL Server serverless compute. The lower SSMS version resumes auto-paused database if you connect to any other database in the same server
  • It is supported only in the Generation 5 hardware of the VCore computing model
  • It is suitable for a single database with intermittent and unpredictable usage pattern workload
  • It is an excellent way to start with the azure database where you don’t have to an upfront cost and pay only as per usage

Conclusion

Azure SQL Database serverless compute model is an excellent way to start with SQL database in Azure. It is similar to the standard database with an auto-pause and resume mechanism based on your configuration. However, it is not a fit for each database. You should evaluate your requirements, workload, analyze vCPU requirements before deploying it.

Rajendra Gupta

Rajendra Gupta

As an MCSA certified and Microsoft Certified Trainer in Gurgaon, India, with 13 years of experience, Rajendra works for a variety of large companies focusing on performance optimization, monitoring, high availability, and disaster recovery strategies and implementation. He is the author of hundreds of authoritative articles on SQL Server, Azure, MySQL, Linux, Power BI, Performance tuning, AWS/Amazon RDS, Git, and related technologies that have been viewed by over 10m readers to date.

He is the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups. Based on his contribution to the SQL Server community, he has been recognized with various awards including the prestigious “Best author of the year" continuously in 2020 and 2021 at SQLShack.

Raj is always interested in new challenges so if you need consulting help on any subject covered in his writings, he can be reached at [email protected]

View all posts by Rajendra Gupta

Rajendra Gupta

Latest posts by Rajendra Gupta (see all)

Azure

About Rajendra Gupta

As an MCSA certified and Microsoft Certified Trainer in Gurgaon, India, with 13 years of experience, Rajendra works for a variety of large companies focusing on performance optimization, monitoring, high availability, and disaster recovery strategies and implementation. He is the author of hundreds of authoritative articles on SQL Server, Azure, MySQL, Linux, Power BI, Performance tuning, AWS/Amazon RDS, Git, and related technologies that have been viewed by over 10m readers to date. He is the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups. Based on his contribution to the SQL Server community, he has been recognized with various awards including the prestigious “Best author of the year" continuously in 2020 and 2021 at SQLShack. Raj is always interested in new challenges so if you need consulting help on any subject covered in his writings, he can be reached at [email protected] all posts by Rajendra Gupta

View all posts by Rajendra Gupta →

Sours: https://www.sqlshack.com/automatic-pause-and-resume-an-azure-sql-database/

Now discussing:

In this article, we will take a look at how to create a serverless Azure SQL database.

Introduction to Serverless Computing Model

Traditionally on-premise or private data-center infrastructure was used to host the IT eco-system. With the advent of the cloud, the IT ecosystem composed of applications, data repositories and services started moving to the cloud. One of the core benefits of cloud that drives migration from on-premise to cloud is the financial incentive, which is achieved due to a heavy reduction in capital and operational expenditure on hardware and infrastructure. Generally, those moving from on-premise to cloud consider the Infrastructure As a Service (IaaS) option i.e. in simplified words, hosting systems on a Virtual Machine (VM). In the context of the Azure cloud and SQL Server as the database server, hosting it on a VM. With this model, while one does not have to permanently own or maintain hardware, but still there is a significant degree of software, infrastructure capacity and OS management involved.

The next logical step from here is to move to Azure SQL Database instances or Managed SQL Database or Elastic Pooled database instance on Azure. With this model, the onus of managing the OS and the software itself gets eliminated, but still one needs to know the capacity one needs to meet the requirements of their workloads. To deal with this situation, a serverless model comes in which can auto-scale based on the threshold criteria to meet the elastic scaling needs of the workloads. SQL Server Database on Azure supports the serverless model and in this article, we would learn how to create a serverless Azure database.

Serverless Azure SQL Database Setup

A pre-requisite to follow this exercise is to have an Azure Account with required privileges on Azure SQL Server Database as well as Azure SQL Server service. As the first step, log on to the Azure portal and navigate to All Services. Click on the Azure SQL Database service from the Databases section as shown below.

Azure SQL Server Database

Once you click on this option, you would be navigated to the database Dashboard page. Let’s start creating a new database on Azure. Click on the Create SQL Database button to initiate database creation.

Create New Database

Provide the subscription and resource group details as shown below. It’s assumed that you already have a resource group in place, if not, you may need to create one and specify in the resource group option.

Basics of Azure SQL Server Database

In the next step, we need to provide a database name and select the SQL Server instance under which this database would be hosted. It’s assumed that you already have a SQL Server instance in place, if not, you can create a new one by clicking on the Create New button under the Server dropdown.

Basics

In the next step, click on the Configure database button. By default, the selected tier is S0 with 10 DTU and 250 GB storage capacity. This falls in the provisioned tier where the capacity is fixed. We intend to change this to the serverless mode so that we can configure it to be elastic.

Baiscs of SQL Server Databases on Azure

Once you click on Configure database, you would see a screen as shown below where you would see the Basic tier, 10 STU And 250 GB storage selection. If you are using the trial subscription, you may find some tiers unavailable for use based on the type of your subscription. Click on the arrow near vCore-based purchasing options, to uncover the other tiers.

Serverless Settings

Once you click on the arrow, the below options would appear. You would find that by default the Provisioned compute tier is selected, which means that compute resources are pre-allocated and fixed. In this model, we pay for the compute resources irrespective of whether it’s used or not.

Serverless Settings on Azure

Click on the Serverless option as shown below, so that the compute resources are elastically scaled based on the workloads. Considering the elasticity, one needs to define the minimum and maximum thresholds so that based on the volatility of the workloads, the resources do not get too under-provisioned or over-provisioned which may have cost or performance impacts.

Serverless Settings of SQL Server Database.

Here you can see that a minimum of 1 vCore and a maximum 2 vCore are selected, and based on this the available memory of 3 GB to 6 GB is auto-selected, as this is the capacity which is tied to the selected vCore sizes.

Azure SQL Server Database Serverless Settings

The next setting that we need to configure is the Auto-pause delay. This setting causes the database to pause after a fixed interval of inactivity and resume once the activity resumes. You can disable this setting too. In non-production workloads, when the environment is dormant, this can save a significant amount of costs. Depending on your requirements, select a relevant time duration for pausing the database. You can also configure the storage capacity as required. By default, the storage capacity is 32 GB.

Serverless Settings

Once done click on the Apply button, and you would find the configuration as shown below.

Databasse Serverless Settings

You can choose to customize the rest of the settings or continue with the default. Once you are done with the rest of the settings, when you reach the Review section, you would find the details as shown below. Based on our selection, the estimated monthly cost is shown to be just $4.78 per month due to the serverless model of the compute.

Create option

Review the details and click on the Create button to create the server Azure SQL Server database. Once the database is created, you would be able to see the dashboard page of this database as shown below.

Dashboard of SQL Server Database

You can connect to this serverless Azure SQL Database using a locally installed SSMS too and execute some queries.

SSMS

Once the database is kept idle for a duration that is more than the auto-pause threshold, the database would automatically get paused. You can check the database utilization and status from the dashboard page as shown below where you would find the spike and drop in compute usage when the database gets paused and when it resumes.

Monitor Activity

In this way, we can create a serverless Azure SQL Database, which provides us the required elasticity as well as cost savings depending on the needs of the workloads.

Conclusion

In this article, we understood how the serverless model provides elasticity and reduces the management or need to pre-determine the capacity allocated or required for the database. Then we learned step-by-step, how to create and configure an Azure SQL database in serverless mode, as well as define the thresholds for the elasticity.

Rahul Mehta

Rahul Mehta

Rahul Mehta is a Software Architect with Capgemini focusing on cloud-enabled solutions. He works on various cloud-based technologies like AWS, Azure, and others.

He has worked internationally with Fortune 500 clients in various sectors and is a passionate author.

View all posts by Rahul Mehta

Rahul Mehta

Latest posts by Rahul Mehta (see all)

Azure, SQL Azure

About Rahul Mehta

Rahul Mehta is a Software Architect with Capgemini focusing on cloud-enabled solutions. He works on various cloud-based technologies like AWS, Azure, and others. He has worked internationally with Fortune 500 clients in various sectors and is a passionate author. View all posts by Rahul Mehta

View all posts by Rahul Mehta →

Sours: https://www.sqlshack.com/create-a-serverless-azure-sql-database/


751 752 753 754 755