A Fine Slice Of SQL Server
Aaron Nelson announces a new feature in Azure Data Studio:
In order to get all the nice intellisense and tab completion features of the PowerShell language inside your PowerShell Notebooks, be sure to install the PowerShell extension from the Azure Data Studio marketplace.
At this point, the biggest remaining language is R, though I’d love to see F# support as well (hey, Azure Notebooks offers F# support).
Comments closedEbru Ersan announces the general availability of the SQL Assessment API in Powershell:
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, as well as Azure SQL DB Managed Instance. More products will be supported in future releases.
SQL Assessment API is shipped as part of SqlServer PowerShell module (21.1.18206) and SMO NuGet Package (150.18208.0).
It’s a set of reasonably good practices and also lets you customize for your own environment, so check it out.
Comments closedFor moving Logins/Users, Microsoft provided revlogin script which made it easy for migration of logins without need to know about passwords. But, there is no easy approach for migration LinkedServers with the actual password. This is where dbatools cmdlet Copy-DbaLinkedServer becomes very handy. But, what about the situation where we have to script out LinkedServer beforehand.
For this reason, based on the blog post of Antti Rantasaari, and using his code as the base script, I have created a cmdlet Get-LinkedServer with SQLDBATools module which accepts SqlInstance name as a parameter along with -ScriptOut switch, and gives Drop/Create statements for linked servers present on that local/remote SqlInstance.
As a quick note, SQLDBATools is not the same as dbatools.
Comments closedDavid 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 closed