Steven Wright Blog

As mention in a previous post, we are using Azure SQL Server IaaS to help our transition to the cloud. The main reason for this is how some of our SSIS projects, which we inherited, were created. Some were using SSIS to manage the transactions, which uses MSDTC not supported in Azure. Use extra .Net libraries to read JSON and office files; external programs to do file operations like decompression. Migration timelines and priorities we didn't have time to refactor these projects. While ago we started to deploy our SSIS projects to our Azure SQL Server IaaS virtual machine, a Windows 2019 server with SQL Server 2019, to get the following error: SSIS Deployment Error 6522 "A required privilege is not held by the client". We able to resolve our problem by using the steps we found in the following blog posts: System.ComponentModel.Win32Exception: A required privilege is not held by the client while Deploying SSIS Project | Microsoft Docs . Fixing SQL 2012 SSIS Deployment Err

In this post I would like to highlight that approach that you can take to resolve the following error that you might get after restoring your on-prem SQL Server database(s) to Azure SQL Server IaaS: Cannot resolve the collation conflict between We were getting the error because the Azure IaaS SQL Server default collation is: SQL_Latin1_General_CP1_CI_AS. Our on-prem default is Latin1_General_CP1_CI_AS. And our SQL code wasn’t setting an explicit collation for joins between temp tables and physical database table for character columns. For the UK SQL Server install the server collation default is Latin1_General_CP1_CI_AS when the windows host language and location are set as English United kingdom. We also didn’t have time to go through all our SQL code to find where we need to add explicit collation. The decision was to change SQL Server default collation to Latin1_General_CP1_CI_AS To set the SQL Server collation to default can be done using this command on the Azure VM: .\ C :

As mention in a previous post: A Gotcha when using the Azure SQL Server IaaS.   I am working on moving our company product to the Azure Cloud and having to use Azure SQL Server IaaS. In this post, I will share how I used the Azure VM Custom Script Extensions to map a drive to Azure file share accessible for SQL Server services. I couldn’t use the Azure portal script:  1 2 3 4 5 6 7 8 9 $connectTestResult = Test-NetConnection -ComputerName mystorageaccount.file.core.windows.net -Port 445 if ($connectTestResult.TcpTestSucceeded) { # Save the password so the drive will persist on reboot cmd.exe /C "cmdkey /add:`"mystorageaccount.file.core.windows.net`" /user:`"Azure\mystorageaccount`" /pass:`"mystorageaccount.file.core.windows.net key `"" # Mount the drive New-PSDrive -Name Z -PSProvider FileSystem -Root "\\mystorageaccount.file.core.windows.net\myfileshare" -Persist } else { Write-Error -Message "Unable to r

I am currently working on moving our company product to Azure Cloud.  In this post, I am going to share a gotcha I came across with our Azure SQL Server IaaS. For our product, we have to use Azure SQL Server IaaS for a few reasons:  The way our SSIS projects have been developed like: using DTC,  Office drivers and file operations; We have a few cross-database queries within our Store procedures; A fair bit of automation using SQL Server Agent. I have been helping to create our Azure infrastructure using Terraform and ARM templates to build our Azure SQL Server IaaS.  Getting our SQL Server made was reasonably straightforward.  The gotcha was when we looked at the results: The SQL Server agent was installed but not started and the start-up for the service set as manual.  As mentioned, we use SQL Server agent to automation a few processes within our product; this wasn't the desired state also I must admit I have forgotten that this is SQL Server Agent default install method. To have