Not all of the information neededfor the design of an application has to be storedwithin a single instance of of SQL Server. Through a feature called Linked Servers. SQL Server’s database engine can query tables and other remote instances of SQL Server or even other products. Such as Microsoft Access Databases, Oracle Databases, Excel Spreadsheets and even plain text files.
To link servers together the external data source must have a registered OLE DB provider on the computer running the main instance of SQL Server. OLE DB stands for Object Linking and Embedding for Databases. And are supplied by the source applications vendor. The OLE DB provider is a DLL library file that provides SQL Server with the connection information that it needs to locate and utilize the remote sources.
Some data providers allow read only access. While others allow full manipulation of the data. Including performing updates and deletes. By linking servers together you can gain access to a diverse set of data from a centralized location without having to import them from their original context. And once the data provider is linked to the local SQL Server instance it will appear in the Object Explorer under Server Objects and then the Linked Servers folder. Tables and views within the provider are accessible there.And they can be queried against using the standard T-SQL commands that you’re already familiar with.
When your application design calls for several databases working together or has a need to pull information out from a file format outside of the SQL Server instance,consider looking at linked servers to provide those connections.
Sources:
Comments