Why VS Code Increases my Productivity

Last week I was showing a co-worker some PowerShell code and he asked what the editor was that I was using. Visual Studio Code I said. Why do you use that? What does it do?

This is what I showed him

Runs on any Operating System

Code (as I shall refer to it) is free lightweight open source editor which runs on all the main operating systems. So you have the same experience in Linux as on Windows. So there is less to learn

Extensions

You can add new languages, themes, debuggers and tools from the extensions gallery to reduce the number of programmes you have open and the need to switch between programmes

You can add extensions using CTRL + SHIFT  + X and searching in the bar

01 - Extensions

or by going to the Extensions gallery searching for the extensions and copying the installation command

02 - extensions gallery.PNG

Debugging

There is a rich de-bugging experience built in

03 - debugging.PNG

You can learn about debugging from the official docs and Keith Hill wrote a blog post on Hey Scripting Guys about debugging PowerShell

Intellisense

An absolute must to make life simpler. Code has intellisense for PowerShell and T-SQL which I use the most but also for many more languages . Read more here

Git integration

I love the Git integration, makes it so easy to work with GitHub for me. I can see diffs, commit, undo commits nice and simply. Just open the root folder of the repository and its there

04 - git

This page will give you a good start on using git with Code

No distractions

With full screen mode (F11) or Zen mode (CTRL +K, Z) I can concentrate on coding and not worry about distractions

Stay in one programme and do it all

I have a Markdown document, a PowerShell script and a T-SQL script all in one Git repository and I can work on all of them and version control in one place. The screencast below also shows some of the new capabilities available in the insiders version I managed to leave the screen recording dialogue open as well, apologies and the mistake was deliberate!

I used the GitLens and SQL beautify extensions as well as the dbatools module in that demo

That’s why I am using Code more and more these days, hope it helps

Happy Automating!

 

 

Advertisements

Export SQL User Permissions to T-SQL script using PowerShell and dbatools

There are times when DBA’s are required to export database user permissions to a file. This may be for a number of reasons. Maybe for DR purposes, for auditing, for transfer to another database or instance. Sometimes we need to create a new user with the same permissions as another user or perhaps nearly the same permissions. I was having a conversation with my good friend and MVP Cláudio Silva and we were talking about how Export-SqlUser from dbatools could help in these situations and he suggested that I blogged about it so here it is.

The dbatools module (for those that don’t know) is a PowerShell module written by amazing folks in the community designed to make administrating your SQL Server significantly easier using PowerShell. The instructions for installing it are available here It comprises of 182 separate commands at present

Cláudio wrote Export-SqlUser to solve a problem. You should always start with Get-Help whenever you are starting to use a new PowerShell command

Get-Help Export-SqlUser -ShowWindow

01 - get help.PNG

The command exports users creation and its permissions to a T-SQL file or host. Export includes user, create and add to role(s), database level permissions, object level permissions and also the Create Role statements for any roles, although the script does not create IF NOT EXISTS statements which would be an improvement. It also excludes the system databases so if you are scripting users who need access to those databases then that needs to be considered. Cláudio is aware of these and is looking at improving the code to remove those limitations.

It takes the following parameters
  • SqlInstance
    The SQL Server instance name. SQL Server 2000 and above supported.
  • User
    Export only the specified database user(s). If not specified will export all users from the database(s)
  • DestinationVersion
    Which SQL version the script should be generated using. If not specified will use the current database compatibility level
  • FilePath
    The filepath to write to export the T-SQL.
  • SqlCredential
    Allows you to login to servers using alternative credentials
  • NoClobber
    Do not overwrite the file
  • Append
    Append to the file
  • Databases
    Not in the help but a dynamic parameter allowing you to specify one or many databases

Lets take a look at it in action

Export-SqlUser -SqlInstance SQL2016N2 -FilePath C:\temp\SQL2016N2-Users.sql
Notepad C:\temp\SQL2016N2-Users.sql

02 - Export user server.PNG

Lets take a look at a single database

Export-SqlUser -SqlInstance SQL2016N2 -FilePath C:\temp\SQL2016N2-Fadetoblack.sql -Databases Fadetoblack
notepad C:\temp\SQL2016N2-Fadetoblack.sql

03 single database.PNG

This is so cool and so easy. It is possible to do this in T-SQL. I found this script on SQLServerCentral for example which is 262 lines and would then require some mouse action to save to a file

We can look at a single user as well. Lets see what Lars Ulrich can see on the FadeToBlack database

04 - export lars.PNG

USE [FadetoBlack]
GO
CREATE USER [UlrichLars] FOR LOGIN [UlrichLars] WITH DEFAULT_SCHEMA=[dbo]
GO
GRANT CONNECT TO [UlrichLars]
GO
DENY INSERT ON [dbo].[Finances] TO [UlrichLars]
GO
DENY SELECT ON [dbo].[RealFinances] TO [UlrichLars]
GO
GRANT SELECT ON [dbo].[Finances] TO [UlrichLars]
GO

So he can select data from the Finances table but cannot insert and cannot read the RealFinances data. Now lets suppose a new manager comes in and he wants to be able to look at the data in this database. As the manager though he wants to be able to read the RealFinances table  and insert into the Finances table. He requests that we add those permissions to the database. We can create the T-SQL for Lars user and then do a find and replace for UlrichLars with TheManager , DENY INSERT ON [dbo].[Finances] with GRANT INSERT ON [dbo].[Finances] and DENY SELECT ON [dbo].[RealFinances] with GRANT SELECT ON [dbo].[RealFinances] and save to a new file.

$LarsPermsFile = 'C:\temp\SQL2016N2-Lars-Fadetoblack.sql'
$ManagerPermsFile = 'C:\temp\SQL2016N2-Manager-Fadetoblack.sql'
Export-SqlUser -SqlInstance SQL2016N2 -FilePath $LarsPermsFile -User UlrichLars -Databases Fadetoblack
$ManagerPerms = Get-Content $LarsPermsFile
## replace permissions
$ManagerPerms = $ManagerPerms.Replace('DENY INSERT ON [dbo].[Finances]','GRANT INSERT ON [dbo].[Finances]')
$ManagerPerms = $ManagerPerms.Replace('DENY SELECT ON [dbo].[RealFinances]','GRANT SELECT ON [dbo].[RealFinances]')
$ManagerPerms = $ManagerPerms.Replace('UlrichLars','TheManager')
Set-Content -path $ManagerPermsFile -Value $ManagerPerms

I will open this in Visual Studio Code Insiders using

code-insiders $LarsPermsFile , $ManagerPermsFile

if you are not using the insiders preview remove the “-insiders”

05 - code insiders.PNG

You can right click on the Lars file and click select for compare and then right click on the Managers file and select compare with Lars File and get a nice colour coded diff

06 - compare.gif

Perfect, we can run that code and complete the request. When we impersonate Lars we get

07 - lars.PNG

but when we run as the manager we get

08 - the manager.PNG

 

Excellent! All is well.

It turns out that there is another Fadetoblack database on a SQL2000 instance which for reasons lost in time never had its data imported into the newer database. It is still used for reporting purposes. The manager needs to have the same permissions as on the SQL2016N2 instance. Obviously the T-SQL we have just created will not work as that syntax did not exist for SQL 2000 but Cláudio has thought of that too. We can use the DestinationVersion parameter to create the SQL2000 (2005,2008/20008R2,2012,2014,2016) code

We just run

Export-SqlUser -SqlInstance SQL2016N2 -Databases FadetoBlack -User TheManager  -FilePath C:\temp\S
QL2016N2-Manager-2000.sql  -DestinationVersion SQLServer2000
Notepad C:\temp\SQL2016N2-Manager-2000.sql

and our SQL2000 compatible code is created

09- manager 2000.PNG

Simply awesome. Thank you Cláudio

Happy Automating

NOTE – The major 1.0 release of dbatools due in the summer 2017 may have breaking changes which will stop the above code from working. There are also new commands coming which may replace this command. This blog post was written using dbatools version 0.8.942 You can check your version using

 Get-Module dbatools

and update it using an Administrator PowerShell session with

 Update-Module dbatools

You may find that you get no output from Update-Module as you have the latest version. If you have not installed the module from the PowerShell Gallery using

Install-Module dbatools

Then you can use

Update-dbatools

Testing SQL Server Access to a share with PowerShell using dbatools

A good security practice is to backup our SQL Servers to a network share but not allow users to be able to browse the share. How can we ensure that our SQL Server has access or test it if it has been set up by someone else?

Lets set this up.

First lets create a share for our backups

$FileShareParams=@{
Name='SQLBackups'
Description='The Place for SQL Backups'
SourceVolume=(Get-Volume-DriveLetterD)
FileServerFriendlyName='beardnuc'
}
New-FileShare @FileShareParams

This will create us a share called SQLBackups on the D drive of the server beardnuc, but without any permissions, lets grant permissions to everyone

$FileSharePermsParams=@{
 Name = 'SQLBackups'
 AccessRight = 'Modify'
 AccountName = 'Everyone'}
Grant-FileShareAccess @FileSharePermsParams

01 file share.PNG

The share is created and I can access it and create a file

02 - create a file.PNG

and as we can see the permissions are granted for everyone

03 -permissions.PNG

OK, that’s not what we want so lets revoke that permission.

Revoke-FileShareAccess Name SQLBackups AccountName 'Everyone'

04 revoked.PNG

Now lets add permissions just for our SQL Server Service Accounts

$FileSharePermsParams = @{
Name = 'SQLBackups'
AccessRight = 'Modify'
AccountName = 'SQL_DBEngine_Service_Accounts
}
Grant-FileShareAccess @FileSharePermsParams 
and explicitly deny our DBA user accounts from accessing them.
$BlockFileShareParams = @{
Name = 'SQLBackups'
AccountName = 'SQL_DBAs_The_Cool_Ones'
}
Block-FileShareAccess @BlockFileShareParams
In the GUI our permissions look like this
and when I try to access as THEBEARD\Rob I get this

07 -no permissions.PNG

So how can I check that I have access from my SQL Server? Sure I could get the password of the SQL Service account and run a process as that account, not saying that’s a good idea but it could be done. Of course it couldn’t be done if you are using Managed Service Accounts or Group Managed Service Accounts but there is a way

Enter dbatools to the rescue 😉 The dbatools module (for those that don’t know) is a PowerShell module written by amazing folks in the community designed to make administrating your SQL Server significantly easier using PowerShell. The instructions for installing it are available here It comprises of 182 separate commands at present

There is a command called Test-SqlPath As always start with Get-Help

Get-Help Test-SqlPath -Full

08 - get help.PNG

So it uses master.dbo.xp_fileexist to determine if a file or directory exists, from the perspective of the SQL Server service account, has three parameters Sqlserver, Path and SqlCredential for SQL Authentication. Of course if that stored procedure is disabled on your estate then this command will not be of use to you. With that in mind, lets run it and see what it does
Test-SqlPath -SqlServer sql2016n1 -Path \\beardnuc\SQLBackups
09 - path test

That’s good I have access, lets back a database up

Backup-SqlDatabase -ServerInstance SQL2016N1 -Database DBA-Admin -CopyOnly -BackupAction Database -BackupFile '\\BeardNuc\SQLBackups\Test-DBA-Admin.bak'
Ah, I cant show you as I don’t have access. Better get in touch with the data centre admin to check 😉 Luckily, I am my own data centre admin and have another account I can use 🙂
10 - check

So what if we want to test all of our servers for access to the new share? I tried this

$SQLServers = (Get-VM -ComputerName beardnuc).Where{$_.Name -like '*SQL*' -and $_.Name -notlike 'SQL2008Ser2008'}.Name
Test-SqlPath -SqlServer $SQLServers -Path '\\BeardNuc\SQLBackups'
but unfortunately I hit an error
11 - error.PNG
It seems that at the moment (version 0.8.942) this command only accepts a single server. This is what you should do if you find either a bug or have an idea for dbatools. Raise an issue on Github
Navigate to the GitHub repository and click on issues. I generally search for the command name in the issues to see if someone else has beaten me to it
12 - issues
If those issues don’t match yours then click the green New Issue button
There is a template to fill in which asks you to specify your Windows, PowerShell and SQL versions with the commands that you need to do so included. Please do this and paste the results in as it will help the folks to replicate the issues in the case of more complicated  bugs
I created this issue with a potential fix as well, you don’t have to do that, just letting the folks know is good enough
Until that issue is resolved, you can check all of your servers as follows
$SQLServers=(Get-VM -ComputerName beardnuc).Where{$_.Name -like '*SQL*' -and $_.Name -notlike 'SQL2008Ser2008'}.Name
foreach($Server in $SQLServers)
{
$Test = Test-SqlPath -SqlServer $Server -Path '\\BeardNuc\SQLBackups'
[PSCustomObject]@{
Server = $Server
Result = $Test
}
}
13 - servers.PNG
and if I remove one of the service accounts from the group and restart the service an run the command again
14 - one fails.PNG
So that’s how to use dbatools to check that your SQL Server have access to a Network share and also how to create an issue on GitHub for dbatools and help it to get even better

Happy Automating

NOTE – The major 1.0 release of dbatools due in the summer 2017 may have breaking changes which will stop the above code from working. There are also new commands coming which may replace this command. This blog post was written using dbatools version 0.8.942 You can check your version using

 Get-Module dbatools

and update it using an Administrator PowerShell session with

 Update-Module dbatools

You may find that you get no output from Update-Module as you have the latest version. If you have not installed the module from the PowerShell Gallery using

Install-Module dbatools

Then you can use

Update-dbatools

Using Pester with Get-DbaLastGoodCheckDb from dbatools

In my last post I showed Get-DbaLastGoodCheckDb  from dbatools. This module is a community based project written by excellent, brilliant people in their own time and available to you free. To find out more and how to use and install it visit https://dbatools.io

In a similar fashion to my post about using Pester with Test-DBALastBackup I thought I would write some Pester tests for Get-DbaLastGoodCheckDb as well

Pester provides a framework for running unit tests to execute and validate PowerShell commands from within PowerShell. Pester consists of a simple set of functions that expose a testing domain-specific language (DSL) for isolating, running, evaluating and reporting the results of PowerShell commands.

First we will use Test Cases again to quickly test a number of instances and see if any servers have a database which does not have a successful DBCC Checkdb. We will need to use the -Detailed parameter of Get-DbaLastGoddCheckDb so that we can access the status property. I have filled the $SQLServers variable with the names of my SQLServers in my lab that are running and are not my broken SQL2008 box.

The status property will contain one of three statements

  • Ok (This means that a successful test was run in the last 7 days
  • New database, not checked yet
  • CheckDb should be performed

We want to make sure that none of the results from the command have the second two statements. We can do this by adding two checks in the test and if either fails then the test will fail.

 Describe "Testing Last Known Good DBCC" {
$SQLServers = (Get-VM -ComputerName beardnuc | Where-Object {$_.Name -like '*SQL*' -and $_.Name -ne 'SQL2008Ser2008' -and $_.State -eq 'Running'}).Name
$testCases= @()
$SQLServers.ForEach{$testCases += @{Name = $_}}
It "<Name> databases have all had a successful CheckDB within the last 7 days" -TestCases $testCases {
Param($Name)
$DBCC = Get-DbaLastGoodCheckDb -SqlServer $Name -Detailed
$DBCC.Status -contains 'New database, not checked yet'| Should Be $false
$DBCC.Status -contains 'CheckDb should be performed'| Should Be $false
}
}

We can save this as a .ps1 file (or we can add it to an existing Pester test file and call it will Invoke-Pester or just run it in PowerShell

05 - dbcc pester

As you can see you will still get the same warning for the availability group databases and we can see that SQL2012Ser08AG1 has a database whose status is CheckDB should be performed and SQL2012Ser08AGN2 has a database with a status of New database, not checked yet

That’s good, but what if we run our DBCC Checkdbs at a different frequency and want to test that? We can also test if the databases have had a successful DBCC CheckDb using the LastGoodCheckDb property which will not contain a Null if there was a successful DBCC CheckDb. As Pester is PowerShell we can use

($DBCC.LastGoodCheckDb -contains $null)

and we can use Measure-Object to get the maximum value of the DaysSinceLastGoodCheckdb property like this

($DBCC | Measure-Object -Property  DaysSinceLastGoodCheckdb -Maximum).Maximum
If we put those together and want to test for a successful DBCC Check DB in the last 3 days we have a test that looks like
Describe "Testing Last Known Good DBCC" {
$SQLServers = (Get-VM -ComputerName beardnuc | Where-Object {$_.Name -like '*SQL*' -and $_.Name -ne 'SQL2008Ser2008' -and $_.State -eq 'Running'}).Name
$testCases= @()
$SQLServers.ForEach{$testCases += @{Name = $_}}
It "<Name> databases have all had a successful CheckDB" -TestCases $testCases {
Param($Name)
$DBCC = Get-DbaLastGoodCheckDb -SqlServer $Name -Detailed
($DBCC.LastGoodCheckDb -contains $null) | Should Be $false
}
It "<Name> databases have all had a CheckDB run in the last 3 days" -TestCases $testCases {
Param($Name)
$DBCC = Get-DbaLastGoodCheckDb -SqlServer $Name -Detailed
($DBCC | Measure-Object -Property  DaysSinceLastGoodCheckdb -Maximum).Maximum | Should BeLessThan 3
}
}
and when we call it with invoke-Pester it looks like
06 - dbcc pester.PNG
That’s good but it is only at an instance level. If we want our Pester Test to show results per database we can do that like this
Describe "Testing Last Known Good DBCC" {
$SQLServers = (Get-VM -ComputerName beardnuc | Where-Object {$_.Name -like '*SQL*' -and $_.Name -ne 'SQL2008Ser2008' -and $_.State -eq 'Running'}).Name
foreach($Server in $SQLServers)
{
$DBCCTests = Get-DbaLastGoodCheckDb -SqlServer $Server -Detailed
foreach($DBCCTest in $DBCCTests)
{
It "$($DBCCTest.Server) database $($DBCCTest.Database) had a successful CheckDB"{
$DBCCTest.Status | Should Be 'Ok'
}
It "$($DBCCTest.Server) database $($DBCCTest.Database) had a CheckDB run in the last 3 days" {
$DBCCTest.DaysSinceLastGoodCheckdb | Should BeLessThan 3
}
It "$($DBCCTest.Server) database $($DBCCTest.Database) has Data Purity Enabled" {
$DBCCTest.DataPurityEnabled| Should Be $true
}
}
}
}
We gather the SQL instances into an array in the same way and this time we loop through each one, put the results of Get-DbaLastGoodCheckDb for that instance into a variable and then iterate through each result and check that the status is Ok, the DaysSinceLastGoodCheckDb is less than 3 and the DataPurityEnabled is true and we have
07 - dbcc pester.PNG

 

You can look at my previous posts on using Pester to see examples of creating XML files or HTML reports from the results of the tests.

Hopefully, as you have read this you have also thought of other ways that you can use Pester to validate the state of your environment. I would love to know how and what you do.

Happy Automating

NOTE – The major 1.0 release of dbatools due in the summer 2017 may have breaking changes which will stop the above code from working. There are also new commands coming which may replace this command. This blog post was written using dbatools version 0.8.942 You can check your version using

 Get-Module dbatools

and update it using an Administrator PowerShell session with

 Update-Module dbatools

You may find that you get no output from Update-Module as you have the latest version. If you have not installed the module from the PowerShell Gallery using

Install-Module dbatools

Then you can use

Update-dbatools

 

 

Getting SQLServers Last Known Good DBCC Checkdb with PowerShell and dbatools

As good SQL Server DBA’s we want to ensure that our databases are regularly checked for consistency by running DBCC CheckDB. This will be frequently scheduled using an Agent Job or by using Ola Hallengrens Maintenance Solution

We can check for the last known good DBCC Check using the undocumented DBCC DBINFO(DBNAME) WITH TABLERESULTS You can see the last known good DBCC Check around about row 50

00 - Using TSQL.PNG

This makes parsing the information a bit more tricky and whilst you could use sp_MSForEachDB to iterate through the databases that doesn’t always work as you expect as Aaron Bertrand explains

Of course, I am going to use PowerShell and also the dbatools module This module is a community based project written by excellent, brilliant people in their own time and available to you free. To find out more and how to use and install it visit https://dbatools.io

In the module there is a command called Get-DbaLastGoodCheckDb This command was created by Jakob Bindslet. You can find Jakob on his blog and on LinkedIn.

As always, you start with any PowerShell command by using Get-Help

get-help Get-DbaLastGoodCheckDb -ShowWindow

00a - get help.PNG

This command has three parameters Sqlserver, Credential and Detailed. Lets see what it looks like

Get-DbaLastGoodCheckDb -SqlServer SQLvNextN2

01 - One server

It returns an object with the server name, database name and the time and date of the last known good checkdb for every database on the server. What happens if we use the detailed parameter?

Get-DbaLastGoodCheckDb -SqlServer SQLvNextN2 -Detailed

 

02 - one server detailed.PNG

This time we get more information. The server name, database name, when the database was created, the last good DBCC Checkdb, how long since the database was created, how long since the last known good DBCC Checkdb, a status and a Data Purity enabled flag. If you look at the image above it shows that the DBA_Admin database has a status of “New database, not checked yet” even though it has a date for the last known good DBCC CheckDb. This is because it was restored after this server was upgrade from CTP 1.3 to CTP 1.4 and there has not yet been a DBCC CheckDb run yet. The system databases have a status of “CheckDb should be performed”. This is because the last known good DBCC CheckDb is more than 7 days ago. Lets run a DBCC CheckDb and check again

02a - one server.PNG

This time the status has changed to OK for all of the databases 🙂

We can pass an array of SQL servers to this command as well and check multiple servers at the same time. In this example, I am querying my Hyper-V server for all VMs with SQL in the name,except for my broken SQL2008 box ,that are running. I love PowerShell’s Out-GridView command for many reasons so lets use that. you can filter quickly and easily in the top bar.

$SQLServers = (Get-VM -ComputerName beardnuc | Where-Object {$_.Name -like '*SQL*' -and $_.Name -ne 'SQL2008Ser2008' -and $_.State -eq 'Running'}).Name
Get-DbaLastGoodCheckDb -SqlServer $SQLServers -Detailed | Out-GridView

03 - many servers ogv.PNG

As you can see, you get a warning for secondary availability group databases. It’s quick too. In my lab of 10 servers and 125 databases ranging from SQL2005 to SQL vNext it runs in a little under  5 seconds. This command is not compatible with SQL2000 servers.

04 - measure comand.PNG

It is important to remember that as this script uses the DBCC DBINFO() WITH TABLERESULTS, there are several known weak points, including:

– DBCC DBINFO is an undocumented feature/command.
– The LastKnowGood timestamp is updated when a DBCC CHECKFILEGROUP is performed.
– The LastKnowGood timestamp is updated when a DBCC CHECKDB WITH PHYSICAL_ONLY is performed.
– The LastKnownGood timestamp does not get updated when a database in READ_ONLY.

Databases created prior to SQL2005 and then upgraded to SQL 2005 or above need to have DBCC CheckDb run once with the DATA_PURITY option to ensure that the DATA_PURITY check ,which look for column values where the value is outside the valid range of values for the column’s data type, is run by default when DBCC CheckDB is run. This is explained more fully by Paul Randal here and Ken Simmons here The Data Purity Enabled flag from the command will show false if the data purity check is not being performed. This should be resolved by running DBCC CheckDB with DATA_PURITY option as explained here

Now with one line of PowerShell code you can check the last time a DBCC CheckDb was run for each database on one or more instances. The beauty of PowerShell is that an object is returned which you can use in any number of ways as shown in a previous post

Happy Automating

NOTE – The major 1.0 release of dbatools due in the summer 2017 may have breaking changes which will stop the above code from working. There are also new commands coming which may replace this command. This blog post was written using dbatools version 0.8.942 You can check your version using

Get-Module dbatools

and update it using an Administrator PowerShell session with

Update-Module dbatools

You may find that you get no output from Update-Module as you have the latest version. If you have not installed the module from the PowerShell Gallery using

Install-Module dbatools

Then you can use

Update-dbatools

 

Test the SQL Server database collation with PowerShell and dbatools

If your server collation is different to your database collation then you may find that you get an error similar to this

Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Latin1_General_CI_AS” in the equal to operation.

when your queries use the tempdb

It would be useful to be able to quickly test if that may be the case and with the dbatools module you can. There is a Test-DbaDatabaseCollation command which will do just that. This page will show you how to install dbatools if you have not already got it

As always you should start with Get-Help when looking at a PowerShell command

Get-Help TestDbaDatabaseCollation -ShowWindow

 

01 - Get Help.PNG

There are only 3 parameters Sqlserver, Credential and detailed

Lets start with SQLServer

Test-DbaDatabaseCollation -SqlServer SQLvNextN2

this gives a quick and simple output showing the server name, database name and an IsEqual property

02 - test server

So in this example we can see that the WideWorldImporters database does not have the same collation as the server. If we only wanted to see information about databases with a collation that does not match the server then we could use

(Test-DbaDatabaseCollation -SqlServer SQLvNextN2).Where{$_.IsEqual -eq $false}

03 - equals false

That doesn’t give us any further information though. There is the detailed parameter as well. Lets see what that does

 Test-DbaDatabaseCollation -SqlServer SQLvNextN2 -Detailed

04 - detailed.PNG

This time we get the server name, server collation, database name , database collation and the IsEqual property. This is a collection of objects so we are not bound be just seeing them on the screen we can use them as I blogged about here

For example

 ## Output to a file
Test-DbaDatabaseCollation -SqlServer SQLvNextN2 -Detailed |Out-File C:\Temp\CollationCheck.txt
## Output to CSV
Test-DbaDatabaseCollation -SqlServer SQLvNextN2 -Detailed |Export-Csv  C:\temp\CollationCheck.csv -NoTypeInformation
<## Output to JSON
Test-DbaDatabaseCollation -SqlServer SQLvNextN2 -Detailed | ConvertTo-Json | Out-file c:\temp\CollationCheck.json
## Look at the files
notepad C:\temp\CollationCheck.json
notepad C:\temp\CollationCheck.csv
notepad C:\temp\CollationCheck.txt

Of course, you will probably want to test more than one server at a time. Lets pass an array of servers and see what happens

$SQLServers = (Get-VM -ComputerName beardnuc | Where-Object {$_.Name -like '*SQL*' -and $_.Name -ne 'SQL2008Ser2008' -and $_.State -eq 'Running'}).Name
# Test Db collation
Test-DbaDatabaseCollation -SqlServer $SQLServers -Detailed 
05 - servers.PNG

In this example, I am querying my Hyper-V server for all VMs with SQL in the name,except for my broken SQL2008 box ,that are running. I love PowerShell’s Out-GridView command for many reasons. The ability to sort by columns quickly and simply is one of them. Lets add that to the code and sort by the IsEquals column

$SQLServers = (Get-VM -ComputerName beardnuc | Where-Object {$_.Name -like '*SQL*' -and $_.Name -ne 'SQL2008Ser2008' -and $_.State -eq 'Running'}).Name
# Test Db collation
Test-DbaDatabaseCollation -SqlServer $SQLServers -Detailed | Out-GridView
06 - servers ogv.PNG

Excellent, that works a treat. How about Linux? Does this work if SQL is running on Linux? We will have to use the credential parameter as we need SQL Authentication. this time I have used the Format-Table command to format the output.

$cred = Get-Credential
Test-DbaDatabaseCollation -SqlServer LinuxvNextCTP14 -Credential $cred -Detailed | Format-Table -AutoSize
07 - Linux.PNG

Lets add some Pester tests. If we want to test a list of servers and see if any of their databases have an incorrect collation we can simply test if the IsEquals flag contains a false.

We can do this using TestCases. Test cases allow Pester to loop through a collection of ‘things’ The testcases parameter takes an array of hashtables. This all sounds very complicated to those unclear about PowerShell but here some code to do it.

$SQLServers = (Get-VM -ComputerName beardnuc | Where-Object {$_.Name -like '*SQL*' -and $_.Name -ne 'SQL2008Ser2008' -and $_.State -eq 'Running'}).Name
$testCases= @()
$SQLServers.ForEach{$testCases += @{Name = $_}}

The first line gathers the list of SQL Servers from the Hyper-V as before. You can get this from a text file, csv, Active Directory, CMS, registered servers list. The second line initiates the TestCases array and the third line iterates through the list of servers and adds a hashtable to the TestCases array

To make use of the test cases we have to use the -TestCases parameter in our It block of our Pester Test and add a param() so that the test knows where to get the values from. To add the value from the test cases into the title of the test we need to reference it inside <>

If you want to learn more about Pester. I highly recommend The Pester Book by Don Jones and Adam Bertram

Here is the code

Describe "Testing Database Collation" {
    $SQLServers = (Get-VM -ComputerName beardnuc | Where-Object {$_.Name -like '*SQL*' -and $_.Name -ne 'SQL2008Ser2008' -and $_.State -eq 'Running'}).Name
    $testCases= @()
    $SQLServers.ForEach{$testCases += @{Name = $_}}
    It "<Name> databases have the right collation" -TestCases $testCases {
        Param($Name)
        $Collation = Test-DbaDatabaseCollation -SqlServer $Name
        $Collation.IsEqual -contains $false | Should Be $false
    }
}

If we save that as a PowerShell file, we can call it with Invoke-Pester

08 - Servers Pester.PNG

which shows which servers do not have databases with the correct collation. This may be all that is required but what about if you want to check each database on each server with Pester?

I could not see a way to do this with TestCases so I reverted to PowerShell. Pester is just PowerShell code after all.

Describe "Testing Database Collation" {
    $SQLServers = (Get-VM -ComputerName beardnuc | Where-Object {$_.Name -like '*SQL*' -and $_.Name -ne 'SQL2008Ser2008' -and $_.State -eq 'Running'}).Name
    foreach($Server in $SQLServers)
    {
        $CollationTests = Test-DbaDatabaseCollation -SqlServer $Server
        foreach($CollationTest in $CollationTests)
        {
            It "$($Collationtest.Server) database $($CollationTest.Database) should have the correct collation" {
                $CollationTest.IsEqual | Should Be $true
            }
        }
    }
}

In this example, we again gather the names of our SQL servers and then iterate through them. Then set the results of the Test-DBADatabaseCollation to a variable and iterate through each of the results and test the IsEquals property. We can save that as a file and call it with Invoke-Pester and this time it looks like

09 - Individual databases.PNG

Excellent we can quickly and easily see which database on which server doesnot have a matching collation. We cant see in the results of the Pester test what collation it should be though. Lets do that as well.

This time we need to use the Detailed parameter and test that the ServerCollation matches the DatabaseCollation. This will enable Pester to display that information to us. Here is the code

Describe "Testing Database Collation" {
    $SQLServers = (Get-VM -ComputerName beardnuc | Where-Object {$_.Name -like '*SQL*' -and $_.Name -ne 'SQL2008Ser2008' -and $_.State -eq 'Running'}).Name
    foreach($Server in $SQLServers)
    {
        $CollationTests = Test-DbaDatabaseCollation -SqlServer $Server -Detailed
        foreach($CollationTest in $CollationTests)
        {
            It "$($Collationtest.Server) database $($CollationTest.Database) should have the correct collation of $($CollationTest.ServerCollation)" {
                $CollationTest.DatabaseCollation | Should Be $CollationTest.ServerCollation
            }
        }
    }
}
and if we save that as a file and call it with invoke-Pester (you can just run the code using PowerShell as well) it looks like this
10 - full test.PNG

Now Pester shows us what collation it is expecting and what the collation of the database is as well when it fails the test. (I love the little arrow showing where the difference is!)

Hopefully this post has shown you how you can use Test-DbaDatabaseCollation from the dbatools module to test your servers and combine that with Pester. If you have any questions about the dbatools module go and ask in the dbatools channel in the SQL Community Slack channel

Max Length of a column in a DataTable in PowerShell

Whilst I was writing my Test-DbaLastBackup Posts I ran into a common error I get when importing datatables into a database

I was using this table

01 - table

and when I tried to add the results of the Test-DbaLastBackup I got this

02 -error.PNG

Exception calling “WriteToServer” with “1” argument(s): “The given value of type String from the data source cannot be converted to type nvarchar of the
specified target column.”
At line:356 char:4
+             $bulkCopy.WriteToServer($InputObject)
+             ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : InvalidOperationException

Hmm, it says that it can’t convert a string to a nvarchar, that doesn’t sound right.To find out what was happening I used a little bit of code that I use every single day

 $Error[0] | Fl -force

All errors from your current session are stored in the $error array so [0] accesses the most recent one and fl is an alias for Format-List and the force switch expands the object. This is what I saw

03 expanded error.PNG

System.Management.Automation.MethodInvocationException: Exception calling “WriteToServer” with “1” argument(s): “The given value of
type String from the data source cannot be converted to type nvarchar of the specified target column.” —>
System.InvalidOperationException: The given value of type String from the data source cannot be converted to type nvarchar of the
specified target column. —> System.InvalidOperationException: String or binary data would be truncated.

String or binary data would be truncated. OK that makes sense, one of my columns has larger data than the destination column but which one? Lets take a look at some of the data

SourceServer  : SQL2016N3
TestServer    : SQL2016N1
Database      : RidetheLightning
FileExists    : Skipped
RestoreResult : Restore not located on shared location
DbccResult    : Skipped
SizeMB        : 4.08
BackupTaken   : 3/19/2017 12:00:03 AM
BackupFiles   : C:\MSSQL\Backup\SQL2016N3\RidetheLightning\FULL\SQL2016N3_RidetheLightning_FULL_20170319_000003.bak

SourceServer  : SQL2016N3
TestServer    : SQL2016N1
Database      : TheCallofKtulu
FileExists    : Skipped
RestoreResult : Restore not located on shared location
DbccResult    : Skipped
SizeMB        : 4.08
BackupTaken   : 3/19/2017 12:00:04 AM
BackupFiles   : C:\MSSQL\Backup\SQL2016N3\TheCallofKtulu\FULL\SQL2016N3_TheCallofKtulu_FULL_20170319_000004.bak

SourceServer  : SQL2016N3
TestServer    : SQL2016N1
Database      : TrappedUnderIce
FileExists    : Skipped
RestoreResult : Restore not located on shared location
DbccResult    : Skipped
SizeMB        : 4.08
BackupTaken   : 3/19/2017 12:00:04 AM
BackupFiles   : C:\MSSQL\Backup\SQL2016N3\TrappedUnderIce\FULL\SQL2016N3_TrappedUnderIce_FULL_20170319_000004.bak

Hmm, its not going to be easy to work out which bit of data is too big here.

All I need to know is the maximum length of the columns in the datatable though so I have a little snippet that will do that for me

$columns = ($datatable | Get-Member -MemberType Property).Name
foreach($column in $Columns) {
$max = 0
foreach ($a in $datatable){
       if($max -lt $a.$column.length){
        $max = $a.$column.length
       }
}
Write-Output "$column max length is $max"
}

and the output looks like this

04 - max length.PNG

So we can quickly see that the backupfiles property is too big and change the table accordingly and no more error.

Its pretty quick too, scanning 105 rows in 56 milliseconds in this example
05 - how long.PNG

I keep this little snippet in my snippets list for PowerShell ISE which you can find here

Here is the code to add this as a snippet to ISE
## A list of snippets
$snips = Get-IseSnippet
## Add a snippet
if(!$snips.Where{$_.Name -like 'Max Length of Datatable*'})
{
$snippet = @{
 Title = 'Max Length of Datatable'
 Description = 'Takes a datatable object and iterates through it to get the max length of the string columns - useful for data loads'
 Text = @"
`$columns = (`$datatable | Get-Member -MemberType Property).Name
foreach(`$column in `$Columns)
{
`$max = 0
foreach (`$a in `$datatable)
{
if(`$max -lt `$a.`$column.length)
{
`$max = `$a.`$column.length
}
}
Write-Output "`$column max length is `$max"
}

"@
}
New-IseSnippet @snippet
}
and if you want to add it to your VSCode snippets then you need to edit the PowerShell.json file which is located in your user home AppData folder ‘C:\Users\User\AppData\Roaming\Code\User\snippets\powershell.json’ or by clicking File –> Preferences –> User Snippets and typing PowerShell
04 user snippets.gif

Then you can add this bit of json inside the curly braces

 "Max Length of Datatable": {
"prefix": "Max Length of Datatable",
"body": [
"$$columns = ($$datatable | Get-Member -MemberType Property).Name",
"foreach($$column in $$Columns) {",
"    $$max = 0",
"    foreach ($$a in $$datatable){",
"        if($$max -lt $$a.$$column.length){",
"            $$max = $$a.$$column.length",
"        }",
"    }",
"    Write-Output \"$$column max length is $$max\"",
"}"
],
"description": "Takes a datatable object and iterates through it to get the max length of the string columns - useful for data loads"
}, 

and you have your snippet ready for use

07 - snippet in vscode.gif

Happy Automating