Category: Powershell

Rob Sewell walks us through dbachecks configuration:

So I can import this configuration and run my checks with it any time I like. This means that I can create many different test configurations for my many different environment or estate configurations.

Yes, I know “good/best practice” says we should use the same configuration for all of our instances but we know that isn’t true. We have instances that were set up 15 years ago that are still in production. We have instances from the companies our organisation has bought over the years that were set up by system administrators. We have instances that were set up by shadow IT and now we have to support but cant change.

As well as those though, we also have different environments. Our development or test environment will have different requirements to our production environments.

This is a good, code-filled demonstration with a few videos as well.  If you want to get started with dbachecks, this is a good place to begin.

Cody Konior has a good post on using psake to populate the FunctionsToExport section of a module definition:

The “best practice” and proper way of handling this then is to add an entry to the array in this file every time you create a new function. Manually. What a pain in the ass right? But there’s a better way!

If you haven’t seen psake before you can take a look later. Start up PowerShell as Administrator and install psake from the PowerShell Gallery using Install-Module psake and then you’re ready to go.

How does psake work? It’s the PowerShell equivalent of a Makefile. Basically:

• You add a file in the root of your module called psakefile.ps1

• When you run Invoke-psake from that location, it will load and execute the file

For a one-off module with one or two functions, it’s probably not worth it, but once you get to several functions (or if you’re building modules regularly), this looks like a time-saver.

Jana Sattainathan shows how to use Powershell to look up a group of Active Directory users’ managers:

Today, I received a request to find the manager for a whole bunch of users. This was a list of names (not UserId’s) in a Excel worksheet.

It is not actually that complex to do it

1. Locate the AD user based on the name

2. Check the Manager property

3. Lookup AD again for Manager to get the name

Click through for the script.  This does, of course, assume that the information is already in Active Directory somewhere.

Steve Jones uses dbatools to fix orphaned users:

One of the common issues that I would run into with refreshing development and test environments are the orphaned users. These are accounts that typically exist in production, but not in development. The logins and users are different in these environments, and often there isn’t a login on the development instance. This creates an orphaned user: one that exists in the database, but has no instance level mapping.

Cleaning up these users isn’t that hard, often with a removal of the user, mapping to a different login, or repairing this user by adding back the server login.  These aren’t difficult tasks, but the logic to write a script to quickly fix this, especially in a DR situation, may not be handy.

Read on to see how easy dbatools makes this.

Andy Mallon shows off the drive-based navigation available in the SQL Server Powershell module:

I recently noticed that the system objects were missing from my results when I do a Get-ChildItem. I noticed it with views, but then realized that none of the system objects showed up. What gives? I floundered through a quick Google search, where I knew I wasn’t searching for the right thing, and was not surprised when I didn’t see the answer.

I said to myself, “Andy, hold on a second & think. If something doesn’t want to open up, sometimes you just have to -force it open.”

I don’t tend to use this much, as I have recollections of it being slow.  Nonetheless, it is good to know about.

Jana Sattainathan has to find and replace a lot of code:

The database has 100’s of stored procedures (700+ to be somewhat precise)

Qualifying stored procedures have these characteristics

• Procedure name ends with “_del”
• Procedure has the string “exec” in the code
• Procedure has the string “sp_execute” in the code

This is what needs to be done:

• Replace CREATE PROC with ALTER PROC

• Replace SYSTEM_USER with “ORIGINAL_LOGIN()”

• Replace AS at the beginning of CREATE PROC with “WITH EXECUTE AS OWNER AS”

• Comment out some SET statements

• …in fact, there could be any number of other changes

Read on to see how Jana did it.

Garry Bargsley walks us through a solution he implemented to load Excel file data into Powershell:

Recently one of our development teams has increased the request of importing an Excel file with 20 sheets in to 20 tables in a database from about once a quarter to multiple times a week and this past Monday was three times in one day.  I have been the lucky DBA to get these requests as of late and after Monday I was determined to fix the process.  The current procedure is to use the good ol’ Import/Export Wizard since this was a rare request. (This included a lot of point and click and possibility for manual error)  With increased requests and increased table counts I knew there had to be a better way to get this accomplished without grimacing each time I see the request.

Garry has a script which he uses, but which can be tailored for other uses pretty easily.