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.
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:
- Prepare phase
- Commit 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.
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.
BEGIN DISTRIBUTED TRAN
Key Points to be aware of:
- Cannot be nested.
- Local transactions can easily be escalated.
- 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:
- Add the remote server as a linked server.
- Add server login to the Added linked server.
- Set RPC calls in case you are using
Sproc from the remote server.
- Use part names for retrieving data.
By this, you can add a server. All the parameters are self explanatory, hence I skip briefing them! Here, the important things are
srvProduct in case you add servers other than SQL Server like Oracle, Exchange...., and
@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
To drop a Linked server:
To drop a Linked server Login info:
- 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
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).
- 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
Here goes your query:
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
sp_LinkedServers - List all active linked server
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:
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.
SELECT ... FROM OPENQUERY('<Linked server name>','<Query>')
FROM OPENQUERY ('MyRemoteServer','Select * from Employee) AS A
Inner Join Department d ON d.DeptID = a.DeptID
Note: Department lies in local server!
This is used with out adding the Linked server because you will be adding them in the query itself!!
SELECT .. FROM OPENROWSET
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
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
DROP on the objects!
- Order by if
BLOB objects are used in the
BLOB related statements cannot be used in the Linked server.
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!!