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.

    d1

    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.

    d2

    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.

    d3

    Figure 3. Setting logid as Primary Key

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

    d4

    Figure 4. Columns>Add

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

    d5

    Figure 5. Dummy Column Added

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

    d6

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

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

    d7

    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.

    d8

    Figure 8. Create Table>OK

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

    d9

    Figure 9. Table successfully created

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

    d10

    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.

    d11

    Figure 11. Edit SQL

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

    d12

    Figure 12. SQL Editor

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

    d13

    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.

    d14

    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.

    d15

    Figure 15. Selecting Build in toolbar

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

    d16

    Figure 16. Selecting table in Object Explorer

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

    d17

    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.

    d18

    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.

    d19

    Figure 19. Adding All Columns

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

    d20

    Figure 20. SQL Query Statement

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

    d21

    Figure 21. Execute the SQL statement

    The result set generated is displayed in Figure 22.

    d22

    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.

    d23

    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.

    d24

    Figure 24. Database Explorer with dbo.wlslog Table Selected

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

    d25

    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.

    d26

    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.

    d27

    Figure 27. Create New Connection

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

    d28

    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.

    d29

    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.

    d30

    Figure 30. Set As Current…

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

    d31

    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.

    d32

    Figure 32. Object Explorer>Databases>Create a database

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

    d33

    Figure 33. Create Database Wizard

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

    d34

    Figure 34. Create Database>OK

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

    d35

    Figure 35. New Database created

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

    d36

    Figure 36. New Database added

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

    d37

    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.

    d38

    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.

    d39

    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.

    d40

    Figure 40. Creating wlslog Table in sqldb Database

    The table created is shown in Figure 41.

    d41

    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.

    d42

    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.

    d43

    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.

    d44

    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.

    d45

    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. 

    d46

    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.

    d47

    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.

    d48

    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.

    d49

    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.

    d50

    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.

    d51

    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.

    d52

    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.

    d53

    Figure 53. Comparison Started

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

    d54

    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.

    d55

    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.

    d56

    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.

    d57

    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.

    d58

    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

    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.

    d60

    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

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

    d62

    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.

    d63

    Figure 63. Generate script…

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

    d64

    Figure 64. Schema Synchronization Wizard

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

    d65

    Figure 65. Selecting Synchronization Direction

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

    d66

    Figure 66. Synchronization Options

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

    d67

    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.

    d68

    Figure 68. Schema Synchronization Wizard Complete

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

    d69

    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.

    d70

    Figure 70. SQL databases>Delete

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

    d71

    Figure 71. Confirm delete

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

    d72

    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.