When is Azure SQL ready to receive queries?

Question

Problem

When I deploy an Azure SQL Database, I would like to make some queries immediately after (on the Azure DevOps agents). Is there any way to find out when it is ready to receive queries?

Testing if the server is "Ready"

I have tried ensuring that the server is running before calling the database:

# Looping till server is ready
status="Not Ready"
time=0
while [ "$status" != "Ready" ]
do   
   state=$(az sql server show --resource-group $resourcegroup --name $servername --query state -o tsv)
   echo "Current state is $state"
   sleep 1m
   echo "Have been waiting $time minutes"
   time=$((time + 1))
done

Testing if database is "Online"

As this didn't do the trick, I decided to write my "wait" statement at the database level, and to ensure that it is online before I make queries.

# Looping till database is online
status="Offline"
time=0
while [ "$status" != "Online" ]
do   
   status=$(az sql db show --resource-group $resourcegroup --server $servername --name $databasename --query status -o tsv)
   echo "Current status is $status"
   sleep 1m
   echo "Have been waiting $time minutes"
   time=$((time + 1))
done

This also does not work, and I get an error when I make my first call using sqlcmd. However, if I wait like 5-10 minutes and run the same calls on a new agent the calls go through.

sqlcmd -U $databaseuser -P $databasepw -S $databasehost -d $databasename -Q "CREATE USER $databaseuser2 FROM LOGIN $databaseuser2"

Example of the ERRORs:

2019-09-11T08:54:19.6306534Z Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Login timeout expired.
2019-09-11T08:54:19.6306875Z Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : TCP Provider: Error code 0x2AF9.
2019-09-11T08:54:19.6308179Z Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..

What else should be checked?

I looked at the following post, but it requires that the database actually accepts queries to be carried out.

Answer 1

Possible solution

Though an Azure database has the default setting Allow access to Azure services = True, this may not be true for Azure DevOps agent machines. If I deploy a database, I cannot send queries to it in the same pipeline. However, if I send the queries in a second pipeline that I run afterworth it seems to work.

Another way to resolve the problem is to add the agent machine's IP to the firewall rules, and then after running the SQL queries to remove the rule again.

# Getting public ip
my_ip=$(curl https://ipinfo.io/ip)

# Creating firewall rule
az sql server firewall-rule create -g $resourcegroup -s $databaseserver -n "agent_access" --start-ip-address $my_ip --end-ip-address $my_ip

# Run cool queries
sqlcmd -U $databaseuser -P $databasepw -S $databasehost -d $databasename -Q "CREATE USER $databaseuser2 FROM LOGIN $databaseuser2" -l 60

# Remove firewall rule
az sql server firewall-rule delete -g $resourcegroup -s $databaseserver -n "agent_access"

Note: I also added "-l 60" to the sqlcmd calls as it is suggested in the documentation to set the timeout to longer than 30 seconds (default is 8 seconds).

Thanks to @Jim Xu for leading me in this direction.