Long standing debate...please tell me your thoughts

Ok, here's the situation. Our MIS department recently relocated to a new
office space about 5 miles away from our database servers. We used to be in
the same building.
We have about 10 different servers over there. For this example...let's say
two of those are called DevData and ProdData.
Since we've moved over here, whenever we have to copy data between the two
servers...it takes much, much longer. I would guestimate that it takes 2 or
3x as long to transfer data. Remember, both servers are located over there
(right next to each other).
When we questioned our Network Services God, he informed us that any time we
transfer data from Dev to Prod that it has to first travel over here before
going to the destination server.
Can someone shed some light on this?
Thanks so much.
JenIt first has to travel back to your office because they have
it configured that way. His team screwed up. You wouldn't
want your network database traffic going outside the
local area network of your database "if" you can help it
for performance and security reasons.
--
2004 and 2005 Microsoft MVP C#
Robbe Morris
http://www.masterado.net
Earn $$$ money answering .NET Framework
messageboard posts at EggHeadCafe.com.
http://www.eggheadcafe.com/forums/merit.asp
"Jennifer" <Jennifer@.discussions.microsoft.com> wrote in message
news:CED31D68-2751-44E9-A48B-2BE9315A52B9@.microsoft.com...
> Ok, here's the situation. Our MIS department recently relocated to a new
> office space about 5 miles away from our database servers. We used to be
> in
> the same building.
> We have about 10 different servers over there. For this example...let's
> say
> two of those are called DevData and ProdData.
> Since we've moved over here, whenever we have to copy data between the two
> servers...it takes much, much longer. I would guestimate that it takes 2
> or
> 3x as long to transfer data. Remember, both servers are located over
> there
> (right next to each other).
> When we questioned our Network Services God, he informed us that any time
> we
> transfer data from Dev to Prod that it has to first travel over here
> before
> going to the destination server.
> Can someone shed some light on this?
> Thanks so much.
> Jen
>|||Well..I totally agree with you and that has been my argument all along. :)
So the big question is...is this something that can be configured in SQL
Server or is this more a general network issue?
I would LOVE to be able to tell this 'God' (oh..he's *that* bad)..that this
is their issue and explain to him what he can do to fix it. Oh that would
make my day.
BTW - I'm an ex-Excel MVP (used to participate a lot a few years ago).
Thanks for answering so quickly and any additional information would be
great. If I ever make it back to the Summit, I'll buy you a beer.|||How are you copying data between the servers? If you are using a DTS
package, data are pumped through the machine running the package.
Consequently, if you run the DTS package on your PC using EM, all data
passes through your PC. You can invoke the package from a job on either the
source or target server to eliminate the extra hop.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Jennifer" <Jennifer@.discussions.microsoft.com> wrote in message
news:CED31D68-2751-44E9-A48B-2BE9315A52B9@.microsoft.com...
> Ok, here's the situation. Our MIS department recently relocated to a new
> office space about 5 miles away from our database servers. We used to be
> in
> the same building.
> We have about 10 different servers over there. For this example...let's
> say
> two of those are called DevData and ProdData.
> Since we've moved over here, whenever we have to copy data between the two
> servers...it takes much, much longer. I would guestimate that it takes 2
> or
> 3x as long to transfer data. Remember, both servers are located over
> there
> (right next to each other).
> When we questioned our Network Services God, he informed us that any time
> we
> transfer data from Dev to Prod that it has to first travel over here
> before
> going to the destination server.
> Can someone shed some light on this?
> Thanks so much.
> Jen
>|||Dan...usually it's just via Import/Export via EM. However, we do have
packages also. The packages are actually stored on the Dev or Prod database.
We don't do anything 'local'.
So..in that case, you are saying that unless we use Terminal Services or are
at the physical location of the source/destination, data does go through your
local machine?
"Dan Guzman" wrote:
> How are you copying data between the servers? If you are using a DTS
> package, data are pumped through the machine running the package.
> Consequently, if you run the DTS package on your PC using EM, all data
> passes through your PC. You can invoke the package from a job on either the
> source or target server to eliminate the extra hop.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Jennifer" <Jennifer@.discussions.microsoft.com> wrote in message
> news:CED31D68-2751-44E9-A48B-2BE9315A52B9@.microsoft.com...
> > Ok, here's the situation. Our MIS department recently relocated to a new
> > office space about 5 miles away from our database servers. We used to be
> > in
> > the same building.
> >
> > We have about 10 different servers over there. For this example...let's
> > say
> > two of those are called DevData and ProdData.
> >
> > Since we've moved over here, whenever we have to copy data between the two
> > servers...it takes much, much longer. I would guestimate that it takes 2
> > or
> > 3x as long to transfer data. Remember, both servers are located over
> > there
> > (right next to each other).
> >
> > When we questioned our Network Services God, he informed us that any time
> > we
> > transfer data from Dev to Prod that it has to first travel over here
> > before
> > going to the destination server.
> >
> > Can someone shed some light on this?
> >
> > Thanks so much.
> >
> > Jen
> >
>
>|||Jennifer wrote:
> Dan...usually it's just via Import/Export via EM. However, we do have
> packages also. The packages are actually stored on the Dev or Prod
> database. We don't do anything 'local'.
> So..in that case, you are saying that unless we use Terminal Services
> or are at the physical location of the source/destination, data does
> go through your local machine?
> "Dan Guzman" wrote:
>> How are you copying data between the servers? If you are using a DTS
>> package, data are pumped through the machine running the package.
>> Consequently, if you run the DTS package on your PC using EM, all
>> data passes through your PC. You can invoke the package from a job
>> on either the source or target server to eliminate the extra hop.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Jennifer" <Jennifer@.discussions.microsoft.com> wrote in message
>> news:CED31D68-2751-44E9-A48B-2BE9315A52B9@.microsoft.com...
>> Ok, here's the situation. Our MIS department recently relocated to
>> a new office space about 5 miles away from our database servers.
>> We used to be in
>> the same building.
>> We have about 10 different servers over there. For this
>> example...let's say
>> two of those are called DevData and ProdData.
>> Since we've moved over here, whenever we have to copy data between
>> the two servers...it takes much, much longer. I would guestimate
>> that it takes 2 or
>> 3x as long to transfer data. Remember, both servers are located
>> over there
>> (right next to each other).
>> When we questioned our Network Services God, he informed us that
>> any time we
>> transfer data from Dev to Prod that it has to first travel over here
>> before
>> going to the destination server.
>> Can someone shed some light on this?
>> Thanks so much.
>> Jen
Yes, it will go through the local machine because SQL EM is on the local
machine. If you set up a job to do this using the SQL Server Agent, it
will stay on the server. This, of course, assumes that both servers can
talk to one another through a local switch and not have to travel back
to the main building.
This reminds me of the beloved Netware copy utility I used to use in the
past called NCOPY. If you issued NCOPY from the local PC to copy a file
from the Netware file server to another location, NCOPY actually kicked
off a remote server copy to avoid having to bring the data to client
first.
Copyting is not like that with Windows. If you have a file on the file
server and open a local command window or Explorer session and
copy/paste the file to the same folder, it all comes to the client first
because the client is performing the copy.
In your situation, it sounds like you need to remote into the server or
kick off a server process that starts the copy (the data copied needs to
be on the server as well).
David Gugick
Quest Software
www.imceda.com
www.quest.com|||> So..in that case, you are saying that unless we use Terminal Services or
> are
> at the physical location of the source/destination, data does go through
> your
> local machine?
This is true when you run the package remotely via EM. The source location
of the package doesn't matter because it is loaded on the machine running
the package before execution. As David and I mentioned, another alternative
is to run the package as a SQL Agent job so that it executes on the source
or target server.
I also work with remote servers and find that it pays to be mindful of
network bandwidth with voluminous data-transfer tasks. Remote Desktop is
your friend :-)
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Jennifer" <Jennifer@.discussions.microsoft.com> wrote in message
news:B757F600-B389-4EF6-9C2B-4DAB81F05B8E@.microsoft.com...
> Dan...usually it's just via Import/Export via EM. However, we do have
> packages also. The packages are actually stored on the Dev or Prod
> database.
> We don't do anything 'local'.
> So..in that case, you are saying that unless we use Terminal Services or
> are
> at the physical location of the source/destination, data does go through
> your
> local machine?
> "Dan Guzman" wrote:
>> How are you copying data between the servers? If you are using a DTS
>> package, data are pumped through the machine running the package.
>> Consequently, if you run the DTS package on your PC using EM, all data
>> passes through your PC. You can invoke the package from a job on either
>> the
>> source or target server to eliminate the extra hop.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Jennifer" <Jennifer@.discussions.microsoft.com> wrote in message
>> news:CED31D68-2751-44E9-A48B-2BE9315A52B9@.microsoft.com...
>> > Ok, here's the situation. Our MIS department recently relocated to a
>> > new
>> > office space about 5 miles away from our database servers. We used to
>> > be
>> > in
>> > the same building.
>> >
>> > We have about 10 different servers over there. For this
>> > example...let's
>> > say
>> > two of those are called DevData and ProdData.
>> >
>> > Since we've moved over here, whenever we have to copy data between the
>> > two
>> > servers...it takes much, much longer. I would guestimate that it takes
>> > 2
>> > or
>> > 3x as long to transfer data. Remember, both servers are located over
>> > there
>> > (right next to each other).
>> >
>> > When we questioned our Network Services God, he informed us that any
>> > time
>> > we
>> > transfer data from Dev to Prod that it has to first travel over here
>> > before
>> > going to the destination server.
>> >
>> > Can someone shed some light on this?
>> >
>> > Thanks so much.
>> >
>> > Jen
>> >
>>|||Dan would the same be true if calling a remote DTS Package through the local
command prompt. Dont know why this would make a difference but I just
thought I would ask.
CR
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:edqu%23WjfFHA.2156@.TK2MSFTNGP14.phx.gbl...
>> So..in that case, you are saying that unless we use Terminal Services or
>> are
>> at the physical location of the source/destination, data does go through
>> your
>> local machine?
> This is true when you run the package remotely via EM. The source
> location of the package doesn't matter because it is loaded on the machine
> running the package before execution. As David and I mentioned, another
> alternative is to run the package as a SQL Agent job so that it executes
> on the source or target server.
> I also work with remote servers and find that it pays to be mindful of
> network bandwidth with voluminous data-transfer tasks. Remote Desktop is
> your friend :-)
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Jennifer" <Jennifer@.discussions.microsoft.com> wrote in message
> news:B757F600-B389-4EF6-9C2B-4DAB81F05B8E@.microsoft.com...
>> Dan...usually it's just via Import/Export via EM. However, we do have
>> packages also. The packages are actually stored on the Dev or Prod
>> database.
>> We don't do anything 'local'.
>> So..in that case, you are saying that unless we use Terminal Services or
>> are
>> at the physical location of the source/destination, data does go through
>> your
>> local machine?
>> "Dan Guzman" wrote:
>> How are you copying data between the servers? If you are using a DTS
>> package, data are pumped through the machine running the package.
>> Consequently, if you run the DTS package on your PC using EM, all data
>> passes through your PC. You can invoke the package from a job on either
>> the
>> source or target server to eliminate the extra hop.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Jennifer" <Jennifer@.discussions.microsoft.com> wrote in message
>> news:CED31D68-2751-44E9-A48B-2BE9315A52B9@.microsoft.com...
>> > Ok, here's the situation. Our MIS department recently relocated to a
>> > new
>> > office space about 5 miles away from our database servers. We used to
>> > be
>> > in
>> > the same building.
>> >
>> > We have about 10 different servers over there. For this
>> > example...let's
>> > say
>> > two of those are called DevData and ProdData.
>> >
>> > Since we've moved over here, whenever we have to copy data between the
>> > two
>> > servers...it takes much, much longer. I would guestimate that it
>> > takes 2
>> > or
>> > 3x as long to transfer data. Remember, both servers are located over
>> > there
>> > (right next to each other).
>> >
>> > When we questioned our Network Services God, he informed us that any
>> > time
>> > we
>> > transfer data from Dev to Prod that it has to first travel over here
>> > before
>> > going to the destination server.
>> >
>> > Can someone shed some light on this?
>> >
>> > Thanks so much.
>> >
>> > Jen
>> >
>>
>|||The same applies when the package is run with the DTSRUN command-prompt
utility on your PC. However, if you invoke DTSRUN using xp_cmdshell, it
will run on the server.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"ChrisR" <noemail@.bla.com> wrote in message
news:%23IOYFnlfFHA.3252@.TK2MSFTNGP10.phx.gbl...
> Dan would the same be true if calling a remote DTS Package through the
> local command prompt. Dont know why this would make a difference but I
> just thought I would ask.
> CR
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:edqu%23WjfFHA.2156@.TK2MSFTNGP14.phx.gbl...
>> So..in that case, you are saying that unless we use Terminal Services or
>> are
>> at the physical location of the source/destination, data does go through
>> your
>> local machine?
>> This is true when you run the package remotely via EM. The source
>> location of the package doesn't matter because it is loaded on the
>> machine running the package before execution. As David and I mentioned,
>> another alternative is to run the package as a SQL Agent job so that it
>> executes on the source or target server.
>> I also work with remote servers and find that it pays to be mindful of
>> network bandwidth with voluminous data-transfer tasks. Remote Desktop is
>> your friend :-)
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Jennifer" <Jennifer@.discussions.microsoft.com> wrote in message
>> news:B757F600-B389-4EF6-9C2B-4DAB81F05B8E@.microsoft.com...
>> Dan...usually it's just via Import/Export via EM. However, we do have
>> packages also. The packages are actually stored on the Dev or Prod
>> database.
>> We don't do anything 'local'.
>> So..in that case, you are saying that unless we use Terminal Services or
>> are
>> at the physical location of the source/destination, data does go through
>> your
>> local machine?
>> "Dan Guzman" wrote:
>> How are you copying data between the servers? If you are using a DTS
>> package, data are pumped through the machine running the package.
>> Consequently, if you run the DTS package on your PC using EM, all data
>> passes through your PC. You can invoke the package from a job on
>> either the
>> source or target server to eliminate the extra hop.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Jennifer" <Jennifer@.discussions.microsoft.com> wrote in message
>> news:CED31D68-2751-44E9-A48B-2BE9315A52B9@.microsoft.com...
>> > Ok, here's the situation. Our MIS department recently relocated to a
>> > new
>> > office space about 5 miles away from our database servers. We used
>> > to be
>> > in
>> > the same building.
>> >
>> > We have about 10 different servers over there. For this
>> > example...let's
>> > say
>> > two of those are called DevData and ProdData.
>> >
>> > Since we've moved over here, whenever we have to copy data between
>> > the two
>> > servers...it takes much, much longer. I would guestimate that it
>> > takes 2
>> > or
>> > 3x as long to transfer data. Remember, both servers are located over
>> > there
>> > (right next to each other).
>> >
>> > When we questioned our Network Services God, he informed us that any
>> > time
>> > we
>> > transfer data from Dev to Prod that it has to first travel over here
>> > before
>> > going to the destination server.
>> >
>> > Can someone shed some light on this?
>> >
>> > Thanks so much.
>> >
>> > Jen
>> >
>>
>>
>