Backup Compression for TDE Databases

Backup Compression and TDE were both released as SQL Server 2008, but they were not work so well together, the backup compression ratio for TDE enabled database was nearly nothing. SQL Server 2016 has a hidden gem that easily be overlooked in the SQL Server BOL BACKUP SQL documentation, saying "Starting with SQL Server 2016 (13.x), setting MAXTRANSFERSIZE larger than 65536 (64 KB) enables an optimized compression algorithm for Transparent Data Encryption (TDE) encrypted databases that first decrypts a page, compresses it, and then encrypts it again. If MAXTRANSFERSIZE is not specified, or if MAXTRANSFERSIZE = 65536 (64 KB) is used, backup compression with TDE encrypted databases directly compresses the encrypted pages, and may not yield good compression ratios.". In this blog post, let's do experiment on backup a TDE enabled database, to compare the differences between the default (no compression), specified WITH COMPRESSION but no max transfer size, and WITH COMPRESSION plus MAXTRANSFERSIZE greater than 64KB.

Here I have a TDE encrypted database, data size 30GB, in a SQL Server 2016 instance.

Backup with default option (uncompressed)

Backup with COMPRESSION option but no max transfer size specified

 
Backup with COMPRESSION and MAXTRANSFERSIZE greater than 64kb

As you can see, only the BACKUP WITH COMPRESSION and MAXTRANSFERSIZE > 64kb can really compress the backup.

Good news:
Starting with SQL Server 2019 (15.x) CU5, setting MAXTRANSFERSIZE is no longer required to enable this optimized compression algorithm with TDE. If the backup command is specified WITH COMPRESSION or the backup compression default server configuration is set to 1, MAXTRANSFERSIZE will automatically be increased to 128K to enable the optimized algorithm.

Resumable Online Index Operations

Starting from SQL Server 2017, a great feature was introduced, called Resumable Online Index Operation, which allows online index rebuild can be paused and resumed. And SQL Server 2019 enhanced this by allowing online index creation resumable. In this blogpost, let's have a deep dive into how to use this new indexing options, and attentions you need to take when using it. Here I use the SQL Server 2019 Developer edition and StackOverflow2010 database to do the demo.

Create an new index with RESUMABLE = ON
CREATE INDEX IX_Votes_UserId ON Votes (UserId) WITH (ONLINE = ON, RESUMABLE = ON);

Check the current execution status for resumable index operation
A new DMV sys.index_resumable_operations let you to check the status and progress for resumable online operations in the current database.

Pause the index operation
ALTER INDEX IX_Votes_UserId ON Votes PAUSE;

Can log space be reclaimed by log backup when resumable index operation paused?
YES!

Resume the paused index operation
ALTER INDEX IX_Votes_UserId ON Votes RESUME;

What happened if the index operation being KILL?
It will become PAUSED if the index operation being killed.

How to cancel the index operation
ALTER INDEX IX_Posts_ParentId ON Posts ABORT;

From the above experiment, we can see that being able to pause and resume your online index operations allows you a way to do these operations in a piecemeal approach, and more importantly allow you to free up transaction log space during the index operation still undergoing.

Read-Only Routing of Availability Group

One of the main benefits of SQL Server AlwaysOn Availability Group is being able to scale out read-only workload to secondary replicas. By default, Read-Only Routing is not automatically enabled when you build your availability group which most of you done by SQL Server Management Studio GUI. Read-only routing can only be configured using Transact-SQL or PowerShell command. In this blog post I'm going to demonstrate how to configure read-only routing using Transact-SQL.
Let's say you already configured an availability group using the GUI wizard in SSMS. In this example I have two availability replicas.

1. Execute ALTER AVAILABILITY GROUP MODIFY REPLICA statements in order to allow secondary role read-only connections and specify read-only routing URL for each replica:

2. For each replica that you want to support read-only routing when it is the primary replica, you need to specify a read-only routing list. A given read-only routing list takes effect only when the local replica is running under the primary role:

3. Execute below query to verify the read-only routing list was set properly:
SELECT   AVGSrc.replica_server_name AS SourceReplica
 , AVGRepl.replica_server_name AS ReadOnlyReplica
 , AVGRepl.read_only_routing_url AS RoutingURL
 , AVGRL.routing_priority AS RoutingPriority
 FROM sys.availability_read_only_routing_lists AVGRL
 INNER JOIN sys.availability_replicas AVGSrc ON AVGRL.replica_id = AVGSrc.replica_id
 INNER JOIN sys.availability_replicas AVGRepl ON AVGRL.read_only_replica_id = AVGRepl.replica_id
 INNER JOIN sys.availability_groups AV ON AV.group_id = AVGSrc.group_id
 ORDER BY SourceReplica;

4. Test read-only routing using SQLCMD with the –K readonly parameter, along with the listener name and the database name in the availability group. The output shows the secondary replica receiving read connections according to read-only routing list:

Replication Error – Cannot execute as the database principal because the principal “dbo” does not exist

Today I was called by the operation team, said that a drive in a database server running SQL Server going to be out of free space. I found a database that its transaction log was much bigger than how it should be. The scheduled transaction log backup was fine (I'm using the Ola's maintenance solution), and no any active transactions that preventing log truncation was found by the DBCC OPENTRAN() command running in the context of that database. However, DBCC OPENTRAN told me that the database has a publication of transactional replication configured. Then I queried the log_reuse_wait_desc column of the sys.databases system catalog view, which told me that the reuse of transaction log space was waiting on REPLICATION. Then I figured out it was the replication log reader agent got some problem. Below screenshot shows the error message of the log reader agent, by checking from the Replication Monitor > right-click that Log Reader Agent > View Details:

The error message was "Cannot execute as the database principal because the principal “dbo” does not exist, this type of principal cannot be impersonated, or you do not have permission". This message is quite misleading, but the root cause is the database owner of the publication database is invalid, which is mostly due to the database was created by a login and then the login was being removed. In order to solve it, the easiest solution is to set sa as the database owner, no matter you are just enabled Windows authentication or the sa login is disabled, you can still use the sa login as a valid database owner. It can be set in the database property file tab:

Now the replication resumed running normally. But if there are too many non-distributed replication records, drop and create the publication and subscription again may be a faster alternative, and that's why you must always remember to generate creation script for all new and changed publications.

Checking Availability Group synchronization performance

One of the most important aspects of a successful deployment of SQL Server Availability Group is the synchronization speed of the secondary replica with the primary replica fulfills your production application performance requirement. Your application will be slowed down if a secondary replica with synchronous commit mode is lagging behind the primary replica. Also if any one of the secondary replicas far behind from the primary replica, transaction log reuse will be hindered, which makes the transaction log file keeps growing until disk full and the database becomes non-updatable. You can check the transaction log reuse wait by querying the sys.databases DMV's log_reuse_wait column.

The easiest way to check the status of AG is through the built-in dashboard in SSMS, you can open it by expanding AlwaysOn High Availability folder in the SSMS Object Explorer > Availability Groups > right-click the AG group > Show Dashboard. The default layout of the dashboard doesn't provide a lot of details, but you can add additional details into the layout through the Add/Remove Columns link on the dashboard, as shown below:

The description of these columns in the dashboard can be checked in the online documentation sys.dm_hadr_database_replica_states DMV. Below are some useful columns that I always add:

• log_send_queue_size : Amount of log records of the primary database that has not been sent to the secondary databases, in kilobytes (KB).
• log_send_rate : Average rate at which primary replica instance sent data during last active period, in kilobytes (KB)/second.
• redo_queue_size: Amount of log records in the log files of the secondary replica that has not yet been redone, in kilobytes (KB).
• redo_rate : Average Rate at which the log records are being redone on a given secondary database, in kilobytes (KB)/second.

 

Cross Domain SQL Server Replication

Setting up replication with publisher on one domain and subscriber on another domain is tricky. This blog post discuss issues encountered and the steps to overcome them.

When you try to add subscription by specifying the FQDN of the subscriber server, you will get an error "Sql Server replication requires the actual server name to make a connection to the server" as shown below.

As you see on the SSMS object explorer in the above picture, the subscriber server actually can be reached from the publisher server by specifying the FQDN, but the subscription wizard denied you to create it. In order to overcome it, you need to create SQL Server Alias in the configuration manager. Alias provides alternate name to be used to connect to the target SQL Server. Note that SSMS is running in 32-bit, while most of the SQL Servers nowadays are in 64-bit version. Replication agent process is called from SQL Server agent, so the replication process run in 64-bit. So, we need to setup an alias in both 32-bit and 64-bit SQL Native Client configuration.

After the server aliases defined, you should able to add subscriber by specifying the server alias.

How to get the service account by using T-SQL

You have the sysadmin privilege in the SQL Server instance, for some reason you need to check the service accounts executing the database engine, agent, and other services. All we know you can get the answer from SQL Server Configuration Manager, but you need to login the Windows OS in the database server first, which is little bit inconvenient. Even more it's the system engineer team in charge of maintaining Windows server, not you, and they don't let you login the OS. Since SQL Server 2008R2 SP1 we have a documented DMV sys.dm_server_services gives us information about the SQL Server, Full-Text, SQL Server Launchpad service, and SQL Server Agent services in the current instance of SQL Server.

As you can see in the result, this DMV also returns the startup type, running status, and other useful information about the services.