| Author |
Topic |
|
CanadaDBA
583 Posts |
 Posted - 2007-11-13 : 09:59:43
|
| SQL Server 2000.I want to copy all the tables from one database to another database on same server using EM. I noticed, when I do the work on the server it copies much faster than when I set EM on my desktop to do the work on that server. Why? In my Desktop case, does the data transfer through network from the server to my machine and back to the server? If yes, that's crazy!Canada DBA |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-11-13 : 10:39:37
|
Beacuse when on server, you copy from server to EM and back to server which is same machine.When using EM on workstation (different machine), you copy from the server over the network to EM on your workstation and then back to server. You get some latency due to network traffic.Nothing stange about this. The "Copy Operation" is done at the same machine where you are instantiating.
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
CanadaDBA
583 Posts |
Posted - 2007-11-13 : 10:47:28
|
| That's why I say EM is not smart. It shouldn't increase network traffic. It was taking over 20 minutes and still was half a way of copying through my desktop and I aborted and ran on the server which took about a minute!!!Canada DBA |
|
|
|
Haywood
Posting Yak Master
221 Posts |
Posted - 2007-11-13 : 10:49:56
|
| Enterprise Manager is not a data entry tool, it is more of a (server/database/job) management tool. The usual method for this moving data process is either DTS/SSIS or custom T-SQL scripts using BCP... |
|
|
|
elancaster
A very urgent SQL Yakette
1208 Posts |
Posted - 2007-11-13 : 10:50:51
|
| it makes sense to me that it works that way, it's the same with DTS of course. if you run a package on your local machine it's your machine doing the work... run it on the server and the server will do the work.Em |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-11-13 : 10:51:46
|
It's not EM that is not smart. How do you think EM is copying the records? With magic?I think EM uses DTS in background to perform this task.It is exactly when you copy a file with Windows Explorer from one network drive to another. The filedata is copied to your machine first and then back to the target network drive.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
CanadaDBA
583 Posts |
Posted - 2007-11-13 : 10:52:58
|
Do you know if DTS/SSIS runs ON the server instead of increasing network traffic? quote:
Originally posted by Haywood
Enterprise Manager is not a data entry tool, it is more of a (server/database/job) management tool. The usual method for this moving data process is either DTS/SSIS or custom T-SQL scripts using BCP...
Canada DBA |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-11-13 : 10:54:18
|
If this copying is run often, you can create a DTS package on the source server to copy the data to the target server directly. And all you have to do is to start and run the package remotly.This is because now it is not longer a "on the fly" operation. It is well defined in the package.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
CanadaDBA
583 Posts |
Posted - 2007-11-13 : 10:54:32
|
| How about Query Analyzer? If I do the copy does it do the work ON the server?Canada DBA |
|
|
|
Haywood
Posting Yak Master
221 Posts |
Posted - 2007-11-13 : 10:56:58
|
| Yes, if you use QA and local (to the server) or other network resources the traffic is from the server to the resource. QA serves only as a 'controller' in that aspect. Now, if you're selecting huge amounts of data in a result set back to QA for display, then yes, you will consume network resources. |
|
|
|
CanadaDBA
583 Posts |
Posted - 2007-11-13 : 13:47:03
|
| Well, I have a test database on the prod server and once in a while have to drop all tables on it and copy all the tables from prod database. I always do it ON the server but today tried to do it through my desktop and found that network traffic is involved. Thanks for the posts!Canada DBA |
|
|
|
|
Enterprize Manager is not smart