managed instance
7 TopicsAzure SQL Managed Instance Next-gen: Severe Slowdowns During Backups
We recently upgraded our Azure SQL Managed Instance from legacy General Purpose to Next-gen General Purpose. Afterward, automatic full backups caused severe application slowdowns. I’m sharing our experience so others can evaluate this before upgrading. We had used legacy General Purpose for more than three years without SQL performance problems. We never paid attention to when the automatic backups ran because they had no noticeable impact. After upgrading, we experienced three consecutive Thursday afternoon slowdowns during full backups, severe enough that multiple customers asked whether we were having an outage. The important difference is how storage performance is allocated. On legacy General Purpose, each database file receives IOPS and throughput based on its allocated size. On Next-gen, IOPS are configured at the instance level, and the allocated IOPS also determine throughput. Our configuration is 4 vCores with 832 GB of reserved storage, primarily supporting one large production database. Its data file is approximately 703 GiB. Under legacy General Purpose, that file falls in the published tier providing 5,000 IOPS and 210 MB/s. After upgrading, the default Next-gen allocation was 2,496 IOPS, calculated as three IOPS per GB of reserved storage. Using Microsoft’s throughput formula of IOPS divided by 30, that provides approximately 83.2 MB/s shared across the instance. In other words, we went from an allowance of approximately 210 MB/s for our main data file alone to approximately 83 MB/s for the instance. Our backups typically run near 100 MB/s, so the reduced allowance left no room for application traffic and caused severe slowdowns. Reverting to legacy restored normal application performance. This will not necessarily affect every workload the same way. Instances with smaller databases may benefit from the different allocation model. Next-gen also allows additional IOPS to be purchased. However, Microsoft’s positioning of the upgrade as having no additional cost needs an important qualification: the upgrade itself may leave the bill unchanged, but maintaining comparable storage performance can require additional spending. Moreover, purchasing additional Next-gen IOPS alone would not restore our previous throughput allowance. Our 4-vCore Next-gen configuration has a maximum throughput of 145 MB/s, regardless of how many additional IOPS we purchase, compared with approximately 210 MB/s for our main data file on legacy. Reaching comparable throughput would therefore require increasing the vCore count as well. In our case, reverting restored acceptable performance, so we plan to remain on legacy for now. Before upgrading, compare your existing per-file IOPS and throughput allowances with the proposed Next-gen instance-wide allocation. Include automatic full backups in that assessment, even if ordinary application traffic is light and CPU utilization is low. The IOPS allocation rules are documented, but we did not find a clear warning that upgrading with the default settings could substantially reduce available throughput for an existing workload and turn previously unobtrusive backups into a major performance event. If anyone from Microsoft is reading, please make this tradeoff explicit in the upgrade guidance. An upgrade assessment that compares existing file-level allowances with the proposed instance-level limits would help customers avoid discovering this in production.95Views1like0CommentsSSIS & Azure SQL Managed Instance with MFA
We've been testing out Azure SQL Managed Instance and created SSIS packages to run against it. We set connections using the ADO.Net connection manager and all worked well. We recently had to apply MFA to all logins and this is causing issues within SSIS as there is no MFA option for logins like there is in SSMS. We can connect if we use SQL Server Authentication but we'd rather not do that. When using Active Directory Integrated Authentication, all of our test connections fail and the package will not run locally. In SSMS, there is an integrated authentication with MFA option and that works from within SSMS. How can we use SSIS and connect to the database using Windows Authentication when MFA is required?604Views0likes0CommentsAzure SQL Managed Instance Business Critical Tier, Transactional Replication, and failing over
Hi all, My company is planning a migration from SQL Server onprem to Managed Instance on Azure, using the business critical tier with zone-redundancy for HA. We currently have a peer-to-peer replication setup where our data services team updates data on a second read/write node that is separate from our main application read/write node, and the replication keeps everything in sync. My question is: on Azure if we have a second read/write MI (for our data team) syncing with the main business-critical tiered MI and there is a failover there to one of the secondary read-only replicas, what is the impact on the transactional replication to the data team MI? Could it break? Or would it now just sync with the new primary read/write node? Thanks.664Views0likes0CommentsODBC Linked Server in Managed Instance
Hi, Peoples I have a Managed Instance that I access via a VM created on Azure. On this same machine I installed the database ODBC driver and registered the connection in ODBC Data Sources. In SQL in Linked Servers I created the connection, but I can't find the data source.5.5KViews0likes3CommentsHow To Read/Write Files from/to Blob Storage with Storage Firewall enabled for Managed Instance
Background Previously, we have discussed about how to read files from blob storage with storage firewall enabled for Azure SQL Database. Please refer to the below online blog for more information. https://techcommunity.microsoft.com/t5/azure-sql-database/how-to-read-files-from-blob-storage-with-storage-firewall/m-p/1404901#M12 When configured firewall rules of storage accounts for Managed Instance, audit logs could no longer be recorded into atorage account, and user will received such email notification as below. "Audit logs for database 'xxxxxxx' on server 'xxxxxxx' are not being recorded in storage account 'xxxxxxx'" There is the option of 'Allow trusted Microsoft service to access this storage account' under storage account firewall configuration page, unfortunately, according to our online document, Managed Instance is not yet considered as trusted service. Refers to https://docs.microsoft.com/en-nz/azure/storage/common/storage-network-security#exceptions Moreover, comparing with SQL Azure, Managed Instance does not support Managed Identity with the error below in the errorlog. Guidance To enable the read/write access to Azure Storage with Firewall turned on, users need to add Managed Instance’s subnet to Storage Account Vnet firewall rules with leveraging the MI subnet delegation and Storage service endpoint. Firstly, users would need to determine which subnet the managed instance has been deployed on. After selecting the specific subnet, change the configuration of Subnet delegation to Managed Instances. Following, users can delegate this subnet to Managed Instance, please wait for approximately one hour, and arrange Storage as a service added to service endpoints. After both of the above steps have been performed successfully, add the configured Vnet/ Subnet to Virtual networks rules of storage account. Using this workaround, users are able to write audit logs to storage account with firewall rules configured. Please note, the above steps could only be performed when the storage account and the managed instance are in the same or paired region. In this case, the Managed Instance was deployed in East Asia, and the storage account is deployed in East US. While adding the configured Vnet/ Subnet to Virtual network rules of the storage account, user would not see any available virtual networks from the list and also portal will show a notification that only virtual networks in 'East US' and 'West US' will be listed, which is paired region. Terminology Behind the Guidance The workaround delegates the specific subnet to Managed Instance, and enables storage service endpoints on this subnet, thus, the subnet will be able to access the storage account through service endpoint. According to our online document, "Subnet delegation enables you to designate a specific subnet for an Azure PaaS service of your choice that needs to be injected into your virtual network. Subnet delegation provides full control to the customer on managing the integration of Azure services into their virtual networks." Refers to https://docs.microsoft.com/en-us/azure/virtual-network/subnet-delegation-overview Using the same terminology, can we access storage account to read files? Before adding the configured Vnet/subnet to storage account, users will have denied access to storage account to read files from a storage account which has configured firewall rules. BULK INSERT CSVtest FROM 'product.csv' WITH ( DATA_SOURCE = 'CSVInsert', Format='CSV' ); Msg 4861, Level 16, State 1, Line 40 Cannot bulk load because the file "product.csv" could not be opened. Operating system error code 5(Access is denied.). After performing the above workaround, users should be able to read files and access the storage account as the subnet of the Managed Instance has been whitelisted. Author: Marlon Jin <marlonj@microsoft.com>; Yvonne Zhou <wenzho@microsoft.com> Please feel free to contact us if any questions.5KViews0likes0Comments