Press "Enter" to skip to content

Category: Administration

Recreating Dropped Azure SQL Managed Instance DBs

Published 2019-01-22 by Kevin Feasel

Jovan Popovic has a script to re-create an Azure SQL Managed Instance database which you might accidentally have dropped:

Azure SQL Database – Managed Instance is fully-managed PaaS service that provides advanced disaster-recovery capabilities. Even if you accidentally drop the database or someone drops your database as part of security attack, Managed Instance will enable you to easily recover the dropped database.
Azure SQL Managed Instance performs automatic backups of you database every 5-10 minutes. If anything happens with your database and even if someone drops it, your data is not lost. Managed Instance enables you to easily re-create the dropped database from the automatic backups.

Click through for the Powershell script.

Comments closed

Get Windows Failover Cluster Errors

Published 2019-01-21 by Kevin Feasel

John Morehouse walks us through the Get-ClusterLog cmdlet in Powershell:

Sometimes you know that a problem occurred, but the tools are not giving you the right information.  If you ever look at the Cluster Failover Manager for a Windows Cluster, sometimes that can happen.  The user interface won’t show you any errors, but you KNOW there was an issue.  This is when knowing how to use other tools to extract information from the cluster log becomes useful.
You can choose to use either Powershell or a command at the command prompt.  I tend to lean towards Powershell. I find it easier to utilize and gives me the most flexibility.

Click through for an example, including of a method which filters out everything but error messages.

Comments closed

SQL Undercover Inspector V1.3

Published 2019-01-17 by Kevin Feasel

Adrian Buckman announces a new version of the SQL Undercover team’s Inspector:

We know some of you really hate linked servers so we have been working on a powershell collection which will allow you to install the inspector without using linked servers to centrally log the information and instead the powershell function Invoke-SQLUndercoverInspector will do the rest for you (We will be writing a blog post about how you can use this soon) – this is currently a pre-release version so it’s a work in progress – I must say a massive thank you to Shane O’Neill (b | t) without his powershell skills this wouldn’t turned out as well as it has, thanks Shane!

If you’ve already downloaded this version, be aware that there is a hotfix.

Comments closed

Migrating Lots Of Databases To SQL Server 2016

Published 2019-01-10 by Kevin Feasel

Andy Levy has a problem. Well, about 8000 of them. In part 1, he describes the plan:

How do you move eight thousand databases in a reasonable amount of time? I spent about an hour and a half one morning hashing ideas out w/ folks in the dbatools Slack channel, plus several conversations in the office and with our hosting provider.

Then, in part 2, he describes the execution:

We missed the estimated time for our go/no-go decision by five minutes. With the number of moving parts, databases in play, unexpected delays, and amount of testing we had to do, that’s pretty good! My colleague and I had some additional work we needed to take care of after the team declared the migration a success. Agent jobs needed to be enabled, overnight job startups monitored, things like that. We called it a day after about 14 hours in the office.

It was a nice success story, so check it out.

Comments closed

Automation With Powershell Desired State Configuration

Published 2019-01-09 by Kevin Feasel

Jess Pomfret takes us on a journey to a desired state:

PowerShell DSC is a platform to support the concept of Infrastructure as Code (IaC).  It uses declarative syntax instead of the usual imperative syntax of PowerShell.  This means that you describe your desired state rather than the specific steps needed to get there.  There are two modes for DSC, push and pull, although pull mode offers more features and scalability, we’ll look at writing our configuration and using push mode for this blog post to keep it simple.

This post covers initial installation and some of the initial configuration, so check it out if you’re new to DSC.

Comments closed

Invoke-DbaDiagnosticQuery In dbatools

Published 2019-01-09 by Kevin Feasel

Andre Kamman walks through a particularly useful cmdlet in the dbatools package:

My answer to that is simple, I’m a major contributor to the awesome Powershell library dbatools. What I’ve contributed to that library are commands that will help automate the running and processing of queries from the DMV library of Glenn Berry
At some point in the life of a DBA we’ve all come accross his scripts. For the longest time I would advise people to google “Glenn Berry DMV”, and it will be the top result. 
The scripts however, come in a single file per SQL Server version and you can’t run them all in one go. You would have to select a script, run it, and paste the result from Management Studio into an Excel sheet. Glenn provides an empty sheet with tabs ready to paste the various result sets in. I’ve automated this part, hope you like it!

Click through for a demonstration of this cmdlet and the useful output it generates.

Comments closed

Installing ML Services With Python Support In SQL Server 2019

Published 2019-01-08 by Kevin Feasel

Rich Brenner walks us through installing SQL Server 2019 and enabling Python support:

First things first, you’ll want to choose your version of SQL Server. Python is available on 2017 and greater. For this demo I’ll be using SQL Server 2019 Developer Edition (CTP 2.2).
With 2019 CTP2.2 they’ve increased the requirement of your OS too, in my example I had a spare VM with Windows Server 2012 laying around but I needed to update this to Server 2016. Check the relevant documentation for the version you’re using.

Click through for a step by step guide with plenty of screenshots.

Comments closed

Emailing SSIS Errors

Published 2019-01-08 by Kevin Feasel

Peter Schott improves upon Kevin Hill’s script:


Recently, Kevin Hill (b | t ) posted on getting package errors from the SSIS catalog in a single query as opposed to clicking through the SSIS Reports and digging through pages.  I took that and ran with it a little bit. The first pass needed an additional index on the catalog to increase performance.  Kevin’s included that at the bottom of his query on the post above.  (You probably don’t need the included “message” column, though.)
I wanted to take this and run with it a little bit to report on all errors for a given folder within the last day, then e-mail that in an HTML formatted e-mail. To that end, I wrote up a quick stored procedure that should take the Folder or Package or Project name and a “to” e-mail address to send an e-mail through DBMail.

Click through for the script.

Comments closed

ARITHABORT And SET Options

Published 2019-01-08 by Kevin Feasel

Jason Brimhall explains an error message he received:

INSERT failed because the following SET options have incorrect settings: ‘ARITHABORT’. 
Verify that SET options are correct for use with 
indexed views 
and/or indexes on computed columns 
and/or filtered indexes 
and/or query notifications 
and/or XML data type methods 
and/or spatial index operations.
[SQLSTATE 42000] (Error 1934). The step failed.


Immediately I started looking at my creation scripts for the tables.  Nope, no XML indexes, no spatial indexes, no filtered indexes, no indexes on computed columns (not even any computed columns),  and no query notifications.

In Jason’s case, it was an oddity around SQL Agent jobs, but there are a few reasons this could pop up, and Jason explains some of the most common.

Comments closed

Unattended SQL Server Installations

Published 2019-01-07 by Kevin Feasel

Michal Sadowski walks us through unattended installations:

Imagine a situation when you are tasked to install a dozen SQL Server instances. Using an approach described in previous blog post  installation can take a significant amount of resources and time, also there is a probability of errors due to typos (e.g. in directory paths, etc.)
There are two options that can be used for the installation:
1. Using ConfigurationFile.ini
2. Using command line parameters to setup.exe file
Below there are step-by-step instructions on how to install SQL Server using both options.

If you only have a couple of SQL Server instances in your environment, unattended installation may seem a bit overwhelming. But as you have to deal with larger and larger numbers of instances, it’s the only way to keep your sanity. It’s also the only way to install on Windows Server Core.

Comments closed