Using Toad for SQL Server with SQL Database on Azure

    Sep 14, 2018 2: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.