A Fine Slice Of SQL Server
Shane O’Neill has a few Powershell tips for you:
If you spend a lot of time in a PowerShell console, it’s not rash to presume that you’re going to be running some of the same commands over and over again.
That’s where PowerShell’s history comes into play.
By using the command
Get-Historyor even its aliash, you can see the commands that you’ve run before:
Click through to see how it works, as well as a few other tips.
Comments closedClaudio Silva has started a series on backing up your SQL Server instance configuration. Part 1 walks us through the basic process:
If you have never used this command, you can test for a single instance by running the following:
Export-DbaInstance -SqlInstance "devInstance" -Path "D:\temp"This will create all scripts in the
D:\tempfolder. A folder named “devInstance-{date}” will be created.
In this folder, you will find 1 file per ‘object type’. The file names are in the form of “#-.sql” where the # is a number that represents the iterator on the order that the internal calls of the underlying functions happen.
Be sure to read the sections around passwords!
Part 2 introduces parallelism into the mix:
There are a couple of options, like the native PowerShell cmdlets
Start-Job/Stop-Joba.k.a background jobs, Runspaces jobs and Thread jobs but I will just mention two of them. One is a nice addition to the most recent version of PowerShell (v7) and the other using a PowerShell module.In case you don’t know, with PowerShell v7 it’s possible to use a new option
-ParallelwithForEach-Object. Check PowerShell’s team blog post PowerShell ForEach-Object Parallel Feature.However, because I don’t have (yet :-)) PS7, I will keep leveraging on PoshRSJob module, which uses runspaces, created by Boe Prox (T | B).
There’s a lot of meat in that second part, so check it out.
Comments closedSander Stad walks us through creating a template for building SSDT solutions:
Yes, templates. But how are we’re going to create a template for an SSDT solution in such a way that it can be reused?
That’s where the PowerShell module called “PSModuleDevelopment” comes in. PSModuleDevelopment is part of the PSFramework PowerShell module.
The PSModuleDevelopment module enables you to create templates for files but also entire directories. Using placeholders you can replace values in the template making is possible to have the name and other variables set.
This is where the SSDT template comes in. I have created a template for SSDT that containes two projects. One project is meant for the data model and the other project is meant for the unit tests.
Read the whole thing and check out Sander’s GitHub repo.
Comments closedEric Cobb has a tiny script for us:
There seems to be some ongoing debate around whether or not formatting your data and log drives with 64KB allocation unit size even matters anymore. I would encourage you to do your own research to determine if you want to do this or not. My personal take on it is: if it doesn’t hurt, and it may help, and it only takes 2 seconds to click the “go” button on my PowerShell script, then I would rather go ahead and do it and not need it than not do it and wish I had later down the road.
I don’t have a strong opinion in that debate, myself, so I’ll just say that if you want to see how to do this in a couple lines of Powershell code, check out Eric’s post.
Comments closedI’m sure you know that PowerBI tabular models are 100% the same as SSAS tabular models, hence you can use SSMS to connect to a PowerBI Tabular model as if it was SSAS (because it is), to do whatever you want, for example scripting out the objects that you’ve created in the PowerBi Desktop GUI or extract the model to deploy it in SSAS.
The only issue to do so is that PowerBI Desktop each time you open a report creates the local SSAS Tabular instance on a random port, so it’s not like you can save a connection string and that’s it
But Emanuele has a quick Powershell script to find the port for you.
Comments closedWhat are the steps involved in a process like this?
Generally speaking, the quickest way I know is:
1. Get the permissions of the source login/user (For each database because we don’t want to miss any permission)
2. Save them to a .sql file
3. Open the file and replace ‘srcUser’ by ‘newUser’
4. Execute
Claudio shows how you can accomplish this quickly.
Comments closedJess Pomfret takes us through the main idea behind PSDefaultParameterValues:
I first heard about PSDefaultParameterValues from a PSPowerHour session by Chrissy LeMaire in 2018. After rewatching this recently, I realised she even mentioned this exact scenario. However, it took until I recently rediscovered this handy preference variable that it all clicked together.
PSDefaultParameterValues does exactly what the name suggests- it lets you specify default values for parameters. PSDefaultParameterValues can be set as a hash table of parameter names and values that will be used in your session for any function that can use it. A simple example is the verbose parameter. If you wanted to turn on the
-Verboseswitch for every function you run you could add-Verboseto each function call, or you could set PSDefaultParameterValues.
Click through for a few examples of how you can use this.
Comments closedJeffrey Hicks builds out a quick Powershell script to test network performance:
I hope you’ve been trying your hand at the scripting challenges being posted on the Iron Scripter web site. The challenges are designed for individuals to do on their own to build up their PowerShell scripting skills. A few weeks ago, a challenge was posted to create a network monitoring tool using PowerShell and the Write-Progress cmdlet. I thought I’d share my notes on the challenge and some of the code I came up with.
It’s an interesting challenge and solution.
Comments closedFred Weinmann explains why you probably don’t want to build a nested module in Powershell:
Yay, but … that is not enough for some people:
– What if somebody copy&pastes it to another machine and forgets the dependencies?
– What if another module uses the same dependency, but at a different version?
– What if I don’t want to confront the user with those dependencies?
– What if a later version of my dependency breaks things? I just tested this version!And the answer to all four of those is the same: Ship dependencies within your own module, hidden from prying eyes. With the NestedModule feature of PowerShell modules we natively support that as well. Great! Feels good, feels stable, feels reliable, feels … solid.
It’s an illusion.
Click through to understand why this benefit is illusory.
Comments closedJohn McCormack takes a look at dbatools with an eye on performance tuning:
DBATools is well known in the SQL Server community for the ease at which it allows you to automate certain tasks but did you know that DBATools can help with performance tuning your SQL Server. As my job recently changed to have more of a performance tilt, I wanted to investigate which commands would be useful in helping me with performance tuning. It turned out there are quite a few.
There are some good commands in here.
Comments closed