A Fine Slice Of SQL Server
Rob Sewell announces updates in the dbachecks Powershell package:
Today we updated the HADR tests to add the capability to test multiple availability groups and fix a couple of bugs
Once you have installed dbachecks you will need to set some configuration so that you can perform the tests. You can see all of the configuration items and their values using
Read on for more about these updates.
Comments closedShane O’Neill shows when you need to use single quotes when splatting in Powershell:
If you’ve checked the examples in that post – and I recomment that you do – then you’ll see that it takes the syntax of
Parameter = 'Value'.Notice the Parameter portion is not in quotes? It also works perfectly well if you have the Parameter name in quotes e.g.
'Parameter' = 'Value'(double quotes works too).Why would you use one instead of the other?
There is a special circle in the Inferno for people who put spaces in their parameter names.
Comments closedDan Blank lays down some information about saving headerless CSV files in Powershell:
Anyway, a question was posted recently using the #pshelp hashtag on Twitter. How do you replace the header line of multiple csv files at once?
I saw this and maybe cockily thought to myself, “that’s got to be an easy 2 liner, bet I can boost my ego and quickly write out a solution”.
Spoilers: it turned out to be slightly more complex.
Comments closedChrissy LeMaire shows off a very helpful command in dbatools:
Nearly every time I inherit a SQL Server environment, I’m only given a partial list of SQL Servers that exist on the network. It’s my usual routine to get permission to sniff the network then run about five different programs including Idera’s SQL Discovery and Microsoft’s SQL Server Assessment and Planning Toolkit.
I always thought it’d be cool to have one comprehensive PowerShell command that could do the work of all the above and was ecstatic to see NetSPI’s Scott Sutherland had written a few commands to do just that in his awesome PowerShell module PowerUpSQL.
When I saw Scott’s multi-pronged approach (including some UDP magic 🎩), I asked if he’d be interested in contributing to dbatools and he said yes! He submitted a gorgeous mock-up and I was so excited. Then came the PR, complete with great documentation and multithreading.
Click through for a lot more information on the command.
Comments closedArun Sirpal shows how to unpause an Azure SQL Data Warehouse instance using Powershell:
I totally forgot that with Azure SQL DWH you can pause and resume compute, to save money because it is expensive. Question is how do you go about resuming compute? TSQL is not possible and sure you can do the change via Azure portal but what about PowerShell?
This makes it easy to script out an overnight data load and then pausing the Azure Data Warehouse until the morning when those analysts come in, so that you can save a bit of cash (or a lot, depending upon your DWU utilization).
Comments closedShane O’Neill makes a discovery:
…if there is enough data to import this into the database & use T-SQL then you can bet that’s what I’m going to do! It’s what it was designed for, I’d find it easier, and it’s probably going to be faster after you hit a certain threshold.
However, if it’s small sets and the effort of importing the data is going to slow you down and break your flow…
Well, that doesn’t have to be the case anymore.
Read on to see joins in action.
Comments closedAs an example, you may have a block of SQL that PRINTs out the current privileges in the databasethat can then be saved off and used as an independent script.
In my case today, I need to collect information on the Service Pack and Cumulative Updates that need to be installed/applied to 200+ SQL Server instances. MS provides the SQL script to identify the right SP and CU to install. Please make sure you download the zip file and unzip the long SQL. I need to run this SQL against the instances to get the info. However, the information returned is completely with PRINT statements!
Click through to see how Jana did it.
Comments closedShane O’Neill reminds us that reading is fundamental:
Glancing at the error message, the first things that stick out are the bits “{x,y}” so I change my regex to be anywhere from 1 to digits
"*\d{1,6}$"Why are you glancing, read the error message!
That doesn’t work, so I again quickly scan the error message and see the bit “following nothing”
“Quickly scan”?! No, actually read the error message!!
This wasn’t a great error message, but at least it does make sense after the fact and it’s a case where actually reading the error message does clarify things. The ones I really hate are “error 0x4849f8f8” types which aren’t even intended to make any sense to us.
Comments closedNow that there are no connections we can move the database. Depending on the situation it might be worth setting the database to read only or single user mode first. In my case, I had the application taken down so I felt confident no connections would be coming in.
With one line of code we can select the source and destination servers, the database name, specify that we want to use the backup and restore method, and then provide the path to a file share that both instance service accounts have access to:
The whole process is just five lines of code, so it could hardly be easier.
Comments closedMark Broadbent has a quick Powershell script to read a selection from a giant text file:
I recently ran into this very same problem in which the bcp error message stated:
An error occurred while processing the file “D:\MyBigFeedFile.txt” on data row 123798766555678.
Given that the text file was far in excess of notepad++ limits (or even easy human interaction usability limits), I decided not to waste my time trying to find an editor that could cope and simply looked for a way to pull out a batch of lines from within the file programmatically to compare the good rows with the bad.
Click through for a precise and useful Powershell snippet. The .NET file stream libraries are also good for this kind of thing.
Comments closed