Author: ramvarma

###

Few days back I was assigned a consulting effort for a well-known customer who wants to explore an azure offering. One of the discussion during this engagement has dragged me to talk about Azure datawarehouse/Synapse and its functionalities . When I talk about MPP architecture and azure SQL datawarehouse most of my customers don’t forget to say ” it’s very expensive…. Can’t afford” . Though I can justify the cost of spinning a parallel processing system , often I respond them with a smile (even they know why and how it’s expensive ) . But this time I decided not to take this lightly instead I requested them to walk me through their pricing tier and utilization pattern .

I started with one of their non-production synapse data pool and got to know that their billing is considerably high compare to their usage . This is because they kept running the synapse pool during most of the day when most of their internal development and testing teams are active .With the help of few DMV’s and tracing I could understand the pool is idle at least 3-4 hours (out of ~ 10 hours) a day . When I questioned about pausing  the compute during idle time , the challenge brought to the table was to identify an usage pattern . They can’t really predict the usage , it totally depends on the testing and development activity . So if I can give them an automated framework which can look for active sessions periodically and take a decision to pause the synapse pool then half of their pain is subdued. Another half is to resume the synapse pool based on the demand , and this can be sorted out by giving them a self-service web application to resume the synapse compute . ( right now it all depends on admin ,end consumers have to wait based on admins availability )

I have used a runbook to pause datawarehouse compute pool based on usage . Script inside runbook will in-fact pause the compute if there is no activity from last 30 minutes ( time is flexible ) . Checks will not consider internal/system sessions and runbook’s own session .

Get this runbook scheduled every “xyz” minutes . ( in my demo it is 30 minutes )

# Modules to be installed and imported.

Import-Module Az.Accounts
Import-Module SqlServer
Import-Module Az.Sql

# Using automation runasconnection to get TenantId and SubscriptionId
$Connection = Get-AutomationConnection -Name "AzureRunAsConnection"
$TenantId = $Connection.TenantId
$SubscriptionId = $Connection.SubscriptionId
 
# Get the service principal credentials connected to the automation account. 
$SPCredential = Get-AutomationPSCredential -Name "logtest"
 
# Login to Azure ($null is to prevent output since Out-Null doesn't work in Azure)
Write-Output "Login to Azure using automation account 'logtest'."
$null = Connect-AzAccount -ServicePrincipal -Credential $SPCredential -Tenant $TenantId


$Conn = Get-AutomationConnection -Name AzureRunAsConnection
Connect-AzAccount -ServicePrincipal -Tenant $Conn.TenantID -ApplicationId $Conn.ApplicationID -CertificateThumbprint $Conn.CertificateThumbprint


$response = Invoke-WebRequest ifconfig.co/ip -UseBasicParsing
$ip = $response.Content.Trim()
Set-AzContext -SubscriptionId "<your subscription id>"
Set-AzSqlServerFirewallRule -ResourceGroupName "<your resource group>" -ServerName "<your server name>" -FirewallRuleName "Azure Synapse Runbook" -StartIpAddress $ip -EndIpAddress $ip

Start-Sleep -s 180

$status = Get-AzSqlDatabase -ResourceGroupName "<your resource group name>" -ServerName "<your server name>" -DatabaseName "<DW pool name>"

if ($status.Status -like 'paused' )
{
Write-Output "Synapse pool is already paused"
}
else
{
Write-Output "Checking if there are any active transactions"
$params = @{
  'Database' = '<DW pool name>'
  'ServerInstance' =  '<server name>.database.windows.net'
  'Username' = '<user name>'
  'Password' = '<password>'
  'OutputSqlErrors' = $true
  'Query' = 'if exists
( select * from sys.dm_pdw_exec_sessions where status in (''ACTIVE'',''IDLE'') and (session_id <> session_id()) 
and (app_name not in (''Internal'')) and (status in (''IDLE'') and login_time > DATEADD(minute, -30, GETDATE()))
)
    begin
        select 1;
    end
else

    begin
        select 0;
    end'
   }

 $transactions = Invoke-Sqlcmd  @params
 if ($transactions.Column1 -eq 0)
 {
 Write-Output "pausing azure synapse sql pool as there are no active transactions" 
 Suspend-AzSqlDatabase -ResourceGroupName "<your resource group>" -ServerName "<server name>" -DatabaseName "<DW pool name>" | Out-Null
 Write-Output "paused azure synapse sql pool"
 }
 else {
 Write-Output "There are active transactions hence cannot pause"
 }
}

First half is done. Now I may have to look into the second half of the challenge , which is resuming the synapse pool compute without admin intervention . For this very purpose I have developed a simple C# web application which fetch the current status of the synapse pool with a button click and another click to resume the compute if wanted . Intentionally I did not provision a pause button on the grounds that it may create outages if someone try to pause without actually checking the sessions currently going on .

“Button1_Click “ C# method to get the status of the synapse pool in a textbox. Here I am making a REST API call with an SPN authentication to get the current status of the synapse ( its worthy to note that I have hardcoded for the sake of demo purpose . In case you are in need of handing multiple synapse pools then you may have to accept the input from a text box and build a dynamic uri to pass on )

protected void Button1_Click(object sender, EventArgs e)
        {
            string clientId = "<GIVE YOUR SPN ID>";
            string clientSecret = "<GIVE YOUR SPN SECRET>";
            string tenantId = "<GIVE YOUR TENANT ID>";
            string authContextURL = "https://login.windows.net/" + tenantId;
            var authenticationContext = new AuthenticationContext(authContextURL);
            var credential = new ClientCredential(clientId, clientSecret);
            var result = authenticationContext.AcquireToken(resource: "https://management.azure.com/", credential);
            string token = result.AccessToken;
            string rawurl = "https://management.azure.com/subscriptions/subscriptionid/resourceGroups/rgname/providers/Microsoft.Sql/servers/server/databases/pool?api-version=2014-04-01";
            Uri uri = new Uri(String.Format(rawurl));
            var httpWebRequest = (HttpWebRequest)WebRequest.Create(uri);
            httpWebRequest.Headers.Add(HttpRequestHeader.Authorization, "Bearer " + token);
            httpWebRequest.ContentType = "application/json";
            httpWebRequest.Method = "GET";
            HttpWebResponse httpResponse = null;
            try
            {
                httpResponse = (HttpWebResponse)httpWebRequest.GetResponse();
                
            }
            catch (Exception ex)
            {
               
            }
            string result1 = null;
            
            using (var streamReader = new StreamReader(httpResponse.GetResponseStream()))
            {
                result1 = streamReader.ReadToEnd();
                dynamic jsondata = JObject.Parse(result1);
                string status = jsondata.properties.status;
                TextBox1.Text = status;

            }

“Button_Click “  to send a resume instruction via REST method .

protected void Button_Click(object sender, EventArgs e)
        {
            string clientId = "<GIVE YOUR SPN ID>";
            string clientSecret = "<GIVE YOUR SPN SECRET>";
            string tenantId = "<GIVE YOUR TENANT ID>";
            string authContextURL = "https://login.windows.net/" + tenantId;
            var authenticationContext = new AuthenticationContext(authContextURL);
            var credential = new ClientCredential(clientId, clientSecret);
            var result = authenticationContext.AcquireToken(resource: "https://management.azure.com/", credential);
            string token = result.AccessToken;
            string puturl = https://management.azure.com/subscriptions/subscriptionid/resourceGroups/rgname/providers/Microsoft.Sql/servers/servername/databases/poolname/resume?api-version=2014-04-01-preview";
            Uri urip = new Uri(String.Format(puturl));
            var httpWebRequestput = (HttpWebRequest)WebRequest.Create(urip);
            httpWebRequestput.Headers.Add(HttpRequestHeader.Authorization, "Bearer " + token);
            httpWebRequestput.ContentType = "application/json";
            httpWebRequestput.Method = "POST";
            try
            {
                using (var streamWriter = new StreamWriter(httpWebRequestput.GetRequestStream()))
                {
                    string vn = null;
                    streamWriter.Write(vn);
                    streamWriter.Flush();
                    streamWriter.Close();
                }
            }
            catch (Exception ex)
            {

            }

            HttpWebResponse httpResponse = null;
            try
            {
                httpResponse = (HttpWebResponse)httpWebRequestput.GetResponse();

            }
            catch (Exception ex)
            {

            }

Remember , credentials used in runbook and SPN used for REST calls require appropriate permission to pause and resume the synapse pool . To further enhance this self service portal you can restrict the access only to designated users and alert admin with information like who resumed ? time /date and purpose . ( and if needed an approval from admin to get it resumed during weekends or holidays or non-business hours for specific set of users)

However ,  this helped my client save an estimate 6K $ per year for a single synapse pool . Hope this helps your customer too !!!