SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Change Publisher in a transactionnal Replication


Change Publisher in a transactionnal Replication

Author
Message
tutupouet
tutupouet
SSC-Enthusiastic
SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)

Group: General Forum Members
Points: 124 Visits: 278
Hello,

I must change one of our SQL Servers (SQL Server 2008 SP3) and I want to take the opportunity to migrate to SQL Server 2012 SP1.
So I will create a new instance and backup/restore the databases.

The problem is that this server is a publisher and I want to know if there is a way to do this without generate snapshots after the recreation of the publications because subscribers are already up to date and there are big databases so the initial snapshots and the replication will take a long time.

P.S.:The new server will not have the same name.
thx
Suresh B.
Suresh B.
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1928 Visits: 5326
You can initialize a subscription with a backup.
BOL:
Initializing a subscription with a backup
A backup contains an entire database; therefore each subscription database will contain a complete copy of the publication database when it is initialized:

The backup includes tables not specified as articles for the publication.

The backup includes all data, even if row or column filters are specified on a table.

It is the responsibility of the administrator or application to remove any unwanted objects or data after the backup has been restored. In subsequent synchronizations, data changes are only replicated if they apply to tables specified as articles, and the changes meet any filtering criteria you specified.

Note:
When restoring a backup, you must ensure that the backup came from the Publisher if you want the Subscriber to automatically synchronize. The log sequence number (LSN) values in the backup (which are used to set the point at which to start synchronizing) are specific to the Publisher.

To initialize a subscription with a backup

To initialize a subscription with a backup, you first must enable the option when you create a publication, and then specify values for a number of options when you create a subscription. Publications can be enabled through the New Publication Wizard or programmatically. However, the values required for the subscription options can only be specified programmatically.

SQL Server Management Studio: How to: Enable Initialization with a Backup for Transactional Publications (SQL Server Management Studio)

Replication Transact-SQL programming: How to: Initialize a Transactional Subscription from a Backup (Replication Transact-SQL Programming)

Note:
If a subscription is initialized without using a snapshot, the account under which the SQL Server service runs at the Publisher must have write permissions on the snapshot folder at the Distributor. For more information about permissions, see Replication Agent Security Model.

Ensuring the suitability of a backup
A backup is suitable for initializing a Subscriber if all transactions that occur after the backup was taken are stored at the Distributor. Replication will display an error message if the backup is not suitable.

To help ensure that a backup is suitable for use, follow these guidelines:

Use the latest backup available, and if the latest backup is older than the maximum distribution retention period, create a new backup before attempting to initialize a subscription with a backup. For more information about retention period, see Subscription Expiration and Deactivation.

By default, the distribution cleanup job clears transactions older than 72 hours from the distribution database. Cleanup is based on the retention period set for the publication. When synchronizing with older backups, consider temporarily disabling the job before the backup you would like to restore and re-enabling it after the subscription is successfully created. This prevents removal of transactions from the distribution database that might be needed to synchronize successfully from the backup. For information about running cleanup jobs, see How to: Run Replication Maintenance Jobs (SQL Server Management Studio).

In some cases you must manually perform customizations in the restored Subscriber database after setting up subscriptions that are initialized with a backup. In general, manual modifications at the restored Subscriber database are required if the Publication is defined in such a way that the Subscriber database content is expected to be different from the Publisher database content.

Indexed-views at the restored database have to be converted to tables if they are published as log-based indexed-view-to-table articles

Subscribed timestamp columns at the restored database must be converted to binary(8) columns: copy the content of the tables containing timestamp columns to new tables with matching schemas except having binary(8) columns in place of the timestamp columns, drop the original tables, and rename the new tables with the same names as the original tables.

tutupouet
tutupouet
SSC-Enthusiastic
SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)

Group: General Forum Members
Points: 124 Visits: 278
Hello,

thanks for your reply but what I really like is doing nothing on the subscriber, as if for him nothing had happened on the publisher.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search