Write Your first Pester Test Today

I was in Glasgow this Friday enjoying the fantastic hospitality of the Glasgow SQL User Group @SQLGlasgow and presenting sessions with Andre Kamman, William Durkin and Chrissy LeMaire

I presented “Green is Good Red is Bad – Turning your checklists into Pester Tests”. I had to make sure I had enough energy beforehand so I treated myself to a fabulous burger.

20171110_114933-compressor.jpg

Afterwards I was talking to some of the attendees and realised that maybe I could show how easy it was to start writing your first Pester test. Here are the steps to follow so that you can  write your first Pester test

Decide the information you wish to test
Understand how to get it with PowerShell
Write a Pester Test

The first bit is up to you. I cannot decide what you need to test for on your servers in your environments. Whatever is the most important. For now pick one thing.

Logins – Lets pick logins as an example for this post. It is good practice to disable the sa account is advice that you will read all over the internet and is often written into estate documentation so lets write a test for that

Now we need the PowerShell command to return the information to test for. We need a command that will get information about logins on a SQL server and if it can return disabled logins then all the better.

As always when starting to use PowerShell with SQL Server I would start with dbatools if we run Find-DbaCommand we can search for commands in the module that support logins. (If you have chosen something none SQL Server related then you can use Get-Command or the internet to find the command you need)

find-dbacommand.png

Get-DbaLogin . That looks like the one that we want. Now we need to understand how to use it. Always always use Get-Help to do this. If we run

we get all of the information about the command and the examples. Example 8 looks like it will help us

get-dbalogin example

So now try running the command for our disabled sa account

disabled sa account

So we know that if we have a disabled sa account we get a result. Lets enable the sa account and run the command again

not disabled.png

We don’t get a result. Excellent, now we know what happens for a successful test – we get one result and for failed test we get zero results. We can check that by running

login count

The first one has the account disabled and the second one not. So now we can write our Pester Test. We can start with a Describe Block with a useful title. I am going to add a context block so that you can see how you can group your tests.

describe context

and then we will write our test. Pester Tests use the It keyword. You should always give a useful title to your test

it should

Now we can write our test. We know that the command returns one result when we want it to pass so we can write a test like this

login test.png

The code I have added is

which is
  • the code for getting the information about the thing we wanted to test (The count of the disabled sa logins on the instance)
  • a pipe symbol |
  • The Should key word
  • The Be keyword
  • and the result we want to pass the test (1)

Ta Da! One Pester test written. You can run the test just by highlighting the code and running it in VS Code (or PowerShell ISE) and it will look like this for a passing test

passing test

It is better to save it for later use and then call it with Invoke-Pester

invoke

So now you can write your first Pester test. Just find the PowerShell to get the information that you need, understand what the results will be for passing and failing tests and write your test 🙂

TSQL2sDay – Get-PostRoundup

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

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

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!

 

 

#TSQL2sDay – Starting Out with PowerShell

tsql2sdayThis months TSQL2sday is hosted by me!

Surprise! – I chose PowerShell

I am really looking forward to seeing what other people post. Some advanced scripts that will help you and show you how you can use PowerShell to save you time and hopefully some beginner posts explaining experiences or showing you how to start with PowerShell and SQL Server. I decided to go with the latter.

First though a warning.

PowerShell is another language, you are not going to be as proficient in a new language as you are in the language you spend all day working with. You will have to go through the learning curve and you will have to understand how to interpret errors. There is a learning curve just like with any language. Undoubtedly you will get frustrated at times. Reach out for help. Use twitter, use the #powershellhelp in the SQL Server Community Slack channel. There are many other places and plenty of people who will be glad to help you.

Open the Editor

If you are using a Windows machine you will have PowerShell installed. You will find it in your start menu on Windows 7 under All Programs, Accessories, Windows PowerShell folder, and then click Windows PowerShell or Windows PowerShell ISE. On Windows 8 or 10 search for PowerShell.

PowerShell or PowerShell ISE? Use PowerShell instead of cmd.exe for command line usage and ISE for developing scripts, functions and modules. (However, read on before making your choice)

PowerShell on Other O/S’s (and Windows!)

Just as SQL Server is available on Linux PowerShell is also available cross-platform, you can run and edit PowerShell on Linux and on Mac natively. The best editor to use is VS Code which is also available cross-platform. In fact, as Microsofts David Wilson says in this blog post

The PowerShell ISE has been the official editor for PowerShell throughout most of the history of Windows PowerShell. Now with the advent of the cross-platform PowerShell Core, we need a new official editor that’s available across all supported OS platforms and versions. Visual Studio Code is now that editor and the majority of our effort will be focused there.

So my advice is, whichever operating system you are using, use VS Code to write, edit, debug and run your PowerShell as that is the editor where Microsoft are spending the majority of their effort in development. Follow the instructions here to install You can also use it with many other languages including T-SQL. I find it a very useful tool

Where to start?

Well that depends what you want to do. Start by reading. Start by trying to accomplish something in PowerShell even if you know how to do it another way. This will also help you to realise when you are trying to use a hammer to put in a screw (using the wrong tool for the job)

What Command?

You don’t know where to start so how do you know the command to use? Here is a first command. You will use it often. I use it everyday still despite using PowerShell daily for many years. But don’t run it yet!

PowerShell is quite intuitive in it’s command naming. It uses a Verb-Noun syntax. You know without me explaining what this command will do. It will “Get” the “Command” (s). PowerShell uses singular nouns for its commands. I have 8000+ commands on my machine so it would have overloaded you with commands if you had run it without filters!

We use * as a wildcard in PowerShell so we can use that with our Get-Command command to find some commands to run. Type Get-Com and then hit the tab button. This will not only help you as it will auto-complete, it will reduce the number of errors as it will only auto-complete correct command names :-).

This will show all commands which end with service. The results will have some different headings. The screenshot below shows just one command to reduce confusion.

03 - get-service.png

The type of command, the name of the command, the version of the command and the module it is in. PowerShell uses modules to group commands together and to enable you to install the ones that you require from a repository like the PowerShell Gallery (more on this later).

You can find all of the commands in a module with the Get-Command command like this

This will show you all of the commands in the management module which may be a good place to start exploring. In this module for example

  • Test-Connection is like ping but better
  • Test-Path tests the existence of files and file paths
  • *-Service works with services
  • *-Process works with processes
  • *-Computer for power options for a computer
  • Get-ComputerInfo which will give you computer information (surprisingly 🙂 )

This is also something to remember in the future when you want to know what commands a module has or what the precise name of the command is.

Maybe once you install the dbatools module, the sqlserver module or the SSRS module??

HOMEWORK – Examine the names of commands in other modules that you find in

or for more choices

How do I use that command though?

You need to read! PowerShell comes with a lot of documentation, a lot of it is available on your machine. Lets introduce another command

As you can imagine, this gets the help for a command or returns help topics about PowerShell. Again, I use this every single day and you should too. Lets start with the first one. Lets get help for the Get-ChildItem command

Now, some of you may not get very much returned and a message at the bottom which says

Get-Help cannot find the Help files for this cmdlet on this computer. It is displaying only partial help.
— To download and install Help files for the module that includes this cmdlet, use Update-Help.
— To view the Help topic for this cmdlet online, type: “Get-Help Get-ChildItem -Online” or
go to https://go.microsoft.com/fwlink/?LinkID=113308.

When you have something else to do I would recommend running Update-Help as it suggests (It can take a few minutes to run) but for now make use of the -Online switch.

HOMEWORK – Run Update-Help

This is available for all of the Microsoft modules and some of the better open source modules, dbatools is an example of a (brilliant) community module which has -Online URLs for it’s commands. Running Get-Help (with or without the -Online switch) will give you plenty of information about the command, the switches available, the inputs and outputs.

Maybe start by using the -examples switch

will show you some examples of how to use the command

05 gethelp examples

Now you can start to explore the file system with this command (also the SQL Server, Registry, Certificate Store and many more things)

I also like to use the ShowWindow switch

which will open up the help in a separate window which can be useful when you are writing a script and it is searchable

04 - gethelp showwindo

But Chrissy prefers to use the -Detailed switch

You can decide which way you like best.

Now you know how to find a command and hot to find out how to use it. If you are using VS Code (or ISE) you have access to Intellisense and snippets to help you with the syntax. You can run a command and get some results.

HOMEWORK – Use Get-Help to understand how to use commands. ( Extra merit marks for teachers pets who read the topics they find using Get-Help About_*) Also, instant demerit points throughout the course (your career) for students who do not use Get-Help when using any command for the first time.

Hopefully you are confident enough to have run some commands. If you are worried about breaking things only run the commands which start with Get- as these should only be returning information or objects and not changing anything.

This isn’t cmd 

This isn’t cmd. What you are getting back is not text.

It is an object.

Unless you choose to use one of the formatting commands or the command you are using outputs using a formatting command (pro-tip don’t do that) then what you will receive as the results of your command is an object.

This means that there is more that you can do with the results than just have them in text format. You want Text File,JSON, XML, CSV, Excel output or to write to a database? Look here for ideas

Objects,Objects,Objects

Objects are awesome. Lets start with a SQL Server Instance object to show you how to explore objects. You will need to install the most useful PowerShell module you will find if you are working with SQL Server – dbatools

Disclosure – I am a contributor to the open source module dbatools. I am presenting a full day workshop in Singapore for the PowerShell Conference Asia in October. I am also proctoring at Chrissy and CK’s PASS Summit in Seattle. I highly recommend that you come and join us at one of those events if you can to further learn how you can use PowerShell to administer SQL Server

You can install it from the PowerShell Gallery using

You will be asked if you want to trust the repository, say Yes. If this doesn’t work on your machine (you are on an older version of PowerShell) follow the instructions on this page.  Or ask for help in #powershellhelp in Slack

Now that the module is installed we can create a SQL Server Instance object by assigning the results of a command to a variable.

A variable in PowerShell is designated by a $.

I suggest that you get used to following this step as well. This is a great way of exploring the results of any command and understanding what you can do with it.

We will use the Get-Member command. Another one that I use every single day.

Warning – You do not have to use these methods to accomplish tasks in PowerShell against SQL Server as both Microsoft and the community have released modules which will enable you to general tasks without “coding”

This is an example to get you comfortable with PowerShell by using a target that you are comfortable with and can recognise.

Now that we have an object we can explore it

By just calling the variable it will display the default properties

06 - default properties.png

If you want to see what else is available you can use Get-Member You just pipe | to Get-Member

This will show you all of the events, methods and properties available on the object.  Methods are really useful, enabling you to do things like start agent jobs or enumerate permissions or members

If you wish to see all of the processes that are running on the instance

Out-GridView is a useful command allowing you to see the results of your command in a graphical format and easily filter using the search bar at the top.

07 - ogv.png

Other things you can do include reading the errorlog

08 - errorlog.png

Get the connections to a database

Get the current Traceflags

The properties that are available at this level are similar to the view that you see in SSMS Object Explorer for the instance. You can see a databases folder in SSMS and you can expand it. You can do the same in PowerShell

That is going to just show you the names of the databases on that instance. You can do the same thing using the pipe | again and Select-Object

This will pass the Databases array of objects “along the pipeline” and then you can use Select-Object to choose them. Multiple properties can be chosen with commas.

09 - databases.png

PowerShell will let you select things that do not exist so for example

does not return an error but some of the columns are empty 🙂 You need to use the names of the properties returned from Get-Member in your Select statement (Hmm, sounds a bit like T-SQL)

18 select.png

You can look at a single database default properties using

You can then use Get-Member to explore deeper and deeper.

You can also look at the Agent from the instance level object

HOMEWORK – Using the code above explore the $SMO object, look at the databases, tables, the columns and the indexes. Explore other properties and select them. (Extra merit points for exploring the SQL Server like a file system using the SQLServer drive. Start with cd SQLSERVER:\ and use Get-ChildItem or its alias dir or ls)

Warning Reminder – You do not have to use these methods to accomplish tasks in PowerShell against SQL Server as both Microsoft and the community have released modules which will enable you to do general tasks without “coding”

But this is a very useful way of understanding and exploring using PowerShell with a type of object that you understand. You will use these methods all of the time and when you need to accomplish the next thing you have the tools. Already. Now.

Recap

You should take from this for the future

  • Get-Command *search* – Find a Command
  • Get-Help NameOfCommand – How do I use the Command ?
  • $var = Some Command here – Set the output of a command to a variable
  • $var | Get-Member – What Events, Methods, Properties do I have on this object
  • $var | Select Property1, Property2, Property3

That’s All Code – Where’s The Easy Buttons ?

There are two answers to this.

Firstly, yes it’s all code, but you will find that code is the future. Every time you run the same piece of code it will do the same thing, every time a junior DBA follows the steps to manually do something in a GUI like SSMS they may make a mistake and a different mistake each time. As DBAs we use stored procedures, views, functions and more, they are all code. Using code is vital for automation, for making things easy, for reducing the risk of mistakes, for getting rid of the mundane.

It is also required for DevopsAgileScrumContinuousIntegrationDeliveryWhatsTheNextBuzzWord which is all the rage (and also a lot of fun with interesting challenges)

As estates get bigger and bigger the GUI becomes less useful to you.

Secondly, remember my warning at the top of this post? There is no easy button. You will have to learn new things you will make mistakes and get errors, you need to ask for help

I Have An ERROR !

Don’t be disheartened, errors happen. The red text looks scary and frightening but its not.

Read the Error

Lets use Microsofts sqlserver module. There are all sorts of ways to get it. If you have SSMS you probably have it already. Lets check

A result like this means that you have it

10 - get-module.png

If not

Again you will be asked if you want to trust the repository, say yes!

If I run

I get

11 - error.png

That error is fairly easy to understand. I can’t connect to an instance that isn’t running

If I run

I get a different error

12 sql login error.png

The Get-SqlLogin from Microsofts sqlserver module returns an error if it cannot find the specified login name. The important part of this error message is ObjectNotFound. That tells us what has happened

If we look at the logins

13 - Logins.png

and choose one that exists everything is good

14 one login.png

HOMEWORK – Here is another object to explore with Get-Member. Explore the Login object

For example

15 - login script.png

But back to errors. Sometimes it isn’t quite so obvious. I have altered my Create-Database function so that it shows an error instead of a helpful message.

16 - db error.png

This is an example of the sort of error message that you might not be able to decipher so easily. Even if you read the message it says Exception calling .ctor with 2 arguments. How can you, as a new PowerShell user work out what has happened?

Objects

Remember, PowerShell is great because of objects? The errors are objects too. Lets have a look at the error in full. This is another piece of code that I still use every single day

The $error variable holds an array of error objects for that session. The [0] targets the 1st object in the array which is the latest. We pipe that to fl which is an alias for Format-List. Normally I am very clear about not using aliases in my blog posts as I believe that you should not use aliases in your scripts and functions (This is due to a bad experience trying to google for % to find out what it was in PowerShell many years ago). However, you are only going to use this at the command line so its ok.

The results are

17 error.png

Once you read the error, you will see that there is a generic connection failed error message which you will be used to seeing and now you can go about fixing it.

Errors aren’t scary. You just have to read them. Sometimes you have to expand them to read them. Once you have read them don’t forget that there is also a lot of help out there on the internet. I recommend the #powershellhelp channel in the slack but with the full error message you will be able to get some useful results from whatever your choice of helpful solutions is.

That’s exactly 3000 words – which is slightly (!) more than I intended to write and a lot to read, so if you have got all the way down here. Congratulations, when you see me at Summit or at a SQL Saturday come up and tell me and I will give you a special sticker 🙂

Hopefully this has given you some guidance to starting to use PowerShell with SQL and how you can help yourself.

Good luck.

Getting SQL Server File Sizes and Space Used with dbatools

I read a great blog post about answering the question how big is the database using T-SQL on SQL Buffet and wondered how much I could do with the dbatools module

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

I know that there is a Get-DBADatabaseFreeSpace Command written by Mike Fal b | t and using Glenn Berry’s diagnostic queries

First thing as always is to look at the help

which will show you the help for the command and some examples

Lets look at the details for a single instance

This is what it looks like

02 - singel server.gif

and yes it really is that fast, I have not speeded this up. 232 ms to get those details for an instance with 19 databases

03 - Measure Command.PNG

What information do you get ? Lets look at the information for a single database, you get an object for each file

Server               : SQL2014SER12R2
Database             : DBA-Admin
FileName             : DBA-Admin_System
FileGroup            : PRIMARY
PhysicalName         : F:\DBA-Admin_System.MDF
FileType             : ROWS
UsedSpaceMB          : 3
FreeSpaceMB          : 253
FileSizeMB           : 256
PercentUsed          : 1
AutoGrowth           : 0
AutoGrowType         : MB
SpaceUntilMaxSizeMB  : 16777213
AutoGrowthPossibleMB : 0
UnusableSpaceMB      : 16777213
Server               : SQL2014SER12R2
Database             : DBA-Admin
FileName             : DBA-Admin_Log
FileGroup            :
PhysicalName         : G:\DBA-Admin_Log.LDF
FileType             : LOG
UsedSpaceMB          : 32
FreeSpaceMB          : 224
FileSizeMB           : 256
PercentUsed          : 12
AutoGrowth           : 256
AutoGrowType         : MB
SpaceUntilMaxSizeMB  : 2528
AutoGrowthPossibleMB : 2304
UnusableSpaceMB      : 0
Server               : SQL2014SER12R2
Database             : DBA-Admin
FileName             : DBA-Admin_User
FileGroup            : UserFG
PhysicalName         : F:\DBA-Admin_User.NDF
FileType             : ROWS
UsedSpaceMB          : 1
FreeSpaceMB          : 255
FileSizeMB           : 256
PercentUsed          : 0
AutoGrowth           : 256
AutoGrowType         : MB
SpaceUntilMaxSizeMB  : 5119
AutoGrowthPossibleMB : 4864
UnusableSpaceMB      : 0
There is a lot of useful information returned for each file. Its better if you use Out-GridView as then you can order by columns and filter in the top bar.
04 - single server ogv.gif

 

As always, PowerShell uses the permissions of the account running the sessions to connect to the SQL Server unless you provide a separate credential for SQL Authentication. If you need to connect with a different windows account you will need to hold Shift down and right click on the PowerShell icon and click run as a different user.

Lets get the information for a single database. The command has dynamic parameters which populate the database names to save you time and keystrokes

05 dynamic parameters.gif

But you may want to gather information about more than one server. lets take a list of servers and place them into a variable. You can add your servers to this variable in a number of ways, maybe by querying your CMDB or using your registered servers or central management server

and then

 

06 - Many servers ogv.PNG

As you can see, you get a warning quite correctly, that the information for the asynchronous secondary node of the availability group databases is not available and I did not have all of my servers running so there are a couple of could not connect warnings as well. You can still filter very quickly. dbatools is tested from SQL2000 to SQL vNext as you can see below (although I don’t have a SQL2000 instance)

07 - filter ogv

 

Not only on Windows, this command will work against SQL running on Linux as well

08 - linux.PNG

So if we want to know the total size of the files on disk for  the database we need to look at the FileSizeMB property

Of course that’s an easy calculation here

08a - filesize.PNG

but if we have numerous files then it may be tougher. we can use the Measure-Object command to sum the properties. We need to do a bit of preparation here and set a couple of calculated properties to make it more readable

09 - filessize

Maybe we want to look at all of the databases on an instance. Again, we have to do a little more work here

10 - size on disk

If we wanted the databases ordered by the size of their files we could do this

11 - size sorted.PNG

As it is PowerShell we have an object and we can use it any way we like. Maybe we want that information in a text file or a csv or an excel file or in an email, PowerShell can do that

I had a little play with Boe Prox PoshCharts (you have to use the dev branch) to see if I could get some nice charts and unfortunately the bar charts did not come out so well but luckily the donut and pie charts did. (I’m a DBA I love donuts!)

12 - donuts.PNG

So the point is, whatever you or your process requires you can pretty much bet that PowerShell can enable it for you to automate.

You can make use of all of the properties exposed by the command. If you want to only see the files with less than 20% space free

13 - percent used.PNG

you can also use the command to check for file growth settings as well

14 - autogrowth.PNG

Or maybe you want to know the FileSize, Used and Free Space per database

15 totals.PNG

Hopefully that has given you a quick insight into another one of the fabulous dbatools commands. Any questions, comment below or head over to the SQL Server Community Slack via https://sqlps.io/slack

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

and update it using an Administrator PowerShell session with

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

Then you can use

 

Quickly Creating Test Users in SQL Server with PowerShell using the sqlserver module and dbatools

One of the most visited posts on my blog is nearly two and half years old now – Add User to SQL Server Database Role with PowerShell and Quickly Creating Test Users. I thought it was time to update it and use the latest sqlserver module and the dbatools module.

You can get the latest version of the sqlserver module by installing SSMS 2016. The PASS PowerShell Virtual Chapter have created a short link to make this easier for you to remember: https://sqlps.io/dl

Once you have downloaded and installed SSMS you can load the module.

Import-Module sqlserver

There is one situation where you will get an error loading the sqlserver module into PowerShell. If you have the SQLPS module already imported then you will get the following error:

Import-Module : The following error occurred while loading the extended type data file:

sqlserver-module-error

In that case you will need to remove the SQLPS module first.

Remove-Module sqlps
Import-Module sqlserver

The original post dealt with creating a number of test users for a database and assigning them to different roles quickly and easily.

First let’s quickly create a list of Admin users and a list of Service Users and save them in a text file.

$i = 0
while($I -lt 100)
{
"Beard_Service_User$i" | Out-File 'C:\temp\Users.txt' -Append
$i++
}

$i = 0
while($I -lt 10)
{
"Beard_Service_Admin_$i" | Out-File 'C:\temp\Admins.txt' -Append
$i++
}

Now that we have those users in files we can assign them to a variable by using Get-Content

$Admins = Get-Content 'C:\temp\Admins.txt'

Of course we can use any source for our users – a database, an excel file, Active Directory or even just type them in.

We can use the Add-SQLLogin command from the sqlserver module to add our users as SQL Logins, but at present we cannot add them as database users and assign them to a role.

If we want to add a Windows Group or a Windows User to our SQL Server we can do so using:

Add-SqlLogin -ServerInstance $Server -LoginName $User -LoginType WindowsUser -DefaultDatabase tempdb -Enable -GrantConnectSql 

Notice that we need to enable and grant connect SQL to the user.

If we want to add a SQL login the code is pretty much the same but we either have to enter the password in an authentication box or pass in a PSCredential object holding the username and password. Keeping credentials secure in PowerShell scripts is outside the scope of this post and the requirement is for none-live environments so we will pass in the same password for all users as a string to the script. You may want or be required to achieve this in a different fashion.

 $Pass = ConvertTo-SecureString -String $Password -AsPlainText -Force
$Credential = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $User, $Pass
Add-SqlLogin -ServerInstance $Server -LoginName $User -LoginType $LoginType -DefaultDatabase tempdb -Enable -GrantConnectSql -LoginPSCredential $Credential

We can ensure that we are not trying to add logins that already exist using

 if(!($srv.Logins.Contains($User)))
{

The $srv is a SQL Server Management Server Object which you can create using a snippet. I blogged about snippets here and you can find my list of snippets on github here. However, today I am going to use the dbatools module to create a SMO Server Object using the Connect-DbaSqlServer command and assign the server and the database to a variable:

 # Create a SQL Server SMO Object
$srv = Connect-DbaSqlServer -SqlServer $server
$db = $srv.Databases[$Database]

Once we have our Logins we need to create our database users:

 $usr = New-Object ('Microsoft.SqlServer.Management.Smo.User') ($db, $User)
$usr.Login = $User
$usr.Create()

and add them to a database role.

#Add User to the Role
$db.roles[$role].AddMember($User)

I created a little function to call in the script and then simply loop through our users and admins and call the function.

foreach($User in $Users)
{
Add-UserToRole -Password $Password -User $user -Server $server -Role $Userrole  -LoginType SQLLogin
}

foreach($User in $Admins)
{
Add-UserToRole -Password $Password -User $user -Server $server -Role $adminrole  -LoginType SQLLogin
}

To check that they have been added correctly I simply use the Get-DbaRoleMember command from dbatools and output it to Out-GridView using the alias ogv as I am on the command line:

Get-DbaRoleMember -SqlInstance $server |ogv

which looks like this:

 

get-dbarole-memebr

Once we need to clean up the logins and users we can use the Get-SQLLogin and Remove-SQLLogin commands from the sqlserver module to remove the logins and if we do that first we can then use the dbatools command Remove-SQLOrphanuser to remove the orphaned users 🙂 (I thought that was rather cunning!)

(Get-SqlLogin -ServerInstance $server).Where{$_.Name -like '*Beard_Service_*'}|Remove-SqlLogin

Remove-SQLOrphanUser -SqlServer $Server -databases $database

The Remove-SQLLogin will prompt for confirmation and the result of the Remove-SQLOrphanUser looks like this

remove-them-all

When you are looking at doing this type of automation with PowerShell, you should remember always to make use of Get-Command, Get-Help and Get-Member. That will enable you to work out how to do an awful lot. I have a short video on youtube about this:

 

and when you get stuck come and ask in the SQL Server Slack at https://sqlps.io/slack. You will find a powershellhelp channel in there.

Here is the complete code:

#Requires -module sqlserver
#Requires -module dbatools

### Define some variables
$server = ''
$Password = "Password"
$Database = 'TheBeardsDatabase'
$Admins = Get-Content 'C:\temp\Admins.txt'
$Users = Get-Content 'C:\temp\Users.txt'
$LoginType = 'SQLLogin'
$userrole =  'Users'
$adminrole = 'Admin'

# Create a SQL Server SMO Object
$srv = Connect-DbaSqlServer -SqlServer $server
$db = $srv.Databases[$Database]

function Add-UserToRole
{
param
(
[Parameter(Mandatory=$true,
ValueFromPipeline=$true,
ValueFromPipelineByPropertyName=$true,
ValueFromRemainingArguments=$false)]
[ValidateNotNullOrEmpty()]
[string]$Password,
[Parameter(Mandatory=$true,
ValueFromPipeline=$true,
ValueFromPipelineByPropertyName=$true,
ValueFromRemainingArguments=$false)]
[ValidateNotNullOrEmpty()]
[string]$User,
[Parameter(Mandatory=$true,
ValueFromPipeline=$true,
ValueFromPipelineByPropertyName=$true,
ValueFromRemainingArguments=$false)]
[ValidateNotNullOrEmpty()]
[string]$Server,
[Parameter(Mandatory=$true,
ValueFromPipeline=$true,
ValueFromPipelineByPropertyName=$true,
ValueFromRemainingArguments=$false)]
[ValidateNotNullOrEmpty()]
[string]$Role,
[Parameter(Mandatory=$true,
ValueFromPipeline=$true,
ValueFromPipelineByPropertyName=$true,
ValueFromRemainingArguments=$false)]
[ValidateSet("SQLLogin", "WindowsGroup", "WindowsUser")]
[string]$LoginType
)

if(!($srv.Logins.Contains($User)))
{
if($LoginType -eq 'SQLLogin')
{
$Pass = ConvertTo-SecureString -String $Password -AsPlainText -Force
$Credential = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $User, $Pass
Add-SqlLogin -ServerInstance $Server -LoginName $User -LoginType $LoginType -DefaultDatabase tempdb -Enable -GrantConnectSql -LoginPSCredential $Credential
}
elseif($LoginType -eq 'WindowsGroup' -or $LoginType -eq 'WindowsUser')
{
Add-SqlLogin -ServerInstance $Server -LoginName $User -LoginType $LoginType -DefaultDatabase tempdb -Enable -GrantConnectSql
}
}
if (!($db.Users.Contains($User)))
{

# Add user to database

$usr = New-Object ('Microsoft.SqlServer.Management.Smo.User') ($db, $User)
$usr.Login = $User
$usr.Create()

}
#Add User to the Role
$db.roles[$role].AddMember($User)
}

foreach($User in $Users)
{
Add-UserToRole -Password $Password -User $user -Server $server -Role $Userrole -LoginType SQLLogin
}

foreach($User in $Admins)
{
Add-UserToRole -Password $Password -User $user -Server $server -Role $adminrole -LoginType SQLLogin
}

Get-DbaRoleMember -SqlInstance $server |ogv

Happy Automating!

 

 

 

Enabling Cortana for dbareports PowerBi

Last week at the Birmingham user group I gave a presentation about PowerShell and SQL Server

saved-image-from-tweetium-8

It was a very packed session as I crammed in the new sqlserver module, dbatools and dbareports 🙂 On reflection I think this is a bit too much for a one hour session but at the end of the session I demo’d live Cortana using the dbareports dataset and returning a Cortana PowerBi page.

As always it took a couple of goes to get it right but when it goes correctly it is fantastic. I call it a salary increasing opportunity! Someone afterwards asked me how it was done so I thought that was worth a blog post

There is a video below but the steps are quite straightforward.

Add Cortana Specific Pages

Whilst you can just enable Cortana to access your dataset, as shown later in this post, which enables Cortana to search available datasets and return an appropriate visualisation it is better to provide specific pages for Cortana to use and display. You can do this in PowerBi Desktop

Start by adding a new page in your report by clicking on the plus button

 

add page.PNG

and then change the size of the report page by clicking on the paintbrush icon in the visualisation page.

page-size

This creates a page that is optimised for Cortana to display and also will be the first place that Cortana will look to answer the question

Power BI first looks for answers in Answer Pages and then searches your datasets and reports for other answers and displays them in the form of visualizations. The highest-scoring results display first as best matches, followed by links to other possible answers and applications. Best matches come from Power BI Answer Pages or Power BI reports.

Rename the page so that it contains the words or phrase you expect to be in the question such as “Servers By Version” You will help Cortana and PowerBi to get your results better if you use some of the column names in your dataset

Then it is just another report page and you can add visualisations just like any other page

cortana page.PNG

Make Cortana work for you and your users

If your users are likely to use a number of different words in their questions you can assist Cortana to find the right answer by adding alternate names. So maybe if your page is sales by store you might add shop, building, results, amount, orders. This is also useful when Cortana doesn’t understand the correct words as you will notice in the screenshot below I have added “service” for “servers” and “buy” for “by” to help get the right answer. You can add these alternate words by clicking the paintbrush under visualisations and then Page Information

cortana-additional

Publish your PBIX file to PowerBi.com

To publish your PowerBi report to PowerBi.com either via the Publish button in PowerBi desktop

publish

or by using the PowerBiPS module

Install-Module -Name PowerBIPS
#Grab the token, will require a sign in
$authToken = Get-PBIAuthToken Verbose
Import-PBIFile authToken $authToken filePath “Path to PBIX file” verbose

Enable Cortana

In your browser log into https://powerbi.com and then click on the cog and then settings

powerbicom.PNG

then click on Datasets

settings

Then choose the dataset – in this case dbareports SQL Information sample and click the tick box to Allow Cortana to access the this dataset and then click apply

dataset settings.PNG

Use Cortana against your PowerBi data

You can type into the Cortana search box and it will offer the opportunity for you to choose your PowerBi data

cortana-search

but it is so much better when you let it find the answer 🙂

cortana-search-1

and if you want to go to the PowerBi report there is a handy link at the bottom of the Cortana page

cortana-search-2

I absolutely love this, I was so pleased when I got it to work and the response when I show people is always one of wonder for both techies and none-techies alike

The conditions for Cortana to work

You will need to have added your work or school Microsoft ID to the computer or phone that you want to use Cortana on and that account must be able to access the dataset either because it is the dataset owner or because a dashboard using that dataset has been shared with that account.

From this page on PowerBi.com

When a new dataset or custom Cortana Answer Page is added to Power BI and enabled for Cortana it can take up to 30 minutes for results to begin appearing in Cortana. Logging in and out of Windows 10, or otherwise restarting the Cortana process in Windows 10, will allow new content to appear immediately.

It’s not perfect!

When you start using Cortana to query your data you will find that at times it is very frustrating. My wife was in fits of giggles listening to me trying to record the video below as Cortana refused to understand that I was saying “servers” and repeatedly searched Bing for “service” Whilst you can negate the effect by using the alternate names for the Q and A settings it is still a bit hit and miss at times.

It is amazing

There is something about giving people the ability to just talk to their device in a meeting and for example with dbareports ask

Which clients are in Bolton

or

When was the last backup for client The Eagles

and get the information they require and a link to the report in PowerBi.com. I am certain that the suits will be absolutely delighted at being able to show off in that way which is why I call it a salary increasing opportunity 🙂

We would love YOU to come and join us at the SQL Community Collaborative

Help us make dbatools, dbareports and Invoke-SQLCmd2 even better. You can join in by forking the repos in GitHub and writing your code and then performing a PR but we would much rather that you came and discussed new requests in our Trello boards, raised issues in GitHub and generally discussed the modules in the SQL Server Community Slack #dbatools #dbareports. We are also looking for assistance with our wiki pages, Pester tests and appveyor integration for our builds and any comments people want to make

SQL Server Collaborative GitHub Organisation holding the modules. Go here to raise issues, fork the repositories or download the code

dbatools Trello for discussion about new cmdlets

SQL Server Community Slack where you can find #dbatools and #dbareports as well as over 1100 people discussing all aspects of the Data Platform, events, jobs, presenting

COME AND JOIN US

 

The SQL Server Community Collaborative GitHub Organisation is born

My wonderful friend Chrissy LeMaire and I are the creators of two GitHub repositories for SQL Server and PowerShell called dbatools and dbareports

If you are working with SQL Server I highly recommend that you take a look at the vast number of commands available to you at dbatools which will help you complete tasks within SQL Server especially for Instance migrations and also a growing number of best practice implementations

Both of these modules are not just the work of one person any more. We have over 20 people who have collaborated on the modules THANK YOU ALL and more that have provided guidance and comments via the Slack Channels in the SQL Server Community Slack https://sqlps.io/slack and via the Trello boards https://dbatools.io/trello and https://dbareports/trello

At SQL Saturday Cambridge this weekend I was proud to join Chrissy in her presentation as we talked about both modules. Heres a fabulous picture of us with Buck Woody

 

wp_20160910_10_14_58_pro

 

We had discussed previously that it didn’t feel quite right that these community tools were under our own personal accounts and it also caused some administration issues with allowing access. So with that in mind after a naming discussion in the slack channel we created an organisation to hold them both

 SQL Server Community Collaborative

is born at https://github.com/sqlcollaborative

Nothing much changes except the name. we have even found that all the old links work and GitHub desktop updated. We will continue to make great commands with all of our fantastic collaborators. Discussions will happen in Slack and organisation in Trello and we will continue to grow and learn and teach and share and create together.

We would love you to come and join us