How to add Failover Partner to connection string in VB.NET

Question

I have a windows application that connects to a database to read some data. Since the database is configured for stability, my application needs to connect to one of two databases. Can someone point out that the syntax was to specify the fault tolerance partner in the connection string using SQL server authentication.

Any help is greatly appreciated.

Answer 1

Check connectionstrings.com :

Database Mirroring
If you are connecting with ADO.NET or a native SQL user to a mirrored database, your application can take advantage of drivers to automatically redirect connections when recovery occurs after a database is mirrored. You must specify the starting primary server and database in the connection string and the crash recovery server.

Data Source=myServerAddress;Failover Partner=myMirrorServerAddress;Initial Catalog=myDataBase;Integrated Security=True; 

There are many other ways to record a connection string using database mirroring, this is just one example that points to fault tolerance functionality. You can combine this with other available string options.

Answer 2

If you specify the partner server failure name in the connection string, the client will transparently try to connect to the partner's fault tolerance if the main database is unavailable when the client application first connects.

 ";Failover Partner=PartnerServerName" 

If you omit the fault tolerance name of the partner server and the main database is unavailable, if the client application first connects, a SqlException is thrown.

A source

Answer 3

If you do not have mirroring between SQL servers, you can achieve this using .net. just in conclusion.

Code below ..

 enter code here Imports System.Data.SqlClient Imports System.Data Public Class dbConn Private primaryServerLocation As String = "SERVER=primaryAddress;DATABASE=yourDB;User id=youruserID;Password=yourPassword;" Private secondaryServerLocation As String = "SERVER=secondaryAddress;DATABASE=yourDB;User id=youruserID;Password=yourPassword;" Public sqlConnection As SqlConnection Public cmd As SqlCommand Public Sub primaryConnection() Try sqlConnection = New System.Data.SqlClient.SqlConnection(primaryServerLocation) cmd = New System.Data.SqlClient.SqlCommand() 'test connection sqlConnection.Open() sqlConnection.Close() Catch ex As Exception secondaryConnection() End Try End Sub Public Sub secondaryConnection() 'Used as the failover secondary server if primary is down. Try sqlConnection = New System.Data.SqlClient.SqlConnection(secondaryServerLocation) cmd = New System.Data.SqlClient.SqlCommand() 'test connection sqlConnection.Open() sqlConnection.Close() Catch ex As Exception End Try End Sub End Class