Tag: Linked Server

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.