Press "Enter" to skip to content

Category: Powershell

Using Desired State Configuration To Install SQL Server

Published 2017-03-08 by Kevin Feasel

Chris Lumnah shows how to use Powershell Desired State Configuration to automate installation and configuration of a new SQL Server instance:

So the installation of SQL Server is now fairly straightforward. The wizard does a nice job of guiding you along the way. 2016 even includes best practice suggestions for tempdb and instance file initialization. Along the way, Microsoft as given us ways to automate the installation of SQL Server. You can sysprep an instance, but this does not really automate the installation. It just helps create a template of an instance. At the end of the day, you still need to do things manually. You can also use a configuration file to assist here. This is a great step forward, but it does not allow for all of the things you need to do to configure a SQL server.

Powershell does. Desired State Configuration (DSC) is functionality built into Powershell that allows for the installation and configuration of a SQL Server.

Chris includes his script as well as a link for more information on DSC in case you aren’t familiar with the concept.

Comments closed

Test User Generation With Powershell

Published 2017-03-06 by Kevin Feasel

Rob Sewell shows how to use dbatools to create test user accounts quickly:

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.

Rob includes a demo script as well, thereby making it even easier.

Comments closed

Automated Database Restoration And CHECKDB

Published 2017-03-06 by Kevin Feasel

Anthony Nocentino shows how to use dbatools to automate testing restoration of database backups and running DBCC CHECKDB against these restored backups:

Requirements

  1. Automation – Complete autopilot, no human interaction.
  2. Report job status – Accurate reporting in the event the job failed, the CHECKDB failed or the restore failed.

Solution

  1. Use dbaltools cmdlets for restore and CHECKDB operations
  2. Use SQL Agent Job automation, logging and alerting

So let’s walk through this implementation together.

You won’t get a turnkey solution from this blog post, but you will get a good process to follow.

Comments closed

Powershell Errors

Published 2017-03-03 by Kevin Feasel

Shane O’Neill explains what the $error variable does:

Now ignoring the fact that you already know what is wrong, this tells me that there is either something wrong with the $Database variable, the $sql variable or the syntax statement. Maybe even something else though!
This is not helpful and I’m going to have a bad time.

I encountered this lately and thanks to Chrissy LeMaire ( b | t ), I was introduced to the $error variable.

Shane also has an interesting side note around error colors.

Comments closed

Changing Powershell Error Colors

Published 2017-03-02 by Kevin Feasel

Jana Sattainathan shows how easy it is to change the colors for Powershell warnings and errors:

How could such a nice tool have such a terrible choice of colors for errors and warnings? I am talking about the ISE and the unreadable contrast of colors.

You can fix it. Open and update your profile by running this from your ISE (which opens your profile in NotePad)

It’s Powershell, so you can do it in a couple lines.

Comments closed

Instance Configuration With dbatools

Published 2017-03-01 by Kevin Feasel

Rob Sewell has an interesting post on cross-platform configuration using dbatools in Powershell:

This weekend I set up some SQL vNext virtual machines, two on Windows and one on Linux so that I could test some scenarios and build an availability group.

IMPORTANT NOTE :- The names of dbatools commands with a Sql prefix WILL CHANGE in a later release of dbatools. dbatools will use Dba throughout in the future as the sqlserver PowerShell module uses the Sql prefix

I used PowerShell version 5.1.14393.693 and SQL Server vNext CTP 1.3 running on Windows Server 2016 and Ubuntu 16.04 in this blog post

There’s some fancy footwork in this post; if you’re looking for ways to compare instance configurations (specifically, sp_configure settings), check it out.

Comments closed

Using popt To Switch Tabs In Powershell ISE

Published 2017-02-23 by Kevin Feasel

Jana Sattainathan shows how to open a specific Powershell ISE tab without moving your hands off the keyboard:

This is a function that activates a tab based on a partial search string that you type in the CLI portion of the ISE. Among the numerous tabs you have open in the ISE, to activate a tab that has the word “register”, you would just type in

1
popt register

That’s it. The tab that has the word “register” will become active.

Why “popt” instead of a standard name?

“popt” is the alias you can create for the real “search and activate function” Pop-FileInISETab. It stands for “poptab”. You don’t want to trade one problem with another by having to type that long name, so, there is functionality to optionally create an alias named “popt” for the function.

At first I said to myself, “Why would anybody have this many tabs open?”  Then I looked at my instance of SQL Server Management Studio.  Then I decided I wanted this for SSMS.

Comments closed

Export-DMVInformation Updates

Published 2017-02-20 by Kevin Feasel

Sander Stad has made changes to his Export-DMVInformation Powershell module:

Last Friday I had the chance to show the Export-DMVInformation module to the Dutch Powershell user group. After the presentation I got a couple of suggestions and wanted to put them in place them into the module.

Changes:

  1. Possibility to execute the module using the pipeline

  2. Get all the databases in one statement by assigning the value “ALL” to the database parameter.

  3. Replaced messages with verbose

Read on for more information, including where you can get the module and its Export-DMVInformation cmdlet.

Comments closed

Using Powershell To Add SQL Agent Job Steps

Published 2017-02-20 by Kevin Feasel

Rob Sewell gives a detailed walkthrough of a small Powershell script which adds a job step to every SQL Server Agent job:

This code was run on PowerShell version 5 and will not run on PowerShell version 3 or earlier as it uses the where method
I put all of our jobs that I required on the estate into a variable called $Jobs. (You will need to fill the $Servers variable with the names of your instances, maybe from a database or CMS or a text file

Click through for the script and line-by-line explanation.

Comments closed

Avoiding Hard-Coded Paths In Powershell

Published 2017-02-17 by Kevin Feasel

Jana Sattainathan gives a few options for getting around hard-coded paths in a Powershell script:

What if the KillerApp’s home folder suddenly moves?

Now, how do you make your app work with all its scripts without having to change code if you move it to a different folder?

You could now change the initial script that that dot-sources all the functions to alter the path and you are all set. This is still not ideal because you have to make a change when the location changed.

Click through for several options, including PSDrives and even automatically dot-sourcing Powershell scripts in the current and all child directories.

Comments closed