0

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

  • mydatabase1.databsae.windows.net
  • mydatabase2.databsae.windows.net

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?

JKennedy
  • 18,150
  • 17
  • 114
  • 198
  • 1
    Documentation states you should have cname listener `Failover group read-write listener: A DNS CNAME record formed as .database.windows.net that points to the current primary server URL. It allows the read-write SQL applications to transparently reconnect to the primary database when the primary changes after failover. `...can you check..I did n't experiment with azure failover groups,but for onpremises you need an listener,so same should be the case there – TheGameiswar Jan 23 '18 at 12:16
  • demo here :https://social.technet.microsoft.com/wiki/contents/articles/37968.working-with-azure-sql-auto-failover-group.aspx – TheGameiswar Jan 23 '18 at 12:18
  • @TheGameiswar actually that link provides a note explaining my situation, I will post an answer. Thanks – JKennedy Jan 23 '18 at 12:23

2 Answers2

3

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

JKennedy
  • 18,150
  • 17
  • 114
  • 198
0

Try :

Server=tcp:mydatabase.database.windows.net;Database=mySqlDatabase;
User ID=MyUser@mydatabase;Password=myPassword;Trusted_Connection=False;
Encrypt=True;MultiSubnetFailover=True;
sanatsathyan
  • 1,713
  • 12
  • 18
  • I've already tried that, it returns the error: `Database 'mySqlDatabase' on server 'mydatabase' is not currently available. Please retry the connection later` – JKennedy Jan 23 '18 at 12:13