Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
maxim_senin
Contributor III

How to connect to secondary replica of SQL Server AlwaysOn Availability group

Hi everyone,

I have a problem with OLE DB connection which should be established to an SQL Server database which is a secondary replica in AlwayOn Availabilty group (AAG) and woring on read-only mode.

When connecting to the database the following error occurs:

[

SQLState: "42000"

SQL Server error: 978

The target database ('xxx') is in an availability group and is currently accessible for connections when the application intent is set to read only. For more information about application intent, see SQL Server Books Online.

]

I use the following OLE DB connection strings:

  • This one does work with the primary database - OLEDB CONNECT TO [Provider=SQLOLEDB.1;Initial Catalog=xxx;User ID=<USER NAME>;Database=xxx;Data Source=<PRIMARY SERVER NAME>];
  • This one does not work with the replica even if I add ApplicationIntent=ReadOnly - OLEDB CONNECT TO [Provider=SQLOLEDB.1;Initial Catalog=xxx;User ID=<USER NAME>;Database=xxx;ApplicationIntent=ReadOnly;Data Source=<SECONDARY SERVER NAME>];

Playing (adding and removing) with the following settings does not help as well:

  • Server=tcp:<PRIMARY OR SECONDARY SERVER NAME> (with the ports 1433 and 5022 and without them)
  • IntegratedSecurity=SSPI
  • Database=xxx

I googled a number of resources including

but nothing helps. I'm stuck.

Did anyone tried working with a read-only replica in AAG?

Best regards,

Maxim

1 Solution

Accepted Solutions
Not applicable

Re: How to connect to secondary replica of SQL Server AlwaysOn Availability group

Hi, i had the same problem and have a solution:

First you need to upgrade the OLE DB drivers, install the SQL Native Client 11.0 (available from Microsoft: Download Microsoft® ODBC Driver 11 for SQL Server® - Windows from Official Microsoft Download Center)

This is because of that support for "readonly" was released with SQL Server 2012.

After that change the provider to: SQLNCLI11.1 and you need to use ;application intent=readonly; with a [space].

applicationintent is for ODBC.

application intent is for OLEDB.

3 Replies
Not applicable

Re: How to connect to secondary replica of SQL Server AlwaysOn Availability group

Hi, i had the same problem and have a solution:

First you need to upgrade the OLE DB drivers, install the SQL Native Client 11.0 (available from Microsoft: Download Microsoft® ODBC Driver 11 for SQL Server® - Windows from Official Microsoft Download Center)

This is because of that support for "readonly" was released with SQL Server 2012.

After that change the provider to: SQLNCLI11.1 and you need to use ;application intent=readonly; with a [space].

applicationintent is for ODBC.

application intent is for OLEDB.

maxim_senin
Contributor III

Re: How to connect to secondary replica of SQL Server AlwaysOn Availability group

Hi Johannes,

Thanks, I found this at MSDN site already.

Best regards,

Maxim

hectorcruz
New Contributor

Re: How to connect to secondary replica of SQL Server AlwaysOn Availability group

thanks a lot i had almost the same problem

Community Browser