I am familiar with SQL server as a product and want to use this to do data federation across a wide range of data sources that can be accessed trough linked servers. My data volumes are rather limited.
However I am interested how would this approach compare to existing data virtualisation software on a data federation level performance wise?
SQL server has his own query optimization engine and so on... so will performance of sql server linked servers federated queries be comparable to the Denodos, Red hats, Ciscos,... and other Data Virtualization products of this world? Or do they have some other trick up their sleeve?
Kind Regards!
The specialised data virtualization tools do have optimization techniques you will not find in LinkedServer and similar technologies. The optimizers of DV systems have been designed from scratch to work with distributed architectures, while the optimizer of SQL Server has been designed for a conventional database. The problem of optimizing queries in both scenarios is VERY different.
You can check this two blog posts by Denodo to get an idea of the differences (DISCLAIMER: I work for Denodo):
http://www.datavirtualizationblog.com/performance-data-virtualization-logical-data-warehouse-scenarios/
http://www.datavirtualizationblog.com/cost-based-optimization-in-data-virtualization/