CODEWITHSUNDEEP
sql-serverazureazure-sql-databaseazure-sql-server
JKennedy
JKennedy

Reputation: 18827

What should the connection string be when using Azure Sql Failover groups?

Ok so I have set up a Azure Sql Failover group at mydatabase.databsae.windows.net which contains 2 servers:

I go to connectionstrings.com to get my Sql Azure Connection String which looks like so:

Server=tcp:mydatabase.database.windows.net;Database=mySqlDatabase; User ID=MyUser@[serverName];Password=myPassword;Trusted_Connection=False; Encrypt=True;

Now the problem lies in User ID=MyUser@[serverName] I have tried:

User ID=MyUser@mydatabase but that didn't work and repeatedly returns the error:

Database 'mySqlDatabase' on server 'mydatabase' is not currently available. Please retry the connection later

I also tried User ID=MyUser@mydatabase1 and that did work.

But the problem with the above in the connection string is that when I do failover to mydatabase2 I will need to go and update all my connection strings.

So what is the correct connection string when using Sql Failover groups?

Upvotes: 1

Views: 1819

Answers (2)

JKennedy
JKennedy

Reputation: 18827

This is a current limitation of failover groups:

Note: If at this point you go to SSMS and try to connect to your Primary/Secondary database using above listeners, you will receive error and will not be able to login. Ideally it should have allowed, but it currently fails, as it tries to connect to the Master database which is currently not part of the group. This is currently being worked upon and should be resolved soon. Till then, workaround is to provide the database name while connecting to server. Use the option button to provide database name.

what this means is that currently only the following connection strings will work:

Server=tcp:mydatabase.database.windows.net;Database=mySqlDatabase;
User ID=MyUser@mydatabase1;Password=myPassword;Trusted_Connection=False;
Encrypt=True;

OR

Server=tcp:mydatabase.database.windows.net;Database=mySqlDatabase;
User ID=MyUser@mydatabase2;Password=myPassword;Trusted_Connection=False;
Encrypt=True;

Unfortunately this renders the automatic failover completely useless as you need to then reconfigure all your connection strings

Ref: https://social.technet.microsoft.com/wiki/contents/articles/37968.working-with-azure-sql-auto-failover-group.aspx

Upvotes: 3

sanatsathyan
sanatsathyan

Reputation: 1773

Try :

Server=tcp:mydatabase.database.windows.net;Database=mySqlDatabase;
User ID=MyUser@mydatabase;Password=myPassword;Trusted_Connection=False;
Encrypt=True;MultiSubnetFailover=True;

Upvotes: 0

Related Questions