Press "Enter" to skip to content

Category: Powershell

Set SQL Server Startup Parameters With Powershell

Mike Fal has a function for managing SQL Server startup parameters:

Looking back at the previous blog post, changing the the startup parameters through the SMO is pretty easy with the ManagedComputer class. In some ways, it is too easy. As Shawn calls out, you could easily overwrite the full string and remove the startup locations for your master database (and breaking your instance). This is where tool building can be such an aid, because by wrapping the change code in a function, we can build some safety mechanisms to protect us (or others) from doing harm when trying to make this sort of change. The function I wrote is not terribly long, but I’ll spare you the whole thing by letting you view it on GitHub. We’ll use our time better by going over how I constructed it while focusing on some of my tool building principles.

Thanks to Mike for making that available to the community.

Comments closed

Schedule Powershell Tasks

Steve Jones show us how to schedule Powershell tasks:

One of the things I needed to do with my SQL Saturday download process was run this automatically. I don’t want to remember to do this, so I decided to set this up on a schedule.

I ran a quick search and it seemed to be a simple process. Essentially I run the Task Scheduler application and then call my script as a parameter to the PowerShell exe.

Windows Task Scheduler isn’t fantastic (and they seem to mess with it in every version), but it does let you run Powershell scripts easily.  Bonus points for those of you using at for scheduling.

Comments closed

Moving The Master Database

Mike Fal shows us how to move the master database:

Last week I blogged about how you can use Smo.ManagedComputer class to update service accounts for your SQL Services. My friend Shawn Melton(@wsmelton) decided to explore the class a little more and discovered how you can change your startup parameters using this class. Shawn’s examples are focused around adding common trace flags to your instance, which is a typical practice. However, let us look at using this to move master.

A word of warning:  don’t move the master database unless you really need to.

Comments closed

Ending A Powershell Loop

Steve Jones shows how to end a loop early:

That’s fine, but it’s not a great loop. It runs 9999 times, which isn’t what I want. It works, but it’s an unnecessary use of resources. However I don’t want to break the loop when the file file isn’t found. There have been issues generating a file, like #350, when #351 exists and is there.

I decided to use a shortcut technique I had learned as a kid. I set a variable and then incremented it when I missed a file. When the increment reaches some value, I break the loop.

I’d just as soon use a break statement, but there are many ways to skin a cat.

Comments closed

SQL Server Startup Parameters

Shawn Melton shows us how to modify SQL Server startup parameters using Powershell:

Low and behold the StartupParameters property is one that can be read and set. So how do you set it? Well the one thing to remember is you DO NOT need to remove what is already in that property because IT WILL BREAK YOUR SERVER!

Let me be clear, setting the property means you need to append to what is already there, so don’t just go setting it equal to something like “-T1118”. Doing this will remove the required parameters to start SQL Server itself, and no it will never warn you of this…so proceed at your own risk.

Read the instructions; otherwise, you can mess up your installation, and that’d be a bad thing.

Comments closed

Powershell Service Management

Mike Fal gives us a pattern for managing SQL Server services with Powershell, WMI, and SMO:

I have built a function around using the second method that makes handling this process a little easier. Also, because I’m not a fan of passing passwords in plain text, I built the function to take a PSCredential object to keep my account information secure. In order to spare you the wall of text, you can view the full function on my GitHub repository.

The function can be loaded through a variety of methods, but once it is loaded calling it is simply a matter of creating the credential for the service account and calling the function

Good stuff.

Comments closed

Test File Existence Using Powershell

Steve Jones shows us the Test-Path cmdlet:

One of the enhancements I wanted to make was check if the file exists, and if not, then download it. However, if it does exist, then I’ll skip the file. I know this means I don’t get updated files if schedules change, which is possible, but in that case, I can just delete the file from my desktop and go from there.

I made a quick search, and found a few links to the Test-Path cmdlet. Essentially you give this a file path and it returns true or false. Almost exactly what I need.

Test-Path is small but helpful, and a vital part of scripts which check files.

Comments closed

PowerShell-Based Health Checks

Omid Afzalalghom discusses an open-source tool using PowerShell to read various DMVs and give you a basic health check:

PowerShell is an ideal tool for doing health-checks of a collection of SQL Server instances, and there are several examples around, but few acknowledge the fact that individual DBAs have their own priorities for tests, and need something easily changed to suit circumstances. Omid’s Healthcheck allows tests to be SQL or PowerShell and requires only adding, altering or deleting files in directories.

Grab the tool from his GitHub repo.

Comments closed

Speed Up SQLPS Load Time

Shawn Melton shows us how to make SQLPS load a bit faster, and which comes with the obligatory warning:

WARNING: You are modifying the files at your own risk. You have been warned.

If you are not familiar with the files involved with a module, you can read more on that here. The file I found most interesting is the “SqlPsPostScript.PS1” file, located in the SQLPS module folder for the given version of SQL Server:

Check it out.  Those two seconds you save add up over time.

Comments closed

SQL Agent Reporting

Mike Fal shows us how to use Powershell and T-SQL to get SQL Agent job status:

This is effective, but I struggle a little with the SQL query. It’s good, but suffers from the structure of the jobs tables in MSDB. We have to account for that and it makes the SQL query a little convoluted. It would be helpful if we could reference a simple data set like the Job Activity Monitor in SSMS.

Of course, this is a leading question on my part. There is a way to do this and it is by leveraging the SQL Server Management Objects (SMO). This .Net library is the API interface for working with SQL Server and is what SSMS is built on. Because it is a .Net library, we can also access it through Powershell.

SMO’s a powerful thing.

Comments closed