April 1, 2010 at 7:32 am
Hi all, I would like to know which is the best method (as performance) to import data from a remote SQL server to another SQL server.
We have a list of tables to import, and in a part of that tables the entire table, in other cases we only select a part of the data in these.
We declared the remote server as linked server and actually we're using the DTS to import the data.
The DTS is working and is quick but very frequently the process is creating locks in tempdb.
I want to abandon the DTS and find another solution also if probably has not the same performances.
So, if not the DTS, what is the best method ?
I think that OPENQUERY is a good solution but I want to know if there are better solutions.
So here you can find the version of the source and destination SQL server.
Source:
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
Destination:
Microsoft SQL Server 2005 - 9.00.4035.00 (X64)
Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
Thank in advance for your help.
Lorenzo
April 1, 2010 at 9:10 am
I use several methods for doing this including SSIS, OPENROWSET, C#, and replication. I'll discuss the factors that lead me in one direction or another briefly.
If the source table never gets updated/deleted, but only inserted into and it has an identity column I like OPENROWSET. The reason being that I can just select the records after the max(id) in the destination table.
If there are several tables and they have primary keys, but they also get updated I like replication. Replication offers near real time synchronization and is relatively easy to configure and manage. I like it because you don't have to reload the entire table, you only get the data modifications. However, this is not a great option if large portions of the table get updated/deleted regularly.
SSIS/C# are best for when there is complex logic involved. However, I don't know why you would be getting extensive tempdb locking for straight data copying. Is the locking causing blocking/waiting? If so what are the objects associated?
April 24, 2010 at 2:26 am
Hello Toby,
the problem I have with the locking is quite strange, because the DTS is scheduled each day at 18.00 but the locking does not happen each day (by luck) but once a week or more.
The last time happened I tried to check the objects involved in the locking, but I had not time to verify deeply the situation because the lock was blocking production batches so I had to re-start the SQLserver service in hurry to solve the locking.
The re-start of the SQL service is the only solution I found, because killing the DTS does not unlock tempd and the killed process does not close.
What I saw is this:
Database: "tempdb";
Status: "SUSPENDED";
Command: "SELECT INTO";
Wait type: "LCK_M_SCH_M";
Resource: "objectlock lockPartition=0 objid=346497059 subresource=FULL dbid=2 id=lock636d87180 mode=Sch-M associatedObjectId=346497059";
in the same time another process run by a linked server was in this state:
Database: "tempdb";
Status: "SUSPENDED";
Command: "KILLED/ROLLBACK";
Wait type: "TEMPOBJ";
Last batch: "sp_table_Statistics2_rowset;1"
I don't understand what's happening, but seems that the process run by the linked server was reading or writing statistics tables in tempdb.
I don't know if this informations are relevant...
Regards,
Lorenzo
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply