Planning to set up an Always On Availability Group configuration to move data data from a 2012 primary replica into a 2014 “readable” secondary replica? Think it twice!

I had a customer who wanted to use AOAG, not as a HA/DR solution but as one to replicate data to another instance to which they planned to offload all read-only workload. The instance hosting the secondary replica of the database was expected to run a new set of business processes which consumed data from the tables in the read-only replicated database, and whose pattern (that of some of those processes) would greatly benefit from having the possibility to use some memory optimized tables stored in a separate local database in that same instance (and maybe some natively stored procedures as well.) That would require them to run SQL Server 2014 Enterprise Edition on the instance hosting the secondary replica.

Now, the primary replica was running SQL Server 2012 Enterprise Edition. It was hosting several databases whose contents were accessed by a server product which supported SQL Server 2012 but, unfortunately, didn’t support SQL Server 2014 yet. That was an impediment which required them to have the instance hosting the primary replica running SQL Server 2012.

Since they didn’t ever plan to failover the availability group to the secondary, and in the event they accidentally did so they were fully aware of the consequences such action would imply, their question was whether or not such AOAG configuration would be one we support as a long term solution.

Every piece of documentation we had where mixed versions configurations were discussed, always referred to them in the context of a rolling upgrade. However, there was no topic in BOLs, KB article, post in the AlwaysON MSDN’s blog, or other authoritative source where we either stated that such setup was not supported as a permanent solution.

It happens that such configuration is not supported because it is possible to have a secondary replica running on a higher version than the primary, and it can even be configured for readable access, but in practice it cannot be accessed for read only until it has been “promoted” to primary. It’s only at that point when the metadata and physical structures in its files are upgraded to be at the level that version of the engine can work with.

Before having done so (ie having upgraded the version of the database) any attempt to startup the database via a USE statement or a reference to an object in that database from the context of a different database, would fail with error 946 (Cannot open database '%.*ls' version %d. Upgrade the database to the latest version), or a 18456 (Login failed for user '%.*ls'.%.*ls%.*ls) with 18337 (Reason: Failed to open the explicitly specified database '%.*ls'. ) for its reason when attempted via a login with explicit initial database specified in the login payload.

So, a doable alternative for what this customer required, could be setting up transactional replication to move data from the 2012 instance into the 2014 instance.