A Fine Slice Of SQL Server
Pain Point: Something bad happened and you need to restore a SQL Server database.
Pain Point you didn’t know you had: The backup files are all corrupt due a problem with the storage subsystem.
A backup is only as good as the last time you tested its restoration. Kevin shows just how easy it is to perform this test using DBATools.
Comments closedI ran into a concern to quickly audit all current users and role members for a set of Azure SQL databases, spread across multiple resource groups. Without an easy CMS concept or a way to quickly loop through an unknown set of servers, resource groups, and databases, that can be a little challenging. I have an account to use that should have access to all databases (but doesn’t) so put together some PowerShell that I could run locally to get that information and send the results to Excel.
Click through for a SQL script to get the data and a Powershell script to run this for each database and export the results into different tabs in Excel.
Comments closedJose Manuel Jurado Diaz wants to know when the Query Store state changes:
This morning, I have been working on a support case where our client was not able to see certain queries when querying the Query Data Store. We have observed that the cause is due to the Query Data Store changing to a read-only mode due to the volume of data and the limitation our client had on the QDS database space. Therefore, I would like to share the following PowerShell script that can be executed at regular intervals to check and retrieve when the state of QDS has changed. Unfortunately, in Azure SQL Database, we cannot use the Extended Event ‘qds.query_store_db_diagnostics’.
Click through to see Jose’s alternative solution.
Comments closedPatrick Gruenauer does two things at once:
In this blog post, I’d like to give you a few examples related to PowerShell Background Jobs to build upon. Let’s jump in.
Let’s say I want to ping a few computers. This consumes time. So I want that this task runs in the background as a PowerShell background job.
Outside of practicing for a certification, I don’t remember the last time I willingly chose to run something as a background job, either in Powershell or bash. The concept is still useful (especially if I’m on an SSH connection or have direct terminal access), though in a UI-driven world, I’d just open a new terminal tab.
Comments closedPatrick Gruenauer checks download speed:
Short explanation: A file will be downloaded from my homepage. It’s a video about Powershell Profiles in German language. During the download of the file the process is measured with the Measure-Command cmdlet. At the end we get the final result and the time it took to download the file.
You can, of course, change the file location to whatever makes sense, but it’s nice to have something handy and not need to go to any websites for a speed test.
Comments closedJose Manuel Jurado Diaz has a script for us:
Today, we encountered an interesting service request of attempting to reduce the load times for 100,000 records from a table with 97 varchar(320) fields in an Azure SQL HyperScale database. Following, I would like to share my lessons learned here.
The idea is to split in different concurrent process the execution of multiples SqlBulkCopy. In this case, we are going to split this process in 5 processes running in parallel inserting 20,000 rows, let’s try to know the total size.
Read on for the script, as well as a rough idea of how long it’ll take inserting into an Azure SQL DB Hyperscale instance.
Comments closedJose Manuel Jurado Diaz has a script for us:
We often encounter support cases where our customers leave the query execution timeout value at its default. In certain situations, we may find that when this value is reached, the application reports an error and does not continue. In this case, I would like to share an example implemented in PowerShell that allows incrementing the command timeout value up to 5 attempts, with a 5-second margin in each operation.
My recommendation here would be to use Polly instead, as it built in support for exponential backoff and more complex retry logic. Adam Driscoll has a Powershell wrapper called pspolly, which may also prove helpful.
Comments closedGianluca Sartori takes us through the process of signing our own executables:
Why does happen with some files and doesn’t happen with the Chrome installer or Acrobat reader? Because those setup kits are signed with a certificate from Google and Adobe, released by a certification authority that checks that Google is actually Google and not a disgruntled random guy pretending to be Google.
This means you can sign your code too, you just need to get a code signing certificate from a certification authority and they will be happy to give you one in exchange for money.
Click through for the step-by-step demonstration and a Powershell script to perform the signing.
Comments closedTracy Boggiano reminds us that we still need to administer Azure SQL DB databases:
I’ve been using Azure SQL Database for quite some while and have set up it in many various ways to run Ola’s Index Optimize and Statistics Updates on them. All of these have seemed way too complicated probably because I was setting them up once, not again for several more months or a year. Well with my new job, I have over 20 subscriptions with various Azure SQL Servers in them so it was time to streamline at least knowing what I was doing. No matter what I googled on the Internet I never did find one source that walked me step by step on each thing I needed to know to set this up. So hopefully this will cover everything.
Click through for the step-by-step process.
Comments closedJeffery Hicks looks at an enumeration:
Earlier this Spring, I released a new function called Get-TypeMember. The function is in the PSScriptTools module. I wrote about that release here. This command is an alternative to Get-Member that includes more details. The other day I pushed a new version that makes it easier to identify properties that are enumerations.
Click through to see how it works.
Comments closed