Increase storage size of Azure SQL Managed Instance

If you need to increase the storage space available to a managed instance – it has to be done via code as the option to edit a managed instance is not available in the Portal.

I recently increased a size of a managed instance from 320GB to 512GB and it took around 2 minutes to complete. I used the cloud shell available in the portal.

$subId = "my_subscription_id"
$resourceGroup = "resourceGroup1"
$instanceName = "managed-instance-1"

Select-AzureRmSubscription -SubscriptionId $subId

$size = 512
Set-AzureRmSqlInstance -Name $instanceName -ResourceGroupName $resourceGroup -StorageSizeInGB $size

Cores, the Admin password and a few other parameters can also be changed in the same manner. See the set-azurermsqlinstance documentation.

Restoring into an Azure Managed Instance from SQL 2014

We are in the process of setting up our azure environment, and using managed instances is something that lets us efficiently move from an on premise legacy world to a similar setup inside Azure. I’ve just completed my first migration of moving from SQL Server 2014 On premise, onto a SQL Managed Instance running SQL Azure Database V12. There are several functional steps that need to be followed succintly to complete the migration of data which i’ll try to cover off below.

Creating an Azure Storage Container

Similar to S3, Azure has storage accounts which can store multiple blob containers (folders). The general heirarchy of this is:

  --Resource Group
    --Storage Account
      --Storage Container

The Storage Account can be set to only allow certain ranges of IP’s (IP Whitelist) to access the data, and for best practice this should be set.

The blob storage should be set to type private to prevent anonymous access – depending on what is being stored, but for databases i’d say this should be the default position.

Backup a SQL 2014 DB to Azure Storage Blob

To be able to talk to the blob storage, we need to provide a credential for SQL Server to use, which has a login access key to the storage account.

USE master 
CREATE CREDENTIAL [backups] -- name of the credential 
WITH IDENTITY='my_storage_account' -- storage account name. 
, SECRET = 'one_of_the_two_storage_keys_for_the_account' -- this is the access key token 

NOTE: If you try to use a Shared Access Signature (SAS) on SQL Server 2014, it will fail to decode the signature when trying to use it. Follow this document to create a SQL 2014 Credential

Error if using SAS on SQL 2014:

Msg 3298, Level 16, State 2, Line 11
Backup/Restore to URL device error: Error while decoding the storage key.
Msg 3013, Level 16, State 1, Line 11
BACKUP DATABASE is terminating abnormally.

To see the credentials that are in place on SQL Server:

select * from sys.credentials

At, this point we can start referencing the credential to backup to a URL based location:

TO URL = 'https://<storage_account><storage_container>/mydatabase.bak'

Restore a SQL 2014 Backup to SQL Azure Managed Instance

To be able to allow a SQL Azure database to talk to blob storage, we need to provide a credential using a Shared Access Signature (SAS).

To create a SAS, you need to generate a signature from within the Storage Account in the azure portal. Set an expiry on the SAS to give some added security.

NOTE: Remember to remove the intial ? from the generated SAS key otherwise the key will be invalid.

USE master 
CREATE CREDENTIAL [https://<storage_account><storage_container>] -- this name must match the container path, start with https and must not contain a trailing forward slash. 
WITH IDENTITY='SHARED ACCESS SIGNATURE' -- this is a mandatory string and do not change it. 
, SECRET = 'sas_key' -- this is the shared access signature token 

Run the restore:

FROM URL = 'https://<storage_account><storage_container>/mydatabase.bak'

NOTE: Notice that there are no WITH clauses in the restore as these are not supported in SQL Azure. Normally you would have a STATS clause to monitor the restore, but this can be done either via sys.dm_exec_requests – precent_complete column or by downloading sp_whoIsActive – check out Brent Ozar’s Post on this

If you’re happy with the restored database, then it can be changed to 2017 compatibility if the application for the database supports it.

USE [master]


The remote copy of database x has not been rolled forward to a point in time that is encompassed in the local copy

When setting up database mirroring, it is possible to receieve the following error when starting the mirroring session between the Principal and Mirror databases:

The remote copy of database x has not been rolled forward to a point in time that is encompassed in the local copy

Assuming the steps taken follow a pattern similar to below:

  1. BACKUP database DBMTest on SQLINS01
  2. BACKUP log DBMTest on SQLINS01
  3. Copy db and log backup files to SQLINS02
  4. RESTORE DBMTest with norecovery
  5. RESTORE log DBMTest with norecovery
  6. create endpoints on both SQLINS01 and SQLINS02
    CREATE ENDPOINT [Mirroring]
  7. Enable mirror on MIRROR Server SQLINS02
    :connect SQLINS02
    SET Partner = N'TCP://';
  8. Enable mirror on PRINCIPAL server SQLINS01
    :connect SQLINS01
    SET Partner = N'TCP://';

The error would appear on step 8

Normally the reason for the error is because the backup file for the log or full backup has not been initialized and therefore contains more than one backup of the database on file.

Add the WITH INIT option to the backups to create a fresh initialized backup file

Insufficient access rights to perform the operation when running setspn

When attempting to add an SPN to a service account for SQL Server, you may get the following error if you are not a domain admin:

Checking domain DC=..,DC=....,DC=..,DC=..

Registering ServicePrincipalNames for CN=vsqldev01 svc,OU=Service Accounts,OU=Shared Resources,OU=..,DC=..,DC=
Failed to assign SPN on account 'CN=vsqldev01 svc,OU=Service Accounts,OU=Shared Resources,OU=E..,DC=.,DC=...
error 0x2098/8344 
-> Insufficient access rights to perform the operation.

If your lucky enough, then get your domain admin to give you the required permissinos against the OU in Active Directory. They would need to do the following:

On a Domain Controller, run adsiedit.msc (Doing this via the normal dsa.msc console will not expose the spn permissions that need to be added)

Then run the following sequence of actions:

Right-Click on the OU and select Properties
Select the "Security" tab
Select the "Advanced" tab
 Select the "Add" button
 Enter the security principal name
 security principal
 Properties tab
 Apply to:
 Descendant User objects
 Read servicePrincipalName - Allow
 Write servicePrincipalName - Allow


Set up Windows Auth to a SQL Instance with SQL Ops Studio on Mac OS

Microsoft released a preview of SQL Ops Studio in the last week or two, and as a Mac user I was interested to see how well the interface would work compared to SSMS.

Here is a quick intro to the produce from Microsoft:
More details about Ops Studio can be found here:
Download –

For reference, I am using MacOS Sierra version 10.12.6 and this is what I did to get windows authentication working properly.

If you try to get Windows AD Auth working, you might initially see this error:


The link in the message above takes you to: Connect SQL Operations Studio (preview) to your SQL Server using Windows authentication – Kerberos

There are 3 main areas to configure before Windows Auth to a SQL Instance works.

Service Principal Names (SPN)

A service principal name (SPN) is a unique identifier of a service instance. SPNs are used by Kerberos authentication to associate a service instance with a service logon account. This allows a client application to request that the service authenticate an account even if the client does not have the account name.

As an Example. on a SQL Clustered instance called VSQLDEV01, you can check if the SPN is set by running the following in a command prompt/ps terminal:

setspn -L VSQLDEV01

If it doesnt return a row that contains the following:


MSSQLSvc/FQDN:port | MSSQLSvc/FQDN, where:

  • MSSQLSvc is the service that is being registered.
  • FQDN is the fully qualified domain name of the server.
  • port is the TCP port number.

You can add an SPN to register the service account by doing the following:


Get Key Distribution Center (KDC) and join Domain

The KDC is usually just the FQDN of your Domain Controller – fairly straightforward to find out via the nltest command on a windows machine:

 nltest /dsgetdc:DOMAIN.COMPANY.COM
Configure KDC in krb5.conf on your mac

Edit the /etc/krb5.conf in an editor of your choice. Configure the following keys

sudo vi /etc/krb5.conf

  default_realm = DOMAIN.COMPANY.COM

   kdc =

Then save the krb5.conf file and exit

Test Granting and Retreiving a ticket from the KDC

On your Mac, run the following in a terminal:

kinit username@DOMAIN.COMPANY.COM

Authenticate and then check the ticket has been granted:

Credentials cache: API:9999E999-99CA-9999-AC9C-99A999999D99
Issued                Expires               Principal
Mar  8 07:55:10 2018  Mar  8 17:55:01 2018  krbtgt/DOMAIN@DOMAIN

Hopefully, if the intruction are followed, then you should be ready to go!

Make a connection using Windows Auth via SQL Ops Studio.

To test a connection has authenticated via KERBEROS, you can check in sql once a connection is made:

FROM sys.dm_exec_connections
WHERE auth_scheme = 'KERBEROS'

Should return your connected session:


Check compressed database backup ratio

If running compressed backups natively in SQL Server, then you can check teh ratio of compressed using the following script:

SELECT backup_size/compressed_backup_size 'Ratio', backup_size/1024/1024/1024 'Original Size GB', compressed_backup_size/1024/1024/1024 'Compressed Size GB', 
backup_start_date, backup_finish_date FROM msdb..backupset
where database_name = 'Database_Name'
and backup_size/compressed_backup_size > 1
order by backup_start_date desc;

Setting up SQL Server vNext CTP 2.0 on Docker

Recently I moved into the brave new world of having a Macbook pro as my main laptop, and one of my first frustrations was the inability to be able to run SQL Server Management Studio locally. Unfortunately Microsoft haven’t come up with a solution for that one, so I’m having to do with a mix of SQLPro Studio and running SSMS inside a VDI machine on the mac.

One thing the Mac has made easier for me is the ability to start testing SQL Server vNext locally and without too much messing around. So when Microsoft released vNext 2.0 on the 19th April 2017, it seemed like a good opportunity to give it a try. (Whats new in SQL Server Linux)

This post should cover what you need to do to get a docker image of SQL Server running on macOS Sierra. The Microsoft documentation is pretty good on this subject, and not convoluted but I always find it good to blog how exactly I’ve done things, as there are always little quirks.

Install Docker for Mac

I downloaded the stable version just to be a bit safer and so that I wouldn’t introduce any unnecessary pinch points.

Once installed, Start the Docker application – it may take a few minutes to initialise, but eventually you will end up with the docker ship icon in the top bar.

There are a few minimum requirements for running docker for the SQL Server image:

  • Docker Engine 1.8+ on any supported Linux distribution or Docker for Mac/Windows.
  • Minimum of 4 GB of disk space
  • Minimum of 4 GB of RAM

If your downloading a recent release of docker then the engine shouldn’t be an issue.

At the time of writing, my version is:

$ docker version
Client:Version	17.03.1-ce
API Version: 1.27
Go Version: go1.7.5

Version: 17.03.1-ce
API Version: 1.27 (minimum version 1.12)
OS/Arch: linux/amd64

Docker default starts with 2GB of RAM, but this can be easily changed to 4GB from the docker preferences in the advanced section.

Pull down and run the latest SQL Server Docker Image – Step by Step details here –

Quick Steps here:

sudo docker pull microsoft/mssql-server-linux

Note that I have added a –name switch to the docker command below. This is to simplify things once we have everything up and running as the container name is quite critical for more or less every command in docker.

sudo docker run --name SQL2017 -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=' -p 1433:1433 -d microsoft/mssql-server-linux

To make sure we have successfully created our container, we can run:

docker ps -a

To familiarise yourself with other docker commands, you can run:

docker --help

Connect to the SQL Server

sudo docker exec -it SQL2017 /bin/bash

The flags are for the following:

-i, –interactive Keep STDIN open even if not attached–privileged Give extended privileges to the command-t, –tty Allocate a pseudo-TTY

This then takes you into the interactive command line for the docker image.

The folder structure by default is /var/opt/mssql for the database files & error logs. Sqlcmd is in /opt/mssql-tools/bin/

Reading the error log

While in exec mode for the SQL2017 container, run the following cat command:

cat /var/opt/mssql/errorlog

There is a very good guide by microsoft on how to troubleshoot SQL Server Linux which can be found here –

Using sqlcmd

Starting with SQL Server vNext CTP 2.0, the SQL Server command-line tools are included in the Docker image. If you attach to the image with an interactive command-prompt (as I have done above), you can run the tools locally.

First step is to add the sqlcmd path to the $PATH environment variable. This step isn’t critical but makes sqlcmd accessible from any location on the docker image

PATH=$PATH:/opt/mssql-tools/binecho 'export PATH="$PATH:/opt/mssql-tools/bin"'
root@5214e1df3c86:/opt/mssql-tools/bin# echo $PATH
sqlcmd -S . -U sa -P
1&gt; select name from sys.databases
2&gt; go

Installing SQLCMD on macOS

/usr/bin/ruby -e "$(curl -fsSL <a href="" data-mce-href=""></a>)"
brew tap microsoft/mssql-preview <a href="" data-mce-href=""></a> 
brew update 
brew install mssql-tools #for silent install ACCEPT_EULA=y 
brew install mssql-tools


Can be quickly tested locally by opening a terminal window and running:

sqlcmd -S my-remote-server -U test -P test
1&gt; select @@SERVERNAME
2&gt; go
(1 rows affected)

Connecting into the docker image from host machine

Get the IP of the container

MCR-AL33450:/mohsin.alipatel$ docker inspect --format "{{ .NetworkSettings.Ports}}" SQL2017
map[1433/tcp:[{ 1433}]]

Then make a sqlcmd connection using the IP and port above

MCR-AL33450:/mohsin.alipatel$ sqlcmd -S,1433 -U sa -P
1&gt;Select @@servername
(1 rows affected)

This should provide a basic introduction to both docker and to SQL server on linux.

Cannot execute as the database principal because the principal "##MS_PolicyEventProcessingLogin##"

Following error continuously in the agent log after restoring the msdb database

The activated proc '[dbo].[sp_syspolicy_events_reader]' running on queue 'msdb.dbo.syspolicy_event_queue' output the following: 'Cannot execute as the database principal because the principal "##MS_PolicyEventProcessingLogin##" does not exist, this type of principal cannot be impersonated, or you do not have permission.'

This is normally down to an orphaned login for the ‘##MS_PolicyEventProcessingLogin##’ login

To fix, you can first make sure if an orphaned login is in fact the cause:

USE master
SELECT * FROM sys.database_principals 
WHERE [name] = '##MS_PolicyEventProcessingLogin##'
USE msdb
SELECT * FROM sys.database_principals 
WHERE [name] = '##MS_PolicyEventProcessingLogin##'

This should show that the SID for the login in the master database is different from the SID in the msdb database


Then run the following to correct the orphaned login:

use [master]
exec sp_change_users_login 'Auto_Fix', '##MS_PolicyEventProcessingLogin##';
use [msdb]
exec sp_change_users_login 'Auto_Fix', '##MS_PolicyEventProcessingLogin##';

By rerunning the check, it should show that the SID is now the same in both database_principals tables

USE master
SELECT * FROM sys.database_principals 
WHERE [name] = '##MS_PolicyEventProcessingLogin##'
USE msdb
SELECT * FROM sys.database_principals 
WHERE [name] = '##MS_PolicyEventProcessingLogin##'


To clean up, cycle the agent error log

use msdb