Toad World Blog

Using Toad for SQL Server with Azure SQL Database - II

Oct 2, 2018 1:15:24 PM by Deepak Vohra

Azure is one of the leading cloud service providers.  Azure SQL Database is a fully managed relational cloud database service for developers. In an earlier article we discussed creating an Azure SQL Database instance and connecting to the SQL Server with Toad for SQL Server. In this continuation article we shall discuss some of the Toad for SQL Server features with the Azure SQL Database service. This article has the following sections.

Creating a Database Table with Create Table Wizard

Adding Table Data with SQL Editor

Querying Table with Query Builder

Exploring with Database Explorer

Setting Current Connection

Creating a Database

Comparing Schemas

Deleting SQL Database Instance on Azure

Creating a Database Table with Create Table Wizard

We shall create a table wlslog with columns logid, category, type, servername, code, and msg. The primary key column is logid of type int and all the other columns are of type VARCHAR. To create a table click on Create a table in Object Explorer with the Tables tab selected and the sql-db database selected as shown in Figure 1.

Figure 1. Clicking on Create a Table

Figure 1. Object Explorer>Tables>Create a table

The Create Table wizard gets launched as shown in Figure 2. The Database is preset to sql-db and the Schema is preset to dbo. A different Schema may be selected from the drop-down.

Figure 2. Create Table wizard

Figure 2. Create Table wizard

Specify a table name in Name (wlslog) field. A default first column is provided with Column Name and Type set to dummy (place-holder) values.  Modify Column Name to logid and keep data type in Type drop-down asint, which is the default data type.

Select the Not Null checkbox and click on Set Primary Key set the column as primary key as shown in Figure 3.

Figure 3. Setting logid as primary key

Figure 3. Setting logid as Primary Key

Click on Add to add another column as shown in Figure 4.

Figure 4. Adding a column

Figure 4. Columns>Add

Another dummy column gets added, as shown in Figure 5.

Figure 5. Dummy column added

Figure 5. Dummy Column Added

Set column name to category and select data type as varchar(50) as shown in Figure 6.

Figure 6. Selecting data type as varchar(50) for category column

Figure 6. Selecting Data Type as varchar(50) for category column

Click on Add as shown in Figure 7 to add the third column.

Figure 7. adding another column

Figure 7. Adding another column with Add

Similarly, add each of the other columns as shown in Figure 8. Click on OK when all columns have been added.

Figure 8. Clicking on OK

Figure 8. Create Table>OK

A confirmation dialog indicates that table has been created, as shown in Figure 9. Click on OK.

Figure 9. Confirmation that the table was created

Figure 9. Table successfully created

The wlslog table gets added to Object Explorer>Tables, as shown in Figure 10. The columns get displayed.

Figure 10. wlslog added in Object Explorer

Table 10. Table wlslog added in Object Explorer

Adding Table Data with SQL Editor

In this section we shall add table data using the following DML SQL script.

INSERT INTO wlslog VALUES(1,'Notice','WebLogicServer','AdminServer','BEA-000365','Server state changed to STANDBY');

INSERT INTO wlslog VALUES(2,'Notice','WebLogicServer','AdminServer','BEA-000365','Server state changed to STARTING');

INSERT INTO wlslog VALUES(3,'Notice','WebLogicServer','AdminServer','BEA-000365','Server state changed to ADMIN');

INSERT INTO wlslog VALUES(4,'Notice','WebLogicServer','AdminServer','BEA-000365','Server state changed to RESUMING');

INSERT INTO wlslog VALUES(5,'Notice','WebLogicServer','AdminServer','BEA-000361','Started WebLogic AdminServer');

INSERT INTO wlslog VALUES(6,'Notice','WebLogicServer','AdminServer','BEA-000365','Server state changed to RUNNING');

INSERT INTO wlslog VALUES(7,'Notice','WebLogicServer','AdminServer','BEA-000360','Server started in RUNNING mode');

To run the SQL we need to launch the SQL  Editor by selecting Edit from the toolbar as shown in Figure 11.

Figure 11. Launching the SQL editor

Figure 11. Edit SQL

A SQL Editor gets launched, as shown in Figure 12.

Figure 12.  The SQL Editor

Figure 12. SQL Editor

Copy the SQL script to the SQL Editor and click on Execute scripts as shown in Figure 13.

Figure 13. Clicking on Execute Scripts

Figure 13. Execute scripts

The SQL runs to add data. As a message in Messages tab indicates, 7 rows are affected, as shown in Figure 14.

Figure 14. Data added and 7 rows affected

Figure 14. Data added and 7 rows affected

Querying Table with Query Builder

Next, we shall query the table created; for which, we shall need to launch the Query Builder.  Click on Build in the toolbar as shown in Figure 15.

Figure 15. Selecting Build in toolbar

Figure 15. Selecting Build in toolbar

The Query Builder gets started. Select the wlslog table in the Object Explorer as shown in Figure 16.

Figure 16. Selecting the table in Object Explorer

Figure 16. Selecting table in Object Explorer

Drag and drop  the table to the Query Builder Diagram pane as shown in Figure 17.

Figure 17. Dragging and dropping the table into Query Builder

Figure 17. Dragging and Dropping table to Query Builder

A diagram gets created from the table with selectable columns listed, as shown in Figure 18.

Figure 18. table diagram

Figure 18. Table Diagram

Columns to include in a query may be selected individually by selecting checkboxes corresponding to the columns, or collectively by selecting the Add All Columns checkbox, as shown in Figure 19.

Figure 19. Adding all columns

Figure 19. Adding All Columns

The Query tab displays the SQL query statement generate from the diagram, as shown in Figure 20.

Figure 20. SQL Query Statement

Figure 20. SQL Query Statement

Click on Execute the SQL Statement as shown in Figure 21 to run the query.

Figure 21. Clicking on Execute the SQL statement

Figure 21. Execute the SQL statement

The result set generated is displayed in Figure 22.

Figure 22. Result set

Figure 22. Result Set

Exploring with Database Explorer

To explore database objects, Toad for SQL Server provides the database Explorer. Click on Explorer in the toolbar as shown in Figure 23.

Figure 23. Selecting Explorer in the toolbar

Figure 23. Selecting Explorer in the Toolbar

The Explorer gets launched, as shown in Figure 24.  Select the dbo.wlslog table and its properties get displayed in the Properties tab.

Figure 24. Database Explorer with dbo.wlslog Table Selected

Figure 24. Database Explorer with dbo.wlslog Table Selected

Click on the Data tab to display table data as shown in Figure 25.

Figure 25. Clicking on the Data tab to display table data

Figure 25. Table Data

Setting Current Connection

If the Connection Navigator has only one connection the connection is the current connection but if multiple connections are created only one connection may be selected as the current connection. To demonstrate, first we shall create multiple connections. Right-click on the one connection in Connection Navigator and select Create Like… as shown in Figure 26.

Figure 26. Selecting Create Like...

Figure 26. Connection Navigator>sql-server.db.azure.database.net>Create Like…

The Create New Connection gets displayed, similar to when creating a new connection, but with the difference that all the field values except the Password  are pre-specified, as shown in Figure 27.

Figure 27. Create New Connection window

Figure 27. Create New Connection

Specify the Password and click on Connect as shown in Figure 28.

Figure 28. Clicking on Connect

Figure 28. Create New Connection>Connect

A new connection gets created and gets added to the Connection Navigator, as shown in Figure 29. The new connection is indistinguishable from the connection copied from. But only one connection gets set as the current connection, as indicated by the icon with a dotted square. The connection that is not the current connection does not have the dotted square.

Figure 29. An icon indicates the current connection

Figure 29. Only One Connection is the Current Connection

To make the connection that is not current as the current connection right-click on the connection and select Set As Current… as shown in Figure 30.

Figure 30. Selecting Set As Current...

Figure 30. Set As Current…

The connection gets set as the current connection as shown in Figure 31.

Figure 31. Connect set as Current

Figure 31. Connection set as Current

Creating a Database

When we created the Azure SQL Database service we created only one database instance, sql-db, which is listed in the Object Explorer. Additional database instances may be added to Azure SQL Database Service from Toad for SQL Server. To create a new database select the Databases tab and click on Create a database as shown in Figure 32.

Figure 32. Clicking on Create a database

Figure 32. Object Explorer>Databases>Create a database

The Create Database wizard gets started, as shown in Figure 33.

Figure 33. The Create Database wizard

Figure 33. Create Database Wizard

Specify a database name (sqldb) in the Name field and click on OK as shown in Figure 34.

Figure 34. Clicking on OK

Figure 34. Create Database>OK

A confirmation dialog indicates that a new database has been created, as shown in Figure 35.

Figure 35. Confirmation message that a new database has been created

Figure 35. New Database created

The new database sqldb gets listed in Databases, as shown in Figure 36.

Figure 36. The new database gets listed in Databases

Figure 36. New Database added

The new database sqldb also gets listed in Azure SQL Database service, as shown in Figure 37.

Figure 37. The new database is listed in the Azure SQL database service

Figure 37. New Database Added in Azure SQL Database Service

Toad for SQL Server may be used as a tool to create a new database instance in Azure SQL Database service, as demonstrated.

Comparing Schemas

Toad for SQL Server may be used to compare different schemas, too.  To perform a schema comparison we would need to provide a source database and a target database.  To demonstrate schema comparison it would be suitable to create tables that are different so that the comparison includes some comparable result. We shall use the sql-db with its wlslog table as one of the databases for schema comparison. For the second database we shall use the sqldb database that we created. Add a table also called wlslog to the sqldb database but make the table columns different.

Creating the Second Table

To create a new table in the sqldb database, first select the database, as shown in Figure 38.

Figure 38. Selecting the database sqldb

Figure 38. Selecting Database as sqldb

To create a new table click on Create a table as before and as shown in Figure 39. The database must have been selected as sqldb.

Figure 39. Clicking on Create a table

Figure 39. Create a table

Create a new table, which is also called wlslog, with the procedure discussed in detail when creating the first wlslog table.  The wlslog table in the sqldb database has only three columns: logid, code, and msg, as shown in Figure 40.

Figure 40. Creating the wlslog table in the sqldb database

Figure 40. Creating wlslog Table in sqldb Database

The table created is shown in Figure 41.

Figure 41. The new table wlslog in sqldb

Figure 41. New Table wlslog in sqldb

Launching the Schema Compare Wizard

To launch the Schema Compare Wizard select Compare in the toolbar or right-click on a table in Object Explorer and select Schema Compare>Compare and sync schema objects as shown in Figure 42.

Figure 42. Launching the Schema Compare wizard

Figure 42. dbo.wlslog>Schema Compare>Compare and sync schema objects

The Schema Compare Wizard gets launched, as shown in Figure 43. Click on Next.

Figure 43. Schema Compare Wizard welcome screen

Figure 43. Schema Compare Wizard

Selecting Source and Target Databases

Next, the Specify Source and Target Databases screen gets displayed, as shown in Figure 44. Default Source and Target databases may have been selected. Click on Add to add a source and a target.

Figure 44. Clicking on Add

Figure 44. Specify Source and Target Databases

The Select source and target (s) window gets displayed, as shown in Figure 45. The first selected connection is set as source.  A selected connection gets added to the Selected Items column with the x option added to remove the connection from the Selected Items. For each connection select databases or snapshots or Azure SQL Database federations. The sqldb database is shown to be selected as the first database in the first selected connection.

Figure 45. The Select source and targets window

Figure 45. Select source and target(s)

The connection and database for each of source and target(s) for schema comparison may be modified. As an example, in the selected connection select the sql-db database as shown in Figure 46. Click on OK. 

Figure 46. Selecting connection and database

Figure 46. Selecting Connection and Database

The sql-db database gets selected as the Source, as shown in Figure 47. Earlier the Source was set to sqldb.

Figure 47. The database sql-db is selected as Source

Figure 47. Database sql-db selected as Source

After both source and target connections have been selected click on OK as shown in Figure 48.

Figure 48. Clicking on OK

Figure 48. Both Source and Target Connections and Objects selected

Both the Source and Target Databases are listed in the Schema Compare wizard in Figure 49. Click on Next.

Figure 49.  Clicking on Next

Figure 49. Schema Compare Wizard>Specify Source and Target>Next

Selecting Objects and Comparison Options

Next select the objects to compare. To select an object click on Add.  To remove all objects click on Remove All. To compare the whole database skip selecting objects.  We have selected the dbo.wlslog table for comparison, as shown in Figure 50. Click on Next.

Figure 50. Selecting objects to compare

Figure 50. Selecting Object/s to Compare

Next, select comparison options from those displayed in the Select Comparison Options wizard, as shown in Figure 51. Click on Next.

Figure 51. Selecting comparison options

Figure 51. Selecting Comparison Options

Performing the Schema Comparison

The Schema Comparison gets started, as shown in Figure 52. First, the sources are loaded and data from source database started.

Figure 52. Loading Sources progress bar

Figure 52. Schema Comparison started

After data from both the source and target databases has been loaded, the comparison starts, as shown in Figure 53. The comparison is performed by loading data to memory.

Figure 53. Comparison Started

Figure 53. Comparison Started

After the comparison has been completed, as shown in Figure 54, click on Next.

Figure 54. Process completed. Click on Next

Figure 54. Comparison Completed

Evaluating Comparison Results

As shown in Figure 55, the Schema Compare Wizard gets completed. The results  summary of the comparison gets displayed. The schema compare result includes Objects existing only on source, Objects existing only on target, Identical objects, Different objects existing in both, and Objects that couldn't be compared. Click on Finish.

Figure 55. Notification that Schema Compare Wizard is Complete. Click on Finish

Figure 55. Schema Compare Wizard Completed

The Schema Compare detail gets displayed, including the objects compared and the differences in the objects' DDL scripts, as shown in Figure 56. The DDL script section/s that are in the target but not in the source are highlighted in a different color (red by default). The toolbar in the Schema Compare results window has options to Change current Comparison, Create new Comparison, Comparison properties, and Change comparison direction without reloading data from databases.

Figure 56. Schema Compare Objects and DDL Scripts

Figure 56. Schema Compare Objects and DDL Scripts

The Script toolbar has options for Ignore case, Ignore whitespace, Next Difference, and Previous Difference. Right-click in a script to display some of the same options such as Next and Previous and some new options such as Go to Line… and Save As… as shown in Figure 57.

Figure 57. Script Options

Figure 57. Script Options

To make the source database as the target and the target the source click on Change comparison direction without reloading data from databases as shown in Figure 58.

Figure 58. Change comparison direction without reloading data from databases

Figure 58. Change comparison direction without reloading data from databases

The comparison direction gets modified, as shown in Figure 59. The DDL Script sections that are in the source schema object but not in the target schema object are highlighted in green.

d59 Figure 59. Comparison result with direction reversed

Figure 59. Comparison result with direction reversed

Using the Schema Synchronization Wizard

The Schema Synchronization wizard could be useful if schemas have differences and a synchronization has to be applied to make the target as the source or vice versa. The synchronization statuses may be displayed by selecting from the Synchronization statuses Sync and No sync in Filter as shown in Figure 60.

Figure 60. Selecting Synchronization statuses

Figure 60. Selecting Synchronization statuses

The sync options are set in Comparison properties wizard, which may be launched from toolbar as shown in Figure 61.

d61 Figure 61. Selecting Comparison properties wizard

Figure 61. Selecting Comparison properties wizard

The sync options are set in Comparison properties>Sync Options, as shown in Figure 62.

Figure 62. Sync Options

Figure 62. Sync Options

Select/deselect the objects to include/exclude in the sync. To launch the Schema Synchronization Wizard and generate the synchronization script click on Generate Script as shown in Figure 63.

Figure 63. Clicking on Generate script…

Figure 63. Generate script…

The Schema Synchronization Wizard gets launched, as shown in Figure 64. Click on Next.

Figure 64. Schema Synchronization Wizard welcome screen

Figure 64. Schema Synchronization Wizard

Select direction of synchronization; select Make target like source as shown in Figure 65. Click on Next.

Figure 65. Selecting the synchronization direction

Figure 65. Selecting Synchronization Direction

Select/modify Synchronization Options as shown in Figure 66. Click on Next.

Figure 66. Synchronization options

Figure 66. Synchronization Options

The Object Synchronization Preview displays the objects to be synchronized, as shown in Figure 67. Click on Next.

Figure 67. Synchronization objects preview

Figure 67. Synchronization Objects Preview

The synchronization gets completed, as indicated by the Synchronization Wizard Complete in Figure 68. Different options are displayed for the synchronization script generated:

-Open the script in the Toad Editor (default)

-Open synchronization results

-Run script immediately

-Take no action

Click on Next with the default option Open the script in the Toad Editor.

Figure 68. Schema Synchronization Wizard Complete

Figure 68. Schema Synchronization Wizard Complete

The synchronization script generated gets opened in Toad for SQL Server Editor, as shown in Figure 69.

Figure 69. Synchronization Script

Figure 69. Synchronization Script

Deleting a SQL Database Instance on Azure

To delete one or more database instances in Azure SQL Database select the database instances and click on Delete as shown in Figure 70.

Figure 70. Deleting database instances in Azure SQL Database

Figure 70. SQL databases>Delete

In the Confirm delete dialog add “yes” and click on Delete as shown in Figure 71.

Figure 71. Confirming delete

Figure 71. Confirm delete

The selected databases get deleted from Azure SQL Database service, as shown in Figure 72.

Figure 72. Azure SQL Databases deleted

Figure 72. Azure SQL Databases  Deleted

Conclusion

In two articles we discussed using Azure SQL Database Service with Toad for SQL Server. Toad for SQL Server provides several features and tools including the features to create a database, create a table, run SQL queries, explore database objects, and compare and sync schemas.

 

Editor's note: Try the full version of Toad for SQL Server free for 30 days.

 

Tags: Toad for SQL Server Cloud

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.