Press "Enter" to skip to content

Category: Powershell

dbachecks Improvements

Published 2018-05-22 by Kevin Feasel

Rob Sewell shows off some improvements in the dbachecks library:

With the latest release of dbachecks we have added a new check for testing that foreign keys and constraints are trusted thanks to Cláudio Silva b | t

To get the latest release you will need to run

You should do this regularly as we release new improvements frequently.

We have also added better descriptions for the checks which was suggested by the same person who inspired the previous improvement I blogged about here

Click through for more details.

Comments closed

Exporting Table Details With Powershell

Published 2018-05-21 by Kevin Feasel

Andy Levy helps an end user understand a database schema:

This week I had a user come to me asking about how fields were defined on a few tables he was using in writing some reports. Long story short, he’s been tasked with writing some new reports and updating existing ones, but he doesn’t have visibility to the database itself so he’s left with the “ok, let’s try this” approach and then reading error messages to debug when things go sideways. Very tedious.

I asked him for a list of the tables he was most interested in, and while he worked on that I set to work on (of course) a quick & dirty PowerShell script to collect the data he needed – field names, types, and whether they’re nullable.

Ideally these analysts would have data model documentation, but it’s not an ideal world.

Comments closed

Creating Azure SQL Database Managed Instances Via ARM Templates

Published 2018-05-16 by Kevin Feasel

Jovan Popovic shows how us how to build a Managed Instance of Azure SQL Database using Powershell and an ARM template:

Values that you need to change in this request are:

  • name – name of your Azure SQL Managed Instance (don’t include domain).

  • properties/administratorLogin – SQL login that will be used to connect to the instance.

  • properties/subnetId – Azure identifier of the subnet where Azure SQL Managed Instance should be placed. Make sure that you properlyconfigure network for Azure SQL Managed Instance

  • location – one of the valid location for Azure data centers, for example: “westcentralus”

  • sku/name: GP_Gen4 or GP_Gen5

  • properties/vCores: Number of cores that should be assigned to your instance. Values can be 8, 16, or 24 if you select GP_Gen4 sku name, or 8, 16, 24, 32, or 40 if you select GP_Gen5.

  • properties/storageSizeInGB: Maximum storage space for your instance. It should be multiple of 32GB.

  • properties/licenceType: Choose BasePrice if you don’t have SQL Server on-premises licence that you want to use, or LicenceIncluded if you can have discount for your on-premises licence.

  • tags(optional) – optionally put some key:value pairs that you would use to categorize instance.

Click through for the template and a quick Powershell script which shows how to use the template.

Comments closed

Docker Containers For Multiple Versions Of SQL Server

Published 2018-05-11 by Kevin Feasel

Rob Sewell shows us how to create Docker containers for multiple versions of SQL Server running on the same machine:

I want to show the two modules running against a number of SQL Versions so I have installed

  • 2 Domain Controllers
  • 2 SQL 2017 instances on Windows 2016 with an Availability Group and WideWorldImporters database
  • 1 Windows 2016 jump box with all the programmes I need
  • 1 Windows 2016 with containers

using a VSTS build and this set of ARM templates and scripts

I wanted to create containers running SQL2017, SQL2016, SQL2014 and SQL2012 and restore versions of the AdventureWorks database onto each one.

Rob shows how to do this all via Powershell so you can automate the process.

Comments closed

Getting Per-Table Space Utilization With Powershell

Published 2018-05-10 by Kevin Feasel

Drew Furgiuele provides us a script and a homework assignment:

Of course, PowerShell excels at this. By using the SQL Server module, it’s really easy to:

  • Connect to an instance and collect every user database, and
  • From each database, collect every table, and
  • For each table, collect row counts and space used, and
  • If there are any indexes, group them, and sum their usage and report that as well

Here’s the script. Note that I have the server name hard-coded in there as localhost (more on that in a coming paragraph). Go ahead and take a look before we break it down.

Click through for the script, and homework is due next Tuesday on his desk.

Comments closed

Reading AWS Aurora Error Logs With Powershell

Published 2018-05-08 by Kevin Feasel

Michael Bourgon has a Powershell script which reads error logs from AWS Aurora:

Been working on monitoring.  For some reason, when you tell Aurora to send errorlogs to Cloudwatch, all it sends are the Audit Logs, which will tell you that code had changed, etc, but doesn’t (!?!?!??!!!) actually put your logs in Cloudwatch.  I don’t understand it, so I built this process to look through logs and return the data.  The next step would be to format it and either upload to Cloudwatch manually, or log it, or send email.

Click through for the script.

Comments closed

Default Displayed Properties In Powershell

Published 2018-05-07 by Kevin Feasel

Claudio Silva explains the default displayed properties in Powershell and how you can find non-default properties:

First, let me say that this person knows that Select-Object can be used to select the properties we want, so he tried to guess the property name using a trial/error approach.

The person tried:

Get-Service WinRM | Select-Object Startup, Status, Name, DisplayName

and also:

Get-Service WinRM | Select-Object StartupType, Status, Name, DisplayName

But all of them were just empty.

There is a better way.

Comments closed

Switching Between Windows And Linux Containers

Published 2018-05-07 by Kevin Feasel

Chris Taylor demonstrates a couple ways of switching from Linux to Windows containers in Docker for Windows:

If you are using Docker for Windows and want to switch between Linux or Windows containers you can do this by right clicking the Docker “Whale” in the systray and selecting “Switch to Windows containers”:

….but no one likes clicking around do they!

There is an alternative way to do this which I use in my docker session demo’s which makes things so much easier and the switch is a lot quicker!

Click through for the Powershell call, which has the added benefit of being scriptable.

Comments closed

SQL Server Disk Space Analysis

Published 2018-05-01 by Kevin Feasel

Jana Sattainathan has a solution using Powershell, Power BI, and T-SQL to track disk usage across a series of SQL Server instances:

This is just great on its own as I get information shown below. It is basically data like this at the Database/FileGroup/File levels

  • Database/Filegroup/File name

  • Size

  • Free size

  • Max size

  • Free of Max size

  • Size as a percent at the instance level

  • Free size as a percent at the instance level

  • Max size as a percent at the instance level

  • Free of Max size as a percent at the instance level

Read on to see how Jana makes use of this data, as well as where you can get the code.

Comments closed