Author: Admin

In SQL 2016 you might have wondered about the strange new setting when adding a new subscription:

This is the option for “Memory Optimised” tables and applies to Transactional and Snapshot subscriptions only. Memory Optimised tables are a newish (SQL 2014 and 2016) design feature which can be really useful for reporting purposes, and this fits in nicely with the use of many Transactional Replication systems, so it’s definitely something worth taking a look. As an additional bonus in SQL 2016 it’s also available in any edition.

Just a quick clarification that we are talking about replication subscribers here, not publishers. We can’t replicate memory optimised tables from the publisher – we can only create them at the subscriber.

First of all I’ll mention that selecting the option above when adding a subscription on its own does nothing! You’ll just get a normal table on the subscriber and we need to do a little more to get this to work.

You need to take a look at each table in the publication and set the article properties there. The 2 options you’ll need are shown below.

If you set “Enable Memory Optimisation” to true and leave the second option as false which is the default, most likely this will fail. This is because usually Primary Keys are created with a clustered index but Memory Optimised tables don’t play happily with clustered indexes. If your primary keys have a non-clustered index and there are no other clustered indexes on the table you’ll be ok, but this is a pretty unlikely scenario. Anyway, the wizard will check for you and you’ll get the following error if you’ve omitted the second option and it was required.

Really there’s no reason I can think of to leave the second option set to false.

So, having set the article properties and the subscription properties we are now almost ready to roll.

One final check – is the subscriber database set up to accommodate memory optimised tables? You can take a look in SSMS or run the following query replacing the “Northwind” with your database name to see:

select * from  [Northwind].sys.filegroups where type = ‘FX’

If nothing is returned, adapt and run the following replacing “Northwind” with your database name:

ALTER DATABASE northwind ADD FILEGROUP northwind_mod CONTAINS MEMORY_OPTIMIZED_DATA
ALTER DATABASE northwind ADD FILE (name=’northwind_mod2′, filename=’c:\data\northwind_mod2′) TO FILEGROUP northwind_mod
ALTER DATABASE northwind SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=ON

At this stage we are good to go. Create the subscription and after initialization run the following at the subscriber to check for any memory optimised tables.

SELECT OBJECT_NAME(object_id) as TableName, * FROM sys.dm_db_xtp_table_memory_stats;  

All being well you’ll get a row returned like the following and we’re done :).

 

Well strictly speaking this is a post to remind you to not accept the defaults when using continuous schedules. Although the problem I’ll describe never arises when you use a repeating schedule.

So – first of all are you running the distribution agent continuously? Remember back to when you set up the subscription there was an option mentioning this…

Can’t remember? Just take a look at the job schedule of the Distribution Agent. You’ll see the description will tell you “Start automatically when SQL Server Agent starts”.

This means a continuous schedule. It is the default and therefore is often the one selected. Also it provides the lowest latency so it seems like the obvious choice. However what if you ever hit a data consistency error where someone has messed up the subscriber data. It happens! If the replication command cannot be applied to the subscriber the job runs, retries, retries, retries and so on. No error is reported. Things just seem to hang. Perhaps it is the report user who tells us that the data seems stale, but being professionals we should know this before them!

The setting we need to be aware of is the one on the job step which runs the distribution agent. It is set to retry indefinitely by default. Well actually 2147483647 times but this amounts to indefinitely. Pretty soon the subscriber will default and have to be reinitialized if we don’t spot that there is a problem.

How does this look with a repeating schedule? Is it any different? Here it is:

Far more reasonable and after 10 mins we have our error, receive a pagerduty alert and then connect up and sort out the problem. Why is the number of retries different here compared to that set in a continuous schedule? Absolutely no reason I know of, and every system I’ve worked on I reset them as soon as I realise people are using continuous.

So – either reset to something reasonable (10 seems ok) or use a repeating schedule.

Let’s consider what happens when transactional replication fails. Say there’s been a dodgy release and the application is now pointing at a replication subscriber where data changes have happened. Realising the problem, the application is re-pointed at the publisher database and when we start the distribution agent our problems begin to surface. We’ll see some info about retrying commands and after 10 retries the distribution agent will fail. I’m assuming you have the distribution agent on a timed schedule and there’ll be another post to explain why this is a good idea :). Failed inserts and updates are the usual issues we’ll see. This has happened to me in a production environment and there are a few ways of dealing with it:

(1) reinitialize. Most safe option but not always possible in production until we can negotiate some downtime. Sometimes we just need to get things working.

(2) use sp_setsubscriptionxactseqno. We just need to get hold of the transaction number (xact_seqno) of the most recent failing command and tell the distribution agent to skip it and move to the next command. We could use the -Output parameter in the distribution agent job (see here). This used to be a common way of getting xact_seqno numbers but the sp_helpsubscriptionerrors stored proc is much easier:

sp_helpsubscriptionerrors @publisher = ‘DESKTOP-NSONKMO’,
@publisher_db = ‘testtran’,
@publication = ‘ptesttran’,
@subscriber = ‘DESKTOP-NSONKMO\PAULSINST’,
@subscriber_db = ‘testtran’

The proc returns enough info to help us understand what has happened eg “Violation of PRIMARY KEY constraint ‘PK_tRegion’. Cannot insert duplicate key in object ‘dbo.tRegion’. The duplicate key value is (3).”. The main thing we need though is the xact_seqno which is also returned.

Then we run the following stored procedure at the Subscriber on the subscription database:

sp_setsubscriptionxactseqno @publisher = ‘DESKTOP-NSONKMO’
, @publisher_db = ‘testtran’
, @publication = ‘ptesttran’
, @xact_seqno = 0x00000022000005FF000300000000

Note that this can be a pain if there are a lot of errors. Each time you’ll skip the last tried command and then find the next xact_seqno and then skip that one. If there are a lot of errors you could be doing this for a while.

(3) we can set the distribution agent profile to skip all these type of data consistency errors – missing rows and already existing rows.

In fact this is what the previous admin had set as a default profile on the system I was looking at. Most often it is the fastest way to get things up and running and it avoids the iterative approach of sp_setsubscriptionxactseqno.

The issue I have is once this is set, there is no error recorded in the distribution agent while it happily skips commands which don’t work.

We should of course run regular validations, but in the meanwhile, if you are using this profile, what you need is a separate job which alerts you when commands have been skipped, and then you can take action. Just create a regular job which checks for consistency errors and sends the email if any are found. The syntax for the check is below:

SELECT id, time, error_text ,
error_code
FROM MSrepl_errors msre
WHERE msre.id IN (SELECT error_id
FROM MSdistribution_history)
and error_code in (‘2601′,’2627′,’20598’)
ORDER by msre.time desc

MSrepl_errors.error_code is of type sysname and may contain non-integer values. (for example: ‘HYT00′ and ’08S01′). So your query should specify the error values as string literals to avoid error:

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the nvarchar value ’08S01’ to data type int.