Press "Enter" to skip to content

Category: T-SQL Tuesday

The Market For IoT Analytics

Published 2017-10-11 by Kevin Feasel

Kennie Nybo Pontoppidan tells a story for T-SQL Tuesday:

At the time in Rehfeld R&D, we experimented with making Effektor a metadata repository for a Hadoop data warehouse, where instead of generating tables and ETL processes in the different data warehouse layers, the synchronization engine in the product would generate the Hive objects on top of Hadoop tables. We never made more than an overall spec and a prototype, but the experiment gave us some insight into the technologies around Hadoop.

Around that time, Phillips released the Hue lightbulbs, and our COO bought us two packs to play with. The idea was to create a physical BI dashboard, where lightbulbs would display KPIs, and change color according to its value and the KPI threshold. I still think that was a brilliant idea, and I would love to see more use of consumer electronics in enterprise BI.

His basic maturity model makes sense; as much as I really want to disagree with the maturity model, I can’t.  Good read.

Comments closed

Testing Backups With dbatools

Published 2017-09-15 by Kevin Feasel

Constantine Kokkinos shows off a dbatools cmdlet to test the last full backup:

This:

  • Defines a list of two servers (PowerShell lists are as easy as “”,””)

  • Pipes them to the Test-DbaLastBackup command.

  • Which then:

    • Gathers information about the last full backups for all of your databases on that instance.

    • Restores the backups to the Destination with a new name. If no Destination is specified, the originating server will be used.

    • The database is restored as “dbatools-testrestore-$databaseName” by default, which you can change with the -Prefix parameter.

    • The internal file names are also renamed to prevent conflicts with original database.

    • A DBCC CHECKTABLE is then performed.

    • And the test database is finally dropped.

Pretty snazzy.

Comments closed

Log Shipping With dbatools

Published 2017-09-14 by Kevin Feasel

Sander Stad shows off a few log shipping functions he created for dbatools:

The entire log shipping functionality is now separated between 5 functions. Four of them are used internally and are not visible as a public function because you can easily break stuff if it’s not being used correctly.

The main function, Invoke-DbaLogShipping, is available in the dbatools module for anyone to use.

If you open the GUI for the log shipping you have lots of choices but most of them are already supplied in the GUI itself and you can decide whether to use them or not.
The whole idea behind the functionality was that it would allow you to quickly setup log shipping using a lot of defaults like you can in the GUI, but if you’re more experienced you can change any setting to your preferences.

Read on for an example of how to use this.  It looks pretty snazzy.

Comments closed

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

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