Azure SQL Database is a fully managed cloud database service. Azure SQL Database auto-tunes database performance and security to a user’s database patterns.  Database performance is optimized based on workloads. When the workload increases the database scales dynamically with very little downtime. Azure SQL Database provides in-memory OLTP for improved throughput and latency on transactional processing and faster queries.  With multiple database clients, Azure SQL Database maximizes resource utilization, manages multiple database instances as one, and allocates each client its own database instance with elastic pools. While Azure provides a hosted environment for SQL Server Azure is not a client tool for SQL Server. SQL Database on Azure does require a development environment to access, administer, and manage the database. In this article we shall discuss using Azure SQL Database with Toad for SQL Server. This article has the following sections.

Setting the Environment

Creating a SQL Database Instance on Azure

Adding Connectivity on SQL Database Instance on Azure to Local Machine

Creating a Connection in Toad for SQL Server

Setting the Environment

Download the following software:

Toad for SQL Server (Trial version or another edition)

Create an Azure account at https://azure.microsoft.com/en-ca/free/.  SQL Database may be used on a free basis initially by selecting “Start free” as shown in Figure 1.

Figure 1. SQL Database>Start Free

Login to the Azure portal at https://portal.azure.com/.

Creating a SQL Database Instance on Azure

In this section we shall create a SQL Database instance. Click on SQL databases in the Azure portal as shown in Figure 2.

Figure 2. Selecting SQL databases in Azure Portal

In the SQL databases wizard click on Create SQL database as shown in Figure 3.

Figure 3. Create SQL database

In the SQL databases>SQL Database window specify a Database name (sql-db) as shown in Figure 4. Select a Subscription type. For Resource group select the Use existing option and click on the drop-down to select a resource group.

Figure 4. Specifying SQL Database Detail

In Select source select Blank database as shown in Figure 5.

Figure 5. Selecting a Source Database

Next click on Server to configure the server settings as shown in Figure 6.

Figure 6. Selecting Server to configure server settings

The Create a new server is selected by default and a window to specify the server settings is provided, as shown in Figure 7.

Figure 7. Server>New server

Specify a Server name (sql-server-db-azure), which must be unique across all Azure accounts and not just a single Azure account.  Select a Server admin login and specify its password. Select a Location for server. Click on Select as shown in Figure 8.

Figure 8. Selecting Server Settings

Next, click on the Pricing Tier to select a pricing tier as shown in Figure 9.

Figure 9. Pricing Tier

The different pricing tiers available are Basic, Standard, Premium, and PremiumRS. Select the Basic pricing tier as shown in Figure 10.

Figure 10. Selecting Pricing Tier as Basic

With all the SQL Database settings configured, click on Create as shown in Figure 11.

Figure 11. SQL Database>Create

The deployment gets started, as indicated by the message “Deployment in progress” in Figure 12. Click on the Refresh button periodically.

Figure 12. Deployment in progress

A new SQL Database instance gets created, as shown in Figure 13. Click on the sql-db link.

Figure 13. New Database sql-db

The SQL Database instance detail gets displayed, as shown in Figure 14.

Figure 14. SQL Database Instance Detail

Adding Connectivity to a SQL Database Instance from a Local Machine

As the SQL Database instance is running on Azure and Toad for SQL Server is on a local machine we need to configure connectivity to SQL Database instance from local machine.  In Overview click on Set Server firewall as shown in Figure 15.

Figure 15. Set server firewall

Allow access to Azure services should be set to ON, as shown in Figure 16. We also need to add the Client IP address to the list of IPs; from which, connections to all the databases in server sql-server-db-azure are provided.  Copy the Client IP address, which would be different for different users.

Figure 16. Client IP address

Click on Add client IP as shown in Figure 17 to add the client IP address to the list of allowed IPs.

Figure 17. Add client IP

The Client IP gets added to the list of allowed IPs for connecting to the databases, as shown in Figure 18. Click on Save.

Figure 18. Saving Client IP to the list of allowed IPs

As the message “Successfully updated firewall rules for server sql-server-db-azure” in Figure 19 indicates, a firewall rule gets added for the Client IP.

Figure 19. Successfully saved firewall rule for Client IP

Click on OK in the Success dialog as shown in Figure 20.

Figure 20. Confirmation dialog for adding a firewall rule

Click on Close as shown in Figure 21.

Figure 21. Firewall Settings>Close

Creating a Connection in Toad for SQL Server

In this section we shall create a connection in Toad for SQL Server to the SQL Server database on Azure. Select Connect>New Connection as shown in Figure 22.

Figure 22. Connect>New Connection

In the Create New Connection wizard we would need to specify the server name. Copy the server name from the Overview as listed in the Server name attribute in Figure 23.

Figure 23. Obtaining Server name from Overview

In the Create New Connection wizard specify the Server name in the Server name field as shown in Figure 24. Select Authentication as SQL Server Authentication. Specify Login and Password as configured earlier in Figure 8. Click on the selector button (…) to select the Database.

Figure 24. Create New Database

If we had not added a firewall rule for the Client IP address we would not be able to access the server sql-server-db-azure.  In Browse Databases select a database; for example, User Databases>sql-db, as shown in Figure 25. Click on OK.

Figure 25. Browse Databases

The sql-db database gets selected. Click on Connect as shown in Figure 26.

Figure 26. Connect

A connection gets added to the Connection Manager, as shown in Figure 27. In the Object Explorer sql-db is the only database listed.

Figure 27. Connection added Connection Manager

Conclusion

In this article we discussed using Toad for Server with SQL Server on Azure cloud service. Toad for SQL Server may be used to create a table with Create Table wizard, add table data with SQL Editor, run query with Query Builder, create a database, explore database objects with Database Explorer, and compare schemas.

Not ready for a free trial? Learn more.

Use the links below to learn how you can use the Toad for SQL Server management tool to resolve issues, manage change and promote the highest levels of code quality, performance and scalability.

Product page: Toad for SQL Server

Tech brief: Solving the SQL Tuning Problem: Secrets of Quest SQL Optimizer

Case study: County enhances critical services for citizens with Toad®

Related blog posts:

Read more Toad World® blog posts on Toad for SQL Server.

Side note: ApexSQL adds to Quest SQL solutions

As an aside, since you’re managing SQL Server, did you know that ApexSQL is now a part of Quest? ApexSQL joined Quest this summer. We’re excited to nod our heads that yes, the ApexSQL portfolio enhances Quest’s already robust set of SQL Server management tools. Make sure to visit ApexSQL.

Got questions?

If you have any questions, please post questions to the Toad for SQL Server forum on Toad World.