SQL Server 2005 database scheduled replication for dual standby (primarily SharePoint content databases)

Original: SQL Server 2005 database scheduled replication for dual standby (primarily SharePoint content databases)

Scene

The company's most recent database server for SharePoint is always in trouble, and is not working properly once a problem occurs. The main thing is that we have to work overtime to restore the previous database, which is the most headache. So on the Internet to find information, to achieve the synchronization of the main standby, for SharePoint we only need to do SharePoint content database synchronization. This technology has a professional term called dual-machine hot standby .

Principle of Dual machine hot preparation

Two-Machine hot-standby (or high-availability) is based on the high-availability system, the two servers in the hot spare (or highly available), because both high-availability in the domestic use more, so the name of the dual-machine heat preparation, Dual-machine high-availability by work switching mode is divided into: master-Standby mode (Active-standby mode) and dual-host mode (active-active mode), primary-standby means that a server is in a certain kind of business activation state (that is, the active state), The other server is in the standby state of the business (that is, the standby state). The dual-host approach means that two different services are in the primary standby state (that is, the Active-standby and Standby-active states) on two servers respectively.

We are mainly using the SQL Server 2005 subscription replication feature to implement a dual-machine hot standby, as shown in:

SQL Server 2005 Subscription replication principle

The following overview is from Microsoft's official MSDN

SQL Server 2005 Replication types

• Transactional Replication Overview:

Transactional replication typically starts with a snapshot of the publication database objects and data. After the initial snapshot is taken, data changes and schema modifications made at the publisher are typically passed to the subscriber when the modification occurs. The change order in which data changes are applied at the Subscriber and the transaction boundary at which the changes are made are the same as at the publisher.

Transactional replication is typically used in a server-to-server environment and is appropriate for transactional replication in the following various scenarios:

1. You want the incremental changes to propagate to subscribers when they occur.
2. When changes are made from the Publisher to the Subscriber, the application needs to have a low latency between the two.
3. The application needs to access the intermediate data state. For example, if a row is changed five times, transactional replication allows the application to respond to each change, not just the final data change for that row.
4. The publisher has a large number of INSERT, update, and delete activities.
5. The Publisher or subscriber is not a SQL Server database (for example, Oracle).

• Merge Replication overview

As with transactional replication, merge replication typically starts with a report snapshot of the publication database objects and data. Use triggers to track subsequent data changes and schema modifications made at the Publisher and Subscribers. The Subscriber synchronizes with the publisher when connected to the network and swaps all rows that have changed between the Publisher and the Subscriber since the last synchronization.

Merge replication is typically used in server-to-client environments. Merge replication applies to the following scenarios:

1. Multiple Subscribers may update the same data at different times and propagate these changes to the Publisher and other Subscribers.
2. Subscribers need to receive data, change data offline, and synchronize changes with the Publisher and other subscribers at a later time.
3. Each subscriber requires a different data partition.
4. There may be conflicts. In the event of a conflict, the ability to detect and resolve conflicts is required.
5. The application requires the result of the final data change, rather than accessing the intermediate data state. For example, if a row at the Subscriber changes five times before the subscriber synchronizes with the publisher, the row is changed only once at the publisher and changed to a fifth value to reflect the final data change.

Merge replication allows different sites to work autonomously and then merge the updates into one unified result at a later time. Because updates are made on multiple servers, the same data may be updated by the Publisher and multiple subscribers. As a result, conflicts can occur when merging updates. Merge replication provides several ways to handle conflicts.

• snapshot replication

Snapshot replication distributes data at the instantaneous state of a specific moment without monitoring updates to the data. If synchronization occurs, a full snapshot is generated and sent to the Subscriber.

Using snapshot replication itself is most appropriate when one or more of the following conditions are met:

1. Data changes are infrequent.
2. Allows for a period of time a copy of data that has a relative publisher that is obsolete.
3. Copy small amounts of data.
4. A large number of changes occur in the short term.

We used a snapshot to replicate the subscription to the SharePoint content database.

Talk less, let's take a look first:

Where OADB05 is the primary server database and Oadb05backup is the backup environment database.

Figure 1

Click Open Copy, then right-click Local copy, select the type of replication, and then click Next

Figure 3

Select the properties for which you want to copy tables

Figure 4

Select Next, if you need to change the copy operation time, click Change, click Next

Click Done, copy is complete.

Because there are so many graphs, they are only part. Here is the subscription, right click on the local subscription select the published database,

Click Next Next. It's OK.

Because there are too many pictures, there is no one to upload, if you have any questions at any time contact me. Or my plan is not good, can come up, we discuss together.

SQL Server 2005 database scheduled replication for dual standby (primarily SharePoint content databases)