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
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/.
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
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
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
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
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.