Steven Wright Blog

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

I want to share the approach that I lead, with the application developers, in optimising a feature within our application. This feature was the search functionality, which almost every application has and gives SQL Server database a bad reputation, that finds data by selected attribute(s) and limits the rows output based upon the application rows and page selectors. The problems were not the usual lousy T-SQL code approach to optional search predicates: 1 2 where ( table .column1 = @pColum1PredicateValue Or @pColum1PredicateValue is null ) AND ( table .column2 = @pColum2PredicateValue Or @pColum2PredicateValue is null ) The main problem was with the implementation of the store procedure to build the dynamic T-SQL string. The string was initialised with a select statement, containing all fields required for the application output, and extra table joins to cover all possible predicates. The where clause only had the predicates that were select by the user. Another issue was the