Goodbye JSON Since Microsoft released bicep version 0.3 I thought I’d give it a try because anyone that’s authored ARM templates will know, they tend to make your eyes bleed after a while. Bicep aims to drastically simplify the authoring experience with a cleaner syntax, improved type safety, and better support for modularity and code re-use. I’m sold! I reverse-engineered an existing simple template that deploys two storage accounts by using bicep decompile and got a template like this: ...

Should we stop using -- in SQL comments? Interesting blog post from Brent Ozar on whether we should stop using -- for SQL Comments. I use these comments a lot because they are quick and easy for me to type. I don’t like voluminous comments but this is a good point. If clicking the link is too much effort for you, he is saying you should do this SELECT * FROM dbo.Users WHERE DisplayName = N'Brent Ozar' /* This line is a comment */ AND Location = N'Iceland' ORDER BY Reputation DESC; instead of this ...

Found an error in Microsoft documentation? Submit a pull request! Much of the Microsoft documentation is open source on GitHub which means that if you spot an error in either the code examples, the grammar, spelling, a typo or copy-and-paste error, you can quickly fix it and people reading the docs after you will benefit. This is such a difference from when I started using Microsoft products back in the 90s where nothing was open source and all documentation was either in a printed manual or a chm file. ...

Maintenance Window now in Preview (at the time of writing) On 2nd March 2021, Microsoft announced a long-awaited feature, at least by me, to give choice over when OS and SQL patching occurs behind-the-scenes in Azure SQL Managed Instance. Before this feature, Microsoft would patch the Virtual Cluster supporting Managed Instances outside of regular business hours in the region your Managed Instance is located. You now get two additional options: Weekday window, 10PM to 6AM local time Monday – Thursday Weekend window, 10PM to 6AM local time Friday - Sunday System default: ...

The problem I came across an interesting (!) issue with an Azure Sql Managed Instance today. One of our users came across this error when trying to SELECT from a view in a database where they are not a member of the db_owner database role or any elevated server-level permission by right clicking the view in SSMS 18.8 and choosing Select Top 1000 Rows. Select Top 1000 Rows in SSMS EXECUTE denied on xp_instance_reg_read in mssqlsystemresource The full text of the error is The EXECUTE permission was denied on the object 'xp_instance_regread', database 'mssqlsystemresource', schema 'sys'. I verified that this was indeed the case with: ...

Today, the Az.Sql module would not load on my machine when attempting to manually fail over a SQL Managed Instance with this error: module could not be loaded I followed the advice of the error message and tried to import the module to get further information. module is already loaded Hmmm :thinking: I decided to uninstall the Azure PowerShell from my machine using the Windows 10 Settings App. Looks like an old version. ...

Hugo is great Even though I’m late to the party, having not blogged for a while (see my older posts here), I came across a bug in Hugo today. To get a little bit more performance from my site I decided to use the --minify option in the hugo command for the production build on netlify’s build server in netlify.toml as you can see here. My navbar didn’t have icons in it before, and I thought it would look nicer with some icons added. On my local server it looked fine: ...

Built with Hugo I’ve moved away from Wordpress as it was getting bloated, complicated to manage and expensive to host. I wanted something that was fast easy to use cheap to host source controlled I can now write blog posts in markdown in vscode and then push my changes to GitHub which lets me have a history, I can edit from anywhere by pulling and pushing from any machine, even my phone. ...

On a client site recently a question was asked about the file share witness in a SQL Server failover cluster on-premises, and where to put it if you only have two sites. As always, it depends! Let’s look at some scenarios. Bear in mind that use of the Azure Cloud Witness requires Windows Server 2016 or later. Topology 1 Three node cluster with 2 nodes at primary and 1 at disaster recovery (DR) Most people want high availability at their “primary site” and are happy to have standalone capability at the business continuity (DR) site. To save storage costs, I would recommend a SQL Server failover clustered instance so you only have one set of storage at the primary site. On top of that you can place an availability group in asynchronous mode to a standalone server at your DR site. In this configuration there are several scenarios to consider, and they can all be served with a file share witness at the primary site, or use a cloud witness in azure. ...

If you have an environment where you need to lock down Sentry One as much as possible, then this article should help. It is well known that the Sentry One service account needs to be a member of the Local Administrators group in each server it monitors, and also a member of the sysadmin role for each SQL Server instance. At the moment this is still a requirement, but if you can live without the Windows metrics, then you could run Sentry One in Limited Mode which will only gather SQL Server specific metrics. Some of our clients run in this configuration for security reasons. ...