This weekend SQL Saturday Dublin occurred. For those that don’t know SQL Saturdays are free conferences with local and international speakers providing great sessions in the Data Platform sphere.

Chrissy LeMaire and I presented our session PowerShell SQL Server: Modern Database Administration with dbatools. You can find slides and code here . We were absolutely delighted to be named Best Speaker which was decided from the attendees average evaluation.

Chrissy also won the Best Lightning talk for her 5 minute (technically 4 minutes and 55 seconds) presentation on dbatools as well

We thoroughly enjoy giving this presentation and I think it shows in the feedback we received.

History

We start with a little history of dbatools, how it started as one megalithic script Start-SQLMigration.ps1 and has evolved into (this number grows so often it is probably wrong by the time you read this) over 240 commands from 60 contributors

Requirements

We explain the requirements. You can see them here on the download page.

The minimum requirements for the Client are

• PowerShell v3
• SSMS / SMO 2008 R2

which we hope will cover a significant majority of peoples workstations.

The minimum requirements for the SQL Server are

• SQL Server 2000
• No PowerShell for pure SQL commands
• PowerShell v2 for Windows commands
• Remote PowerShell enabled for Windows commands

As you can see the SQL server does not even need to have PowerShell installed (unless you want to use the Windows commands). We test our commands thoroughly using a test estate that encompasses all versions of SQL from 2000 through to 2017 and whenever there is a vNext available we will test against that too.

We recommend though that you are using PowerShell v5.1 with SSMS or SMO for SQL 2016 on the client

Installation

We love how easy and simple the installation of dbatools is. As long as you have access to the internet (and permission from your companies security team to install 3rd party tools. Please don’t break your companies policies) you can simply install the module from the PowerShell Gallery using

Install-Module dbatools

If you are not a local administrator on your machine you can use the -Scope parameter

Install-Module dbatools -Scope CurrentUser

Incidentally, if you or your security team have concerns about the quality or trust of the content in the PowerShell Gallery please read this post which explains the steps that are taken when code is uploaded.

If you cannot use the PowerShell Gallery then you can use this line of code to install from GitHub

Invoke-Expression (Invoke-WebRequest https://dbatools.io/in)

There is a video on the download page showing the installation on a Windows 7 machine and also some other methods of installing the module should you need them.

Website

Next we visit the website dbatools.io and look at the front page. We have our regular joke about how Chrissy doesn’t want to present on migrations but I think they are so cool so she makes me perform the commentary on the video. (Don’t tell anyone but it also helps us to get in as many of the 240+ commands in a one hour session as well ). You can watch the video on the front page. You definitely should as you have never seen migrations performed so easily.

Then we talk about the comments we have received from well respected people from both SQL and PowerShell community members so you can trust that its not just some girl with hair and some bloke with a beard saying that its awesome.

Contributors

Probably my favourite page on the web-site is the team page showing all of the amazing fabulous wonderful people who have given their own time freely to make such a fantastic free tool. If we have contributors in the audience we do try to point them out. One of our aims with dbatools is to enable people to receive the recognition for the hard work that they put in and we do this via the team page, our LinkedIn company page as well as by linking back to the contributors in the help and the web-page for every command. I wish I could name check each one of you.

Thank You each and every one !!

Finding Commands

We then look at the command page and the new improved search page and demonstrate how you can use them to find information about the commands that you need and the challenges in keeping this all maintained during a period of such rapid expansion.

Demo

Then it is time for me to say this phrase. “Strap yourselves in, do up your seatbelts, now we are going to show 240 commands in the next 40 minutes. Are you ready!!”

Of course, I am joking, one of the hardest things about doing a one hour presentation on dbatools is the sheer number of commands that we have that we want to show off. Of course we have already shown some of them in the migration video above but we still have a lot more to show and there are a lot more that we wish we had time to show.

Backup and Restore

We start with a restore of one database and a single backup file using Restore-DbaDatabase showing you the easy to read warning that you get if the database already exists and then how to resolve that warning with the WithReplace switch

Then how to use it to restore an entire instance worth of backups to the latest available time by pointing Restore-DbaDatabase at a folder on a share

Then how to use Get-DbaDatabase to get all of the databases on an instance and pass them to Backup-DbaDatabase to back up an entire instance.

We look at the Backup history of some databases using Get-DbaBackupHistory and Out-GridView and examine detailed information about a backup file using Read-DbaBackupHeader.

We give thanks to Stuart Moore for his amazing work on these and several other backup and restore commands.

SPN’s

After a quick reminder that you can search for commands at the command line using Find-DbaCommand, we talk about SPNs and try to find someone, anyone, who actually likes working with SQL Server and SPNs and resolving the issues!!

Then we show Drew’s SPN commands Get-DbaSpn, Test-DbaSpn, Set-DbaSpn  and Remove-DbaSpn 

Holiday Tasks

We then talk about the things we ensure we run before going on holiday to make sure we leave with a warm fuzzy feeling that everything will be ok until we return :-

• Get-DbaLastBackup will show the last time the database had any type of backup.
  
• Get-DbaLastGoodCheckDb which shows the last time that a database had a successful DBCC CheckDb and how we can gather the information for all the databases on all of your instances in just one line of code
  
• Get-DbaDiskSpace which will show the disk information for all of the drives including mount points and whether the disk is in use by SQL
  

Testing Your Backup Files By Restoring Them

We ask how many people test their backup files every single day and Dublin wins marks for a larger percentage than some other places we have given this talk. We show Test-DbaLastBackup in action so that you can see the files being created because we think it looks cool (and you can see the filenames!) Chrissy has written a great post about how you can set up your own dedicated backup file test server

Free Space

We show how to gather the file space information using Get-DbaDatabaseFreespace and then how you can put that (or the results of any PowerShell command) into a SQL database table using Out-DbaDataTable and Write-DbaDataTable

SQL Community

Next we talk about how we love to take community members blog posts and turn them into dbatools commands.

We start with Jonathan Kehayias’s post about SQL Server Max memory (http://bit.ly/sqlmemcalc) and show Get-DbaMaxMemory , Test-DbaMaxMemory and Set-DbaMaxMemory

Then Paul Randal’s blog post about Pseudo-Simple Mode which inspired  Test-DbaFullRecoveryModel

We talked about getting backup history earlier but now we talk about Get-DbaRestoreHistory a command inspired by Kenneth Fishers blog post to show when a database was restored and which file was used.

Next a command from Thomas LaRock which he gave us for testing linked servers Test-DbaLinkedServerConnection.

Glenn Berrys diagnostic information queries  are available thanks to André Kamman and the commands Invoke-DbaDiagnosticQuery and Export-DbaDiagnosticQuery. The second one will output all of the results to csv files.

Adam Mechanic’s sp_whoisactive is a common tool in SQL DBA’s toolkit and can now be installed using Install-DbaWhoIsActive and run using Invoke-DbaWhoIsActive.

Awesome Contributor Commands

Then we try to fit in as many commands that we can from our fantastic contributors showing how we can do awesome things with just one line of PowerShell code

The awesome Find-DbaStoredProcedure which you can read more about here which in tests searched 37,545 stored procedures on 9 instances in under 9 seconds for a particular string.

Find-DbaOrphanedFile which enables you to identify the files left over from detaching databases.

Don’t know the SQL Admin password for an instance? Reset-SqlAdmin can help you.

It is normally somewhere around here that we finish and even though we have shown 32 commands (and a few more encapsulated in the Start-SqlMigration command) that is less than 15% of the total number of commands in the module!!!

Somehow, we always manage to fit all of that into 60 minutes and have great fun doing it. Thank you to everyone who has come and seen our sessions in Vienna, Utrecht, PASS PowerShell Virtual Group, Hanover, Antwerp and Dublin.

More

So you want to know more about dbatools ? You can click the link and explore the website

You can look at source code on GitHub

You can join us in the SQL Community Slack in the #dbatools channel

You can watch videos on YouTube

You can see a list of all of the presentations and get a lot of the slides and demos

If you want to see the slides and demos from our Dublin presentation you can find them here

Volunteers

Lastly and most importantly of all. SQL Saturdays are run by volunteers so massive thanks to Bob, Carmel, Ben and the rest of the team who ensured that SQL Saturday Dublin went so very smoothly