This article discusses the requirements for backing up databases involved in snapshot replication, transactional replication, or merge replication.These requirements depend on the role that the server plays in replication and on the place where replication data must be restored in the replication topology. To restore data involved in replication, you must regularly back up publishers, distributors, and subscribers.
There are four most common backup strategies used in replication, which are presented below and are defined by the list of replicated databases:
The more reliable strategy is to reserve the publisher and distributor at the moment when they are synchronized. This strategy also allows you to fully restore replication.Backing up subscribers is optional, but it can reduce the time to resume subscribers.
In most backup scenarios, published databases and a distributor should be backed up immediately after adding or modifying replication objects, such as articles and subscriptions, or after making changes to the schema affecting replication. If the distributor's database is restored to the point in time when such changes have not yet been made, it is necessary to restore the published database to the same point in time. From this we can conclude that the time of servers participating in replication should automatically be synchronized.
Distributor backup allows you to store data about publication snapshots, replication session history, errors, and other replication agent information. With such a copy, you can speed up the recovery of the publisher and distributor, since you do not need to reinstall the replication. A feature of transactional replication is that its backup strategy requires coordination between backing up publications and distributors databases.SQL Server 2000 can do this coordination automatically.Back up the distribution database, and then back up the transaction log and differential backups of this database.It is necessary to restore the distribution database synchronously with the restoration of the published database.
There can be several distribution databases for different publishers on one server acting as a distributor. In such a configuration, it is important to follow the coordination of the reservation of publishers and the distributor base, so that this would reduce the time for re-initialization of subscribers.
You need to make a full backup of the signed database, and then make backup copies of the transaction log and differential copies of the database. Note that backing up each subscriber is not a must for replicating failover. In most cases, reserving a publisher and distributor regularly is enough to do this. If the cost of re-initializing a subscriber is significantly greater than restoring it from a backup copy, and the complexity of managing backups across the enterprise is not great, you may want to consider expanding the backup scheme by backing up subscribers. In addition, if only a few articles are involved in replication and there are tables on the subscriber that cannot be reinitialized, you will also need to back up the databases on the subscribers. You will need this when republishing articles involved in replication.
The publisher master database stores subscriber information in the sysservers table. Publisher information is stored in the same master database table on the distributor. To ensure replication can be restored after losing a publisher or distributor, make backup copies of their master databases after changes in the number of subscribers or publishers. The most important table in the master database is sysdatabases, since whether or not the correct values are saved in the category field for replicable databases will determine the success of their further recovery without losing the settings of publications.For replication of transactions or snapshots, this field should contain a one, and for merge replication - a four. When restoring the database to the same server where its copy was made, SQL Server analyzes the value set for this database in the master.dbo.sysdatabases.category field by which it becomes clear to it whether it is necessary to save the replication settings during recovery. In order to explicitly indicate that the database being restored is published in transactional replication or merge, you must use the sp_replicationdboption system stored procedure.
The msdb database is used by SQL Server Agent, Enterprise Manager and SQL Server to store information about tasks and their schedules, backup copies and their history. The information in this database changes when scheduling jobs, when saving DTS packets, when backing up and restoring, as well as during replication sessions. SQL Server automatically saves backup data and data recovery history in the msdb database. It is remembered who backed up, at what time and in which devices or files the backup is stored. It is this information that Enterprise Manager uses to calculate the database recovery plan, taking into account the necessary to correctly restore the recovery history of transaction log copies and differential copies.
Together with the publisher, you need to reserve a distributor so that, if necessary, they would be synchronized. While they are being backed up, you cannot create new snapshots, publications, or add subscribers.Before backing up the distributor, it is useful to run the Distribution Cleanup task automatically created by replication wizards for execution, after which the unnecessary information will be deleted from the distributor database. This may allow reducing the time for generating snapshots and creating backup copies of the distribution database.
As for the rest, the backup strategy of database snapshots involved in replication is no different from the backup strategy of regular user databases.
To enable the publisher and distributor to be restored at any given time, it is necessary for these SQL Server 2000 databases to enable the synchronization of the replicated database with the backup copy. This is done using the sp_replicationdboption system stored procedure, the syntax of which is as follows:
In merge replication, all changes to the data are recorded only in the system metadata tables in the published database and in the subscriber databases, as a result of which there is no need for consistency between the data of the publisher and the distributor. Metadata tables fall into the backup copy in the same way as other tables of the published or signed base, and therefore the synchronization status of subscribers and publisher reflected in the metadata will be restored as it was in the backup copy. At the same time, data convergence in all backup copies of databases participating in replication is guaranteed.
After restoring the published database, you can simply reinitialize all subscribers, which will allow you to bring their data in accordance with the new state of the publisher. However, before re initialization, you can synchronize the publisher with those subscribers who have the latest data that is missing in the backup copy of the publisher.
Reservation strategies
Proper selection of a backup strategy for databases involved in replication will minimize the recovery time of the distributed system after a failure or hardware failure.A backup strategy depends on the recovery time requirements and on its complexity, usually described in the backup plan.As a rule, if you need to quickly restore any backup with a minimum probability of data loss, a full backup of all user and system databases is provided.There are four most common backup strategies used in replication, which are presented below and are defined by the list of replicated databases:
- Database, master and msdb
- Publishers database, distributor, master and msdb
- Published databases, signed databases, master and msdb
- Published databases, subscribed databases, distributor, master, and msdb
The more reliable strategy is to reserve the publisher and distributor at the moment when they are synchronized. This strategy also allows you to fully restore replication.Backing up subscribers is optional, but it can reduce the time to resume subscribers.
In most backup scenarios, published databases and a distributor should be backed up immediately after adding or modifying replication objects, such as articles and subscriptions, or after making changes to the schema affecting replication. If the distributor's database is restored to the point in time when such changes have not yet been made, it is necessary to restore the published database to the same point in time. From this we can conclude that the time of servers participating in replication should automatically be synchronized.
Publisher Backup
Published databases are the most important and vulnerable part of replication topology; therefore, even the simplest backup plan should include a full backup of publishers. Publisher backups should be regular and, in addition to a full copy of the published database, should include a transaction log backup and / or differential backup. You can also copy the master and msdb databases to protect against the complete loss of the system, and not just user databases. If Log Shipping is used, be sure to regularly copy the msdb system database (which is used to account for the transfer of log copies).Distributor backup
Distributor backups imply backups of the distribution database and the msdb and master system databases.This allows you to restore its functionality almost after any failure, without the need to re-create publications or reconfigure replication.Distributor backup allows you to store data about publication snapshots, replication session history, errors, and other replication agent information. With such a copy, you can speed up the recovery of the publisher and distributor, since you do not need to reinstall the replication. A feature of transactional replication is that its backup strategy requires coordination between backing up publications and distributors databases.SQL Server 2000 can do this coordination automatically.Back up the distribution database, and then back up the transaction log and differential backups of this database.It is necessary to restore the distribution database synchronously with the restoration of the published database.
There can be several distribution databases for different publishers on one server acting as a distributor. In such a configuration, it is important to follow the coordination of the reservation of publishers and the distributor base, so that this would reduce the time for re-initialization of subscribers.
Subscriber backup
A simple backup strategy can be based on re-initializing subscriptions instead of restoring them from a backup, and can be expanded by adding back-up copies of each signed database and the system databases of the subscriber msdb and master to the backup plan.The msdb and master databases need to be copied if pull - subscription is used and this will be required only when recovering from the loss of the entire system.You need to make a full backup of the signed database, and then make backup copies of the transaction log and differential copies of the database. Note that backing up each subscriber is not a must for replicating failover. In most cases, reserving a publisher and distributor regularly is enough to do this. If the cost of re-initializing a subscriber is significantly greater than restoring it from a backup copy, and the complexity of managing backups across the enterprise is not great, you may want to consider expanding the backup scheme by backing up subscribers. In addition, if only a few articles are involved in replication and there are tables on the subscriber that cannot be reinitialized, you will also need to back up the databases on the subscribers. You will need this when republishing articles involved in replication.
Backup master database, msdb and distribution
The master, model, msdb and distribution databases are backed up in the same way as user databases, and for them (if there are changes) this operation should also be performed regularly. In order to avoid competition with system objects, it is not recommended to create user objects in these databases. Also, you should be careful when using the system database recovery utility rebuildm.exe, because in the course of its operation, all system databases are replaced with templates from the distribution kit and the information stored in the system databases will be lost. When choosing a backup strategy, you need to take into account the fact that the master, model, msdb and distribution databases support all existing backup models, from full to simple.The publisher master database stores subscriber information in the sysservers table. Publisher information is stored in the same master database table on the distributor. To ensure replication can be restored after losing a publisher or distributor, make backup copies of their master databases after changes in the number of subscribers or publishers. The most important table in the master database is sysdatabases, since whether or not the correct values are saved in the category field for replicable databases will determine the success of their further recovery without losing the settings of publications.For replication of transactions or snapshots, this field should contain a one, and for merge replication - a four. When restoring the database to the same server where its copy was made, SQL Server analyzes the value set for this database in the master.dbo.sysdatabases.category field by which it becomes clear to it whether it is necessary to save the replication settings during recovery. In order to explicitly indicate that the database being restored is published in transactional replication or merge, you must use the sp_replicationdboption system stored procedure.
The msdb database is used by SQL Server Agent, Enterprise Manager and SQL Server to store information about tasks and their schedules, backup copies and their history. The information in this database changes when scheduling jobs, when saving DTS packets, when backing up and restoring, as well as during replication sessions. SQL Server automatically saves backup data and data recovery history in the msdb database. It is remembered who backed up, at what time and in which devices or files the backup is stored. It is this information that Enterprise Manager uses to calculate the database recovery plan, taking into account the necessary to correctly restore the recovery history of transaction log copies and differential copies.
Strategies for replicating snapshots
Backup / restore strategies in snapshot replication are the easiest to implement and maintain. In addition, since this type of replication creates snapshots of publications that contain not only data, but also a schema, there is no need to back up the published database as often as is done in transactional replication or merge replication. It is a good practice to back up a published database immediately after making changes to the settings or layout of existing publications or after adding new ones.Together with the publisher, you need to reserve a distributor so that, if necessary, they would be synchronized. While they are being backed up, you cannot create new snapshots, publications, or add subscribers.Before backing up the distributor, it is useful to run the Distribution Cleanup task automatically created by replication wizards for execution, after which the unnecessary information will be deleted from the distributor database. This may allow reducing the time for generating snapshots and creating backup copies of the distribution database.
As for the rest, the backup strategy of database snapshots involved in replication is no different from the backup strategy of regular user databases.
Transnational Replication Strategies
Unlike earlier versions, in SQL Server 2000 it is possible to restore the published databases and the distributor database without the need to re-initialize subscribers or disable / reconfigure publications and distributors. After recovery, SQL Server 2000 will automatically coordinate these databases. Another new feature in SQL Server 2000 is the possibility of sharing without the need for special settings for transactional replication and log synchronization services with a backup server (log shipping).To enable the publisher and distributor to be restored at any given time, it is necessary for these SQL Server 2000 databases to enable the synchronization of the replicated database with the backup copy. This is done using the sp_replicationdboption system stored procedure, the syntax of which is as follows:
EXEC sp_replicationdboption '', 'sync with backup', 'true'
Merge replication strategies
Databases that participate in merge replication can also be restored without the need for subsequent re-initialization of subscribers and setting up publishing and subscriptions. This is done using the latest data available on other servers, which can be synchronized with changes not stored in the last backup. This type of replication can also be combined with the transaction log synchronization service and the backup server (log shipping).In merge replication, all changes to the data are recorded only in the system metadata tables in the published database and in the subscriber databases, as a result of which there is no need for consistency between the data of the publisher and the distributor. Metadata tables fall into the backup copy in the same way as other tables of the published or signed base, and therefore the synchronization status of subscribers and publisher reflected in the metadata will be restored as it was in the backup copy. At the same time, data convergence in all backup copies of databases participating in replication is guaranteed.
After restoring the published database, you can simply reinitialize all subscribers, which will allow you to bring their data in accordance with the new state of the publisher. However, before re initialization, you can synchronize the publisher with those subscribers who have the latest data that is missing in the backup copy of the publisher.
Post A Comment:
0 comments: