Administration

This category page is part of a series of Professional PowerShell SMO Recipes. You can find an index of all recipes on the main SMO Recipes page, and if you want to learn more about each recipe and see some sample screenshots, click on its category page.

These scripts were created and tested on Windows 8.1 with PowerShell v4 and SQL Server 2014, though most recipes should work with earlier versions of SQL Server. PowerShell v3 and above is required for many of the recipes. If you have a recipe request, leave a comment and I'll see what I can do. This cookbook will be continuously built, as I work more with SMO.

Recipe Categories

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 SQL Server Default Paths

Knowing the SQL instance’s default paths is often useful when automating database migrations. SMO has a built-in properties, but they can be unreliable. According to Allen White:

When the default location for the user database files is the same as the system database files, the DefaultFile and DefaultLog properties are never initialized. If you change them (using Management Studio or via the registry values) then the properties will be populated, but if you don’t the values will be empty.

To get around this, you can supplement missing information with MasterDBPath and MasterLogPath.

Alternatively, you can use a SERVERPROPERTY T-SQL query.

Get disk free for all drives, including mounted drives and clustered drives

This snippet uses Get-WmiObject Win32_Volume to get disk info for all drives, including mounted and clustered drives. Thanks goes out to Boe Prox for the straightforward snippet that this recipe was primarily based on.

Limit RAM usage if max is not set

Failing to set Maximum (and some argue Minimum) RAM will likely negatively impact the performance of your SQL Servers. This script uses the suggestions of Jonathan Kehayias to automatically adjust your SQL Server’s settings. In his book Troubleshooting SQL Server: A Guide for the Accidental DBA Jonathan says the following

As a general base configuration, for a dedicated SQL Server machine, reserve 1 GB of RAM for the OS, 1 GB for each 4 GB of RAM installed from 4–16 GB, and then 1 GB for every 8 GB RAM installed above 16 GB RAM. This means that, for a server with 64 GB RAM, the starting point for max server memory should be in the 54 GB range, and then tuned higher based on monitoring the Memory\Available Mbytes performance counter.

Note that the code below will only change your configuration if MaxServerMemory is not set.

Or you can just set it to pre-determined amount for your environment. Say, 80%.

Find Blocking processes and associated SQL statements

I generally try to stay away from embedded SQL calls, but I was unable to figure out how to get the offending SQL statements without performing a quick query to dm_exec_sql_text. Nevertheless, this is still a high-performance call.

This took an awesome 30 milliseconds in my lab :D
blocking

Is executing user a sysadmin on SQL Server?

These recipes determine if the account executing the PowerShell statements/making the SMO connection has admin rights, and return $true or $false. Most of the scripts I use require sysadmin access, so this snippet is used extensively. Unfortunately, I could not find the DMO issysadmin equivalent for SMO, so I had to get creative.

Get NetBIOS name

Figuring out the SQL Server’s network name can be important when you’re determining disk space, using Microsoft.SqlServer.SqlWmiManagement, or moving files using administrative shares. Here are two ways to find it using SMO.

Stop SQL Server and SQL Agent on a remote computer

This script looks a little complex, but I’ve found that .Stop() and .Start() both return success too quickly so I wrote a function to stop the service. Clustered services and stand-alone services act differently, so they’re handled differently. If you attempt to use Get-Service to stop a Clustered SQL instance, it will just cause a failover, which likely isn’t what you’re looking to accomplish.

For clustered instance, this script only stops SQL Server and SQL Agent, not the entire role.

offline-clustered-service

As the above code states in the comments, if you’d like to take all resources within the role offline, change $clusteredservices to:

Start SQL Server and SQL Agent on a remote computer

Along the same line as stopping…

Is SQL Server FILESTREAM enabled?

This is particularly useful when automating restores.

Force Drop Database

While you can also use $db.drop(), using $server.KillDatabase($dbname) is more effective because it drops all connections to the database, then drops it.

Set Database to single user and back

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.

Iterate server list on a SQL Server Central Management Server

Instead of using text files, keep your SQL Server servername inventory in a SQL Server Central Management Server. This script allows you to query all groups or just specific ones.

Want to populate a dynamic parameter with these servers? Check out the code within Watch-DBLogins.ps1.

Want to see more? You can find an index of all recipes on SMO Recipes Index Page page or click on any specific category at the top of this page.

Chrissy is a PowerShell MVP who has worked in IT for nearly 20 years, and currently serves as a Sr. Database Engineer in Belgium. Always an avid scripter, she attended the Monad session at Microsoft’s Professional Developers Conference in Los Angeles back in 2005 and has worked and played with PowerShell ever since. Chrissy is currently pursuing an MS in Systems Engineering at Regis University and helps maintain RealCajunRecipes.com in her spare time. She holds a number of certifications, including those relating to SQL Server, SuSE Linux, SharePoint and network security. She recently became co-lead of the SQL PASS PowerShell Virtual Chapter. You can follow her on Twitter at @cl.

Leave a Reply

Your email address will not be published. Required fields are marked *

*