How-to auto-scale Azure SQL Databases

by Contributed | Apr 19, 2021 | Technology

This article is contributed. See the original author and article here.

Azure SQL Database offers an easy several-clicks solution to scale database instances when more resources are needed. This is one of the strengths of PaaS, you pay for only what you use and if you need more or less, it’s easy to do the change. A current limitation, however, is that the scaling operation is a manual one. The service doesn’t support auto-scaling as some of us would expect.

Having said that, using the power of Azure we can set up a workflow that auto-scales an Azure SQL Database instance to the next immediate tier when a specific condition is met. For example: what if you could auto-scale the database as soon as it goes over 85% CPU usage for a sustained period of 5 minutes? Using this tutorial we will achieve just that.

Supported SKUs: because there is no automatic way to get the list of available tiers at script runtime, these must be hard-coded into it. For this reason, the script below only supports DTU and vCore (provisioned compute) databases. Hyperscale, Serverless, Fsv2, DC and M series are not supported. Having said that, the logic is the same not matter the tier so feel free to modify the script to suit your particular SKU needs.

Step #1: deploy Azure Automation account and update its modules

The scale operation will be executed by a PowerShell runbook inside of an Azure Automation account. Search Automation in the Azure Portal search bar and create a new Automation account. Make sure to create a Run As Account while doing this:

Once the Automation account has been created, we need to update the PowerShell modules in it. The runbook we will use uses PowerShell cmdlets but by default these are old versions when the Automation account is provisioned. To update the modules to be used:

• Save the PowerShell script here to your computer with the name Update-AutomationAzureModulesForAccountManual.ps1. The Manual word is added to the file name as to not overwrite the default internal runbook the account uses to update other modules once it gets imported.


• Import a new module and select the file you saved on step #1:

• When the runbook has been imported, click Test Pane, fill in the details for the Resource Group and the Azure Automation account name we are using and click Start:

• When it finishes, the cmdlets will be fully updated. This benefits not only the SQL cmdlets used below but any other cmdlets any other runbook may use on this same Automation account.

Step #2: create scaling runbook

With our Automation account deployed and updated, we are now ready to create the script. Create a new runbook and copy the code below:

The script below uses Webhook data passed from the alert. This data contains useful information about the resource the alert gets triggered from, which means the script can auto-scale any database and no parameters are needed; it only needs to be called from an alert using the Common Alert Schema on an Azure SQL database.

param
(
    [Parameter (Mandatory=$false)]
    [object] $WebhookData
)
# If there is webhook data coming from an Azure Alert, go into the workflow.
if ($WebhookData){
    # Get the data object from WebhookData
    $WebhookBody = (ConvertFrom-Json -InputObject $WebhookData.RequestBody)

    # Get the info needed to identify the SQL database (depends on the payload schema)
    $schemaId = $WebhookBody.schemaId
    Write-Verbose "schemaId: $schemaId" -Verbose
    if ($schemaId -eq "azureMonitorCommonAlertSchema") {
        # This is the common Metric Alert schema (released March 2019)
        $Essentials = [object] ($WebhookBody.data).essentials
        Write-Output $Essentials
        # Get the first target only as this script doesn't handle multiple
        $alertTargetIdArray = (($Essentials.alertTargetIds)[0]).Split("/")
        $SubId = ($alertTargetIdArray)[2]
        $ResourceGroupName = ($alertTargetIdArray)[4]
        $ResourceType = ($alertTargetIdArray)[6] + "/" + ($alertTargetIdArray)[7]
        $ServerName = ($alertTargetIdArray)[8]
        $DatabaseName = ($alertTargetIdArray)[-1]
        $status = $Essentials.monitorCondition
    }
    else{
        # Schema not supported
        Write-Error "The alert data schema - $schemaId - is not supported."
    }
    # If the alert that triggered the runbook is Activated or Fired, it means we want to autoscale the database.
    # When the alert gets resolved, the runbook will be triggered again but because the status will be Resolved, no autoscaling will happen.
    if (($status -eq "Activated") -or ($status -eq "Fired"))
    {
        Write-Output "resourceType: $ResourceType"
        Write-Output "resourceName: $DatabaseName"
        Write-Output "serverName: $ServerName"
        Write-Output "resourceGroupName: $ResourceGroupName"
        Write-Output "subscriptionId: $SubId"

        # Because Azure SQL tiers cannot be obtained programatically, we need to hardcode them as below.
        # The 3 arrays below make this runbook support the DTU tier and the provisioned compute tiers, on Generation 4 and 5 and
        # for both General Purpose and Business Critical tiers.

        $DtuTiers = @('Basic','S0','S1','S2','S3','S4','S6','S7','S9','S12','P1','P2','P4','P6','P11','P15')
        $Gen4Cores = @('1','2','3','4','5','6','7','8','9','10','16','24')
        $Gen5Cores = @('2','4','6','8','10','12','14','16','18','20','24','32','40','80')

        # Here, we connect to the Azure Portal with the Automation Run As account we provisioned when creating the Automation account.

        $connectionName = "AzureRunAsConnection"
        try
        {
            # Get the connection "AzureRunAsConnection "
            $servicePrincipalConnection=Get-AutomationConnection -Name $connectionName         

            "Logging in to Azure..."
            Add-AzureRmAccount `
                -ServicePrincipal `
                -TenantId $servicePrincipalConnection.TenantId `
                -ApplicationId $servicePrincipalConnection.ApplicationId `
                -CertificateThumbprint $servicePrincipalConnection.CertificateThumbprint 
        }
        catch {
            if (!$servicePrincipalConnection)
            {
                $ErrorMessage = "Connection $connectionName not found."
                throw $ErrorMessage
            } else{
                Write-Error -Message $_.Exception
                throw $_.Exception
            }
        }

        # Gets the current database details, from where we'll capture the Edition and the current service objective.
        # With this information, the below if/else will determine the next tier that the database should be scaled to.
        # Example: if DTU database is S6, this script will scale it to S7. This ensures the script continues to scale up the DB in case CPU keeps pegging at 100%.

        $currentDatabaseDetails = Get-AzureRmSqlDatabase -ResourceGroupName $ResourceGroupName -DatabaseName $DatabaseName -ServerName $ServerName

        if (($currentDatabaseDetails.Edition -eq "Basic") -Or ($currentDatabaseDetails.Edition -eq "Standard") -Or ($currentDatabaseDetails.Edition -eq "Premium"))
        {
            Write-Output "Database is DTU model."
            if ($currentDatabaseDetails.CurrentServiceObjectiveName -eq "P15") {
                Write-Output "DTU database is already at highest tier (P15). Suggestion is to move to Business Critical vCore model with 32+ vCores."
            } else {
                for ($i=0; $i -lt $DtuTiers.length; $i++) {
                    if ($DtuTiers[$i].equals($currentDatabaseDetails.CurrentServiceObjectiveName)) {
                        Set-AzureRmSqlDatabase -ResourceGroupName $ResourceGroupName -DatabaseName $DatabaseName -ServerName $ServerName -RequestedServiceObjectiveName $DtuTiers[$i+1]
                        break
                    }
                }
            }
        } else {
            Write-Output "Database is vCore model."

            $currentVcores = ""
            $currentTier = $currentDatabaseDetails.CurrentServiceObjectiveName.SubString(0,8)
            $currentGeneration = $currentDatabaseDetails.CurrentServiceObjectiveName.SubString(6,1)
            $coresArrayToBeUsed = ""
            try {
                $currentVcores = $currentDatabaseDetails.CurrentServiceObjectiveName.SubString(8,2)
            } catch {
                $currentVcores = $currentDatabaseDetails.CurrentServiceObjectiveName.SubString(8,1)
            }
            Write-Output $currentGeneration
            if ($currentGeneration -eq "5") {
                $coresArrayToBeUsed = $Gen5Cores
            } else {
                $coresArrayToBeUsed = $Gen4Cores
            }
            
            if ($currentVcores -eq $coresArrayToBeUsed[$coresArrayToBeUsed.length]) {
                Write-Output "vCore database is already at highest number of cores. Suggestion is to optimize workload."
            } else {
                for ($i=0; $i -lt $coresArrayToBeUsed.length; $i++) {
                    if ($coresArrayToBeUsed[$i] -eq $currentVcores) {
                        $newvCoreCount = $coresArrayToBeUsed[$i+1]
                        Set-AzureRmSqlDatabase -ResourceGroupName $ResourceGroupName -DatabaseName $DatabaseName -ServerName $ServerName -RequestedServiceObjectiveName "$currentTier$newvCoreCount"
                        break
                    }
                }
            }
        }
    }
}

Step #3: create Azure Monitor Alert to trigger the Automation runbook

On your Azure SQL Database, create a new alert rule:

The next blade will require several different setups:

1. Scope of the alert: this will be auto-populated if +New Alert Rule was clicked from within the database itself.


2. Condition: when should the alert get triggered by selecting a signal and defining its logic.


3. Actions: when the alert gets triggered, what will happen?

Condition

For this example, the alert will monitor the CPU consumption every 1 minute. When the average goes over 85%, the alert will be triggered:

Actions 

After the signal logic is created, we need to tell the alert what to do when it gets fired. We will do this with an action group. When creating a new action group, two tabs will help us configure sending an email and triggering the runbook:

Notifications

Actions

After saving the action group, add the remaining details to the alert.

That’s it! The alert is now enabled and will auto-scale the database when fired. The runbook will be executed twice per alert: once when fired and another when resolved but it will only perform a scale operation when fired.

Unified experiences across endpoint and email are now generally available in Microsoft 365 Defender

by Contributed | Apr 19, 2021 | Technology

This article is contributed. See the original author and article here.

We’re excited to announce that we have reached a new milestone in our XDR journey: the integration of our endpoint and email and collaboration capabilities into Microsoft 365 Defender is now generally available. Security teams can manage all endpoint, email, and cross-product investigations, configuration, and remediation within a single unified portal.

Register for the Microsoft 365 Defender’s Unified Experience for XDR webinar to learn how your security teams can leverage the unified portal and check out our video to learn more about these new capabilities.

This release delivers the rich set of capabilities we announced in public preview, including unified pages for alerts, users, and automated investigations, a new email entity page offering a 360-degree view of an email, threat analytics, a brand-new Learning hub, and more – all available exclusively in the Microsoft 365 Defender portal at security.microsoft.com.

Now is the time to start moving your users to the unified experience using the automatic URL redirection for Microsoft Defender for Endpoint and automatic URL redirection for Microsoft Defender for Office 365 as the previously distinct portals will eventually be phased out.

Figure 1: Endpoint features integrated into Microsoft 365 Defender.  

Figure 2: Email and collaboration features integrated into Microsoft 365 Defender. 

We’re excited to be bringing these additional capabilities into Microsoft 365 Defender and look forward to hearing about your experiences and your feedback as you explore and transition to the unified portal.

To read more about the unified portal experience, check out: 

• Overview – Microsoft 365 security center 


• Microsoft Defender for Endpoint in the Microsoft 365 security center 


• Microsoft Defender for Office 365 in the Microsoft 365 security center 

An Online Education Journey To New Potential

by Contributed | Apr 19, 2021 | Technology

This article is contributed. See the original author and article here.

Ivana Tilca’s tech journey is marked by a life-long passion for learning. The AI MVP from Argentina is now a tech evangelist at the top of her field, but things were not always this way.

Originally, Ivana began her academic career in journalism at National University in Cordoba, Argentina. For family reasons, however, Ivana moved back to her home city of Salta where she changed course and began studies in Information Systems.

“I started speaking at events and mentoring students at my university in my 20s, and from that moment I realized the passion I feel for technology and for sharing with the community,” Ivana says.

It was not as easy to learn then as it is now. Rather, the education experience relied on books or the little content that existed on the internet. That’s when Ivana found Microsoft Channel 9, a video platform that covered innovation and topics breaking new ground. Then, Ivana says she was later able to support those videos with MS Learn and MS Docs.

Fast forward years later and Ivana is an accomplished MVP and Quality Manager at 3XM Group. These resources changed the course of her education experience, Ivana says, especially in an ever-changing field like AI. Further, the tech evangelist continues to take advantage of online resources to study AI, autonomous systems and mixed realities. 

“I believe that technology is advancing by leaps and bounds, and the reality is that those who do not spend time updating themselves lose competitiveness in the professional market,” Ivana says.

“Further, continuing to face challenges gives you motivation and passion for your profession.”

For those looking to break into AI, Ivana suggests the following open resources: Bring AI to your business with AI Builder, AI edge engineer, Build AI solutions with Azure Machine Learning, Explore computer vision in Microsoft Azure.

“I think many professions in the future will involve the use of AI,” Ivana says. “AI has existed for several years, but recently it has grown and found its place. It is a branch of technology that is just beginning and will continue to improve constantly, and personally, I believe that it has no limits.”

For more on Ivana, visit her Twitter @ivanatilca

MVPs Ignite Education In China and Japan

by Contributed | Apr 19, 2021 | Technology

This article is contributed. See the original author and article here.

Microsoft Ignite might be done and dusted for another year, but that did not stop scores of MVPs and tech enthusiasts in both China and Japan from continuing their education journey in March.

Following the global tech conference, both communities in Asia decided to review the takeaways in their local language and focus on lessons learned.

First, Microsoft China hosted a two-day digital conference, Microsoft Ignite China, that comprised seven keynotes, 11 connection sessions and 30 featured sessions. In collaboration with the MVPs and Regional Directors (RD), the March 18 event brought to life six hours of online engagement per day, with two RDs contributing to the “Connection Zone” and nine MVPs helping with live stage sessions.

Data Platform MVP Dan Zhang says it is very important for self-learners to have an integrated and authentic learning platform in China. Microsoft Ignite China, along with resources like MS Learn, are vital to staying on top of the ever-changing world of tech, Dan says.

“Knowledge on the internet is separated and unsystematic, which will cost learners a lot of time and energy to find good resources and materials. The emergence of MS Learn is a good benefit for numbers of developers,” Dan says.

“Continuous learning in technical areas is important in China since it can help developers keep up to pace with new tech and the environment,” agrees AI MVP Yuxiang Wang. Meanwhile, Azure and AI MVP Hao Hu suggests that an additional achievement system on MS Learn could be one way to encourage developers to further their skills. 

Later that month on March 24, it was time for the community-driven learning event Microsoft Ignite Recap Community Day in Japan. A total of 20 MVP/RD/community leaders contributed to 10 sessions across the 4-hour event. Not only that, but the 20 MVP/RDs also shared their Ignite recommended sessions for further member learning.

Business Applications / Office Apps & Services MVP Taichi Nakamura joined multiple sessions and shared the latest updates of Microsoft 365 & Power Platform. Taichi says he wanted to take part simply because it looked fun. “I wanted to share my joy with attendees, provide our passion to attendees, and call more attention to join a tech community,” he says.

Developer Technologies MVP and fellow session presenter Kazushi Kamegawa says the pandemic has enacted many rapid changes in the world, and education remains an important way to stay connected. “As an engineer, of course, in order to adapt to the new world in the future, I think I have to continue to learn, to identify the right information, and not to be left behind by the situation in the world.”

Azure MVP Tetsuya Odashima contributed to one of the Ignite recommended session resources and says it is important to remember that “learning is not something that is given by someone, but something that you can get by acting on your own.”

“Learning new things stimulates curiosity,” posits Enterprise Mobility MVP Yutaro Tamai. “I’d like to share this experience with more community members.”

For more information on the dual events, visit the community pages for Microsoft Ignite China and Microsoft Ignite Recap Community Day Japan.

Introducing the Email Entity Page in Microsoft Defender for Office 365!

by Contributed | Apr 19, 2021 | Technology

This article is contributed. See the original author and article here.

Today I wanted to share with you some exciting new capabilities that are now available to help Microsoft Defender for Office 365 and Microsoft 365 Defender customers investigate emails.

Investigating email threats is now easier than ever!

We know that you, the security teams, spend a lot of time diving deep into alerts, hunting threats, identifying malicious indicators, and taking remediation actions. You go through multiple workflows to take the right measures to protect your organization. These workflows involving email borne threats typically have a few steps in common – all involving analyzing an email in question and any related emails – to answer questions like: Why did the system call an email malicious? Why did an email get blocked (or delivered)? How many users (and which ones) received these emails? What actions have already been taken on these emails? And a lot more.

Answering these questions often takes time and effort. And we consistently hear how much you crave ever-increasing efficiency in the tools you use, so the effort and time involved in responding to alerts and threats is reduced.

That’s why we’re excited to introduce the new Email Entity page in Microsoft Defender for Office 365. A simple, yet rich experience that offers a single pane of glass view to answer all the questions above, greatly amplifying the efficiency with which you can investigate and respond to threats.

Introducing the new Email Entity page

The new email entity page brings a comprehensive experience that provides an exhaustive view of details critical to investigation. The email entity page gives a 360-degree view of an email in one page, and helps security analysts save time and effort, leading to more effective threat protection.

Curious why an email was delivered despite being marked as malicious? Or what the latest location of the email is? What are the rich set of details for a URL or file that was detonated? Was it sent to a priority account? The email entity page brings you the answer to these questions, and the details you need to investigate and analyze an email – overrides, exchange transport rules, latest delivery location, detonation details, tags and a lot more.

The email page has information and capabilities for analysts to dig deeper into intricate email details, and headers, look at email preview or email download. The email page also builds on our promise to integrate Defender for Office 365 tightly with other Microsoft 365 Defender experiences like hunting, alerts, investigations and more.

What’s exciting about the Email Entity page?

We are sure the single page view is appealing, but that is not it. We bring a lot more details and capabilities to the new email entity page.

Each tab presents you with information about the email. The timeline tab has a series of events which took place on email by system, admin or user.

 Figure 1: The timeline tab has a series of events which took place on email by system, admin or user.

The analysis tab shows pre and post-delivery fields about email, in addition to the headers presented in the same tab, helpful for a side-by-side analysis.

Figure 2: The analysis tab shows pre and post-delivery fields about email, in addition to the email headers

The attachment and URL tabs present detailed information about attachments and URLs present in the email, along with detonation details in case a detonation occurs (shown in the section later on detonation details).

Lastly, the similar emails tab shows emails found similar to the email. Similar emails are found using the body fingerprint i.e. the cluster ID.

Figure 3: The similar emails tab shows emails found similar to the email, using cluster ID

The email entity page not only has enriched details, but also new capabilities to help the security operations team investigate successfully, like email preview and detonation details.   

Email preview for cloud mailboxes

We now provide full previews of emails found in cloud mailboxes. No need to download a copy of a malicious message to understand what your users saw – you can now do this with the click of a button from the safety of the admin center.

Figure 4: Email preview provides full previews of emails found in cloud mailboxes

Exposing details for detonated URLs and attachments

With the email entity page, we have greatly enhanced the level of details we present about the observations we make in the detonation chamber for entities which get detonated. When a URL or file present in an email is found malicious during detonation, we will present the information to help you understand the full scope of related threats. Detonation details reveal information like the full detonation chain, a detonation summary, a screenshot and observed behavior details. This information can help security teams understand why we reached a malicious verdict for a URL or file following a detonation.

For file detonation cases (you can filter by detection technology in Threat Explorer), the Attachments tab shows a list of attachments and their respective threats. Clicking on the malicious attachment opens the detonation details flyout for the detonated attachments. For URL detonations, the URL tab shows a list of URLs and the corresponding threats. Clicking on the malicious URL will open the detonation details flyout for detonated URLs.

 Figure 5: Detonation details shows additional details discovered during detonation of links and files

How can I get started with this new experience?

If you have Microsoft Defender for Office 365 or Microsoft 365 Defender, you can take advantage of this new experience today. When hunting for email-based threats, natively integrated into Explorer, you may now choose to navigate to the new email entity page. You can do the same with alerts experience, across both the security and protection portals at security.microsoft.com and protection.office.com respectively.

Learn more about the email entity page on Microsoft Docs, and check out a video overview of these capabilities here.

Do you have questions or feedback about Microsoft Defender for Office 365? Engage with the community and Microsoft experts in the Defender for Office 365 forum.