Try adding MultiSubnetFailover=true
to the connection string.
Any particular reason you are using OleDb and not SqlClient?
And it is called "Load balancer", but the term is "listener".
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
So we wrote a service for a client using vb.net and the SqlClient namespace. The ConnectionString is built once, then for each sequent run - the service is called by a System.Timer.Timer once every 2 seconds.
On such a run via conn.Open() the database connection is established, then a select statement is executed, the data if found are handed into a DataTable and processed from there, afterwards via conn.Close() the connection is closed.
Some years later the client wanted to improve data security and availability - so they opted for a second installation using MSSQL Always ON Availability Groups - and "hid" both instances behind a loadbalancer that took the former database-server's place. So our service calls this loadbalancer-address and as long as the primary database is up and running everything runs fine. But in case of error or for maintenance purposes they switch over to the secondary MSSQL-Server. Reported problem is in either case: the service detects an error in the database connection, but isnt "able" to recover by himself - even though the connection uses the same address and credentials. due to the loadbalancer. The service then needs to be stopped manually and started again, at which point it starts working again ...
How can I prevent the need for manual service restart?
Public Function ReadData(ByVal SQLCommand As String) As DataTable
Dim dr As SqlDataReader
Dim dtReturn As New DataTable
Dim cmd As New SqlCommand(SQLCommand, conn)
If OpenDB() Then
'Connection wird automatisch geschlossen nach lesen
Try
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
Catch ex As OleDb.OleDbException
' handling of Error
...
...'pack data into datatable
Return datatable
End Function
Public Function OpenDB() As Boolean
Try
If (conn.ConnectionString = String.Empty) Then Return False
conn.Open()
Return True
...
End Function
this shows the basic layout of the ReadData Method that is called by the Timer.Elapsed Sub - as you can see, during each launch the connection is opened and closed.
Only problem is .. this works well, if I say stop the MSSQL server in test environment , upon restart, the service picks up right where it got "cut off" ...
So how do I make it work together with the MSSQL Always ON ?
if you need more information I'll try to provide
Try adding MultiSubnetFailover=true
to the connection string.
Any particular reason you are using OleDb and not SqlClient?
And it is called "Load balancer", but the term is "listener".