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 columnslogid, category, type, servername, code, and msg. The primary key column islogid 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. 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
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
Click on Add to add another column as shown in Figure 4.
Figure 4. Columns>Add
Another dummy column gets added, as shown in Figure 5.
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
Click on Add as shown in Figure 7 to add the third 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. Create Table>OK
A confirmation dialog indicates that table has been created, as shown in Figure 9. Click on OK.
Figure 9. Table successfully created
Thewlslog table gets added to Object Explorer>Tables, as shown in Figure 10. The columns get displayed.
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. Edit SQL
A SQL Editor gets launched, as shown in Figure 12.
Figure 12. SQL Editor
Copy the SQL script to the SQL Editor and click on Execute scripts as shown in Figure 13.
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
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
The Query Builder gets started. Select the wlslog table in the Object Explorer as shown in Figure 16.
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 table to Query Builder
A diagram gets created from the table with selectable columns listed, as shown in Figure 18.
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
The Query tab displays the SQL query statement generate from the diagram, as shown in Figure 20.
Figure 20. SQL Query Statement
Click on Execute the SQL Statement as shown in Figure 21 to run the query.
Figure 21. Execute the SQL statement
The result set generated is displayed in Figure 22.
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
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
Click on the Data tab to display table data as shown in Figure 25.
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. 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
Specify the Password and click on Connect as shown in Figure 28.
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. 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. Set As Current…
The connection gets set as the current connection as shown in Figure 31.
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. Object Explorer>Databases>Create a database
The Create Database wizard gets started, as shown in Figure 33.
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. Create Database>OK
A confirmation dialog indicates that a new database has been created, as shown in Figure 35.
Figure 35. New Database created
The new database sqldb gets listed in Databases, as shown in Figure 36.
Figure 36. New Database added
The new databasesqldb also gets listed in Azure SQL Database service, as shown in Figure 37.
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 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. 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 wlslog Table in sqldb Database
The table created is shown in Figure 41.
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. 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
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. 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. Thesqldb database is shown to be selected as the first database in the first selected connection.
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
The sql-db database gets selected as the Source, as shown in Figure 47. Earlier the Source was set to sqldb.
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. 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. 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 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
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. 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
After the comparison has been completed, as shown in Figure 54, 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. 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
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
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
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.
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
The sync options are set in Comparison properties wizard, which may be launched from toolbar as shown in Figure 61.
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
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. Generate script…
The Schema Synchronization Wizard gets launched, as shown in Figure 64. Click on Next.
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 Synchronization Direction
Select/modify Synchronization Options as shown in Figure 66. Click on Next.
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
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
The synchronization script generated gets opened in Toad for SQL Server Editor, as shown in Figure 69.
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. SQL databases>Delete
In the Confirm delete dialog add “yes” and click on Delete as shown in Figure 71.
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
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.
Try Toad for SQL Server nowAre you just starting your journey to evaluate if you should purchase the Toad for SQL Server management tool? If you’re not quite ready to buy, we’re so sure you’ll love Toad for SQL Server, that we’re offering a Toad for SQL Server free 30-day trial. Already in a trial? Buy it now or contact sales. |
Not ready for a free trial? Learn more.
Use the links below to learn how you can use the Toad for SQL Server management tool to resolve issues, manage change and promote the highest levels of code quality, performance and scalability.
Product page: Toad for SQL Server
Tech brief: Solving the SQL Tuning Problem: Secrets of Quest SQL Optimizer
Case study: County enhances critical services for citizens with Toad®
Related blog posts:
Read more Toad World® blog posts on Toad for SQL Server.
Side note: ApexSQL adds to Quest SQL solutions
As an aside, since you’re managing SQL Server, did you know that ApexSQL is now a part of Quest? ApexSQL joined Quest this summer. We’re excited to nod our heads that yes, the ApexSQL portfolio enhances Quest’s already robust set of SQL Server management tools. Make sure to visit ApexSQL.
Got questions?
If you have any questions, please post questions to the Toad for SQL Server forum on Toad World.
Start the discussion at forums.toadworld.com