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.

 

How would we copy this with PowerShell? First, let’s use the Get-Help command to see what commands are available related to logins.

Get-Command *login* -module DBATools, sqlserver

From that output you see a command called Copy-DBAlogin in the DBATools module.

So now run the first command to see the syntax and run the second one to see all the help data, including some examples of how the command works:

Help Copy-DBALogin

Help Copy-DBALogin -Full

The most common way you’re going to use this command is with a Source and Destination SQL Server name and one or more login names. See the example below.

These logins can be SQL logins or Active Directory Logins. One thing to note about this command is that the SID is copied with the login as well as the associated permissions. If you need to, you can use the -Force switch to re-copy an existing login. You might need to do this to ensure that logins, SIDs and permissions are the same between all nodes involved in a group of servers. Those SQL Servers could stand alone instances that are all servicing a particular application or they could be nodes in a Failover Cluster Instance or an Availability Group.

Copy-DBALogin -Source MySourceSQLServerIntanceName -Destination MyDestinationSQLServerInstanceName -Login Login1, Login2, Login3

Maybe you want to copy all the logins except a certain small list. In that case, use the -ExcludeLogin parameter and pass in the logins to omit from the copy operation.

For my scenario from above, my command looks like this.

Copy-DbaLogin -Source MyPCName\Kronos2017 -Destination MyPCName\Romulus2014 -Login mytestsqllogin

And the output is this.

DBATools Copy-DBALogin Output

 

What Is Out-GridView?

You can also use a cmdlet called Out-GridView to pass the objects of the pipeline into a GUI where you can then select the items you want to have PowerShell do the action against.

Get-DBALogin -Sqlinstance MySourceSQLInstance | Out-GridView -PassThru | Copy-DBALogin -Destination MyDestinationSQLInstance

Here are the results of that Out-GridView. Click to enlarge.

Out-GridView Example

To use this for selecting only the rows of output that you want to do something with, left click the row you want to actually copy and then click the OK button in the bottom right. That will copy the selected row. You can use Shift + Click or Control + Click to do a multi-select. There is a Filter option at the top of the Out-GridView screen that also allows you to trim down the results you want to copy.

Of course, using Out-GridView re-introduces the human element to the process and so isn’t great when the goal is to completely automate a process. However, it is great if you want to see the output before you do something you don’t want to do. As a consequence, if you see results in Out-GridView that don’t match what you expect, then you can simply click on Cancel in the lower right hand corner and the PowerShell will do nothing.

Using the -WhatIf Switch In PowerShell

This brings me to another switch that helps in this scenario where you want to see what a command is going to do before you actually executing it. There is a switch called -WhatIf that will stream output showing you what a command would do if it were actually executed. This is a great tool for testing your commands. Often there will be errors or other unexpected results from a command. Using -WhatIf is a great way to catch this before you actually execute the command for real.

So far in this article, you have reviewed what you can do for a very specific object, logins. Let me suggest that you also look at what can be done at the wider scope of the SQL Server level by running the below. You may remember from the previous post that GCM is an alias for the Get-Command cmdlet.

GCM *instance* -Module DBATools, SQLServer

Here is my output for the above. Click to enlarge.

Get-Command Instance :evel Commands

As you see, there are a lot of things you can do with commands related to the SQL Server Instance.

If you want to consider automating the scripting out of your SQL Server instance objects without buying software to do it, then check out the Help for Export-DBAInstance, which is one of the commands that is in the above list.

Help Export-DbaInstance -Full

 

Next Steps To Take

  1. Try out Copy-DBALogin in a test environment. Use it with the -WhatIf switch, or pipe it to a file by using the Out-File command to see what the command does when it runs.
  2. Experiment with various commands and Out-GridView in your test SQL Server environment.
  3. Don’t have a test SQL Server environment? Consider learning what Export-DBAInstance does and using it as part of an “Easy button” for creating a test environment on a fresh install of SQL Server. Or, look up Start-DBAMigration for this task as well.
  4. If you would like help with anything in this post, or with something else related to SQL Server, reach out to me here, or on Twitter, and I’ll be glad to offer assistance.

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. This will produce time savings for you as a DBA in the long run and provide better consistency in your environment than doing things manually. Better consistency means reduced or easier troubleshooting because your SQL Server environment will be more consistent with automation. All of this will make your SQL Servers easier to manage.

Imagine being able to migrate an entire SQL Server instance with a single command! You can do this with PowerShell. You want to copy logins from one server to another without installing sp_help_revlogin and  working through the scripting process involved? You can do that with PowerShell.  You want to copy DatabaseMail settings from one server to other servers without writing out all the T-SQL? You can do that with PowerShell!

With PowerShell you can get information faster than from a GUI, especially when that task needs to be done on multiple computers. These tasks include things like like search event logs, search the SQL Server Error Log,  get the status of services, see sp_configure information and more.

You can also do things like copy SQL Server jobs from one server to another or export the results of sp_configure to a file. When doing an action like backing up a database, PowerShell also enables you to output the actions of the script itself and thereby create a self-documenting log that you can look at afterward to see exactly what the script did.  With a module called DBATools, you can migrate an entire instance to a new SQL Server with a single, short command.

Getting Set Up for Using PowerShell

PowerShell is already installed on Windows. Different OS versions have different versions of PowerShell. There is a process to use for updating your PowerShell version. I’ll not be showing that process in this series though.

Also, in order to follow along in the series. You will need to get the DBATools module for PowerShell. Instructions for doing that are located at DBATools.io. The simple answer is to open PowerShell as an Administrator and run the below command. There are some things to consider and so that’s why the link to DBATools instructions on installation.

Install-module DBAtools

 

You will want the SQLServer module in order for some of these commands to work properly, and you may want to explore the more “native” way to use PowerShell with SQL Server, without the aid of the DBATools module. I think DBATools simplifies things so immensely that it is indispensable. However, you may be curious about what is in the SQLServer module for PowerShell. If so, you can add the module to your PowerShell by opening  PowerShell as an administrator and running the following.

Install-Module -Name SqlServer

 

Once you have DBATools and the SQLServer module installed, you are ready to get started.

Finding Available PowerShell Commands

Get-Command: This cmdlet enables you to explore what is available to do in PowerShell.  For instance, you might run the below to see what commands are available in PowerShell related to backups. Notice you can use asterisks as wildcards as well as specify a PowerShell module to look in for commands. If you don’t have the DBATools module, I urge you to get it installed and start using it, if for no other reason than that you will need it if you’re going to follow along with the examples!

Get-Command *Backup* -Module DBATools, SqlServer

The above command will output a long list of things.  You can backup computer certificates, database certificates, find information about backup history and throughput, as well as a lot of other things beyond simply using PowerShell to make a database backup.

Maybe you’re interested in what is available for Logins or Database Mail. If so, try the below commands.  Notice that the second command says “gcm”.  What is that? Well, that is a shortened version, or alias, for Get-Command.  Most, maybe all commands, have an alias you can use.  Aliases provide a shorter way to write a command, but when someone else is looking at your code, then they have to know what your alias is for. Otherwise it will be harder to figure out what you’re code is doing.

Get-Command *Login*  -Module DBATools, SqlServer

gcm *mail*

Getting Help with PowerShell Commands

From the above example where you are looking for commands containing the word “Login”, you should see something similar to the below if you have DBATools and the SQLServer module installed.

 

Let’s start simple and review Get-DbaLogin.  In your PowerShell window run the command  below.

Get-Help Get-DBALogin -Detailed

From this code you will get an output that provides a summary of what the cmdlet does, the syntax options it can be used with and a text description of what those syntax options do.

Let’s say that you’re interested in finding information about the Logins on your SQL Servers. If you just pass this command to PowerShell you will get a list of all the logins on the SQL instance with some properties. Of course, you will want to substitute your own valid SQL Server instance name.

Get-DbaLogin -SqlInstance MySQLInstanceName

The output will list the computer name, SQL Server instance name, name of the login, the login type, create date, the last login timestamp and some other useful properties.

Let’s suppose we only cared about the logins that weren’t system logins so we want to exclude logins like NT Service\SQLWriter and the ones whose name starts with ##. How do we do that? Well, look at the help output again and notice the -ExcludeFilter option. Add that to the earlier command, like so:

Get-DbaLogin -SqlInstance MYSQLServerInsstane -ExcludeFilter '##*', 'NT*'

PowerShell Piping

Now let’s suppose that you are only interested in a couple of the properties that are output from this command.  You’re interested in the login name and the last time that login accessed this SQL Server.

To get only those properties we can do what is called “piping”.  This is where the “|” symbol is used to move PowerShell output from the left over to the right side of the script objects.  This is often done for filtering or to pass along PowerShell output for further processing by other commands.  PowerShell output looks like text, but it’s really .Net objects.

In the first example below, you’re passing all the objects left from Get-DbaLogin when the ones with ‘##*’ or ‘NT*’ are eliminated and then telling PowerShell to display only the Name and LastLogin objects.

In the second example, you will be sending the output of the command to a file.

Get-DbaLogin -SqlInstance MySQLServerInstance -ExcludeFilter '##*', 'NT*' | SELECT Name, LastLogin

Get-DbaLogin -SqlInstance Skolarlee-PC\KRONOS2017 -ExcludeFilter '##*', 'NT*' | SELECT Name, LastLogin | Out-File 'C:\DBATools\LoginsOutput.txt'

On my local PC this returns the sa account and my local Windows account along with the last time these accounts accessed the SQL Server. This sort of information is useful, probably in a number of scenarios, but let’s suppose that you’re doing an audit of SQL Server logins because you suspect you have a lot of left over, unused logins.  You might want to examine them to see if you can drop users and logins that are no longer being used.  This sort of thing makes your SQL Server easier to manage because you don’t have unused Logins and database users cluttering up your SSMS when you’re connected to a SQL Server.

Let’s suppose you don’t want or need to do something with this information right now, but you want to review it later, or provide it to someone else for review. What can you do? The simplest thing is to use the second command above to send the PowerShell objects to a text file by using the piping technique I mentioned earlier.  The directory in the Out-File command needs to exist already but the command will create the text file for you.  This information can be written to a share for your team to access later.  You can also do things like email this output to the team using a PowerShell command for email. If you run GCM *mail* in PowerShell, you will find a command that lets you do this.

How To Script Out SQL Server Logins With PowerShell

Another powerful thing that can be done with a one line PowerShell script is scripting out your logins and users.  This cmdlet from the DBATools module will create a script of your logins at the server level and their corresponding database users and all associated permissions. Look at the help for this command.

#Start by getting the help for the command
Help Export-DbaLogin -Detailed

Now that you have reviewed the help content, let’s try something.

Export-Dbalogin -SqlInstance MySQLServerInstanceNameHere -Path 'C:\DBATools\'

Now go to the C:\DBATools\ directory and double click the .sql file.  SQL Server Management Studio will launch and prompt you to connect to a SQL instance. Once you do that, the script will load. Review the output and take a look at the Help output from PowerShell to see what else you can do with this.  This command gives a DBA a great way to script out login and user information, and this can be helpful for migrations or for providing a history of changes to logins, users, and their permissions.

Next Steps To Take

  1. Explore more commands and the possibilities that they offer. Here is a place to start.
GCM *export* -Module DBATools

Help Export-DbaInstance -Detailed

Export-DbaInstance -SqlInstance MySQLServerInsanceNameHere -Path C:\DBATools\

2. Find a book or video series on PowerShell and consume that material.

3. Read some of the documentation and blogs over at dbatools.io .

4. If you would like help with anything in this post, or with something else related to SQL Server, reach out to me here, or on Twitter, and I’ll be glad to offer assistance.

How Do I Measure My DBA Skills Part 6

With the last post, we began talking about the Development DBA and the skills needed at the beginning of this career path. Now it’s time for you to ponder what the next level looks like.

Dev DBA II – 2 to 4 Years of Experience

  1. All competencies from level 1
  2. Develop basic to intermediately complex stored procedures, triggers, views and other database objects.
  3. Will have some experience with SSRS, SSIS, and/or SSAS development, deployment and possibly administration.
  4. May administer source control systems for the environment.
  5. Promotes SQL changes from Dev up through all environments, including production.
  6. Contributes to automation, particularly using native SQL Server methods, but could also leverage PowerShell or other languages for automating tasks (Python, C#, etc.)
  7. Designs pre-prod testing of SQL code prior to production upgrades or migrations. This includes performance and regression testing.
  8. Basic to mid-level understanding of execution plans, indexes, SQL Server statistics, and query tuning.
  9. Investigate basic to moderately complex data integrity/repair issues within SQL Server
  10. May begin leading in knowledge sharing in some capacity in one or more areas from level I or II.
  11. May take an active interest in leadership and in development of leadership skills, including emotional intelligence.

In years 2 through 4 the Dev DBA will work with more complex T-SQL such as recursive CTE’s, and error handling with TRY CATCH or the THROW syntax will likely be introduced at this stage. You may begin writing database views during this part of your career and leveraging them to make query writing a bit simpler for yourself and others. You are likely to be exposed to T-SQL Triggers and you’ll need to understand them and even maybe write a few triggers of your own.

You will likely start to get some sort of regular exposure to SSRS, SSIS or SSAS at this point. This will of course be based partly on your own interest in learning the tools of the trade and what is in use already at your employer.

Source control and the process of promoting changes from source control up into QA, and even production, may be something that begins at this level. After all, as a person who is writing a fair amount of code, you will have an interest in ways to manage code changes.

Automation

 

automation example

Let me ask you a question. Would you rather wash clothes manually with a washboard and tub or use a washing machine? It seems like a silly question. You would want to use a washing machine. You probably can’t imagine washing clothes without one.

In a similar thought process, there are things that you and others at your job will not want to do over and over in a manual way.  Accordingly, you will make more significant contributions to automation of tasks at this level in your career. SQL Agent will become your friend and learning to script out processes to make them easily repeatable, rather than doing everything in the SSMS GUI by hand, will occur more frequently. You may automate tasks with PowerShell for sure, especially using DBATools, but Python may also be an option. In case you didn’t know, Python does integrate with SQL Server via a specific driver.

An important part of the Dev DBA’s work is testing SQL code. You’re going to be doing more of that at this level in your career. You will need to verify query results and probably automate repeated testing of code as well so that you can test not only results but performance. Maybe the query returned the results you expected but it took too long to do it. This is where learning how to read execution plans will come in.  Execution plan operators will give you clues about what is happening with your T-SQL and those clues can help you with re-writes or with indexing.

Aaah yes. Indexes.  You’ll need to begin understanding indexes as well.  You’ll need to know what they are, the different types of indexes and when to use each type. There are clustered indexes. Indexes made as a result of creating a primary key. These may be clustered or non-clustered primary keys. There are non-clustered indexes. There are filtered indexes. There are non-clustered columnstore indexes. There are clustered columnstore indexes.  As a corollary, you’ll need to look into the concept of SQL Server statistics as well.

Next Steps to Take

  1. If you’ve read the other parts of this series, then you know I’m going to tell you to copy the skills list above to a Word doc and place an “X” next to the things you need to work on.
  2. Use those “X” marks to build a training plan. Put the training plan somewhere that you will see every day. One thing that should be on your training plan at this stage is learning more T-SQL.
  3. Work the plan. I have a post that will help you find resources for your learning plan.
  4. Contact me here for questions about this post or the skills listed in it. You can also reach out to me via Twitter using the handle @leemarkum.