Toad World Blog

Using Toad for SQL Server with SQL Database on Azure

Sep 14, 2018 1:19:21 PM by Deepak Vohra

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.

d1

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.

d2

Figure 2. Selecting SQL databases in Azure Portal

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

d3

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.

d4

Figure 4. Specifying SQL Database Detail

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

d5

Figure 5. Selecting a Source Database

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

d6

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.

d7

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.

d8

Figure 8. Selecting Server Settings

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

d9

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.

d10

Figure 10. Selecting Pricing Tier as Basic

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

d11

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.

d12

Figure 12. Deployment in progress

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

d13

Figure 13. New Database sql-db

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

d14

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.

d15

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.

d16

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.

d17

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.

d18

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.

d19

Figure 19. Successfully saved firewall rule for Client IP

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

d20

Figure 20. Confirmation dialog for adding a firewall rule

Click on Close as shown in Figure 21.

d21

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.

d22

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.

d23

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.

d24

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.

d25

Figure 25. Browse Databases

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

d26

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.

d27

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.

 

 

 

Tags: Toad for SQL Server

Deepak Vohra

Written by Deepak Vohra

Deepak Vohra is an Oracle Certified Associate, Oracle Database 10g, and Sun Certified Java Programmer. Deepak has published on OTN and in Oracle Magazine.