A Fine Slice Of SQL Server
Our decision to move some (not all) SQL databases to AWS Aurora was partially a cost decision, but also an effort to simplify operational management by diversifying our database platforms. Now that second reason might seem counter-intuitive, but here are some details about the situation and how we came to our decision:
Read on for Jamie’s thoughts and tips to make the migration process easier.
Comments closedNithya Bondalapati notes a discrepancy:
ARM (Azure Resource Manager) API does not support specific T-SQL resource naming formats and that’s why when Databases are created using T-SQL/SSMS, unsupported characters could get allowed into the names.
Azure Portal UI does not have this issue, because when you use Portal UI for creating a Database, the creation goes through ARM, and hence when unsupported characters are entered in the Name field, it immediately throws error, as shown in the below image.
None of the restrictions or onerous, but Nithya explains what actions fail or don’t quite work right if you have a database with special characters or ending in a period or space.
Comments closedChad Callihan explains a feature:
If you’re running into problems caused by concurrent inserts and you’re on SQL Server 2019 or above, it’s worth testing out OPTIMIZE_FOR_SEQUENTIAL_KEY. This may alleviate those issues by handling inserts a little bit differently to reduce contention. OPTIMIZE_FOR_SEQUENTIAL_KEY will “control the rate at which new threads are allowed to request the latch, and favor threads that are likely to keep the throughput high.”
Read on to see how you can enable it but also when it makes sense to enable it.
Comments closedReza Rad shares some recommendations with us:
Power BI workspaces are not like the old days when we had Edit access and View access only. You have more options for roles in a workspace, and in my courses, I have found that many people have chosen the incorrect role without knowing what the role does. In this article, I’ll explain all the roles in the workspace, and what is the best way to set them up to have a secure workspace.
Click through for the article, as well as an accompanying video. Or a video and an accompanying article, if that’s how you roll.
Comments closedMarc Lelijveld looks at an update to Power BI:
Back in August 2022, Microsoft released a feature to have multiple audiences for Power BI Apps, or if you will deploying multiple versions of the same app where you can actively show and hide content for different audiences. A very welcome feature that has a big impact on the number of workspaces people require to have to reach the same goals. In this blog, I will elaborate on the impact of multiple audiences for Power BI Apps, as well as the impact on workspace governance.
Read on for Marc’s thoughts on the topic.
Comments closedDenny Cherry notes that the DNS server is in another castle:
If you have an Azure environment when you have your DNS servers in a separate vNet from your new AKS environment you’ll notice that you get an error when deploying the AKS environment which looks something like this.Agents are unable to resolve Kubernetes API server name. It’s likely custom DNS server is not correctly configured, please see https://aka.ms/aks/private-cluster#hub-and-spoke-with-custom-dns for more information.
The fix for this is actually pretty straightforward, but I’m going to give you a little background on why this happens.
Click through for the answer.
Comments closedBen Miller begins a series on Ola Hallengren’s maintenance solution:
I recommended creating a database to use for this solution or even installing it into an existing DBA function database. I usually create a DBA database and use it for this purpose and others as well. With this new database, you configure the Database in the header of the maintenance solution SQL file, whether to create jobs, retention time and backup directory for the jobs.
This first post acts as a primer for those who might have the solution but haven’t investigated it in any detail.
Comments closedEmin Atac gets a scary message:
When you type the following
Get-EventLog-SourceMicrosoft-Windows-Kernel-General-Newest20-LogNameSystem-InstanceId1 | Select-ExpandPropertyMessageYou get
Possible detection of CVE: 2023-01-09T09:08:23.5000000Z
Additional Information: 2023-01-08T19:56:29.1492612Z
This Event is generated when an attempt to exploit a known vulnerability (2023-01-09T09:08:23.5000000Z) is detected.
This Event is raised by a User mode process.
Read on to learn what this error message means, why it pops up, and what you can do to avoid it in the future.
Comments closedAndrew Pruski talks snapshots:
When we talk about snapshots of SQL Server there are two types, application consistent snapshots and crash consistent snapshots.
Application consistent snapshots require freezing IO on a database allowing for a checkpoint to be performed in order to write all dirty pages to disk.
In the past, application consistent snapshots relied on the third party software to call the SQL Writer service but now with SQL Server 2022 T-SQL snapshot backups we have the ability to use T-SQL commands to freeze IO on a database in order for us to take an application consistent snapshot of the database.
Read on for more detail on the topic. With SQL Server 2022, I’ve softened a bit on using snapshots for data retention—prior to that, I was not a fan of the idea.
Comments closedRobert Haas doesn’t wear a cape:
As I’ve written about before, a PostgreSQL superuser always has the ability to take over the operating system account in which PostgreSQL is running, but sometimes you’d like to have a role that can administer the database but not break out of it. In existing releases, there’s no good way to accomplish that. You can either make a new role so weak that it can’t perform ordinary administration tasks, or you can make it so strong that it can easily break into the operating system account and thus take over the superuser role as well. Unless you hack the source code, which some people have done, there’s no real way to set up an account that has enough power to usefully administer the database in meaningful ways but yet not enough power to take over everything. I’ve committed a number of patches to v16 to try to improve the situation, and I think that we can look forward to big improvements in this area once it is released.
Read on for some of the implications of this change.
Comments closed