Troubleshooting Node-MSSQL ReadOnly Connections in SQL Server AG (2026)

When working with SQL Server Always On Availability Groups (AG), it’s critical to ensure that read-only queries are directed to the secondary replica to optimize performance and utilize resources effectively. However, a common issue is that applications, particularly those using the Node.js mssql package, connect to the primary replica even when configured otherwise. This guide will help you understand why this happens and how to configure your Node.js application correctly to avoid this problem.

Key Takeaways

• Understand the role of ApplicationIntent in SQL Server AG.
• Learn how to configure Node.js mssql package for read-only routing.
• Identify common misconfigurations that lead to incorrect routing.
• Explore troubleshooting steps for verifying replica connections.

Introduction

SQL Server Always On Availability Groups provide high availability and disaster recovery solutions for SQL Server databases. They include a primary replica that handles read-write operations and one or more secondary replicas designed for read-only operations. This setup aims to balance the load and improve query performance, especially for read-heavy applications.

In Node.js applications using the mssql package, developers can specify the ApplicationIntent=ReadOnly option to direct queries to a readable secondary replica. However, users often encounter issues where connections inadvertently target the primary replica instead. This can be caused by several factors, including incorrect connection configurations or server settings.

Prerequisites

• Node.js installed (version 14.0.0 or later)
• mssql package installed in your Node.js project
• SQL Server 2019 or later with an Always On Availability Group configured
• Basic understanding of SQL Server AG and Node.js

Step 1: Verify SQL Server Configuration

Before diving into the Node.js configuration, ensure that your SQL Server AG is correctly set up for read-only routing. This involves checking that the secondary replicas are configured to accept read-only connections.

Check Read-Only Routing Lists

-- Check the routing lists for the AG
SELECT ag.name AS [Availability Group],
       rl.routing_priority,
       rl.read_only_replica_name
FROM sys.availability_groups AS ag
JOIN sys.availability_read_only_routing_lists AS rl
ON ag.group_id = rl.group_id;

Ensure that your secondary replicas appear in the read-only routing list.

Step 2: Configure Node.js Connection

In your Node.js application, the connection configuration plays a crucial role in ensuring that read-only queries are correctly routed. Here’s how you should configure it:

const sql = require('mssql');

const config = {
  server: 'my-ag-listener',
  database: 'my_database',
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  options: {
    encrypt: true, // Use encryption
    enableArithAbort: true,
    readOnlyIntent: true // Ensure this is set to true
  },
  pool: {
    max: 10,
    min: 0,
    idleTimeoutMillis: 30000
  }
};

The readOnlyIntent option is crucial. Ensure it is set to true to indicate that the application intends to perform read-only operations.

Step 3: Test the Connection

Once you’ve configured your connection, it’s important to test and verify that it connects to the intended replica.

async function testConnection() {
  try {
    const pool = await sql.connect(config);
    const result = await pool.request().query('SELECT SERVERPROPERTY(''IsHadrEnabled'') AS IsHadrEnabled');

    console.log('Connected to server:', result.recordset[0]);
  } catch (err) {
    console.error('SQL error:', err);
  }
}

testConnection();

This script will connect and return properties that help you confirm the connection status.

Step 4: Common Issues and Troubleshooting

Even with correct configurations, issues may still arise. Here are some common problems and solutions:

Incorrect Listener Configuration

Ensure that your AG Listener is correctly configured to route read-only connections. Misconfigurations here are a common cause of routing failures.

Firewall and Network Issues

Verify that your network settings and firewalls allow traffic between your Node.js application and the SQL Server instances.

SQL Server Version Compatibility

Ensure that the SQL Server version supports the features you are using, as older versions may not fully support read-only routing.

Common Errors/Troubleshooting

• "ConnectionError: Failed to connect to ..." - Check network settings and SQL Server instance availability.
• "Read-Only Routing Not Working" - Verify AG configuration and Node.js connection settings.
• "Timeout expired" - Adjust connection timeout settings or check server responsiveness.

Conclusion

By following this guide, you should be able to effectively route read-only connections to the secondary replica in a SQL Server Always On Availability Group using Node.js. Proper configuration and testing are key to ensuring optimal performance and resource utilization in your applications.

Frequently Asked Questions

What is the ApplicationIntent in SQL Server?

ApplicationIntent is a connection string property that specifies whether the application intends to perform read-only or read-write operations.

Why are read-only connections going to the primary replica?

This could be due to misconfigured routing lists, incorrect connection settings, or network issues preventing access to the secondary replica.

How can I verify which replica my application is connecting to?

You can run diagnostic queries or check server properties using SQL queries to determine the connected replica.