In SQL Server2005, there are some conflicts between addition, deletion, modification, and query, which can easily lead to deadlocks. Currently, the FileDetail table in a database is operated by two sets of programs. One program is responsible for addition, deletion, and modification, and the other is responsible for query. When the data volume is large, it is easy to cause the program to query data very slowly, or even cause a deadlock. The reason is that the design mechanism in SQL Server2005 is that when a deadlock record is inserted, the deadlock record will only be one, and will not affect the next record and the previous record, but the tangle is coming, when you insert is good, does not affect you, but when I query, if you find a record is deadlocked, then the result of my T-SQL query is all deadlocks, nothing can be found.
To solve this problem, we can use synchronization to solve this problem. To separate this database, we create two FileDetail tables, one for the add, delete, and modify programs, another query program can be called to solve the above problems.
In SQL Server2005, data synchronization requires the publishing server, distribution server, and subscription server. The source data is located on the publishing server, which is responsible for data publication. The publisher copies the changes to the data or objects to be copied to the distributor. The distributor receives all changes to the data. It contains a distribution database and stores the changes, then, distribute these changes to the subscription server. During the replication process, the publishing server is a database instance that provides data to other locations through replication. The distribution server is also a database instance and plays a role in the storage zone. The subscription server is a database instance that receives replicated data. A subscription server can receive data from multiple publishing servers.
In the following example, because I don't have so many resources now, I only use the local machine for testing. I also tested the operations between two servers. If you are interested, you can try multiple servers.
Now we can use SQL Server2005 to synchronize data:
First, enable the SQL Server proxy (disabled by default)
Starting... started successfully.
Now let's copy the database.
Right-click siccdb and choose task> Copy database. The following page is displayed.
Next Step
SQL Server Authentication is used here. It is generally recommended that you use this method to ensure security and avoid errors. After filling in the information, go to the next step.
The target Server also uses SQL Server authentication. If you want to use the database of another computer in the same network segment, you can select your remote computer as the target server. Note: the IP address and alias are not supported for remote computer connection, you must use the DoMain login name (that is, the DoMain computer login name ). Here I am talking about the problems I encountered during the test of synchronization between the two computers. I find that I cannot log on?
Cause
Change your computer name
Available
SELECT * FROM Sys. SysServers to query the name of the source server in the database
I am WILCOM-DEV_SQLS here
Ping the IP address of the server. If you cannot log on remotely, you can configure the IP address in the hosts file under C: WINDOWSsystem32driversetc.
Next step:
Here you can choose according to the actual situation. The default is the first type. I select the second type here, because my source database cannot be disconnected (a major event will happen if it is disconnected, huh, huh, make a joke:
By default, the database you want to publish is selected. The database must be in the target state before it can be published. Okay, I have no problem here. Next step:
Here is the target database. I changed the name of the target database to siccdb_bak.
Next, if the target database already exists, select first and next.
Do not change the value here. Keep the default value. Next Step
I will not select a plan here. You can try the plan execution status on your own.
If you find that the following integration Services proxy account is not allowed to be selected and is in a gray prohibited state, we recommend that you check whether your SQL Server Service has installed this. If not, it is a sad reminder, run the SQL Server installation wizard again to reinstall the service. (The service shown in the following figure)
Next, the next step
This can be done here.
OK. The copy is complete.
Refresh the database and we will find an additional siccdb_bak
Now, let's publish this siccdb database so that siccdb_bak can be synchronized with it.
Select copy-local release-new release under the Database Directory at the same level to start the release wizard.
Next step:
The first option is selected by default.
Here is the directory where the snapshot files are stored. By default, you can find them later. Next step:
Here we select the database to be released, and next step:
Here we choose to release snapshots. Other options can be compared with the instructions in the preceding figure. I believe everyone is very smart. Next step:
Here we expand the table to find all the tables in this database. We can check the table as needed. Now, next:
Here we can also add SQL statements to the selected table for filtering, because I cannot use them here, so we will not demonstrate it for you, saving time and writing a tutorial quickly. Next step:
Here we need to check the "now" and "plan" options, and then select "change".
I need real-time synchronization here, so I chose to synchronize every minute every day. You can choose according to the actual situation. OK.
Now let's check whether it is the plan you selected. Next:
Select security settings,
I have already mentioned the above information. If you do not know it, refer to my previous instructions. After filling in, OK.
Next step:
Release now. Next step:
Enter the release name.
OK. Everything is normal. We disable it.
A [siccdb] is added to the local release: TOB is the release we just created. Now we need to subscribe to it, right-click local subscription, and choose create subscription:
Open the subscription wizard. Next step:
Here we find our release source. Next step:
Here, select [push subscription], which is simple and does not delay time. Next step:
Here we select the subscription server and the subscription database. Next step:
Here we select the connection with the distribution server and the subscription server:
After filling in, OK.
Select continuous operation. Next step:
Select continuous operation. Next step:
Create a subscription. Next step:
Okay, now it's done.
Now we add, delete, and modify some data in siccdb. One minute later we will find that siccdb_bak also updates the data synchronously.
