Press "Enter" to skip to content

Category: Powershell

Type Information Change In Export-CSV Cmdlet

Max Trinidad notes that a default parameter in the Export-Csv cmdlet has flipped between Powershell on Windows and Powershell Core 6:

For a long time, in Windows PowerShell, we had to add the parameter “-NoTypeInformation“, so the “#TYPE …” line on the first row of the *CSV would not be included.

So, in Windows PowerShell executing the command without the “-NoTypeInformation” parameter, will produce the following result:

Now, using the same command in PowerShell Core without the “-NoTypeInformation” parameter, will produce a different result:

This is a better default, but I think it’s going to burn some people who have scripts pre-built expecting to clear out that first line.

Comments closed

Creating Azure VMs Using Powershell: Laying The Groundwork

Robert Cain has part one of a two-part series on creating VMs in Azure using Powershell:

Creating a virtual machine is great, but it won’t be of much use unless it can communicate outside of itself. That’s where virtual networking comes in. To setup a virtual network, often abbreviated vnet, you need to accomplish three things. First is the creation of the virtual network itself. After the network is created, you need to define a security group for it. In essence, the security group defines a firewall. In the process of creating it, the PSAzure module automatically creates firewall rules that allow HTTP and RDP (Remote Desktop Protocol) traffic through the firewall. There are functions in PSAzure to create security groups at a lower level, allowing one to create alternate rules. This example will demonstrate the most common options.

The final step is to create a virtual NIC, or Network Interface Card. The NIC will form the bridge between the virtual network and the virtual machine, much like a physical network card allows a physical computer to connect to a real network. First off, a few variables are assigned. These will hold names for the security group, network and subnet names. The network addresses for the main network and subnet are also placed into into variables. Finally, a name is assigned to the NIC.

Check it out, especially if you build a lot of VMs in Azure.

Comments closed

Running The SQL Server Features Discovery Report

Dave Mason shows us how to run the SQL Server features discovery report via command prompt and PowerShell:

I don’t need to validate SQL Server installations on a regular basis. When the need arises, my preference is to run the SQL Server features discovery report. Further, I prefer to run it from the command line. After looking up command line parameters one too many times, I decided to script it out.

It turns out the script commands are a little more complicated than I realized: there is a different setup.exe file for each version of SQL Server installed. I ended up making two script versions: a DOS batch file with hard-coded paths, and a PowerShell script that’s more robust. Check them out and let me know what you think. (Keep scrolling down for a report sample image.)

I’m not sure I’ve ever run that report, but now I know how to do it from Powershell.

Comments closed

Synchronizing User Logins Across SQL Server Instances

Hamish Watson shows how easy it is to synchronize SQL authenticated logins using dbatools:

When building new servers the most important thing after restoring and securing the database is syncing up the users. This is especially important for Availability Groups as SQL Authenticated users required the SIDS to be the same.

In the past I had some very long winded code that would do the sync – it was a mixture of TSQL and PowerShell. It worked but you know – it was cumbersome.

Read on to see how life gets easier with dbatools.

Comments closed

Optimizing Powershell Module Load Times

Chrissy LeMaire explains ways that the dbatools team reduced how long it takes to load their module:

We noticed that the longest part of importing the module was importing all the extra SMO DLL’s that we require for many of the commands. We import about 150 DLLs and it looks like that number will only grow as we begin to support more functionality (such as Integration services, etc.)

To address this concern, Fred added multi-threading via runspaces to our import process. Too cool! This resulted in a significant decrease in time.

Read on for more details; some of these tips might work on other slow modules, too.

Comments closed

Loading CSVs Into Azure Using dbatools

Stuart Moore has a quick Powershell script which loads CSV data into Azure SQL Database using dbatools:

To get some of this data usable for reporting we’re importing it into Azure SQL Database so people can start working their way through it, and we can fix up errors before we push it through into Azure Data Lake for mining. Being a fan of dbatools it was my first port of call for automating something like this.

Just to make life interesting, I want to add a time of creation field to the data to make tracking trends easier. As this information doesn’t actually exist in the CSV columns, I’m going to use LastWriteTime as a proxy for the creationtime.

Click through for the script.

Comments closed

Tracking Powershell Command Execution Time

Constantine Kokkinos shows how to track time spent on the last command in Powershell:

You can select any property from the output and get just the TotalSeconds, but I like this simple output for when I have to leave some work in progress and I need to come back and check some time in the future.

If you are confused by this code and want further explanations, keep reading!

That’s a lot simpler than the “classic” .NET way of setting up a StopWatch and tracking changes.

Comments closed

dbatools: The Swiss Army Knife For DBAs

Jess Pomfret uses this T-SQL Tuesday to cover some of her favorite cmdlets in dbatools:

Test-DbaSqlBuild

When I found this command I couldn’t have been more excited. My day-to-day job requires the care and watering of over 100 SQL Server instances of varying versions.  Using this command you can get the current build of all your instances and then compare that to the most recent available.  There are also parameters for how far you want to be from the latest version. Setting the -latest switch means just that, your server will only be seen as compliant if it’s on the latest release, passing in -1CU means that it can be no more than 1 cumulative update behind.

Read on for a few additional useful cmdlets.  Out of a large number of useful cmdlets.

Comments closed

SQL Server Powershell Module On PowerShell 6 Core

Drew Furgiuele is ready to retire to his fainting couch:

So, I bit: the tweet he referenced was announcing a new version of the SQL Server module (21.0.17240). Here’s a quick list of the updates included:

  • Added Get-SqlBackupHistory cmdlet
  • Ported PS Provider to .NET Core for PowerShell 6 support 
  • Ported a subset of cmdlets to .NET Core for PowerShell 6 support 
  • Powershell 6 support on macOS and Linux in Preview. 
  • To use SqlServer provider on macOS and Linux mount it using a new PSDrive. Examples in documentation.
  • Removed restriction of 64-bit OS for this module. Note: Invoke-Sqlcmd cmdlet is the only cmdlet not supported on 32-bit OS.

The bold lines are my emphasis: with PowerShell 6 support for Linux and macOS, that opens up new avenues for connecting to and automating SQL Server from any platform. This is exciting stuff. I couldn’t wait to take it for a spin, so I set up a quick demo environment to test it out.

It’s not perfect but it did give Drew the vapors, which is a good sign that they’re on the right track.

Comments closed

Collecting Login Details With dbatools

Chrissy LeMaire shows us several ways to track who has connected to your SQL Server instance:

Using the default trace is pretty lightweight and backwards compatible. While I generally try to avoid traces, I like this method because it doesn’t require remote access, it works on older SQL instances, it’s accurate and reading from the trace isn’t as CPU-intensive as it would be with an Extended Event.

Click through for details on this as well as three other methods, along with the dbatools glue to make it work.

Comments closed