Press "Enter" to skip to content

Category: Powershell

SQL Server Trends Worth Watching

Published 2019-11-13 by Kevin Feasel

Grant Fritchey follows up on a Kevin Hill tweet:

There are a million things to learn about in our rapidly shifting technological landscape, but I think this assessment, especially the way it was put, “no longer justify ignoring” really nails some of the fundamentals.

Let’s talk about why you can no longer ignore Docker, Git and DBATools either.

If you’re a DBA and aren’t familiar with Docker, Git, or DBATools, that’s a pretty good trio of things to spend some time learning. You can survive without them, but you’re more likely to thrive if you know them.

Comments closed

Powershell Notebooks in Azure Data Studio

Published 2019-11-12 by Kevin Feasel

Aaron Nelson announces a new feature in Azure Data Studio:

In order to get all the nice intellisense and tab completion features of the PowerShell language inside your PowerShell Notebooks, be sure to install the PowerShell extension from the Azure Data Studio marketplace.

At this point, the biggest remaining language is R, though I’d love to see F# support as well (hey, Azure Notebooks offers F# support).

Comments closed

SQL Server Assessment API Generally Available

Published 2019-11-11 by Kevin Feasel

Ebru Ersan announces the general availability of the SQL Assessment API in Powershell:

SQL Assessment API is a new mechanism to evaluate configuration of your SQL Server for best practices. The API methods are used by means of a SQL Server Management Object (SMO) extension and new cmdlets in SqlServer PowerShell module. API is delivered with a ruleset that is highly customizable and extensible. It can be used to assess SQL Server versions 2012 and higher, both on Windows and Linux, as well as Azure SQL DB Managed Instance. More products will be supported in future releases.
 
SQL Assessment API is shipped as part of SqlServer PowerShell module (21.1.18206) and SMO NuGet Package (150.18208.0).

It’s a set of reasonably good practices and also lets you customize for your own environment, so check it out.

Comments closed

Scripting Out Linked Servers with Actual Passwords

Published 2019-10-29 by Kevin Feasel

Ajay Dwivedi shows how you can script out a linked server creation statement which includes actual passwords:

For moving Logins/Users, Microsoft provided revlogin script which made it easy for migration of logins without need to know about passwords. But, there is no easy approach for migration LinkedServers with the actual password. This is where dbatools cmdlet Copy-DbaLinkedServer becomes very handy. But, what about the situation where we have to script out LinkedServer beforehand.

For this reason, based on the blog post of Antti Rantasaari, and using his code as the base script, I have created a cmdlet Get-LinkedServer with SQLDBATools module which accepts SqlInstance name as a parameter along with -ScriptOut switch, and gives Drop/Create statements for linked servers present on that local/remote SqlInstance.

As a quick note, SQLDBATools is not the same as dbatools.

Comments closed

Making SQL Agent Jobs AG-Aware

Published 2019-10-22 by Kevin Feasel

Stuart Moore shows how you can use dbatools to make SQL Agent jobs Availability Group-aware:

What do I mean by Availability Group aware? When running on an Availability Group, one SQL Server instance ‘owns’ the database at any point in time, but the SQL Agent jobs have to be replicated across all of the instances in the cluster. So you want to make sure that your SQL Server Agent jobs only do work on the instance that currently owns the Availability Group.

Doing this is pretty simple. Below is a piece of T-SQL that checks if the current SQL Server Instance is the primary instance in the AG. If it isn’t then we exit with an error.

Read on to see how, and how you can use dbatools to automate this work.

Comments closed

Refreshing Power BI Dataflows with Powershell

Published 2019-10-21 by Kevin Feasel

Craig Porteous shows how to use the Power BI Dataflows REST API with Powershell:

I like to use my favourite scripting language to do this – PowerShell. Although we have the Power BI Management PowerShell module (MicrosoftPowerBIMgmt) to interact with Power BI, the cmdlets aren’t yet there to refresh or retrieve the history of a dataflow (or even a dataset) but the module can still help us get what we need without jumping through too many hoops (and as long as we aren’t automating the authentication, that’s another post.).

Click through to see how it’s done.

Comments closed

Deploying CUs to Multiple Instances with Powershell

Published 2019-10-07 by Kevin Feasel

Jeff Iannucci embraces the power of the shell:

This all started because we had some 14 new SQL Server 2017 instances that we were setting up, but we hadn’t yet applied the most recent cumulative update that we are using in our environment.  I started using the Update-DbaInstance cmdlet in the script below to apply to one server, but then I looked at the list of outstanding requests and thought about something Buck Woody once told me.

“You don’t have time for that. You’re going to be dead soon.”

He’s a fantastic fellow, but we should all be grateful he didn’t become a physician.

Click through for the five-line script and an explanation of what each line does.

Comments closed