Follow dbatools on Feedspot

Continue with Google
Continue with Facebook

dbatools by Chrissy Lemaire - 1w ago

Today’s article is part of T-SQL Tuesday. T-SQL Tuesday is the brainchild of Adam Machanic. It is a blog party on the second Tuesday of each month and everyone is welcome to participate.

This month’s T-SQL Tuesday is hosted by Tracy Boggiano ([b]|[t]), is all about Linux.

dbatools and linux

As a long-time Linux user and open-source advocate, I was beyond excited when PowerShell and SQL Server came to Linux.

A few of the decisions I made about dbatools were actually inspired by Linux. For instance, when dbatools was initially released, it was GNU GPL licensed, which is the same license as the Linux kernel (we’ve since re-licensed under the more permissive MIT). In addition, dbatools’ all-lower-case naming convention was also inspired by Linux, as most commands executed within Linux are in lower-case and a number of projects use the lower-case naming convention as well.

dbatools on linux

Thanks to Microsoft’s PowerShell and SQL Server teams, dbatools runs on Linux and mac OS!

As covered in our book which’ll be released next year, dbatools in a Month of Lunches, 75% of commands in dbatools are supported by Linux and mac OS.

Commands that are pure-SQL Server, like Get-DbaDatabase or New-DbaLogin, work on Linux and mac OS. Others that rely on WMI or remoting, like Get-DbaDiskSpace or Enable-DbaAgHadr, do not currently work on Linux.

If you’re curious, our docs site will tell you if a command is supported by Linux and mac OS.

And, of course, you can also ask on Linux itself. After installing dbatools, run Get-Command -Module dbatools to see all of the commands that are available.

sql on linux

dbatools also supports SQL on Linux. If it works on SQL Server Management Studio, it’ll work with dbatools as we’re built on the same libraries. Also because SQL on Linux is pretty much the same everything as SQL on Windows.

Interested in learning more? Check out the #1 rated SQL Server on Linux book, Pro SQL Server on Linux by Microsoft’s Bob Ward. This book was actually edited by our friend Anthony Nocentino and it’s rumored that Anthony will contribute some sweet Linux-centric commands to dbatools when the Year of the Linux Desktop arrives, so we’re looking forward to that

Kidding aside, dbatools supports Linux and mac OS in a number of ways. Not only can you run dbatools FROM Linux, you can also connect TO SQL on Linux. We even have fantastic Registered Server support that eases authentication.

to linux / mac os

Connecting to SQL Server (Windows or Linux) from Linux or mac OS is generally done with using an alternative -SqlCredential. So let’s say you follow Microsoft’s guide to setting up SQL on Linux or if you use dbatools’ Docker guide, you’ll likely need to authenticate with the sa or sqladmin account. Execute the following command.

Get-DbaDatabase -SqlInstance sqlonlinux -SqlCredential sa

You’ll then be prompted for your password, and voilà! Don’t want to type your password every time? You can reuse it by assigning the credential to a variable.

$cred = Get-Credential sa
Get-DbaDatabase -SqlInstance sqlonlinux -SqlCredential $cred

You can also export your credentials to disk using Export-CliXml. The password will be encrypted and only the same user/computer can decrypt.

Get-Credential sa | Export-CliXml -Path C:\temp\creds.xml
$cred = Import-CliXml -Path C:\temp\creds.xml
Get-DbaDatabase -SqlInstance sqlonlinux -SqlCredential $cred

Prefer using the Windows Credential Store instead? Check out the PowerShell module, BetterCredentials.

to linux / mac os using registered servers

You can also use Local Registered Servers! This functionality was added in dbatools 1.0

Connect-DbaInstance -SqlInstance sqlonlinux -SqlCredential sa | Add-DbaRegServer -Name “SQL Server on Linux”

From there, you can see it in SQL Server Management Studio’s Local Server Groups.

And the detailed pane

Ohhh! What! Now you can do this without worrying about authentication:

Get-DbaRegServer -ServerName sqlonlinux | Get-DbaDatabase

from dbatools on mac os to sql server on windows

Something that I had super fun doing was joining my Mac Mini to my homelab then authenticating flawlessly with my domain-joined, Windows-based SQL Server. Didn’t even need the -SqlCredential because Integrated Authentication worked

If you cannot join your Linux or mac OS workstation to a Windows domain, you can still use -SqlCredential. This parameter not only supports SQL Logins, but Windows logins as well. So you could connect to a Windows-based SQL Server using an Active Directory account such as ad\sqladmin.

Got any questions, hit up #dbatools on the SQL Server Community Slack at aka.ms/sqlslack and one of our friendly community members will be there to assist.

- Chrissy

  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

I am Andreas Schubert and I am working as a Principal Consultant and Database Reliability Engineer for SQL Server & Azure for multiple national and international companies. My focus is on implementing and operating complex 24/7 SQL environments with tens and hundreds of servers and multi-terabyte databases.

Welcome to a quick post that should help you operate your SQL Server environment more consistently and reduce manual, repetitive work.

The Problem

When you are running SQL Server Availability Groups, one of the most cumbersome tasks is to ensure that all logins are synchronised between all replicas. While not exactly rocket science, it is something that quickly means a lot of work if you are managing more than one or two Availability Groups.
Wouldn’t it be nice to have a script that is flexible enough to

  • be called by only specifying the Availability Group Listener
  • detect all replicas and their roles automatically
  • connect to the primary, read all SQL logins and apply them to EVERY secondary automatically?

Well, dbatools to the rescue again.

The solution

With dbatools, such a routine takes only a few lines of code.
The below script connects to the Availability Group Listener, queries it to get the current primary replica, as well as every secondary replica and then synchronizes all logins to each secondary.

In the template code, no changes are actually written due to the -WhatIf switch, so that you can safely test it to see what changes would be committed.

    Script : SyncLoginsToReplica.ps1
    Author : Andreas Schubert (http://www.linkedin.com/in/schubertandreas)
    Purpose: Sync logins between all replicas in an Availability Group automatically.
    The script will connect to the listener name of the Availability Group
    and read all replica instances to determine the current primary replica and all secondaries.
    It will then connect directly to the current primary, query all Logins and create them on each
    The script is provided so that no action is actually executed against the secondaries (switch -WhatIf).
    Change that line according to your logic, you might want to exclude other logins or decide to not drop
        any existing ones.
    Usage: Save the script in your file system, change the name of the AG Listener (AGListenerName in this template) 
           and schedule it to run at your prefered schedule. I usually sync logins once per hour, although 
           on more volatile environments it may run as often as every minute

# define the AG name
    $AvailabilityGroupName = 'AGListenerName'

# internal variables
    $ClientName = 'AG Login Sync helper'
    $primaryInstance = $null
    $secondaryInstances = @{}

try {
    # connect to the AG listener, get the name of the primary and all secondaries
        $replicas = Get-DbaAgReplica -SqlInstance $AvailabilityGroupName 
        $primaryInstance = $replicas | Where Role -eq Primary | select -ExpandProperty name
        $secondaryInstances = $replicas | Where Role -ne Primary | select -ExpandProperty name
    # create a connection object to the primary
        $primaryInstanceConnection = Connect-DbaInstance $primaryInstance -ClientName $ClientName
    # loop through each secondary replica and sync the logins
        $secondaryInstances | ForEach-Object {
            $secondaryInstanceConnection = Connect-DbaInstance $_ -ClientName $ClientName
            Copy-DbaLogin -Source $primaryInstanceConnection -Destination $secondaryInstanceConnection -ExcludeSystemLogins -WhatIf
catch {
    $msg = $_.Exception.Message
    Write-Error "Error while syncing logins for Availability Group '$($AvailabilityGroupName): $msg'"

To make tools reusable, you could easily turn this script into a function by adding the 2 variables as parameters. Then you could call it from any other script like

SyncLoginsToReplica.ps1 -AvailabilityGroupName YourAGListenerName -ClientName "Client"

For simplicity, I created this as a standalone script though.

I hope you find this post useful. For questions and remarks please feel free to message me!

  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
dbatools by Chrissy Lemaire - 2M ago

Today marks the 30 day countdown to dbatools 1.0, which we will be debuting at Data Grillen in Lingen, Germany! These next 30 days are important and I’m writing to ask for your help.

Integration tests

We could still use a hand getting in those last few Integration tests. If you’re interested in adding a couple tests, I did a quick lil livestream on Twitch about writing integration tests for dbatools that could be useful to you.

powershell and chill: integration testing with pester - YouTube

I don’t think I’ve had the time to share, but dbatools team members livestream at dbatools.io/live and videos are published on our youtube channel, youtube.com/dbatools. More on that later.

Bugs and various issues

We’ve got about 30 issues left to resolve which you can see and follow on our GitHub Projects page. If you’ve ever been interested in helping, now is the perfect time as we only have 30 more days left to reach our goal.

If you’re a current or past dbatools developer, we’d love any help we can get. Just hit up the GitHub Projects page to see what issues are left to resolve. If someone is already assigned, please reach out to them on Slack in the #dbatools-dev channel and see if they can use your help.

prerelease branch

For anyone submitting Pull Requests, please submit to the temporary default: prerelease

Oh, and if you dbatools, please give it a on GitHub if you haven’t already. Stars make our day.


  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
The situation

Hey all, I am Andreas Schubert and I am working as a Principal Consultant and Database Reliability Engineer for SQL Server & Azure for multiple national and international companies. My focus is on implementing and operating complex 24/7 SQL environments with tens and hundreds of servers and multi-terrabyte databases.

With the multitude of environments that I am operating, it’s impossible to remember every server, every database or the multiple different ways they are interacting with each other. Therefore, one of the first things I do when taking over a consulting engagement is mapping out all those different bits of information.

Since the environments usually change pretty fast, my goal is to automate this process as much as possible.

In this series of posts, I will try to show you how I am implementing this. Of course, your requirements or implementations may differ, but hopefully this blog post can give you some ideas about your tasks too.

Enter dbatools

Before dbatools existed, I had to rely on either the various monitoring solutions that my customers are using or on scripts created by myself. There are a lot of really great 3rd party tools out there that do an awesome job. Unfortunately, they all differ in how they are used or what information they report back. I needed something that is easy to implement, with as few dependencies as possible and works across all SQL Server versions. That’s when I started using dbatools.
I immediately felt in love with how flexible it is. And boy, did its functionality grow fast!

Today, there are tons of commands available that cover almost all, of the various, areas SQL Server has to offer.

The task

Before I dive into specific SQL Servers for in-depth analysis, I want to see some sort of inventory. The minimum information I would like to collect is:

  • What server is SQL running on?
  • What edition and patch level does it have?
  • How many cores and RAM is it equipped with?
  • How many databases are on the SQL, what size are they and what’s the biggest one?
  • Is the SQL part of an Availability Group? If yes, what’s the name of the AG, what role does the SQL have right now and what’s the underlying cluster object name?
  • On top of that, I find that SQL Instances usually get a “pet name” alias. This means, when people within the company are talking about the SQL Server, they don’t call it by it’s machine name, but rather something like “the production database” or “the cluster”. Technically not correct, but that’s how it is happening. Due to that, my overview should also contain that alias to make it easier for me until I remember all the mappings.

Whoever has built an inventory script in the past knows that collecting the above information requires quite a few scripts. On top of that, the underlying DMVs have been changed between SQL versions, so you need to account for that. Microsoft has made that much easier with providing SMO (SQL Server Management Objects), a set of libraries that abstract away the complexity of collecting that information. Thankfully, Microsoft also enabled the dbatools team to include SMO in their framework. My example solution relies solely on dbatools (which works – not only but also – with SMO).

OK, enough talk, let’s jump straight into the code.

The solution

Right at the beginning of any of my scripts, I am defining the root of the script itself. I do this because I re-use a lot of functions.

Since we want to collect the information for more than one SQL Server instance, we will first build a list of SQL Servers. We could query the list of instances from a central management server, but for the purpose of this post – and portability- we will keep it simple. We will also assume the account executing this script will have sufficient permissions on each SQL Server instance and that it can connect via Windows Authentication. I generally prefer Windows Authentication over SQL Server authentication due to security concerns, but that is a completely separate topic.

The names of the Servers will be coming from a simple text file in our example. Just do me a favour and do NOT put your server list into an unsecured network location – again, we need to keep security in mind.

Next, we need to load this file into our PowerShell session. For the sake of simplicity, I am loading it explicitely into my script. Normally, I have a variable populated with the servers in my profile, so I don’t have to do this each time.

$script:root = 'D:\AdminScripts'
$ProductionServers = Get-Content (Join-Path $script:root -ChildPath 'Production.txt')

Similar to the actual server list, I am using a text file “AliasList.txt” to store the alias information i mentioned above:

It´s the same system: the name of the server or instance, followed by the alias name. Both values are separated by a semicolon. Loading and storing the alias information in a hash table is a simple one-liner in PowerShell:

$AliasList = Get-Content (Join-Path $script:root -ChildPath 'AliasList.txt') | Select @{Name= "Instance";Expression={$_.ToString().Split(';')[0]}},@{Name= "Alias";Expression={$_.ToString().Split(';')[1]}}

Since I usually exclude system databases from my reports, I am defining a separate list of them as well for easier reuse:

$systemDBs = "master","model","msdb","tempdb", "ReportServer","ReportServerTempDB"

At this point, we have all the preliminaries completed: A list of SQL Servers to query, a list of system databases that we will exclude and a list of alias information. Let’s hit the servers and put the resulting data into a variable. I’ll first show the complete code block, then we will talk about what it does.

$rawData = $ProductionServers | Connect-DbaInstance | Sort-Object Computername | Select-Object ComputerName,
    # map the SQL version
    @{Name="SQL Version";Expression={ if ($_.VersionMajor -eq "11") {"SQL 2012"} elseif ($_.VersionMajor -eq "12") {"SQL 2014"} elseif ($_.VersionMajor -eq "13") {"SQL 2016"}
    elseif ($_.VersionMajor -eq "14") {"SQL 2017"} elseif ($_.VersionMajor -eq "15") {"SQL 2019"} elseif ($_.VersionMajor -lt "11") {"SQL 2008R2 or older"} else {"unknown"}}},
    ProductLevel, Edition,
    # RAM
    @{Name= "Memory (GB)";Expression={[math]::Round(($_.PhysicalMemory) / 1024)}},
    Processors, InstanceName,
    # total count of user dbs
    @{Name= "User DBs";Expression={($_.Databases | where {$_.Name -notin $systemDBs} | Measure).Count}},
    # total db size for all user dbs
    @{Name= "Total DB Size (GB)";Expression={[math]::Round(($_.Databases | where {$_.Name -notin $systemDBs} | Select size | Measure -Property Size -sum | Select sum).sum / 1024)}},
    # biggest DB (name, Size(GB)
    @{Name= "Biggest DB (GB)";Expression={"$($_.Databases | where {$_.Name -notin $systemDBs} | Sort Size -Descending | Select -ExpandProperty Name -First 1)
    ($([math]::Round(($_.Databases | where {$_.Name -notin $systemDBs} | Sort Size -Descending | Select -ExpandProperty Size -First 1)/1024)) GB)"}},
    # add the name of the Availability Group (if any)
    @{Name= "AG (s)";Expression={$_ | Select -ExpandProperty AvailabilityGroups | Select -ExpandProperty AvailabilityGroupListeners}},
    # add the current role of the server in the Availability Group (if any)
    @{Name= "Role (s)";Expression={$_ | Select -ExpandProperty AvailabilityGroups | Select -ExpandProperty LocalReplicaRole}}, ClusterName | Sort ComputerName

While this code may look complex, from a PowerShell point of view it’s really pretty simple. First, we take the list of our Productionservers and pipe it to Connect-DbaInstance cmdlet. Connect-DbaInstance is the result of dbatools calling the SMO functionality, returning a complete SMO object of the SQL Server connected to.

Technically, the part with $rawData = $ProductionServers | Connect-DbaInstance already gives us all the information we need for our report. But since we don’t want to return all the possible SMO properties and objects (that would result in a very long operation), we pipe the results of this directly to a Sort, followed by returning the actual information we are interested in:

$rawData = $ProductionServers | Connect-DbaInstance | Sort Computername | Select ComputerName,

First we extract the Computername. On the next two lines, we map the SQL Server major version number to a clear-text string:

# map the SQL version
@{Name="SQL Version";Expression={ if ($_.VersionMajor -eq "11") {"SQL 2012"} elseif ($_.VersionMajor -eq "12") {"SQL 2014"} elseif
($_.VersionMajor -eq "13") {"SQL 2016"} elseif ($_.VersionMajor -eq "14") {"SQL 2017"} elseif ($_.VersionMajor -eq "15") {"SQL 2019"}
elseif ($_.VersionMajor -lt "11") {"SQL 2008R2 or older"} else {"unknown"}}},

Right on, we extract the product level (e.g. RTM, SP1, …) and the Edition of the SQL Server (Standard, Enterprise…), followed by the available machine memory. Since this is returned in MB, we format and round it so that we get a nice number in GB (e.g. 12, 48 or 128).

Then we include the number of logical processors and the name of the instance – in case we have a named instance.

The number and size of user databases is a bit more complex. We need to query the “Databases” collection of the SMO Server object, filter out the system databases, get the size property of each object in the collection and measure it (count for the number and SUM for the combined size of the databases). Of course, we want those numbers to be nicely formatted and rounded to the full GB, so we add the formatting as well:

# total count of user dbs
@{Name= "User DBs";Expression={($_.Databases | Where {$_.Name -notin $systemDBs} | Measure).Count}},
# total db size for all user dbs
@{Name= "Total DB Size (GB)";Expression={[math]::Round(($_.Databases | Where {$_.Name -notin $systemDBs} | Select size | Measure
-Property Size -sum | Select sum).sum / 1024)}},

To get the size of the biggest / largest database on the server, we use the same technique, only that we sort the database object list by size in descending order and take only the first object:

# biggest DB (name, Size(GB)
@{Name= "Biggest DB (GB)";Expression={"$($_.Databases | Where {$_.Name -notin $systemDBs} | Sort Size -Descending | Select -ExpandProperty Name -First 1)
($([math]::Round(($_.Databases | Where {$_.Name -notin $systemDBs} | Sort Size -Descending | Select -ExpandProperty Size -First 1)/1024)) GB)"}},

The PowerShell pipeline can be really awesome!

Now let’s add the information about the Availability Group Listener to our list. The AG information is another sub-object of the SMO collection which we first have to extract to get to the information below.

# add the name of the Availability Group (if any)
@{Name= "AG (s)";Expression={$_ | Select -ExpandProperty AvailabilityGroups | Select -ExpandProperty AvailabilityGroupListeners}},

And exactly the same way for the role of the current replica as well as the cluster object name:

# add the current role of the server in the Availability Group (if any)
@{Name= "Role (s)";Expression={$_ | Select -ExpandProperty AvailabilityGroups | Select -ExpandProperty LocalReplicaRole}}, ClusterName | Sort ComputerName

That’s a whole lot of information retrieved by just one call to a PowerShell method. How awesome is that? Most of our script is logic around formatting and extracting information from sub-properties and objects.

And finally, we add the Alias to our result, matching them by computername:

# add the alias to the rawdata
$rawData | % {
        $v = $_.ComputerName
        if ('' -ne $_.InstanceName){$v +="\$($_.InstanceName)"}
        $alias = $AliasList | Where {$_.Instance -eq $v } | Select -ExpandProperty Alias -First 1
        $_ | Add-Member -MemberType NoteProperty -Name AliasName -Value $alias

The last step is to convert our object to HTML and add some css styling to it. Then we can either send it via eMail or store the generated html as a file for future reference.

$css = Get-Content (Join-Path $script:root -ChildPath 'css.txt')
$html = $rawData | ConvertTo-Html -Fragment -PreContent "$($css)<h2>Instance KPI Summary</h2>" -PostContent "This summary has been generated with the help of the awesome PowerShell module dbatools!" | Out-File (Join-Path $script:root -ChildPath 'result.html')

The result is a nicely formatted html report:

This was only a very basic example of what you can do with PSTools, PowerShell and a bit of magic piping. I hope you found this useful.

For questions and remarks please feel free to message me at any time!

You can find the complete script in my GitHub repo.

  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

This post originally appeared in two parts on my personal blog at flxsql.com and has been reposted here by request.

A bit over a year ago, I blogged about my experience migrating a test SQL Server instance from a VM to a physical machine with a little help from my friends. That migration went well and the instance has been running trouble-free ever since. But it’s small potatoes. A modest instance, it’s only about 5% the size of production. With SQL Server 2008R2’s EOL looming, it was time to migrate production to SQL Server 2016. It’s a pretty beefy setup:

  • 2-node Failover Clustered Instance
  • 16 cores
  • 768GB RAM
  • 4 TB of storage
  • Over 8000 databases
The Challenge

How do you move eight thousand databases in a reasonable amount of time?

I spent about an hour and a half one morning hashing ideas out w/ folks in the dbatools Slack channel, plus several conversations in the office and with our hosting provider.

  • Start-DbaMigration? I love that function but it’s single-threaded and we just don’t have time to backup & restore that many databases that way.
  • Backup & restore? Multi-threaded, our daily full backups take over 3 hours to run. Double that to do a backup & restore.
  • Detach & reattach? We’ll need either double the storage and eat the time copying the data, or risk the time required to restore from backup if we have to revert.
  • Log shipping, mirroring, replication? Again, double the storage, and we have so many databases that it’s just not feasible.
  • In-place upgrade? Not supported by our hosting provider, and there’s not much of a safety net.

Ultimately the team settled on a variation of the detach & reattach.

  1. Install SQL Server 2016 on new physical servers with “dummy” drives (so that paths could be set during install)
  2. Shut down SQL Server 2008R2
  3. Take a SAN snapshot
  4. Move the LUN to the new server
  5. Attach the databases

This is the fastest way for us to move the data, and the snapshot provides a way back if we have to revert. We have backups as well, but those are the reserve parachute – the snapshot is the primary. This process is easiest if the paths for the data and log files remain the same. But that didn’t happen here. On the old instance, both data and logs were dumped in the same directory. The new instance has separate data and log directories. And it’s a new drive letter to boot.

OK, so how do you attach eight thousand databases and relocate their files in a reasonable amount of time?

It’s dbatools to the rescue, with Mount-DbaDatabase being the star of the show. Not only does it attach databases for you, you can use it to relocate and rename the files as well. But that’s really one of the last steps. We have setup to do first.

Preparation Basics

Once the servers were turned over to us, the DBA basics had to get set up. I configured a few trace flags for the instance with Set-DbaStartupParameter -Traceflags 3226,4199,7412,460.

Trace Flag Purpose
460 Enable detailed String or Binary Data would be truncated error message in a future Cumulative Update
3226 Suppress successful backup messages in the error log
4199 Enable query optimizer fixes in CUs and hotfixes
7412 Enable lightweight execution statistics profiling

Since publishing the original post, I’ve received a couple questions about the use of TF4199. With the database-scoped option QUERY_OPTIMIZER_HOTFIXES this isn’t absolutely necessary to get post-RTM hotfixes for the optimizer. Pedro Lopes (blog|twitter) recommended in his Summit 2018 to enable it globally, and he confirmed that in an email exchange with Andy Galbraith (blog|twitter) that Andy wrote up on his blog. Pam Lahoud (twitter) wrote about the topic in a recent post as well.

I did use Start-DbaMigration but excluded Databases, Logins, AgentServer, ExtendedEvents (the last because we don’t use XE on the old instance anyway; this avoided any warnings or errors related to it). Excluding databases makes sense given what I wrote above, but why logins and Agent jobs?

The source instance is several years old and has built up a lot of cruft; this migration was a good chance to clear that out. All disabled logins and jobs were scripted out and saved, and only the active items migrated. But that also meant I couldn’t use Copy-DbaAgentServer because it doesn’t filter jobs out; a few extra steps were necessary. For reasons I don’t understand, Start-DbaMigration copied our database mail and Linked Server setups faithfully, with one exception – the passwords.

We were able to fix that up easily enough but I found it strange that of all things, the passwords weren’t copied properly. Especially since I’ve done this successfully with dbatools in the past.

Moving logins

Although I only wanted to migrate the currently-active logins, I wanted the ability to re-create any disabled logins just in case, so I needed to extract the create scripts for them. I achieved this via Get-DbaLogin, Export-DbaLogin, and Copy-DbaLogin:

Moving Agent jobs

I had the same need for Agent jobs, and achieved it similarly. However, because I excluded the AgentServer from Start-DbaMigration, I had to peek into that function to find out all the other things it copies before copying the jobs. I also wanted to leave the jobs disabled on the new server so they didn’t run before we were ready to test & monitor them in a more controlled way.

Maintenance & Monitoring

When that was complete, we updated the community tools that are installed in system databases

We use MinionWare’s Minion CheckDB but didn’t need do a separate installation or migration. With the exception of the Agent jobs, everything is self-contained in a single database. The Agent jobs were copied above, and the database came over with all the others.

Ready to Go

With the above complete, there wasn’t much left to do aside from doing some small-scale testing of the database attachment process and validating system operations (database mail, backups, CheckDB, etc.). ## Final Prep We completed our nightly backups as usual on Friday night, so when I arrived Saturday I kicked off a final differential backup to catch any overnight changes. We’ve multi-threaded Ola’s backup script by creating multiple jobs and I started them all at once with (of course) PowerShell.

Get-DbaAgentJob -SqlInstance $OldInstance | Where-Object {$_.name -like 'user database backups - diff*'} | ForEach-Object {$_.Start()}

I estimated that the diff backups would take about 90 minutes based on a couple test runs; they took 100 minutes (not too shabby!). While that ran, I re-exported the Agent jobs just to be sure I had everything captured there. I also copied a few databases to another 2008R2 instance in case they were needed for debugging purposes. The very last step was to extract a listing of all our databases and the full paths to the physical data and log files, then split them into ten files.

The method I used to attach the databases wasn’t scalable to running it for all eight thousand databases at once and this let me control the batch sizes easily.

The resulting CSV file gave me each database file, the type of file (data or log), and the database name itself.

database_id name FileType physical_name
7 MyDatabase ROWS S:\Very\Long\Path\MyDatabase.mdf
7 MyDatabase LOG S:\Very\Long\Path\MyDatabase_1.ldf
Time to Move

With all of our pre-migration work complete, we shut down the SQL Server 2008R2 instance. Then we turned things over to the folks in the datacenter to detach the storage LUN, take the snapshot, and attach the LUN to the new instance. When their work was complete, they passed the baton back to me to move the files around and attach the databases.

Attaching the Databases

Before reading this section, I suggest you read two other recent posts, PowerShell Multithreading with PoshRSJob and Thread-safe PowerShell Logging with PSFramework, as they’ll provide the background for how some of this was done.

I created my own function as a wrapper for Mount-DbaDatabase from dbatools, adding the extra features I needed (or thought I needed):

  • Logging
  • Multi-threading
  • Logically renaming the files
  • Setting the database owner appropriately
  • Rebuilding indexes
  • Upgrading the CompatibilityLevel

In practice, I only used the first four in the initial attachment of the databases for the upgrade. Example execution:

Get-MountProgress is a variation on one of the functions in my multithreading post above, which let me keep tabs on the progress as the function ran. I ran the above code ten (well, eleven) times, once for each “batch” of 10% of the databases. The first group ran great! Only about 6 minutes to attach the databases. The next batch was 10 minutes. Then 16. And then, and then, and then…

Progress just kept getting slower

From the timestamps on the log files, you can see that each batch took progressively longer and longer. It was agonizing once we got past the 5th group. I have observed that SMO’s enumeration when connecting to a database instance can be lengthy with large numbers of databases on the instance, which would correlate to what I observed; the more databases I have, the longer it takes. But I can’t completely attribute the slowdown to this.

Partway through, we shifted gears a bit and I ran a special group of databases which hadn’t yet been attached but the QA team needed for their testing. This let them get rolling on their test plans without waiting longer.

Memory consumption for powershell.exe was very high as well, and kept growing with each batch. After the 3rd batch, I decided I needed to exit PowerShell after each one and restart it just to keep that from getting out of hand. I’m not sure what happened there; maybe a runspace memory leak?

I had estimated attaching the databases would take about one hour. It took over six and a half. There was no one more upset over this than me. On the up side, the logs showed nothing but success.


Once we realized that attaching the databases was going to run longer than expected, our developers and QA team pivoted to testing what they could with the databases we had attached early on. In hindsight, I should have asked them which databases they needed for their test plans and attached those right away, so that they could test while ther remainder of the databases were running. But, great news! They didn’t find any issues that could be directly attributed to how we did the migration of the data.

I re-ran my earlier PowerShell to fetch the databases and their files from sys.databases against the new instance and compared to the original; everything matched! Confirmation that all of the databases were attached.

Final Steps

We missed the estimated time for our go/no-go decision by five minutes. With the number of moving parts, databases in play, unexpected delays, and amount of testing we had to do, that’s pretty good! My colleague and I had some additional work we needed to take care of after the team declared the migration a success. Agent jobs needed to be enabled, overnight job startups monitored, things like that. We called it a day after about 14 hours in the office.

The next day, we had some more tasks to complete. Per a blog post by Erin Stellato (blog|twitter) back in May, because we upgraded from 2008R2 to 2016, an index rebuild was advisable for all our nonclustered indexes. I did this via Red Gate Multi Script and some dynamic SQL instead of PowerShell this time, looping through all the NC indexes in each database and running ALTER INDEX REBUILD.


Our first few days post-upgrade didn’t go well. CPU usage and response times were terrible and after checking over everything for days on end, we finally tracked it back to a piece of code that was still looking for the old SQL Server 2008R2 instance. Once that was fixed, everything came back to normal.

A few days post-migration, we did have one problem caused by a change in SQL Server 2016. It seems that SQL Server got a little stricter about doing subtraction of integers from time types (instead of using dateadd()). Correcting it was pretty easy as it was limited to a couple stored procedures which are used in a limited capacity.

Lessons Learned

It’s not a good project without some solid lessons learned! What could we have done to make migration day easier?

  • Work out which databases need to be available for performing post-upgrade checks and attach those first
  • Work in smaller batches (not sure how much this would have helped)
  • Have a test environment that’s as close as possible to production in all aspects

I suppose you’re expecting me to say something profound here. The most surprising thing to me about this migration is that there were no major surprises. Aside from one portion taking longer than anticipated and those two small pieces of code, everything went to plan. All in all, our migration is a success and after working out a few glitches in the week or two after, things have been running well. We’re on a modern release now, and looking forward to taking advantage of the new features available to us now.

- Andy Levy, flxsql.com

  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

In this age of many companies adopting the ideas of either DevOps or SRE (Site Reliability Engineer) roles, there is constant need for automation. Mundane tasks like collecting metrics, alerts, server and database asset info into a central database on which you can do reporting is now a common task that many DBAs or system admins are performing.

In SQL Server world, we are fortunate to have a vibrant community of PowerShell enthusiasts that have open sourced dbatools – a community driven PowerShell module to automate database development and administration.

This begs a question..

Why do we need automation?

We need automation to get rid of repetitive tasks that we have to perform on a daily basis. It is often said:

If you have to repeat it, better automate it.

This makes sense as the room for common errors is alleviated when the same steps you are taking are automated. Also, with ever increasing data needs, the infrastructure footprint is ever increasing. To meet this ever increasing demand, the only way for a team to scale is to adopt automation.

For example, being a SQL Server DBA, you might have been involved in server migration projects wherein you are tasked to migrate from older version of SQL server (SQL server 2008, SQL server 2008R2) to a more modern & supported version (SQL server 2014, 2016, 2017, etc.).

In the stone age (before dbatools), we as DBA’s spent countless hours scripting pre-migration tasks like logins, SQL Agent jobs, linked servers, system database custom objects, resource governor settings, certificates, etc. Even doing all this effort, often we miss few critical things due to non-standard process. dbatools takes care of the entire migration including pre-migration steps and migrating all or few databases from one instance to another along with error handling and logging. This is awesome, isn’t it?

Once good thing about dbatools is that it has lots of commands to get all kinds of information that you want to gather. The commands name have the prefix Get-Dba*. Another beauty is the ability to pipe the output of the commands and write to SQL tables using Write-DbaDataTable command.

In a large organization where you have hundreds of servers to manage and you often face a situation where you want to query all your SQL server estate. For example, checking total RAM and the max memory setting or you want to get version and service pack info for your entire SQL Server estate. Doing these tasks single threaded against a ton of servers is often painful because it takes lot of time!

Multithreading to the rescue What is multithreading and how does PowerShell offer multithreading?

In simple terms, multithreading is the ability to run multiple threads on same or separate processors at the same time resulting in parallel execution.

PowerShell provides many ways to allow multi-threading in your scripts. Let’s explore the most common ways:

  • Background jobs: A powerful way of allowing commands to run in the background and then retrieve the results later thereby freeing up the terminal and allowing the users to continue their work without interruption. Whenever a new job is started using Start-Job, it ends up spawning a new powershell.exe process which you can easily see using windows task manager or just running Get-Process command. This means that the more background jobs you run, the more powershell.exe processes are created which is a resource hog! Also, there is no ability to throttle the jobs (i.e. all jobs are started and executed at the same time). There is limited ability to manage multiple threads and have them share variables as well.
  • PowerShell Runspaces: A powerful and flexible way of leveraging .NET’s system.management.automation.runspaces namespace that exposes variety of classes designed to create, manipulate and orchestrate a pool of PowerShell processes. In simple terms, think of Runspaces as containers where everything is contained or stored and ran in an asynchronous fashion.

How to efficiently leverage PowerShell Runspaces with high performance and less overhead ?

Boe Prox has open sourced his awesome PoshRSJob module that provides an alternative to native PowerShell jobs with greater performance and less overhead to run commands in the background, freeing up the console and allowing throttling on the jobs.

We can use this module to leverage multi-threading in dbatools as well. The PoshRSJob module is well-documented, so in this blog post, I will just get into the details of how to leverage PoshRSJob module with dbatools.

As of writing this blog post, there are total of 514 functions in 0.9.722 version of dbatools out of which 192 are for fetching data (Get-Dba*) from your SQL server instances. How did I get those? Easy enough:

# How many functions are there in dbatools?
Get-Command -Module dbatools -Type Function | Measure-Object

# How many functions are there in dbatools that fetch data from SQL Server instances?
Get-Command -Module dbatools -Verb Get | Where CommandType -eq Function | Measure-Object

Now let’s say you are tasked to get all the databases that are part of a given Availability Group. There are two ways to approach this: the traditional (sequential) way and multithreading.

The traditional sequential way

# Get the list of servers. You can read directly from a text file or you can connect to your central database to get the list using tsql.
$servers = Get-Content D:\DBA\serverList.txt

# store credential in a variable which will be used for SQL authentication to your sql servers
$cred = Get-Credential -UserName sqladmin -Message "Enter your sqladmin Password"

# use foreach to loop through the collection and get the desired data
foreach ($server in $servers) {
    # here you can use any dbatools commands (Get-Dba*)
    Get-DbaAgDatabase -SqlInstance $server -SqlCredential $cred

Using PoshRSJob module to leverage Runspaces & Multithreading

# Get the list of servers. You can read directly from a text file or you can connect to your central database to get the list using tsql.
$servers = Get-Content D:\DBA\serverList.txt

# store credential in a variable which will be used for SQL authentication to your SQL Servers
$cred = Get-Credential -UserName sqladmin -Message "Enter your sqladmin Password"

# PoshRSJob has throttle parameter. I usually keep it to the number of processors installed on the server
$throttle = $env:NUMBER_OF_PROCESSORS

# Multithread! See below for a breakdown
Start-RSJob -InputObject $servers -Throttle $throttle -ModulesToImport dbatools -ScriptBlock {
    Get-DbaAgDatabase -SqlInstance $server -SqlCredential $Using:cred

# Get-RSJob will display all jobs that are currently available to include completed and currently running jobs.
# Receive-RSJob Gets the results of the Windows PowerShell runspace jobs in the current session. Also you can use -OutVariable variableName e.g. -OutVariable Results and then do $Results to get all the output
Get-RSJob | Where-Object  {{State -like "Completed"} -and  {HasMoreData -like "False"} -and {HasErrors -like "False"} }  | Receive-RSJob

# cleanup only the jobs that are completed, does not have more data and no errors
Get-RSJob | Where-Object  {{State -like "Completed"} -and  {HasMoreData -like "False"} -and {HasErrors -like "False"} } | Remove-RSJob


-InputObject $servers

Here we are passing the collection item – $servers as an input object that will take that object and use it as the first parameter in the script block as long as you add a Param() block in it.


This is added so that the -InputObject collection can be used as the first parameter

-ModulesToImport dbatools

Here you can use any dbatools commands as all the commands are imported as part of the module import

-SqlCredential $Using:cred

This is pretty cool way of passing a local variable to the script block i.e. you can use variables from parent PS Process into PSJob’s scriptblock

On a side note, if you want to get the results or output of Start-RSJob into a variable,  you can do it using $results = Get-RSJob | Receive-RSJob or Get-RSJob | Receive-RSJob -OutVariable Results (now you can use $Results same like former example) and then you can work with the result set as usual, and even write the entire result set to a database using Write-DbaDataTable.


Below test shows the performance difference between Sequential Execution vs Multithreaded execution using PoshRSJob:

  • Total Servers in the serverList.txt: 134
  • Total No. of logical CPUs: 16
dbatools command Sequential Execution PoshRSJob PercentGain
Get-DbaAgDatabase TotalSeconds: 123.13 TotalSeconds: 30.75 300.42 %

Note: When running using Start-Job, the CPU is pegged to 100% (since it spawns individual powershell.exe processes and does not have native throttling option) and it uses almost all the available RAM than the PoshRSJob. The execution time for Start-Job is more than double and exhaust CPU and available RAM on the machine.


Thanks for reading !
~ Kin Shah

Get in touch @TheRockStarDBA or at dba.se

  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Separate tags by commas
To access this feature, please upgrade your account.
Start your free month
Free Preview