dbatools & sql on linux

TSQL2SDAY-150x150
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 πŸ‘

mac

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 🐧

2 thoughts on “dbatools & sql on linux

  1. Stephen Mandeville Reply

    Hello Chrissy and thanks for all the work on dbatools

    Do you have a link to the upcoming book

    “dbatools in a Month of Lunches”

    • Chrissy LeMaire Post authorReply

      Hey Stephen,
      Thanks so much for your interest. I do not have a link just yet but we should be added to Manning’s Early Access Program (MEAP), soon. Subscribe to the blog if you haven’t already and you’ll get an update when we go live πŸ‘

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.