Turning a database administrator into an integration superhero
With the introduction of the Linked Server feature, Microsoft arms database administrators and developers with a powerful tool that enables SQL Server Database Engine to execute commands against OLE DB data sources outside of the instance of SQL Server. Hence, this feature facilitates integration of data external to the local SQL Server instance into your local database applications.
If your data is stored in other systems, they remain unreachable to you. To integrate data from other, non-SQL enterprise systems, such as Exchange, SharePoint and Dynamics CRM, into an SQL data source, a custom solution is needed.
Creating a Linked Server in SQL Server Management Studio is a rather straightforward process. It’s simply a matter of expanding the “Server Objects” tree, right-clicking on “Linked Servers”, selecting “New Linked Server” and configuring a few simple options. This enables you to gain a unified view on your data in multiple SQL-based servers.
But can you utilize this simplicity to integrate data from other business applications?
Businesses can rarely rely only on data in SQL Server for real business insight. No doubt, it is a very powerful data store for business critical data and for feeding them into your custom business applications, yet company data is often gathered through various CRM, file-storage and BPM systems. This presents a substantial integration issue, as data is stored with disparate, often incompatible representations, so custom enterprise applications are not able to retrieve them. Companies used to have only two options: either rewrite their legacy applications to be able to communicate with the target system, or create a custom solution to provide and convert the data for legacy applications.
However, inherent to both of these options are huge risks and costs resulting from custom development. Another disadvantage of these ad-hoc solutions is the lack of flexibility and versatility.
The third option rises - CB Linked Server
CB Linked Server for Enterprise Applications, built on Connect Bridge, enables you to define Linked Server connections to these business systems and thus integrate their data into your SQL Server data source and feed them into your enterprise applications.
SELECT * FROM EXCHANGE365.Contacts… Can it get any easier?
CB Linked Server for Enterprise Applications is an implementation of the Connect Bridge integration platform. It serves as a proxy – or intermediary – between your SQL Server and your business systems, translating SQL queries to API calls to the target systems and, vice versa, presenting the data retrieved in a consumable form to the SQL Server.
The connections to target systems, as well as users and permissions, are defined in the Connect Bridge Server Administration tool.
The Linked Servers in SQL Server Management Studio represent these connections defined in Connect Bridge. After a successful connection, the items in the target system are listed as table rows in Management Studio, thus you can manipulate them simply through basic SQL commands.
This setup enables you to easily work with your data and retrieve, update and delete records via simple SQL queries. For example, to list all contacts on a SharePoint server, you would use the following command:
SELECT * FROM EXCHANGE365…Contacts;
No, it can't get any easier, can it?
With this logic, you can easily integrate data from multiple diverse business systems into one SQL data source or even synchronize data between systems through SQL Server. Moreover, it enables you to gain a comprehensive view of all your business-critical data in your enterprise applications.