Press "Enter" to skip to content

Category: Powershell

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

Finding Identity Columns Close To Overflow

Published 2017-04-07 by Kevin Feasel

Rob Sewell demos another dbatools feature:

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

That’s quite useful for taking a quick look at identity columns across a database or instance.

Comments closed

DataRows Everywhere

Published 2017-04-07 by Kevin Feasel

Shane O’Neill shows how to deal with .NET DataRows in Powershell:

Now I don’t know about you but when I query stuff in a SQL database, it’s to do something to/with the results. They could be a list of servers that I monitor, they could be a list of databases that I want to check the recovery model of, or it could be a list of tables that I want to see how much space they are using. The main point is that I want to do something with the results.

But for this simple case, I just want to list out the customer name from this table. Simple? Yes, but this is just a test case to prove a point.

So let’s PowerShell this!

The moral of the story is to Get-Member early and Get-Member often.

Comments closed

Connecting Powershell To Docker Containers

Published 2017-04-06 by Kevin Feasel

Andrew Pruski shows how to open a Powershell session on a container:

One of the questions that I was asked at SQL Saturday Iceland was “how can I view the filesystem within a container?”.

This is a great question as one of the things that people find off-putting about containers is their opaqueness. It’s not obvious where everything lives within the container or how we can view the files within it.

Thankfully there’s a simple docker command that allows us to open a powershell session within a container, that command is docker exec.

For Linux-based containers, /bin/bash (or your favorite shell, if it’s installed) serves as its analog.

Comments closed