Condividi tramite

Connect to SQL Server Using Application Intent Read-Only

  • Articolo

 

Once you have configured your SQL Server availability group for read-only routing (see blog 'End to End - Using a Listener to Connect to a Secondary Replica (Read-Only Routing)') you must install the SQL Native Access Client (SNAC) provider that supports application intent connections and you must write your application using the correct and necessary connection properties, to successfully connect to the secondary read-only replica.

 

SQL Native Access Client Supports Application Intent property

Support for read-only routing of SQL connections is via the SQL Native Access Client provider (SNAC) version 11. You can specify the Application Intent connection property when connecting to SQL Server via SQLClient, SNAC ODBC or SNAC OLEDB.

The SNAC provider must be installed where your client application runs.

How to obtain SQL Native Access Client

 SNAC version 11 (sqlncli11.dll) supports the Application Intent connection property. You can obtain the SQL Native Access Client with one of the following:

 

 <> Use SQL Server 2012 Setup to install the SQL Server Client Tools.

NOTE: The SNAC version that ships with SQL Server is 11.0.2100.60

 

 <>  Install Microsoft® SQL Server® 2012 Native Client from the Microsoft® SQL Server® 2012 Feature Pack. Under the 'Install Instructions' link, you will find an individual link for the SQL Server 2012 Native Client.

   https://www.microsoft.com/en-us/download/details.aspx?id=29065

 

Sample Connection Strings - Implementing the Application Intent connection property

 

Depending on the SNAC protocol you are using there is a slight variation in the application intent connection string property.

  • When using SQL Native Access Client SQL OLE DB, the application intent connection string should be specified with a space: 'Application Intent'
  • When connecting using SQLClient or ODBC specify 'ApplicationIntent' with no space

The following Visual Basic code demonstrates a valid connection string for each SNAC API that supports application intent.

The strings assume an availability group listener named 'aglisten', an availability group database called 'ag' and a trusted connection. In order to successfully connect to the secondary using Application Intent, your connection string must:

  1 Specify the availability group listener name

  2 Specify the catalog - the database that is defined in the availability group

 3 Specify the 'application intent' (SQLClient or SQL OLE DB) or 'applicationintent' (ODBC) connection property (depending on what protocol you connect with)

 

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

        'Use SQLClient and ApplicationIntent
        Dim SQLClientConn As New SqlClient.SqlConnection("data source=aglisten;integrated security=sspi;initial catalog=ag;applicationintent=readonly")
        SQLClientConn.Open()
        Dim objCmd As SqlClient.SqlCommand = New SqlClient.SqlCommand("select @@servername", SQLClientConn)
        MsgBox(objCmd.ExecuteScalar())
        SQLClientConn.Close()

        'Use SNAC ODBC and ApplicationIntent
        'Dim SNACODBCConn As New Odbc.OdbcConnection("Driver={SQL Server Native Client 11.0};server=aglisten;database=ag;trusted_connection=yes;applicationintent=readonly")
        'SNACODBCConn.Open()
        'Dim objCmd As Odbc.OdbcCommand = New Odbc.OdbcCommand("select @@servername", SNACODBCConn)
        'MsgBox(objCmd.ExecuteScalar())
        'SNACODBCConn.Close()

        'Use SNAC SQL OLE DB and ApplicationIntent
        'Dim SNACOLEDBConn As New OleDb.OleDbConnection
        'SNACOLEDBConn.ConnectionString = "Provider=sqlncli11;data source=aglisten;integrated security=sspi;initial catalog=ag;application intent=readonly"
        'SNACOLEDBConn.Open()
        'Dim objCmd As OleDb.OleDbCommand = New OleDb.OleDbCommand("select @@servername", SNACOLEDBConn)
        'MsgBox(objCmd.ExecuteScalar())
        'SNACOLEDBConn.Close()

    End Sub

 Proper connection strings will result in a successful connect to the read-only secondary.

 

 

Important! Legacy SQL OLE DB and ODBC providers do not support these connection strings. Be sure that the legacy SQL OLE DB provider or the legacy ODBC driver is not specified when connecting. If it is, the application intent property will be ignored and the connection will be directed to the primary.

The following sample code demonstrates that if the legacy ODBC or SQL OLE DB drivers are used, application intent is not utilized and your application will be connected to the primary replica.

 

       'DEMONSTRATE LEGACY ODBC AND OLEDB DO NOT SUPPORT Application Intent

        'Using legacy SQL ODBC and ApplicationIntent will fail
        'Dim SNACODBCConn As New Odbc.OdbcConnection("Driver={SQL Server Native Client 11.0};server=aglisten;database=ag;trusted_connection=yes;applicationintent=readonly")
        'SNACODBCConn.Open()
        'Dim objCmd As Odbc.OdbcCommand = New Odbc.OdbcCommand("select @@ServerName", SNACODBCConn)
        'MsgBox(objCmd.ExecuteScalar())
        'SNACODBCConn.Close()

        'Using legacy SQL OLE DB and ApplicationIntent will fail
        Dim SNACOLEDBConn As New OleDb.OleDbConnection
        SNACOLEDBConn.ConnectionString = "Provider='SQLOLEDB';data source=aglisten;integrated security=sspi;initial catalog=ag;application intent=readonly"
        SNACOLEDBConn.Open()
        Dim objCmd As OleDb.OleDbCommand = New OleDb.OleDbCommand("select @@ServerName", SNACOLEDBConn)
        MsgBox(objCmd.ExecuteScalar())
        SNACOLEDBConn.Close()

  Note that when you use one of these legacy providers, your application will fail to connect to the secondary.

For more information on high availability support in SQL Native Client, see the MSDN topic 'SQL Server Native Client Support for High Availability, Disaster Recovery'

   https://msdn.microsoft.com/en-us/library/gg471494.aspx

Comments

  • Anonymous
    November 26, 2013
    nice blog and information about Connect to SQL Server Using Application Intent Read-Only and <a href="www.datasparc.com/download.html"> soracle tools </a>.
  • Anonymous
    May 21, 2016
    I was using SQLOLEDB.1 with no luck. Did not know it ignores application intent. Using SQLNCLI11.1 worked !! Thanks.