Press "Enter" to skip to content

Category: Administration

Finding your SQL Server Product Key

Published 2022-01-19 by Kevin Feasel

I share a tale of woe:

I was working on an upgrade recently, trying to move from SQL Server 2016 to SQL Server 2019. I wanted to perform an upgrade in-place on an Azure VM, but needed to get the product key. There are a few places where you can find Powershell functions to get this product key, with Ryan @ Ryadel’s post being the most well-known. That method covers versions from SQL Server 2005 through 2014 (if you follow the notes in the blog post), but breaks on 2016.

Click through for a script which works for SQL Server 2016 and later.

Comments closed

The Benefits of Indirect Checkpoints

Published 2022-01-19 by Kevin Feasel

Aaron Bertrand explains why it’s probably a good idea to enable indirect checkpoints:

At Stack Overflow, the upgrade to SQL Server 2019 was not as smooth as expected. It introduced long recovery times and high CPU on a secondary, during synchronization activity after failover, patching, and network connectivity testing. This symptom wasn’t present during similar activities and workloads under SQL Server 2017.

Aaron points out that if you have databased created in older versions of SQL Server, enabling indirect checkpoints can provide a performance boost to certain activities around log writing., including backup times.

Comments closed

Automating Azure SQL DB Maintenance with Data Factory

Published 2022-01-18 by Kevin Feasel

Hiten Bhavsar works around the lack of SQL Agent:

As we know, it’s crucial that you run Database maintenance regularly in order to keep your database performance up with the latest statistics and healthy indexes, here we provide another way to schedule this job using Azure Data Factory; this can be done on a scheduled time interval weekly/bi-weekly/monthly.

Click through for the process and try not to think too hard about this secretly being maintenance plans all over again.

Comments closed

Cannot Open User Default Database

Published 2022-01-17 by Kevin Feasel

David Alcock backs out of a problem:

This error isn’t to do with my login as such, it’s still there with sysadmin role membership so I don’t have to do anything too drastic like restarting SQL Server with the -m or -f startup parameters and recreate it. The error message is telling me that my logins default database cannot be opened, which is more than likely because I’ve deleted it.

Click through to see how David got out of this issue. This is a big part of why I highly prefer not to change the default database from master for logins..

Comments closed

Testing Failover Group and TCP Connectivity with Managed Instances

Published 2022-01-14 by Kevin Feasel

Niko Neugebauer has a pair of connectivity tests for us. First up is failover group connectivity:

When you set up a failover group between primary and secondary SQL Managed Instances in two different regions, each instance is isolated using an independent virtual network. Replication traffic needs to be allowed between these VNets.

To allow this kind of traffic, one of the prerequisites is:

– “You need to set up your Network Security Groups (NSG) such that ports 5022 and the range 11000-11999 are open inbound and outbound for connections from the subnet of the other managed instance. This is to allow replication traffic between the instances.”

Click through for a SQL Agent job script which helps with the test. Meanwhile, you can also test TCP connectivity from a managed instance:

In this post we shall focus on helping you determining the TCP connectivity from SQL Managed Instance against a given endpoint and port of your choice.

If you are interested in other posts on how-to discover different aspects of SQL MI – please visit the  http://aka.ms/sqlmi-howto, which serves as a placeholder for the series.

There are scenarios where it would be nice to be able to test if a SQL Managed Instance can reach some “external” endpoints, like Azure Storage as an example.

Check out both posts.

Comments closed

Thoughts on Page Life Expectancy

Published 2022-01-11 by Kevin Feasel

Chad Callihan gives us a few high-level thoughts on page life expectancy and the buffer pool:

The buffer pool in SQL Server is an area in memory for caching data. Once data is read from disk, it can be kept in the buffer pool and SQL Server can check here for data when it needs to be found in the future. If requested data is not found in the buffer pool, a hard page fault can occur, meaning data needs retrieved from disk. It’s possible that data is found somewhere else still in memory which is called a soft page fault.

Click through for Chad’s thoughts on what a good page life expectancy looks like. My minor addition is that the number isn’t as important as the shape of the curve: if you have a fairly stable PLE above some arbitrary threshold (well above 300 seconds!), you’re probably in good shape. If your PLE sawtooths, your server’s RAM Pez dispenser needs refilled.

Comments closed

Finding Buffer Pool Distribution by Table

Published 2022-01-11 by Kevin Feasel

Guy Glantser has a script to track buffer pool size by table:

Sometimes we need to troubleshoot memory pressure issues in SQL Server or in Azure SQL. One of the things that can help in these cases is to view the contents of the buffer pool.

I wrote a simple script that displays the contents of the buffer pool in terms of tables in the current database. For each table, it presents the total table space and the space consumed by the table in the buffer pool. The script is based mainly on the sys.dm_os_buffer_descriptors dynamic management view.

Click through for the script.

Comments closed