Now I have a need for data access and updates between two different servers in SQL Server 2012. We need to insert the data from server one into the server's servers and perform the task once a day. Our preferred solution is to build between two servers
link Server, below describes how to establish a linked server. the thing I need to do is, I need to plug the server one data into server one in a timed, full amount. So here comes the need to delete the information from the table in server one, because we all know that the delete table needs to be logged, and he is doing it for rollback. So log in detail. I don't need to write a log because I have a bigger table and I delete it every day. So I don't want to use the delete operation, I want to use TRUNCATE to delete the table. This is the interesting point of the story.
configuration of two servers:Server One:DB version:2012Server ip:192.168.21.252Souce Database
Server:DB version:2012Server ip:192.168.25.210Target Database
We can set up a linked server on any server, but there are subtle differences, the following look at my data in the time of the problem encountered, this problem let me know that on any server to establish a linked server is different. Method One: I set up a linked server on the source database server, I want to let the source database actively push the data into the target database.
1, establish a linked serverestablish a linked server on the source database server as follows:
Note: The linked server ip:192.168.25.210 is my target database, and the linked server is established to directly access the database tables on the 192.168.25.210 server. Because I am a SQL Server 2012 to SQL Server 2012 database, the server type selects SQL Server.
you need to add a login mapping for the local server to log on to the remote server.
This time we saw a 192.168.25.210 linked server.
You can test if the linked server is linked successfully.
2, set up a job on the source database server to push the data to the target database actively. A, new job creation
b, editing steps
my SQL statements from the source database push data to the target database are:TRUNCATE TABLE [192.168.25.210]. BI. [dbo]. [Biu8_gl_accvouch]
Go
insert into [192.168.25.210]. BI. [dbo]. [Biu8_gl_accvouch] SELECT * from BI. Dbo. [Biu8_gl_accvouch]
Go
C, New Job schedule
I changed the execution time of the plan and asked him to execute the job immediately. Here's the question. Such as:
Below I put the script in the job in the Query Analyzer executed, the results of the implementation of I am very confused. Such as:
You can see from the error message that this statement is problematic and cannot be executed. But the syntax of this statement is not wrong. TRUNCATE TABLE [192.168.25.210]. BI. [dbo]. [Biu8_gl_accvouch], I started to wonder if I could not execute the SQL statement so I executed the following statement
From the above you can see that the link database is written correctly, because delete from [192.168.25.210]. BI. [dbo]. [Biu8_gl_accvouch] Deletion of the target database indicates success. Because I said, I don't want to delete the data table with delete, which will produce a lot of logs. I want to delete the data with truncate. So I'm going to do it again with a method.
method Two: I set up a linked server on the target database server, and I want the target database to proactively pull data from the source database into its own database. 1, on the target library server (192.168.25.210) to establish a linked server, connection server (192.168.21.252) For example, please refer to the above method.
2. Test if the linked server is linked successfully3, establish a job on the target database server to actively pull the source database data into its own database. You can refer to the above steps for details on how to create a job. 4, newly established job name: pull_data_from_target_db5, the specific job step code such as:The SQL statement I pull the source database from the target database is:TRUNCATE TABLE BI. [dbo]. [Biu8_gl_accvouch]
Go
INSERT INTO BI. [dbo]. [Biu8_gl_accvouch] SELECT * FROM [192.168.21.252]. BI. Dbo. [Biu8_gl_accvouch]
Go
6, new job creation plan
7. Check the job execution status as follows:
from the above 2014/12/19 9:48:02 is what I just executed, it successfully executed the job. You can see that the following statement is successful. TRUNCATE TABLE bi.[ DBO]. [Biu8_gl_accvouch]
Go
INSERT INTO BI. [dbo]. [Biu8_gl_accvouch] SELECT * FROM [192.168.21.252]. BI. Dbo. [Biu8_gl_accvouch]
Go
Therefore, from the above operation can draw the following very important conclusion:
There are two features of the linked server:
1: You cannot delete (including truncate) any alignment of the source server through the linked server.
2: Can cross the linked server can insert,updae,delete the table of the linked source server.
Experience sharing--->SQL Server 2012 establishing a linked server
