Have you ever experienced that you accidentally deleted your Logical Server in Azure? Because, for example, you made your Pipeline wrong. Surely. And of course you didn't have a backup in your storage either. Well I must confess that happened to me recently. It was on my test environment but if you delete a configuration database with your metadata it is quite annoying.
How to Resolve?
But there is a solution to get the deleted Azure SQL databases back.
You need to take the following steps for this:
Recreate the Logical server with the same name and in the same region.
Do not re-deploy the deleted databases from your pipeline.
After the Logical Server has been created, click Create Database in the Azure Portal
Go directly to the tab Additional Settings
Select Backup
Search for your deleted Logical Server and you will see that you can restore your removed databases.
Select the correct database, click on review and create. Your database will now be restored
The last thing you need to do is to remove the datetime extension from your database
You can now start using your database again and all security roles that were there before are also present.
Repeat above steps in case you had more databases attached to the same server.
It's a fairly simple process, but you just need to know it. And it will ultimately save you a lot of time and frustration. If you have any questions regarding the above, please let me know.
Use this template to scale up and down an Azure SQL Database in Azure Synapse Analytics or in Azure Data Factory.
This article describes a solution template how you can Scale up or down a SQL Database within Azure Synapse Analytics or Azure Data Factory dynamically based on metadata. This is actually a necessary functionality during your Data Movement Solutions. In this way you can optimize costs and gain more performance during batch loading. The Pipeline can be added before and after your Nightly Run.
The template contains 8 activities:
Lookup Activity Get the necessary metadata from a table in your configuration database.
Until Activity to check a set of activities in a loop until the condition associated with the activity evaluates to true.
Web Activity activity which will check the current Status of the SQL Pool
Wait Activity activity which will wait before retry to check the Status of the SQL Database
If Condition Activity Activity to check if the SQL Database is Online
Web Activity Activity to Resume the SQL Database(Serverless only) if not Online
Wait Activity Activity to wait before to go to the next activity
Web Activity Activity to Scale the SQL Database up or down to the desired DatabaseLevel
Pipeline Parameters:
Parameter
Value
Description
WaitTime
10
Wait time in seconds before the Pipeline will finish
Create a control table in Azure SQL Database to store the Metadata.
[NOTE] > The table and stored procedure can be stored in any database, but preferred in a database where you store all your configuration in.
[sql]
CREATE TABLE [configuration].[Environment_Parameter1](
[ParameterId] [int] IDENTITY(1,1) NOT NULL,
[ParameterName] [varchar](128) NOT NULL,
[ParameterValue] [nvarchar](max) NOT NULL,
[Description] [nvarchar](max) NULL,
CONSTRAINT [PK_Environment_Parameter1] PRIMARY KEY CLUSTERED
(
[ParameterId] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
INSERT [configuration].[Environment_Parameter] ( [ParameterName], [ParameterValue], [Description]) VALUES (N'yourResourceGroupName', N'', N'ResourceGroupName of your Azure Synapse or ADF Instance')
GO
INSERT [configuration].[Environment_Parameter] ( [ParameterName], [ParameterValue], [Description]) VALUES (N'SubscriptionId', N'XXXXXXXX', N'SubscriptionId of your Azure Synapse or ADF Instance')
GO
INSERT [configuration].[Environment_Parameter] ( [ParameterName], [ParameterValue], [Description]) VALUES (N'SQLServer', N'yoursqlserver', N'Name of your SQL Server( Needed for scaling databases)')
GO
[/sql]
[sql]
CREATE PROCEDURE [configuration].[Environment]
@ColumnToPivot NVARCHAR(255),
@ListToPivot NVARCHAR(max)
AS
/**********************************************************************************************************
* SP Name: [configuration].[[Environment]]
*
* Purpose: Procedure display record parameters for environment Settings
*
*
* Revision Date/Time:
* 2020-12-01 Erwin de Kreuk (InSpark) - Initial creation of SP
*
**********************************************************************************************************/
BEGIN
DECLARE @SqlStatement NVARCHAR(MAX)
SET @SqlStatement = N'
SELECT * FROM (
SELECT
[ParameterName] ,
[ParameterValue]
FROM [configuration].[Environment_Parameter] ) EnvironmentTable
PIVOT
(max([ParameterValue])
FOR ['+@ColumnToPivot+']
IN ('+@ListToPivot+' ) ) AS PivotTable
';
EXEC(@SqlStatement)
END
[/sql]
After you have imported the Template you will see the following:
[NOTE] > Azure Synapse has no import functionality, create a new pipeline PL_ACT_SCALE_SQLDATABASE and copy the code into the pipeline. Once the pipeline is created manualy link the correct linked service for your Metadata table
Create a connection to the database where your metadata tables is stored. Followed by use this template.
Lookup Activity Name = Get SQL Server Name
Source Dataset = Linked Services to your Metadata Table
Until Activity We can only change the DatabaseLevel when the SQL Database is Paused or Online. That’s why we need to add an Until activity to check for these statusses.
Web Activity Within the Until Activity we need to create a new Web Activity.
https://management.azure.com/subscriptions/@{activity('Get SQL Server Name').output.firstRow.SubscriptionId}/resourceGroups/@{activity('Get SQL Server Name').output.firstRow.ResourceGroupName}/providers/Microsoft.Sql/servers/@{activity('Get SQL Server Name').output.firstRow.SQLServer}/databases/@{pipeline().parameters.DatabaseName}/?api-version=2019-06-01-preview
After we have created the Web Activity, we can define the expression for the Until Activity.
The Pipeline can only continue when the current status is not scaling. We can check this by comparing the currentServiceObjectiveName and the requestedServiceObjectiveName.
Expression: @equals(activity('Check for Database Status').Output.Properties.currentServiceObjectiveName,activity('Check for Database Status').Output.Properties.requestedServiceObjectiveName)
Time out: 0.00:20:00
The Until Activity will only continue, when the status from the above Web Activity output is equal, this can take a while and we don’t want to execute the Web Activity every time. That’s why we add a Wait Activity.
Wait Activity
A Wait Activity waits for the specified period of time before continuing with execution of subsequent activities.
Check for the SQL Database Status (Serverless Only)
If Condition Activity (Name: Check if Database is Paused). When is SQL Database is Paused, we need to Resume
Expression: @bool(startswith(activity('Check for Database Status').Output.Properties.status,'Paused'))
Web Activity In case the SQL Database is Paused we need to Resume.
The XXX are replaced with the output from Lookup activity.
https://management.azure.com/subscriptions/@{activity('Get SQL Server Name').output.firstRow.SubscriptionId}/resourceGroups/@{activity('Get SQL Server Name').output.firstRow.ResourceGroupName}/providers/Microsoft.Sql/servers/@{activity('Get SQL Server Name').output.firstRow.SQLServer}/databases/@{activity('Get SQL Server Name').output.firstRow.DatabaseName}/Resume?api-version=2019-06-01-preview
It is almost the same URL we used in the First Web Actvity but have to add the action option Resume.
The XXX are replaced with the output from Lookup activity.
https://management.azure.com/subscriptions/@{activity('Get SQL Server Name').output.firstRow.SubscriptionId}/resourceGroups/@{activity('Get SQL Server Name').output.firstRow.ResourceGroupName}/providers/Microsoft.Sql/servers/@{activity('Get SQL Server Name').output.firstRow.SQLServer}/databases/@{pipeline().parameters.DatabaseName}/?api-version=2019-06-01-preview
Method = PATCH
Headers = Name = Content-Type Value= application/json
Body = { “sku”: { “name”: ‘@{pipeline().parameters.DatabaseLevel}’ } }
To allow Azure Synapse Analytics or Azure Data Factory to call the REST API we need to give the Synapse/ADF access to the SQL Database/Server. In the Access control (IAM) of the SQL Server assign the SQL Contributor role to Synapse/ADF.
Debug
Select Debug, enter the Parameters, define the correct DatabaseLevel and DatabaseName to Scale and then select Finish.
When the pipeline run completes successfully, you will see the result similar to the following example:
You can now call this pipeline from every other pipeline, you only need to change the DatabaseLevel and DatabaseName.
You have now learned how to Scale your SQL Database Dynamically with the use of Metadata.
Please feel free to download the Pipeline code here for Azure Synapse Analytics and for herefor Azure Data Factory
Hopefully this article has helped you a step further. As always, if you have any questions, leave them in the comments.
During the event 'Around the clock Azure SQL and Azure Data Factory' event on Feb 3, 2021, they kicked-off the Azure Data Factory (ADF) Hackathon. Recording of this event can be found here.
Winner
I submitted a ADF Pipeline Template "Scale Dedicated SQL Pool Dynamically using Azure Data Factory control flow" and my submission was marked as WINNER. I am very proud that a simple template where you can easily save costs has won. See full post of the announcement Announcing the Azure Data Factory Hackathon winners! | LinkedIn
This template will help you can to scale up and down a Dedicated SQL Pool in Azure Synapse Analytics.
The pipelines is designed to Scale a SQL Pool within Azure Synapse Analytics. A SQL Pool(Former Azure SQL DW) linked to a SQL (Logical) Server has a slightly different approach(documentation can be found on Github).
Scaling a SQL Pool is actually a necessary functionality during your Data Movement Solutions, it will help to save and optimize your costs.
Documentation of this pipeline can be found on GitHub.
You can also use this template in Azure Synapse and the details can also be found on Github more details can also be found in this Article.
In case you have unanswered questions please do not hesitate to contact me.
Scale your Dedicated SQL Pool in Azure Synapse Analytics
In my previous article, I explained how you can Pause and Resume your Dedicated SQL Pool with a Pipeline in Azure Synapse Analytics. In this article I will explain how to scale up and down a SQL Pool via a Pipeline in Azure Synapse Analytics. This is actually a necessary functionality during your Data Movement Solutions. In this way you can optimize costs.
The Pipeline can be added before and after your Nightly Run.
As a quick resume from the previous article, a SQL Pool can have different statuses:
Pausing: SQL Pool is Pausing and we cannot change the status.
Resuming: SQL Pool is Resuming, the SQL Pool starting and during this process and we cannot change the status.
Scaling: SQL Pool is Scaling, the SQL Pool is scaling to a different compute level and during this process we cannot change the status.
Paused: SQLPool is Paused, we can now change the status.
Online: SQLPool is Online, we can now change the status.
To allow the Synapse workspace to call the REST API we need to give the Synapse workspace access to the SQL Pool. In the Access control (IAM) of the SQL Pool assign the contributor role to your Synapse Workspace.
Build Pipeline
Clone the Pipeline PL_ACT_RESUME_SQLPOOL and rename it to PL_ACT_SCALE_SQLPOOL.
Change the description of the Pipeline, ‘Pipeline to SCALE a Synapse Dedicated SQL Pool‘
Add the PerformanceLevel parameter to the Parameters of the Pipeline:
Action: RESUME(Leave this on RESUME, if we want to SCALE the SQL Pool must be Online)
WaitTime: Wait time in seconds before the Pipeline will finish
WaitTimeUntil: Wait time in seconds for the retry process
Synapse_ResourceGroupName: Name of the ResourceGroup of the used Synapse Workspace
SynapseWorkspace: SynapseWorkspace
SynapseDedicatedSQLPool: Name of the dedicated SQL Pool
SubsriptionId: SubscriptionId of Synapse Workspace
We leave the first two activities as is. The Pipeline can only continue when the status is Paused or Online and not one of the other statuses. When the SQL Pool is Paused, the second activity will Resume the SQL Pool.
To Scale the SQL Pool we need add a new Web Activity.
Headers = Name = Content-Type Value= application/json
Body = { “sku”: { “name”: ‘@{pipeline().parameters.PerformanceLevel}’ } }
Resource =https://management.azure.com/
Please feel free to download the Pipeline code here.
DAILY RUN
Add the above Pipeline as a Start Pipeline before your Daily run and Scale up to the desired Performance Level. When the Daily run is finished you Scale Down to a lower level or can you add the Pipeline to Pause the SQL Pool.
Metadata
If you’re already using a database where you store your Meta Data, you can create a table where you store the desired Performance Level The only thing you need to do is adding a Lookup Activity to get the parameters from your database and replace the parameters with the output from the lookup activity.
[sql]
CREATE TABLE [configuration].[Database_Level](
[Id] [int] IDENTITY(1,1) NOT NULL,
[DatabaseName] [varchar](30) NULL,
[DatabaseLevel] [varchar](10) NOT NULL,
[PerformanceLevel] [varchar](10) NOT NULL,
CONSTRAINT [PK_Pipeline_ExecutionLog] PRIMARY KEY CLUSTERED
(
[Id] DESC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
[/sql]
A SQL Pool(Former SQL DW)
A SQL Pool(Former SQL DW) linked to a SQL (Logical) Server has a slightly different approach.
Use the settings below to create a Pipeline to Scale the SQL Pool.
Action: RESUME
WaitTime: Wait time in seconds before the Pipeline will finish
WaitTimeUntil: Wait time in seconds for the retry process
SQLServer_ResourceGroupName: Name of the ResourceGroup of the used SQL(Logical) Server
SQLServer: SQL(Logical) Server name
SQLServerDedicatedSQLPool: Name of the dedicated SQL Pool
SubsriptionId: SubscriptionId of Synapse Workspace
Pause or Resume your Dedicated SQL Pool in Azure Synapse Analytics
Azure Synapse Analytics went GA in beginning of December 2020, with Azure Synapse we can now also create a Dedicated SQL Pool(formerly Azure SQL DW). Please read this document to learn what a Dedicated SQL Pool is. This article describes how to Pause or Resume a SQL Pool within Azure Synapse Analytics. A SQL Pool(Former Azure SQL DW) linked to a SQL (Logical) Server has a slightly different approach.
A SQL Pool is a MPP Database (short for massively parallel processing) and has a different approach of loading data but also different kind of pricing. This are details for another Blogpost.
Pausing: SQL Pool is Pausing and we cannot change the status.
Resuming: SQL Pool is Resuming, the SQL Pool starting and during this process and we cannot change the status.
Scaling: SQL Pool is Scaling, the SQL Pool is scaling to a different compute level and during this process we cannot change the status.
Paused: SQLPool is Paused, we can now change the status.
Online: SQLPool is Online, we can now change the status.
To allow the Synapse workspace to call the REST API we need to give the Synapse workspace access to the SQL Pool. In the Access control (IAM) of the SQL Pool assign the contributor role to your Synapse Workspace.
Build Pipeline
Create a new Pipeline with the name PL_ACT_PAUSE_SQLPOOL
Add the following Parameters to the Pipeline:
Above are the generic Parameters used within the Pipeline.
Action: PAUSE or RESUME
WaitTime: Wait time in seconds before the Pipeline will finish
WaitTimeUntil: Wait time in seconds for the retry process
Synapse_ResourceGroupName: Name of the ResourceGroup of the used Synapse Workspace
SynapseWorkspace: SynapseWorkspace
SynapseDedicatedSQLPool: Name of the dedicated SQL Pool
SubsriptionId: SubscriptionId of Synapse Workspace
Until Activity
We can only change the status when the SQL Pool is Paused or Online That’s why we need to add an Until activity to start the Pipeline. It executes a set of activities in a loop until the condition associated with the activity evaluates to true.
With this activity we can check the status of the SQL Pool and wait until it becomes Paused or Online. Let me explain how this works.
Web Activity
Within the Until Activity we need to create a new Web Activity. A Web Activity can be used to call a custom REST API endpoint from a Synapse Data pipeline.
Once we have created the Web Activity, we can define the expression for the Until Activity.
The Pipeline can only continue when the status is Paused or Online and not one of the other statuses. That’s the reason we need to add these 2 two statuses to check for.
Expression: @or(bool(startswith(activity(‘Check for changed SQLPool Status’).Output.Properties.status,’Paused’)),Bool(startswith(activity(‘Check for changed SQLPool Status’).Output.Properties.status,’Online’)))
Time out: 0.00:20:00
The Until Activity will only continue, when the status from the above Web Activity output is Paused or Online, this can take a while and we don’t want to execute the Web Activity every time. That’s why we add a Wait Activity.
Wait Activity
A Wait Activity waits for the specified period of time before continuing with execution of subsequent activities. Within the Wait Activity add an expression from above parameters for Wait time seconds.
After the Web Activity, Azure Synapse waits in this case 30 seconds to check if the status has changed before it will check again.
Check for the SQL Pool Status
To check if the SQL Pool is paused, we’re adding an If Condition Activity (Name: Check if SQL POOL is Paused)
Add an Expression on the If Condition Activity@bool(startswith(activity(‘Check for changed SQLPool Status’).Output.Properties.status,’Paused’))
This expression will check if the SQL Pool is Paused or not. In this situation we want to Pause our SQL Pool, to Pause the SQL Pool we need to add as Activity for pausing(see below) to False. In case the SQL Pool is already Paused we do nothing(True).
The following settings are set for the Web Activity:
It is almost the same URL but we have to add the action option @{pipeline().parameters.Action}
Method = Post
Header = {“Nothing”:”Nothing”}
Resource =https://management.azure.com/
Add a Wait Activity but this time with a different parameter @pipeline().parameters.WaitTime, the purpose of this activity is to wait a period before we start ingestion data(just to be sure the SQL Pool in online)
Create Pipeline to Resume your SQL Pool
Clone your PL_ACT_PAUSE_SQLPOOL and rename it to PL_ACT_RESUME_SQLPOOL. Change your action Parameter to “Online”.
Within the IF Condition move the Web Activity Pause SQL Pool and the Wait Activity from False to True and rename to Resume SQL Pool.
You have now learned how to Pause and Resume your SQL Pool Dynamically with the use of Parameters. Both Pipelines can be easily transferred to different customers.
Please feel free to download the Pipeline code here
MetaData
If you’re already using a database where you store your Meta Data, then you have also the possibility to store the necessary parameters in the database. The only thing you need to do is adding a Lookup Activity to get the parameters from your database(and replace the parameters with the output from the lookup activity)
A SQL Pool(Former SQL DW)
A SQL Pool(Former SQL DW) linked to a SQL (Logical) Server has a slightly different approach, use the settings below to create a Pipeline to Pause or Resume.
Action: PAUSE or RESUME
WaitTime: Wait time in seconds before the Pipeline will finish
WaitTimeUntil: Wait time in seconds for the retry process
SQLServer_ResourceGroupName: Name of the ResourceGroup of the used SQL(Logical) Server
SQLServer: SQL(Logical) Server name
SQLServerDedicatedSQLPool: Name of the dedicated SQL Pool
SubsriptionId: SubscriptionId of Synapse Workspace
To allow the Synapse workspace to call the REST API we need to give the Synapse workspace access to the SQL(Logical) Server. In the Access control (IAM) of the SQL(Logical) Server assign the SQL DB Contributor role to your Synapse Workspace.
Hopefully this article has helped you a step further. As always, if you have any questions, leave them in the comments.
