Press "Enter" to skip to content

Category: Powershell

Loading From Excel Into SQL Server With Powershell

Published 2017-09-14 by Kevin Feasel

Shane O’Neill tackles one of the all-time important questions, how to get data from Excel into something else:

Now let’s say that we are working in an environment that does not allow us to change the server configurations, meaning that OPENROWSET() is closed for us.

Being completely honest, my spreadsheet is only 8 rows so I would just manually insert the data but what happens if it’s a few thousand rows big? Not a viable option.

So with most of my T-SQL ideas have been exhausted, let’s look at PowerShell!

The other all-time important question, of course, is how to get data from something else into Excel.

Comments closed

Automatically Restoring Databases To Another Server

Published 2017-09-14 by Kevin Feasel

Bjorn Peters needs to migrate a few databases over to a test server nightly:

My first thoughts about that were creating a SQL Server Agent Job with following steps:

  1. check the availability of Shared-Destination-Folder
  2. delete/clear Destination-Folder-Content
  3. Shrink all Transaction-Logfiles
  4. Backup all Databases from given list
  5. Restore each Backup-File from folder
  6. Check all orphaned user
  7. delete/clear Destination-Folder-Content

A year or two ago, I had built this with a lot of normal T-SQL-Agent-Jobs, now I’m doing this with dbatools which make it very easy and fast (and in one step)

It’s only a few lines of Powershell code, which you can see upon clicking through.

Comments closed

Comparing Server Configurations With Powershell

Published 2017-09-14 by Kevin Feasel

Andy Levy shows how to use a dbatools cmdlet, Get-DbaSpConfigure:

I started with Get-DbaSpConfigure to retrieve the settings available from sp_configureas these were the most important to my comparison. I ran this against production as well as each of my test instances and saved the results of each to a variable. Because accessing my production instance requires either jumping through hoops or using SQL Authentication, I passed -SqlCredential (get-credential -Message "Prod" -UserName MySQLLogin) so I’d be prompted for that password instead of using Windows Authentication.

It’s good to have an automated process in place to script comparisons, either against a known good build or across servers which ought to be the same.  Things that ought to be the same often aren’t.

Comments closed

dbatools Now With 287 Commands

Published 2017-09-14 by Kevin Feasel

Chrissy LeMaire reports that dbatools has 287 cmdlets:

Really, I said all that so that you’d hopefully excuse the recent lack of communication 😉 Not only did I have the big ol’ audit, but we are also preparing for two dbatools pre-cons. One with Klaas Vandenberghe and Rob Sewell in Ghent, Belgium at dataminds and then the one in Seattle at PASS Summit.

This means that communication will slow a bit until December, but I wanted to give you an update. First, we’re now at a whopping 287 magical commands! We’ve got three kind folks working on the webpages and once they are all ready, I’ll do a new release. Until then, here’s over 80 newish commands that you can explore using Get-Help -Detailed.

dbatools has been a tremendous community success.

Comments closed

Synchronizing Availability Group Objects

Published 2017-09-13 by Kevin Feasel

Derik Hammer has a process to maintain logins, backup devices, linked servers, SQL Agent details, and more between Availability Group nodes:

A notable limitation of this process is that it does not update existing objects. Jobs which already exist but were updated, will not be altered. I chose to omit that functionality because it presents merge complications and problems. For example, the cleanest way to handle the process would be to drop and create the object each time the synchronization runs. If that happened, however, there would be gaps when logins didn’t exist and applications would fail to connect, SQL Agent jobs would lose history, and/or the processing of a job would fail because it was dropped part way through executing.

With that limitation aside, this is a very interesting process and I recommend giving it a careful read.  Derik also includes the Powershell script at the end.

Comments closed

Getting Fancier With VM Creation

Published 2017-09-13 by Kevin Feasel

Raul Gonzalez shows how to spin up a Hyper-V VM using Powershell:

There are a lots of command to create or manipulate VM’s and I’m still only scratching the surface, but although I’m not a PS person, I have to admit that every time I want to do something, I find relatively easy to find a powershell command or a script for it, so I like it.

For instance, creating new virtual machines it’s a simple as one command

New-VM

And that’s only the beginning, we can add the different virtual hardware like in the UI, Drives, Network Adapters and so on. And then configure memory, CPU and NUMA, etc…

This is the script which I’m more or less running to create my VM’s, this in particular will be a Hyper-V Host itself, so there are a couple of interesting settings I’ll tell you about later.

Click through for Raul’s script.

Comments closed

Checking Availability Group Status With Powershell

Published 2017-09-13 by Kevin Feasel

Tracy Boggiano shows off a script which checks Availability Group status of selected servers:

My favorite thing to automate using PowerShell is checking on the status of things on multiple servers.  For example, after patching your environment running a quick query to make sure the version number is the same.  In this example, we will use a cmdlet my coworker wrote in combination in my cmdlet to check the health of all the Availability Groups across our landscape or you could use it just check one.  After all I do consider myself to be an HA/DR nut.

I’ve blogged about my coworker’s Get-CmsHost cmdlet before but now he has and shared it on github so you can read more about here.

In my cmdlet I use the same code that used in the SSMS AG dashboard to check the status of my Availability Groups.

Tracy includes her cmdlet as well as several example calls.

Comments closed

Powershell And CMS

Published 2017-09-13 by Kevin Feasel

Mark Wilkinson loves Powershell and he loves Central Management Servers and he loves combining the two:

Get-CmsHosts is a function I wrote as part of a custom PowerShell module we maintain internally at my employer. It is simple to use, but is the base of most automation projects I work on.

Simple Example

PS> Get-CmsHosts -SqlInstance 'srv-' -CmsInstance srv-mycms-01

This example will connect to srv-mycms-01 and return a distinct list of instance host names registered with that CMS server that start with the string srv-. This output can then be piped to other commands:

Read on for more examples and details, and then grab the script at the end of Mark’s post.

Comments closed

Disabling Named Pipes Using Powershell

Published 2017-09-13 by Kevin Feasel

Brian Carrig shows how to disable the Named Pipes protocol using Powershell:

Windows and POSIX systems both support something referred to as “named pipes”, although they are different concepts. For the purposes of this post I am referring only to the Windows version. By default on most editions of SQL Server (every edition except Express Edition), there are three supported and enabled protocols for SQL server to listen on – Shared Memory, TCP/IP and Named Pipes. The inclusion of named pipes has always confused me somewhat. In theory, named pipes allow communication between applications without the overhead of going through the network layer. This advantage disappears when you want to communicate over the network using named pipes. In all modern versions of SQL Server, named pipes does not support Kerberos, so for most shops you likely will not be using or should not be using named pipes to communicate with SQL Server.

Security best practices dictate that if you are not using a particular protocol, you should disable it. There is an option to disable this is in the GUI in Configuration Manager but since this T-SQL Tuesday blog post is about using Powershell it does not make sense to cover it here. Nor is it particular easy to use the GUI to make a configuration change across hundreds of SQL instances. Unfortunately, I have not found a good way to make this change that does not involve using WMI, if anybody is aware of a better method, I welcome your feedback.

Read the whole thing.  You should have Named Pipes enabled if you’re running a NetBIOS network.  But because it’s not 1997 anymore, you probably shouldn’t be running a NetBIOS network.

Comments closed

Spinning Up Hyper-V VMs With Powershell

Published 2017-09-13 by Kevin Feasel

Andrew Pruski shows us how to enable Hyper-V and create a Windows VM using Powershell:

I’m constantly spinning up VMs and then blowing them away. Ok, using the Hyper-V GUI isn’t too bad but when I’m creating multiple machines it can be a bit of a pain.

So here’s the details on the script I’ve written, hopefully it could be of some use to you too.

Click through for the script; it’s ultimately just a few lines of code.

Comments closed