Sqlserver 2000 synchronize the content of two SQLServer Databases

Preparations before replication:

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 User
Create 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: \ create a directory named PUB
Right-click the new directory
Attribute sharing
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 the program management tool Service
Right-click SQLSERVERAGENT
Select "this account" for property Login"
Enter or select the windows login 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 SQL instance attributes
Security Authentication
Select "SQL Server and Windows"
OK

5. Register with each other on the Publishing Server and subscription Server
Enterprise Manager
Right-click an SQL Server Group
Create an SQL Server registration...
In the available server next step, enter the remote server name you want to register to add
For next connection use, select the second "SQL Server Authentication"
Next, enter the user name and password.
Next, select the SQL Server group or create a new group.
Next step 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)
Microsoft SQL Server Client Network Utility
Add alias
Select "TCP/IP" server alias for the network library and enter the SQL Server Name
Enter the SQL Server IP address in the connection parameter server name
If you have modified the SQL port, deselect "dynamically determine port" and enter the corresponding port number.


SQL database synchronous Replication

1. Create a publishing and distribution server

[Welcome to the configuration release and distribution wizard]-> [select a distribution server]

-> [Make "@ servername" its own distribution Server, and SQL Server will create distribution databases and logs]

-> [Create a snapshot folder]-> [custom configuration]-> [No, use the following default configuration]-> [complete]

After the preceding steps are completed, a distribion library and

A distributor_admin administrator-level user (we can change the password as needed)

Four new jobs are added to the server:
[Clear Agent history: distribution]
[Clear distribution: distribution]
[Copy proxy check]
[Re-initialize a subscription with failed data verification]

The SQL Server Enterprise Manager has a replication Monitor, which allows you to publish, distribute, and subscribe to the current machine.

In SQL Server Enterprise Manager, right-click and choose
-> [Configure Publishing, subscription server, and Distribution], you can see the following:



In the [attributes of the publisher and distributor] window
-> [Publisher]-> [add]-> [OK]
-> [Publish database]-> [transaction]/[merge]-> [OK]
-> [Subscription server]-> [add]-> [OK]
Add other SQL Server servers on the network as Publish or subscribe servers.
Options for adding a Publishing Server:

The newly created JIN001 Publishing Server is connected to the Administrator-level database user test,
The password option box for the management link to the Publishing Server. The default value is selected,
Enter the password of the distributor_admin user when creating a FENGYU/FENGYU connection on the newly created JIN001 Publishing Server and the distribution server.
You can enter an optional Password box for the management link to the Publishing Server,
That is to say, you do not need a password to establish a link for publishing to the distributor (this is of course a lack of security and can be used in a testing environment)

2. Select the distribution server for another Publishing Server (for example, JIN001) on the new network.

[Welcome to the configuration release and distribution wizard]-> [select a distribution server]

-> Use the following servers (the selected servers must be configured as distribution servers)-> [Selected servers] (for example, FENGYU/FENGYU)

-> [Next]-> [enter the password of the distributor_admin user of the distributor (for example, FENGYU/FENGYU) Twice]

-> [Next]-> [custom configuration]-> [No, use the following default configuration]

-> [Next]-> [complete]-> [OK]

Create a database copy and release process:

[Copy]-> [publish content]-> right-click and choose-> [Create release]

-> [Next]-> [select Release Database]-> [select a database to be released]

-> [Next]-> [select release type]-> [Transaction release]/[merge release]
-> [Next]-> [specify the subscription Server type]-> [run SQL Server 2000 Server]

-> [Next]-> [specify a project]-> [only tables with primary keys can be published in transaction Publishing]-> [select a table with primary keys to be released]

-> [The Unique Identifier Field [rowguid] of the unique index and ROWGUIDCOL attribute will be added to the table during merge and release. The default value is newid ()].

(Adding a new column will cause the INSERT statement without the column list to fail, increase the table size, and increase the time required to generate the first snapshot)

-> [Select a table to be released]

-> [Next]-> [select the release Name and description]->

-> [Next]-> [custom release attributes]-> [No, create a release according to the specified method]

-> [Next]-> [complete]-> [close]

There are many useful options in publishing properties: Set subscription expiration (for example, 24 hours)

Set the project attributes of the publishing table:

In the general window, you can specify the name of the target table to be published, which may be different from the original table name.

Is the topic of the command and snapshot window

(SQL Server database replication technology uses insert, update, and delete operations to re-Execute transaction operations on the publishing Server on the subscription Server.

To read documents, you need to set the release database to the full recovery mode so that transactions will not be lost.

However, in my own test, I found that publishing a database is in simple recovery mode. Some large transactions are generated every 10 seconds and the database logs are reduced after 10 minutes,
During this period, jobs on the publishing and subscription servers are paused. After the pause is resumed, no transaction changes are lost)

You can filter data in a published table. For example, you can only select some columns in the table:

For example, if you only select some qualified records in the table, you can manually write the filtered SQL statement:

Publish the subscription options of the table and create a forced subscription:


After the publishing is successfully established, a new job is added to the Publishing Server: [Clearing invalid subscriptions]

Two new jobs are added to the distributor:
[JIN001-dack-dack-5] type [REPL snapshot]
[JIN001-dack-3] type [REPL Log Reader]

The blue name above uses different numbers based on the server name, name, and number of releases.

A repl snapshot job is a prerequisite for SQL Server replication. It first generates the published table structure, data, indexes, and constraints to the files in the OS directory of the Publishing Server.
(It is generated only when a subscription is available. It is generated when the subscription request is initialized or scheduled according to a certain schedule)

The REPL Log Reader is always running during transaction replication. (You can run the task according to the scheduling schedule when merging and copying data)

Create a database copy and subscription process:

[Copy]-> [subscribe]-> right-click and choose-> [Create a request subscription]

-> [Next]-> [find a release]-> [view a release made by a registered server]

-> [Next]-> [select release]-> [select the Database Release name on the created Publishing Server]

-> [Next]-> [Specifying synchronization agent logon]-> [when a contemporary agent is connected to the proxy Server: using SQL Server Authentication]
(Enter the distributor_admin user name and password on the Publishing Server)

-> [Next]-> [select the target database]-> [select the database name in which the subscription is created]/[Create a database name]

-> [Next]-> [Allow Anonymous subscription]-> [Yes, generate anonymous subscription]

-> [Next]-> [initialize subscription]-> [Yes, initialize Architecture and Data]

-> [Next]-> [snapshot transfer]-> [use the snapshot file in the default snapshot folder of the release]
(The subscription server must be able to access the publisher's REPLDATA folder. If there is a problem, you can manually set network sharing and sharing permissions)

-> [Next]-> [snapshot transfer]-> [use the snapshot file in the default snapshot folder of the release]

-> [Next]-> [set distribution Agent Scheduling]-> [use the following scheduling]-> [change]-> [for example, scheduling every five minutes]

-> [Next]-> [enable required services]-> [This subscription requires the SQLServerAgent service to be run on the Publishing Server]

-> [Next]-> [complete]-> [OK]

After the subscription is successfully created, the subscription Server adds a [REPL-distribution] job (when merging and copying, the job category is [REPL-merge]).
It will run the Database Synchronization and replication jobs according to the time schedule table we have given.

Iii. Experiment logs that may encounter exceptions after SQL Server replication is configured:

1. When the Publishing server is disconnected, the SQL server service is shut down, restarted, and shut down, it does not have much impact on the configured replication.

During the interruption, the distribution and subscription receive the transaction information that has not been copied.

2. If the distribution server is disconnected, the SQL server service is disabled, restarted, or shut down, the replication settings may be affected.

Transactions on the Publishing Server are accumulated during the interruption.
(If you set the option to delete expired subscriptions for a long time, the transaction logs of busy database publishing may expand rapidly ),

The subscription server will try again and again because it cannot access the Publishing Server.
We can set the interval between the number of retries and the number of retries (the maximum number of retries is 9999. If the number of retries is one per minute, it can be about 6.9 days without error)

The SQL server Service of the distribution server is started. After the network is connected, the accumulated jobs on the distribution server will be applied to the subscription machine in chronological order:

It takes a relatively long time (in fact, the insert, update, and delete statements of all transactions are generated and executed on the subscription server)
We tested 58 transactions on a common PC and executed 100228 commands at 7 minutes 28 seconds.

3. The subscription server is disconnected, the SQL server service is disabled, restarted, and shut down, which has a great impact on the configured replication. You may need to try again.

Our lab environment (subscription server) was shut down unexpectedly at, and then restarted at the next day,
The configured replication starts to run again after, and the stacked jobs on the publishing server will be applied to the subscription machine in chronological order.
However, the snapshot error message appears in the replication manager. The snapshot may need to be retried, and the copy may need to be restarted.
(The machines in our experiment environment have not been tested for snapshots, And the replication operation is still successful)

4. You can use the delete button to delete a created release and subscription.

We 'd better always delete and review, then delete, and finally disable the order of publishing.

To completely delete the replication settings on SQL Server, perform the following operations:

[Copy]-> right-click and choose [disable Publishing]-> [Welcome To The Disable publishing and distribution wizard]

-> [Next]-> [disable Publishing]-> [disable publishing on "@ servername"]

-> [Next]-> [complete the release and distribution disabling wizard]-> [complete]

We can also use the T-SQL command to copy the publication and subscription creation and deletion, select the published and subscription has been set, right-click the label
You can [generate an SQL script]. (I will not discuss it in detail here. The recommended website will have more details)

When you try to delete or change a table, the following error occurs:
Server: Msg 3724, Level 16, State 2, Line 1
Cannot drop the table object_name because it is being used for replication.

A typical case is that the table was used for replication, but was deleted later.

Solution:
Copy codeThe Code is as follows:
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




Question:
If the synchronization agent is stopped after the merge replication configuration is complete. I want to restart the synchronization proxy for merge replication in the program. What commands or stored procedures are used?
Solution:
Sp_start_job
Indicates that the SQL Server Agent executes the job immediately.

Example
In the following example, start a job named "Maid Backup.

USE msdb
EXEC sp_start_job @ job_name = maid Backup


2. Log restoration functions ,,,

Copy codeThe Code is as follows:
Note:
The following code demonstrates how to use the log restoration function to timely report data changes in the primary database to the standby database.
The data of the standby database can be used for query at any time, but cannot be updated (the standby database is read-only ).

First, create a database for demonstration (master database)
Create database Db_test
ON
(NAME = Db_test_DATA,
FILENAME = c: \ Db_test.mdf)
LOG ON
(NAME = Db_test_LOG,
FILENAME = c: \ Db_test.ldf)
GO

Back up databases
Backup database Db_test to disk = c: \ test_data.bak WITH FORMAT
GO

Restore the database to a backup database (this shows the synchronization between the primary database and the backup database)
Restore database Db_test_bak from disk = c: \ test_data.bak
With replace, STANDBY = c: \ db_test_bak.ldf
, MOVE Db_test_DATA TO c: \ Db_test_data.mdf
, MOVE Db_test_LOG TO c: \ Db_test_log.ldf
GO

Start the SQL Agent service
EXEC master.. xp_cmdshell net start sqlserveragent, no_output
GO

Create jobs for data training on the master server and synchronization between the backup server database
DECLARE @ jogid uniqueidentifier
EXEC msdb .. sp_add_job
@ Job_id = @ jogid OUTPUT,
@ Job_name = N data synchronization Processing

Create synchronization procedure
EXEC msdb .. sp_add_jobstep
@ Job_id = @ jogid,
@ Step_name = N data synchronization,
@ Subsystem = TSQL,
@ Command = N
Log backup in the primary database
Backup log Db_test to disk = c: \ test_log.bak WITH FORMAT

Restore the log backup of the master database in the backup database (the latest changes in the application master database)
In practice, the restoration jobs of the backup and backup databases of the primary database should be created on the primary server and the backup server respectively, and the backup files should be placed in the shared directory that can be accessed by both the primary server and the backup server.
Restore log Db_test_bak from disk = c: \ test_log.bak with standby = c: \ test_log.ldf,
@ Retry_attempts = 5,
@ Retry_interval = 5

Create Scheduling (executed once per minute)
EXEC msdb .. sp_add_jobschedule
@ Job_id = @ jogid,
@ Name = N schedule,
@ Freq_type = 4,
@ Freq_interval = 1,
@ Freq_subday_type = 0x4,
@ Freq_subday_interval = 1,
@ Freq_recurrence_factor = 1

Add target server
EXEC msdb. dbo. sp_add_jobserver
@ Job_id = @ jogid,
@ Server_name = N (local)
GO

After the above processing, the synchronization relationship between the master database and the slave database has been set up.
Next, we will test whether synchronization can be implemented.

Create a test table in the primary database
Create table Db_test.dbo.TB_test (ID int)
GO

Wait 1 minute 30 seconds (because the synchronization interval is set to 1 minute, so the latency is required to see the effect)
Waitfor delay 00:01:30
GO

Check the standby database to see if the synchronization is successful.
SELECT * FROM Db_test_bak.dbo.TB_test

Result:
ID
-

(The number of affected rows is 0)


Test successful
GO

Finally, delete all tests.
Drop database Db_test, Db_test_bak
EXEC msdb .. sp_delete_job @ job_name = N Data Synchronization
GO


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

Server File Processing instructions
This method is used to synchronize databases. When the primary database is unavailable (for example, the primary database is damaged or shut down for repair)
You can use either of the following methods to make the standby database available.



1. If the primary database is damaged and the latest logs cannot be backed up, you can directly use the following statement to make the standby database readable and writable (loss of all data after the last log restoration ).
Restore log Db_test_bak WITH RECOVERY


2. If the primary database can back up the latest logs, you can use the following statement.
Back up the latest transaction logs of the primary database
Backup log Db_test to disk = c: \ test_log.bak WITH FORMAT
Recover the latest transaction logs in the slave database and make the slave database readable and writable (upgraded to the master database)
Restore log Db_test_bak from disk = c: \ test_log.bak

Simply put:
1. You must use the specified windows user to log on to your SQL Service, instead of using the "local SYSTEM account"
2. users logging on to the SQL service must have all permissions on the shared directory.
3. If your computer is not added to the domain, you must also ensure that the login users set for the SQL service on the source and target servers are the same (the user names and passwords are the same)

Network Backup is mainly about permission settings. refer to the following Backup File Sharing directory permission settings to solve directory sharing permissions.

The following assumes the permission settings for the shared directory on which the database on server A is backed up to server B (the two servers should be in the LAN and allow shared directory access )::

1. Machine A and machine B create A windows user with the same name. Set the user group to "administrators" and set the same password as the valid user accessing the backup folder. The operation is as follows:
My computer
Control Panel
Management tools
Computer Management
Users and groups
Right-click User
Create user
Create a windows login user affiliated to the administrator Group

2. Create a new shared directory on the B server as the directory for storing backup files:
My computer D: \ creates a directory named BAK
Right-click the new directory
Attribute sharing
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 start user of the MSSQLSERVER and SQLSERVERAGENT services
Start the program management tool Service
Right-click MSSQLSERVER
Select "this account" for property Login"
Enter or select the windows login user name created in step 1
Enter the user's password in "password"
OK
Set SQLSERVERAGENT in the same way

4. Map the BAK directory of machine B on machine

5. Run the following statement in the analyzer to check whether the statement is successful:
The drive letter mapped by exec master .. xp_mongoshell dir

6. Prepare A backup plan on server

Note: to create a new user, the startup account of the MSSQLSERVER service must have the same name and password as the valid access to the shared directory, in this way, you can pass the verification (so you can use another valid user instead, only the user name and password must be the same and have sufficient permissions)