Transcript lesson18
Linking Servers Lesson 18 Skills Matrix Distributed Transactions • Distributed transactions span two or more servers known as resource managers. • The management of the transaction must be coordinated between the resource managers by a server component called a transaction manager. • Each instance of the SQL Server Database Engine can operate as a resource manager in distributed transactions coordinated by the Microsoft Distributed Transaction Coordinator (MS DTC) which complies with the Open Group XA specification for distributed transaction processing. Distributed Transactions • At the end of the transaction, DTC requests the transaction to be either committed or rolled back. • A distributed commit must be managed differently by the transaction manager to minimize the risk that a network failure may result in some resource managers successfully committing while others roll back the transaction. • This is achieved by managing the commit process in two phases (the prepare phase and the commit phase), which is known as a two-phase commit (2PC). Transact-SQL Statements • The Transact-SQL statements controlling the distributed transactions are few because most of the work is done internally by the SQL Server Database Engine and MS DTC. • The only Transact-SQL statements required in the TransactSQL script or application are those required to: – Start a distributed transaction. – Perform distributed queries against linked servers or execute remote procedure calls against remote servers. – Call the standard Transact-SQL COMMIT TRANSACTION, COMMIT WORK, ROLLBACK TRANSACTION, or ROLLBACK WORK statements to complete the transaction. Configuring Host and Target Servers • Start the Distributed Transaction Coordinator service at the operating system level and set up your environment as linked servers. Linked Servers • Linking servers provides a mechanism for performing distributed queries. • Data can be retrieved from one or more servers both homogeneous (all SQL Server) or heterogeneous (including Oracle, for example). • When a result set returns from databases on multiple servers, the remote servers in the query are called linked servers. • You may use SQL Server Management Studio or Transact-SQL to link the target servers. Querying Remote Servers • Two constructs support querying separate server stores: OPENROWSET and the fourpart server name. SQL Server Browser • The SQL Server Browser program runs as a Windows service. • SQL Server Browser listens for incoming requests for Microsoft SQL Server resources and provides information about SQL Server instances installed on the computer. SQL Server Browser • SQL Server Browser contributes to the following actions: – Browsing a list of available servers. – Connecting to the correct server instance. – Connecting to dedicated administrator connection (DAC) endpoints. SQL Server Browser • During installation, TCP port 1433 and pipe \sql\query are assigned to the default instance, but you can change these using SQL Server Configuration Manager. • Because only one instance of SQL Server can use a particular port or pipe, different port numbers and pipe names are assigned for named instances, including SQL Server Express. SQL Server Configuration Manager Securing Linked Servers • The sending server must log in to the remote server on behalf of the user to gain access to the data. SQL Server can use one of two methods to send this security information: – security account delegation. – linked server login mapping. • If your users have logged in using Windows Authentication, and all the servers in the query are capable of understanding Windows domain security, you can use account delegation. Securing Linked Servers 1. If the servers are in different domains, you must make certain the appropriate Windows trust relationships are in place. The remote server’s domain must trust the sending server’s domain. 2. Add a Windows login to the sending server for the user to log in with. 3. Add the same account to the remote server. 4. Create a user account for the login in the remote server’s database, and assign permissions. 5. When the user executes the distributed query, SQL Server sends the user’s Windows security credentials to the remote server, allowing access. Summary • In this lesson, you learned you can maintain synchronized databases on many servers using the Distributed Transaction Coordinator. • You learned about and how to configure linked servers. • You also learned how to query distributed data. • And finally, you learned about security concerns and how to resolve them. Summary for Certification Examination • Understand how to create and secure linked servers.