[dba-SQLServer] Remote ETL

 Hi Arthur --

Read the following KB article: 

 http://support.microsoft.com/kb/287932

it describes the process of communication of a client app (SSMS in your case) with remote MS SQL Server(s) over TCP/IP using WinSock.

FYI: I'm using SSMS here with my ASP.NET hosting provider (parking.ru) and my hosted  MS SQL Server databases connection strings are looking something like the following:

Data Source=myTestMsSql.corp.parking.ru;Initial Catalog=myDatabase;User ID=myUserName;Password=myPassword

or they could also be:

Data Source = 190.190.200.100,1433; Initial Catalog = myDataBase; User ID = myUsername; Password = myPassword;
Such a connection works well here even via mobile GSM modem.

BTW, AFAIK there are quite a few ASP.NET providers worldwide, which you can sign up for a free usually for two weeks trial period to test how SSMS remote connection will work for you. Thank you.

-- Shamil

Sunday, October 13, 2013 12:42 PM -04:00 from Arthur Fuller <fuller.artful at gmail.com>:
>Lately I've begun work on a project which involves migration from an
>inadequate solution to a much more beautiful solution. Both solutions are
>web-UI. I need to migrate a bunch of data from the old system to the new
>system. In neither case do I have access using trusty tools such as SSMS.
>
>I think that I have worked around this by a) exporting the data from the
>old system to CSV files, then inhaling them into a new SQL database on my
>home machine. But although I've dipped into the docs about remote servers,
>etc., I have never actually had the need to do this. So I have a few
>questions....
>
>Is it possible that from my home, I can set up remote connections to both
>the old and the new systems? If so, what info about these instances will I
>need to set up the connections? So far I have logins to both but no
>apparent access to their SSMS installations, so my hands are somewhat tied
>with respect to this setup.
>
>Assuming that both vendors would be willing to supply the info required for
>a direct connection (i.e. I run SSMS from home and connect to both
>databases, each of which resides in a far-off place), is this possible? So
>actually there would be three objects: databases A and B, and my home
>machine running SSMS+SSIS, so I can inspect and compare the db structures
>in both places and write an ETL package that transports data from A to B.
>
>I've done this sort of thing locally (in my home) but never yet to two
>remote instances.
>
>Any advice, guidance, pointers to tutorials, etc. shall be gratefully
>received.
>
>-- 
>Arthur
>_______________________________________________
>


-- 
Салахетдинов Шамиль