SQLTeam.com | Weblogs | Forums

Replicating a compressed table

sql2016

#1

Has anyone replicated a compressed table before? I'm add a table to an existing publication and the snapshot agent is generating an error I've not seen before. I'm wondering if it's because the table is compressed. The publisher and distributor are SQL Server 2016 but the subscriber is SQL Server 2014. And the published database is on an availability group but that's worked fine so far.

The error I get is below and hasn't been very helpful. I'm debating between removing compression or just calling Microsoft.

Error messages:
Message: The subscription status of the object could not be changed. 
Command Text: sp_MSactivate_auto_sub
Parameters: 	@publication = MyPublicationName
	@article = %
	@status = initiated

Stack:    at Microsoft.SqlServer.Replication.AgentCore.ReMapSqlException(SqlException e, SqlCommand command)
   at Microsoft.SqlServer.Replication.AgentCore.ExecuteWithResults(CommandSetupDelegate commandSetupDelegate, ProcessResultsDelegate processResultsDelegate, Int32 queryTimeout)
   at Microsoft.SqlServer.Replication.Snapshot.TransSnapshotProvider.SetPublisherTranSequenceNumViaAutoSub(PublicationActivationState publicationActivationState, SqlConnection connection)
   at Microsoft.SqlServer.Replication.Snapshot.TransSnapshotProvider.ActivateCSSPublicationAndSetTranSequenceNums(PublicationActivationState publicationActivationState, SqlConnection connection)
   at Microsoft.SqlServer.Replication.RetryableSqlServerTransactionManager.ExecuteTransaction(Boolean bLeaveTransactionOpen)
   at Microsoft.SqlServer.Replication.Snapshot.TransSnapshotProvider.DoConcurrentPreArticleFilesGenerationProcessing()
   at Microsoft.SqlServer.Replication.Snapshot.TransSnapshotProvider.DoPreArticleFilesGenerationProcessing()
   at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.GenerateSnapshot()
   at Microsoft.SqlServer.Replication.SnapshotGenerationAgent.InternalRun()
   at Microsoft.SqlServer.Replication.AgentCore.Run() (Source: MSSQLServer, Error number: 14068)
Get help: http://help/14068
Server MyAGName, Level 16, State 1, Procedure sp_MSrepl_changesubstatus, Line 1394
The subscription status of the object could not be changed. (Source: MSSQLServer, Error number: 14068)
Get help: http://help/14068

#2

That I understand! but I'm struggling to understand why a table being compressed would make any difference (but, yeah, sure, until you know the cause it could be anything)

A google turned up (other than "Drop the subscription and recreate it" - which is presumably "onerous")

"I found out that it will only work if you log onto the sever using the server name rather than an ip address"


#3

It turns out the compression of the table doesn't matter. I think something is broken deep inside my replication. I guess it's time to open a ticket. Ugh.