Forum / Devolutions Server - Support

Mirror SQL setup doesnt auto failover

Hi!

We got our DVLS SQL Backend (SQL2017) setup with a synchronous mirror setup, so it autofailsover when it needs to. I've configured backup SQL server in DVLS settings, but it doesnt seem to get that, since DVLS gets a DB error everytime the DB has failed over to the mirror partner.

What do I need to check/configure to get DVLS to understand/change automatically to the other mirrored instance?

/Kaj

Clock3 mths

Hello,

Does your SQL instance manage the fail over or you have configured the fail over partner in the DVLS settings?

Have you only one DVLS instance or multiple instances connected on the SQL instance (HA/Load balancing topology)?

Best regards,



Érica Poirier

signaturesignature

Clock3 mths

Hi!

Its the SQL Server that automatically manages the failover to the mirrored partner & I've setup failover partner in DVLS settings.

Its only one instance of DVLS, dont think our license allows us to install a HA/Load balanced topology with DVLS - correct me if I'm wrong.

/Kaj

Clock3 mths

We're using 4.7.0.0 on DVLS

Clock3 mths

Hello,

First, I have looked in your records and you can indeed deploy DVLS in HA/Load balancing topology. If you plan to do so, please consult the following online help page about managing encryption keys in a HA/Load balancing topology.

Finally, because your SQL Server instance already manages the fail over, there is no need to set the fail over partner in the DVLS settings.

Best regards,



Érica Poirier

signaturesignature

Clock3 mths

Erica Poirier wrote:

First, I have looked in your records and you can indeed deploy DVLS in HA/Load balancing topology. If you plan to do so, please consult the following online help page about managing encryption keys in a HA/Load balancing topology.

Excellent - will put that into the todo itemlist.

Finally, because your SQL Server instance already manages the fail over, there is no need to set the fail over partner in the DVLS settings.

Are you sure ? We're not talking Always on availability group, but a standard Synchronous Mirror. My understanding of the error that we have is that DVLS continues to talk to the 'mirror partner' server after a failover and since that DB isn't primary it doesnt answer. So I would assume that some sort of logic is needed in DVLS to retry the connection on the other configured DB.

/Kaj

Clock3 mths

Hello,

After a discussion with an engineer, the connection pool still have the information of the server that failed and try to talk to it without success. We have on our to do list the implementation of such structure for testing. I will ask to set this on a higher priority.

Best regards,



Érica Poirier

signaturesignature

Clock3 mths

Thanks, can we get early release on this for us to test? Would hate to be forced to switch off mirroring on the DB and now it seems to swap mirror pairs every morning.

Clock3 mths

Hello,

Sure, as soon as we get an internal version that includes a fix for this, I will send it to you.

Best regards,



Érica Poirier

signaturesignature

Clock3 mths

Below is the error from DVLS server when the DB fails over.

Error:
SqlException - A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions) at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry) at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry) at System.Data.SqlClient.SqlConnection.Open() at Devolutions.Server.DatabaseManager.GetDataSet(String sql, IEnumerable`1 parameters, CommandType commandType) at Devolutions.Server.DatabaseManager.GetData(String sql, IEnumerable`1 parameters) at Devolutions.Server.DataSourceManager.GetDataSourceSettingsDataTable() at Devolutions.Server.DataSourceManager.GetDataSourceSettings(SessionContext context) at Devolutions.Server.ServerManager.GetWebServerInfo(SessionContext context) at Devolutions.Server.ServerInfo.ProcessRequest(HttpContext context) at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() at System.Web.HttpApplication.ExecuteStepImpl(IExecutionStep step) at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) ------------------------------------------ Win32Exception - The network path was not found --- Default

Clock3 mths

Hi Kaj,

I had a chat with Erica and the engineering department and we will build a similar infrastructure as you to try to reproduce the same issue.

As simple Microsoft is many type of "Backup" exist in SQL server, some of them are: Always On HA, FailOver Cluster, Database Mirroring...

Is the Database mirroring that you talked about is this type of setup that Microsoft explain here (https://docs.microsoft.com/en-us/sql/database-engine/database-mirroring/database-mirroring-sql-server?view=sql-server-2017) ?

If so, to make it work as Microsoft write

Before a mirroring session can begin, the database owner or system administrator must create the mirror database, set up endpoints and logins, and, in some cases, create and set up certificates.


And I'm curious to understand how did you "copy" the information from the first database to the second database. Did you export and import all the entries? Did you backup and restore it. Do you have some idea about the initial setup because that could have a big impact of the setup.

I will wait your answer and base on these I will be able to go further in the setup or confirm the installation.

Best regards,



David Grandolfo

signaturesignature

Clock2 mths

David Grandolfo wrote:

Is the Database mirroring that you talked about is this type of setup that Microsoft explain here (https://docs.microsoft.com/en-us/sql/database-engine/database-mirroring/database-mirroring-sql-server?view=sql-server-2017) ?

Yes its that setup - we have it running in 'High-safety mode with automatic failover' I'e with a third Witness server.

And I'm curious to understand how did you "copy" the information from the first database to the second database. Did you export and import all the entries? Did you backup and restore it. Do you have some idea about the initial setup because that could have a big impact of the setup.

We did according to the instructions from Microsoft, I'e make a backup of the database from the 'primary' server, then restore with norecovery and then switch on the Mirror on the mirror node and then finally on the primary node. The DB's then state that they are Principal, Syncronized on the primary and Synchronised, Restoring on the mirror. We've done the same setup on other DB's that support mirroring and they work perfectly over a fail over scenario.

Should we run a quick webex tonight to check that we haven't missed anything in the config? I'm available for another 2 hours or so. We've running 4.7.0.0 on the DVLS server itself.

Clock2 mths

Thanks for all these precious information.

With this setup I will build an internal Database Mirroring with three servers and test all this with Devolutions Server.

I inform you as soon as I give our engineering team the access to this setup.

Best regards,



David Grandolfo

signaturesignature

Clock2 mths

Any progress?

Clock2 mths

Hi Kaj,

The infrastructure for the test has been created and SQL server is currently configured. We had issues with the Windows and SQL licensing. Now everything is correct and we should transfer the servers to the engineering department next week the infrastructure to continue the case.

When the infrastructure is transferred to the engineering department, I will post on the forum to inform you of the progress.

Best regards,



David Grandolfo

signaturesignature

Clock2 mths

Erica Poirier wrote:

Hello,
First, I have looked in your records and you can indeed deploy DVLS in HA/Load balancing topology. If you plan to do so, please consult the following online help page about managing encryption keys in a HA/Load balancing topology.


Is there any instruction on how to configure a loadbalancer in front of the two DVLS servers? Thinking specifically about KEMP Technologies.

Clock30 days

Hello,

I am afraid to say that we do not have any documentation about Kemp technologies configurations.

We haven't tested any Kemp products. It is quite impossible to test all different technologies available on market. DVLS should run on almost any load balancing topology as long as you set each DVLS instance that are connected on the same SQL instance to use the same encryption keys as stated on the following online help page.
https://helpserver.devolutions.net/kb_encryptionkeyha.htm

Best regards,



Érica Poirier

signaturesignature

Clock29 days