Category: Linked Server

Linked Server, SQL

Error Message: Server is not configured for RPC in SQL Server

Recently one of my colleague reached out to me with an error message as : “Server is not configured for RPC”

This a typical issue with Linked Server. When we configure a linked server, we need to set up the right values for RPC & RPC Out . We can see these values in SSMS -> Right click on Linked Server -> Properties -> Server Options as in the below screen shot.

RPC / RPC OutRPC stands for Remote Procedure Call and allows you to run stored procedures on the Linked Server.  RPC enables Remote Procedure Calls from the specified server and RPC OUT enables Remote Procedure Calls to the specified server.

Hope this helps; If you enjoyed this blog post, feel free to share it with your friends!

Linked Server, SQL

More about MSDTC

What is MSDTC?

MSDTC is a acronynm for Microsoft Distributed Transaction Coordinator which is a windows service to maintain the transactions in a distributed environment. The internals of MSDTC is a black box for users, however, this service is very important to maintain the transactions between two different systems. A Distributed transaction is a transaction that spans across multiple machines.Distributed transaction will ensure if there are multi operation happens between servers, if any of the operation is failed and cancelled, none of the operations in the transaction is getting committed in a multi server environment. I am not sure if I can simply explain than this.

Where do we need MSDTC?

Typically, in the context of SQL Server, yes we may need MSDTC for Linked Server usages.If transaction is opened to do some operations across servers in a linked server environment, this needs to be converted as a distributed transaction to maintain the integrity of the data. We need to understand two things clearly here.

1. When two physical machines are communicating with a transaction. If you are using two instances of the same machine, DTC will not be used. However, if the instances are in a cluster, you need to use DTC as you cannot ensure the instances are in the same physical nodes in the cluster environment.

2. Irrespective of type of transaction, whether its implicit or explicit, you need to use DTC. A typical example for implicit transaction is Trigger. Please find more detail here.

How to troubleshoot MSDTC issues?

Troubleshooting MSDTC issues is a pain area as you will not have complete information about the DTC component. We can use DTCPing tool to troubleshoot the issues which will provide much better insights.

DTCPing tool can be downloaded here.

How to configure and troubleshoot using DTCPing tool?

Test Network Connectivity
1. On Computer A, run DTCPing.exe.

2. On Computer B, run DTCPing.exe.

3. On Computer A, type the NetBIOS name of Computer B, and then click Ping.

4. On Computer B, type the NetBIOS name of Computer A, and then click Ping.

NOTE: The DTCPing log file can be found in the same folder as the DTCPing.exe file. The log file name has the following format: “NetBIOSName” + “ProcessID” + .log

You can see more details in the readme.txt available in the tool kit.

See Also:

How to troubleshoot msdtc issues with dtcping tool

DBA Utilities, Linked Server, NON SQL

How to identify NetBIOS Name of a computer?

Its not a SQL Server related, but I found it difficult to identify a way to get the NetBIOS name of a computer, hence sharing the same.

Recently, while troubleshooting an issue with MSDTC, I had to use DTCPing tool. I do not cover the DTCPing tool here, but will share more about it in another post. DTCPing tool was expecting NetBIOS name instead of IP address.

So, here are few options to identify the NetBIOS Name of a computer. 


Using T-SQL

Select serverproperty('ComputerNamePhysicalNetBIOS')
(Or)
exec master..xp_regread 'HKEY_LOCAL_Machine', 'SYSTEM\CurrentControlSet\Control\ComputerName\ComputerName\','ComputerName'

Using Windows

nbtstat -n  (-n   -- Lists local NetBIOS names.)

Thats it for now, but if you think there is any other method, post it in the comment section.

Errors and Exceptions, Linked Server, SQL

Error Message: The operation could not be performed because OLE DB provider “SQLNCLI11” for linked server “” was unable to begin a distributed transaction.

Problem Statement:

We had an issue with Linked Server in one of our environment while executing a functionality. The error message is shown as below.

OLE DB provider “SQLNCLI11” for linked server “” returned message “The transaction manager has disabled its support for remote/network transactions.”.
Msg 7391, Level 16, State 2, Procedure “VIEWNAME” Line 8 [Batch Start Line 1]
The operation could not be performed because OLE DB provider “SQLNCLI11” for linked server “” was unable to begin a distributed transaction.

Analysis:

While analyzing, we identified the reason for the error is actually an INSERT operation on a view.To be more clear, lets discuss the scenario with an example as below.

Lets assume we have two servers ServerA and ServerB connected through linked server “LKSERVER”.

1. Create a table Called – dbo.TestLinkedServerTargetonPrimary in ServerA

Create Table dbo.TestLinkedServerTargetonPrimary(Col1 int)

2. Create a view called vw_LinkedServerTest in ServerB

create view vw_LinkedServerTest
		as
			Select Col1 From [ServerA].DatabaseA.dbo.TestLinkedServerTargetonPrimary

3. Create an INSTEAD OF Trigger for INSERT operation on the view created above

create TRIGGER [dbo].TR_LinkedServerTest on [dbo].vw_LinkedServerTest
		INSTEAD OF INSERT
		AS		
		BEGIN
                	INSERT INTO [ServerA].DatabaseA.dbo.TestLinkedServerTargetonPrimary
	                (
				Col1
                	)
	                SELECT 
				Col1
        	        from inserted
		END

4. Try inserting data into Linked Server table through view.

Insert into vw_LinkedServerTest Select 1

The insert operation is failing with the below error message.

OLE DB provider “SQLNCLI11” for linked server “LKSERVER” returned message “The transaction manager has disabled its support for remote/network transactions.”.
Msg 7391, Level 16, State 2, Procedure TR_LinkedServerTest, Line 7 [Batch Start Line 21]
The operation could not be performed because OLE DB provider “SQLNCLI11” for linked server “LKSERVER” was unable to begin a distributed transaction.

The error is due to the transaction created by the INSTEAD OF Trigger on the view. Though, we dont have any explicit transaction defined, the instead of trigger is creating an implicit transaction. As the transaction scope is across linked server, it tries to open a distributed transaction in the trigger and it fails due to non access to network DTC access.

Lets quickly confirm the cause of the issue by modifying the trigger code to get the transaction count.

From the above, we can clearly see the transaction count is increasing as the trigger is getting executed.

Solution:

There are two options to solve the issue.

1. Provide enough security or enable the configuration at Network DTC access

a. Open “Component Services” in both servers and change the security settings as below.

b. Once the settings are changed, try to execute the INSERT query.

One drawback of this solution is a high dependency on MSDTC. Unless there are no alternatives, I would not suggest to use this method .

2. Try to Avoid the Trigger

Yes, As already discussed,INSTEAD OF trigger on the view is causing the issue. If we can drop the trigger on the view, it will not create a distributed transaction and the query will be executed successfully.

Hope this post helps you for similar situations, please share your feedback/comments….

Linked Server, SQL

Linked Server in SQL Server – Best Practices

Linked Server is one of the easiest way of communicating between multiple servers/instances in SQL Server. By linking the servers, you would be able to receive/send data between the partners. This becomes handy as it may not really need *lots of* changes in your code to work, however, caveat is it may have some performance issues depending on your code and other factors. I am trying to put few best practices for you while you work with Linked Server in this post.

1. “Collation Compatible” Setting while creating the Linked Server

Collation Compatible is a setting to instruct SQL Server to do the evaluation of comparisons on character columns locally. By default, the value is false.

If the value is true, then SQL Server will consider that all partners of a Linked Servers are compatible regards to character set and sort order.

If the value is false, then SQL Server tries to process the data locally by pulling the data from remote server. This may lead to a performance issue if your remote table has more data. A word of caution: The choice of this setting should be considered at most care as this may even lead for data inconsistency if we set the value is true for performance gain and collation are different for Linked servers.

2. PUSH versus PULL method in Linked Server

PUSH or PULL are denoting how the SQL query is being operated through Linked Server. PUSH denotes the data to send (push) to the partner. PULL denotes the data to receive as required from the partner. In Linked Server world, PULL is much faster than PUSH method.

Eg: If you need to get some data from Remote, Local server can PULL data from remote server table instead of pushing the data from remote server. This has an impact on performance and I personally experienced in one of my earlier projects. Having said, the amount of data pass through the network has a major impact on the performance. As much as can limit the data volume over the network, we will get better performance. It depends on the query being passed, size of tables, cardinality, type of queries etc. This is a huge topic to be discussed later and forever.

3. Query dialects

SQL Server always do the best work for you, meaning, it will do most of the work at remote server and get you the required data to locally. However, sometimes, it may not happen as desired due to many factors. There are few operations that can hinder the work at remote if you use in your queries.

Any formatting/conversion of data
Queries that use uniqueidentifier datatype
Queries that use TOP operator
Complex queries
UNION queries with local objects

4. Required Permission

This is one of the important criteria while you set a Linked Server. SQL Server query execution is heavily depending on the statistics of objects, which will decide the best execution plan for your query. Linked Server is not a magic technology in SQL Server. It gets an additional layer OLEDB interface while communicating between servers. That enables Linked Server to connect heterogeneous systems. When your Linked server is between SQL Servers, the native client SQL Server retrieves statistics from the remote and process the query for the performance. Please find the SQL Profiler collected from the remote server for one of my Linked Query:

The highlighted lines are the ones getting executed in remote server to get the statistics. SQL Server needs special permission for the users connected to remote server to use the statistics of remote server. This could be either of the below:

1. sysadmin
2. db_owner
3. ddl_admin

If the user is not having any of the above roles, then the query cannot use the distributed statistics from the remote server and it will have an impact on performance. The issue with providing these roles is that compromising the security.

Prior to SQL Server 2012 (SP1), this is one of “must to verify”, however, there is a relief in SQL Server 2012 (SP1) , MS has put a fix to use statistics information even for read only user (not necessarily be associated with any of the above user roles). You have a choice of disabling this behavior by using trace flag 9485, that means, enabling trace flag 9485 reverts the new permission check to the original behavior. But, be aware, this may not solve all your Linked Server performance issues.

Please comment your experiences with Linked Server if you find anything interesting to be shared.