SQL Server Replication upgrade practices - choose optimal upgrade options
SQL Server Upgrade task needs a thorough planning and testing before you deploy the process in production environment. In this regard when performing an upgrade to a replicated environment, DBAs must make sure that all changes have been applied to all the databases that are involved in the replication topology before they attempt the upgrade. If your application databases are not involved in Replication procedures then you can proceed as per your pre-planning process. IN any case all the attributes must also account for the order in which the databases are upgraded. On the replication upgrade process perspective such databases must be upgraded in the following order: Distributor, Publisher, Subscribers.
One of the key indicators in upgrade process is the order of upgrade which must be followed to satisfy the version requirements for Publisher, Distributor, and Subscriber. While there are slight variations depending on the method of replication that you are using, the Distributor must be at the same version or higher than the version of the Publisher while the Subscriber must be at the same version or lower than the Distributor. The key success on procedure is you always upgrade all Distributors first within the architecture. Once all Distributors are upgraded, you will upgrade all Publishers. Once all Publishers have been upgraded, you will complete the process by upgrading all Subscribers. This process is the same regardless of the replication methods that you are using within your architecture.
Coming on the point about upgrade options, in-place or side-by-side and we have additional option which is called offline upgrade. To go futher an in-place upgrade of replication will cause the SQL Server, databases, and replication to be upgraded directly with fewest steps. While an in-place upgrade is the most direct way to upgrade, the previous SQL Server 2000 instance will be overwritten and will no longer exist on your computer. Before upgrading, back up SQL Server databases and other objects associated with the previous SQL Server instance and a complete backup of all databases is essential. Next on a side-by-side upgrade will create a new instance of SQL Server 2005, and then you will migrate databases to the new instance. But because the databases are migrated to the new instance, the applications may have to be reconfigured to point to the new instance, and thus, it may not be viable. This upgrade option is only possible for merge replication if all data changes are prevented throughout the architecture. This upgrade option is possible for transactional or snapshot replication while changes are being issued against the existing architecture. On the final option which is an offline upgrade is not actually an upgrade method at all. Offline upgrades involve completely removing replication from SQL Server 2000, upgrading the database to SQL Server 2005, and then recreating the replication architecture.
But in my experience whenever the environment is involved in complex replication it is always best to take the entire replication architecture offline and reinitialize everything, this will give you an opportunity to clean up anything within your existing architecture.
Another practice is to upgrade the replication scripts, as there is a major changes to SQL 2000 and 2005 DB engine architecture. SO it is best to if you have generated replication scripts in SQL Server 7.0 or SQL Server 2000, these scripts will execute without errors only if you are executing under the security context of a sysadmin. Otherwise, you will receive errors, and the scripts will not complete. Books Online consists a major section on replication security & scripts and few of them listed below where each replication agent can run under separate security contexts with minimal permissions. Replication stored procedures that are affected by this behavior change are:
· sp_addpublication_snapshot
· sp_addpushsubscription_agent
· sp_addmergepushsubscription_agent
· sp_addpullsubscription_agent
· sp_addmergepullsubscription_agent
· sp_addlogreader_agent
· sp_addqreader_agent
Finally few counters on the upgrade steps (pre & post), if you are upgrading the Publisher to SQL Server 2005, the Distributor must be upgraded as well. Also by design it is not possible to publish from SQL Server 2000 to SQL Server 2005 if you are using merge replication. However, this is possible if you are using transactional or snapshot replication and on the case of transactional replication, the distribution database will only contain changes that need to be sent to each Subscriber. In the case of merge replication, the distribution database does not contain any data that you will want to upgrade. In either case, we can safely overwrite the distribution database because a SQL Server 2005 Distributor can service both SQL Server 2000 and SQL Server 2005 Publishers and Subscribers.
The upgrade document from Microsoft & Books Online highlights the discontinued functions that will affect all replication methods on a major way:
· Creating push subscriptions without an active connection
· Using file transfer protocol (FTP) to initialize Subscribers running SQL Server version 7.0
· Creating subscriptions in Windows Synchronization Manager
· Subscribing to a publication by locating it in Active Directory
· Snapshot ActiveX control
· Remote agent activation & Subscriptions using Microsoft Access (Jet 4.0)
On the aspects of deprecated features in particular to replication in SQL Server 2005 and these features will continue to work in SQL Server 2005, you should not use them for new applications because these features will be removed in a future release. Database administrators upgrading replication topologies that are multi-leveled, with Subscribers republishing the replicated data to other Subscribers, must make sure that they upgrade all Distributors in the replication hierarchy, followed by all the Publishers in the hierarchy before upgrading bottom-tier Subscribers.
It is important to review and understand changes made to SQL Server 2005 replication that will change the behavior of current SQL Server replication functionality. These behavioral changes need to be reviewed, understood, and accounted for in any SQL Server 2005 upgrade plan. Several changes to SQL Server 2005 replication may require application changes and should be reviewed and understood during upgrade planning.
On the next blog post I will cover on problem finding and solution within Replication upgrade process.