Potential data latency with AlwaysOn Availability Groups and Memory Optimized Tables

Today I was testing a scenario with a readable secondary in an Availability Group (AG). The database in the AG contained a memory-optimized table and I was testing read-only query support on the secondary.

The mystery

While changes for on-disk tables are replicated near-instantaneously (of course in my test setup there was no other overhead) and were almost immediately visible on the secondary replica, I was surprised to see that it was not the case with the memory-optimized table.

For example, I would insert a row into the table at the primary replica. That row would not appear in the readable secondary, not even after waiting a few seconds. Note that there was no other workload related to memory optimized tables at the time.

Interestingly, if I would insert a new row in the table on the primary, it would cause the previously inserted row to show up at the secondary! Smile I was quite puzzled to see this behavior. On a hunch, I issued a manual CHECKPOINT on the primary. This ‘flushed’ the queue and I was able to see both rows on the secondary.

Truth revealed!

Since this was a bit strange, I dug around a bit and found a gem in the documentation which explains this behavior. There is something called a ‘safe timestamp’ which is used on the readable secondary to only return rows older than a ‘safe point’ which is normally updated by the garbage collection thread on the primary.

Normally on an active system, this safe timestamp would be periodically changed and periodically sent across to the secondary on a regular basis. But if there is not much activity on memory optimized tables in the database (like in my case) then there is an inordinate delay in sending this safe timestamp across.

Conclusion

If such data latency for in-memory tables is not tolerable on the readable secondary, the product team recommends the usage of ‘dummy’ transactions to propagate this safe timestamp more periodically:

Changes made by transactions on the primary replica since the last safe-timestamp update are not visible on the secondary replica till the next transmission and update of the safe-timestamp. If transactional activity on the primary replica stops before the internal threshold for safe-timestamp update is crossed, the changes made since the last update to safe-timestamp will not be visible on the secondary replica. To alleviate this issue, you may need to run a few DML transactions on a dummy durable memory-optimized table on the primary replica. Alternatively, though not recommended, you can force shipping of safe-timestamp by running a manual checkpoint.

Be aware of this behavior if you plan to use readable secondary and have some phases where there is minimal activity in the system but you still need the changes to in-memory tables to show up on the secondary as soon as possible.

NUL or NULL?

Ever since the old MS-DOS days, the Windows OS family has supported a special ‘null device’ – NUL. Notice the single ‘L’. This is a ‘reserved’ device identifier which you can use as a path. The special thing about this null device (NUL) is that it essentially swallows whatever is sent to it.

NUL and SQL

So what does this have to do with let’s say SQL? Many of us use the null device to test raw backup throughput. The correct way to do this is to specify DISK = ‘NUL’ in the backup command. In that case, you will see an entry similar to the below in the error log, and no physical destination file will be created:

2014-09-20 21:47:43.890 Backup       Database backed up. Database: master, creation date(time): 2014/09/18(16:33:26), pages dumped: 459, first LSN: 234:304:73, last LSN: 234:352:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {‘nul’}). This is an informational message only. No user action is required.

Now, in some cases, you might accidentally or unknowingly type in NULL (with the double L) instead of NUL. What that does is actually end up with a physical file called NULL Smile You can see proof of that here:

2014-09-20 21:47:03.480 Backup       Database backed up. Database: master, creation date(time): 2014/09/18(16:33:26), pages dumped: 460, first LSN: 234:208:128, last LSN: 234:280:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {‘C:\Program Files\Microsoft SQL Server\MSSQL12.SQL14\MSSQL\Backup\null’}). This is an informational message only. No user action is required.

Conclusion

So if you are using the NUL device in your tests, make sure you spell it as NUL (without the double L!)

Have a good weekend!

Strange issue when enabling AlwaysOn AG feature in SQL 2014

Today I ran into an issue, which eventually turned out to be a really silly thing, but I think it is important that I share the details.

I was trying to install SQL 2014 side-by-side with an already existing SQL 2012 AG setup. Everything went fine till the customary step when I tried to ‘tick the box’ and enable the AG feature for the newly installed SQL 2014 instances. But that was not to be, with the following error dialog (“The AlwaysOn Availability Groups feature requires the x86(non-WOW) or x64 Enterprise Edition of SQL Server 2012 (or later version) running on Windows Server 2008 (or later version) with WSFC hotfix KB 2494036 installed.)

image

The hotfix in question was already installed and most importantly, my SQL 2012 AG was already functional on this cluster. So it was definitely not something with the OS or cluster.

Not to be deterred, I used the PowerShell option, but that failed as well:

PS SQLSERVER:SQLSQL108W2K8R22SQL14> Enable-SqlAlwaysOn
Enable-SqlAlwaysOn : Could not obtain information about SQL Server Service ‘MSSQL$SQL14’.
At line:1 char:19
+ Enable-SqlAlwaysOn <<<<
    + CategoryInfo          : ResourceUnavailable: (MSSQL$SQL14:String) [Enable-SqlAlwaysOn], SqlPowerShellServiceException
    + FullyQualifiedErrorId : ServiceError,Microsoft.SqlServer.Management.PowerShell.Hadr.EnableSqlHADRServiceCommand

Providence!

Suddenly it dawned upon me that maybe in my haste I had installed an edition of SQL 2014 which did not permit the AG feature. And indeed, it was the Business Intelligence edition that I had installed, which does not permit the usage of AGs.

The cool thing though about SQL 2008 and above is the “Edition Upgrade” feature of the setup, which allows you to easily change editions (provided the upgrade path is allowed – for example you cannot ‘upgrade’ from BI edition to Evaluation) just by entering the correct product key.

The whole edition upgrade thing took just under a minute and after that I was able to successfully enable the SQL 2014 installation for AlwaysOn AGs.

Hope this tip helps someone!