Sql server transactional replication queued updating
An availability group is self-contained such that all the databases to be replicated through the Availability Group are included as one unit.
Many enterprise customers have asked the capability to combine the usage of SQL Server replication and Always On, such that they can place replication distribution databases within an Always On AG to achieve high availability for their distribution databases, with the expectation that after doing so and when AG failover happens, SQL Server replication will continue functioning seamlessly and correctly.
We do come across different deployments of the publisher and subscriber which are a mix of standalone instances, SQL Server failover cluster instances or Always On Availability Group replica instances.
Depending on the deployment pattern, the upgrade path to SQL Server 2016 would be different. SQL Server offers two upgrade paths in general: The scenarios below apply to Transactional Replication (without P2P Replication, Queued Updating Subscription and Immediate Updating Subscription) and Merge Replication.
SQL Server Replication provides multi-faceted data movement capabilities across SQL Server releases which has been used by customers across the globe for a large number of years.
When moving from one major release of SQL Server to another, replication topology upgrade has been a constant topic of lengthy discussions.
The assumption here is that the edition of the SQL Server instance will not change and a failover cluster instance of SQL Server will be upgraded to a failover cluster instance where as a standalone instance will be upgraded to a standalone instance using the steps mentioned below. This will allow you to take a phased approach, reduce risk and minimize downtime.
Upgrading a replication topology is a multi-step process.
A common approach that has been adopted for side-by-side upgrades of replication topologies is to move publisher-subscriber pairs in parts to the new side-by-side environment as opposed to a movement of the entire topology.
We have setup a replication using 'transactional with queued updating'.
If the publisher database crashes and some records in the subscriber database have not been replicated to the publisher database (and in the publisher database backup), how would we have to re-sync the data after restoring the publisher database and setting up replication without losing the new data in the subscriber database? Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. See the How to Ask page for help clarifying this question.
Side-by-side upgrade will require the upgrade of subscriber to happen together with the publisher and requires a re-initialization of the subscribers. In-place upgrade (Requires publisher to be upgraded also because subscriber and publisher need to be within two major releases.
A SQL Server 2008/R2 publisher cannot have a SQL Server 2016 subscriber.) Intermediate in-place upgrade to SQL Server 2012/2014 for the subscriber which is acting as the distributor also The publisher can then be upgraded to SQL Server 2016 post this intermediate distributor upgrade : In-place upgrade would need to occur for both publisher and subscriber at the same time as publisher and subscriber need to be within two major releases.
Side-by-side upgrade requires re-setup of all the publisher/subscriber pairs in the replication topology served by this distributor*.