1 linking to an instance of another SQL Server
EXEC sp_addlinkedserver ' server address ', @srvproduct ='SQL Server' Go
The return of XML data types is not supported in distributed queries
2 SQL Server connects to an external data source
Conditional OLE DB provider is available
When the spreadsheet is open in Excel, the SQL Server cannot hold row access to the Excel table's distributed query
exec sp_addlinkedserver @server = ' Template ',
@srvproduct = ' Jet 4.0 ',
@provider = ' microsoft.jet.oledb.4.0 ',
@datasrc = ' D:\Temp\Template.xlsx ',
@provstr = ' Excel 5.0 '
2 SQL Server uses the OPENDATASOURCE function to query another instance of SQL Server
Select from OpenDataSource ('SQLOLEDB','Data source=szdcdevdb02; User Id=dmclient2; password=icanfly#2013'). [Securitydata]. [dbo]. [Performancebasics]
3 Opendatasouce function can also query Excel
Select from OpenDataSource ('microsoft.jet.oledb.4.0','Data Source ' =d:\temp\template.xlsx; Extended properties=excel 5.0') ... A1
--4 Processing of distributed things
Set XACT_ABORT on-The connection option causes the current thing to roll back in the event of an error
BEGIN Distributed TRANSACTION--check if the DTC service is available
This contains WML action statements
Commit TRANSACTION--commit things
5 Database Sharding
Decompose the database into many smaller chunks, called shards, and then walk these chunks into many distributed servers
1 adding a linked server in SQL Server