Powershell

6 Reasons to Learn PowerShell

SQL Server professionals may be reluctant to start using PowerShell.

After all, you already know T-SQL well and can do a lot with that to manage SQL Server.

I’ve been using PowerShell to manage SQL Server for the last 5 years and it has revolutionized how I interact with SQL Server. For example, I recently needed to update a SQL Agent job across the entire production environment. That’s almost 100 SQL Servers. I have a Central Management Server with folders for each product in my employer’s portfolio. Then there are folders for Dev, Staging and Prod. I could have manually clicked into the first product family then the prod folder and right clicked that to make a connection to all the SQL Servers in that prod folder. Then I copy paste my script in there to run it against those prod servers I’ve connected to.

Continue reading

How To Use Powershell to Configure Multiple Databases for Log Shipping

I recently needed to configure log shipping for multiple databases at once as part of a migration project. I turned to PowerShell to do this.

But before we get to that part, this post assumes that you’ve done the upfront work to create shares for the backups to write to and for the backups to be copied to. This will involve providing the right permissions for the SQL Server service accounts involved in Log Shipping. If you are not familiar with this, that’s perfectly fine. Check out this article in MS Docs first.

Continue reading

Introduction To PowerShell For The DBA Part 3

Hello dear reader! This is the third post in a series to help you get started automating things with PowerShell. You will be looking at how to start and stop SQL Server using PowerShell as well as setting the Max Server Memory setting, measuring free disk space and more.

This series is just an introduction to PowerShell and basic functionality you might be interested in as a SQL Server Database Administrator or as a System Administrator responsible for SQL Server. Because it is introductory I don’t have examples of long scripts that do a series of complex things as part of these posts. What the series does have are one or two line scripts that are still powerful and help you explore what is available to you in PowerShell.

Continue reading

Introduction To PowerShell For The DBA Part 2

In Part 1 of this series we began to look at how you can leverage PowerShell as a DBA and there was a brief discussion about automation. Today you will experiment with the DBATools command Copy-DbaLogin and learn about Out-Gridview and the -whatif switch.

Let’s suppose you have one, or more logins you need to get from one SQL instance to another.  Perhaps you are setting up a new test environment for SQL Server 2019 and you need  to move this login to that environment. Maybe this is one node of a SQL Server AlawaysOn Availability Group and you need to get this login, with its SID, on each node of the AG. Below you see I have a screenshot of a login called mysqltestlogin. It’s on a SQL Server 2017 instance.

Continue reading

Introduction To PowerShell for the DBA Part 1

Automating Tasks With PowerShell

What we’re really talking about in this series is automation, specifically by using PowerShell. Now, in simple terms, automation is about removing the human, manual element in doing tasks. Why should you care about that? Automation will provide the following benefits:

• Automation saves you time
• Automation provides consistency
• Automation results in reduced and easier troubleshooting
• Automation can use your current inventory of T-SQL Scripts
• Automation is easier to get started with than you think
• Automation skills are a mostly required skill for career advancement

Continue reading