[Source] SQL Server syncs databases across servers or synchronizes them regularly

1. Create a database (called dnt_new here) on the database you want to publish, and then right-click on "Copy" - "Local Publish" in the left navigation of the database instance, and select "New Release", as follows:

  In this way, the system will launch the 'Publish Wizard' to guide everyone, click 'Next', and then select the database you want to publish in the current window, as follows:

Click Next, and then select "Issue Release" in the next window, as shown below:

Then click Next and select the data objects (data tables, stored procedures, custom functions, etc.) to synchronize, as follows:

Then there is the "Project Problem Window", because you have logged in as DBO before, so you just need to click Next, as shown in the figure below:

Here you can filter the data information to be synchronized by "add", because the whole table data is synchronized, so it is not set here

Then in the 'Proxy Security' window, click on the 'Security Settings' button:

Set the following information in the 'Security Settings' sub-window that pops up, and click the 'OK' button:

Then click the Next button:

Select the Create Publish checkbox and click Next, where the wizard will let you enter a Publishing Name, named dnt_new_snap here:

Click the "Finish button", and the system will start to create the publishing target information based on the previously collected information, as follows:

  At this point, the work of 'creating a release' is complete. The following describes the process of creating a subscription. On another machine's sqlserver instance, open the instance and use Replication - New Subscription, as shown in the following image:

The system will start the "New Subscription" wizard, click Next, and select the "Check and publish sqlserver server" item in the "Publish" drop-down box in the "Publish" window, as follows

Then select 'Create the database instance used for publishing' in the pop-up window and log in, then the publisher information will appear in the following list box:

Select the publishing object "dnt_new_snap" we created earlier, and click Next:

In the Distribution Proxy Location window, select Run All Proxies on Distribution Servers, click Next, and select the name of the subscription database to be synchronized in the Subscription Database list box in the Subscriber Servers window (you can create a new one):

Click Next, and then in the 'Distribution Proxy Security' window, click '...' in the list box to set the installability, and do the following settings (note the red box):

Then click the "OK" button, and then click the "Next" button on the wizard, then the system will display the "Agent Plan Execution Method" window, select "Continuous Run" or custom time (customization can achieve scheduled backup data):

Click Next and select "Execute Now" in the window:

If everything is running normally, sqlserver will synchronize the previously specified data tables and stored procedures from the 'publisher' to the current 'subscription database'. At this time, we can add or modify the specified table data information in the table on the source database (publisher), and after waiting for 1-3 seconds (or a set time), the additions and modifications will be synchronized to the corresponding table on the 'subscription database'

Note: The two machines in this article must be connected using the sqlserver client (set 'Allow remote link' in sqlserver studio, set the corresponding IP address, and enable the TCP/IP protocol in the configuration manager)

Notes:

LAN SQL remote connection method:

SQL2005 SQL2008 remote connection configuration method

Step 1 (SQL2005, SQL2008):

Start --> program -->Microsoft SQL Server 2008 (or 2005) --> configuration tool --> SQL Server configuration manager --> SQL Server network configuration --> MSSQLSERVER protocol (this name is based on the specific instance name) -->TCP/IP--> right-click --> enabled

Step 2:

SQL2005:

Start--> Program-->Microsoft SQL Server 2005--> Configurator-->SQL Server 2005 Peripheral Application Configurator--Peripheral Application Configurator for > Services and Connections -->Database Engine--> Remote Connection, select Local Connection and Remote Connection, and select Use both Tcp/IP and named pipes.

SQL2008:

Open SQL Server Management Studio --> select the first item (main database engine) in the left [Object Explorer] -->right-click --> Aspect --> select [Peripheral Application Configurator] in the drop-down list of aspects -> set RemoteDacEnable to True.

Express：

If XP has a firewall, add the following two programs to the exception:
C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBinnsqlservr.exe,
C:Program FilesMicrosoft SQL Server90Sharedsqlbrowser.exe

Not only should the Windows firewall be turned off, but also the antivirus firewall.

Step 3:

Start --> Program-->Microsoft SQL Server 2008 (or 2005)--> Configuration Tool-->SQL Server Configuration Manager-->SQL Server Service-->Right-click SQL Server (MSSQLSERVER) (Note: the specific instance name is subject to the specific instance name in parentheses)--> restart