1 / 25

Module 11: Programming Across Multiple Servers

Learn about distributed queries, setting up linked server environment, working with linked servers, using partitioned views in SQL. Explore best practices and security considerations while utilizing cross-server data.

ttoole
Download Presentation

Module 11: Programming Across Multiple Servers

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Module 11: Programming AcrossMultiple Servers

  2. Overview • Introducing Distributed Queries • Setting Up a Linked Server Environment • Working with Linked Servers • Using Partitioned Views

  3. Lesson: Introducing Distributed Queries • What Are Distributed Queries? • Ad Hoc Query Execution on Remote Data Source

  4. What Are Distributed Queries? • Remote Data Access • Ad hoc query • Linked server query • Specify Where to Process Distributed Queries • Local computer running SQL Server • Remote OLE DB data source (pass-through query) • Verify Connection Settings

  5. Ad Hoc Query Execution on Remote Data Source • Use the OPENROWSET Function When You Do Not Expect to Use the Data Source Repeatedly • Use the OPENROWSET Function to Access Remote Data Without Setting Up a Linked Server SELECT a.* FROM OPENROWSET('SQLOLEDB', 'LONDON1'; 'newcustomer';'mypassword','SELECT ProductID, UnitPriceFROM Northwind.dbo.Products ORDER BY UnitPrice')AS a

  6. Lesson: Setting Up a Linked Server Environment • What Is a Linked Server? • How Links Are Established • Security Considerations • Configuration Options • Linked Server Information

  7. What Is a Linked Server? SQL Server allowsaccess to otherdata sources Remote serversmust be linked tothe local computer running SQL Server Local Computer Running SQL Server Remote Computer Running SQL Server Remote SQL Server Other Data Sources

  8. How Links Are Established • Connecting to a Remote Computer Running SQL Server • Connecting to an OLE DB Data Source EXEC sp_addlinkedserver @server = 'AccountingServer', @svrproduct = 'SQL Server' EXEC sp_addlinkedserver @server = 'OracleFinance', @svrproduct = 'Oracle', @provider = 'MSDAORA', @datasrc = 'OracleDB'

  9. Security Considerations • Local Server Must Log On to Remote Server on Behalf of User • If User’s Logon Account Exists on Both Servers, It Can Be Used to Log On to Remote Server • Use sp_addlinkedsrvlogin to Map Logon Accounts • Use Security Account Delegation to Connect to Multiple Servers with One Authentication

  10. Configuration Options • Collation Compatible USE master EXEC sp_serveroption 'AccountingServer', 'collation compatible', true • Collation Name and Use Remote Collation • Data Access • RPC and RPC out • Lazy Schema Validation

  11. Linked Server Information

  12. Lesson: Working with Linked Servers • Linked Server Restrictions • Examples of Query Execution • Pass-Through Query Execution • Stored Procedure Execution • Distributed Transaction Management • Data Modification • Best Practices

  13. Linked Server Restrictions • Referring to Objects on Linked Servers • Allowed Transact-SQL Statements • SELECT, INSERT, UPDATE, DELETE • Disallowed Transact-SQL Statements • CREATE, ALTER, DROP • ORDER BY on remote tables containing large objects • READTEXT, WRITETEXT, UPDATETEXT • Remote Query Optimization

  14. Examples of Query Execution • Use Fully Qualified Names to Reference Objects on Linked Servers Example 1 SELECT CompanyName FROM AccountingServer.NorthwindRemote.dbo.Suppliers Example 2 SELECT ProductName, CompanyName FROM Products p JOIN AccountingServer.NorthwindRemote.dbo.Suppliers ON p.supplierid = s.supplierid Example 3 SELECT CompanyName, Phone INTO PhoneList FROM AccountingServer.NorthwindRemote.dbo.Suppliers

  15. Pass-Through Query Execution • Use the OPENQUERY Function to Execute Pass-Through Queries on a Linked Server • Use the OPENQUERY Function in a SELECT Statement in Place of a Table Name • Use the Result of an OPENQUERY Function as the Target Table of an INSERT, UPDATE, orDELETE Statement SELECT * FROM OPENQUERY(AsiaServer, 'SELECT ProductID, RoyaltyFROM Northwind.dbo.ProductInfo')

  16. Stored Procedure Execution Local Server Linked Server Stored Procedure Call Parameters and Output Stored Procedure Processing User EXEC accounting.master.dbo.sp_helpntgroup

  17. Distributed Transaction Management • Managing Distributed Transactions by Using MS DTC • Managing Distributed Transactions by UsingComponent Services

  18. Data Modification • Distribute Transactions by: • Executing BEGIN DISTRIBUTED TRANSACTION -OR- • Calling API functions from a client • Consider These Facts: • BEGIN DISTRIBUTED TRANSACTION statements cannot be nested • ROLLBACK TRANSACTION rolls back entire transaction • Savepoints are not supported • Set the XACT_ABORT session option

  19. Best Practices Use Linked Servers for Frequent Remote Data Access ü Use Ad Hoc Queries for Infrequent RemoteData Access ü Set Up Linked Servers to Execute Stored Procedures Remotely or to Execute Distributed Queries ü Restrict Access to Linked Resources ü Avoid Setting Up Duplicate Logon Accounts on Different Servers ü

  20. Lesson: Using Partitioned Views • Advantages of Partitioned Views • Partitioned Views • Implementation Steps for Partitioned Views • Considerations for Partitioning Data

  21. Advantages of Partitioned Views • Scalability • Add more hardware to a single server • Divide workload and database across multiple independent computers • Benefits of Partitioned Views • Results of separate tables can appear as one table • Data location is transparent to the application • Database is programmed as a single entity

  22. Customer A ~ ~ ~ ~ ~ ~ ~ ~ B ~ ~ ~ ~ ~ ~ ~ ~ Table B Table A Customer B A ~ ~ ~ ~ ~ ~ ~ ~ A ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ B ~ ~ ~ ~ ~ ~ ~ ~ Partitioned Views CREATE VIEW Cust_ViewSELECT Table A UNION ALL SELECT Table B CREATE VIEW Cust_ViewSELECT Table A UNION ALL SELECT Table B Member Server 1 Member Server 2 Partitioned View

  23. Create multiple databases, each on a different member server 1 Horizontally partition the tables 2 Create linked server definitions on eachmember server 3 Create a partitioned view on each member server by using the UNION ALL set operator 4 Implementation Steps for Partitioned Views To Set Up Distributed Partitioned Views:

  24. Considerations for Partitioning Data • Design Considerations • Partition data to keep related data on the same server • Minimize need to access data on other member servers • Place complete records on the same member server • Select the appropriate column to define the partition • Ways to Partition • Rules for Partitioning

  25. Lab A: Using Distributed Data • Exercise 1: Setting Up Linked Servers • Exercise 2: Querying Remote Data • If Time Permits: ManagingDistributed Transactions

More Related