SSMS 21/22 Error Upload BACPAC file to Azure Storage
Hello All In my SSMS 20, I can use "Export Data-tier Application" to export an BACPAC file of Azure SQL database and upload to Azure storage in the same machine, the SSMS 21 gives error message when doing the same export, it created the BACPAC files but failed on the last step, "Uploading BACPAC file to Microsoft Azure Storage", The error message is "Could not load file or assembly 'System.IO.Hashing, Version=6.0.0.0, Culture=neutral, PublicKeyToken=cc7b13ffcd2ddd51' or one of its dependencies. The system cannot find the file specified. (Azure.Storage.Blobs)" I tried the fresh installation of SSMS 21 in a brand-new machine (Windows 11), same issue, Can anyone advice? Thanks
Setting up SQL Managed Instance Link to an Availability Group (Updated September 2025)
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 there are some differences required to ensure a failover does not break the connection. Please note that Managed Instance Link can only be created with an availability group with a single database. 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 the AG nodes Enabling trace flags on the AG nodes (Optional) Testing network connectivity between SQL Managed Instance and the AG Create certificates on the AG nodes Import AG certificate public keys to SQL Managed Instance Import the certificate public key of the SQL Managed Instance to AG nodes Import Azure-trusted root certificate authority keys to AG nodes Alter the mirroring endpoint on AG nodes Create a distributed availability group on AG Set up the managed Instance Link Create a database master key Firstly, a master encryption key is required on all nodes of the AG if it does not already exist. The script below will not return any rows if it does not exist. -- Run on SQL Server USE master; GO SELECT * FROM sys.symmetric_keys WHERE name LIKE '%DatabaseMasterKey%'; If no rows are returned, the following query can be used. Keep a 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 AG nodes (Optional) To optimise the performance of the link, we recommend enabling the trace flags T1800 and T9567 on all nodes. Instructions on this are here and will require a restart of the service. Testing network connectivity between SQL Managed Instance and the AG Instructions on how to run the connectivity tests can be found here, and can be done by SSMS GUI or T-SQL. For these tests, the SQL Managed Instance will need to be able to connect to the Mirror endpoint using the IP of the availability group. The SQL Managed Instance IPs can can change (for example during maintenance). For that reason NSG and firewall rules should allow access to ports 5022 and 11000-11999 for the whole subnet range of the SQL Managed Instance. Create certificates on AG nodes The below query will need to be run on all AG nodes. Pay particular attention to the @cert_expiry_date variable to ensure it is a date in the future. It will need to be replaced after expiry. -- 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 SQL Managed Instance The public keys of the AG certificates need to be imported into the SQL Managed Instance. The below query will return the data required for the next step. -- 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; Example result The next step will need to be run in PowerShell and can be run through Azure Cloud Shell. Below is an example script to log in to Azure. # 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 Replace the values of $CertificateName, $PublicKeyEncoded and $ManagedInstanceName and then run this. # 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 node values. Import the certificate public key of SQL Managed Instance to AG nodes Using the same PowerShell session, run this script to get the public key of the 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</managedinstancename> On all nodes of the AG run the following query, replacing the certificate name and the value of the PublicKey with the output from the previous step. -- Run on SQL Server USE MASTER CREATE CERTIFICATE [<managedinstancefqdn>] FROM BINARY = <publickey></publickey></managedinstancefqdn> Import Azure-trusted root certificate authority keys to AG nodes Use the steps here to import the Microsoft PKI root-authority certificate and DigiCert PKI root-authority certificate onto all nodes of the AG. Alter the mirroring endpoint on AG nodes The mirroring endpoint on the AG nodes will also need to be updated to allow the Managed Instance to authenticate with the 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. Change CREATE ENDPOINT to ALTER ENDPOINT and add this command after AUTHENTICATION = WINDOWS NEGOTIATE CERTIFICATE, changing the cert_name value. CERTIFICATE [cert_name] Once updated, execute the query. For multiple Availability groups on the same instance, the next section would need to be repeated for each one. Create a distributed availability group on the AG (running against the primary server) Use this script to set up the distributed availability group, updating the sections in angle brackets (<>). The SQLServerIP will need to be the IP of the listener of the availability group. -- 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</managedinstancename></managedinstancefqdn></agnameonsqlmi></endpointport></sqlserverip></agnameonsqlserver></dagname> Set up the managed Instance Link The final step is to set up the link, the script for this is here. Please ensure that the $SQLServerIP is the IP of the AG listener and not the IP of the primary node. Once this is executed, the MI Link should be up and running and failing over to another node on the AG will not stop 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 September 2025. I hope this article was helpful for you, please feel free to share your feedback in the comments section.
SQL Server 2022 Express Install Issues
I am a non tech savvy student trying to install SQL Server 2022 Express for class and I am running into issues, looking for help. It gets all the way through the install process and gives me "Unable to install SQL Server (setup.exe) Exit code (Decimal): -2061893606Error description: Wait in the Database Engine recovery handle Failed. Check the SQL Server error log for potential causes" and C:\Program Files\Microsoft SQL Server\160\Setup Bootstrap\Log\20250903_162628 I have fully uninstalled anything related to SQL and reinstalled multiple times with no success. I reset windows, no luck. Any help would be appreciated.Using Copilot (GPT-5) to turn schema into SQL and write queries
This article shows a few ways you can use Copilot to help with Microsoft Graph Data Connect for SharePoint. This includes turning a schema into a SQL CREATE TABLE statement and summarizing data from that. Make sure to use the Copilot option to use GPT-5 for best results. Scenario 1 This first scenario just builds a CREATE TABLE statement from the dataset schema definition in markdown notation. I provide a few instructions on how to handle the flattening of the objects. Me: Given the schema at this URL: https://github.com/microsoftgraph/dataconnect-solutions/blob/main/Datasets/data-connect-dataset-sharepointsites.md, give me a CREATE TABLE statement for a SQL Server. Just include the single SQL command in the output. Each column should have a comment with a short description. Don't include any information about keys or constraints. This dataset is flattened. One column in the table for every row, except those of type object. Column names to be flattened with a prefix. Use an underline to separate the prefix. Copilot: CREATE TABLE SharePointSites ( ptenant NVARCHAR(255) -- Id of the tenant ,Id NVARCHAR(255) -- GUID of the site ,Url NVARCHAR(2048) -- URL for the site ,ArchiveState NVARCHAR(50) -- Archive state of the site: None, Archiving, Archived, or Reactivating ,SiteArchivedBy NVARCHAR(255) -- Who archived the site, null if not archived ,SiteArchivedTime DATETIME -- When the site was archived, null if not archived ,RootWeb_Id NVARCHAR(255) -- Root web id ,RootWeb_Title NVARCHAR(512) -- Root web title ,RootWeb_WebTemplate NVARCHAR(255) -- Root web template name ,RootWeb_WebTemplateId INT -- Root web template id ,RootWeb_Configuration INT -- Root web template configuration id ,RootWeb_LastItemModifiedDate DATETIME -- Date when an item in the root web was last modified ,WebCount BIGINT -- Number of webs (subsites) in the site ,StorageQuota BIGINT -- Total storage in bytes allowed for this site ,StorageUsed BIGINT -- Total storage in bytes used by this site ,StorageMetrics_MetadataSize BIGINT -- Total metadata size for the site in bytes ,StorageMetrics_TotalFileCount BIGINT -- Total number of files for the site ,StorageMetrics_TotalFileStreamSize BIGINT -- Total size of latest version of files in bytes ,StorageMetrics_AdditionalFileStreamSize BIGINT -- Total size of additional file streams in bytes ,StorageMetrics_TotalSize BIGINT -- Total size of all files for the site in bytes ,GroupId NVARCHAR(255) -- Id of the group associated with this site ,GeoLocation NVARCHAR(50) -- Geographic region where the data is stored ,IsInRecycleBin BIT -- Indicates if the site is in the recycle bin ,RecycleBinItemCount BIGINT -- Number of items in the recycle bin ,RecycleBinItemSize BIGINT -- Size of items in the recycle bin ,SecondStageRecycleBinStorageUsage BIGINT -- Size of items in the second stage recycle bin ,IsTeamsConnectedSite BIT -- Indicates if the site is connected to Teams ,IsTeamsChannelSite BIT -- Indicates if the site is a channel site ,TeamsChannelType NVARCHAR(50) -- Type of channel if site is a Teams channel site ,IsHubSite BIT -- Indicates if the site is associated with a hub site ,HubSiteId NVARCHAR(255) -- Id of the hub site for this site ,IsCommunicationSite BIT -- Indicates if the site is a communication site ,IsOneDrive BIT -- Indicates if the site is a OneDrive ,BlockAccessFromUnmanagedDevices BIT -- Site blocks access from unmanaged devices ,BlockDownloadOfAllFilesOnUnmanagedDevices BIT -- Site blocks download of all files from unmanaged devices ,BlockDownloadOfViewableFilesOnUnmanagedDevices BIT -- Site blocks download of viewable files from unmanaged devices ,ShareByEmailEnabled BIT -- Site enables share by e-mail ,ShareByLinkEnabled BIT -- Site enables share by link ,IsExternalSharingEnabled BIT -- Indicates if external sharing is enabled ,SiteConnectedToPrivateGroup BIT -- Indicates if site is connected to a Private Group ,Privacy NVARCHAR(50) -- Privacy of the site: Private or Public ,SensitivityLabelInfo_Id NVARCHAR(255) -- Id of the Sensitivity Label for the site ,SensitivityLabelInfo_DisplayName NVARCHAR(255) -- Display name of the Sensitivity Label for the site ,Classification NVARCHAR(255) -- Classification of the site ,IBMode NVARCHAR(50) -- Information Barriers Mode ,IBSegments NVARCHAR(MAX) -- List of organization segments if IB mode is Explicit ,Owner_AadObjectId NVARCHAR(255) -- AAD Object Id of the owner ,Owner_Email NVARCHAR(320) -- Email of the owner ,Owner_UPN NVARCHAR(320) -- User Principal Name of the owner ,Owner_Name NVARCHAR(255) -- Name of the owner ,SecondaryContact_AadObjectId NVARCHAR(255) -- AAD Object Id of the secondary contact ,SecondaryContact_Email NVARCHAR(320) -- Email of the secondary contact ,SecondaryContact_UPN NVARCHAR(320) -- User Principal Name of the secondary contact ,SecondaryContact_Name NVARCHAR(255) -- Name of the secondary contact ,ReadLocked BIT -- Whether the site is locked for read access ,ReadOnly BIT -- Whether the site is in read-only mode ,CreatedTime DATETIME -- When the site was created (UTC) ,LastSecurityModifiedDate DATETIME -- When security on the site was last changed (UTC) ,LastUserAccessDate DATETIME -- Last access by a real user (UTC) ,LastContentChange DATETIME -- When the site contents were last changed (UTC) ,SnapshotDate DATETIME -- When this site information was captured (UTC) ,Operation NVARCHAR(50) -- Extraction mode of this row ); Scenario 2 Now I use the CREATE TABLE as the base to ask for a SELECT statement. Me: Give me a list of sites that have more than 5GB of files. Give me a single SQL statement using the tables described below. <create table statements included here> Copilot: SELECT Id AS SiteId, Url AS SiteUrl, RootWeb_Title AS SiteTitle, StorageMetrics_TotalSize AS TotalSizeInBytes, CAST(StorageMetrics_TotalSize / (1024.0 * 1024 * 1024) AS DECIMAL(10,2)) AS TotalSizeInGB FROM SharePointSites WHERE StorageMetrics_TotalSize > (5 * 1024 * 1024 * 1024) ORDER BY StorageMetrics_TotalSize DESC; Note: Interesting choice of columns and order, without any specific prompting to that effect. Scenario 3 Now for a more challenging scenario using two tables and a more complex requirement. Me: Given the tables below, write a SELECT statement to give me a list of sites with the name of the site, the total storage size and the top 3 file extensions by size. <create table statements for Sites and Files included here> Copilot: WITH ExtensionTotals AS ( SELECT f.SiteId, f.Extension, SUM(f.SizeInBytes) AS TotalSize FROM SharePointFiles f WHERE f.Extension IS NOT NULL GROUP BY f.SiteId, f.Extension ), RankedExtensions AS ( SELECT et.SiteId, et.Extension, et.TotalSize, ROW_NUMBER() OVER (PARTITION BY et.SiteId ORDER BY et.TotalSize DESC) AS rn FROM ExtensionTotals et ), TopExtensions AS ( SELECT SiteId, STRING_AGG(Extension + ' (' + CAST(TotalSize AS VARCHAR(20)) + ')', ', ') WITHIN GROUP (ORDER BY TotalSize DESC) AS Top3Extensions FROM RankedExtensions WHERE rn <= 3 GROUP BY SiteId ) SELECT s.RootWeb_Title AS SiteName, s.StorageMetrics_TotalSize AS TotalStorageSize, te.Top3Extensions FROM SharePointSites s LEFT JOIN TopExtensions te ON s.Id = te.SiteId ORDER BY s.StorageMetrics_TotalSize DESC; Notes: Scenario is nicely handled, using both the Files and Sites tables. Complex aggregation is coded correctly. Scenario 4 In this one, I skip the step of using a SQL CREATE TABLE statement and go directly from the markdown for the two schemas (Sites and Files) to a SQL SELECT statement. I also let GPT-5 add his comments and notes this time, to make sure it's thinking correctly... Me: Consider a SQL table for SharePoint Sites based on the schema at https://github.com/microsoftgraph/dataconnect-solutions/blob/main/Datasets/data-connect-dataset-sharepointsites.md - This dataset is flattened. There is one column in the SQL table for every row, except those of type object. Column names are flattened with a prefix. Column names use an underline to separate the prefix. Also consider a SQL table for SharePoint Files based on the schema at https://github.com/microsoftgraph/dataconnect-solutions/blob/main/Datasets/data-connect-dataset-sharepointfiles.md - Use the same assumptions as the previous one. Now write a SELECT statement to give me a list of sites with the name of the site, the total storage size and the top 3 file extensions by size. Copilot: Got it — you want to join the flattened SharePoint Sites table with the flattened SharePoint Files table, aggregate storage usage, and then find the top 3 file extensions by size for each site. Here’s a SQL example that works with the flattened column naming convention you described: WITH FileSizeByExtension AS ( SELECT f.SiteId, f.Extension, SUM(f.SizeInBytes) AS TotalExtensionSize FROM SharePointFiles f GROUP BY f.SiteId, f.Extension ), RankedExtensions AS ( SELECT SiteId, Extension, TotalExtensionSize, ROW_NUMBER() OVER (PARTITION BY SiteId ORDER BY TotalExtensionSize DESC) AS rn FROM FileSizeByExtension ), Top3Extensions AS ( SELECT SiteId, STRING_AGG(Extension, ', ' ORDER BY TotalExtensionSize DESC) AS TopExtensions FROM RankedExtensions WHERE rn <= 3 GROUP BY SiteId ) SELECT s.RootWeb_Title AS SiteName, s.StorageMetrics_TotalSize AS TotalStorageBytes, t.TopExtensions FROM SharePointSites s LEFT JOIN Top3Extensions t ON s.Id = t.SiteId ORDER BY s.StorageMetrics_TotalSize DESC; How it works FileSizeByExtension: Groups files by SiteId and Extension to sum their sizes. RankedExtensions: Uses ROW_NUMBER() to rank extensions by size within each site. Top3Extensions: Filters to the top 3 per site and concatenates them into a comma-separated list. Final SELECT: Joins back to the Sites table to get the site name and total storage size. Notes RootWeb_Title comes from flattening the RootWeb object in the Sites dataset. StorageMetrics_TotalSize is the flattened column for total site storage (excluding recycle bin). STRING_AGG works in SQL Server 2017+, Azure SQL, and some other RDBMS. If your SQL dialect doesn’t support it, you’d need a different aggregation method. This query orders sites by total storage size so the largest sites appear first. Note: Nicely done in one prompt with the proper explanations. Conclusion I hope these examples help you see the potential of getting Copilot's help with your queries. This is one more reason to load your MGDC for SharePoint tables directly into Microsoft Fabric, where you can run SQL queries against tables in the OneLake...
To call a VBA script from a T-SQL procedure
I have the following (unusual) task: I need my T-SQL procedure to somehow call a VBA script. The VBA should copy all content of a certain sheet of an input Excel file into a certain sheet of an output Excel file. The physical full path of the input Excel file is available only in the T-SQL procedure. Therefore, the procedure should somehow pass the file path into the VBA script. How can I do that?
adding computed column to MS SQL
would like to check how to add a computed column with the following requirement Column value will be C-yyyyMMdd-0001 where date will be from the CreatedDate column in the same table. 0001 is an auto-incremental value based on when the record is inserted on the same day eg, the first record added on 23/08/2025, the computed column will be C-20250823-0001. As the table may already contain existing data, a SQL script may also be necessary to update this new column with the correct value.
