Diagram of SQL Server database synchronization replication

Snapshot replication: By setting a fixed time period for replication, the time cycle range can be set arbitrarily between one minute to one months, is the most stable of three replication functions.

Things to replicate: through real-time monitoring of database updates, each update to the data and database objects to replicate, real-time performance is best, but the SQL Server Personal version can not provide things to replicate, Enterprise version although this feature, but not the data content to filter, practicality and stability are lower than snapshot replication.

Merge replication: Any one database update between two databases causes another database to be updated with poor security and is often less used.

Server-side configuration:

1. Create a new shared directory

Will D:\Program Files\Microsoft SQL Server\mssql10. Mssqlserver\mssql\repldata share, add everyone user and give all permissions.

You need to write a UNC path in sql: \\SYSSYS\repldata (test under Run to see if you can access);
2, set up an FTP site, using the FTP service access to the snapshot files (in the way of shared files access to the snapshot file, for unknown reasons)

The physical path is: D:\Program Files\Microsoft SQL Server\mssql10. Mssqlserver\mssql\repldata

IP address is all unassigned; SSL selection None

Web page Input ftp://10.19.1.54/Verify that you have access to the folder; If the server has a firewall, you need to set FTP as an exception
3. Publish snapshot files (in case of things published)

Enter the user name and password for the publisher, such as the local publisher: sa,123
4, right click Publish file-attribute-ftp snapshot

Set as shown above, OK.
Subscription-side configuration:

1. New Subscription

Select Client as Subscriber

Here, enter the user name of the publisher, password
2, refresh the database can be synchronized results; Large amount of data synchronization time will be relatively longer
Description

1, publish and subscribe can not use IP connection database, need to connect the database directly with the computer name, in order to execute publish and subscribe

2, LAN can be used to publish and subscribe to the publisher (called Push), if similar to the network between the Internet and 218 must be published under the publisher (218) snapshot files, in the client (government Internet) subscription
Problem:
1. SQL Server replication needs to have a real name in order to connect to the server, please specify the actual server name. There are two cases found:
1 their own computer name has been modified, resulting in inconsistent with the server.
Solve:
execute the following statement in SQL Server:
Use Mastergoselect @ @servername; select serverproperty (' servername ')
Use the above statement to query the name of the server and the name of the actual computer, and if the two are inconsistent, you need to modify it. Inconsistencies execute the following statement:
Sp_dropserver ' Win-eoj9mm0see7 '
Go
sp_addserver ' xcyl ', ' local ' after the modification, restart the SQL Server service.
2 in the direct use of IP address to connect the remote server, if you do not use the alias to connect, it will appear above this error prompt box.
Solve:

Add "Alias" to the remote server and use aliases instead of IP addresses to connect to the remote database server. Open the SQL SERVER2008 Configuration Manager and navigate to the alias Service option, as follows:

Right----> New alias:

This "alias" here uses the server name, which is the name queried by the SQL statement above: (select @ @servername;), the IP address is the access address of this server.
Then add the appropriate IP address and alias to the hosts on this machine:
C:\Windows\System32\drivers\etc

Hosts
IP address Alias
For example:
10.19.1.54 Syssyssingle

Finish modifying, saving, and restarting SQL Server services. Connect again, use "Alias" instead of IP address to connect to the server can be (with alias login not line, also prompts to need computer name);

At this point, solve the problem!
2, unable to delete the database ' XXX ' because it is being used for replication
Solution: sp_removedbreplication ' Newsql '
DROP DATABASE Newsql
3, cannot execute as the database body, because the principal "dbo" does not exist
Solve:
Use Mete_data;
EXEC sp_changedbowner ' sa ';
4, sometimes the client and server are not in the same LAN, it is very likely that the server name can not be used directly to identify the server, at this time we may use the Hosts file for the name resolution
Solve:
1 Use Notepad to open the Hosts file (usually under C:\Windows\System32)
2 Add a corresponding record of IP address and server name, such as: 218.205.197.58 xcyl 10.9.34.111 gxx-pc
5, sometimes can not modify the table design, set as follows: Remove the check mark

Other:

 1, router port mappings
 1 not on a network segment does not matter, the key is the network does not pass, the net does not pass all white pull.
 2) Netcom also have to check whether 1433 ports are available, the server installed SQL Server 2000 must be 1433 ports open, the middle of the router firewall what also have to open 1433 ports, SQL Server 2000 the default is 1433 Port
 2, can use Telnet server IP 1433来 in the client DOS to check whether can access the server 1433 ports
 3, Remote control between the teamviewer:internet, similar to the QQ remote