SQL Server distribution and subscription

Concept of Replication

Microsoft? SQL Server? 2000 of replication is a group of technologies that replicate, distribute, and synchronize data and database objects between databases to ensure consistency.

Data can be distributed to different locations by means of replication. data can be distributed to remote or mobile users through a LAN, a dial-up connection, or the Internet. Replication also improves application performance and physically separates data (for example, separating online transaction processing (OLTP) from Decision Support Systems) based on data usage ), or distributed database processing across multiple servers.

---------------------------------------------------------------------------
The basic elements of SQL replication include

Publishing Server, subscription server, distribution server, publishing, Project

Publishing Server
The Publishing Server is a server that provides data to be copied to other servers. The Publishing Server can have one or more releases. Each release represents a set of Logical Data. In addition to specifying which data needs to be replicated, the publisher also detects the changed data during transaction replication and maintains information about all the releases on this site.

Distributor
A distributor is a server that hosts distributed databases and stores historical data and/or transactions and metadata. The role of the distributor varies depending on the replication type. For more information, see replication types. A remote distribution server is a replication-independent distribution server. A local distribution server is a replication distribution server.

Subscription Server
The subscription server is the server that receives the copied data. The subscription server subscribes to a release instead of a project separated from the release. The subscription server only subscribes to the desired release, rather than all the available releases on the Publishing Server. Based on the replication type and the selected replication options, the subscription server can also spread the data changes back to the Publishing Server or re-publish the data to another subscription server.

Release
Publishing is a collection of one or more projects in a database. The grouping of multiple projects makes it easier to copy a set of data related to the specified logic and database objects together.

Project
A project is a data table, data partition, or database object to be copied. The project can be a complete table, a few columns (using vertical filtering), and several rows (Using Horizontal Filtering), stored procedure or view definition, execution of stored procedure, view, index view or user-defined function.

Subscription
Subscription is a request to copy data or database objects. Subscription defines the time and place at which the subscription will be published and received. Subscription synchronization or data distribution can be requested by the Publishing Server (Force subscription) or the subscription server (request subscription. Publishing supports the combination of forced subscription and request subscription.
---------------------------------------------------------------------------
How SQL replication works
SQL Server mainly uses publications and subscriptions to process replication. The server on which the source data is located is the publishing server, which is responsible for publishing data. The Publishing Server copies all changes to the data to be published to the distribution server. The distribution server contains a distribution database that can receive all changes to the data and save the changes, and then distribute the changes to the subscription server.

---------------------------------------------------------------------------
(For details, refer to ):
1. Copying snapshots
2. Transaction Replication
3. Merge and copy

========================================================== ==========================================
This section describes how to copy data. (Take snapshot copy as an example)

Preparations:

1. On the Publishing Server, the subscription server creates a Windows user with the same name, and sets the same password as the valid user who accesses the snapshot folder.
-- My computer
-- Control Panel
-- Management Tools
-- Computer Management
-- Users and groups
-- Right-click the user
-- Create a user
-- Create a Windows login user affiliated to the Administrator Group

2. Create a new shared directory on the Publishing Server as the directory for storing the published snapshot files. perform the following operations:

My computer -- D: \ creates a directory named pub
-- Right-click the newly created directory
-- Property -- share
-- Select "share this folder"
-- Use the "permission" button to set specific user permissions to ensure that the user created in step 1 has all permissions on the folder
-- OK

3. Set the startup user of the SQL proxy (SQLServerAgent) Service (this setting is done by the publishing/subscription server)

Start -- program -- management tool -- service
-- Right-click SQLServerAgent
-- Property -- login -- select "this account"
-- Enter or select the Windows logon user name created in step 1.
-- Enter the user's password in "password"

4. Set the SQL Server Authentication Mode to solve the permission issue during connection (this setting is done by the publishing/subscription servers)

Enterprise Manager
-- Right-click an SQL instance -- Properties
-- Security -- Authentication
-- Select "SQL Server and Windows"
-- OK

5. Register with each other on the Publishing Server and subscription Server
Enterprise Manager
-- Right-click the SQL server group
-- Create an SQL Server registration...
-- Next -- available server, enter the name of the remote server you want to register -- add
-- Next -- connect to use, select the second "SQL Server Authentication"
-- Next -- enter the user name and password
-- Next -- select an SQL Server group or create a new group.
-- Next -- complete

6. Register a server alias for an IP address-only computer name.
(Configure on the connection end. For example, if the server is configured on the subscription server, enter the IP address of the Publishing Server in the server name)
Start -- program -- Microsoft SQL Server -- client network utility
-- Alias -- add
-- Select "TCP/IP" for the network library -- enter the SQL server name as the server alias
-- Connection parameter -- enter the SQL Server IP address in the server name
-- If you have modified the SQL port, deselect "dynamically determine port" and enter the corresponding port number.

========================================================== ==============================================

Official start:

1. Configure the Publishing Server

A. Select the specified [server] Node
B. Select the [Publish, subscribe server, and distribute] command from the [copy] submenu in the [tools] drop-down menu.
C. In the dialog box that appears, click [next] and follow the prompts.
-- Until "specified snapshot folder"
-- Enter the directory created during preparation in the "snapshot folder": \ <Server Name> \ pub
1. [next] The operation is complete.
D. After setting the Publishing Server, the system adds a replication monitor to the tree structure of the server and generates a distribution database (distribution)

---------------------------------------------------------------------------

2. Create a release
A. Select the specified server
B. Select the [create and manage release] command from the [copy] sub-menu in the [tools] menu. A dialog box is displayed.
C. Select the database for which you want to create a release, and click [Create release]
D. Click [next] in the prompt dialog box of [Create release wizard]. A dialog box is displayed. The dialog box contains three types of copies. Now we select the first one, that is, the default snapshot release (the other two can be checked for help)
E. Click [next] system requirements to specify the type of database server that can subscribe to the release. sqlserver allows data replication between different databases, such as oracle or access. But here we choose to run the database server "SQL Server 2000"
F. Click [next] and select the objects to be published (such as tables, views, stored procedures, and tables)
G. Then [next] until the operation is completed. After the publication is created, the database for creating the publication becomes a shared database.

---------------------------------------------------------------------------

3. Design subscription
A. Select the specified subscription Server
B. Select [subscribe request] from the [tools] drop-down menu in the [copy] submenu
C. Follow the prompts and click [next] until the system prompts you to check the running status of the SQL Server proxy service. The prerequisite for performing the copy operation is that the SQL Server proxy service must be started.
D. Click [finish] to complete the subscription operation.

----------------------------------------------------------------------------

The above steps are actually copied successfully. But how can we know whether the replication is successful?
This method can be used to quickly check whether the operation is successful.
Expand copy under the Publishing Server -- publish content -- Right-click Publish content -- properties -- click live -- status, and then click Run Agent immediately, and then click agent properties to view live Scheduling
Set scheduling to happen every day, every minute, between 0:00:00 and 23:59:59.
The next step is to determine whether the replication is successful.
Open c: \ Program Files \ Microsoft SQL Server \ MSSQL \ repldata \ UNC \ xiaowangzi_database_database and check whether there are some folders that use time as file names.

Precautions for SQL Replication
(Alibaba Cloud customer http://blog.csdn.net/sxycgxj/archive/2005/09/30/492765.aspx)
1. Set the shared copy directory:
\\\\ Computer_machine \ C $... \\.. if the server cancels the default share ($), it will fail. In this case, a new directory is created and the permission is granted. The directory is changed to \\\\ computer_machine \ shared directory name.

2. Create a Windows account for publishing an application

3. An error occurred while configuring the distributor. The error 18483 is prompted because it is not defined as remote login on this server, so it cannot be created.
Solution: (run the following Program)
Use master
Go
-- Set two variables
Declare @ serverproperty_servername varchar (100 ),
@ Servername varchar (100)
-- Obtain information about the Windows NT server and the instance associated with the specified SQL server instance
-- For details, refer to the online series "serverproperty"
Select @ serverproperty_servername = convert (varchar (100), serverproperty ('servername '))
-- Return the name of the local server running Microsoft & reg; SQL Server & #8482;
-- For more information, see books online @ servername.
Select @ servername = convert (varchar (100), @ servername)

-- If @ serverproperty_servername is different from @ servername (because you changed the computer name), run the following
-- Delete the wrong server name
Exec sp_dropserver @ Server = @ servername
-- Add the correct server name
Exec sp_addserver @ Server = @ serverproperty_servername, @ local = 'local'

In addition, the two servers can be scheduled for regular backup.
In this way, the server data can be backed up to ensure data security.