Failover Partner issue or other kind of error?

[stevelord]

Hi Community,  What's this error message associated with and what is the solution?

Error message:

5/4/2016 9:54:28 AM:       Error: ErrorSource: Microsoft OLE DB Provider for SQL Server, ErrorMsg: Database 'DatabaseA' cannot be opened. It is in the middle of a restore.

I have Qlikview setup to extract data from many databases that were formerly housed on a single other server environment that Qlikview connected to.  A year or so ago, one of the more demanding databases (based on non-qlikview processes that were writing to it) was moved to a separate machine for load-balancing reasons, and I was instructed to add 'Failover Partner=123.456.789.12;' into the data extraction qlikview's connection string (actual ip not given here).  This allowed Qlikview to extract data from the databases in ip .11 and go to ip .12 for the database that had been moved there.  It worked okay last year.  Then it seems that database was moved back to ip .11 after optimization or something, but I left the failover partner= bit in the connection string since it was okay to do so.

This year, a couple of other databases were moved to that other machine at ip ....12, and Qlikview is failing to open them.  IT Director adjusted some permissions settings he thought might be causing the issue, but Qlikview is still unable to open those databases.  Any ideas?

[Peter_Cammaert]

You're sure that the DB QlikView is trying to connect to is NOT in the middle of a restore? Can other applications (or even Management Studio) access the DB without any weird messages?

[stevelord]

IT Director says this when I ask about sqlserver issues and that error message:

"the problem is that qlikview is trying to access the database that is currently the mirror

it needs to see that it cannot connect, and then try to connect to the other db in the connection string"

Qlikview's connection string is this:

OLEDB CONNECT TO [Provider=SQLOLEDB.1;Persist Security Info=True;User ID=$(DbUserId);Initial Catalog=$(DbInitialCatalog);Data Source=$(DbConnStr);Failover Partner=123.456.789.12;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=AA-Qlikview;Use Encryption for Data=False;Tag with column collation when possible=False] (XPassword is $(DbXPassword));

(We have a config file with the values for the variables loaded ahead of this.  I could add the Failover Partner to that list of variables too, but was fine just writing it into the connection string since this is exactly the one and only place it is used.)

[Peter_Cammaert]

Found it, I think. A DB connection automatically switching over to the mirror sql server when the original one is unavailable is only supported by the SQL Server Native Client. Try using that driver instead of the "OLDE DB Provider for SQL Server". See here:

Best,

Peter

[stevelord]

Thanks, can you confirm what the revised connection string would look like?  I'm unsure how the name in the screenshot would translate in the connection string.

I tried just navigating the wizard, but SQL Server Native Client 11.0 is not present in the qlikview server machine so I need to have the server admin add that driver to it.  Which is funny, because I saw it on my local machine which is not allowed to connect directly to these databases.

[stevelord]

Marking helpful at the moment, but will circle back with correct answer points after we install the needed driver and try the solution.

[Peter_Cammaert]

The MS product is called SQL Server Native Client and can be downloaded from the Microsoft downloads site. Make sure that you download a correct or newer driver for your SQL Server version, not an older one. The package is available as a separate download and includes both an ODBC and an OLE DB dll.

In the connection string, you will end up with something like:

[Provider=SQLNCLI11.1;...

Best,

Peter

[stevelord]

Thanks for everything, I'll call this correct and helpful all the way now and revisit if we hit any snags.  Good to know it was some subtle configuration difference between last year and this year, and not me going crazy.

[stevelord]

Hi Peter and community, I'm leaving peter's information marked as helpful in that it helped us rule out some more things, but we're still stuck a bit.

What I should've tested sooner, and what I will test the next time the IT Director is inclined to revisit, is to just make a connection string that points directly to the database on its new machine.  So we'd know off the bat if the new machine itself was the problem if the regular connection attempt failed.  If the regular connection attempt succeeded, then issue might still be some combination of other machine configuration and qlikview failover functionality/issue.

Beyond that, I had a thought that instead of leaving a mirror of the database in question on the first machine and the active copy on the second machine... maybe just completely remove the database from the first machine and let the active copy stand only on the second machine.  In case the mirror functionality was confusing qlikview's failover functionality.

Anyway.. we had everything working fine 18 months ago with the regular failover connection string I posted at the top, but engineers involved in that are gone and some piece of knowledge wasn't transferred when they left I think.  Since the QV string worked okay 18 months ago and not now, and all we did with qlikview was ram upgrade and 11-11.2 update, I'm inclined to think it's an issue with the machine configurations.

I will post the final solution whenever it happens, but work on this was prioritized for now.  Or maybe workarounds were setup on the machine side of things.

[Peter_Cammaert]

So it still didn't work, and you removed the mirror functionality altogether from the DB? Or did you simply connect straight to the active DB (at the moment) with problems looming as soon as those two DBs start hopping over each other?