SQL Server index rebuid online and blocking scenario

Mis en avant

A couple of months ago, I experienced a problem about index rebuild online operation on SQL Server. In short, the operation was supposed to be online and to never block concurrent queries. But in fact, it was not the case (or to be more precise, it was partially the case) and to make the scenario more complex, we experienced different behaviors regarding the context. Let’s start the story with the initial context: in my company, we usually go through continuous deployment including SQL modification scripts and because we usually rely on daily pipeline, we must ensure related SQL operations are not too disruptive to avoid impacting the user experience.

Lire la suite

Monitoring Azure SQL Databases with Azure Monitor and Automation

Mis en avant

Supervising Cloud Infrastructure is an important aspect of Cloud administration and Azure SQL Databases are no exception. This is something we are continuously improving at my company.

On-prem, DBAs often rely on well-established products but with Cloud-based architectures, often implemented through DevOps projects and developers, monitoring should be been redefined and include some new topics as:

Lire la suite

AAD user creation on behalf AAD Service Principal with Azure SQL DB

Mis en avant

An interesting improvement was announced by the SQL AAD team on Monday 27th July 2020 and concerns the support for Azure AD user creation on behalf of Azure AD Applications for Azure SQL as mentioned to this Microsoft blog post.

Lire la suite

Dealing with SQL Server on Linux on WSL2

Mis en avant

This is a blog post I intended to write sometime ago … about using SQL Server on Linux on WSL2. For whom already installed it on Windows 10, version 2004, you are already aware it doesn’t come with the support of systemd. Indeed, although it does exist in the file system, systemd is not running. If you intend to use directly SQL Server on WSL2, you need to read this carefully, because installation and management relies precisely on systemd!

Lire la suite

Universal usage of NVARCHAR type and performance impact

Mis en avant

A couple of weeks, I read an article from Brent Ozar about using NVARCHAR as a universal parameter. It was a good reminder and from my experience, I confirm this habit has never been a good idea. Although it depends on the context, chances are you will almost find an exception that proves the rule.

Lire la suite

dbachecks and AlwaysOn availability group checks

Mis en avant

When I started my DBA position in my new company, I was looking for a tool that was able to check periodically the SQL Server database environments for several reasons. First, as DBA one of my main concern is about maintaining and keeping the different mssql environments well-configured against an initial standard. It is also worth noting I’m not the only person to interact with databases and anyone in my team, which is member of sysadmin server role as well, is able to change any server-level configuration settings at any moment. In this case, chances are that having environments shifting from our initial standard over the time and my team and I need to keep confident by checking periodically the current mssql environment configurations, be alerting if configuration drifts exist and obviously fix it as faster as possible.

Lire la suite

SQL Server on Linux and new FUA support for XFS filesystem

Mis en avant

I wrote a (dbi services) blog post concerning Linux and SQL Server IO behavior changes before and after SQL Server 2017 CU6. Now, I was looking forward seeing some new improvements with Force Unit Access (FUA) that was implemented with Linux XFS enhancements since the Linux Kernel 4.18.

Lire la suite

Database maintenance thoughts with Azure SQL databases

Mis en avant

As DBA, your priority is to ensure your data are consistent, safely backed up and you get steady performance of your database. In on-prem environments, these tasks are generally performed through scheduled jobs including backups, check integrity and index / statistics maintenance tasks.

But moving databases to the cloud in Azure (and others) tells a different story. Indeed, even if the same concern and tasks remain, some of them are under the responsibility of the Cloud provider and some other ones not. If you’re working with Azure SQL databases – like me – some questions raise very quickly on this topic and it was my motivation to write this write-up. I would like to share with you some new experiences by digging into the different maintenance items. If you have a different story to tell, please feel free to comment and to share your own experience!

Lire la suite

Availability Group 2017 Direct seeding and updated default path policy

Mis en avant

A couple of days ago, I ran into an issue when adding a new database in direct seeding mode that led me to reconsider refreshing my skills on this feature. Going through the AG database wizard for adding database, I faced the following error message …

Lire la suite

Mitigating Scalar UDF’s procedural code performance with SQL 2019 and Scalar UDF Inlining capabilities

Mis en avant

A couple of days ago, I read the write-up of my former colleague @FranckPachot about refactoring procedural code to SQL. This is recurrent subject in the database world and I was interested in transposing this article to SQL Server because it was about refactoring a Scalar-Valued function to a SQL view. The latter one is a great alternative when it comes performance but something new was shipped with SQL Server 2019 and could address (or at least could mitigate) this recurrent scenario.

Lire la suite