February 14, 2013 at 2:57 am
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
February 14, 2013 at 4:13 am
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.
February 15, 2013 at 2:32 am
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.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply