Tag Archives: databases

TSQL2sDay – Get-PostRoundup

Posted on by

First an apology, this round up is late!

The reason for that is an error in the PowerShell testing module Pester (That’s not completely true as you shall see!!)

I spoke in Stuttgart at the PowerShell Saturday last weekend and had intended to write this blog post whilst travelling, unfortunately I found a major error in Pester (again not strictly true but it makes a good story!!)

I explained it with this slide in my presentation

Yep, I forgot to pack my NUC with my VMs on it and had to re-write all my demos!!

But anyway, on to the TSQL2sDay posts

What a response. You wonderful people. I salute you with a Rimmer salute

There are 34 TSQL2sDay posts about dbatools, about starting with PowerShell, If you should learn PowerShell, SSAS, SSRS, Log Shipping, backups, restores, Pester, Default settings, best practices, migrations, Warnings in Agent Jobs, sqlpackage, VLFs, CMS, Disabling Named Pipes, Orphaned users, AG Status, AG Agent Jobs, logging, classes, auditing, copying files, ETL and more.

I am really pleased to see so many first timers to the TSQL2sDay blog monthly blog party. Please don’t let this be your only TSQL2sDay post. Come back next month and write a post on that topic.

Here they are below in the media of tweets, so that you can also go and follow these wonderful people who are so willing to share their knowledge. Say thank you to them, ask them questions, interact.

Learn, Share, Network

Volker wrote about testing best practices with dbatools

https://twitter.com/VolkerBachmann/status/907664676150022144

Dave explains why PowerShell is so useful to him in his ETL processes

Steve writes about the time he has saved using PowerShell to automate restores and audit SQL Server instances

https://twitter.com/Steve_TSQL/status/907713842943115264

Nate talks about copying large files like SQL Server backups using BITS with PowerShell

Warren talks about his experience as a beginner, the amount of things he automates and his DBReboot module

THANK YOU every single one and apologies if I have missed anyone!

 

 

Testing the Identity Column usage in SQL Server with PowerShell and dbatools

Posted on by

SQL Server uses identity columns to auto generate values, normally keys. When you create an identity column, it has a data type and that data type has a maximum number of values.

  • BigInt 9,223,372,036,854,775,808
  • Int 2,147,483,647
  • SmallInt 32,767
  • tinyint 255

What happens when you try to insert a value in an identity column that is greater than the maximum value? You get an error and a failed transaction. Lets do that

Using AdventureWorks, I know (I’ll show how in a minute) that the HumanResources.Shift column is a tinyint. So the highest value for the ShiftID column is 255.

If we run

USE AdventureWorks2014;
GO
INSERT INTO [HumanResources].[Shift]
([Name]
,[StartTime]
,[EndTime]
,[ModifiedDate])
VALUES
( 'Made Up SHift ' + CAST(NEWID() AS nvarchar(MAX))
,DATEADD(hour,-4, GetDate())
,'07:00:00.0000000'
,GetDate())
WAITFOR DELAY '00:00:00.050';
GO 252
Adding a number after GO says run this that many times, so we have added 252 rows to the existing 3 rows.
01 - maxx value.PNG

 

So what happens if we try to add another row?

USE AdventureWorks2014;
GO
INSERT INTO [HumanResources].[Shift]
([Name]
,[StartTime]
,[EndTime]
,[ModifiedDate])
VALUES
( 'Made Up SHift ' + CAST(NEWID() AS nvarchar(MAX))
,DATEADD(hour,-4, GetDate())
,'07:00:00.0000000'
,GetDate())
GO
02- error.PNG
We get an error
Msg 8115, Level 16, State 1, Line 4
Arithmetic overflow error converting IDENTITY to data type tinyint.
Arithmetic overflow occurred.
If that is a table that is important to your system, a logging table or worse, an order table then there is quickly going to be phone calls, visits to your desks, arm waving etc until you get it resolved. Lets clean up our mess
USE AdventureWorks2014
GO
DELETE FROM HumanResources.Shift
WHERE ShiftId > 3
GO
DBCC CHECKIDENT ('HumanResources.Shift', RESEED, 3)
GO
It would be very useful to be able to quickly see what the current values of the identity columns are and how close they are to being full so that we can plan for and be able to take action before we end up with shouty smart suits at our desk. If we could do it with just one line of code that would be even easier.
Step forward dbatools.  This PowerShell 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
There is a command called Test-DbaIdentityUsage This command was created by Brandon Abshire. You can find Brandon blogging at netnerds.net. Thank you Brandon
As always with a new PowerShell command you should always start with Get-Help
Get-Help Test-DbaIdentityUsage -ShowWindow
03 - get help.PNG

 

The command has a few parameters

  • SqlInstance – One or many Instances
  • SqlCredential – for SQL Authentication
  • Databases – to filter for databases ( This is a dynamic parameter and doesn’t show in the Help)
  • Threshold – define a minimum percentage for how full the identity column is
  • NoSystemDB – to ignore the system databases

So we can run the command against one instance

Test-DbaIdentityUsage -SqlInstance sql2014ser12r2

 

04 - one server.PNG

This returns an object for each identity column in each database on the instance. The object has the following properties

ComputerName   : SQL2014SER12R2
InstanceName   : MSSQLSERVER
SqlInstance    : SQL2014SER12R2
Database       : AdventureWorks2014
Schema         : HumanResources
Table          : Shift
Column         : ShiftID
SeedValue      : 1
IncrementValue : 1
LastValue      : 3
MaxNumberRows  : 254
NumberOfUses   : 3
PercentUsed    : 1.18

We can use the objects returned from this command in a number of ways, this is one of the beauties of PowerShell that we can interact with numerous systems. I have blogged about some simple ways of doing this here but your only limit is your imagination.

I love to use Out-GridView as it enables quick and easy sorting of the returned data

06 - ogv filter.gif

The databases parameter is dynamic so it will prefill the names of the databases on the instance. This is what it looks like in VS Code

07 vscode tab.gif

 

and in ISE

08 ise tab.gif

 

We can use the threshold parameter to only show results for the identity columns whose value is above a percent of the max value for the column. Lets fill the ShiftId column to above 90% and show this

USE AdventureWorks2014;
GO
INSERT INTO [HumanResources].[Shift]
([Name]
 ,[StartTime]
,[EndTime]
,[ModifiedDate])
VALUES
( 'Made Up SHift ' + CAST(NEWID() AS nvarchar(MAX))
,DATEADD(hour,-4, GetDate())
,'07:00:00.0000000'
,GetDAte())
WAITFOR DELAY '00:00:00.050';
GO 230

and now run

Test-DbaIdentityUsage -SqlInstance sql2014ser12r2  -Threshold 90

08 - threshold.PNG

Don’t forget to use the cleanup script. You can pass a whole array of SQL instances to the command. 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. Just to get some results I will set the threshold to 1

$SQLServers = (Get-VM -ComputerName beardnuc | Where-Object {$_.Name -like '*SQL*' -and $_.Name -ne 'SQL2008Ser2008' -and $_.State -eq 'Running'}).Name
Test-DbaIdentityUsage -SqlInstance $SQLServers -Threshold 1 | Out-GridView
10 ogv thredshold.PNG
As you can see this function does not support SQL instances lower than SQL 2008 and you will get warnings for availability group databases
It’s quick too, finishing in less than 2 seconds in my lab of 10 SQL Servers and 125 databases. The WarningAction SilentlyContinue supresses the yellow warnings
11 - measure command.PNG
This is ideal for using Pester to test.
 Describe "Testing how full the Identity columns are" {
$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 all have identity columns less than 90% full" -TestCases $testCases {
Param($Name)
(Test-DbaIdentityUsage -SqlInstance $Name -Threshold 90 -WarningAction SilentlyContinue).PercentUsed | Should Be
}
}
12 pester test.PNG
An excellent quick test but it doesn’t show us which databases have failed. We can iterate through our servers and databases like this
Describe "Testing how full the Identity columns are" {
    $SQLServers = (Get-VM -ComputerName beardnuc | Where-Object {$_.Name -like '*SQL*' -and $_.Name -ne 'SQL2008Ser2008' -and $_.State -eq 'Running'}).Name
    foreach($SQLServer in $SQLServers)
    {
        Context "Testing $SQLServer" {
            $dbs = (Connect-DbaSqlServer -SqlServer $SQLServer).Databases.Name
            foreach($db in $dbs)
            {
                It "$db on $SQLServer identity columns are less than 90% full" {
                    (Test-DbaIdentityUsage -SqlInstance $SQLServer -Databases $db -Threshold 90 -WarningAction SilentlyContinue).PercentUsed | Should Be
                }
            }
        }
    }
}
This is using the Connect-DbaSqlServer to create a SMO object and then gathering the databases on the server into a variable and iterating through them
It looks like this when it is running
13 - pester test.png
and at the end gives you a little overview of the number of tests that have failed
14 end of pester test.png
In a previous post I showed how you can output these results to XML or even make a HTML page showing the output
But perhaps that isn’t granular enough for you and you want a test for each column. This is how you could do that
$SQLServers = (Get-VM -ComputerName beardnuc | Where-Object {$_.Name -like '*SQL*' -and $_.Name -ne 'SQL2008Ser2008' -and $_.State -eq 'Running'}).Name
foreach($SQLServer in $SQLServers)
{
    Describe "$SQLServer - Testing how full the Identity columns are" {
            $dbs = (Connect-DbaSqlServer -SqlServer $SQLServer).Databases.Name
            foreach($db in $dbs)
            {
                Context "Testing $db" {
                $Tests = Test-DbaIdentityUsage -SqlInstance $SQLServer -Databases $db -WarningAction SilentlyContinue
                foreach($test in $tests)
                {
                    It "$($test.Column) identity column in $($Test.Table) is less than 90% full" {
                        $Test.PercentUsed | Should BeLessThan 90
                    }
                }
            }
        }
    }
}
Which looks like this, a test for each identity column in each database in each server in your environment
15 every pester teest.PNG

 

The other question that we have to answer these days is – Does it work with SQL on Linux? We will have to pass a SQL authentication credential and this time I will use Format-Table for the output

 Test-DbaIdentityUsage -SqlInstance LinuxvNextCTP14 -SqlCredential (Get-Credential) | Format-Table

16 - on Linux.PNG

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

Posted on by

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

Posted on by

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

 

Taking dbatools Test-DbaLastBackup a little further

Posted on by

In a previous post I showed how easy it is to test your backups using Test-DbaLastBackup

Today I thought I would take it a little further and show you how PowerShell can be used to transmit or store this information in the manner you require

Test-DBALastBackup returns an object of information

SourceServer  : SQL2016N2
TestServer    : SQL2016N2
Database      : FadetoBlack
FileExists    : True
RestoreResult : Success
DbccResult    : Success
SizeMB        : 1243.26
BackupTaken   : 3/18/2017 12:36:07 PM
BackupFiles   : Z:\SQL2016N2\FadetoBlack\FULL_COPY_ONLY\SQL2016N2_FadetoBlack_FULL_COPY_ONLY_20170318_123607.bak

which shows the server, the database name, if the file exists, the restore result, the DBCC result, the size of the backup file, when the backup was taken and the path used

Text File

As it is an object we can make use of that in PowerShell. We can output the results to a file

Test-DbaLastBackup -SqlServer sql2016n2 -Destination SQL2016N1 -MaxMB 5 | Out-File C:\temp\Test-Restore.txt
notepad C:\temp\Test-Restore.txt

01 - out file.PNG

CSV

Or maybe you need a CSV

Test-DbaLastBackup -SqlServer sql2016n2 -Destination SQL2016N1 -MaxMB 5 | Export-Csv C:\temp\Test-Restore.csv -NoTypeInformation
02 - csv file.PNG

JSON

Maybe you want some json

Test-DbaLastBackup -SqlServer sql2016n2 -Destination SQL2016N1| ConvertTo-Json | Out-file c:\temp\test-results.json
06 - json results.PNG

HTML

Or an HTML page

$Results = Test-DbaLastBackup -SqlServer sql2016n2 -Destination SQL2016N1
$Results | ConvertTo-Html | Out-File c:\temp\test-results.html
03 - html.PNG

Excel

or perhaps you want a nice colour coded Excel sheet to show your manager or the auditors

Import-Module dbatools
$TestServer = 'SQL2016N1'
$Server = 'SQL2016N2'
## Run the test and save to a variable
$Results = Test-DbaLastBackup -SqlServer $server -Destination $TestServer
# Set the filename
$TestDate = Get-Date
$Date = Get-Date -Format ddMMyyy_HHmmss
$filename = 'C:\Temp\TestResults_' + $Date + '.xlsx'
# Create a .com object for Excel
$xl = new-object -comobject excel.application
$xl.Visible = $true # Set this to False when you run in production
$wb = $xl.Workbooks.Add() # Add a workbook
$ws = $wb.Worksheets.Item(1) # Add a worksheet
$cells = $ws.Cells
$col = 1
$row = 3
## Create a legenc
$cells.item($row, $col) = "Legend"
$cells.item($row, $col).font.size = 16
$Cells.item($row, $col).Columnwidth = 10
$Cells.item($row, $col).Interior.ColorIndex = 34
$row ++
$cells.item($row, $col) = "True or Success"
$cells.item($row, $col).font.size = 12
$Cells.item($row, $col).Columnwidth = 10
$Cells.item($row, $col).Interior.ColorIndex = 10
$row ++
$cells.item($row, $col) = "False or Failed"
$cells.item($row, $col).font.size = 12
$Cells.item($row, $col).Columnwidth = 10
$Cells.item($row, $col).Interior.ColorIndex = 3
$row ++
$cells.item($row, $col) = "Skipped"
$cells.item($row, $col).font.size = 12
$Cells.item($row, $col).Columnwidth = 10
$Cells.item($row, $col).Interior.ColorIndex = 16
$row ++
$cells.item($row, $col) = "Backup Under 7 days old"
$cells.item($row, $col).font.size = 12
$Cells.item($row, $col).Columnwidth = 10
$Cells.item($row, $col).Interior.ColorIndex = 4
$row ++
$cells.item($row, $col) = "Backup Over 7 days old"
$cells.item($row, $col).font.size = 12
$Cells.item($row, $col).Columnwidth = 10
$Cells.item($row, $col).Interior.ColorIndex = 3
## Create a header
$col ++
$row = 3
$cells.item($row, $col) = "Source Server"
$cells.item($row, $col).font.size = 16
$Cells.item($row, $col).Columnwidth = 10
$Cells.item($row, $col).Interior.ColorIndex = 34
$col ++
$cells.item($row, $col) = "Test Server"
$cells.item($row, $col).font.size = 16
$Cells.item($row, $col).Columnwidth = 10
$Cells.item($row, $col).Interior.ColorIndex = 34
$col ++
$cells.item($row, $col) = "Database"
$cells.item($row, $col).font.size = 16
$Cells.item($row, $col).Columnwidth = 10
$Cells.item($row, $col).Interior.ColorIndex = 34
$col ++
$cells.item($row, $col) = "File Exists"
$cells.item($row, $col).font.size = 16
$Cells.item($row, $col).Columnwidth = 10
$Cells.item($row, $col).Interior.ColorIndex = 34
$col ++
$cells.item($row, $col) = "Restore Result"
$cells.item($row, $col).font.size = 16
$Cells.item($row, $col).Columnwidth = 10
$Cells.item($row, $col).Interior.ColorIndex = 34
$col ++
$cells.item($row, $col) = "DBCC Result"
$cells.item($row, $col).font.size = 16
$Cells.item($row, $col).Columnwidth = 10
$Cells.item($row, $col).Interior.ColorIndex = 34
$col ++
$cells.item($row, $col) = "Size Mb"
$cells.item($row, $col).font.size = 16
$Cells.item($row, $col).Columnwidth = 10
$Cells.item($row, $col).Interior.ColorIndex = 34
$col ++
$cells.item($row, $col) = "Backup Date"
$cells.item($row, $col).font.size = 16
$Cells.item($row, $col).Columnwidth = 10
$Cells.item($row, $col).Interior.ColorIndex = 34
$col ++
$cells.item($row, $col) = "Backup Files"
$cells.item($row, $col).font.size = 16
$Cells.item($row, $col).Columnwidth = 10
$Cells.item($row, $col).Interior.ColorIndex = 34
$col = 2
$row = 4
foreach ($result in $results) {
    $col = 2
    $cells.item($row, $col) = $Result.SourceServer
    $cells.item($row, $col).font.size = 12
    $Cells.item($row, $col).Columnwidth = 10
    $col ++
    $cells.item($row, $col) = $Result.TestServer
    $cells.item($row, $col).font.size = 12
    $Cells.item($row, $col).Columnwidth = 10
    $col++
    $cells.item($row, $col) = $Result.Database
    $cells.item($row, $col).font.size = 12
    $Cells.item($row, $col).Columnwidth = 10
    $col++
    $cells.item($row, $col) = $Result.FileExists
    $cells.item($row, $col).font.size = 12
    $Cells.item($row, $col).Columnwidth = 10
    if ($result.FileExists -eq 'True') {
        $Cells.item($row, $col).Interior.ColorIndex = 10
    }
    elseif ($result.FileExists -eq 'False') {
        $Cells.item($row, $col).Interior.ColorIndex = 3
    }
    else {
        $Cells.item($row, $col).Interior.ColorIndex = 16
    }
    $col++
    $cells.item($row, $col) = $Result.RestoreResult
    $cells.item($row, $col).font.size = 12
    $Cells.item($row, $col).Columnwidth = 10
    if ($result.RestoreResult -eq 'Success') {
        $Cells.item($row, $col).Interior.ColorIndex = 10
    }
    elseif ($result.RestoreResult -eq 'Failed') {
        $Cells.item($row, $col).Interior.ColorIndex = 3
    }
    else {
        $Cells.item($row, $col).Interior.ColorIndex = 16
    }
    $col++
    $cells.item($row, $col) = $Result.DBCCResult
    $cells.item($row, $col).font.size = 12
    $Cells.item($row, $col).Columnwidth = 10
    if ($result.DBCCResult -eq 'Success') {
        $Cells.item($row, $col).Interior.ColorIndex = 10
    }
    elseif ($result.DBCCResult -eq 'Failed') {
        $Cells.item($row, $col).Interior.ColorIndex = 3
    }
    else {
        $Cells.item($row, $col).Interior.ColorIndex = 16
    }
    $col++
    $cells.item($row, $col) = $Result.SizeMb
    $cells.item($row, $col).font.size = 12
    $Cells.item($row, $col).Columnwidth = 10
    $col++
    $cells.item($row, $col) = $Result.BackupTaken
    $cells.item($row, $col).font.size = 12
    $Cells.item($row, $col).Columnwidth = 10
    if ($result.BackupTaken -gt (Get-Date).AddDays(-7)) {
        $Cells.item($row, $col).Interior.ColorIndex = 4
    }
    else {
        $Cells.item($row, $col).Interior.ColorIndex = 3
    }
    $col++
    $cells.item($row, $col) = $Result.BackupFiles
    $cells.item($row, $col).font.size = 12
    $Cells.item($row, $col).Columnwidth = 10
    $row++
}
[void]$ws.cells.entireColumn.Autofit()
## Add the title after the autofit
$col = 2
$row = 1
$cells.item($row, $col) = "This report shows the results of the test backups performed on $TestServer for $Server on $TestDate"
$cells.item($row, $col).font.size = 18
$Cells.item($row, $col).Columnwidth = 10
$wb.Saveas($filename)
$xl.quit()

It looks like this. Green is Good, Red is Bad, Grey is don’t care!

Email

You might need to email the results, here I am using GMail as an example. With 2 factor authentication you need to use an app password in the credential

Import-Module dbatools
$TestServer = 'SQL2016N1'$Server = 'SQL2016N2'
## Run the test and save to a variable
$Results = Test-DbaLastBackup -SqlServer $server -Destination $TestServer -MaxMB 5
$to = ''
$smtp = 'smtp.gmail.com'
$port = 587
$cred = Get-Credential
$from = 'Beard@TheBeard.Local'
$subject = 'The Beard Reports on Backup Testing'
$Body = $Results | Format-Table | Out-String
Send-MailMessage -To $to -From $from -Body $Body -Subject $subject -SmtpServer $smtp -Priority High -UseSsl -Port $port -Credential $cred</pre>
07 -email

You can of course attach any of the above files as an attachment using the -attachment parameter in Send-MailMessage

Database

Of course, as good data professionals we probably want to put the data into a database where we can ensure that it is kept safe and secure

dbatools has a couple of commands to help with that too. We can use Out-DbaDataTable to create a datatable object and Write-DbaDatatable to write it to a database

Create a table

USE [TestResults]
GO
CREATE TABLE [dbo].[backuptest](
[SourceServer] [nvarchar](250) NULL,
[TestServer] [nvarchar](250) NULL,
[Database] [nvarchar](250) NULL,
[FileExists] [nvarchar](10) NULL,
[RestoreResult] [nvarchar](200) NULL,
[DBCCResult] [nvarchar](200) NULL,
[SizeMB] [int] NULL,
[Backuptaken] [datetime] NULL,
[BackupFiles] [nvarchar](300) NULL
) ON [PRIMARY]
GO

then add the data

Import-Module dbatools
$TestServer = 'SQL2016N1'
$Server = 'SQL2016N2'
$servers = 'SQL2005Ser2003','SQL2012Ser08AG1','SQL2012Ser08AG2','SQL2012Ser08AG3','SQL2014Ser12R2','SQL2016N1','SQL2016N2','SQL2016N3'
## Run the test for each server and save to a variable (This uses PowerShell v4 or above code)
$Results = $servers.ForEach{Test-DbaLastBackup -SqlServer $_ -Destination $TestServer -MaxMB 5}
## Convert to a daatatable.
$DataTable = Out-DbaDataTable -InputObject $Results
## Write to the database
Write-DbaDataTable -SqlServer $Server -Database TestResults -Schema dbo -Table backuptest -KeepNulls -InputObject $DataTable

and query it

08 - Database.PNG

Hopefully that has given you some ideas of how you can make use of this great command and also one of the benefits of PowerShell and the ability to use objects for different purposes

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

Converting SQL Agent Job Duration to TimeSpan using PowerShell

Posted on by

When you look in msdb for the SQL Agent Job duration you will find that it is an int.

sysjobshistoiry

This is also the same when you look at Get-SQLAgentJobHistory from the sqlserver module. (You can get this by downloading the latest SSMS release from here)

agentjobhistoryproperties

This means that when you look at the various duration of the Agent Jobs you get something like this

duration.PNG

The first job took 15 hours 41 minutes  53 seconds, the second 1 minute 25 seconds, the third 21 seconds. This makes it quite tricky to calculate the duration in a suitable datatype. In T-SQL people use scripts like the following from MSSQLTips.com 

((run_duration/10000*3600 + (run_duration/100)%100*60 + run_duration%100 + 31 ) / 60)  as 'RunDurationMinutes'

I needed more information than the number of minutes so I have this which will convert the Run Duration to a timespan

$FormattedDuration = @{Name = 'FormattedDuration' ; Expression = {[timespan]$_.RunDuration.ToString().PadLeft(6,'0').insert(4,':').insert(2,':')}}

formatted.PNG

So how did I get to there?

First I tried to just convert it. In PowerShell you can define a datatype in square brackets and PowerShell will try to convert it

timespan

It did its best but it converted it to ticks! So we need to convince PowerShell that this is a proper timespan. First we need to convert the run duration to a standard length, you can use the PadLeft method of a string to do this which will ensure that a string has a length and precede the current string with a value you choose until the string is that length.

Lets have a length of 6 and preceding zeros PadLeft(6,’0′)

padlefterror

But this works only if it is a string!! Remember red text is useful, it will often contain the information you need to resolve your error. Luckily there is a method to turn an int to a string. I am using the foreach method to demonstrate

padleft-with-string

Now every string is 6 characters long starting with zeros. So all that is left is to format this with colons to separate the hours and minutes and the minutes and seconds. We can do this with the insert method. You can find out the methods using Get-Member or its alias gm

methods.PNG

So the insert method takes an int for the startindex and a string value to enter

insert

There we go now we have some proper formatted timespans however they are still strings. We can then convert them using [timespan] Now we can format the results within the select by using an expression as shown below

select

and as you can see it is a timespan now

timespan property.PNG

On a slight side note. I needed the durations for Agent Jobs with a certain name within the last 6 days.

getting-agent-jobs

I did this by passing an array of servers (which I got from my dbareports database) to Get-SQLAgentJobHistory. I then used the Where method to filter for JobName and the Job Outcome step of the history. I compared the RunDate property  to Get-Date (today) adding -6 days using the AddDays method 🙂

Hopefully this will be of use to people and also I have it recorded for the next time I need to do it 🙂

 

 

Some Pester Tests for SQL Defaults

Posted on by

When I was at PowerShell Conference EU in Hannover last month (The videos are available now – click here and the slides and code here) I found out about Irwin Strachans Active Directory Operations Test which got me thinking.

I decided to do the same for my usual SQL Set-up. Treating all of your servers to the same defaults makes it even easier to manage at scale remotely.

I am comfortable with using SMO to gather and change properties on SQL Instances so I started by doing this

        It 'Should have a default Backup Directory of F:\SQLBACKUP\BACKUPS' {
$Scriptblock = {
[void][reflection.assembly]::LoadWithPartialName('Microsoft.SqlServer.Smo');
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server .
return $srv.BackupDirectory}
$State = Invoke-Command -ComputerName ROB-SURFACEBOOK -ScriptBlock $Scriptblock
$State |Should Be 'F:\SQLBACKUP\BACKUPS'

This is the how to find the properties that you want

  ## Load the Assemblies
[void][reflection.assembly]::LoadWithPartialName('Microsoft.SqlServer.Smo');
## Create a Server SMO object
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server SERVERNAME

## Explore it
$srv|gm

## If you find an array pick the first one and expand and then explore that
$srv.Databases[0] | select *
$srv.Databases[0] | gm

I quickly found as I added more tests that it was taking a long time to perform the tests (about 5 seconds each test) and that it took an age to fail each of the tests if the server name was incorrect or the server unavailable.

I fixed the first one by testing with a ping before running the tests

   ## Check for connectivity
if((Test-Connection $Server -count 1 -Quiet) -eq $false){
Write-Error 'Could not connect to $Server'
$_
continue
}

The continue is there because I wanted to loop through an array of servers

I improved the performance using a remote session and a custom object

      Describe "$Server" {
BeforeAll {
$Scriptblock = {
[pscustomobject]$Return = @{}
$srv = ''
$SQLAdmins = $Using:SQLAdmins
[void][reflection.assembly]::LoadWithPartialName('Microsoft.SqlServer.Smo');
$srv = New-Object Microsoft.SQLServer.Management.SMO.Server $Server
$Return.DBAAdminDb = $Srv.Databases.Name.Contains('DBA-Admin')
$Logins = $srv.Logins.Where{$_.IsSystemObject -eq $false}.Name
$Return.SQLAdmins = @(Compare-Object $Logins $SQLAdmins -SyncWindow 0).Length - $Logins.count -eq $SQLAdmins.Count
$SysAdmins = $Srv.Roles['sysadmin'].EnumMemberNames()
$Return.SQLAdmin = @(Compare-Object $SysAdmins $SQLAdmins -SyncWindow 0).Length - $SysAdmins.count -eq $SQLAdmins.Count
$Return.BackupDirectory = $srv.BackupDirectory
$Return.DataDirectory = $srv.DefaultFile

The BeforeAll script block is run, as it sounds like it should, once before all of the tests, BeforeEach would run once before each of the tests. I define an empty custom object and then create an SMO object and add the properties I am interested in testing to it. I then return the custom object at the end

   $Return.Alerts82345Exist = ($srv.JobServer.Alerts |Where {$_.Messageid -eq 823 -or $_.Messageid -eq 824 -or $_.Messageid -eq 825}).Count
$Return.Alerts82345Enabled = ($srv.JobServer.Alerts |Where {$_.Messageid -eq 823 -or $_.Messageid -eq 824 -or $_.Messageid -eq 825 -and $_.IsEnabled -eq $true}).Count
$Return.SysDatabasesFullBackupToday = $srv.Databases.Where{$_.IsSystemObject -eq $true -and $_.Name -ne 'tempdb' -and $_.LastBackupDate -lt (Get-Date).AddDays(-1)}.Count
Return $Return
}
try {
$Return = Invoke-Command -ScriptBlock $Scriptblock -ComputerName $Server -ErrorAction Stop
}
catch {
Write-Error "Unable to Connect to $Server"
$Error
continue

I was then able to test against the property of the custom object

   It 'Should have Alerts for Severity 20 and above' {
$Return.Alerts20SeverityPlusExist | Should Be 6
}
It 'Severity 20 and above Alerts should be enabled' {
$Return.Alerts20SeverityPlusEnabled | Should Be 6
}
It 'Should have alerts for 823,824 and 825' {
$Return.Alerts82345Exist |Should Be 3
}
It 'Alerts for 823,824 and 825 should be enebled' {
$Return.Alerts82345Enabled |Should Be 3
}

Occasionally, for reasons I haven’t explored I had to test against the value property of the returned object

          It "The Full User Database Backup should be scheduled Weekly $OlaUserFullSchedule" {
$Return.OlaUserFullSchedule.value | Should Be $OlaUserFullSchedule
}

I wanted to be able to run the tests against environments or groups of servers with different default values so I parameterised the Test Results as well and then the logical step was to turn it into a function and then I could do some parameter splatting. This also gives me the opportunity to show all of the things that I am currently giving parameters to the test for

   $Parms = @{
Servers = 'SQLServer1','SQLServer2','SQLServer3';
SQLAdmins = 'THEBEARD\Rob','THEBEARD\SQLDBAsAlsoWithBeards';
BackupDirectory = 'C:\MSSQL\Backup';
DataDirectory = 'C:\MSSQL\Data\';
LogDirectory = 'C:\MSSQL\Logs\';
MaxMemMb = '4096';
Collation = 'Latin1_General_CI_AS';
TempFiles = 4 ;
OlaSysFullFrequency = 'Daily';
OlaSysFullStartTime = '21:00:00';
OlaUserFullSchedule = 'Weekly';
OlaUserFullFrequency = 1 ;## 1 for Sunday
OlaUserFullStartTime = '22:00:00';
OlaUserDiffSchedule = 'Weekly';
OlaUserDiffFrequency = 126; ## 126 for every day except Sunday
OlaUserDiffStartTime = '22:00:00';
OlaUserLogSubDayInterval = 15;
OlaUserLoginterval = 'Minute';
HasSPBlitz = $true;
HasSPBlitzCache = $True;
HasSPBlitzIndex = $True;
HasSPAskBrent = $true;
HASSPBlitzTrace =  $true;
HasSPWhoisActive = $true;
LogWhoIsActiveToTable = $true;
LogSPBlitzToTable = $true;
LogSPBlitzToTableEnabled = $true;
LogSPBlitzToTableScheduled = $true;
LogSPBlitzToTableSchedule = 'Weekly';
LogSPBlitzToTableFrequency = 2 ; # 2 means Monday
LogSPBlitzToTableStartTime  = '03:00:00'}

Test-SQLDefault @Parms

I have some other tests which always return what I want, particularly the firewall rules which you will have to modify to suit your own environment

To be able to run this you will need to have the Pester Module. If you are using Windows 10 then it is installed by default, if not

  Find-Module –Name 'Pester' | Install-Module

You can find more about Pester here and here and also these videos from the conference
You can find the tests on GitHub here and I will continue to add to the defaults that I check.
This is not a replacement for other SQL configuration tools such as PBM but it is a nice simple way of giving a report on the current status of a SQL installation either at a particular point in time when something is wrong or after an installation prior to passing the server over to another team or into service

.

Backing up to URL container name – case is important

Posted on by

If you use SQL Backup to URL to backup your databases to Azure blob storage remember that for the container name case is important

So

BACKUP LOG [DatabaseName]
TO URL = N'https://storageaccountname.blob.core.windows.net/containername/databasename_log_dmmyyhhss.trn'
WITH CHECKSUM, NO_COMPRESSION, CREDENTIAL = N'credential'

will work but

BACKUP LOG [DatabaseName]
TO URL = N'https://storageaccountname.blob.core.windows.net/CONTAINERNAME/databasename_log_dmmyyhhss.trn'</span>
WITH CHECKSUM, NO_COMPRESSION, CREDENTIAL = N'credential'

will give an (400) Bad Request Error which may not be easy to diagnose

Msg 3271, Level 16, State 1, Line 1
A nonrecoverable I/O error occurred on file "https://storageacccountname.blob.core.windows.net/CONTAINERNAME/databasename_log_dmmyyhhss.trn':" Backup to URL received an exception from the remote endpoint. 
Exception Message: The remote server returned an error: (400) Bad Request..
Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.

If you are using Ola Hallengrens jobs to perform your backup then your job step will look like this

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d DBA-Admin -Q "EXECUTE [dbo].[DatabaseBackup] @Databases = 'USER_DATABASES',&nbsp; @URL = 'https://storageaccountname.blob.core.windows.net/containername', @Credential = 'credential', @BackupType = 'LOG', @ChangeBackupType = 'Y', @Verify = 'Y', @CheckSum = 'Y', @LogToTable = 'Y'" -b

Note the @ChangeBackupType = ‘Y’ parameter which is not created by default but I think is very useful. If you have just created a database and take log backups every 15 minutes but differential (or full) every night the log backup will fail until a full backup has been taken. This parameter will check if a log backup is possible and if not take a full backup meaning that you still can keep to your RTO/RPO requirements even for newly created databases

Populating My DBA Database for Power Bi with PowerShell – Databases

Posted on by

Following my post about using Power Bi with my DBA Database I have been asked if I would share the PowerShell scripts which I use to populate my database.

In this post I will show how to create the following report

db1

db2

Although you will find so many items of data that I expect that you will want to create different reports for your own requirements. You will also want to put the report onto PowerBi.com and explore the natural language querying as I show at the end of this post

You will find the latest version of my DBADatabase creation scripts and PowerShell scripts here.

The SQLInfo table is created using this code

CREATE TABLE [Info].[Databases](
	[DatabaseID] [int] IDENTITY(1,1) NOT NULL,
	[InstanceID] [int] NOT NULL,
	[Name] [nvarchar](256) NULL,
	[DateAdded] [datetime2](7) NULL,
	[DateChecked] [datetime2](7) NULL,
	[AutoClose] [bit] NULL,
	[AutoCreateStatisticsEnabled] [bit] NULL,
	[AutoShrink] [bit] NULL,
	[AutoUpdateStatisticsEnabled] [bit] NULL,
	[AvailabilityDatabaseSynchronizationState] [nvarchar](16) NULL,
	[AvailabilityGroupName] [nvarchar](128) NULL,
	[CaseSensitive] [bit] NULL,
	[Collation] [nvarchar](30) NULL,
	[CompatibilityLevel] [nvarchar](15) NULL,
	[CreateDate] [datetime2](7) NULL,
	[DataSpaceUsageKB] [float] NULL,
	[EncryptionEnabled] [bit] NULL,
	[IndexSpaceUsageKB] [float] NULL,
	[IsAccessible] [bit] NULL,
	[IsFullTextEnabled] [bit] NULL,
	[IsMirroringEnabled] [bit] NULL,
	[IsParameterizationForced] [bit] NULL,
	[IsReadCommittedSnapshotOn] [bit] NULL,
	[IsSystemObject] [bit] NULL,
	[IsUpdateable] [bit] NULL,
	[LastBackupDate] [datetime2](7) NULL,
	[LastDifferentialBackupDate] [datetime2](7) NULL,
	[LastLogBackupDate] [datetime2](7) NULL,
	[Owner] [nvarchar](30) NULL,
	[PageVerify] [nvarchar](17) NULL,
	[ReadOnly] [bit] NULL,
	[RecoveryModel] [nvarchar](10) NULL,
	[ReplicationOptions] [nvarchar](40) NULL,
	[SizeMB] [float] NULL,
	[SnapshotIsolationState] [nvarchar](10) NULL,
	[SpaceAvailableKB] [float] NULL,
	[Status] [nvarchar](35) NULL,
	[TargetRecoveryTime] [int] NULL,
 CONSTRAINT [PK_Databases] PRIMARY KEY CLUSTERED 
(
	[DatabaseID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

The Powershell script uses Jason Wasser @wasserja Write-Log function to write to a text file but I also enable some logging into a new event log by following the steps here http://blogs.technet.com/b/heyscriptingguy/archive/2013/02/01/use-powershell-to-create-and-to-use-a-new-event-log.aspx to create a log named SQLAutoScript with a source SQLAUTOSCRIPT

To run the script I simply need to add the values for

$CentralDBAServer = '' ## Add the address of the instance that holds the DBADatabase
$CentralDatabaseName = 'DBADatabase' 
$LogFile = "\DBADatabaseServerUpdate_" + $Date + ".log" ## Set Path to Log File

And the script will do the rest. Call the script from a PowerShell Job Step and schedule it to run at the frequency you wish, I gather the information every week. You can get the script from here or you can read on to see how it works and how to create the report and publish it to powerbi.com and query it with natural langauge

I create a function called Catch-Block to save keystrokes and put my commands inside a try catch to make the scripts as robust as possible. I won’t include the try catch in the examples below. I gather all of the server names from the InstanceList table and set the results to an array variable called $ServerNames holding the server name, instance name and port

 $Query = @"
 SELECT [ServerName]
      ,[InstanceName]
      ,[Port]
  FROM [DBADatabase].[dbo].[InstanceList]
  Where Inactive = 0 
    AND NotContactable = 0
"@
try{
$AlltheServers= Invoke-Sqlcmd -ServerInstance $CentralDBAServer -Database $CentralDatabaseName -Query $query
$ServerNames = $AlltheServers| Select ServerName,InstanceName,Port
}

I then loop through the array and create a $Connection variable for my SMO connection string and connect to the server

foreach ($ServerName in $ServerNames)
{
## $ServerName
 $InstanceName =  $ServerName|Select InstanceName -ExpandProperty InstanceName
 $Port = $ServerName| Select Port -ExpandProperty Port
$ServerName = $ServerName|Select ServerName -ExpandProperty ServerName 
 $Connection = $ServerName + '\' + $InstanceName + ',' + $Port

 try
 {
 $srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $Connection

Even though I place the creation of the SMO server object in a try block you still need to an additional check to ensure that you can connect and populate the object as the code above creates an empty SMO Server object with the name property set to the $Connection variable if you can’t connect to that server and doesn’t error as you may expect
The way I have always validated an SMO Server object is to check the version property. There is no justifiable reason for choosing that property, you could choose any one but that’s the one I have always used. I use an if statement to do this ( This post about Snippets will show you the best way to learn PowerShell code) The reference I use for exiting a loop in the way that you want is this one In this case we use a continue to carry on iterating the loop

 if (!( $srv.version)){
 Catch-Block " Failed to Connect to $Connection"
 continue
 }

I then loop through the user databases

foreach($db in $srv.databases|Where-Object {$_.IsSystemObject -eq $false })
{
$Name = $db.Name
$Parent = $db.Parent.Name

To gather information on all databases just remove everything after the pipe symbol or if you wish to exclude certain databases from the collection gathering, maybe the database you keep your Change log table and DBA Team info in you can do that as well here

foreach($db in $srv.databases|Where-Object {$_.Name -ne 'EXCLUDENAME' })
{
$Name = $db.Name
$Parent = $db.Parent.Name

If you wish to view all of the different properties that you can gather information on in this way you can use this code to take a look. (This is something you should get used to doing when writing new Powershell scripts)

$Connection = 'SERVERNAMEHERE'
$srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $Connection
 $srv.databases | Get-Member

An alternative method of doing this is to set a variable to a $db and then to select all of the properties so that you can see the values and identify the ones you want. Again this a good thing to do when exploring new objects

$db = $srv.databases['DBNAMEHERE'] 
$db| Select *

You can see from the screen shot below that there are 170 properties available to you on a SQL2014 instance. You can gather any or all of that information as long as you ensure that you have the columns with the correct data types in your table and that your script has the logic to deal with properties that do not exist although I have had less issue with this for the database object than the server object

db3

You can look for the property that you want by using the Get-Member cmdlet as shown above or use MSDN to find it starting from here or by GoogleBingDuckDuckGo ing “Powershell SMO” and the property you wish to find.

The rest of the script follows exactly the same pattern as the previous post by checking the SQL Info table for an entry for that instance and updating the table if it exists and inserting if it does not.

This is how I created the reports shown above.

Connect to the DBA Database and run these queries to gather the data for the report.

SELECT 
IL.ServerName
,IL.InstanceName
,IL.Location
,IL.Environment
,IL.Inactive
,IL.NotContactable
,D.[DatabaseID]
,D.[InstanceID]
,D.[Name]
,D.[DateAdded]
,D.[DateChecked]
,D.[AutoClose]
,D.[AutoCreateStatisticsEnabled]
,D.[AutoShrink]
,D.[AutoUpdateStatisticsEnabled]
,D.[AvailabilityDatabaseSynchronizationState]
,D.[AvailabilityGroupName]
,D.[CaseSensitive]
,D.[Collation]
,D.[CompatibilityLevel]
,D.[CreateDate]
,D.[DataSpaceUsageKB]
,D.[EncryptionEnabled]
,D.[IndexSpaceUsageKB]
,D.[IsAccessible]
,D.[IsFullTextEnabled]
,D.[IsMirroringEnabled]
,D.[IsParameterizationForced]
,D.[IsReadCommittedSnapshotOn]
,D.[IsUpdateable]
,D.[LastBackupDate]
,D.[LastDifferentialBackupDate]
,D.[LastLogBackupDate]
,D.[Owner]
,D.[PageVerify]
,D.[ReadOnly]
,D.[RecoveryModel]
,D.[ReplicationOptions]
,D.[SizeMB]
,D.[SnapshotIsolationState]
,D.[SpaceAvailableKB]
,D.[Status]
,D.[TargetRecoveryTime]
FROM [DBADatabase].[Info].[Databases] as D
JOIN [DBADatabase].[dbo].[InstanceList] as IL
ON IL.InstanceID =D.InstanceID

To get all the database and instance information and

SELECT C.ClientName
 ,[DatabaseID]
 ,[InstanceID]
 ,[Notes]
  FROM [DBADatabase].[dbo].[ClientDatabaseLookup] as CDL
  JOIN [DBADatabase].[dbo].[Clients] as C
  ON CDL.clientid = c.clientid

To get the client information. The client information needs to be manually added to the table as this (in general) needs a human bean to understand. When the script runs every night it will pick up new databases and I add a default value of “Not Entered” to the table which makes it easier to identify the databases that need this additional work. (This also means that as a Team Leader I can monitor that my team are doing this) It can also be added to any scripts which create new databases for deployment.

Then we need to create some measures and calculated columns for our report. I did this as I realised that I needed it when making the report rather than all up front.

I created two calculated columns for size for the databases one for Gb and one for Tb by clicking on the data icon on the left and then new measure

SizeGb = Query1[SizeMB]/1024
SizeTb = Query1[SizeGb]/1024

Some measures for count of Databases, Instances and Servers

Databases = COUNT(Query1[DatabaseID])
Instances = DISTINCTCOUNT(Query1[InstanceID])
Servers = DISTINCTCOUNT(Query1[ServerName])

I also wanted to be able to differentiate between ‘External’ and ‘Internal’ customers. So I created a calculated column for this value using a switch statement.

External = SWITCH(Clients[ClientName],"Not Entered", 0 , "Dev Team",0,"Mi Team",0,"DBA Team",0,"Finance Department",0,"HR",0,"Operations",0,"Payroll",0,"Test Team",0,"Systems Team",0,"Unknown",0,1)

I create a donut chart to show the size of the database in Gb by client (and no, my real clients are not rock bands 🙂 ) as shown below. I formatted the title, legend and background by clicking on the paintbrush in the visualisation pane. I would encourage you to investigate the options here.

db4
The other donut chart is number of clients per location (and those are SQL User group locations in the UK and my hometown Bolton)

db5

The rest of the visualisations on that report are cards and tables which I am sure that you can work out.

I created a map to show the location of the databases

db6

And after reading this post http://sqldusty.com/2015/08/03/power-bi-tip-use-the-treemap-chart-as-a-colorful-slicer/ by Dustin Ryan I created a colourful slicer for environment and the client and then added some other information. The important thing here is to pick the information that the person looking at the report needs to see. So if it is recovery model, compatibility level, collation, page verify setting, mirroring, replication, size and number of databases then this report is correct but I doubt that’s what you want 🙂

You can slice this report by location, client or environment. For example, I can easily see which clients have data in Exeter and the size and number of databases

db7

Or if Metallica ring me up I can quickly see that they have 4 databases, just under 69Gb of data in Exeter and it isn’t mirrored. You will notice that it is not easy to see the recovery model or the compatibility level. If you hover over the results you get a highlight figure which shows the data is filtered but it is not shown visually very well as there are over a thousand databases using full recovery model.

db8

If we are asked about the Integration environment we can see that it is hosted in Bolton, Manchester, Southampton and Exeter and comprises of 394 databases and 739 Gb of data. It is also easier to see the compatibility level and recovery model as the ratios are larger

db9

Once we have created the report in the way that we want we can then publish it to powerbi.com and share it with others if we wish. Publishing is as easy as pressing the publish button and entering your powerbi credentials but if you want your data to automatically refresh (and this is the point of the exercise to remove manual work) then you will need to install and configure the PowerBi gateway and schedule a refresh I will post about this later.

Once the report is published you can access it in the browser and create a dashboard by clicking the pin in the top right of a visualisation and a pop up will ask you which dashboard you wish to pin it to (Another recent update to Power Bi)

db10

Once you have a dashboard you can then perform some natural language question and answer on it. This can be quite interesting and not always quite what you (or your report readers) might expect but it is getting better all the time

db11

You have to remember to use the names of the columns correctly

db12

But once you have the query correct you can alter it by adding “as a VISUALISATION” and choose the visualisation

db13

db14

And once you have the visualisation you can pin it to the dashboard

I think you can see how useful it can be

db15

This doesn’t work quite as you expect

db16

But this does

db17

How about this (and yes it felt wrong to type!)

db18

And the auditors would love to be able to do this. (This is an old copy of the database in case The Eagles people are reading this – your database is backed up every 15 minutes)

db19

Or this for a DBA ( Yes, my obfuscation script database naming convention is a bit bland)

db20

Or the DBA team manager might choose this one

db21

The advantage that I cannot show via static pictures is that the data, visualisation and the suggestions alter in real time as you type

I hope that you have found this useful and that you can see the benefits and advantages of using a DBA Database and empowering people to use self-service to answer their own questions leaving the DBA time to do more important things like drinking coffee 🙂

As always if you have any questions or comments please feel free to post them on the blog.

I have written further posts about this

Using Power Bi with my DBA Database

Populating My DBA Database for Power Bi with PowerShell – Server Info

Populating My DBA Database for Power Bi with PowerShell – SQL Info

Populating My DBA Database for Power Bi with PowerShell – Databases

Power Bi, PowerShell and SQL Agent Jobs

Using Power Bi with my DBA Database

Posted on by

Every good DBA should have a DBA database. A place to store information about all of their instances and databases.

I have an InstanceList table which looks like this

CREATE TABLE [dbo].[InstanceList](
[InstanceID] [int] IDENTITY(1,1) NOT NULL,
[ServerName] [nvarchar](50) NOT NULL,
[InstanceName] [nvarchar](50) NOT NULL,
[Port] [int] NOT NULL,
[AG] [bit] NULL,
[Inactive] [bit] NULL CONSTRAINT [DF_InstanceList_Inactive] DEFAULT ((0)),
[Environment] [nvarchar](25) NULL,
[Location] [nvarchar](30) NULL,
CONSTRAINT [PK_InstanceList_ID] PRIMARY KEY CLUSTERED
(
[InstanceID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

I use this as the basis for all of my information gathering. By adding Server name, Instance Name , Port, Environment and Location to the table I use overnight Agent jobs to run Powershell scripts to gather information about all of the instances. This way the information is dynamic and gathered from the server, so when we add RAM and change Max memory this is updated the next time the script runs. You can also automate your installation and decommission procedures (using Powershell scripts) to add the information to the DBA database automatically

I have 4 scripts

  • ServerInfo which gathers Windows OS information such as Version and edition of the operating system, number of processors,amount of RAM, IP address, domain name etc
  • SQLInfo which gathers information about the instance such as SQL version, edition, collation, max and min memory, MAXDOP , service accounts and start modes, default file locations etc
  • Database information such as size, data usage, index usage, last backup dates, owner and many more
  • Agent Job which gathers the outcome of the jobs that have run, their names, category into two tables one for a server rollup and one for details about each job

Recently I have received a lot of requests for information from various sources, auditors asking about encryption and backup policies, Project managers asking about database and sql versions, compliance asking about numbers of Windows 2003 servers or SQL 2005 servers, system teams asking which serves in a particular location can be turned off at which time dependant on which system they are supporting for a power down

Before we had the DBA database holding all of the information about the instances we would have struggled to be able to compile this information and when I saw Power Bi was released to GA I thought that it would be a good place to start to learn about it. By using data that I understood and answering questions that I knew the format of the answer I could be more confident about experimenting – ie. if I know I have 100 servers then any result for servers that exceeds that is incorrect

I have never been a BI guy, I claim no expertise in the correct methods of manipulating the data. There may very well be better methods of achieving these results and if there please feel free to comment below so that I can improve my knowledge and keep on learning

All data shown in the examples below has been generated from real-life data but all identifiable data has been altered or removed. I have no servers in Bolton, it is where I am from originally!!

I downloaded Power BI Desktop from powerbi.com and ran the installer and the first screen you see is this one

1

I then clicked on Get Data

2

And then SQL Server and filled in the details for my DBA Database and clicked connect

3

I used my current Windows credentials

4

It then asked me which tables I wanted to load so I said all of them 🙂

5

Once I had loaded the data I looked at the queries and renamed some of the columns to make more sense to me. I also created some calculated columns by clicking New Column

I created a relative date column using this code from Chris Webb http://blog.crossjoin.co.uk/2013/01/24/building-relative-date-reports-in-powerpivot/

Relative Date Offset=INT([Date] – TODAY()
Relative Date=IF([Relative Date Offset]=0
, "Today"
, "Today " & IF([Relative Date Offset]>0, "+", "") & [Relative Date Offset])

This will enable me to show data for the last day

I also did the same for days of the week

DayOfWeek = CONCATENATE(WEEKDAY('Info AgentJobDetail'[LastRunTime],2),FORMAT('InfoAgentJobDetail'[LastRunTime]," -dddd"))

Because I struggled to show the information about the Operating system I also created two columns for OS name and OS edition by adding columns as shown below

Operating System Version = SWITCH('Info ServerOSInfo'[OperatingSystem], "Microsoft Windows Server 2012 Datacenter", "Server 2012",
"Microsoft Windows Server 2012 Standard","Server 2012",
"Microsoft Windows Server 2012 R2 Datacenter", "Server 2012 R2",
"Microsoft Windows Server 2008 R2 Standard", "Server 2008 R2",
"Microsoft Windows Server 2008 R2", "Server 2008 R2",
"Microsoft Windows Server 2008 R2 Enterprise", "Server 2008 R2",
"Microsoft® Windows Server® 2008 Standard", "Server 2008",
"Microsoft® Windows Server® 2008 Enterprise","Server 2008",
"Microsoft(R) Windows(R) Server 2003, Standard Edition", "Server 2003",
"Microsoft(R) Windows(R) Server 2003, Enterprise Edition", "Server 2003",
"Microsoft Windows 2000 Server", "Server 2000",
"Unknown")

And

Operating System Edition = SWITCH('Info ServerOSInfo'[OperatingSystem], "Microsoft Windows Server 2012 Datacenter", "DataCenter",
"Microsoft Windows Server 2012 Standard","Standard",
"Microsoft Windows Server 2012 R2 Datacenter", "DataCenter",
"Microsoft Windows Server 2008 R2 Standard", "Standard",
"Microsoft Windows Server 2008 R2 Enterprise", "Enterprise",
"Microsoft® Windows Server® 2008 Standard", "Standard",
"Microsoft® Windows Server® 2008 Enterprise","Enterprise",
"Microsoft(R) Windows(R) Server 2003, Standard Edition", "Standard",
"Microsoft(R) Windows(R) Server 2003, Enterprise Edition", "Enterprise",
"Microsoft Windows 2000 Server", "Server 2000",
"Unknown")

Then I started to play with the data.

This is probably not how a professional would phrase it but I would say that if you don’t know how to use a new application be brave and give it a try.

OBVIOUSLY you are a PROFESSIONAL DBA and will not do anything that would endanger production, use a backup of your database and work locally if you need to.

The first thing I wanted to know was how many servers I had by operating system, how many by SQL version and the location of them so that I could answer the questions I had been asked. I had already written a query to get the correct information to give to the requestors so I knew the correct answers which was also an advantage. I did this like this

I expanded the Info ServerOSInfo query and dragged the ServerName field to the report which created a table of names

6

I then changed the ServerName values to Count

7

I then dragged the calculated column Operating System Version to the table

8

If I click on the table and then donut chart in the visualisations it changes to

9

So you can quickly see how you want the data displayed

I then decided to look at the number of SQL 2005 instances that I had and as I had relationships between SQLInfo and Instancelist and Clients I could build a more dynamic report.

I created a donut chart with SQLVersion as the legend and InstanceID as the values and a table of SQLVersion, ServerName and Instance Name. I also created a card that was count of InstanceID

10

Now it starts getting really useful. If I want to know how many SQL 2005 instances I have I simply click on SQL2005 in the donut chart and the rest of the report changes

11

This is very cool and I hope you can see how useful this could be and how brilliant it would be to enable relevant people within the organisation the ability to look at that report and answer their own questions.

Lets take it to the next step. I have a location column in the InstanceList table which comprises of town names. If I choose a map and drag that column to the Location field and set Values and Color Saturation to the Count of InstanceID

12

and create two tables one of client with a count of instanceid and one location with a count of instance id I can do this

13

Look at how it dynamically changes as you click on the data labels – This is very cool and makes me smile every time!! I altered the colour saturation colours to make it easier to see. Now if I am asked about SQL 2005 servers I can quickly click on SQL 2005 and

14

I can see that there are 32 instances, most are in Southampton, and which clients they support

If I click a location rather than SQL version the report alters like so

15

So you can simply pass the report file to your colleagues to enable them to use it or you can publish it to Powerbi.com. I am not going to go into any detail about the costs or licensing etc I will just say it is as easy as clicking publish. If you wish to have the information automatically refreshed there are some more steps that you would need to go through which are detailed here which enable you to connect your on-premise database to Powerbi using the data management gateway, alternatively you can simply refresh the data in the report and then publish it and replace the existing report.

Once the report is in powerbi.com you can enable Q and A on the data. This is some kind of supernatural mystical magical query language which enables you to query your data with natural language and will alter the results as you type and even cope with (deliberate for screenshot) spelling mistakes 🙂

16

I also created a report for my Agent Jobs to enable me to quickly and easily see which Jobs have failed in the last day

17

I did this by filtering the report by Relative Date Offset greater than -1 (today) and isenabled = True and Outcome = Failed

There are many many more ways I can see this being useful and I hope I have given you some ideas and encouraged you to try for yourself and find out more

I have written further posts about this

Using Power Bi with my DBA Database

Populating My DBA Database for Power Bi with PowerShell – Server Info

Populating My DBA Database for Power Bi with PowerShell – SQL Info

Populating My DBA Database for Power Bi with PowerShell – Databases

Power Bi, PowerShell and SQL Agent Jobs