Using dbatools to verify your SQL Server instances version compliance

One of the main DBA’s duties is to guarantee that SQL Server instances are up-to-date in terms of patches (Service Packs, Cumulative Updates or Security Updates).

Recently, dbatools added a new command that turns this validation a piece of cake. Thanks to Simone Bizzotto (@niphlod) for baking up the command that Shawn Melton (@wsmelton) and I initially requested.

Some dbatools users already expressed their happiness with the command, like Jess Pomfret

So, I thought that this information should be shared with other people too.

Let me introduce to you – Test-DbaSqlBuild

This new command is available since v0.9.150.

If you are running this version or higher you can already take advantage of it, otherwise, you need to upgrade your module version first. Assuming you have installed the module from the PowerShell Gallery and that you have internet access, you can update as easy as running the following command:

Update-Module dbatools -Force

Otherwise, you can use the Save-Module command and then copy the files to your destination host.

How does the command works?

This command uses the dbatools-buildref-index.json file that contains all the information about SQL Server builds. This is the same file that feeds the dbatools builds table already shown on the introducing the community-driven build reference blog post.

The combinations

To run the command, we need at least two parameters. The -SqlInstance or -Build and one of the following 3: -MinimumBuild, -MaxBehind or -Latest.

The most straight example is when you want to check if the instance is running the latest build (it can be a security update even if not labelled as CU). To do that you just need to run:

Test-DbaSqlBuild -SqlInstance <instance> -Latest

In this example, I’m testing an instance that I have patched with SQL Server 2012 to SP4 but after that, the new security fix for Meltdown/Spectre was released, that is why the Compliant property shows False, it is not on the Latest existing build.
Note: If you just want to check for the latest SP and CU (leaving out the security patches) you need to use -MaxBehind "0CU"

Now, let’s say that you want to confirm that a specific instance is no more than 1 CU behind.
It’s easy as:

Test-DbaSqlBuild -SqlInstance <instance> -MaxBehind "1CU"

The output:

In this example, you can see that this instance is not compliant. Why? Because it is running the SQL Server 2016 SP1 CU5 but we asked for a max behind of 1 CU and that is the SP1 CU6 (because at the moment I’m writing this text, the most recent version is SP1 CU7).

Easy, right?
Keep in mind that for -MaxBehind you can also specify the number of service packs using -MaxBehind "1SP" and even use both, SP and CU like -MaxBehind "1SP 1CU".
Now, you can use multiple instances and verify them all like:

$SQLInstances = "SQL1", "SQL2", "SQL3"
Test-DbaSqlBuild -SqlInstance $SQLInstances -MaxBehind "1SP"

Other (real and useful) scenarios

We saw the “online” example where we will query each instance at the moment. Now, I want to share with you two more examples.

Using central database as data source

Let’s say you have a central database where you keep some of the information about your estate and one of those pieces of information is the SQL Server build version.

One code example:

$Instance = "<instance>"
$Database = "<centralDatabase>"
$InstancesTable = "dbo.Instances"
$SQLServersBuilds = Invoke-DbaSqlcmd -ServerInstance $Instance -Database $Database -Query "SELECT serverName, productVersion FROM $InstancesTable" 
$SQLServersBuilds | ForEach-Object {
    $build = $_.ProductVersion.SubString(0, $_.ProductVersion.LastIndexOf('.'))
    $serverName = $_.ServerName
    Test-DbaSqlBuild -Build $build -MaxBehind "1CU" | Select-Object @{Name="ServerName";Expression={$serverName}}, *
} | Out-GridView

For this example, I will query my dbo.Instances table and get the serverName and productVersion columns.
This is how it looks when running the select statement on SSMS:

You can pick that data and pass it to the Test-DbaSqlBuild command to know if it is compliant or not.

Then for each result, we will format the productVersion value to use just a 3 part value (it is how we catalog on dbatools build reference file) and pass it to the Test-DbaSqlBuild command.
In this example, I’m piping the output to Out-GridView so I can filter my results and add a filter for compliant equals false
.

Doing ad-hoc testing

The other example I would like to share is using the -Build parameter.
Imagine that you know that your SQL server instance is running build “13.0.4001” corresponding to SQL Server 2016 SP1, and you want to know if it is too far behind compared with the last available CU update. If we run the following command we will know it:

Test-DbaSqlBuild -Build "13.0.4001" -MaxBehind "0CU"

From this output we know that the most recent version is SP1 CU7 and we asked for latest SP1 (without CU), this means we are not Compliant

To give another example of this “ad-hoc” testing, we can use the following code provided by Simone Bizzotto to verify if our instances have the Meltdown/Spectre fix in place:

#Meltdown/Spectre check:
$mapping = @{
    '2008'   = '10.0.6556'
    '2008R2' = '10.50.6560'
    '2012'   = '11.0.7462'
    '2014'   = '12.0.5571'
    '2016'   = '13.0.4466'
    '2017'   = '14.0.3015'
}
$serv = 'SQL01','SQL02'
foreach($ref in (Get-DbaSqlBuildReference -SqlInstance $serv)) {
    Test-DbaSqlBuild -SqlInstance $ref.SqlInstance -MinimumBuild $mapping[$ref.NameLevel]
}

Thanks for reading.

New version of sp_WhoIsActive (v11.20) is available – Deployed on 123 instances in less than 1 minute

Last night, I received Adam Machanic’s (b | t) newsletter “Announcing sp_whoisactive v11.20: Live Query Plans”.

For those who don’t know about it, sp_WhoIsActive is a stored procedure that provides detailed information about the sessions running on your SQL Server instance.
It is a great tool when we need to troubleshoot some problems such as long-running queries or blocking. (just two examples)

This stored procedure works on any version/edition since SQL Server 2005 SP1. Although, you only will be able to see the new feature (live query plans) if you run it on SQL Server 2016 or 2017.

If you don’t receive the newsletter you can read this announcement here and subscriber to receive the next ones here.

You can read the release notes on the download page.

Thank you, Adam Machanic!

The show off part

Using the dbatools open source PowerShell module I can deploy the new latest version of the stored procedure.

By running the following two lines of code, I updated my sp_WhoIsActive to the latest version (we always download the newest one) on my 123 instances in less than one minute (to be precise, in 51,717 seconds).

$SQLServers = Invoke-DbaSqlcmd -ServerInstance "CentralServerName" -Query "SELECT InstanceConnection FROM CentralDB.dbo.Instances" | Select-Object -ExpandProperty InstanceConnection
Install-DbaWhoIsActive -SqlInstance $SQLServers -Database master

The first line will retrieve from my central database all my instances’ connection string.
The second one will download the latest version, and compile the stored procedure on the master database on each of the instances in that list (123 instances).

Thanks for reading

Using Common Table Expression (CTE) – Did you know…

Today I will write just a short blog post to do a quick reminder!

I still hear a lot of people suggesting CTEs because they think it works like a temporary table (you populate the table and then it can be/is reutilized).

It doesn’t!

From de documentation:

Specifies a temporary named result set, known as a common table expression (CTE).

Maybe they are focusing on the “temporary” word.

Using the CTE two times will perform two different executions! Don’t believe me? See the next example!
If we run the following code do you expect to get the same value for both queries? Note: we have a UNION ALL between them.

WITH cte AS
(
	SELECT NEWID() AS Col1
)
SELECT Col1
  FROM cte
UNION ALL
SELECT Col1
  FROM cte

Sorry to disappoint you but it will run the CTE’s code twice and return the value(s) from each execution.
As we are using the function NEWID(), two different values will be generated.

output.png

To complete the question: “Did you know that CTE’s code will be executed as many times as you use it?”

Thanks for reading!

TSQL Tuesday #96: Folks Who Have Made a Difference

tsql2sdayThis month’s T-SQL Tuesday is brought to us by Ewald Cress‏ (blog | twitter) and is all about “folks who have made a difference” in our careers.

Thank you, Ewald! This is a great topic!

Here is my short list:

Paulo Silva (in)

He was my first boss in the IT world! I was his apprentice when I started my internship. He was going to move to a manager position and I  had to continue his work. He was responsible for the beginning of my career with SQL Server 2000 and VB6.

He was one of the main culprits for my growth not only in IT but also as a person!

Etienne Lopes (t | b)

After 5 years working on IT I had the tremendous pleasure to meet Etienne. This guy is a professor! He has the gift of the word!

I have worked closely with him for about 2 years and were one of the best times of my career! I always consider myself as a sponge, and as long Etienne shared is knowledge I felt I was absorbing every single word!

Much of the bases I have with SQL Server I learned from him!

André Batista (t) / Niko Neugebauer (t | b)

These two guys are the responsible for my very first talk on a user group (SQLPort).

After that, I became more and more involved with the local community and today I speak for more user groups and I help with SQL Saturday / TugaIT events in Lisbon!

Rob Sewell (t | b)

The one and only DBAWithABeard! My recent experiments were from blog posts/presentations that I read/saw from Rob. PowerBI & Pester are just two of them. He is super accessible and always willing to help.

Chrissy LeMaire (t | b)

I met Chrissy less than 2 years ago at the TugaIT conference (May 2016) in Lisbon. At the time has passed like 1 month from the dbatools.io launch date and I had written a couple of PowerShell scripts that I thought would be nice to add to the initial tool.

We talked, exchange contacts and one month later, in June, I was submitting my first pull request to the dbatools GitHub repository.

From that time until now it has been a blast! I learned so much about PowerShell with her and she is also one of the responsible for my MVP not only because she nominated me for the very first time but also because all the visibility that the project brought to me.

She was also the first person delivering a presentation with me. 🙂

People I know from the magazines or internet

People that helped me to understand SQL Server much better and from whom I have read a lot of articles: Itzik Ben-Gan (I remember the times I read the SQL Magazine with great articles from him), Paul Randal, Kimberly L. Tripp, Adam MachanicPaul White and Kendra Little.

Wrap up

I could add more people to the list but, those are the ones that I want to highlight from different periods (the beginning, middle and nowadays) of my career.

Thank you all!

 

DELETE data on SQL Server HEAP table – Did you know…

Before I complete my question let me provide context.

I’ve received an alert saying that a specific database could not allocate a new page (disk was full)

The message that you will see on the SQL Server Error log is:

Could not allocate a new page for database ” because of insufficient disk space in filegroup ”. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

I didn’t know the database structure or what is saved there, so I picked up a script from my toolbelt that shreds all indexes from all table. Just some information like number of rows and space that it is occupying. I have sorted by occupying space in descending order, look what I found…

So…my script has a bug? 🙂 No, it hasn’t!

The joy of heaps

First, the definition:

A heap is a table without a clustered index. One or more nonclustered indexes can be created on tables stored as a heap. Data is stored in the heap without specifying an order. Usually data is initially stored in the order in which is the rows are inserted into the table, but the Database Engine can move data around in the heap to store the rows efficiently; so the data order cannot be predicted. To guarantee the order of rows returned from a heap, you must use the ORDER BY clause. To specify the order for storage of the rows, create a clustered index on the table, so that the table is not a heap.

Source: MS Docs – Heaps (Tables without Clustered Indexes)

Until now, everything seems normal, it is just a table with unordered data.

Why am I talking about heaps?

Not because of table name (was created on propose for this demo), let me show to you the whole row of the script:

Do you have a clue? Yup, index_id = 0. That means that our table does not have a clustered index defined and therefore it is an HEAP.

Even so, how it is possible? 0 rows but occupying several MB…

The answer is…on the documentation 🙂

When rows are deleted from a heap the Database Engine may use row or page locking for the operation. As a result, the pages made empty by the delete operation remain allocated to the heap. When empty pages are not deallocated, the associated space cannot be reused by other objects in the database.

source: DELETE (Transact-SQL) – Locking behavior

That explains it!

So…what should I do in order to get my space back when deleting from a HEAP?

On the same documentation page we can read the following:

To delete rows in a heap and deallocate pages, use one of the following methods.

  • Specify the TABLOCK hint in the DELETE statement. Using the TABLOCK hint causes the delete operation to take an exclusive lock on the table instead of a row or page lock. This allows the pages to be deallocated. For more information about the TABLOCK hint, see Table Hints (Transact-SQL).
  • Use TRUNCATE TABLE if all rows are to be deleted from the table.
  • Create a clustered index on the heap before deleting the rows. You can drop the clustered index after the rows are deleted. This method is more time consuming than the previous methods and uses more temporary resources.

Following the documentation, it suggest we can to use the TABLOCK hint in order to release the empty pages when deleting the data.
Example:

DELETE 
  FROM dbo.Heap WITH (TABLOCK)

What if I didn’t that way or if anyone else run a DELETE without specify it?

You can rebuild your table using this syntax (since SQL Server 2008):

ALTER TABLE dbo.Heap REBUILD

This way, the table will release the empty pages and you will recovery the space to use on other objects in the database.

Wrap up

I hope that with this little post you understood how and why a HEAP can have few rows or even zero but still occupy lots of space. Also I have mentioned two ways to solve this problem.
Also, I have found databases with dozens of HEAPS almost empty or even empty that were occupying more than 50% of the total space allocated to the database. And guess what? People where complaining about space.

To finish, I need to complete the title, Did you know…you should use TABLOCK hint when deleting data from a HEAP?

Thanks for reading!

PowerShell Modules Central – Share with community – What PowerShell modules are you using?

Like the blog post title states this is all about sharing with others! My idea is to share with the community which PowerShell modules you are using.

Let me introduce to you the PowerShell Modules Central

PowerShellModulesCentral is a GitHub repository that was founded as a central hub to a list of PowerShell modules that people know/use. Each module has a file describing its name, basic information about the module, as well as one or more blog posts/videos from people that have written about or used them.

This way we can reduce friction when people are starting out or are trying to solve similar problems.

Why?

When a new module appears on the PowerShell scene it can be difficult to advertise and gain mindshare among developers/end users who could be interested in it. There are also times when difficulties arise in finding if a good tool exists or not, if its up to date, and how relevant it is in the community.

Why not just use the PS Gallery or script center?

This is, by no means, a replacement of those. Actually it is opposite, it is meant to be a community complement. Normally, when you need to do a task that you’ve never done before you like to have some jump start like blog posts or videos, and maybe you find the ones that are very close to your real scenario.
This repository enables not only people to write blog posts and share them with the community, but also the new guy (on PowerShell or just on a new task) that is searching for a specific tool to accomplish a task.
I can go to the PowerShell Gallery and see that the module I want to use has 1K downloads. That is really cool! It will give me confidence to use it. But, next, when you want to start working with it maybe you would like to see examples. The objective here is to have a quick look on some problems and tools used to solve them, as they can also be your problem.

Let me tell you a quick story

I went to google, found a PowerShell Gallery script, and after checking that the script didn’t work with some particulars I did a further research and found (google results – page 3 or 4 due ranks) a comment on a forum pointing to the GitHub repository. Guess what? The problems I was having were already addressed. 😉

Are you a module owner? Are you writing something new? Do you contribute to a module? Share it! The ones I know and use could be very different from the ones you know and use! Why not share?

How can this help me?

Are you trying to find a module to work on a specific task? Use the search on the top of the repository page and try to find what you need.

  • Working with Windows? Type “Windows”.
  • Working with SQL Server? Type “SQL Server”.
  • Do you know the author’s name? You can search that way too.
  • Have you read a blog post before and just remember one word or the blogger’s name? Type it and see what you find.

How to contribute?

Just fork the repository, add the information and send a pull request (PR). I will merge it once everything is OK.
For new modules please use the template available here. If you find that module already exists, you just need to add your URLs and any other information to be updated, tags that you think may be useful, add something to description, etc.

If you use a module that doesn’t have a blog post and/or videos yet, you can submit a PR anyway so all of the community can know that it exists and maybe someone will write about it!

Follow up

Follow the repository news by clicking on “Watch” button and/or follow @psmcentral Twitter account.

Feel free to share this blog post! The more people we reach, the better!

Thanks for reading.

Generate Markdown Table of Contents based on files within a folder with PowerShell

Last week I was talking with Constantine Kokkinos (b | t) about generating a Table Of Contents (TOC) for a GitHub repository.

He wrote a cool blog post – Generating Tables of Contents for Github Projects with PowerShell – about it and I will write this one with a different problem/solution.

Context

I’m working on a new project (news coming soon) that uses a GitHub repository and I expect to have a big number of files within a specific folder.

Requirement

After some pull requests and merges, I want to update my readme.md file and update the INDEX with this TOC.
For this:

  • I want to be able to generate a TOC based on the existing content of a specific folder.
  • Each TOC entry must be composed by a name and a link to the .md online file.
  • This list must be ordered alphabetically.

Then, I can copy & paste and update the readme.me .
NOTE: For now, I just want a semi-automatic way to do it. Maybe later I will setup Appveyor and make this fully automated 🙂 ).

Approach

Get all files with .md extension, order by name and, for each one, generate a line with a link to the GitHub repository .md file.

To do the list I will use the “*” (asterisk) character after a “TAB” to generate a sub list. (This is Markdown’s syntax)

The code

I have three parameters:

  • the $BaseFolder – It’s the folder’s location on the disk
  • $BaseURL – to build the URL for each file. This will be added as a link
  • $FiletypeFilter – to filter the files on the folder. In my case I will use “*.md” because I only want markdown files.

The code is:
UPDATE: Thanks to Jaap Brasser (b | t) who has contributed to the this code by adding the help and some improvements like dealing with special characters on the URL (spaces). You can find the most recent version of this Convert-FolderContentToMarkdownTableOfContents.ps1 function here on my GitHub

Running this code pointing to my “NewProject” folder

I will get this output (This have fake links but just to show the output format)


Index


Nice! This has the following code behind:

## Index
* Modules
  * [File1](https://github.com/user/repository/tree/master/Modules/File1.md)
  * [OneNewFile](https://github.com/user/repository/tree/master/Modules/OneNewFile.md)
  * [OtherFile](https://github.com/user/repository/tree/master/Modules/OtherFile.md)

Now, I can copy this markdown code and update my readme.md file.

Final thoughts

This isn’t rocket science 🙂 but it is an idea and a piece of code that will help me and maybe can help you too 🙂

Read Constantine’s blog post (Generating Tables of Contents for Github Projects with PowerShell) to get different ideas.

Thanks for reading