{"id":11334,"date":"2018-06-13T05:43:19","date_gmt":"2018-06-13T03:43:19","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/introducing-sql-managed-instances-on-azure\/"},"modified":"2023-07-17T16:43:20","modified_gmt":"2023-07-17T14:43:20","slug":"introducing-sql-managed-instances-on-azure","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/introducing-sql-managed-instances-on-azure\/","title":{"rendered":"Introducing SQL managed instances on Azure"},"content":{"rendered":"

I never wrote about data platform solutions on Azure so far. The fact is in Switzerland we\u2019re definitely late about the Cloud adoption and Azure data platform solutions. There are different reasons that are more or less valid but I don\u2019t want to contribute to any debate here. In any case the last announcements<\/a> in this field with Azure data centers in Switzerland could encourage customers to reconsider this topic in the near future. Don\u2019t get me wrong here, it doesn\u2019t mean that customers in Switzerland must move all their database infrastructures in the Cloud but this is just an opportunity for them to consider rearchitecting some pieces of their information system including databases that may lead to hybrid scenarios. It will be likely the first step to the cloud adoption for data platforms solutions. At dbi services we didn\u2019t exclude Azure from our target but we just silently continued to keep an eye on the data platform stack awaiting the right opportunity to move up a gear.<\/p>\n

\"blog<\/p>\n

Why to begin with SQL managed instances (MI)? After all, this feature is still on preview and it already exists a lot of Azure solutions as Azure SQL Databases (singleton and with elastic pool as well) as well as SQL Server on Azure VMs.<\/p>\n

The point is this new\u00a0feature is interesting in many aspects. Firstly, it will address the gap that currently exists between IaaS infrastructures (SQL Server VMs) and fully-managed services (with Azure SQL DBs). The former still requires maintaining the operating system (and licenses) while the latter didn\u2019t expose all the feature surface<\/a> needed by various application scenarios.<\/p>\n

At the same time, Microsoft introduced another purchasing model that based on VCore<\/a>. I remembered a discussion with one of my customers sometimes ago about DTUs. He asked me what is exactly DTU and I pointed out the following sentence from the Microsoft documentation<\/a>.<\/p>\n

The amount of resources is calculated as a number of Database Transaction Units or DTUs and is a bundled measure of compute, storage, and IO resources<\/em><\/strong><\/p>\n

That is definitely a good way to simply resource management because it makes an abstraction of the physical resources but this is probably it weakness in some degrees. Indeed, how to translate what DBAs and infrastructure team usually manage from on-premises to the cloud? Obviously, Microsoft provided a calculator<\/a> to help customers to address their questions before moving to the cloud but the fact is database administrators seem to not be comfortable to deal with DTUs . But now let\u2019s talk about flexibility: in many databases scenarios we don\u2019t want to increase \/ decrease resources in the same bundle but we want to get a better control of the resource configuration by dissociating compute (CPU \/ RAM) from the storage. From my experience, I had never seen one customer to scale compute and storage in the same manner regarding their workload. Indeed, some workloads require high-performance storage while others are more CPU-bound. This is where the new vCore-based model<\/a> comes into play by and I believe it will get a better adoption from customers to smoothly move to the cloud. That\u2019s at least my opinion!<\/p>\n

So, let\u2019s try to play with MI. As a reminder, currently it is in preview but that\u2019s enough to get a picture of what you may expect in the future. In my demo, I will use\u00a0intensively\u00a0CLI tools with dedicated PowerShell cmdlets and mssql-cli as well. This is voluntary because the fact is more and more administration tasks are done\u00a0in this way and Microsoft provided all the commands to achieve them.<\/p>\n

[dab@DBI-LT-DAB:#]> Get-AzureRmResourceGroup -Name sql-mi-rg\n\n\nResourceGroupName : sql-mi-rg\nLocation          : westeurope\nProvisioningState : Succeeded\nTags              :\nResourceId        : \/subscriptions\/913528f5-f1f8-4d61-af86-30f2eb0839ba\/resourceGroups\/sql-mi-rg\n\n[dab@DBI-LT-DAB:#]> Get-AzureRmResource -ResourceGroupName sql-mi-rg | ft Name, ResourceType, Location -AutoSize\n\nName                                                    ResourceType                             Location\n----                                                    ------------                             --------\nsql-mi-client_OsDisk_1_842d669310b04cbd8352962c4bda5889 Microsoft.Compute\/disks                  westeurope\nsql-mi-client                                           Microsoft.Compute\/virtualMachines        westeurope\nshutdown-computevm-sql-mi-client                        Microsoft.DevTestLab\/schedules           westeurope\nsql-mi-client453                                        Microsoft.Network\/networkInterfaces      westeurope\nsql-mi-client-nsg                                       Microsoft.Network\/networkSecurityGroups  westeurope\nsqlmiclientnsg675                                       Microsoft.Network\/networkSecurityGroups  westeurope\nsql-mi-client-ip                                        Microsoft.Network\/publicIPAddresses      westeurope\nsqlmiclientip853                                        Microsoft.Network\/publicIPAddresses      westeurope\nsql-mi-routetable                                       Microsoft.Network\/routeTables            westeurope\nsql-mi-vnet                                             Microsoft.Network\/virtualNetworks        westeurope\nsql-mi-dbi                                              Microsoft.Sql\/managedInstances           westeurope\nsql-mi-dbi\/ApplixEnterprise                             Microsoft.Sql\/managedInstances\/databases westeurope\nsql-mi-dbi\/dbi_tools                                    Microsoft.Sql\/managedInstances\/databases westeurope\nVirtualClustersql-mi-subnet                             Microsoft.Sql\/virtualClusters            westeurope\nsqlmirgdiag947                                          Microsoft.Storage\/storageAccounts        westeurope<\/pre>\n
<\/pre>\n
My MI is composed of difference resources including:<\/p>\n