Press "Enter" to skip to content

Category: Administration

Connecting a SQL Server Instance to Azure Arc

Published 2023-07-21 by Kevin Feasel

Deepthi Goguri has a guide:

When you install SQL Server 2022 through the GUI, you will see an option in the features “SQL Server Extention for Azure”

This is more of a “how” than a “why.” Azure Arc-enabled SQL Server instances let you use Azure’s control plane (their graphs and some configuration options) to manage SQL Server instances, regardless of whether they’re actually in Azure or on-premises. That way, a DBA with one foot in both camps can have a consistent administrative experience for things like inventorying SQL Server instances.

Comments closed

Migrating Column-Level Encryption to Azure SQL MI

Published 2023-07-19 by Kevin Feasel

Keshav Kiran performs a migration:

One of our customers came up with a requirement where they wanted to Migrate On-prem Database to Azure SQL Managed instance. The databases had traditional column level encryption enabled.

He has restored the database on the SQL Managed instance by Backup/Restore approach. Now when he was trying to read the encrypted column on the destination database, It was showing NULL values after decryption.

Read on for the solution.

Comments closed

Automating Database Copy in Azure SQL Managed Instance

Published 2023-07-18 by Kevin Feasel

Sasa Popovic creates some clones:

Database copy and database move operations for Azure SQL Managed Instance are very convenient in various situations when you want to copy or move database from one managed instance to another in an online way. What does online mean in this context? It means that the database on destination managed instance will be identical to the source database at the moment when operation is explicitly completed by user action. Copying a database is a size of data operation, and you can expect copy will take some time, but what is important and convenient, unlike point in-time restore where database is in state from some point in time in the past, with database copy you get database in state as it was when the operation was completed.

Read on to see how you can set this up for an Azure SQL Managed Instance.

Comments closed

Service Broker and the Unstoppable SQL Server Instance

Published 2023-07-10 by Kevin Feasel

Sean Gallardy shuts it all down:

I was sent a quite an interesting email stating that SQL Server would not shut down. Attempting to stop the service via services or the SQL Server Configuration Manager resulted in a timeout with SQL Server still running. Trying to execute the shutdown with and without NOWAIT T-SQL command resulted in the same, the process still running. Seems quite weird that SQL Server just refuses to shutdown!

Click through for Sean’s investigations, what the result was, and how the customer ultimately decided to deal with it.

Comments closed

Log Analysis by Hand in Postgres

Published 2023-07-10 by Kevin Feasel

Laetitia Avrot looks at the logs:

If you’re one of my customers, you might know how I insist on monitoring your Postgres logs and digging into them to find precious insights on what’s going on with your Postgres. For a long time now, there is pgBadger. For PGSQL Phriday #010, Alicja asks us to focus on pgBadger.

You might be surprised to find out I am not using pgBadger. I will explain why later, but keep assured that I do think pgBadger is a good tool. It will help DBAs get better performance and follow how their instance is doing before there is a very bad problem.

Click through for Laetitia’s reasons as well as an alternative way of analyzing log files.

Comments closed

Analyzing Postgres Logs with pgbadger

Published 2023-07-10 by Kevin Feasel

Anthony Nowocien takes us through a useful tool:

This week, #PGSQLPhriday is hosted by Alicja Kucharczyk. Every month, one community member proposes a new subject to this monthly blogging event and let the world (or your family/friends/neighbors if you prefer) know all about pgBadger. It’s a tool to analyze your PostgreSQL logs and present you a nice web report.

If you like some history, it has been developed by Gilles Darold since more than 11 years, as v1.0 came along on June the 10th in 2012. At this time, pgfouine was the main log analyzer and the complete Perl rewrite was greatly performance influenced. In V4, it started to have its current look, by embarking the Bootstrap library and fonts.

Click through to see what pgbadger does and an example of how it makes log analysis understandable.

Comments closed

Impact of and Limitations to Parameter Tuning in Postgres

Published 2023-07-07 by Kevin Feasel

Henrietta Dombrovskaya wraps up a series on PostgreSQL parameters:

In this first blog, we didn’t provide any examples of the practical impact of parameters tuning on performance. Indeed, it is challenging to model such an impact on the training database. 

In this blog, we will segue from discussing PostgreSQL system parameters best practices to other ways of performance tuning. Moreover, we will demonstrate that essential database performance tuning goes beyond choosing the appropriate parameters settings.

Click through for that post.

Comments closed

model_msdb and model_replicatedmaster Databases

Published 2023-07-05 by Kevin Feasel

Sean Gallardy says hands off:

There was a question that came up on the MCM mailing list about what are these files: model_msdbdata.mdf, model_msdblog.ldf, model_replicatedmaster.mdf, model_replicatedmaster.ldf? It was pointed out that the files don’t show up in the typical DMVs and seem to not really be used at the moment.

These are the databases used as the basis for the system databases in contained availability groups. Definitely don’t touch them, just leave them be, even if you aren’t using availability groups (let alone contained).

Click through for more information on how you can find them and another warning not to mess with them.

Comments closed

Last Page Insert Contention in SQL Server

Published 2023-07-05 by Kevin Feasel

Eitan Blumin spots a wild Latch Convoy:

The “Last Page Insert Contention” in SQL Server, also known as “Latch Convoy Problem“, also known as “PageLatchEx Contention” is one of those extremely rare use cases that are very difficult to see in real-world scenarios.

Evidently, it was impactful enough that Microsoft implemented a solution for this problem back in SQL Server 2019 in the form of the new OPTIMIZE_FOR_SEQUENTIAL_KEY index option, which reportedly fixes it.

Click through to learn more about a scenario in which Eitan saw this in the wild. In fairness, I’m not sure I’d do any better at realizing that this was a last page insert contention problem.

Comments closed