Distribution and replication of databases

1. Distribution and Replication
Implementing database synchronization with push subscriptions
Large and batch data can be processed using the synchronization mechanism of the database:
//
Description
For ease of operation, all operations operate at the Publisher (distributor) and use push mode
Use the push subscription method in the client machine.
Test Pass
//
--1: Environment
Server environment:
Machine Name: zehuadb
Operating system: Windows Server
Database version: SQL Server Personal Edition

Client
Machine Name: ZLP
Operating system: Windows Server
Database version: SQL Server Personal Edition

--2: Build user account
Establishing a domain user account on the server side
My Computer Management, local Users and groups, user-based
Username:zlp
Userpwd:zlp

--3: Restart the server MSSQLServer
Service->mssqlserver services, management tools, control Panel, My Computer
(Change to: domain user account, our new ZLP user./ZLP, password: ZLP)

--4: Installing the distribution server
A: Configure the distribution server
Tools, replication, configure Publishing, Subscribers, and distribution next--next (all with default configuration)
B: Configure the Publisher
Tools, replication, create and manage publications, select the database to publish (SZ), Next, Next, Snapshot publishing
Select what you want to publish, next, click Next, Next.
C: Force configuration of Subscribers (push mode, pull mode is similar to this)
Tools, copy, configure Publishing, Subscribers, and distributions, new->sql server database, enter client server name (ZLP), using SQL server authentication (SA, null password)- > OK, application--
D: Initializing Subscriptions
Replication Monitor, publishing Server (ZEHUADB), double-click Subscriptions, force new, Next, select Enabled Subscribers->zlp->
Next, Next, next, next step, complete

--5: Test configuration is successful
Copy Monitor, Publishers (ZEHUADB), double-click sz:sz-> Point status, run the agent now
View:
Replication Monitor, Publishers (ZEHUADB)->sz:sz-> Select Zlp:sz (type coercion), right-click Start synchronous Processing
If there is no error flag (Red Fork), congratulations on your successful configuration
--6: Test Data
--On server execution:
Select a table and execute the following SQL
Insert INTO wq_newsgroup_s Select ' Test succeeded ', 5
Replication Monitor, publishing Server (ZEHUADB)->sz:sz-> snapshot, startup agent
To see if the synchronized wq_newsgroup_s is inserting a new record

Test finished, pass.

--7 Modify the synchronization time of the database, generally choose the night to perform database synchronization processing

NOTES:

The server cannot publish and distribute data at (local), you need to remove the registration and then create a new registration local computer name
Uninstall by: Tools--copy-to-publish, disable publish on "zehuadb", uninstall all database synchronization configuration servers
Note: The SQLServerAgent service at the Publisher, distributor must start
Use push mode: "D:/microsoft SQL server/mssql/repldata/unc" directory file can not be set to share
Pull mode: You need to share!

A small number of database synchronization can be implemented by trigger, synchronous single table can:
========================================================
The problems that may occur during the configuration process are summarized as follows:
========================================================
Before you set up and use database replication in SQL Server 2000, you should first check the following points for the relevant SQL Server servers:
1. Whether the MSSQLServer and SQLServerAgent services are started and run as Domain users (./administrator users are also allowed)
If you are logged on with the local System account locally, you will not have network functionality and will produce the following error:
Process failed to connect to Distributor' @ServerName
(If your server is already using the SQL Server full-text Indexing Service, do not modify the local startup of the MSSQLServer and SQLServerAgent services.)
The full-text search service is not available. Please change another machine to do the distribution server in SQL Server 2000 replication. )
Modifying the logged-on user that the service initiates requires that the MSSQLServer and SQLServerAgent services be restarted to take effect.
2. Check the relevant SQL Server server for the name (Srvname and DataSource are required on the local machine srvid=0)
Execute in Query Analyzer:
Use master
Select Srvid,srvname,datasource from sysservers
If there is no srvid=0 or srvid=0 (that is, this machine) but Srvname and DataSource are not the same, you need to modify as follows:
Use master
GO
--Set two variables
DECLARE @serverproperty_servername varchar (100),
@servername varchar (100)
--Get the Windows NT server and instance information associated with the specified SQL server instance
SELECT @serverproperty_servername = CONVERT (varchar (+), serverproperty (' servername '))
--Returns the local server name running Microsoft SQL Server
SELECT @servername = CONVERT (varchar (+), @ @SERVERNAME)
--Displays both of the obtained parameters
Select @serverproperty_servername, @servername
--if @serverproperty_servername and @servername are different (because you changed the computer name), run the following
--delete the wrong server name
EXEC sp_dropserver @[email protected]
--Add the correct server name
EXEC sp_addserver @[email protected]_servername, @local = ' local '
Modifying this parameter requires that the MSSQLServer and SQLServerAgent services be restarted to take effect.
This will not result in a 18482 or 18483 error during the creation of the replication.
3. Check that SQL Server Enterprise Manager has several SQL Server registered names like the one described in 2nd above srvname
The registered name of the IP address cannot be used.
(We can delete the registration of the IP address and create a new server name registered with the SQL Server administrator-level user)
This will not result in 14010, 20084, 18456, 18482, 18483 errors during the creation of the replication.
4, check the relevant SQL Server server network is able to access the normal
If the ping host IP address can be, but the ping host name is not available, you need to
Winnt/system32/drivers/etc/hosts (WIN2000)
Windows/system32/drivers/etc/hosts (WIN2003)
The corresponding relationship between the database server IP address and the host name is written in the file.
For example:
127.0.0.1 localhost
192.168.0.35 OracleDB OracleDB
192.168.0.65 fengyu02 fengyu02
202.84.10.193 bj_db bj_db
or create aliases in the SQL Server Client Network Utility, for example:
5. Whether the extended stored procedure required by the system exists (if it does not exist, it needs to be restored):
Sp_addextendedproc ' xp_regenumvalues ', @dllname = ' Xpstar.dll '
Go
Sp_addextendedproc ' Xp_regdeletevalue ', @dllname = ' Xpstar.dll '
Go
Sp_addextendedproc ' Xp_regdeletekey ', @dllname = ' Xpstar.dll '
Go
Sp_addextendedproc xp_cmdshell, @dllname = ' Xplog70.dll '
Next, you can use SQL Server Enterprise Manager [Copy]-> right-click to select
->[Configure the publishing, subscriber, and distribution graphical interface to configure database replication.
Here are the steps to list configuration replication sequentially:
I. Establishing a publishing and distribution server
[Welcome to the Configure Publishing and Distribution Wizard]->[Select Distributor]
->[make "@servername" its own distributor, SQL Server creates a distribution database and logs]
->[developing the snapshot folder]-> [custom Configuration], [No, use the default configuration below]--[complete]
When the above steps are complete, a distribion library and a database are created in the current "@servername" SQL Server
A distributor_admin administrator-level user (we can change the password arbitrarily)
Four new jobs were added on the server:
[Agent History cleanup: distribution]
[Distribution cleanup: distribution]
[Replication agent Check]
[Reinitialization of subscriptions with data validation failures]
There is one more Replication Monitor in SQL Server Enterprise Manager, and the current machine can be published, distributed, and subscribed to.
We are in SQL Server Enterprise Manager again [copy]-> right-click to select
->[Configuring publications, subscribers, and distributions, you can see similar:
We can do this at the Publisher and Distributor Properties window
, [publisher], [new], [OK]
, [Publication database], [transactions]/[Merge], [OK]
[subscriber], [new], [OK]
Add other SQL Server servers on the network as a publication or subscriber.
The option to add a new publisher:
My new JIN001 Publisher is connected with the administrator-level database user test.
Admin link to Publisher optional box to enter a password, default is selected,
You need to enter a password for the distributor_admin user when establishing and distributing a link to the server Fengyu/fengyu on a new JIN001 publisher
The admin link to the Publisher optional box to enter the password, or you can uncheck the
That is, you do not need a password to establish a link to the Distributor (which is of course not secure and can be used in a test environment)
The option to add a new subscriber:

Second, another publisher on the newly established network (for example, JIN001) Select a Distributor

[Welcome to the Configure Publishing and Distribution Wizard]->[Select Distributor]

, use the following server (the selected server must already be configured as a distributor), [selected server] (for example, Fengyu/fengyu)
[Next], [Enter the password for the distributor_admin user of the Distributor (for example, Fengyu/fengyu) two times]
, [next], [Custom Configuration], [No, use the following default configuration]
[Next], [finish], [OK]

To establish a database replication publication process:

[Copy], [Publish content], right-click Select, [New Publication]
[Next], [choose Publication Database], [select a database to publish]
, [Next], [select publication type], [transactional publication]/[merge publication]
, [Next], [specify subscriber type], [Server running SQL Server 2000]
[Next], [Specify project], [only tables with primary key can be published in a transactional publication], [Select a table with primary key to publish]
->[a unique identifier field [ROWGUID] for a table that adds a unique index and ROWGUIDCOL property to the tables in a merge publication, the default value is newid ()]
(Add new column: Causes the INSERT statement without the column list to fail, increasing the size of the table, increasing the time required to generate the first snapshot)
->[Select a table to be published]
[Next], [select Publication name and description]
[Next], [Properties of custom Publication], [No, create a publication as specified]
[Next], [finish], [close]

There are many useful options in publishing properties: setting subscription expiration (e.g. 24 hours)

To set project properties for a publishing table:
The General window can specify the name of the publication destination table, which may not be the same as the original table name.
Is the column of the command and Snapshot window

(SQL Server database replication technology actually uses the Insert,update,delete operation to redo transactional operations at the Publisher at the Subscriber

Look at the documentation need to set the publishing database to full recovery mode, the transaction will not be lost

But I myself in the test found that the release database is the simple recovery model, every 10 seconds to generate some large transactions, 10 minutes before shrinking the database log,
During this period, the jobs on both the publication and the Subscriber are paused, and no transaction changes are lost after a pause recovery

A publishing table can be used for data filtering, such as selecting only some of the columns in the table:

For example, to select only certain qualifying records in the table, we can manually write the filtered SQL statements:

Publish the table's subscription options, and you can establish a push subscription:

A new job has been added to the publisher since the publication was successfully established: [Expired subscription cleanup]

Two new jobs were added to the Distributor:
[jin001-dack-dack-5] Type [REPL snapshot]
[jin001-dack-3] Type [REPL Log Reader]

The name of the blue word above uses a different number depending on the publisher name, publication name, and several releases

The Repl snapshot job is a precondition for SQL Server replication, which generates the published table structure, data, indexes, constraints, etc. to the Publisher's OS directory file
(Generated when there is a subscription, when the subscription request is initialized or is generated according to a timesheet schedule)

The REPL Log reader is running at the time of transactional replication. (Can be run according to scheduled schedule when merging replication)

To establish a database replication subscription process:

[Copy], [subscription], right-click Select-[New Pull subscription]
[Next], [Find publications], [view published publications by registered Servers]
[Next], [choose Publish], [select the database publication name at the publisher already established]
, [Next], [Specify Synchronization Agent login], [when the agent connects to the proxy server: Using SQL Server Authentication]
(Enter distributor_admin user name and password at publisher)
[Next], [select destination Database], [select the database name in which the subscription is created]/[You can also create a new library name]
, [next], [Allow anonymous subscriptions], [Yes, generate anonymous subscriptions]
[Next], [Initialize subscription], [Yes, initialize schema and data]
[Next], [snapshot transfer], [using snapshot files in the default snapshot folder for this publication]
(Subscribers will be able to access the publisher's Repldata folder, if there is a problem, you can manually set the network share and share permissions)
[Next], [snapshot transfer], [using snapshot files in the default snapshot folder for this publication]
[Next], [set Distribution Agent schedule], [change], [for example, schedule every five minutes]
[Next], [start required services], [this subscription requires running the SQLServerAgent service at the publisher]
[Next], [finish], [OK]

After the subscription has been successfully established, a new category is added to the Subscriber (repl-distribution) job (merge replication when the category is [repl-merge])
It's going to run the database synchronous replication job According to the time schedule we give.

View the history of its operation, example diagram:

On the distributor's [Replication Monitor]->[publisher]->[publication name]->[Log reader]-> Right-select->[Agent history], example:

After SQL Server replication is configured, you may have an experimental log of abnormal conditions:

1. The publisher is disconnected, the SQL Server service shuts down, restarts, shuts down, has no significant impact on the already set replication

During an outage, both the distribution and the subscription receive transaction information that is not replicated

2. The Distributor is disconnected, the SQL Server service shuts down, restarts, and when it is shut down, there are some effects on the replication that has been set up

During an outage, the publisher's transactions are queued and piled up
(if the option to delete expired subscriptions is set for a long time, the transaction log for the busy publication database may expand rapidly),

Subscribers will retry repeatedly because they cannot access the publisher server
We can set the number of retries and retry interval (maximum number of retries is 9999, if retry every minute, can support about 6.9 days without error)

The Distributor SQL Server service starts, and after the network is switched on, the heap jobs on the publisher will be in chronological order on the subscribing machine:

It will take a long time (actually the Insert,update,delete statement that generates all the transactions, executed at the subscriber)
We spent 7 minutes and 28 seconds executing 100,228 command executions of 58 transactions on a normal PC.

3. Subscriber off-grid, SQL Server service shutdown, restart, shutdown, the replication has been set up the impact of the larger, may need to re-preliminary

        Our experimental Environment (subscriber) from 18:46 unplanned downtime to, after 8:40 next day after the restart,  
             established replication starts to run normally after 8:40, and the heap jobs on the publisher will be in chronological order on the subscribing machine
            However, a snapshot error appears in the Replication Manager, the snapshot may need to be re-initialized, and replication may need to be restarted.
            (the machines in our experimental environment are not taking snapshots, replication is still successful)
    
    Iv. Delete the already established publications and subscriptions you can delete the button directly with the delete
    
    we'd better always press the first to delete the read, then delete the release, and finally disable the order of publishing to operate.  
    If you want to completely delete the replication settings above SQL Server, you can do this:

[Copy], right-click Select [Disable Publishing], [Welcome to the Disable Publishing and Distribution Wizard]
, [next], [Disable publishing], [to disable publishing on @servername]
[Next], [Finish disabling Publishing and Distribution Wizard], [finish]

We can also use T-SQL commands to complete the creation and deletion of publications and subscriptions in replication, select already established publications and subscriptions, right-click
You can [generate SQL script]. (Not in detail here, the following recommended site has more detailed content)

When you try to delete or change a table, the following error occurs
Server:msg 3724, Level A, state 2, line 1
Cannot drop the table ' object_name ' because it is being used for replication.

The typical case is that the table was used for replication, but later deleted the copy

Treatment methods:
SELECT * from sysobjects where replinfo > ' 0 '

sp_configure ' allow updates ', 1
Go
Reconfigure with override
Go
BEGIN TRANSACTION
Update sysobjects Set replinfo = ' 0 ' where Replinfo > ' 0 '
Commit TRANSACTION
Go
ROLLBACK TRANSACTION
Go
sp_configure ' allow updates ', 0
Go
Reconfigure with override
Go

Questions:
After the merge replication configuration is complete, if the synchronization agent is stopped. I'm going to restart the merge Replication Synchronization agent in the program. What commands or stored procedures do you use?

？
Workaround:
Sp_start_job
Instructs the SQL Server agent to execute the job immediately.

Example
The following example launches a job named Nightly Backup.

Use msdb
EXEC sp_start_job @job_name = ' Nightly Backup '

Distribution and replication of databases