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:

Subscription
  --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 
GO

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:

BACKUP DATABASE mydatabase 
TO URL = 'https://<storage_account>.blob.core.windows.net/<storage_container>/mydatabase.bak'
WITH CREDENTIAL = 'backups'

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>.blob.core.windows.net/<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 
GO

Run the restore:

RESTORE DATABASE mydatabase 
FROM URL = 'https://<storage_account>.blob.core.windows.net/<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]
GO
ALTER DATABASE [mydatabase] SET COMPATIBILITY_LEVEL = 140
GO

 

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]
    STATE=STARTED
    AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
    FOR DATA_MIRRORING (ROLE = PARTNER)
  7. Enable mirror on MIRROR Server SQLINS02
    :connect SQLINS02
    ALTER DATABASE DBMTest
    SET Partner = N'TCP://SQLINS01.mydomain.com:5022';
  8. Enable mirror on PRINCIPAL server SQLINS01
    :connect SQLINS01
    ALTER DATABASE DBMTest
    SET Partner = N'TCP://SQLINS02.mydomain.com:5022';

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:

setspn -S MSSQLSvc/VSQLDEV01.DOMAIN DOMAIN\SVCACCOUNT.SVC
Checking domain DC=..,DC=....,DC=..,DC=..

Registering ServicePrincipalNames for CN=vsqldev01 svc,OU=Service Accounts,OU=Shared Resources,OU=..,DC=..,DC=
...,DC=..,DC=..
 MSSQLSvc/VSQLDEV01.DOMAIN
Failed to assign SPN on account 'CN=vsqldev01 svc,OU=Service Accounts,OU=Shared Resources,OU=E..,DC=.,DC=...
,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
  Ok
 Properties tab
 Apply to:
 Descendant User objects
 Permissions:
 Read servicePrincipalName - Allow
 Write servicePrincipalName - Allow
  Ok
 Ok
Ok

 

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: https://www.youtube.com/watch?v=s5DopE7ktwo
More details about Ops Studio can be found here: https://docs.microsoft.com/en-us/sql/sql-operations-studio/what-is
Download – https://docs.microsoft.com/en-us/sql/sql-operations-studio/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:

dbamohsin-opsstudio-connectionerror

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/VSQLDEV01.<DOMAIN.COMPANY.COM>

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:

setspn -A MSSQLSvc/VSQLDEV01.DOMAIN.COMPANY.COM DOMAIN\SQLSERVICEACC

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

[libdefaults]
  default_realm = DOMAIN.COMPANY.COM

[realms]
DOMAIN.COMPANY.COM = {
   kdc = dc-33.domain.company.com
}

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
        Principal: ADUSER@DOMAIN.COMPANY.COM
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:

SELECT 
session_ID, 
connect_time, 
net_transport, 
protocol_type, 
auth_scheme 
FROM sys.dm_exec_connections
WHERE auth_scheme = 'KERBEROS'

Should return your connected session:

Auth_scheme

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;