Press "Enter" to skip to content

Category: Powershell

Getting The SqlServer Powershell Module

Published 2017-04-27 by Kevin Feasel

Drew Furgiuele shows how to install the SqlServer Powershell module from the Powershell Gallery:

That’s because, out of the box, Server 2012 R2  is running PowerShell 4.0. These Gallery cmdlets require PowerShell 5. To upgrade, you either need to upgrade to PowerShell 5.0 and that means installing Windows Management Framework 5.0. This is compatible with versions of Windows as far back as Windows 7, and Windows Server as far back as 2008 R2. Anything earlier, and you’re out of luck. This also requires the .NET framework 4.5 (or above). That means system updates, which could (potentially) lead to system reboots. Plan (and for the love God test) accordingly!

There’s a couple other hitches as well. One, and this sort of goes without saying, you need internet access for this to work. If your machines are behind any kind of filtering or firewall restrictions that prevent them from talking out to the internet, you’ll need to either open them up or use the Save-Module  feature to download the bits and install them yourself. Secondly, you need Administrator access for this to work. And three, if you do install them manually, you might have different versions installed for different users (or service accounts).

They’ve made it nice and easy, so read Drew’s post and give it a try.

Comments closed

CI With SQL Server And Jenkins

Published 2017-04-26 by Kevin Feasel

Chris Adkin shows how to auto-deploy SQL Server Data Tools projects to a SQL Server instance using Jenkins:

The aim of this blog post is twofold, it is to explain how:

  • A “Self building pipeline” for the deployment of a SQL Server Data Tools project can be implemented using open source tools
  • A build pipeline can be augmented using PowerShell

What You Will Need

  • Jenkins automation server

  • cURL

  • SQL Server 2016 (any edition will suffice)

  • Visual Studio 2015 community edition

  • A windows server, physical or virtual to install all of the above on, I will be using Windows Server 2012 R2 as the operating system

Automated integration via CI is extremely helpful, and Chris makes it look easy in this post.

Comments closed

Comparing Tables With Powershell

Published 2017-04-21 by Kevin Feasel

Shane O’Neill compares table columns with T-SQL and Powershell:

That is not really going to work out for us…
So I’m not liking the look of this, and going through the results, it seems to me that these results are just not useful. This isn’t the computers fault – it’s done exactly what I’ve told it to do – but a more useful result would be a list of columns and then either a simple ‘Yes’, or a ‘No’.

There’s syntax for this…PIVOT…

This is helpful for normalizing a bunch of wide, related tables into a subclass/superclass pattern.

Comments closed

Resetting SQL Administrators

Published 2017-04-19 by Kevin Feasel

Chris Lumnah shows how to use dbatools to reset a SQL authenticated administrative account:

As I was going through my environment, I realized I created a new domain controller for my tests. This DC has a new name and domain name which is different from my other VMs. I quickly realized that this will cause me issues later with authentication. No worries. I will just boot up the VMs and then and join them to the new domain. Easy-peasy. Now let met go test out my SQL Servers.

DOH!!

I received a login failure with access is denied. Using Windows Authentication with my new domain and recently joined server is not working. Why?…..Oh right, my new user id does not have access to SQL Server itself. As I sit there smacking myself in the head, I am also thinking about the amount of time it will take me to rebuild those VMs. Then it hit me!!!

Read on to see the solution, including a Powershell one-liner showing how it’s done.

Comments closed

Powershell Difficulties

Published 2017-04-17 by Kevin Feasel

Dave Mason shares some difficulties he has had grokking Powershell:

The developer in me thinks this is nuts. Run the same few lines of code twice, with no changes in between, and get different outputs? Madness!

Here’s another example. Nothing too complex here: I connect to an instance of SQL, SELECT CURRENT_TIMESTAMP, and show the returned value in the output window. (There’s a fixable issue here that I would go on to discover later. But hold that thought for now.)

Even when you’re conceptually familiar with a language, getting into the particular foibles of that language can expose all sorts of behavior which is strange to newcomers.

Comments closed

Linear Regression In SQL

Published 2017-04-17 by Kevin Feasel

Phil Factor shows how to generate a quick linear regression using SQL, Powershell, and Gnuplot:

It looks a bit like someone has fired a shotgun at a wall but is there a relationship between the two variables? If so, what is it? There seems to be a weak positive linear relationship between the two variables here so we can be fairly confident of plotting a trendline.

Here is the data, and we will proceed to calculate the slope and intercept. We will also calculate the correlation.

It’s good to know that this is possible, but I’d switch to R or Python long before.

Comments closed

SQL Server Migration With dbatools

Published 2017-04-14 by Kevin Feasel

Garry Bargsley gives an example of how quickly you can migrate a SQL Server instance:

So in just 1 minute and 34 seconds you have migrated all of the following from one server to another.

  • All SP_Configure settings

  • Any Custom Error Messages

  • Any SQL Credentials

  • All Linked Servers

  • Database Mail along with Configuration and Profiles and Accounts

  • All user objects in System Databases

  • All Backup Devices

  • Any System Triggers

  • All User Databases

  • All Logins

  • Any Data collectors

  • Any Security Audits

  • All Endpoints, Policy Management, Resource Governor, Extended Events

  • And Finally All SQL Server Agent Jobs, Schedules, Operators, Alerts

These are probably very small databases (as it was a test instance), but dbatools is quite impressive.

Comments closed

Query All Servers In A CMS Folder

Published 2017-04-11 by Kevin Feasel

Tracy Boggiano has a Powershell script for querying each SQL Server instance in a Central Management Server folder:

In this post I’m going to share a function (actually two) I use run scripts against multiple instances of SQL servers and run the data into a data table. I use this mainly for a replacement of the CMS feature of running against a folder and to put the data into a DataTable object which I output to a GridView that I can sort and filter any way I want which you can’t do in CMS.

Click through for the script.

Comments closed

Exporting SQL Server User Permissions

Published 2017-04-10 by Kevin Feasel

Rob Sewell shows how to use the Export-SqlUser cmdlet in dbatools:

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

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

Cláudio wrote Export-SqlUser to solve a problem.

Read on to see what this does and how to use it.

Comments closed

Improving Read-HostSpecial

Published 2017-04-07 by Kevin Feasel

Jana Sattainathan makes Jeff Hicks’s Read-HostSpecial better:

Some shortcomings of Read-HostSpecial

While it does a lot already, I did find some very minor shortcomings that I wanted to address

  1. Bad inputs killed the program – If you are prompting for a series of inputs (like a wizard) and the user mis-keyed one input by mistake, the error recovery is very hard and the user has to start-over from the beginning. This called for a RepromptOnError switch which issues a gentle warning and then allows the user to input a valid value upon encountering validation errors.

  2. I needed a couple of more canned validations like ValidateFolder and ValidateFile.

  3. Too bad, there is no Write-HostSpecial – I wanted Read-HostSpecial to display some pretty text and not wait for input (like Write-Host) using the same nomenclature for fonts/look/feel/usability as Read-HostSpecial. So, I needed a NoWait switch.

Read on for Jana’s script and usage examples.

Comments closed