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
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,
ROLLBACK WORK statements to complete the
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
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.
• 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
• 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