A Fine Slice Of SQL Server
Jeffrey Hicks shows how we can format TimeStamp objects:
This is pretty straight forward. Subtract the LastBootUpTime property from the current datetime to get a timespan object that shows how long this computer has been up and running. But…I want to get rid of the milliseconds value. It’s irrelevant as far as I’m concerned and takes up space that I might want to use for another property. I need to format that value.
Click through for several methods.
Comments closedMike Bronowski takes us through the ImportExcel Powershell module:
Using Add-PivotTable is straightforward (and from now on I am closing the whole Excel). Note the Activate switch at the end. When you open the Excel file the worksheet that was used in the command will show up first.
A long while ago, I had put together Powershell code to do this kind of work with Excel, but back then we needed to use COM. This looks much simpler.
Comments closedGerhard Brueckl has updated the DatabricksPS library:
Databricks recently announced that it is now also supporting Azure Active Directory Authentication for the REST API which is now in public preview. This may not sound super exciting but is actually a very important feature when it comes to Continuous Integration/Continuous Delivery pipelines in Azure DevOps or any other CI/CD tool. Previously, whenever you wanted to deploy content to a new Databricks workspace, you first needed to manually create a user-bound API access token. As you can imagine, manual steps are also bad for otherwise automated processes like a CI/CD pipeline. With Databricks REST API finally supporting Azure Active Directory Authentication of regular users and service principals, this last manual step is finally also gone!
If you do use Databricks and haven’t tried out DatabricksPS, I highly recommend it. I think it’s a much nicer experience than hitting the REST API directly, particularly because it deals with continuation tokens and making multiple calls to get your results.
Comments closedShane 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 closed