Professional PowerShell-based SQL Server SMO Recipes

Last year, I finally started working with SMO (SQL Management Objects) within PowerShell and I absolutely love it. Below are a few recipes that I created and use extensively throughout my SQL Management scripts, which you can find on ScriptCenter. Some recipes show more than one way to perform a task.

These SMO recipes are particularly useful because of the extensive automation being performed. For instance, in the restore recipe, file structures are automatically determined using information contained within the given .bak file. These scripts were created and tested on Windows 8.1 with PowerShell 4 and SQL Server 2014. PowerShell v3 and above is required for many of the recipes.

SQL Administration

Programmatically change SQL Server startup parameters

Usually when we deal with SMO, we’re using the Microsoft.SqlServer.SMO. This snippet, however, uses Microsoft.SqlServer.SqlWmiManagement. This is what controls everything you usually see in SQL Server Configuration Manager.

sql-config-mgr

It’s important to note that you must use the server’s network name when working with SqlWmiManagement. This script automatically determines your SQL instance’s network name by using $server.ComputerNamePhysicalNetBIOS.

Get info similar to SQL Server Management Studio Activity Monitor Processes tab

I used this technique to collect information for my DB/Login Inventory App, Watch-DBLogins.ps1.

In my script, I added the results to a SQL table and collected the information over time.
Watch-DBLogins

Get Server Configuration (sp_cofigure)

$server.Configuration is useful for not only viewing, but modifying server configurations as well. Don’t forget the .Alter() if you make any changes.

To see more recipes, head to the PowerShell SMO Recipe index.

Posted in PowerShell, SQL Server

Enumerate file structure within detached MDF database file using SMO

While researching how connect to a detached MDF and read the internal file structure of the internal files, including LDF files, I kept seeing suggestions for using the undocumented DBCC command DBCC checkprimaryfile. Initially, my code looked like this:

After some exploration, I discovered that this task can actually be accomplished purely in SMO using EnumDetachedDatabaseFiles and EnumDetachedLogFiles.

Posted in PowerShell, SQL Server

Create Scheduled Task or Scheduled Job to Indefinitely Run a PowerShell Script Every 5 Minutes

Here are two straight-forward ways to create Scheduled Tasks within Windows using PowerShell. One script creates a PowerShell Scheduled Job, and the other creates a classic Windows Scheduled Task. You can read about the differences here. PowerShell v3 and above required.

Create a Scheduled Job

The following script creates a PowerShell Scheduled Job which shows up in the Task Scheduler GUI, and also generates files located at:

$home\AppData\Local\Microsoft\Windows\PowerShell\ScheduledJobs\\Output

I included the options that I most often use — repeat every X indefinitely, run as specified user, and run with highest privileges. Note that you should always use service accounts or accounts without expiring or changing passwords when setting up Scheduled Jobs and Scheduled Tasks.

This job will appear under Task Scheduler -> Task Scheduler Library -> Microsoft -> Windows -> PowerShell -> ScheduledJobs.

taskscheduler

These Scheduled Jobs can be managed within PowerShell using cmdlets.

Create a Scheduled Task

This script will generate a new Scheduled Task. As previously stated, I included the options that I most often use — repeat every X indefinitely, run as specified user, and run with highest privileges. Note that you should always use service accounts or accounts without expiring or changing passwords when setting up Scheduled Jobs and Scheduled Tasks.

taskscheduler2
This job will appear under Task Scheduler -> Task Scheduler Library. Also, don’t forget to return $true or $false. If you don’t, the “Last Run Result” will be 0x1.

Posted in PowerShell, Windows

Workaround for SMO Bug: urn could not be resolved at level OleDbProviderSetting

Microsoft ConnectM isn’t letting me sign in to post this workaround, but I just ran into a bug with the OleDbProviderSettings within SMO.

smobug

To get around it, I used $server.Settings.OleDbProviderSettings instead of $server.OleDbProviderSettings

Posted in PowerShell, SQL Server

About Invoke-Locate, a PowerShell port of GNU findutils’ locate and updatedb.

Tonight, I published Invoke-Locate.ps1 to ScriptCenter because I was tired of mounting my Windows drive to my Linux servers to quickly and reliably find files. Originally, it was going to be a small script, but then I decided to trick it out, add an installer and automatic Task Scheduling to run updatedb.

The installer gets you going pretty quickly. I’ve always appreciated how easy WordPress is to setup, and tried to follow suit.

install

That’s it. Then you run your locate or updatedb.

search

The script is super fast (it had to be, because GNU locate is) and each query takes about 300ms.

measure

A task in created in Task Scheduler, which rebuilds the index every 6 hours, kinda like what cron does for locate. Originally, I thought I’d use SQL Server Compact Edition (CE) but was super disappointed when the queries took FOUR SECONDS, as opposed to SQLite which takes 0.3 seconds per query!

Where it all started

This is the heart of the code. I’ll include both SQLite and the SQL Server CE editions, for those interested.

The original test using SQL Server CE

It’s likely that the slower results is because of the fact that SQL Server CE was not intended to be super fast, it was intended to be compatible with larger SQL Server-based projects.

The better test using SQLite

This query took an incredible 0.3 seconds. I recommend using SQLite for any small projects, if at all possible. If you’re interested in the formal script, you can download it on ScriptCenter

Posted in PowerShell, SQL Server

Downgrade SQL Server Cluster from Enterprise to Standard

I’m currently working on a PowerShell/SQL set of scripts to ease SQL Server Migrations. A few of the scripts have been posted to Microsoft’s ScriptCenter. Recently, one of the ScriptCenter visitors asked:

Can this script be used to backup all server configuration and restore at a later date. The use case I have is that I have a SQL 2012 Enterprise cluster and need to downgrade it to SQL 2012 Standard. A key requirement is to maintain the same IPs/SQL instance names/virtual names etc. Our method will be to uninstall and reinstall SQL and we need a reliable method to backup and then restore the databases/logins/server configuration.

My response got cut off, so I’m posting it here.

Start-SQLMigration.ps1 can backup key elements of the server configuration, but cannot restore databases from a server that is offline. I’m currently working on a separate script that would likely help in that scenario, but it will be a bit before it’s released.

Fortunately, I just tested the following in my lab and was successful in downgrading my Enterprise Edition cluster to Standard. Here are the steps I took:

  1. Took note at the exact version of SQL Server, down to the build number
  2. Made backups of all databases, including the system databases
  3. Stopped the SQL server role within the Failover Cluster Manager
  4. Once the disks went offline within Windows, took a storage-level snapshot of the shared disks. I named this snap “ent version”.
  5. Copied the system databases, and their logs (mdfs and ldfs) to backup directories just in case the snapshot didn’t work (one can never be too safe)
  6. Used VMware to snapshot both Windows nodes (alternatively, you can shut them down and snap them at the storage level)
  7. Uninstalled SQL Server from both nodes using the Remove Node from Cluster option. This will delete the system dbs, but not the user dbs.
  8. Deleted templog.ldf from my system directory (it was the only one that the uninstall didn’t get rid of)
  9. Reinstalled SQL Server Standard exactly as Enterprise was before on both nodes – down to the IP, service account, name, directory structure, filestream share etc.
  10. Patched both nodes of SQL Server until I was back at the same build
  11. Ensured the SQL role was stopped within the cluster and that disks were offline
  12. Snapshotted the storage again, naming this snap “std version”
  13. Restored storage back to “ent version”
  14. Started SQL role within the cluster
  15. Voila! The version was now SQL Standard and all of my dbs/logins/settings were just as they were.

Of course, you should not do this without testing it in your dev environment first.

Posted in PowerShell, SQL Server

PowerShell: Create Human-readable Shortcuts to IIS Log File Directories

About 8 years ago, I wrote a blog post that provided a VBScript to create easy-to-read shortcuts to IIS Log directories. W3SVC56 just wasn’t helpful enough to identify which website logs to which directory.

Today, I ported that same script to PowerShell. I’m too lazy to explain, so this is what it does:

iis-big

Easy peasy. Here’s the script. Note that you have to run this on the IIS server itself, and you have to have adequate permissions. I ran this with admin privs.

Posted in IIS, PowerShell

Batch Resize Photos in Linux

Oops! I forgot to tell my mom to resize her photos before uploading them to RealCajunRecipes.com. We had colossal 7MB+ images floating around on the web server, and SSD space doesn’t come cheap. No problem, I just installed ImageMagick, backed up my directories, then ran the following command which finds all the 1MB+ *.jpg files within the uploads directory, then resizes them to a reasonable dimension (800xwhatever)

Posted in Linux

About

We are IT pros who grew up living and loving life in Cajun Country.

Authors


Chrissy LeMaire
View Chrissy LeMaire, BSc. MCITP's profile on LinkedIn


Brandon Abshire
View Brandon Abshire, MCDBA's profile on LinkedIn