QTip: Getting timeouts and exceptions using Azure SQL DB Query Store
Requirements: Azure SQL Database SQL Server Management Studio (SSMS) Tables testcodes and testinvoice used for this demo 1-Configure Query Store to collect data and save as soon as possible to be able to review it * Data flush interval is the time to send information to disk ** Statistics collection interval is the time range to collect data , query store is not collecting one record by execution is collecting statistics of each execution and you can get max, min, avg but not excecution by excecution *** Query store capture mode ALL will collect information of all executions query store will not make any kind of filter 2-Create table testcodes CREATE TABLE [dbo].[testcodes]( [Code] [char](5) NULL, [Description] [nchar](10) NULL ) ON [PRIMARY] GO 3-Create table testinvoice CREATE TABLE [dbo].[testinvoice]( [code] [char](5) NULL, [pieces] [int] NULL ) ON [PRIMARY] GO 4-Add some codes to be used 5-Add some records to invoice Exception ... 6-Run query to get information from invoices and get description from codes select *,description=(select testcodes.Code from testcodes where testcodes.code=invoice.code) from testinvoice invoice Is possible to see execution without error 7-Now add in codes a second code BBB to force exception 8-Run query from point 6 again Result is an exception 9-Run query to see queries with exceptions or timeouts starting 2 days ago (declare @datestart as datetime = dateadd(D,-2,getdate());) declare @datestart as datetime = dateadd(D,-2,getdate()); declare @datefinish as datetime = getdate(); /* if you want to set to specific time */ --set @datestart = '2025-04-09 00:00:00'; --set @datefinish = '2025-04-09 23:59:59'; select rs.last_execution_time, rs.execution_type_desc, qt.query_sql_text, q.query_id, CONVERT(VARCHAR(1000), q.query_hash, 1) as strqueryhash, p.plan_id, rs.last_cpu_time, rs.last_duration, rs.count_executions, rs.last_rowcount, rs.last_logical_io_reads, rs.last_physical_io_reads, rs.last_query_max_used_memory, rs.last_tempdb_space_used, rs.last_dop, p.is_forced_plan, p.last_force_failure_reason, p.last_force_failure_reason_desc FROM sys.query_store_query_text AS qt JOIN sys.query_store_query AS q ON qt.query_text_id = q.query_text_id JOIN sys.query_store_plan AS p ON q.query_id = p.query_id JOIN sys.query_store_runtime_stats AS rs ON p.plan_id = rs.plan_id where rs.last_execution_time>= @datestart and rs.last_execution_time<=@datefinish and (rs.execution_type=3 or rs.execution_type=4) -- 3 timeout, 4 error --and qt.query_sql_text like '%actual%' --and q.query_hash=0x009C458D20394C37 --and p.plan_id=12 ORDER BY rs.last_execution_time DESC 10-In query there are some commented lines that you can use to add more filters or modify them To see all records comment line 20 declare @datestart as datetime = dateadd(D,-2,getdate()); declare @datefinish as datetime = getdate(); /* if you want to set to specific time */ --set @datestart = '2025-04-09 00:00:00'; --set @datefinish = '2025-04-09 23:59:59'; select rs.last_execution_time, rs.execution_type_desc, qt.query_sql_text, q.query_id, CONVERT(VARCHAR(1000), q.query_hash, 1) as strqueryhash, p.plan_id, rs.last_cpu_time, rs.last_duration, rs.count_executions, rs.last_rowcount, rs.last_logical_io_reads, rs.last_physical_io_reads, rs.last_query_max_used_memory, rs.last_tempdb_space_used, rs.last_dop, p.is_forced_plan, p.last_force_failure_reason, p.last_force_failure_reason_desc FROM sys.query_store_query_text AS qt JOIN sys.query_store_query AS q ON qt.query_text_id = q.query_text_id JOIN sys.query_store_plan AS p ON q.query_id = p.query_id JOIN sys.query_store_runtime_stats AS rs ON p.plan_id = rs.plan_id where rs.last_execution_time>= @datestart and rs.last_execution_time<=@datefinish --and (rs.execution_type=3 or rs.execution_type=4) -- 3 timeout, 4 error --and qt.query_sql_text like '%actual%' --and q.query_hash=0x009C458D20394C37 --and p.plan_id=12 ORDER BY rs.last_execution_time DESC Now you can see all records 11-Reproduce error several times within 1 minute to see value in execution count (number of executions inside of statistics collection interval) Timeout ... 12-Configure command timeout different to 0 for this demo use 10 (seconds) in parameters previous to connect Second option 12-Use query below that will run 1000 times and at some point will fail INSERT INTO [dbo].[testinvoice] SELECT * FROM [dbo].[testinvoice] GO 1000 13-Run query from point 9 to see data in query store Now you can reproduce and get data about all excecutions , exceptions and timeouts good luck!
Setting up SQL Managed Instance Link to an Availability Group
On a recent case, a customer was trying to set up SQL Managed Instance Link to partner with an on premise Always On Availability Group (AG). Using the public documentation will work, but this will only be active against the primary node and a failover will cause the database on the managed instance side to stop syncing. This post will be using steps from the following documents. Prepare your environment for a link - Azure SQL Managed Instance Configure link with scripts - Azure SQL Managed Instance The steps to set this up are below Create a database master key on your AG nodes Enabling trace flags on your AG nodes (Optional) Testing network connectivity between your SQL Managed Instance and the AG Create certificates on your AG nodes Import AG certificate public keys to your SQL Managed Instance Import the certificate public key of your SQL Managed Instance to your AG nodes Import Azure-trusted root certificate authority keys to your AG nodes Alter the mirroring endpoint on your AG nodes Create a distributed availability group on your AG Set up the managed Instance Link Create a database master key You first need to create a master encryption key on all nodes of your AG if it does not already exist. You can check if it exists by running this query. -- Run on SQL Server USE master; GO SELECT * FROM sys.symmetric_keys WHERE name LIKE '%DatabaseMasterKey%'; If no results are returned, run this query, keep note of the passwords in a confidential and secure place. -- Run on SQL Server -- Create a master key USE master; GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<strong_password>'; Enabling trace flags on your AG nodes (Optional) To optimise the performance of your link, we recommend enabling the trace flags T1800 and T9567 on all of your nodes. Instructions on this are here and will require a restart of the service. Testing network connectivity between your SQL Managed Instance and the AG Instructions on how to run the connectivity tests can be found here, and can be done by SSMS or T-SQL. For these tests, your SQL Managed Instance will need to be able to connect to your Mirror endpoint using the IP of your availability group. All nodes of your AG will need to be able to connect to ports 5022 and 11000-11999 on the subnet of your SQL Managed Instance. Create certificates on your AG nodes On each node, run the query below to create a certificate. You can alter your @cert_expiry_date variable to a date that suits you. -- Create the SQL Server certificate for the instance link USE MASTER -- Customize SQL Server certificate expiration date by adjusting the date below DECLARE @cert_expiry_date AS varchar(max)='03/30/2025' -- Build the query to generate the certificate DECLARE @sqlserver_certificate_name NVARCHAR(MAX) = N'Cert_' + @@servername + N'_endpoint' DECLARE @sqlserver_certificate_subject NVARCHAR(MAX) = N'Certificate for ' + @sqlserver_certificate_name DECLARE @create_sqlserver_certificate_command NVARCHAR(MAX) = N'CREATE CERTIFICATE [' + @sqlserver_certificate_name + '] ' + char (13) + ' WITH SUBJECT = ''' + @sqlserver_certificate_subject + ''',' + char (13) + ' EXPIRY_DATE = '''+ @cert_expiry_date + ''''+ char (13) IF NOT EXISTS (SELECT name from sys.certificates WHERE name = @sqlserver_certificate_name) BEGIN PRINT (@create_sqlserver_certificate_command) -- Execute the query to create SQL Server certificate for the instance link EXEC sp_executesql @stmt = @create_sqlserver_certificate_command END ELSE PRINT 'Certificate ' + @sqlserver_certificate_name + ' already exists.' GO Import AG certificate public keys to your SQL Managed Instance The public keys of your AG certificates need to be imported into your SQL Managed Instance. Run this query on each node to get the data required -- Run on SQL Server -- Show the name and the public key of generated SQL Server certificate USE MASTER GO DECLARE @sqlserver_certificate_name NVARCHAR(MAX) = N'Cert_' + @@servername + N'_endpoint' DECLARE @PUBLICKEYENC VARBINARY(MAX) = CERTENCODED(CERT_ID(@sqlserver_certificate_name)); SELECT @sqlserver_certificate_name as 'SQLServerCertName' SELECT @PUBLICKEYENC AS SQLServerPublicKey; You will get a result similar to below You can now import these into your SQL Managed Instance using Azure Cloud Shell. Log in using this script, replacing . # Run in Azure Cloud Shell (select PowerShell console) # Enter your Azure subscription ID $SubscriptionID = "<SubscriptionID>" # Login to Azure and select subscription ID if ((Get-AzContext ) -eq $null) { echo "Logging to Azure subscription" Login-AzAccount } Select-AzSubscription -SubscriptionName $SubscriptionID Then run this script, replacing the values of $CertificateName, $PublicKeyEncoded and $ManagedInstanceName. # Run in Azure Cloud Shell (select PowerShell console) # =============================================================================== # POWERSHELL SCRIPT TO IMPORT SQL SERVER PUBLIC CERTIFICATE TO SQL MANAGED INSTANCE # ===== Enter user variables here ==== # Enter the name for the server SQLServerCertName certificate – for example, "Cert_sqlserver1_endpoint" $CertificateName = "<SQLServerCertName>" # Insert the certificate public key blob that you got from SQL Server – for example, "0x1234567..." $PublicKeyEncoded = "<SQLServerPublicKey>" # Enter your managed instance short name – for example, "sqlmi" $ManagedInstanceName = "<ManagedInstanceName>" # ==== Do not customize the below cmdlets==== # Find out the resource group name $ResourceGroup = (Get-AzSqlInstance -InstanceName $ManagedInstanceName).ResourceGroupName # Upload the public key of the authentication certificate from SQL Server to Azure. New-AzSqlInstanceServerTrustCertificate -ResourceGroupName $ResourceGroup -InstanceName $ManagedInstanceName -Name $CertificateName -PublicKey $PublicKeyEncoded Repeat this step for all AG nodes. Import the certificate public key of your SQL Managed Instance to your AG nodes Using the same Azure Cloud Shell, run this script to get the public key of your SQL Managed Instance certificate, replacing the value of the variable $ManagedInstanceName. # Run in Azure Cloud Shell (select PowerShell console) # =============================================================================== # POWERSHELL SCRIPT TO EXPORT MANAGED INSTANCE PUBLIC CERTIFICATE # ===== Enter user variables here ==== # Enter your managed instance short name – for example, "sqlmi" $ManagedInstanceName = "<ManagedInstanceName>" # ==== Do not customize the following cmdlet ==== # Find out the resource group name $ResourceGroup = (Get-AzSqlInstance -InstanceName $ManagedInstanceName).ResourceGroupName # Fetch the public key of the authentication certificate from Managed Instance. Outputs a binary key in the property PublicKey. Get-AzSqlInstanceEndpointCertificate -ResourceGroupName $ResourceGroup -InstanceName $ManagedInstanceName -EndpointType "DATABASE_MIRRORING" | out-string On all nodes of your AG you can then run this query, replacing with the value of the PublicKey output in the previous step. -- Run on SQL Server USE MASTER CREATE CERTIFICATE [<ManagedInstanceFQDN>] FROM BINARY = <PublicKey> Import Azure-trusted root certificate authority keys to your AG nodes Use the steps here to import the Microsoft PKI root-authority certificate and DigiCert PKI root-authority certificate onto all nodes of your AG. Alter the mirroring endpoint on your AG nodes The mirroring endpoint on your AG nodes will also need to be updated to allow your Managed Instance to authenticate with your AG using the newly created certificate. This can be done by navigating to Server Objects > Endpoints > Database Mirroring. Right click on the endpoint and select Script Endpoint as > CREATE To > New Query Editor Window. You will need to add this command under AUTHENTICATION, changing the cert_name value and change the CREATE ENDPOINT to ALTER ENDPOINT. CERTIFICATE [cert_name] Once updated, execute the query. Create a distributed availability group on your AG Use this script to set up the distributed availability group, updating the values - Your choice of name - AG Name already created : - The IP of your AG listener and the endpoint listener port - Your choice of name - FQDN of your instance - Just the instance name -- Run on SQL Server -- Create a distributed availability group for the availability group and database -- ManagedInstanceName example: 'sqlmi1' -- ManagedInstanceFQDN example: 'sqlmi1.73d19f36a420a.database.windows.net' USE MASTER CREATE AVAILABILITY GROUP [<DAGName>] WITH (DISTRIBUTED) AVAILABILITY GROUP ON N'<AGNameOnSQLServer>' WITH ( LISTENER_URL = 'TCP://<SQLServerIP>:<EndpointPort>', AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, FAILOVER_MODE = MANUAL, SEEDING_MODE = AUTOMATIC, SESSION_TIMEOUT = 20 ), N'<AGNameOnSQLMI>' WITH ( LISTENER_URL = 'tcp://<ManagedInstanceFQDN>:5022;Server=[<ManagedInstanceName>]', AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, FAILOVER_MODE = MANUAL, SEEDING_MODE = AUTOMATIC ); GO Set up the managed Instance Link The final step is to set up the link, you can do this by running the script here. Please ensure that the $SQLServerIP is the IP of your AG listener and not the IP of the primary node. The in the link includes updated parameters from Az.Sql 6.0.0. This version is not currently available in Azure Cloud Shell. If you are using Azure Cloud Shell, replace the final section of the command... ...with the below New-AzSqlInstanceLink -InstanceName $ManagedInstanceName -Name $DAGName -PrimaryAvailabilityGroupName $AGNameOnSQLServer -ResourceGroupName $ResourceGroup -SecondaryAvailabilityGroupName $AGNameOnSQLMI -SourceEndpoint $SourceIP -TargetDatabase $DatabaseName Once this is executed, your MI Link should be up and running and failing over to another node on your AG will not stop your MI Link from syncing. Disclaimer Please note that products and options presented in this article are subject to change. This article reflects MI Link and the documentation in January 2025. I hope this article was helpful for you, please feel free to share your feedback in the comments section.SQL MI Restore with valid SAS key failing with Operating System Error 86
We will provide details about a workaround for resolving the "Operating System Error 86" encountered during database restoration from Azure Storage on SQL Managed Instance. In this scenario, the Storage Account was not behind firewall and the credential already had a valid SAS token.
Azure SQL Database idle sessions are killed after about 30 minutes when "Proxy" connection policy
Let's see how the connection policy affects the idle sessions on our Azure SQL Database connections. As we will see, the Azure SQL Gateways will kill idle sessions after 30 minutes when "Proxy" connection policy is set.How to Change Collation of an Azure SQL Managed Instance When There Are Dependency Errors
In this article, I will explain how to change the collation for your Azure SQL Managed Instance when the following scenario is encountered: You try to change the database collation with the following T-SQL: USE [master] GO ALTER DATABASE [testdb] COLLATE SQL_Latin1_General_CP1_CS_AS GO And it fails with the following message: Msg 5075, Level 16, State 1, Line 24 The column 'tablex.colx' is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation. Steps in brief: Export the database to bacpac using SQLPackage. Modify the collation by editing the model.xml file. Import the database using SQLPackage and overriding the model.xml path. Change the database name or modify app connection string to use the new database. Considerations: Schedule a maintenance window for your application during the process and stop the workload to prevent data loss. Ensure you have the latest SQLPackage version: Download and install SqlPackage - SQL Server | Microsoft Learn Review the different connection strings depending on the connection method outlined in these examples: SqlPackage Export - SQL Server | Microsoft Learn This article applies to Azure SQL DB and Azure SQL Managed Instance. Steps in details: Start the maintenance window for your application. Export the database using SQLPackage: sqlpackage.exe /Action:Export /ssn:tcp:<ServerName>.database.windows.net,1433 /sdn:<DatabaseName> /su:<UserName> /sp:<Password> /tf:<TargetFile> Rename the *.bacpac file to *.zip so you can open it with the file explorer: Copy the model.xml to a local folder “C:\Temp\model.xml”. Edit the “C:\Temp\model.xml” with the desired collation and save the file. For example: <Property Name="Collation" Value="Thai_CI_AS" /> Rename the file extension modified in step 3 from *.zip back to *.bacpac. Run the import using sqlpackage.exe. Use the flag /ModelFilePath:C:\Temp\model.xml to override the model.xml in the .bacpac file. sqlpackage.exe /Action:Import /tsn:<server>.database.windows.net /tdn:<database> /tu:<user> /tp:<password> /sf:"C:\Temp\database.bacpac" /ModelFilePath:<path>model.xml When the import operation is completed, change the database name, or modify the application connection string to use the new database name. You can also check the new collation setting in the database properties using SSMS: Stop the maintenance window for your application and run the workload. Ensure that the application is working as expected.Export Azure SQL Database | Advanced Scenarios
Introduction: Export Azure SQL Database is a common request for Azure SQL DB customers, in this article we are going to list down some advanced scenarios, on how this can be achievable through various tools not limited to Azure Portal, Azure CLI and PowerShell. In addition, this article will provide alternative methods when it comes to private endpoints and deny public access. Scenarios: In this section, we are going through the scenarios and provide a thoughtful insight on each one. Note: - Import Export using Private Link now in public review, more information at blog article: Import Export using Private Link now in Preview - Microsoft Tech Community Export via Azure Portal to Storage Account This can be a seamless solution to do the database export when the SQL server allows the public access, untoggled the Deny public access option on SQL DB Azure portal, otherwise you might get error like: An unexpected error was returned by the SQL engine while preparing the operation inputs. Error: 47073, State: 1. To overcome such error, you can TEMPORARY set deny public access to NO during the export operation. Note: - You don’t need to worry, if you set “Deny public access” to “No” it doesn’t mean that everyone will be able to connect from outside; you still can restrict the access using the database firewall. You can find more information at: Connectivity settings for Azure SQL Database and Azure Synapse Analytics - Azure SQL Database and Azure Synapse Analytics | Microsoft Docs Export via REST API You can use Export REST API to export the database, this can be done programmatically, or from tools like Postman, Also you can try this from Azure Documentation using the >try it button, More information can be found at: Databases - Export - REST API (Azure SQL Database) | Microsoft Docs Here is an example using postman: Request Body: { "storageKeyType": "StorageAccessKey", "storageKey": "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx= =", "storageUri": https://xxxxxxxxxxxxxxxxx.blob.core.windows.net/testc, "administratorLogin": "xxxxxxxxxxxx", "administratorLoginPassword": "xxxxxxxxxxxxxx", "authenticationType": "Sql", "networkIsolation": { "sqlServerResourceId": "/subscriptions/xxxxxxxxxxxxx/resourceGroups/customer/providers/Microsoft.Sql/servers/xxxxxxxxx", "storageAccountResourceId": "/subscriptions/xxxxxxxxxxx/resourceGroups/customer/providers/Microsoft.Storage/storageAccounts/xxxxxxxxx" } } Below error may occur if the Deny public access is enabled, the solution is to enable the public access temporarily {"error":{"code":"ResourceNotFound","message":"The specified resource 'https://management.northeurope.control.database.windows.net/modules/AzureResourceManager.dsts/subscriptions/<yoursubscriptionid>/resourceGroups/customer/providers/Microsoft.Sql/servers/<servername>/databases/<dbname>/export?api-version=2021-02-01-preview ' was not found."}} Note:- networkisolation setting, this feature is currently under development and not ready for public consumption. More information can be found at: New-AzSqlDatabaseExport with network isolation · Discussion #13937 · Azure/azure-powershell · GitHub Error when calling New-AzSqlDatabaseExport with UseNetworkIsolation on $true · Issue #13964 · Azure/azure-powershell · GitHub Export via SQLPackage This can be a best bet solution for many scenarios to overcome limitations on the database size and also to export SQL DB via private endpoint through a VM running in the same VNET. Note:- you can export to local disk or Azure File Share, but you cannot use Azure Blob, for details can be found at Lesson Learned #25: Export/Import Azure SQL Database using Azure File Service? - Microsoft Tech Community Therefore, you can export the .bacpac locally/File share on the VM in the same VNET as the private endpoint of the SQL Server using SQLPackage.exe/SSMS then copy the bacpac to Azure blob (if required). For example: Using SQLPackage to import or export SQL Server and Azure SQL DB - Microsoft Tech Community Export via SQL server Management Studio : Export using SSMS from the VM running in the same VNET as a private endpoint from SQL to blob storage/ file share You can make use SQL Server Management Studio Export data-tier application wizard to export the Azure SQL database to a .bacpac file. The .bacpac can be stored into Azure blob storage or file share. Right click on the SQL Database on logical SQL Server from SSMS --> Tasks --> Select 'Export data-tier application' wizard. Select the location to store the BACPAC file You can select the subset of the tables from export setting in Advance tab --> Click Next to view the summary of export. One you click finish. And up on completion of the process you will be able to view the BACPAC file in the specified destination. More information at blog: Using data-tier applications (BACPAC) to migrate a database from Managed Instance to SQL Server - Microsoft Tech Community Export via Powershell/ CLI The New-AzSqlDatabaseExport cmdlet can be used to export database request to the Azure SQL Database service. Make a note that you have to enable public access to export the database via this method. With Deny public access set to YES, you might encounter below error. PowerShell command to export the database. Command to export the database via PS : New-AzSqlDatabaseExport -ResourceGroupName "customer" -ServerName "<your server name>" -DatabaseName "<your db name>" -StorageKeyType "StorageAccessKey" -StorageKey "<your storage access key>" -StorageUri "https://xxxxxxxxxxxxxxxxx.blob.core.windows.net/testc/database01.bacpac" -AdministratorLogin "<your login name>" To check the status of the export request, use the Get-AzSqlDatabaseImportExportStatus cmdlet. Get-AzSqlDatabaseImportExportStatus -OperationStatusLink https://management.azure.com/subscriptions/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxx/providers/Microsoft.Sql/locations/northeurope/importExportOperationResults/xxxxxxx-xxxxxxx?api-version=2021-02-01-preview Use the Database Operations - Cancel API or the PowerShell Stop-AzSqlDatabaseActivity command to cancel an export request. Stop-AzSqlDatabaseActivity -ResourceGroupName $ResourceGroupName -ServerName $ServerName -DatabaseName $DatabaseName -OperationId $Operation.OperationId - Please make a note of some of the considerations when using PowerShell method. Also check Azure SQL CLI at: az sql db | Microsoft Docs - Check out: How to cancel Azure SQL Database Import or Export operation - Microsoft Tech Community Database Copy You can use copy database from Azure portal to copy the database to the different server, then perform the export to Azure Blob, later on you can clean up the copied database The database export can also be done via automation, more information can be found at Blog: How to automate Export Azure SQL DB to blob storage use Automation account - Microsoft Tech Community Video: SQL Insider Series: Exporting Azure SQL DB BACPAC file to Azure with Azure Automation | Data Exposed - YouTube Additional References: Export a database to a BACPAC file - Azure SQL Database & Azure SQL Managed Instance | Microsoft Docs Using Azure Import/Export to transfer data to and from Azure Storage | Microsoft Docs Configure Azure Storage firewalls and virtual networks | Microsoft Docs Connectivity settings for Azure SQL Database and Azure Synapse Analytics - Azure SQL Database and Azure Synapse Analytics | Microsoft Docs Automate native database backup of Azure SQL Managed instance to Azure blob storage - Microsoft Tech Community Disclaimer Please note that products and options presented in this article are subject to change. This article reflects the database export options available for Azure SQL database in February, 2022. Closing remarks We hope you find this article helpful. If you have any feedback, please do not hesitate to provide it in the comment section below. Abhishek Shaha (Author) Ahmed Mahmoud (Co-Author)LicenseType parameter when you scale your Azure SQL Managed Instance
When you try to scale your Azure SQL Managed Instance and you face the following error message: "Configured pricing tier and vCore values require greater number of licenses than previous instance configuration. Please submit operation again with specified value for licenseType property." you have to review your License type.
