SQL Server cross-Library query

Because of business splitting, the database is split into two roles:

1. Summary database (Master, head node database),
2. Child node Database (Compute node, compute child node database)

In this way, the design to the child node access to the head node database of a summary table, the records of such tables generally in a few, to hundreds of thousands of rows or so, currently suitable for cross-library queries.

Cross-Library queries are currently divided into two types:

1. Establishing a linked server through sp_addlinkedserver
2. You can use the OPENROWSET or OPENDATASOURCE function when you do not have a linked server
   1. When you deploy, you need to enable OPENROWSET and OPENDATASOURCE support in the SQL Server perimeter application Configurator
   2. The first (Create and destroy a connection server immediately) is a simple, easy-to-use SQL statement, and if it is complex logic that requires a statement block, function, or stored procedure to complete, the first type (to build a linked server via sp_addlinkedserver)

• establishing a linked server through sp_addlinkedserver
  • Use steps
    • Creating a linked server from sp_addlinkedserver
      • Example: Exec sp_addlinkedserver ' Remotelinkmasterdb ', ', ' SqlOleDB ', ' 172.21.1.1\mydbinstace '
    • Create a linked server with sp_addlinkedsrvlogin using an account, password
      • Example: Exec sp_addlinkedsrvlogin ' Remotelinkmasterdb ', ' false ', NULL, ' Mymasterdatabaseuser ', ' Mymasterdbbaseuserpassword '
    • Set RPC through sp_serveroption, RPC out to True (no need to set this if you do not need to perform cross-domain stored procedures)
      • Exec sp_serveroption ' Remotelinkmasterdb ', ' RPC ', ' true ';
      • Exec sp_serveroption ' Remotelinkmasterdb ', ' RPC out ', ' true ';
    • After use, delete the linked server via sp_dropserver, delete the link login
      • Exec sp_dropserver ' Remotelinkmasterdb ', ' droplogins ';
    • Examples of use in SQL:
      • Query: Select * from REMOTELINKMASTERDB. Database name. Scheme (database Schema). Table/view name;
      • Perform cross-domain stored procedures: Exec remotelinkmasterdb. Database name. Scheme (database Schema). Stored procedure name (parameter dependent);
      • You can also use the OpenQuery function: SELECT * from OpenQuery (REMOTELINKMASTERDB, ' select * from database name. Schema name. Table name ');
• You can use the OPENROWSET or OPENDATASOURCE function when you do not have a linked server
  • OPENROWSET Usage Example:
    
    • Select * from OpenRowSet (' SqlOleDB ', ' 172.21.1.1\mydbinstace ', ' mymasterdatabaseuser ', ' Mymasterdbbaseuserpassword ', ' Select GetDate () ');
    • Select * from OpenRowSet (' SqlOleDB ', ' source=172.21.1.1\mydbinstace; Uid=mymasterdatabaseuser; Pwd=mymasterdbbaseuserpassword ', ' Select GetDate () ');
  • OpenDataSource Usage Example:
    • Select * from OpenDataSource (' SqlOleDB ', ' Data source=172.21.1.1\mydbinstace; User Id=mymasterdatabaseuser; Password=mymasterdbbaseuserpassword '). Database name. Schema name. Table Name
  • These two methods must be opened Ad Hoc distributed Queries.

Reference: http://www.2cto.com/database/201206/136383.html

SQL Server cross-Library query