A quick .NET notebook for exploring dbatools¶
Just to see what works :-)
Setting up the containers for the rest of the dbachecks notebooks¶
To be able to follow along with the rest of the notebooks, you will need to set up two containers.
This notebook will enable you to do that.
You will need to have Docker installed. The image is based on the SQL Server 2019 image so you will need to have docker set to use Linux Containers. Right click on the Docker icon in the notification area and if it says "Switch to Linux Containers" click to switch and wait for Docker to restart.
You will be able to run all of the code in the notebooks by creating the folder, credential and containers in this notebook and then you can click on the play button in each code block to run the code. Note - There are a few code blocks with the results already included which should not be run. They are to show you the results of a command that cannot be run against containers (setting up configuration for domain accounts for example)
Create the folders and the credential¶
The code below will create a directory called dbachecks-demo in your Documents folder and save a credential file for logging into the containers. You can alter the directory created by altering the $FolderPath but you will have to do this in every notebook.
In [17]:
$FolderPath = $Env:USERPROFILE + '\Documents\dbachecks'
########################################################
Write-Output "Creating Directory $FolderPath"
if(Test-Path $FolderPath){
Write-Output "Path $FolderPath exists already"
}else {
New-Item $FolderPath -ItemType Directory
}
Write-Output "Creating a credential file for the containers - Please don't do this in production"
$sqladminPassword = ConvertTo-SecureString 'dbatools.IO' -AsPlainText -Force
$cred = New-Object System.Management.Automation.PSCredential ('sqladmin', $sqladminpassword)
$Cred | Export-Clixml -Path $FolderPath\sqladmin.cred
Write-Output "Credential file created"
Creating Directory C:\Users\mrrob\Documents\dbachecks
Path C:\Users\mrrob\Documents\dbachecks exists already
Creating a credential file for the containers - Please don't do this in production
Credential file created
Containers¶
We are going to create two SQL 2019 containers using the sqldbawithabeard\dbachecks image from the Docker Hub.
Please copy the docker-compose.yml file from the folder that the Notebooks are in into the directory that was created above.
The first time it is going to pull the image sqldbawithabeard/dbachecksdemo from the Docker Hub. If you wish to do this first, you can run
docker pull sqldbawithabeard/dbachecksdemo
In [18]:
$FolderPath = $Env:USERPROFILE + '\Documents\dbachecks'
Set-Location $FolderPath
docker-compose up -d
dbachecks_SQL2019_1 is up-to-date
dbachecks_SQL2019-1_1 is up-to-date
All being well, you wil have something that looks like
Now we can start exploring with dbatools :-)
If you have not installed dbatools, it can be got from the PowerShell Gallery using Install-Module dbatools the code below will check for the module and either install it in your user profile or update it and Import it
In [19]:
if(Get-Module dbatools -ListAvailable){
Write-Output "Updating dbatools"
Update-Module dbatools
}else {
Write-Output "Installing dbatools in your user profile"
Install-Module dbatools -Scope CurrentUser
}
Import-Module dbatools
Updating dbatools
Now that is done, we can make a connection to our instances and see if we can connect to them
In [20]:
$FolderPath = $Env:USERPROFILE + '\Documents\dbachecks'
$SqlInstances = 'localhost,15592','localhost,15593'
$SqlCredential = Import-Clixml -Path $FolderPath\sqladmin.cred
$SQL1 = Connect-DbaInstance -SqlInstance $SqlInstances[0] -SqlCredential $SqlCredential
$SQL2 = Connect-DbaInstance -SqlInstance $SqlInstances[1] -SqlCredential $SqlCredential
Maybe get the logins on SQL1
In [21]:
Get-Dbalogin -SqlInstance $sql1 | Format-Table
ComputerName InstanceName SqlInstance Name LoginType CreateDate LastLogin
------------ ------------ ----------- ---- --------- ---------- ---------
localhost MSSQLSERVER f348131a8cc0 ##MS_PolicyEventProcessingLogin## SqlLogin 24/09/2019 14:21:53
localhost MSSQLSERVER f348131a8cc0 ##MS_PolicyTsqlExecutionLogin## SqlLogin 24/09/2019 14:21:53
localhost MSSQLSERVER f348131a8cc0 akamman SqlLogin 21/12/2019 14:33:58
localhost MSSQLSERVER f348131a8cc0 alevy SqlLogin 21/12/2019 14:33:59
localhost MSSQLSERVER f348131a8cc0 beardapp1 SqlLogin 21/12/2019 14:34:00
localhost MSSQLSERVER f348131a8cc0 beardapp2 SqlLogin 21/12/2019 14:34:00
localhost MSSQLSERVER f348131a8cc0 beardapp3 SqlLogin 21/12/2019 14:34:00
localhost MSSQLSERVER f348131a8cc0 beardapp4 SqlLogin 21/12/2019 14:34:00
localhost MSSQLSERVER f348131a8cc0 beardapp5 SqlLogin 21/12/2019 14:34:00
localhost MSSQLSERVER f348131a8cc0 beardapp6 SqlLogin 21/12/2019 14:34:00
localhost MSSQLSERVER f348131a8cc0 beardapp7 SqlLogin 21/12/2019 14:34:00
localhost MSSQLSERVER f348131a8cc0 beardapp8 SqlLogin 21/12/2019 14:34:00
localhost MSSQLSERVER f348131a8cc0 BUILTIN\Administrators WindowsGroup 24/09/2019 14:23:37
localhost MSSQLSERVER f348131a8cc0 clemaire SqlLogin 21/12/2019 14:33:58
localhost MSSQLSERVER f348131a8cc0 csilva SqlLogin 21/12/2019 14:33:59
localhost MSSQLSERVER f348131a8cc0 fatherjack SqlLogin 21/12/2019 14:33:59
localhost MSSQLSERVER f348131a8cc0 gsartori SqlLogin 21/12/2019 14:33:58
localhost MSSQLSERVER f348131a8cc0 jamrtin SqlLogin 21/12/2019 14:33:59
localhost MSSQLSERVER f348131a8cc0 NT AUTHORITY\NETWORK SERVICE WindowsUser 21/12/2019 14:33:37 07/02/2020 …
localhost MSSQLSERVER f348131a8cc0 NT AUTHORITY\SYSTEM WindowsUser 21/12/2019 14:33:37
localhost MSSQLSERVER f348131a8cc0 OldSa SqlLogin 08/04/2003 09:10:35 07/02/2020 …
localhost MSSQLSERVER f348131a8cc0 Reporting1 SqlLogin 21/12/2019 14:34:02
localhost MSSQLSERVER f348131a8cc0 Reporting2 SqlLogin 21/12/2019 14:34:02
localhost MSSQLSERVER f348131a8cc0 Reporting3 SqlLogin 21/12/2019 14:34:02
localhost MSSQLSERVER f348131a8cc0 Reporting4 SqlLogin 21/12/2019 14:34:02
localhost MSSQLSERVER f348131a8cc0 smelton SqlLogin 21/12/2019 14:33:58
localhost MSSQLSERVER f348131a8cc0 soneill SqlLogin 21/12/2019 14:33:59
localhost MSSQLSERVER f348131a8cc0 sqladmin SqlLogin 21/12/2019 14:33:50 07/02/2020 …
localhost MSSQLSERVER f348131a8cc0 Support1 SqlLogin 21/12/2019 14:34:01
localhost MSSQLSERVER f348131a8cc0 Support2 SqlLogin 21/12/2019 14:34:01
localhost MSSQLSERVER f348131a8cc0 Support3 SqlLogin 21/12/2019 14:34:01
localhost MSSQLSERVER f348131a8cc0 Support4 SqlLogin 21/12/2019 14:34:01
localhost MSSQLSERVER f348131a8cc0 Support5 SqlLogin 21/12/2019 14:34:01
localhost MSSQLSERVER f348131a8cc0 Support6 SqlLogin 21/12/2019 14:34:01
localhost MSSQLSERVER f348131a8cc0 tboggiano SqlLogin 21/12/2019 14:33:59
localhost MSSQLSERVER f348131a8cc0 thebeard SqlLogin 21/12/2019 14:33:59
localhost MSSQLSERVER f348131a8cc0 wdurkin SqlLogin 21/12/2019 14:33:58
Of course, you are not limited to just one instance with dbatools. Lets get the databases on all instances
In [22]:
Get-DbaDatabase -SqlInstance $SQL1 , $SQL2 | Format-Table
ComputerName InstanceName SqlInstance Name Status IsAccessible RecoveryModel LogReuseWaitStatus SizeMB
------------ ------------ ----------- ---- ------ ------------ ------------- ------------------ ------
localhost MSSQLSERVER f348131a8cc0 master Normal True Simple Nothing 8.5625
localhost MSSQLSERVER f348131a8cc0 tempdb Normal True Simple Nothing 40
localhost MSSQLSERVER f348131a8cc0 model Normal True Full Nothing 16
localhost MSSQLSERVER f348131a8cc0 msdb Normal True Simple 13 16
localhost MSSQLSERVER f348131a8cc0 AdventureWorks2017 Normal True Simple Nothing 336
localhost MSSQLSERVER f348131a8cc0 WideWorldImporters Normal True Simple Nothing …140625
localhost MSSQLSERVER f348131a8cc0 Northwind Normal True Simple Nothing 16
localhost MSSQLSERVER f348131a8cc0 pubs Normal True Simple Nothing 16
localhost MSSQLSERVER e4053cd3e5f8 master Normal True Simple Nothing 8.5625
localhost MSSQLSERVER e4053cd3e5f8 tempdb Normal True Simple Nothing 40
localhost MSSQLSERVER e4053cd3e5f8 model Normal True Full Nothing 16
localhost MSSQLSERVER e4053cd3e5f8 msdb Normal True Simple 13 16
localhost MSSQLSERVER e4053cd3e5f8 AdventureWorks2017 Normal True Simple Nothing 336
localhost MSSQLSERVER e4053cd3e5f8 WideWorldImporters Normal True Simple Nothing …140625
localhost MSSQLSERVER e4053cd3e5f8 Northwind Normal True Simple Nothing 16
localhost MSSQLSERVER e4053cd3e5f8 pubs Normal True Simple Nothing 16
or maybe all of the logins
In [23]:
Get-DbaLogin -SqlInstance $SQL1 , $SQL2 -ExcludeSystemLogin | Format-Table
ComputerName InstanceName SqlInstance Name LoginType CreateDate LastLogin
------------ ------------ ----------- ---- --------- ---------- ---------
localhost MSSQLSERVER f348131a8cc0 ##MS_PolicyEventProcessingLogin## SqlLogin 24/09/2019 14:21:53
localhost MSSQLSERVER f348131a8cc0 ##MS_PolicyTsqlExecutionLogin## SqlLogin 24/09/2019 14:21:53
localhost MSSQLSERVER f348131a8cc0 akamman SqlLogin 21/12/2019 14:33:58
localhost MSSQLSERVER f348131a8cc0 alevy SqlLogin 21/12/2019 14:33:59
localhost MSSQLSERVER f348131a8cc0 beardapp1 SqlLogin 21/12/2019 14:34:00
localhost MSSQLSERVER f348131a8cc0 beardapp2 SqlLogin 21/12/2019 14:34:00
localhost MSSQLSERVER f348131a8cc0 beardapp3 SqlLogin 21/12/2019 14:34:00
localhost MSSQLSERVER f348131a8cc0 beardapp4 SqlLogin 21/12/2019 14:34:00
localhost MSSQLSERVER f348131a8cc0 beardapp5 SqlLogin 21/12/2019 14:34:00
localhost MSSQLSERVER f348131a8cc0 beardapp6 SqlLogin 21/12/2019 14:34:00
localhost MSSQLSERVER f348131a8cc0 beardapp7 SqlLogin 21/12/2019 14:34:00
localhost MSSQLSERVER f348131a8cc0 beardapp8 SqlLogin 21/12/2019 14:34:00
localhost MSSQLSERVER f348131a8cc0 BUILTIN\Administrators WindowsGroup 24/09/2019 14:23:37
localhost MSSQLSERVER f348131a8cc0 clemaire SqlLogin 21/12/2019 14:33:58
localhost MSSQLSERVER f348131a8cc0 csilva SqlLogin 21/12/2019 14:33:59
localhost MSSQLSERVER f348131a8cc0 fatherjack SqlLogin 21/12/2019 14:33:59
localhost MSSQLSERVER f348131a8cc0 gsartori SqlLogin 21/12/2019 14:33:58
localhost MSSQLSERVER f348131a8cc0 jamrtin SqlLogin 21/12/2019 14:33:59
localhost MSSQLSERVER f348131a8cc0 NT AUTHORITY\NETWORK SERVICE WindowsUser 21/12/2019 14:33:37 07/02/2020 …
localhost MSSQLSERVER f348131a8cc0 NT AUTHORITY\SYSTEM WindowsUser 21/12/2019 14:33:37
localhost MSSQLSERVER f348131a8cc0 Reporting1 SqlLogin 21/12/2019 14:34:02
localhost MSSQLSERVER f348131a8cc0 Reporting2 SqlLogin 21/12/2019 14:34:02
localhost MSSQLSERVER f348131a8cc0 Reporting3 SqlLogin 21/12/2019 14:34:02
localhost MSSQLSERVER f348131a8cc0 Reporting4 SqlLogin 21/12/2019 14:34:02
localhost MSSQLSERVER f348131a8cc0 smelton SqlLogin 21/12/2019 14:33:58
localhost MSSQLSERVER f348131a8cc0 soneill SqlLogin 21/12/2019 14:33:59
localhost MSSQLSERVER f348131a8cc0 sqladmin SqlLogin 21/12/2019 14:33:50 07/02/2020 …
localhost MSSQLSERVER f348131a8cc0 Support1 SqlLogin 21/12/2019 14:34:01
localhost MSSQLSERVER f348131a8cc0 Support2 SqlLogin 21/12/2019 14:34:01
localhost MSSQLSERVER f348131a8cc0 Support3 SqlLogin 21/12/2019 14:34:01
localhost MSSQLSERVER f348131a8cc0 Support4 SqlLogin 21/12/2019 14:34:01
localhost MSSQLSERVER f348131a8cc0 Support5 SqlLogin 21/12/2019 14:34:01
localhost MSSQLSERVER f348131a8cc0 Support6 SqlLogin 21/12/2019 14:34:01
localhost MSSQLSERVER f348131a8cc0 tboggiano SqlLogin 21/12/2019 14:33:59
localhost MSSQLSERVER f348131a8cc0 thebeard SqlLogin 21/12/2019 14:33:59
localhost MSSQLSERVER f348131a8cc0 wdurkin SqlLogin 21/12/2019 14:33:58
localhost MSSQLSERVER e4053cd3e5f8 ##MS_PolicyEventProcessingLogin## SqlLogin 24/09/2019 14:21:53
localhost MSSQLSERVER e4053cd3e5f8 ##MS_PolicyTsqlExecutionLogin## SqlLogin 24/09/2019 14:21:53
localhost MSSQLSERVER e4053cd3e5f8 akamman SqlLogin 21/12/2019 14:33:58
localhost MSSQLSERVER e4053cd3e5f8 alevy SqlLogin 21/12/2019 14:33:59
localhost MSSQLSERVER e4053cd3e5f8 beardapp1 SqlLogin 21/12/2019 14:34:00
localhost MSSQLSERVER e4053cd3e5f8 beardapp2 SqlLogin 21/12/2019 14:34:00
localhost MSSQLSERVER e4053cd3e5f8 beardapp3 SqlLogin 21/12/2019 14:34:00
localhost MSSQLSERVER e4053cd3e5f8 beardapp4 SqlLogin 21/12/2019 14:34:00
localhost MSSQLSERVER e4053cd3e5f8 beardapp5 SqlLogin 21/12/2019 14:34:00
localhost MSSQLSERVER e4053cd3e5f8 beardapp6 SqlLogin 21/12/2019 14:34:00
localhost MSSQLSERVER e4053cd3e5f8 beardapp7 SqlLogin 21/12/2019 14:34:00
localhost MSSQLSERVER e4053cd3e5f8 beardapp8 SqlLogin 21/12/2019 14:34:00
localhost MSSQLSERVER e4053cd3e5f8 BUILTIN\Administrators WindowsGroup 24/09/2019 14:23:37
localhost MSSQLSERVER e4053cd3e5f8 clemaire SqlLogin 21/12/2019 14:33:58
localhost MSSQLSERVER e4053cd3e5f8 csilva SqlLogin 21/12/2019 14:33:59
localhost MSSQLSERVER e4053cd3e5f8 fatherjack SqlLogin 21/12/2019 14:33:59
localhost MSSQLSERVER e4053cd3e5f8 gsartori SqlLogin 21/12/2019 14:33:58
localhost MSSQLSERVER e4053cd3e5f8 jamrtin SqlLogin 21/12/2019 14:33:59
localhost MSSQLSERVER e4053cd3e5f8 NT AUTHORITY\NETWORK SERVICE WindowsUser 21/12/2019 14:33:37 07/02/2020 …
localhost MSSQLSERVER e4053cd3e5f8 NT AUTHORITY\SYSTEM WindowsUser 21/12/2019 14:33:37
localhost MSSQLSERVER e4053cd3e5f8 Reporting1 SqlLogin 21/12/2019 14:34:02
localhost MSSQLSERVER e4053cd3e5f8 Reporting2 SqlLogin 21/12/2019 14:34:02
localhost MSSQLSERVER e4053cd3e5f8 Reporting3 SqlLogin 21/12/2019 14:34:02
localhost MSSQLSERVER e4053cd3e5f8 Reporting4 SqlLogin 21/12/2019 14:34:02
localhost MSSQLSERVER e4053cd3e5f8 smelton SqlLogin 21/12/2019 14:33:58
localhost MSSQLSERVER e4053cd3e5f8 soneill SqlLogin 21/12/2019 14:33:59
localhost MSSQLSERVER e4053cd3e5f8 sqladmin SqlLogin 21/12/2019 14:33:50 07/02/2020 …
localhost MSSQLSERVER e4053cd3e5f8 Support1 SqlLogin 21/12/2019 14:34:01
localhost MSSQLSERVER e4053cd3e5f8 Support2 SqlLogin 21/12/2019 14:34:01
localhost MSSQLSERVER e4053cd3e5f8 Support3 SqlLogin 21/12/2019 14:34:01
localhost MSSQLSERVER e4053cd3e5f8 Support4 SqlLogin 21/12/2019 14:34:01
localhost MSSQLSERVER e4053cd3e5f8 Support5 SqlLogin 21/12/2019 14:34:01
localhost MSSQLSERVER e4053cd3e5f8 Support6 SqlLogin 21/12/2019 14:34:01
localhost MSSQLSERVER e4053cd3e5f8 tboggiano SqlLogin 21/12/2019 14:33:59
localhost MSSQLSERVER e4053cd3e5f8 thebeard SqlLogin 21/12/2019 14:33:59
localhost MSSQLSERVER e4053cd3e5f8 wdurkin SqlLogin 21/12/2019 14:33:58
In [ ]: