A Fine Slice Of SQL Server
David Fowler shows how you can find the primary node of an Availability Group using Powershell:
A while back, Adrian put together a bunch of queries for Always On Availability Groups,
7 ways to Query Always On Availability Groups using SQL
7 more ways to Query Always on Availability groupsBut he never actually answered that particular question.
Click through for the script.
Comments closedJeffery Hicks looks at a nice addition to Powershell:
Using this operator in PowerShell 7 means, you can write a PowerShell expression like this:
$IsWindows ? "ok":"not ok"
The traditional If statement alternative would be:
if($IsWindows) {
"ok"
}else{
"not ok"
}
Their implementation pretty much matches C#’s. Read on for additional testing Jeffery did and a few more examples.
Comments closedStuart Moore shows how you can use dbatools to make SQL Agent jobs Availability Group-aware:
What do I mean by Availability Group aware? When running on an Availability Group, one SQL Server instance ‘owns’ the database at any point in time, but the SQL Agent jobs have to be replicated across all of the instances in the cluster. So you want to make sure that your SQL Server Agent jobs only do work on the instance that currently owns the Availability Group.
Doing this is pretty simple. Below is a piece of T-SQL that checks if the current SQL Server Instance is the primary instance in the AG. If it isn’t then we exit with an error.
Read on to see how, and how you can use dbatools to automate this work.
Comments closedCraig Porteous shows how to use the Power BI Dataflows REST API with Powershell:
I like to use my favourite scripting language to do this – PowerShell. Although we have the Power BI Management PowerShell module (MicrosoftPowerBIMgmt) to interact with Power BI, the cmdlets aren’t yet there to refresh or retrieve the history of a dataflow (or even a dataset) but the module can still help us get what we need without jumping through too many hoops (and as long as we aren’t automating the authentication, that’s another post.).
Click through to see how it’s done.
Comments closedRob Sewell shares with us how you can get the installation date for SQL Server:
How can I get the Installation Date for SQL Server on my estate into a database with dbatools ?
You can get the date that SQL Server was installed using the creation date of the NT Authority\System login using T-SQL
Read on for the T-SQL answer as well as the Powershell answer.
Comments closedJeff Iannucci embraces the power of the shell:
This all started because we had some 14 new SQL Server 2017 instances that we were setting up, but we hadn’t yet applied the most recent cumulative update that we are using in our environment. I started using the Update-DbaInstance cmdlet in the script below to apply to one server, but then I looked at the list of outstanding requests and thought about something Buck Woody once told me.
“You don’t have time for that. You’re going to be dead soon.”
He’s a fantastic fellow, but we should all be grateful he didn’t become a physician.
Click through for the five-line script and an explanation of what each line does.
Comments closedGerhard Brueckl has made DatabricksPS better:
I do not know what is/was the problem here but I did not have time to investigate but instead needed to come up with a proper solution in time. So I had a look what needs to be done for a manual export. Basically there are 5 types of content within a Databricks workspace:
– Workspace items (notebooks and folders)
– Clusters
– Jobs
– Secrets
– Security (users and groups)For all of them an appropriate REST API is provided by Databricks to manage and also exports and imports. This was fantastic news for me as I knew I could use my existing PowerShell module DatabricksPS to do all the stuff without having to re-invent the wheel again.
I’ve used DatabricksPS and really like it for cases where I’d have to loop with the Databricks REST API—for example, when uploading files.
Comments closedIn the section above, it would be common for the command to return many thousand objects especially for long date ranges. To prevent overwhelming the API, the
Get-UsageAggregatescommand only returns a maximum of 1000 results. If you’ve saved the$usageDatavariable as covered in the previous section, you can confirm it by using running this command$usageData.UsageAggregations.count.What if there are more than 1000 results? You’re going to have to do a little more work.
Knowing how much you’re spending is critical in an Op-X world like Azure or AWS.
Comments closedEbru Ersan announces public preview 2 of the SQL Assessment API:
SQL Assessment API is a new mechanism to evaluate configuration of your SQL Server for best practices. The API methods are used by means of a SQL Server Management Object (SMO) extension and new cmdlets in SqlServer PowerShell module. API is delivered with a ruleset that is highly customizable and extensible. It can be used to assess SQL Server versions 2012 and higher, both on Windows and Linux.
Read on for instructions on how to install and what has changed since public preview 1.
Comments closedJeff Pries takes us through the Power BI API with Powershell:
The Power BI API is a way of essentially bypassing the web interface of powerbi.com and asking questions directly to the back-end of the service (without bypassing security). Using this allows you to issue a command, such as “Give me a list of all of the workspaces” and receive just the result data in a bulk data format. While outside the scope of this exercise, the API also allows reading actual business data from Power BI assets and even manipulating assets.
For my first attempt at interacting with the API, I decided to try the Microsoft developed Powershell Cmdlets. These are a set of free Powershell commands which can be installed on your workstation and then will query the Power BI API and return results in a standard way without the need to write any code (C# or otherwise) or understand how to read a JSON response.
Jeff does more than just hit API endpoints, though, so read the whole thing.
Comments closed