Parallel Workflows in PowerShell

I’ve mentioned before that running Copy-DbaDatabase worked well in my experience, but the downside that I ran into was utilizing it for larger counts of databases. It should be no surprise that the more databases to copy, the longer the process takes to complete. I did some more research and came across using workflows along with the parallel keyword to speed up the copy process.

Let’s take a look at what a workflow is and how we can apply it to add parallelism to the database copy process.

What is a PowerShell Workflow?

PowerShell workflows are useful for creating and running sequences of tasks that can handle interruptions like reboots or network drops. Workflows can be used to run tasks in parallel, which is what will be helpful in this example. By adding the parallel keyword, the scripts in the block will run at the same time independently of each other.

Workflow Example

Let’s look at an example. Imagine we have four databases that we need to copy from one server to another. Four databases aren’t many to most, but I want to keep scripts short for the examples. We can use the Copy-DbaDatabase and copy the databases with a single script like this:

Copy-DbaDatabase -Source "source_server" -Destination "destination_server" -Database  Database_1, Database_2, Database_3, Database_4 -BackupRestore -UseLastBackup -Verbose

Copying all four databases with a single Copy-DbaDatabase script may complete successfully, but we want it to complete faster. This is where PowerShell workflows can help. Let’s create a workflow and specify the Parallel keyword:

Workflow Copy-Databases
{
    Parallel
    {
		...
	}
}

Now, we can split our Copy-DbaDatabase script into two (or more), add to our workflow, and run something like this instead:

Workflow Copy-Databases
{
    Parallel
    {
		Copy-DbaDatabase -Source "source_server" -Destination "destination_server" -Database  Database_1,Database_2 -BackupRestore -UseLastBackup -Verbose
		Copy-DbaDatabase -Source "source_server" -Destination "destination_server" -Database  Database_3,Database_4 -BackupRestore -UseLastBackup -Verbose
	}
}

Copy-Databases

Pick Up The Pace

More testing awaits, but I wanted to spread the word in case anyone else had similar cases. My experience so far with testing has been positive. If you’re looking for a parallel solution, give workflows and the parallel keyword a try.

Thanks for reading!