This is a big one!

Thanks to @andreasjordan, @niphlod, @Stuart-Moore, @JPRuskin and @wsmelton for helping make this release happen 🤩 A list of all PRs included in this release can be found way down below.

Updated SMO library / updated .NET requirements

We've updated from an SMO version (150.x) released in 2019 to the newest SMO library, released in the past 3 months: version 161.46367.54. The new version includes a ton of bug fixes and new functionality.

Now, we also use Microsoft.Data instead of System.Data.

The new version does require a much higher version of .NET -- 4.6.2, up from whatever allowed us to run PowerShell v3. Fortunately, .NET 4.6.2 was introduced in 2018, so everyone has been given a chance to upgrade. To see your current version of .NET, you can run the following from PowerShell:

[System.Runtime.InteropServices.RuntimeInformation]::get_FrameworkDescription()

dbatools also still works on older versions of Windows -- we tested on Windows Server 2008 R2 and Windows 7 with PowerShell v3 (bravo Microsoft SMO team!).

You can still connect to SQL Server 2000 as well.

We held off upping our requirements for as long as possible because we priortize backwads compatibility, but we also rely heavily on SQL Management Objects and newer versions have the bug fixes we needed addressed.

If you can't upgrade to .NET 4.6.2, you can still use the version of dbatools we released on July 13, 2021 by running: Install-Module -Name dbatools -RequiredVersion 1.0.173

Connect-DbaInstance

Connect-DbaInstance has been rewritten and uses new approaches to connect. If you have any issues, please let us know. You can use the old code path by running:

Set-DbatoolsConfig -FullName sql.connection.legacy -Value $true

Now, it's even easier to connect to Azure with Tokens.

# Auto-generated using Tenant (these tokens last one hour)
Connect-DbaInstance -SqlInstance sample.database.windows.net -Tenant $tenantid -SqlCredential $cred

# Create a token using dbatools
$token = (New-DbaAzAccessToken -Type RenewableServicePrincipal -Subtype AzureSqlDb -Tenant $tenantid -Credential $cred).GetAccessToken()
Connect-DbaInstance -SqlInstance sample.database.windows.net -Accesstoken $token

# Create a token using Get-AzAccessToken
$azureCredential = Get-Credential -Message 'Azure Credential'
$azureAccount = Connect-AzAccount -Credential $azureCredential
$token = (Get-AzAccessToken -ResourceUrl https://database.windows.net).Token
Connect-DbaInstance -SqlInstance sample.database.windows.net -Accesstoken $token -OutVariable server

Invoke-DbaQuery -SqlInstance $server -Query "Select 1"

Note that generating access tokens is not supported on Core, so when using Tenant on Core, we rewrite the connection string with Active Directory Service Principal authentication instead.

dbatools.dll

The dbatools.dll is now signed too 🥳 We've signed our ps files for years, and now the DLL is signed too. It shouldn't make an impact one way or another but it's still cool anyway.

DACPACs and BACPACs + Linux / Core support

A number of commands have been modified to support Linux and now 77.2% of commands are cross-platform!

The biggest highlight is that Publish-DbaDacPackage, Export-DbaDacPackage, New-DbaDacProfile are now available on Linux. This means that you can do some pretty easy deployments on your favorite CI/CD platform, like GitHub Actions.

Unfortunately,it's still not supported on Mac as we'd have to add a bunch of DLLs which increases the size of the package and the tradeoff for such a small target audience doesn't seem big enough.

In addition, we've updated a bunch of other commands and made them work with Linux and OS X too. It was mostly path and decryption issues.

SQL Server Integration Services (SSIS) commands

Three of our SSIS commands, Copy-DbaSsisCatalog, New-DbaSsisCatalog and Get-DbaSsisEnvironmentVariable have been removed. They've always been problematic and with the newest update, they've just stopped functioning.

We're considering moving them to a new module dbatools.ssis but we're waiting to see if new versions of SMO will work more reliability.

The replication commands continue to work if SSMS is installed, so we've kept them in.

New firewall commands

We've introduced 3 new firewall commands, New-DbaFirewallRule, Get-DbaFirewallRule, and `Remove-DbaFirewallRule have been added to dbatools. Huge shoutout to @andreasjordan for making that happen.

These commands make it super easy to add new firewall rules to allow connections to SQL Server components.

New-DbaFirewallRule -SqlInstance SRV1, SRV1\TEST

In the above example, New-DbaFirewallRule automatically configures the needed firewall rules for both the default instance and the instance named TEST on SRV1

New mapping command

If your databases have non-default or complex file structures, we've made it even easier to perform restores.

Now,you can easily use the file structure from one server as the mapping for a database restore on another server by using Get-DbaDbFileMapping.

$filemap = Get-DbaDbFileMapping -SqlInstance sql2016 -Database test
Get-ChildItem \\nas\backups\db\test | Restore-DbaDatabase -SqlInstance sql2019 -Database test -FileMapping $filemap.FileMapping

New cert expiration command

If you encrypt your network connections, Test-DbaComputerCertificateExpiration, can make it easy to check for certificates that are expiring soon.

Test-DbaComputerCertificateExpiration -ComputerName sql2016 -Threshold 90

This command will also check other types of certs, so you can use it as a general purpose cert expiration checker if you'd like.

Connection strings

If you work with Connection Strings a lot, be aware that some of the connstrings that Microsoft.Data.SqlClient generates are incompatible with good ol' System.Data.SqlClient.

If you use dbatools to generate connection strings, you can use the -Legacy switch to output System.Data compatible connection strings.

New-DbaConnectionString -SqlInstance sql2012 -Database pubs -Legacy

Get-DbatoolsError

We've created a new command that gives detailed information about dbatools-related errors. It gives the stack trace and everything.

Bug fixes

We also fixed a ton of bugs and are now down to less than 10 🔥🚒

Up Next

Up next, we're fixing dbatools.io. Sorry it's in shambles and the commands page is so outdated. docs.dbatools.io is still lookin fab (thanks @niphlod!)

Pull requests included in this release

dataplat/dbatools v1.1.0 on GitHub