You can see the name, the current value and the description
Ah thats cool he said so
How Do I Know Which Configuration Is For Which Check?
Well, you just…. , you know…… AHHHHHHH
Rob then made this possible. Click through to see how you can determine which configuration works for which checks.
Values that you need to change in this request are:
name – name of your Azure SQL Managed Instance (don’t include domain).
properties/administratorLogin – SQL login that will be used to connect to the instance.
properties/subnetId – Azure identifier of the subnet where Azure SQL Managed Instance should be placed. Make sure that you properlyconfigure network for Azure SQL Managed Instance
location – one of the valid location for Azure data centers, for example: “westcentralus”
sku/name: GP_Gen4 or GP_Gen5
properties/vCores: Number of cores that should be assigned to your instance. Values can be 8, 16, or 24 if you select GP_Gen4 sku name, or 8, 16, 24, 32, or 40 if you select GP_Gen5.
properties/storageSizeInGB: Maximum storage space for your instance. It should be multiple of 32GB.
properties/licenceType: Choose BasePrice if you don’t have SQL Server on-premises licence that you want to use, or LicenceIncluded if you can have discount for your on-premises licence.
tags(optional) – optionally put some key:value pairs that you would use to categorize instance.
Click through for the template and a quick Powershell script which shows how to use the template.
I want to show the two modules running against a number of SQL Versions so I have installed
- 2 Domain Controllers
- 2 SQL 2017 instances on Windows 2016 with an Availability Group and WideWorldImporters database
- 1 Windows 2016 jump box with all the programmes I need
- 1 Windows 2016 with containers
using a VSTS build and this set of ARM templates and scripts
I wanted to create containers running SQL2017, SQL2016, SQL2014 and SQL2012 and restore versions of the AdventureWorks database onto each one.
Rob shows how to do this all via Powershell so you can automate the process.
Of course, PowerShell excels at this. By using the SQL Server module, it’s really easy to:
- Connect to an instance and collect every user database, and
- From each database, collect every table, and
- For each table, collect row counts and space used, and
- If there are any indexes, group them, and sum their usage and report that as well
Here’s the script. Note that I have the server name hard-coded in there as localhost (more on that in a coming paragraph). Go ahead and take a look before we break it down.
Click through for the script, and homework is due next Tuesday on his desk.
Been working on monitoring. For some reason, when you tell Aurora to send errorlogs to Cloudwatch, all it sends are the Audit Logs, which will tell you that code had changed, etc, but doesn’t (!?!?!??!!!) actually put your logs in Cloudwatch. I don’t understand it, so I built this process to look through logs and return the data. The next step would be to format it and either upload to Cloudwatch manually, or log it, or send email.
Click through for the script.
First, let me say that this person knows that
Select-Objectcan be used to select the properties we want, so he tried to guess the property name using a trial/error approach.
The person tried:Get-Service WinRM | Select-Object Startup, Status, Name, DisplayName
and also:Get-Service WinRM | Select-Object StartupType, Status, Name, DisplayName
But all of them were just empty.
There is a better way.
If you are using Docker for Windows and want to switch between Linux or Windows containers you can do this by right clicking the Docker “Whale” in the systray and selecting “Switch to Windows containers”:
….but no one likes clicking around do they!
There is an alternative way to do this which I use in my docker session demo’s which makes things so much easier and the switch is a lot quicker!
Click through for the Powershell call, which has the added benefit of being scriptable.
This is just great on its own as I get information shown below. It is basically data like this at the Database/FileGroup/File levels
Free of Max size
Size as a percent at the instance level
Free size as a percent at the instance level
Max size as a percent at the instance level
Free of Max size as a percent at the instance level
Read on to see how Jana makes use of this data, as well as where you can get the code.
What you’ll notice is that several of the queries are filtering on the FirstName column. There’s no good index there. If you look at the execution plans for those queries you’ll also note the Missing Index suggestion. That suggestion is a necessary part of the automatic indexing. Yeah, missing indexes. I know. They’re not always accurate. It’s just a suggestion. Blah, blah, blah. I hear you.
The magic is not supplied by missing indexes. The magic is supplied by lots of data. Microsoft can take advantage of three things. Yes, missing index suggestions is first. Then, they can use the query metrics gathered in Query Store to see the behavior of your queries over time. Finally, they can use machine learning algorithms to determine if indexes will be helpful and measure how helpful they’ve been if one gets added. It’s great stuff. Go and read on it.
Click through for more notes, as well as a Powershell script you can use to replicate his findings.
SQL Search is doing exactly what it’s designed to do here: it’s finding every object that matches that string, HumanResources. It’s unfortunate that it also happens to be the name of my linked server and a schema in my database, but such is life, right? Its returning every object it hits a match on, which includes a bunch of views, plus one of those views actually contains my linked server reference.
I love SQL Search
Let me be clear: I’m not here to gang up on the fine folks at Redgate. This tool is beautiful and I love it. Otherwise, how else could we quickly search for objects in our databases? The alterative would be either querying system views for object definitions or using cursors to call sp_helptext over and over, and then trying to do pattern matching. Same as what SQL Search does.
There’s no easy way to sort this wheat from chaff, is there? This might be a starting point; let’s narrow down the search the objects we might need to look at. Then, we’ll manually script each one out, one at a time. That sounds an awful lot like a manual process. “If only there was a way to automate this checking”, he asked, sarcastically.
Click through for a very interesting cmdlet.