When I want to move data between two databases, what better choice.
A) Linked Servers
database local-> Linked Servers -> database azure
b) ETL - SSIS
database local create procedure xml -> integration service -> serialize xml to object c#> call wcf service async(queue-servicebus) -> persist database azure
The following link addresses the pros and cons of Linked Servers vs. SSIS, with a recommendation that Linked Servers are best applied in moderation for queries.
It really boils down to how much data your are looking at moving from one database to another and for what purpose. That is, are you dealing with real-time data that must be acquired for an interface? It must be considered on a case-by-case basis. In my development environment, real-time is not required when pulling information from other sources into the database. In this case, SSIS works best and it provides a great log of the package applications throughout the day.
Additional observations:
Lastly, I recommend that you speak with your DBA about applying Linked Servers. The DBA's I've worked with in the past have mostly been apprehensive with the responsibility of maintaining their application. This is one of those "could" vs. "should" issues in development where you must focus on the impact to the system as whole.
When we use Linked Servers, there are also options to use BULK INSERT. In this case, SSIS won't be faster (in many cases it's even slower).
SSIS has some limitations in certain implementations: - cross domains issues when the domains are not trusted (when we call the packages, SSIS does not work with SQL authentication) - not easy to automate when the schema changes - if transformations are required, TSQL is generally faster. - SSIS with integrated CDC Data Sources works incorrectly and slow in certain scenarios. Confirmed by Microsoft, the issues are not yet fixed (SQL 2014/2016)
As mentioned above, it should be "must be considered on a case-by-case basis". There is no 'YES' or 'NO' here.
