SQL Server 2000 Using Tranasaction on Distributed servers - Linked Servers

Introduction

Let's say, for example, you are having your Stock database and Sales database, both in different locations (geographic locations). Then you need to use your Action query on both the servers. For this situation, let’s see how you can implement transactions.

MS DTC

In order to make a safe connection for distributed transaction, Microsoft facilitates us with MS DTC, that is Microsoft Distributed Transaction Coordinator. This works with a two phase Commit Protocol called 2PC.

The two phases of the same are:

1. Prepare phase
2. Commit phase

Prepare phase

The MS DTC of the local server sends a request to all the servers used in the transaction to start a session which in turn returns a Success/ Failure acknowledgement. And the remote server does all the Atomicity and Data consistency at their end.

If any one of the servers acknowledges the local server with a failure message, then the transaction is taken as a Doubt transaction and can be rolled back at any point of time.

Commit phase

When the entire server returns a Success message, then the local server sends all the Remote server’s message to commit which in turn receives a Success/ Failure. In case of failure, the entire transaction is rolled back across the servers.

Commands

BEGIN DISTRIBUTED TRAN
COMMIT TRAN
ROLLBACK TRAN

Key Points to be aware of:

1. Cannot be nested.
2. Local transactions can easily be escalated.
3. MS DTC must be running in the local server.

OK, now let's see how this can be implemented physically.

For this task to be accomplished, you need to do the following steps:

1. Add the remote server as a linked server.
2. Add server login to the Added linked server.
3. Set RPC calls in case you are using Sproc from the remote server.
4. Use part names for retrieving data.

1. sp_AddLinikedServer [@server=],
                                [@srvProduct=],
                                [@Provider=], 
                                [@DataSrc=],
                                [@Location=], 
                                [@provStr=], 
                                [@Catalog=]

   By this, you can add a server. All the parameters are self explanatory, hence I skip briefing them! Here, the important things are server, srvProduct in case you add servers other than SQL Server like Oracle, Exchange...., and ProvStr.

2. sp_AddLinkedSrvLogin

   Parameters are:

   • [@rmtsrvName=] - Server name
   • [@useSelf=] - Set it to true so that the current login info will be carried over. Very useful if NT authentication is used.
   • [@LocalLogin=] - Name of the user for which a mapping is done!
   • [@rmtUser=] - Alias of the local login (login info for remote server).
   • [@rmtPasssword=] - Password for the rmtUser.

   To drop a Linked server:

   sp_dropServer '<Servername>'
   

   To drop a Linked server Login info:

   sp_dropLinkedSrvLogin '<Servername>'

3. For using the sproc from the remote server, you need to enable the RPC calls on both the servers using RPC, RPC OUT with the sproc sp_ServerOption
   • sp_ServerOption RPC - from Remote to Local server.
   • sp_ServerOption RPC OUT - from Local server to Remote.

   Now you are done with all the settings for accessing and querying the remote servers (linked servers).

4. How to query??

   Big question isn't it? But SQL gives a simple answer... just use the full part names as, say for example:

   Linked server Name: MyRemoteServer
   DB_OWNER: Senthil
   Database: dbTest
   Table: tblTest

   Here goes your query:

   SQL

   SELECT a.* FROM MyRemoteServer.dbTest.Senthil.tblTest AS A

   I.e.: select .......... from LinkedServerName.DataBase / Catalog name.Owner.Table /Entity. You can join this result and do any thing as you do with your local database.

   To retrieve the Metadata from the Linked server, you can use the following sproc:

   sp_LinkedServers - List all active linked server
   sp_Catalog
   sp_Tables_ex
   sp_Columns_ex
   sp_table_previlages_ex
   sp_column_privilages_ex
   sp_Primarykeys
   sp_foreignkeys
   sp_indexes

   Note: all the stored procedures have some parameters which I'm really bored to explain here. You can find them from the book online!

Other than the above stated method for retrieving, you can still use other methods also for the same. They are:

1. OPENQUERY
2. OPENROWSET

OPENQUERY

For this, you need to add the Linked server first!

• you can use the Action queries with this (I/U/D) until your OLEDB Provider supports!
• you can join the result set with any thing as you do always with local server.

Syntax:

SELECT ... FROM OPENQUERY('<Linked server name>','<Query>')

Example:

SELECT a.*
FROM OPENQUERY ('MyRemoteServer','Select * from Employee) AS A
Inner Join Department d ON d.DeptID = a.DeptID

Note: Department lies in local server!

OPENROWSET

This is used with out adding the Linked server because you will be adding them in the query itself!!

Syntax:

SELECT .. FROM OPENROWSET
       ('<Provider>','<Datasource/Catalog>','<Loginid>','<pwd>','<Query>')

Performance Issues:

There is some thing called as Collation. What this does is some thing superb and also something a developer should know!!

Default set to off and you can turn it to on in:

sp_ServerOption '<Servername>','Collation Compatible',True

What it does if this is set to off is the data retrieved from remote server comes as a whole from the referenced table irrespective of the WHERE clause. In the local server where the clause is applied on the result set, say for example, if you have a query like:

SELECT * FROM employee WHERE salary >  10000

Here all the records of employee is brought to the local server and with that result set, the WHERE clause is applied. So turn it on so that you will be having at least two CPUs in action and also you can still have security on your data!!

Things that cannot be used on a Linked server

1. CREATE/ALTER/DROP on the objects!
2. Order by if BLOB objects are used in the SELECT clause.
3. Any BLOB related statements cannot be used in the Linked server.

Conclusion

By this, I'm sure, you can have a Linked server and keep it working. Be aware that registering a server in EM and this is same! Any further clarification on this is welcome!!