Read-only error after SQL Server reboot

[jeansouza]

Every now and then we have a high workload on our company's SQL Server instance and need to restart it. Until the last week we would restart the server and after a few minutes all the applications would be working normally again. This changed when I updated the mssql lib from 5.1.0 to 8.1.0 last week, after that every time we've needed to restart SQL Server the applications would stop working, throwing the following error:

Failed to update database "XXXX" because the database is read-only.

They're only able to execute write queries after I restart them.

Expected behaviour:

The applications should be able to keep communicating with SQL Server after a reboot.

Actual behaviour:

Applications stops communicating with SQL Server after a reboot throwing the error mentioned above.

Configuration:

{
    connectionTimeout: 15000,
    database: 'DATABASE',
    options: {
      appName: 'APPNAME',
      encrypt: false,
      trustServerCertificate: true,
    },
    password: 'PASS',
    pool: {
      idleTimeoutMillis: 5000,
      max: 50,
      min: 5,
    },
    port: 1433,
    requestTimeout: 15000,
    server: 'HOST',
    user: 'USER',
  }

Software versions

• NodeJS: v16.14.2
• node-mssql: v8.1.0
• SQL Server:

Microsoft SQL Server 2019 (RTM-CU12) (KB5004524) - 15.0.4153.1 (X64) 
    Jul 19 2021 15:37:34 
    Copyright (C) 2019 Microsoft Corporation
    Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Datacenter 10.0 <X64> (Build 14393: ) (Hypervisor)

[dhensby]

Have you done any debugging into what is going on?

I'm not familiar with this error but with 2 minutes searching the error online I found this rather helpful article: https://blog.greglow.com/2017/08/12/failed-to-update-the-database-because-the-database-is-read-only/

Is your database part of an availability group?

I'm afraid I can't provide help debugging your infrastructure, if you're having to restart your SQL server periodically because it can't handle the load, then you've got problems outside the scope of this library.

Between the two versions you're looking at the underlying tedious driver has gone from v4 to v14, so there could be any number of reasons as to why the connections are finding themselves fixated on a read-only instance, but it almost certainly sounds like a replication/availability group issue as I can't see why else the connections would find themselves in read-only mode otherwise.

[jeansouza]

@dhensby thank you for the response!

Yes, you're right, we do have a bigger problem, but for the time being we do need to reboot the server sometimes, unfortunately. So I just want to find a solution so I can avoid the described problem.

Thanks for the advice regarding the availability group, I'm gonna take a look at this.

[dhensby]

Good luck, if you're able to find a bug with the lib that's leading to this issue, let me know, but for the time being I'm going to close the issue as I don't think it's entirely in the scope of the library (feels like a configuration issue).

What should happen in this instance is that the connections to the database will error and thus be discarded from the pool (because they will have been forcibly disconnected when the SQL server restarts). Then the pool will attempt to create a new connection and will return that successfully. However, from what you're saying, those new connections are read-only.

It makes sense to me that those new connections may point to a read-only replica whilst the master database reboots, especially during high-load where one would expect there to be little respite for the connections to just "wait" for the master to become available and/or for a replica to have been promoted to master.

You may also want to use multiSubnetFailover if you are using an availability group.