How to Scaleup and ScaleDown Azure SQL database using Azure data factory?

 Sometimes we face the problem to Scaleup/ ScaleDown the Azure SQL DB using code in our adf pipeline. We handle it manually most of the times but what if our adf pipeline is triggered by Schedule trigger and we need to Scale up / down DB as per Start/ finish of pipeline copy activity?


here is the solution.


Create a proc to Select and Update Current DTU of Azure SQL DB.


Create Proc SelectUpdate

( @CurrentDtu varchar (10) output , @DTUtoUpdate varchar (10)) -- S12 - 3000


as

begin

SELECT

@CurrentDTU =slo.service_objective

FROM sys.databases d

JOIN sys.database_service_objectives slo

ON d.database_id = slo.database_id


ALTER DATABASE [TestAzureDB] MODIFY( SERVICE_OBJECTIVE=@DTUtoUpdate)


end


adf pipeline steps


1- call the proc (@cDTU output,'S12') to select current DTU first n then update DTU --- call this from lookup activity and it will set value into @cDTU variable


2- use wait activity - bcz DB takes 1 min to reflect changes


3 - copy data activity


4 - call same proc again by passing value of current DTU variable to reset back to old dtu


I hope it will helpful


#azuresql #adf #database #data #azure #Mission100AzureDataEngineer

Comments

Post a Comment

Popular posts from this blog

Performance optimization in Copy data activity in Azure

This article outlines the copy activity performance optimization features that you can leverage in Azure Data Factory and Synapse pipelines. Copy performance optimization features The service provides the following performance optimization features: Database transaction units (DTUs) Data Integration Units Self-hosted integration runtime scalability Parallel copy Staged copy Database transaction units (DTUs) A database transaction unit (DTU) represents a blended measure of CPU, memory, reads, and writes.  Service tiers in the DTU-based purchasing model are differentiated by a range of compute sizes with a  fixed amount of included storage, fixed retention period for backups, and fixed price. All service tiers in the DTU-based purchasing model  provide flexibility of changing compute sizes with minimal downtime; however, there is a switch over period where connectivity is lost to  the database for a short amount of time, which can be mitigated using retry logic. Single...
Read more

Azure blob Storage interview questions

 Azure Blob Storage is a cloud-based storage service that is often used to store and process large amounts of data.  it is important to be prepared to answer questions about this service. 1. What is Azure Blob Storage? Azure Blob Storage is a cloud-based storage service that is optimized for storing large amounts of unstructured data, such as video, audio,and images. Blob storage is also a good choice for storing data that needs to be accessed frequently, but doesn’t need to be updated often. 2. How does Azure Blob storage work? Azure Blob storage is a cloud-based storage service that allows you to save and retrieve data from anywhere in the world.  Blobs are stored in containers, which can be public or private, and can be accessed via a URL. Azure Blob storage is a great way to store large amounts of data that you need to be able to access quickly and easily. 3. What are the key components of Azure blob storage? The key components of Azure blob storage are containers and...
Read more

Why do we have two keys in storage account and need for rotate them ?

 On more than one occasion developers do not see the sense to have two keys for an Azure Storage account when they always use the first one  to its developments. The truth is that I have found that most of the time there is no key maintenance   to improve the security of the storage account.   The idea with this pair of keys is to always have a «backup key» when you regenerate one of them so that there is no loss of service at any time.  There might be case when you see your key1 has been compromised so you can rotate the key in applications , doing this your application will still work. The upgrade process in this case could be the following: The first key is used in your applications. When it’s time to regenerate the keys, change the first key for the second key in your applications settings that are using it. Regenerate the primary key and again change the secondary key with the new one generated. Regenerate the second key. This process can easily be aut...
Read more