Active Directory, Windows Containers and SQL Server

The problem

Everything appears to be in containers nowadays, even the SQL Server. But still there are mixed environments, people and companies wanting to try containers without going all in. So I was wondering how practical would it be to have .net core services on docker, running in Windows containers connecting to an external, old fashioned SQL Server instance? Also, as it is all in a Windows domain, I’d like to use domain authentication so I don’t have to worry about managing passwords.

Simple isn’t it? Well, it turns not that simple as not everything is on the domain. The SQL Server is, the docker hosts are, but the containers are not.

Additionally there are differences depending on whether you want to run as an independent container, or in docker swarm mode. This blog post focuses on standalone containers, and the swarm mode is covered in the follow-up post.

The quick answer

The good news is that it is not an unreasonable requirement and it has been done before. The solution is to use Group Managed Service Accounts (gMSA) and Credential Spec Files. A number of people have already documented their efforts. Some were more successful than others.

My story

My problem was that I wasn’t able to make it work just by following any single write-up. In fact, for a few days, I was not able to get it going at all. But eventually it happened and here is a step by step description of how I made it work on Windows Server 2016, 1803, 1809 and 2019 as the host OS and 2016, 1803 and 1809 in full and nano options as the container base image. Generally, it is very simple once you know what to do, and more importantly what not to do (more about it later).

Test setup

To test it I have set up a virtual lab environment on Azure with 6 VMs

  • DC – Windows Server 2016 Datacenter acting as a domain controller
  • DB – Windows Server 2016 Datacenter with SQL Server 2017 Developer edition installed
  • DH2016A – Windows Server 2016 Datacenter with Containers (Docker version 18.09.2)
  • DH1803A – Windows Server 1803 with Containers (Docker version 17.06.2-ee-18)
  • DH1809A – Windows Server 1809 with Containers (Docker version 18.09.0)
  • DH2019A – Windows Server 2019 with Containers (Docker version 18.09.1)

I have created a sqlgmsa.local domain and joined all the VMs to it. SQL Server was run using SQLGMSA\SqlServer Managed Service Account without any special permissions.

In the domain I have 2 service accounts SQLGMSA\ServiceA and SQLGMSA\ServiceB. Both have logins on the SQL Server instance. I will be setting some of my containers to connect to the SQL Server as ServiceA and some as ServiceB.

Initially I tested the connectivity from containers build with full base images (standard mrc.microsoft.com/windows/servercore) and using dbatools module to run queries from them to the DB.sqlgmsa.local server. To be able to test nano based images I created two test images containing simple .net core WebAPI service written in C# with two public methods. Calling api/info you can check if the service is running, what system is it running on. Calling api/query/ attempts to open connection to the specified database (or master if the db parameter is not provided) and returns information about the database, the original login and the current user. A simple query

select 
     @@version SqlServer
    ,db_name()  [Database]
    ,current_user CurrentUser
    ,original_login() OriginalLogin
for json path

The test images are available on docker hub and the source code here on github.

PowerShell and AD

In the example I am using PowerShell to manage my active directory. If the commands I use don’t work for you you may be missing the AD modules. To install them add the RSAT-AD-PowerShell windows feature by executing this PowerShell command

Add-WindowsFeature RSAT-AD-PowerShell

Group Managed Service Accounts (gMSA)

Managed Service Accounts where introduced some time ago to reduce overhead associated with managing passwords for service accounts. The Group Managed Service Accounts solve the same problem but unlike MSAs gMSAs can be used across multiple computers.

To start using gMSAs on a domain a KDS Root Key has to be created first. It is the key with which passwords shared between the computers on the domain are protected. If your domain has other MSAs already you will not need to do it again.

To create a KDS Root Key Run I used this PowerShell command on the domain controller

Add-KdsRootKey -EffectiveTime (Get-Date).AddHours(-10)

and then to verify that it has been created

Get-KdsRootKey

Now to create the test service accounts I used the following commands. I was doing it on the DC, but with the right permissions it should be possible to do from any computer on the domain.

New-AdServiceAccount -Name ServiceA -DNSHostName sqlgmsa.local `
   -PrincipalsAllowedToRetrieveManagedPassword "Domain Controllers", "Domain Admins", "CN=DockerHosts,CN=Computers,DC=sqlgmsa,DC=local" `
   -KerberosEncryptionType AES128, AES256

New-AdServiceAccount -Name ServiceB -DNSHostName sqlgmsa.local `
   -PrincipalsAllowedToRetrieveManagedPassword "Domain Controllers", "Domain Admins", "CN=DockerHosts,CN=Computers,DC=sqlgmsa,DC=local" `
   -KerberosEncryptionType AES128, AES256

Where ServiceA and ServiceB are the names of the accounts and "CN=DockerHosts,CN=Computers,DC=sqlgmsa,DC=local" is the distinguished name of the group I have created for the docker hosts.

If you don’t know the exact distinguished name running this command can help

Get-AdGroup -filter { name -like "yourgroupname" }

Now on every docker host all the specific service accounts (2 in my test case) have to be installed so that the host OS can access them.

Install-AdServiceAccount -Identity ServiceA
Install-AdServiceAccount -Identity ServiceB

If there is an error message like this, it means the permissions were not set correctly
Install-AdServiceAccount : Cannot install service account. Error Message: ‘{Access Denied}

Credential Spec file

Docker Credential Spec Files have been created specifically to solve the problem of passing gMSA to containers. They are plain json files with information about the service account. It is possible to create the files manually, but there is module for it. It is documented here but here is a short instruction of how to create get and import the module.

This part needs to be done on every docker host.

# 1. Set TLS1.2 support from PowerShell so the module can be downloaded from github. 
PS C:\Tmp> [Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12

# 2. Download the psm1 file using Invoke-WebRequest
PS C:\Tmp> Invoke-WebRequest "https://raw.githubusercontent.com/MicrosoftDocs/Virtualization-Documentation/live/windows-server-container-tools/ServiceAccounts/CredentialSpec.psm1" -OutFile "CredentialSpec.psm1"

# 3. Import the module
PS C:\Tmp> Import-Module .\CredentialSpec.psm1

With CredentialSpec module imported for each gMSA a credential spec file has to be created.

PS C:\> New-CredentialSpec -Name ServiceA`
  -AccountName ServiceA `
  -Domain $(Get-AdDomain -Current LocalComputer)

PS C:\> New-CredentialSpec -Name ServiceB`
  -AccountName ServiceA `
  -Domain $(Get-AdDomain -Current LocalComputer)

The list of existing files can be obtained with

PS C:\> Get-CredentialSpec

Name     Path
----     ----
ServiceA C:\ProgramData\docker\CredentialSpecs\ServiceA.json
ServiceB C:\ProgramData\docker\CredentialSpecs\ServiceB.json

And finally, run the containers passing the credential spec files with the --security-opt parameter. (This is an example from DH2019A using the 1809 nano base image).

docker run -d -it -p 8001:80 `
   --security-opt "credentialspec=file://ServiceA.json" `
   --name ServiceA `
   michalporeba/sqlgmsatest:1809nano

docker run -d -it -p 8002:80 `
   --security-opt "credentialspec=file://ServiceB.json" `
   --name ServiceB `
   michalporeba/sqlgmsatest:1809nano

The proof is in the pudding

After checking both containers are running with docker ps I can start testing. As the test is not focused on anything else but domain authentication I didn’t open any ports to the lab, and all I was doing was to either connect to the container and use dbatools to execute a query on the db server, or from the docker host connecting to the service listening on the published port. Here are the example calls using Invoke-WebRequest on DH2019A.

sqlgmsa.proof

PS C:\> $env:ComputerName
DH2019A
PS C:\> docker start ServiceA
ServiceA
PS C:\> docker start ServiceB
ServiceB
PS C:\> docker ps
CONTAINER ID        IMAGE                               COMMAND                  CREATED             STATUS              PORTS                           NAMES
c6382bb7d816        michalporeba/sqlgmsatest:1809nano   "dotnet TestService.…"   2 days ago          Up 4 seconds        443/tcp, 0.0.0.0:8002->80/tcp   ServiceB
02ece189cb74        michalporeba/sqlgmsatest:1809nano   "dotnet TestService.…"   2 days ago          Up 8 seconds        443/tcp, 0.0.0.0:8001->80/tcp   ServiceA
PS C:\> # Service A
PS C:\> (Invoke-WebRequest -UseBasicParsing http://localhost:8001/api/info).Content
["OS:  Microsoft Windows 10.0.17763 ","Framework: .NET Core 4.6.27317.07"]
PS C:\> (Invoke-WebRequest -UseBasicParsing http://localhost:8001/api/query/DB.sqlgmsa.local).Content
[{"Database":"master","CurrentUser":"guest","OriginalLogin":"SQLGMSA\\ServiceA$"}]
PS C:\> # Service B
PS C:\> (Invoke-WebRequest -UseBasicParsing http://localhost:8002/api/info).Content
["OS:  Microsoft Windows 10.0.17763 ","Framework: .NET Core 4.6.27317.07"]
PS C:\> (Invoke-WebRequest -UseBasicParsing http://localhost:8002/api/query/DB.sqlgmsa.local).Content
[{"Database":"master","CurrentUser":"guest","OriginalLogin":"SQLGMSA\\ServiceB$"}]
PS C:\>

Conclusions

The above setup really boils down to 5 steps. If you want to use windows authentication from windows containers on docker to a SQL Server instance (or a cluster you have to

  1. Create gMSAs for your services
  2. Create logins for the service accounts on the SQL Server
  3. Install gMSAs on the docker hosts
  4. Create credential spec files
  5. Create containers with --security-opt parameter pointing to the credential spec file.

That’s it. In my case I was able to make it work (for standalone containers, not in swarm mode) on different OS versions (2016, 1803, 1809, 2019) using full and nano base images and using docker 17.06 and 18.09. However, there can be surprises and pulling hair. In the week I spent trying to figure it out I had a number of moments when I thought I’ve got it, just to realise that what worked a moment ago, doesn’t any more.

The biggest lessons where

  • use AD Groups for managing access to gMSAs rather than individual computer accounts,
  • be very careful with Set-AdServiceAccount which I have seen in some of the posts out there,
  • SSPI context errors is not what it seems, and can be very annoying

More details about the lessons learnt can be found in the follow up post.

Trying to do the same but in a service run on Docker in swarm mode is similar, but not exactly the same. I have described it here.

This post is long enough as it is, so I will not go into the details of those lessons learnt here but instead include them in a follow up to which I will link here later.

Backing SQL on Linux to Windows Share

How to back up a database from SQL Server on Linux (perhaps in a Docker container) to a Windows Share already on the network?

If you want to know how to run SQL Server on Linux in a Docker container read my earlier post.

Container run-time privileges and Linux capabilities

Before we start it is important to note that by default the containers are run with very limited privileges and cannot do much. That’s by design, to make them more secure. However, that prevents them from being able to use CIFS protocol to mount to an SMB share, exactly what we are trying to do. If you just follow the steps in the next sections when you try to mount the share you will get

Unable to apply new capability set.

To avoid this problem the container needs to be created with two extra capabilities: SYS_ADMIN and DAC_READ_SEARCH. You can read more about it on the here. But to make the long story short you need to add two --cap-add parameters to your docker run command.

The full command from my earlier post becomes:

docker run -d `
   -e ACCEPT_EULA=Y -e SA_PASSWORD=Secret1234 `
   -p 14333:1433 --name sql1 `
   --cap-add SYS_ADMIN --cap-add DAC_READ_SEARCH ` 
   mcr.microsoft.com/mssql/server:latest

Create a container with those capabilities before continuing.

The Naive Approach

Let’s assume you have a network share already available and it is accessible using UNC \\FileShare1\SqlBackups\. Being used to Windows networking one would expect to simply take a backup like so, for example:

backup database [TestDb] 
   to disk='\\FileShare1\SqlBackups\TestDB.bak'

The command completes with no errors and yet there is no TestDB.bak file in \\FileShare1\SqlBackups. Stranger still it is possible to restore from that file which doesn’t seem to exist. Try:

restore filelistonly 
   from disk='\\FileShare1\SqlBackups\TestDB.bak'

It works, because Linux translates the UNC it knows nothing about (after all it’s a Windows thing) to a local file /FileShare/SqlBackups/TestDB.bak. If you remote to the container (or execute bash on it with docker exec -it sql bash) you will find /FileShare1/SqlBackups/TestDB.bak file.

Interesting, but not what we expected.

Getting into SMB and CIFS

To solve this problem a network share needs to be mounted to a node in the Linux file system. There are two ways to do it, one is temporary using the mount command only, or a more permanent involving editing the fstab file and then using mount. But before we can do it, one problem has to be solved first. The Windows file server shares the folder using the SMB protocol which is gibberish to Linux. Luckily Linux can be taught to speak CIFS protocol which is compatible with SMB. The way to do it is to install the cifs-utils package. On Ubuntu (SQL Server vNext in a container runs on Ubuntu so I use it as an example) is to install the package with apt-get. Other flavours of the OS will use their own package managers, but overall the process is the same.

First, you will have to Connect to SQL Server on Linux.

Then the CIFS protocol utilities have to be installed using apt-get on Ubuntu.

apt-get update
apt-get install cifs-utils

You will be asked if you want to really do it as it will take an extra 41 MB of disk space. Press Y to agree.

After this operation, 41.2 MB of additional disk space will be used.
Do you want to continue? [Y/n]

Now a directory where the backups will be mounted needs to be created. Typically the external mounts are all in /mnt. So let’s create a backups directory there.

mkdir /mnt/backups

Now it is time to mount.

mount -t cifs //FileShare/SqlBackups /mnt/backups \
   -o username=yourusername,domain=yourdomain,file_mode=0777,dir_mode=0777,rw,sec=ntlm

You will be asked for a password and if everything is correct you will get prompt again which means everything went well. It is important to note the change from \ to /.

It should not be possible to navigate to that directory and create a test text file.

cd /mnt/backups
touch test.txt

With that, the test.txt file should be now visible on \\FileShare1\Backups.

And to take the backup to that share becomes no different to taking any other backup to disk:

backup database [TestDb] 
   to disk='/mnt/backups/testdb.bak'

Using Shares to Interact with the Host

It is also possible to use this approach instead of using -v or --mount to share the file system between a container and its host. It is arguably a bit more effort but it allows to easily share backup and restore location between multiple containers without read/write issues associated with folder binding.

Connect to SQL Server on Linux

How to connect to SQL Server on Linux?
It really depends on what one means by ‘connect’.

Query SQL Server on Linux from Windows

To connect to the SQL Server instance running on Linux directly or in a container to run a query it is no different from connecting to one running on Windows. Just connect using the DNS name or an IP and a port number if it is not the default 1433. The only difference is that only SQL Authentication is supported so you will not be able to use Windows Authentication and your domain credentials.

Query SQL Server from Linux

Azure Data Studio (formerly known as SQL Server Operations Studio) can run on Linux offering the same user experience as on Windows. It is also possible to use the sqlcmd which works exactly the same as the one on windows. To install it on Ubuntu or any Linux flavour using apt-get package manager you can simply do

apt-get install mssql-tools

End then just use it the same way as you would normally do

sqlcmd -S localhost -U sa -P mysecretpassword

In the default SQL Server Docker image the mssql-tools are already installer but they are not added to the $PATH variable. You can either add it, or use the full path to execute it which changes the above command to

/opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P mysecretpassword

Remote to Linux remote server

When managing a SQL Server instance it is sometimes necessary to connect to the operating system on which it runs. This is very different on Linux to running SQL Server on Windows. There is no remote desktop, and PowerShell doesn’t really work either. That’s where the typical Linux admin tools become necessary.

In a way the Secure Shell or SSH is the Linux equivalent of the Remote Desktop. There are many ways to do it, especially from another Linux box. Most of us, SQL DBAs, will be typically on a Windows machine. In that case a common approach is to use PuTTy. But if you use PowerShell and keep up with updates it is now possible to SSH directly for a PowerShell terminal too.

ssh mysqlonlinux.mydomain.com

or

ssh 10.11.12.13

Simple as that. You will connect with a specific user and now most of the admin commands will have to start with sudo which allows to execute them with elevated permissions. It’s similar to Windows’ Run as Administrator.

Connect to Docker container from the host

If you want to connect to a container running your SQL Server on Linux from the host use docker exec to start a bash shell.

docker exec -it sql1 bash

where sql1 is the name of the container hosting SQL Server.

You will connect with superuser permissions and sudo will not be necessary. The prompt will look something like this:

root@2ff21ad83800:/#

SSH into a container running SQL Server on Linux

If you cannot or don’t want to remote onto the host first to connect to the container running your SQL Server instance SSH is the way to go, again. By default, the SQL Server image does not have SSH server so you will have to install it or a custom image will have to be created. Once that is done it is no different from connecting with the ssh (or PuTTy) to any other Linux server. It doesn’t matter that it runs in a container.

Using SQL Server on Docker (1)

It is yesterday’s news. SQL Server runs on Linux, and on Docker too. There are plenty of blog posts showing how to install, start and connect to it too.

During last Data Relay, I have seen Mark Pryce-Maher‘s talk on SQL Server on Linux (twice). It was a good talk, but it followed the same pattern so not surprisingly a common question from the audience appeared to be ‘why would you want to do it?’.

I mean, the biggest claim to fame appears to be that SQL Server actually works on Linux. It doesn’t bring any new features. In fact, some features are missing. A good part of docker demos appears to be an exercise in futility too. One starts a container with SQL Server, creates a database, inserts some data then the container restarts and as if by magic the data is gone, the database never existed. So, indeed, why would you want to run an SQL Server on Docker?

This post is my attempt to address this question by showing how I use SQL Server on Docker and how I deal with the persistence problem.

First: Get it up and running!

If you want to know how to run and connect to SQL Server on Docker read the Microsoft’s Quickstart Document, or Andrew Pruski’s blog post about running vNext on Docker.

Making the long story short, assuming you are running Windows, have Docker installed, configured to run Linux containers and that you don’t have an SQL Server instance locally installed:

docker pull mcr.microsoft.com/mssql/server:latest
docker run -d -e ACCEPT_EULA=Y -e SA_PASSWORD=Secret1234 -p 1433:1433 --name sql mcr.microsoft.com/mssql/server:latest

That’s all you need to do to have the latest production SQL Server (Developer Edition) instance running on localhost on port 1433.

Creating a database that survives container restarts

If you have the container from the previous example running let’s stop and remove it first.

docker stop sql
docker rm sql

There is a number of ways to persist container data. For my purposes the simples appears to be docker volumes. You can read more about docker persistence on the official documentation site but for now let’s just create a local volume called sqlvolume

docker volume create sqlvolume

and mount that volume on the container

docker run -d -e ACCEPT_EULA=Y -e SA_PASSWORD=Secret1234 -p 1433:1433 --mount source=sqlvolume,target=/data --name sql mcr.microsoft.com/mssql/server:latest

Now, if you create a database with files on the mounted volume like so:

create database [TestDb]
on primary (name = N'TestDb', filename = N'/Data/TestDb.mdf')
log on (name = N'TestDb_log', filename = N'/Data/TestDb.ldf')

and you stop and start the container

docker stop sql
docker start sql

as if by magic, the TestDb database survived the restart.

Why I find it useful

So with some considerable effort, I was able to create a database that survives a restart of a container or that of a host system. Nothing a standard SQL Server instance running on windows couldn’t do! So why do I do it?

I write my blog posts on my laptop and I don’t want to commit to installing SQL Server on it. I want to be able to try out vNext while still having the ability to run demos against 2017 and I’m definitely not installing two instances on my laptop. Luckily I don’t have to. With the above setup all I have to do is run docker start sql and within seconds I have a local instance of SQL Server ready to serve my queries. When I’m done I do docker stop sql and it is as if the SQL Server was never there.

Command Line Tools for SQL Server

TLDR: To efficiently manage multiple SQL Server instances learn PowerShell and check out the dbatools. There is also the mssql-cli to look at too.


Since its introduction in 2005 the SQL Server Management Studio (SSMS) was the tool used for development and administration of SQL Server databases. Most of the common, day to day tasks one can perform by pointing and clicking without the need to remember (or at least type in) all those confusing commands. But I never liked mice much and the main reason I kept using SSMS was that the only real alternative was the sqlcmd command line utility which is a command line tool, but doesn’t make you any more productive than SSMS.

But the times are changing. I attended ~SQL~Data Relay earlier this week and it struck me that a lot of SQL Server demos where done without the SSMS. And as I started thinking about it I realised that in fact I don’t use it that frequently in my job either. With the power of PowerShell and the dbatools and dbachecks module I can no longer imagine managing SQL Servers with SSMS.

Examples

Imagine you’ve got 3 servers (just to make the example simple, but it could be 30 or 300, it doesn’t really matter). All we have to do is define a variable holding a list of those servers like so:

$s = server1,server2,server3

And now we can get to work. Here are a few example of tasks which in SSMS would involve too much clicking for my liking, and task I can perform with a single PowerShell command.

Find a Database

I’ve got hundreds of databases on each instance but I need to find the specific one. All I have to do is

Find-DbaDatabase -SqlInstance $s -Pattern "PartOfMyDatabaseName"

And it really can be just a part of the name, so I don’t have to remember the whole name, it doesn’t matter if I remember the beginning or the end of the name.

Check server logs

Let’s say I need to check all my servers error logs for a specific five minutes when our system had a wobble. With SSMS it’s hours of mouse abuse. With PowerShell it’s a single command again

Get-DbaSqlLog -SqlInstance $s -Afater '2018-10-10 10:00' -Before '2018-10-10 10:05`

It will collate all error logs for the time period between 10:00 and 10:05 on 10/10/2018. If the output is too long to read in the PowerShell console you can pipe it to Out-GridView. In fact you can do that with any powershell command returning data. Try adding | Out-GridView at the end of any command like so in the log example

Get-DbaSqlLog -SqlInstance $s -Afater '2018-10-10 10:00' -Before '2018-10-10 10:05` | Out-GridView

Find DB Growth Events

How to find when my databases' files have grown recently? It is possible with SSMS but why would you do it if you can do:

Find-DbaDbGrowthEvent -SqlInstance $s | Out-GridView

That’s it. That is how simple it is. The above command returns all the growth events from all of the servers previously defined in the $s variable.

Conclusions

I still use SSMS, especially when it comes to managing Availability Groups and the AG Dashboards. It is useful for report writing and some ad hoc querying although for those sort of tasks I started using ~SQL Server Operations Studio~ recently renamed to Azure Data Studio. It is based on VS Code and runs on Windows, Linux and macOS, and although it doesn’t have all the features of SQL Server Management Studio, it does have some gems of its own which are not available in SSMS. Another reason could be performance tuning and looking at execution plans, but for those reasons I have long switched to the free Sentry One product Plan Explorer.

For anything else I use PowerShell with dbatools. Looking at the content of SQL Relay demos I’m not alone on that trend.

Parsing Database Object Names

Problem: Parse database object identifiers from single name to 4 part name and extract individual elements. So for example Server1.MyDb.dbo.TableA is TableA in schema dbo in MyDb database on a linked server Server1.

I’m not looking for a solution that will validate the name is valid, that it meets all the requirements, or even if it is well formatted. I’m assuming that it is a reasonable name, I just want to extract all the components provided: Linked Server, Database, Schema and Object names.

Solution 1 – PowerShell and String Splitting

Define a function that splits the string by ‘.’ and assigns values based on number of elements in the array.

function Parse-SqlName {
    param([string]$name)
    process {
        $output = [pscustomobject]@{ Server = ""; Database = ""; Schema = ""; Object = ""; }

        $names = $name.Split('.')
        if ($names.Length -ge 4) { $output.Server = $names[-4] }
        if ($names.Length -ge 3) { $output.Database = $names[-3] }
        if ($names.Length -ge 2) { $output.Schema = $names[-2] }
        if ($names.Length -ge 1) { $output.Object = $names[-1] }

        return $output 
    }
}

To use it call

Parse-SqlName "Server1.MyDb.dbo.TableA"

This returns

Server Database Schema Object
Server1 MyDb dbo TableA

#Solution 2 – Regular Expression

Regular Expression is another way to parse strings so I set myself a challange to create one that will do the same as the above function and would work regardless how many parts of the identifier are present. Here is the result

[\[]?(?:(?:(?:(?<Server>[\w_&@$ -]+)[\.\[\]]+)?(?<Database>[\w_&@$ -]+)[\.\[\]]+)?(?<Schema>[\w_&@$ -]+)[\.\[\]]+)?(?<Object>[\w_&@$ -]+)\]?

After processing a string with it up to four groups will be available. It can be used in any language that supports regex, but here is an example using PowerShell again

function Parse-SqlName2 {
    param([string]$name)
    process {
        if ($name -match '[\[]?(?:(?:(?:(?<Server>[\w_&@$ -]+)[\.\[\]]+)?(?<Database>[\w_&@$ -]+)[\.\[\]]+)?(?<Schema>[\w_&@$ -]+)[\.\[\]]+)?(?<Object>[\w_&@$ -]+)\]?') {
            return [pscustomobject]@{
                Server = $Matches.Server 
                Database = $Matches.Database 
                Schema = $Matches.Schema 
                Object = $Matches.Object 
            }
        }
    }
}

Reseeding Identity Columns

Recently I have been asked to increase the current value of all identity columns in tens of databases. No, the point is not to argue if it was smart, or a necessary thing to do, it wasn’t production environment and it was helpful to people requesting it, so I focused on how to do it. This blog post is about how dbatools once again proved to be an invaluable tool.

The first instinct was, of course I can do it… just a little bit of dynamic SQL, perhaps a coursor or two and I’ll have it done. But the thing is, since I discovered dbatools I don’t like dynamic T-SQL any more, and I’m sure whatever the task at hand might be, somebody probably has implemented a dbatools function for that alraedy. In this instance it wasn’t not quite so, but there is Test-DbaIdentityUsage which proved to be helpful. With it, in no time, I had code looking like this:

$databases.ForEach{
    $db = $psitem.db
    (Test-DbaIdentityUsage -SqlInstance myinstance -Databases $db).ForEach{
        $newid = [int]($psitem.LastValue * 1.2)
        Write-Host "  processing [$db].[$($psitem.Schema)].[$($psitem.Table)] has max ID $($psitem.LastValue), setting it to $newid"
        Invoke-DbaSqlQuery -SqlInstance myinstance -Database $db -Query "dbcc checkident ('[$($psitem.Schema)].[$($psitem.Table)]', reseed, $newid)"
    }
}

Not quite the one-liner I hoped for, but not all bad either. And after few minutes all the identity column next values were increased by 20%. Dbatools made me look smart again 😉