Managed Instance – SQL Studies

What’s in a SQL Server Managed Instance?

Kenneth Fisher — Thu, 03 Aug 2023 13:00:00 +0000

My current position involves a lot of work with SQL Server Managed Instances so you’ll probably be hearing a lot about them from me. At least for a while. Prior to this 95%+ of my experience has been on-premises and I’d always figured that SQL Server is SQL Server. It turns out there’s a lot more that’s different than I expected. I mean a lot of it’s the same. If you are an on-premises DBA, you’ll be able to manage a MI (managed instance) pretty easily and pick up on the major differences quickly enough. For example, did you know that xp_cmdshell isn’t available on an MI? I mean it’s for obvious reasons, but if you are used to it it can still be a bit of a blow.

The other day while doing a bit of research for a blog post I came across this little gem: T-SQL differences between SQL Server & Azure SQL Managed Instance. Just skimming it (all I’ve had time for so far) it’s got a lot of great information and even more links back to additional information.

Now because I’m not always the brightest bulb in the pack I lost the link. But! While I was looking for it I found this page: Features comparison: Azure SQL Database and Azure SQL Managed Instance which is very similar but at a higher level and linked back to the previous link and a bunch more.

It’s always nice when you know something pretty well to be able to get a list of just the differences/new stuff to learn. And, yes, I know SQL Homework was a couple of days ago but these links were just too good not to share.

SQL Agent job history on a Managed Instance

Kenneth Fisher — Tue, 25 Jul 2023 13:00:00 +0000

The defaults for saving SQL Agent Job history are ok (at best), so you should probably check and update them if needed. Sadly, if you are using a Managed Instance this isn’t an option.

SQL Managed Instance currently doesn’t allow you to change any SQL Agent properties because they are stored in the underlying registry values.

Now this might change in the future, but I wouldn’t hold my breath. Fortunately, Jovan Popovic on Microsoft’s Techcommunity site gave what I thought was a really cool solution. He provides the code to convert the table msdb.dbo.sysjobhistory into a temporal table to store all of the history into a separate table called msdb.dbo.sysjobhistoryall. I highly recommend going and reading his post but just in case you don’t want to here is the basic code he recommends using.

ALTER TABLE [msdb].[dbo].[sysjobhistory] 
ADD StartTime DATETIME2 NOT NULL DEFAULT ('19000101 00:00:00.0000000') 
GO 

ALTER TABLE [msdb].[dbo].[sysjobhistory] 
ADD EndTime DATETIME2 NOT NULL DEFAULT ('99991231 23:59:59.9999999') 
GO 

ALTER TABLE [msdb].[dbo].[sysjobhistory] 
ADD PERIOD FOR SYSTEM_TIME (StartTime, EndTime) 
GO 

ALTER TABLE [msdb].[dbo].[sysjobhistory] 
ADD CONSTRAINT PK_sysjobhistory PRIMARY KEY (instance_id, job_id, step_id) 
GO 

ALTER TABLE [msdb].[dbo].[sysjobhistory] 
SET(SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[sysjobhistoryall], DATA_CONSISTENCY_CHECK = ON, HISTORY_RETENTION_PERIOD = 1 MONTH)) 
GO

To encourage you to go to his post I’m going to mention that in the comments there is a link to a SSMS report someone wrote to more easily read the information out of the table. I had to make a minor change to the history table it pulled from but it works pretty well.

#PowershellBasics: Warnings and how to hide them.

Kenneth Fisher — Thu, 08 Dec 2022 14:00:00 +0000

One of my favorite parts of having a new job is that I learn a ton. (No really, my brain feels like there is a ton of information just sitting on it and compressing it into a little brain shaped ball.) Right now I’m doing a lot of Powershell and since Powershell is fairly new to me I’m learning quite a bit. Today I was working with Get-AzSqlInstance (used to get a list of Managed Instances). As it turns out there is a breaking change coming up and Microsoft has been kind enough to warn me about it every time I run it.

WARNING: Upcoming breaking changes in the cmdlet ‘Get-AzSqlInstance’ :

The output type ‘Microsoft.Azure.Commands.Sql.ManagedInstance.Model.AzureSqlManagedInstanceModel’ is changing

The following properties in the output type are being deprecated : ‘BackupStorageRedundancy’

The following properties are being added to the output type : ‘CurrentBackupStorageRedundancy’ ‘RequestedBackupStorageRedundancy’

The change is expected to take effect from the version : ‘4.0.0’
Note : Go to https://aka.ms/azps-changewarnings for steps to suppress this breaking change warning, and other information on breaking changes in Azure PowerShell.

Ok, great! Good information. Not something that affects me right now but still helpful to know. And I really appreciate the fact that going forward I can expect this type of information.

I found this information less useful and less appreciated after I put it into a loop and it ran ~40 times in a row, hiding any real information beneath a pile of warnings. Fortunately, right there in the warning there is a helpful note on how to suppress it :). Fair warning that link takes you to a Github repository. Regardless, it suggests running this piece of code to turn off the warnings.

Update-AzConfig -DisplayBreakingChangeWarning $false

Yay! Now I can run my script without all of those annoying messages. That’s fantastic right? Well .. yes and no. It’s nice for right now, but those warnings are important. What if one comes across for another command and is actually going to break my code? Unfortunately, I don’t see a way to say Yes, I’ve seen this warning, thank you for letting me know, and you can stop showing me this particular warning for this particular command. So personally, as soon as I am done developing this piece of code (the warning won’t matter once it goes into automation) I’m going to turn the breaking change warnings back on again.