MSSQL database backup

SQL Server 2005 synchronous replication technology (transfer)

Follow these steps to copy a snapshot)

Running platform: SQL Server 2005

I. preparations:

1. Create a Windows user, set it as administrator, and set the password as the valid user for publishing the snapshot file.

2. The communication between the publishing server and the subscription server is normal under SQL Server (that is, mutual access is allowed ). Open port 1433 and set a special case in the firewall

3. Create a shared directory on the Publishing Server as the storage directory of the published snapshot file. For example, create a folder named sqlcopy under the root directory of drive D.

4. Set the SQL proxy (both the publishing server and the subscription server are set) This article is published at www.xker.com)

Open the Service (Control Panel --- management tools --- Services)

--- Right-click sqlserver agent --- properties --- login --- Select "this account"

--- Enter or select the Windows user created in step 1

--- Enter the user password in "password"

5. Set SQL Server Authentication To solve the permission issues during connection (publishing and subscription server settings)

Step: Object explorer ---- right-click SQL instance ----- properties ---- Security ---- Server Authentication ------ select "SQL Server and Windows", and then click OK

6. enable the network protocol TCP/IP and pipe naming protocol for SQL Server 2005 and restart the network service.

7. Create the System user login name in step 1 in SQL Server as the publishing database owner (set as dbo_owner and public ).

8. log on to SQL Server as the Super User SA to create a database and a table.

9. The Publishing Server and the subscription server register with each other.

The steps are as follows: View ---- click Register server ---- right-click Database Engine ---- create Server Registration ----- enter the name of the remote server to be registered ------ select "SQL Server Authentication" ----- User Name (SA) for Identity Authentication) password ------ create a group (or not) ----- complete.

10. If you can only use IP addresses and cannot use computer names, register the server alias for them.

Ii. Start:

Publishing Server Configuration (configure publishing and subscription on the Publishing Server)

1. Select a replication Node

2. Right click Local release ---- next --------- system pop-up dialog box to see the prompt ---- until "specify snapshot folder"

---- Enter the directory created during preparation in the "snapshot folder" (point to the shared folder created in step 3) ------ select Release Database ------- select release type ------- select subscription server type ------- select the object to be released ------ set snapshot proxy ------- enter the release name. This article is published on www.xker.com)

3. Right-click Local subscription -------- select the Publishing Server ------- select the subscription method (if the server side subscribes, select push subscription; otherwise

Select request subscription) ------- fill in the subscription server -------- select the proxy Plan (generally choose to run continuously) --------- select the default items for the rest.

So far, SQL Server 2005 synchronization replication is complete. With the replication technology, users can publish a copy of client data to multiple servers, so that different server users can share the data within the permitted range of permissions. The replication technology ensures that the data distributed in different locations is automatically updated synchronously to ensure data consistency, so that data synchronization between the client and the server is not required! This greatly improves work efficiency!
========================================================

I. Features of transaction Replication
We have pointed out that the essence of replication is to copy data from the source database to the target database, but there is always a difference for different replication types. From the specific content of the copy, snapshot replication is a real data replication, regardless of the data receipt method (such as deleting the table and then recreating or deleting the number of tables ).

Data, but the table structure is retained. Transaction replication transfers transactions (by one or more insert, delete, and update statements) over the network. From the perspective of the data volume transmitted, transaction replication only transmits changes to the subscription, is an incremental copy, but the snapshot copy will be published as a whole

Copy the item to the subscriber.
As transaction replication constantly monitors data changes in the source database, its server load is heavier than snapshot replication.
In transaction replication, when the Publishing Database changes, this change will be immediately passed to the subscriber and completed in a short period of time (several seconds or less ), instead of taking a long time interval like copying a snapshot. Therefore, transaction replication is a kind of nearly real-time distribution from the source database to the target database.

Data method. Since transaction replication is frequent, it is necessary to ensure a reliable network connection between the subscriber and the publisher.
Transaction replication only allows the publisher to modify the copied data (if the option to update the subscription now is set, the consumer is allowed to modify the copied data), unlike the combined replication, all nodes (publishers and subscribers) are allowed to modify and copy data, so transaction replication ensures transaction consistency. It

Transaction consistency is between immediate transaction consistency and potential transaction consistency.
Since transaction replication distributes data to the subscriber within a very small latency, it requires the publisher to always maintain a connection with the subscriber. However, in snapshot replication, because the two adjacent copies have a long data transmission interval, the subscription owner and the publisher are not allowed to maintain a permanent connection.
Another unique feature of transaction replication is its support for parallel snapshot processing, which is also a new feature of SQL Server 2000 transaction replication. As described in the snapshot copy section, a shared lock should be placed on the publishing table to prevent

Publishing more. The concurrent snapshot processing supported by transaction replication does not allow the shared lock to be kept until the snapshot file is created. The specific process is: when the replication starts, the snapshot agent places a shared lock on the publishing table. When an event starting with a snapshot is written into the transaction log

The shared lock is released. In this way, you can modify the publishing table even if the snapshot file is still being generated. It can be seen that the shared lock lasts for a short period of time during table Publishing. The time when the snapshot agent starts creating the snapshot file is the time when the shared lock is released. After the snapshot file is created

. Indicates that the event after creation is recorded in the transaction log. Transactions that affect the publishing table during snapshot generation from the beginning to the end will be sent to the distribution database by the log reading agent.
Although parallel snapshot processing allows you to modify the publishing table while creating snapshot files, it also increases the snapshot processing load and reduces the performance of copying, therefore, snapshot initialization should be performed when there are few system activities.

Ii. Execution steps of transaction Replication
Transaction replication mainly requires three proxies: Snapshot proxy, log reading proxy, and distribution proxy.

1. snapshot proxy
Before obtaining new changes from the publisher, the snapshot agent must have the same table structure and data as the table in the database to be subscribed. Therefore, the snapshot proxy must first initialize the synchronization set. SQL Server can resume a transaction only after it confirms that the subscription contains a snapshot file of the table description and data.

.

2. Log reading agent
Search for transactions with the replication Identifier from the publisher transaction log and insert these transactions into the distribution database.

3 Distribution agent
The distribution agent inserts the log reading agent into the distribution database and distributes the transactions to the subscription.
The procedure of snapshot proxy and distribution proxy in transaction replication is basically the same as that in snapshot replication. In transaction replication, each proxy completes transaction replication according to the following execution sequence (see Figure 16-53 ).
 
(1) When a subscription is created or a publication is created, the scheduled time snapshot proxy will be executed. After the snapshot proxy places a shared lock on the paper, create a synchronization set that contains data files and description files. The description file is used to create the same table structure as the paper table in the ordering database. Then

: These two files are stored in the publisher's copy directory, and the synchronization jobs are recorded in the distribution database.

(2) The log reading agent can continuously run or run at the scheduled time when a publication is created to monitor data changes. When the log reading agent is executed, it first reads the transaction log of the publication and searches for insert, update, delete statements and other modification transactions with the replication flag. Next, log reading

The read proxy copies the transaction batches with the replication flag to the distributor's distribution database. The log reading agent uses the system process sp_replcmds to obtain the next batch of commands with the replication flag from the log. Only the committed transactions are sent to the distribution database.

The replication transactions in the distribution database and those with replication marks in the publisher transaction logs are one-to-one. A transaction stored in the msrepl_transactions table can be composed of multiple commands, each of which is stored in the msrepl _ commands table. After the entire batch transaction is successfully written to the Distribution Database

, Each command will be submitted and then read the agent's call to the sp_repldone system process to indicate where the replication transaction is finally completed. Finally, the proxy indicates which line in the transaction log will be truncated. The rows that are still waiting for replication will not be truncated. Deleting transaction logs from the publisher does not affect replication, because only the Unlabeled

Transactions with replication will be cleared.
(3) Transaction commands are stored in the distribution database all the time, unless the distribution agent pushes it from the distributor to the subscription database or pulls it from the subscription database to the subscription database.

Note: The main task for the distribution agent to execute for the first time is to initialize the subscription and distribute the initial snapshot file to the subscription.
The distribution agent is only used to copy and does not contain any user tables. It is not allowed to create any other database objects in the distribution database.
Operations that will be copied at the publisher's reception will be executed on the subscription in order to ensure the final consistency of data.
If the subscription transaction publication is not initialized, the storage process automatically customized on the publisher cannot be used by the subscription. On the contrary, you must manually create a stored procedure in the subscription.

Iii. Replication of Stored Procedures
In SQL Server, you can copy stored procedures in addition to tables. In snapshot copy, if the paper contains a stored procedure, the entire stored procedure will be transmitted from the publisher to the subscription during the copy process. In transaction replication, if the paper contains a stored procedure, it is passed to the subscription During the replication process.

It is only the execution statement of a stored procedure, rather than the changed data caused by the execution of the stored procedure.
If a stored procedure produces a large result set after execution, we will find that copying only the execution statements of the stored procedure rather than a large number of SQL statements will bring great benefits. There is no need to worry that a large number of replication statements will occupy the distribution database and cause transaction loss, nor will it be caused by copying a large number of SQL statements.

The decline in Network Transmission Performance is frustrating.
The following example may help you better understand the advantages of the stored procedure.
Assume that the database user intends to modify a column of 10000 records in a table in the publication. If Stored Procedure replication is not used, the process should be as follows: when these modifications are completed by the publisher, the log reading agent reads the first 10000 copies with the replication flag from the transaction log.

Delete statements and update statements of the same number, and send them to the distribution database. The limited space of the distribution database immediately seems a little difficult if you are unlucky, the distribution database will be filled up, resulting in a copy interruption), and then the distribution agent will distribute the 20000 statements to the subscriber.

At the time of sending, 20000 statements are scrambling to be crowded with network lines. I think you will feel the kind of waiting experience.
However, if you use a stored procedure to implement this modification and add the stored procedure to your transaction replication thesis, it is only an exec statement passed online.
Defining a stored procedure as publishing content is not difficult. You only need to select the include store check box in Figure 16-31 specify articles.