Some people might have noticed it, but Microsoft has released a new Azure Icon in its portal. By changing the icon Microsoft wants to match the style with their Fluent Design System making it more familiar for their customers.
Recently a customer asked me how to save cost on their Azure SQL database without moving away from DTU based subscription model. In this case this customer knows exactly at what time their database is heavily utilized, and when it’s idling. So with a script its easy to automate.
In this manual we are going to size a SQL database from S4 to S3.
Step 1: In this first step we are going to add some modules to your Automation Account. Go to modules, and click on Browse gallery
From the Gallery search for az.accounts, click on it
Next make sure to Import the module
Now browse the Gallery again, this time search for az.sql and make sure to import this module as well.
STEP 2: This next step is important. We will need to create and assign a Run As Account when you’ve chosen not to create a run as account on the setup of your automation Account. Go to Run as Account, and click on Create Azure Run As Account
Click on Create
STEP 3: Now we will need to add some variables to your automation account. These variables will need to be filled with information about your Azure SQL Database and Server. Create the following variables, and make sure that you fill them.
Servername (without database.windows.net)
STEP 4: Now go to runbooks, and create a new runbook!
Give your runbook a name, as type select PowerShell!
In the new opened window copy and paste the code from below. Adjust the variables $Edition and $PricingTier to your needs.
$ResourceGroupName = Get-AutomationVariable -Name "Resourcegroup"
$ServerName = Get-AutomationVariable -Name "Servername"
$DatabaseName = Get-AutomationVariable -Name "Database"
$Edition = "Standard"
$PricingTier = "S4"
# Keep track of time
# Log in to Azure with AZ (standard code)
Write-Verbose -Message 'Connecting to Azure'
# Name of the Azure Run As connection
$ConnectionName = 'AzureRunAsConnection'
# Get the connection properties
$ServicePrincipalConnection = Get-AutomationConnection -Name $ConnectionName
'Log in to Azure...'
$null = Connect-AzAccount `
-TenantId $ServicePrincipalConnection.TenantId `
-ApplicationId $ServicePrincipalConnection.ApplicationId `
# You forgot to turn on 'Create Azure Run As account'
$ErrorMessage = "Connection $ConnectionName not found."
# Something else went wrong
Write-Error -Message $_.Exception.Message
# Getting the database for testing and logging purposes
$MyAzureSqlDatabase = Get-AzSqlDatabase -ResourceGroupName $ResourceGroupName -ServerName $ServerName -DatabaseName $DatabaseName
Write-Error "$($ServerName)\$($DatabaseName) not found in $($ResourceGroupName)"
Write-Output "Current pricing tier of $($ServerName)\$($DatabaseName): $($MyAzureSqlDatabase.Edition) - $($MyAzureSqlDatabase.CurrentServiceObjectiveName)"
# Set Pricing Tier Database
# Check for incompatible actions
if ($MyAzureSqlDatabase.Edition -eq $Edition -And $MyAzureSqlDatabase.CurrentServiceObjectiveName -eq $PricingTier)
Write-Error "Cannot change pricing tier of $($ServerName)\$($DatabaseName) because the new pricing tier is equal to current pricing tier"
Write-Output "Changing pricing tier to $($Edition) - $($PricingTier)"
$null = Set-AzSqlDatabase -DatabaseName $DatabaseName -ServerName $ServerName -ResourceGroupName $ResourceGroupName -Edition $Edition -RequestedServiceObjectiveName $PricingTier
# Show when finished
$Duration = NEW-TIMESPAN –Start $StartDate –End (GET-DATE)
Write-Output "Done in $([int]$Duration.TotalMinutes) minute(s) and $([int]$Duration.Seconds) second(s)"
Use the menu to Save your runbook, use the Test pane to review the output of your PowerShell script. When ready Publish your runbook!
STEP 5: Last step is to create a schedule. From your workbook go to Schedules, and Add an schedule.
Create a new schedule based on your requirements/needs.
Click create to finalize the process. Now go back to your SQL database. When the change is happening, you should see a update line like below that shows that the pricing tier is being updated!
How cool would it be to automate your daily SQL tasks using Azure Automation? Well, really cool off course! So lets start using Azure Automation! So go ahead, if you don’t have an automation account yet, create one by going to Automation Accounts.
Give your automation account an name, choose a subscription, resource group and a location and hit the create button!
When you migrate to Azure SQL, you might think that Azure does all SQL maintenance, including the maintenance of your database… But the truth is, you will need to setup some maintenance yourself for your databases. Microsoft doesn’t know what is best for your application or database. With this manual you should be able to setup basic database maintenance on Azure SQL.
Microsoft had announced the limited preview of Azure Shared disks. With these announcement it will be possible to migrate clustered environments running Windows Server to Azure. This capability is designed to support SQL Server, Scale-Out File servers, RDS User Profile Disk and SAP ASCS/SCS servers running on Windows. Also Linux-based clustered file systems like GFS2 are supported.
The diagram above shows a 2 node cluster with a single shared disk. Just one node will receive write access, the other node will only receive read access. In case Azure Virtual Machine 1 goes down, write access will be transferred to Azure Virtual Machine 2. This scenario can be extended to more than 2 machines, but multiple shared disks can be attached as well, making it ideal for running parallel jobs or other multi machine tasks.
Azure Shared Disks are only available on Premium SSD disks and only greater than P15 (256GiB) Microsoft has announced that Azure Ultra disk support will be released soon. The number of nodes that can be attached to a disk needs to be preset before mounting the disk to any node. Each disk type has its only limitation. The IOPS limit and bandwidth limit are not affected by this number. I would recommend to set this value has high as possible when deploying. In case a shared disk needs resizing to expand the number of nodes, it is required to un-mount the disk from all nodes.
Today I noticed a new checkbox in the Azure Portal. Microsoft has released IPv6 in the Public preview for Azure VNets. Virtual machines will be equipped with a dual-stack IP connectivity. Meaning both will be available. With the ending of IPv4 addresses it makes IPv6 mandatory for everybody.
From the Azure portal you can now add IPv6 address to the address scope on the VNet level.
The following diagram shows how IPv6 works as a dual-stack next to IPv4
If you have a large on premise environment, you might want to automate the assignment of Office 365 licenses by using (dynamic) security groups in Azure AD. With this simple manual you should be able to setup automatic license assignment based on a security group.
By default everyone may create a new team in Microsoft Teams. As an organisation admin you might want to control this, or release it a some point. With this manual you should be able to lock down team creation to users that are member of a Azure AD Security group.
STEP 1: First we will need to install the Preview version of the Azure Active Directory PowerShell module for Graph. Open a PowerShell window with Adminstrator privileges and run the following 2 commands:
The result of the script should give you the updated settings. On the last line you should see EnableGroupCreation. If you want to reverse this setting. Just simply change the following line to True and run the entire script:
$AllowGroupCreation = “True”
If you want another security group, rerun the script with the new group name.
Microsoft has announced SSD bursting capabilities. This means that Premium SSD disks can achieve higher peak loads than the maximum IOPS with a new maximum of 3500 IOPS and a bandwidth up to 170 MiB/s. Together with this announcement Microsoft also announced new disk sizes (4, 8 & 16 GiB)
With the new bursting disks you can achieve up to 30 times the provisioned bandwidth, which will give better performance for spiky workloads. Disk bursting is based on a credit system. You will receive bursting credits when traffic is below the provisioned limit. Let me try to explain it using a simple chart.
Multi Factor Authentication (MFA) is an added security feature from Azure which I believe that should be enabled by default for everybody in Office 365 and Azure. There for this manual how to enforce (Azure) MFA for all users using Azure Multi Factor Authentication
MFA can prevent unauthorized access in case of the following events:
Sign-ins from anonymous IP addresses
Impossible travel to atypical locations
Sign-ins from unfamiliar locations
Sign-ins from infected devices
Sign-ins from IP addresses with suspicious activities
Using Conditional access we can ensure that your users and company data is safe. Important to know is that Office 365 MFA is free of charge, and if you have Azure AD applications an Azure AD Premium license is required.
If you want to mark your locations as trusted location, you can do that if you have a static public IP. So the first steps are there to define your office locations.
I recently run into a case where I needed to update statistics of an Azure SQL Database because of poor performance and deadlocks. Preventing disruptions is key, so it is important to do something about it. With a simple script we can update the statistics easaly.
Why should I update statistics?
SQL Server statistics are essential for the query optimizer to prepare an optimized and cost-effective execution plan. These statistics provide distribution of column values to the query optimizer, and it helps SQL Server to estimate the number of rows. The query optimizer should be updated regularly. Improper statistics might mislead query optimizer to choose costly operators such as index scan over index seek and it might cause high CPU, memory and IO issues in SQL Server. We might also face blocking, deadlocks that eventually causes trouble to the underlying queries, resources.
Just execute the following query on your database and you should be good to go! Keep in mind, depending on your database this might take a while. During this script your database will get slow, but will remain online.
SET NOCOUNT ON
DECLARE updatestats CURSOR FOR
SELECT table_schema, table_name
where TABLE_TYPE = 'BASE TABLE'
DECLARE @tableSchema NVARCHAR(128)
DECLARE @tableName NVARCHAR(128)
DECLARE @Statement NVARCHAR(300)
FETCH NEXT FROM updatestats INTO @tableSchema, @tableName
WHILE (@@FETCH_STATUS = 0)
SET @Statement = 'UPDATE STATISTICS ' + '[' + @tableSchema + ']' + '.' + '[' + @tableName + ']' + ' WITH FULLSCAN'
EXEC sp_executesql @Statement
FETCH NEXT FROM updatestats INTO @tableSchema, @tableName
SET NOCOUNT OFF
Windows Virtual Desktop is the new Azure desktop and app virtualization service running in the cloud. With simplefied management, multisession Windows 10, optimizations for Office 365 using FSLogix in the background. With this cloud managed VDI environment, you can build, deploy and scale your virtual desktops and apps in minutes.
If you’re still hosting RDS servers with virtual desktops and apps, and you want to migrate to the cloud, you definitely need to look in to Windows Virtual Desktop.
In the coming month I will be writing a manual how to set it up, and where to think about.
Today I was browsing the Azure Management portal and discovered that Microsoft Azure released a new virtual machine series based on the AMD EPYC 7452V processors that can achieve a boosted 3.35Ghz. With these new AMD processers there are 4 new series available in Azure: Dasv3-series, Dav3-series, Easv3 and the Eav3-series
Same performance, lower price!
With almost the same performance as the DSv3 and Dv3 series Intel virtual machines, these machines might be an interesting choice, especially if we do a price comparison, just 2 examples:
Price per month
If we look at the above pricing, there is a 234% price difference between AMD and Intel. I know, its not a perfect 1 on 1 comparison, but for the same price, you get the double amount of cores, and memory…
With an increased security and privacy in mind Microsoft has been working on private links to Azure resources. Azure Private Link is a secure way to consume Azure Services like Azure SQL and Azure Storage using a private connection in your own VNet. This will replace the need for IaaS hosted virtual machines with SQL Server or the file server role installed.
Azure Private Link brings Azure services inside the customer’s private VNet. The service resources can be accessed using the private IP address just like any other resource in the VNet. It is basically an NIC inside one of your VNET’s. This will allow all traffic to flow over the internal network, and will not go over the internet. There is no need to put gateways or any other network devices in place to make this happen.
With Azure Conditional access you get more control over your data, get better security and visibility! To use this feature you will need to buy and assign Azure AD Premium or EM+S E3/E5 licenses to your users.
This manual can be used to enforce the use of the Outlook app on IOS and Android devices by blocking all apps that do not support Modern Authentication like iOS mail and Google mail client.
Step 1: In the Azure Portal go to Conditional Access. On the first page that you get create a New policy
If you deployed Intune to your mobile devices, you want to enforce the use of the Outlook app on the mobile device. We want to make the end user experience as smooth as possible and preconfigure Outlook for the. How can we prepare the Outlook app with your company email settings? With just a few steps, we can get this setup!
Step 1: From the Azure Portal go to Intune –> Clients Apps –> App configuration policies and click Add
Step 2: Give the configuration policy a name and description. Select Device Enrollment type, my preferred method is to use Managed apps, because this will deploy the policy to both enrolled and unenrolled devices. Select the Outlook apps on Associated app, and go to Configuration settings.
Microsoft has announced the availability of the new Azure data-centers in Switzerland. With 2 data-centers in Switzerland, Zurich and Geneva, Azure has created a full region (West and North)
Microsoft worked together with several Swiss companies as early adopters to improve cloud adoption in Switzerland. As this region is fairly new it might take some time before all Azure and Office 365 services are available.
If you would like to start deploying resources in Azure, it might be that you don’t have access yet. During the initiation phase it is required to request access before you can start utilizing resources in Switzerland. Request access to Azure Switzerland
Tom Keane, Corporate Vice President, Microsoft Azure:
Today, we’re announcing the availability of Azure from our new cloud regions in Switzerland. These new regions and our ongoing global expansion are in response to customer demand as more industry leaders choose Microsoft’s cloud services to further their digital transformations. As we enter new markets, we work to address scenarios where data residency is of critical importance, especially for highly regulated industries seeking the compliance standards and extensive security offered by Azure.
For very high demanding workloads, storage wise, Azure has released Ultra Disk performance tier for production use. I’ve already written about it in a previous post ( Slow IOPS in Azure VM’s? not anymore!) But now is the time to take a deeper look.
Which disk types do we have in Azure?
In the following table you can see what the difference is between all disk types in Azure. This table should help you to decide which disk to use for specific workloads.