Solved

Connection issues to MS SQL

sigtill
Rank
Badge +21

I have read this: https://knowledge.safe.com/articles/281/failed-to-connect-using-sql-server-2012-native-cli.html

And tried to fix it with all these steps, with no luck. 

UPDATE: With respect to workspaces these was updated from FME Server 2015.1.3.1 - Build 15573 - win64 to FME Server 2016.1.2.1 - Build 16674 - win64

2017-01-24 20:12:10|   0.1|  0.0|INFORM|FME Configuration: Using Named Connection values from 'XXX' for MSSQL_ADO_1
2017-01-24 20:12:10|   0.1|  0.0|INFORM|Creating reader for format: Microsoft SQL Server Non-Spatial
2017-01-24 20:12:10|   0.1|  0.0|INFORM|Trying to find a DYNAMIC plugin for reader named `MSSQL_ADO'
2017-01-24 20:12:10|   0.1|  0.0|INFORM|FME API version of module 'MSSQL_ADO' matches current internal version (3.8 20160224)
2017-01-24 20:12:10|   0.1|  0.0|INFORM|Microsoft SQL Server Non-Spatial Reader: Read 2 DEF line(s).  Found 9 attribute(s)
2017-01-24 20:12:10|   0.1|  0.0|INFORM|Microsoft SQL Server Non-Spatial Reader: Opening `XXX' for read operation
2017-01-24 20:12:10|   0.1|  0.0|INFORM|Microsoft SQL Server Non-Spatial Reader: Trying to connect using SQL Server 2012 Native Client...
2017-01-24 20:12:25|   0.1|  0.0|ERROR |Microsoft SQL Server Non-Spatial Reader: Failed to connect using SQL Server 2012 Native Client. Provider error '(-2146824582) Provider cannot be found. It may not be properly installed.'. Connection string 'Provider=SQLNCLI11;DataTypeCompatibility=80;Data Source=XXX;Initial Catalog=XXX;User ID=XXX;Password=********'
2017-01-24 20:12:25|   0.1|  0.0|INFORM|Microsoft SQL Server Non-Spatial Reader: Trying to connect using SQL Server 2008 Native Client...
2017-01-24 20:12:25|   0.1|  0.0|ERROR |Microsoft SQL Server Non-Spatial Reader: Failed to connect using SQL Server 2008 Native Client. Provider error '(-2147467259) Named Pipes Provider: Could not open a connection to SQL Server [5]. '. Connection string 'Provider=SQLNCLI10;DataTypeCompatibility=80;Data Source=XXX;Initial Catalog=XXX;User ID=XXX;Password=********'
2017-01-24 20:12:25|   0.1|  0.0|ERROR |Failed to open reader

And these

2017-01-24 20:11:55|  16.9|  0.8|ERROR |Microsoft SQL Server Non-Spatial Reader: Query failed, possibly due to a malformed statement.  Query Text `select count(*) as _numodptot from dbo.XXX where XXX= XXX'. Provider error `(-2147467259) Named Pipes Provider: Could not open a connection to SQL Server [5]. '  

It happens randomly, on different workspaces, we have a group of 10 workspaces running every 1-5-10 minutes)

It seems some connection,issue - however this does not exist on the staging server (same FME Server version etc). During the upgrade we replaced the Embedded database parameters with Database Connections to simplify things, not sure if that is messing up stuff. Also the SQLExecutors, Joiners, Readers connecting to the MSSQL was created with FME2015 in the beginning and havent been deleted/recreated. The Joiners have been Upgraded to the latest version. Nor sure if there is some changes with the MS SQL Reader / Writer / Joiner that has changed how to connect to MSSQL and that might be an issue? Seems to work fine on FME Desktop 2016 with no issues.

Any help or suggestions appreciated!

icon

Best answer by sigtill 25 January 2017, 20:11

View original
The real SigTill

14 replies

sigtill
Rank
Badge +21

I saw I only had Native Client 2008 installed on the Server - installed 64bit Native Client 2012 from (https://www.microsoft.com/en-us/download/details.aspx?id=29065)

NB: Download link is found under INSTRUCTIONS NOT when clicking download.

Will reboot and see if there are any changes.

The real SigTill
fmelizard
Rank
Userlevel 4
Badge +13

I saw I only had Native Client 2008 installed on the Server - installed 64bit Native Client 2012 from (https://www.microsoft.com/en-us/download/details.aspx?id=29065)

NB: Download link is found under INSTRUCTIONS NOT when clicking download.

Will reboot and see if there are any changes.

Any joy?

 

 

sigtill
Rank
Badge +21

No luck :(

However now when some workspace fails, it is in 90% of the cases 2 of the particular workspaces. I suspect that there are just too many joiners / sqlexecutors / readers either in one workspace or that the connection after them does not get cleaned up properly after each run. Unfortunately it is difficult for me to get access to the admin of the database to see the connections. I will try that today and keep this thread update for anyone else with similar issue.

The real SigTill
sigtill
Rank
Badge +21

It seems the issue is due to the process taking to long to process - expiring the timeout for keeping the database connection. even though it was set on 10 minutes on all joiner, reader, sqlexecutor. The trick was to optimize the workspace and replace sqlexecutors with joiners (with Prefetch-queries). We only worked with 8000 rows in this case, however it really slowed down due to very non-optimal workspace. For instance sending 8000 sqls one and one to a server, and doing this multiple times resulted in runtime of more than 10 minutes. With optimizations it seems to work great and spend less than 1minute in the same data.

Perhaps write an error in the log when "Database connection from Joiner_4 / FeatureReader_2 / SQLExecutor_2 have timed out (timeout set to 600) - optimize query or set a larger timeout) - so you can easily see in the logfile - and find the correct Transformer among many

The real SigTill
sigtill
Rank
Badge +21

In the fmeServerConfig.txt there is a part:

#--------------------------------------
# Limits
#--------------------------------------
MAX_TRANSACTION_RESULT_SUCCESSES=100
MAX_TRANSACTION_RESULT_FAILURES=10
MAX_FAILED_TRANSACTION_REQUEST_RETRIES=3
MAX_REGISTRATION_READ_ATTEMPTS=6000
REGISTRATION_READ_RETRY_WAIT=10
MAINTENANCE_IDLE_WAIT=90000
TRANSACTION_DB_RETRY_WAIT=20

1. It this only related to communication between FME Engine and Core - or does it also apply to any other databaseconnections?

2. Is there a parameter that defines the maximum number of databaseconnections from an FME Engine? Some simple math if a assume one FME Workspace that contains 10 reader/joiner/sqlexecutor with the same database connection uses 10 database-connections (1 connection per transformer) and they run 10 workspaces on 10 minutes. Then the number of database-connections to the same database by FME is 10 connections/workspace * 10 workspaces run = 100 connections per 10 minutes. 

3. Does one FME Workspace handle 1 Database Connection as a single connection, or multiple connections when used in several SQLExecutors,joiners,readers,writers? If FME handles one Database Connection as 1 connection through the whole run of the workspace (regardles if there is a Joiner, SQLExecutor, Reader, Writer) that uses the same connection - then the needed connections is only 10. 

The real SigTill
fmelizard
Rank
Userlevel 4
Badge +13

In the fmeServerConfig.txt there is a part:

#--------------------------------------
# Limits
#--------------------------------------
MAX_TRANSACTION_RESULT_SUCCESSES=100
MAX_TRANSACTION_RESULT_FAILURES=10
MAX_FAILED_TRANSACTION_REQUEST_RETRIES=3
MAX_REGISTRATION_READ_ATTEMPTS=6000
REGISTRATION_READ_RETRY_WAIT=10
MAINTENANCE_IDLE_WAIT=90000
TRANSACTION_DB_RETRY_WAIT=20

1. It this only related to communication between FME Engine and Core - or does it also apply to any other databaseconnections?

2. Is there a parameter that defines the maximum number of databaseconnections from an FME Engine? Some simple math if a assume one FME Workspace that contains 10 reader/joiner/sqlexecutor with the same database connection uses 10 database-connections (1 connection per transformer) and they run 10 workspaces on 10 minutes. Then the number of database-connections to the same database by FME is 10 connections/workspace * 10 workspaces run = 100 connections per 10 minutes. 

3. Does one FME Workspace handle 1 Database Connection as a single connection, or multiple connections when used in several SQLExecutors,joiners,readers,writers? If FME handles one Database Connection as 1 connection through the whole run of the workspace (regardles if there is a Joiner, SQLExecutor, Reader, Writer) that uses the same connection - then the needed connections is only 10. 

We are looking into this at Safe support. A quick response to your points above:

 

 

1. Those parameters from fmeServerConfig.txt apply to FME Server and not to database connections within a workspace

 

2. There is no parameter for this. The MS SQL Server Spatial reader has a parameter "Make Connection Persistent" so we reuse connections, but I assume you aren't using this reader. For the MS SQL Server (non-spatial) Reader it looks like we will open a connection for each reader. The SQLExecutor should use persistent connections so that multiple SQLExecutor queries will should share a connection. 

 

3. See above. A reader, a Joiner and a SQLExecutor would each be a separate connections but multiple SQLExectuor queries or multiple SQLExecutors should share a persistent connection

 

 

steveatsafe
Rank
Badge +11

Is there any chance the link between FME Server and the Database system drops at all? Even for a split second? I'm really curious to learn more about this. You have experienced several variations of the SQL Server provider error.

 

Can you confirm that the FME Server system only has the SQL Server 2012 client installed and the 2008 client was removed?
SteveatSafe
fmelizard
Rank
Userlevel 4
Badge +13

We are fairly sure now that this was a known issue with the Joiner which was reported and fixed in FME 2016.1.2 Build 16670 (PR#71929). The issue is known to have caused workspaces to fail intermittently on FME Server when the Joiner was connecting to Microsoft SQL Server or Oracle and possibly other databases. We will create a Knowledge Center article shortly and include the link here. Upgrading FME Desktop and FME Server to 2016.1.2 or later and upgrading any Joiner transformers within the workspace should hopefully resolve this issue.

sigtill
Rank
Badge +21

Is there any chance the link between FME Server and the Database system drops at all? Even for a split second? I'm really curious to learn more about this. You have experienced several variations of the SQL Server provider error.

 

Can you confirm that the FME Server system only has the SQL Server 2012 client installed and the 2008 client was removed?
It had only 2008 Native Client, but I installed also the 2012. Currently BOTH are installed. Should I try to remove the 2008?

 

 

The real SigTill
sigtill
Rank
Badge +21

We are fairly sure now that this was a known issue with the Joiner which was reported and fixed in FME 2016.1.2 Build 16670 (PR#71929). The issue is known to have caused workspaces to fail intermittently on FME Server when the Joiner was connecting to Microsoft SQL Server or Oracle and possibly other databases. We will create a Knowledge Center article shortly and include the link here. Upgrading FME Desktop and FME Server to 2016.1.2 or later and upgrading any Joiner transformers within the workspace should hopefully resolve this issue.

It seems the issues is still there on newer versions of FME and after upgrading the Joiner. We are replacing all Joiners with SQLExecutor (or better in this case to use FeatureReaders) to see if issue persist.

 

 

 

The real SigTill
sigtill
Rank
Badge +21

I tried to replace all Joiners, and I also forced these group of workspace to use only 1 specific engine. I still have workspaces using both joiner, reader, sqlexecutors - however now they do not seem to fail. I suspect there is an issue when 2 different Engines runs simultaneously connecting to the same MS SQL-database.

Well, case solved for now, and I`ll repost in this thread if the issue starts again. Thanks for all the reply - and the reason I put it here on the forum is for people to easily try the different solutions here if they experience issues with MS SQL on FME Server.

The real SigTill
steveatsafe
Rank
Badge +11
It had only 2008 Native Client, but I installed also the 2012. Currently BOTH are installed. Should I try to remove the 2008?

 

 

Removing the 2008 client might have been a way to reduce confusion of where the issue is occurring. But now seems unlikely to be the issues (as you've posted otherwise)
SteveatSafe
steveatsafe
Rank
Badge +11

I tried to replace all Joiners, and I also forced these group of workspace to use only 1 specific engine. I still have workspaces using both joiner, reader, sqlexecutors - however now they do not seem to fail. I suspect there is an issue when 2 different Engines runs simultaneously connecting to the same MS SQL-database.

Well, case solved for now, and I`ll repost in this thread if the issue starts again. Thanks for all the reply - and the reason I put it here on the forum is for people to easily try the different solutions here if they experience issues with MS SQL on FME Server.

This does give us some information on what we can look for and see what is causing the problem. Thanks for sharing.

 

 

SteveatSafe
sigtill
Rank
Badge +21
This does give us some information on what we can look for and see what is causing the problem. Thanks for sharing.

 

 

We have now been running for 24 hours without any issues. We have restricted these groups of FME Workspaces to run on only 1 specific engines. Ant it seems to work. Previously more workspaces failed. We have removed the Joiners on only 1 of the 10 workspaces, the rest of the 9 still contains a mix of Joiners / SQLExecutors etc.

 

 

So for this KB:

 

https://knowledge.safe.com/articles/281/failed-to-connect-using-sql-server-2012-native-cli.html

 

 

I would add a case 10:

 

10. Restrict workspaces that connect to the same SQL Server to use only 1 engine. This is to test if this solves the issue or not.

 

 

The real SigTill

Reply


Community Stats

28,669
Posts
109,361
Replies
36,531
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.