Toad World Blog

Using Toad for SQL Server with AWS Fargate - 2

Oct 22, 2018 4:07:58 PM by Deepak Vohra

In this continuation article we shall connect with the ECS service using Toad for SQL Server. Previously, in the first article of this series on using Toad for SQL Server 6.8 with SQL Server 2017 with Amazon ECS on Fargate launch type, we discussed creating an ECS service using the Docker image for SQL Server 2017. SQL Server 2017 is supported on Linux and Windows OS. SQL Server 2017 also provides Docker images for both Linux and Windows.  

This article has the following sections.

Creating a Connection

Creating a Table

Importing Table Data

Creating a Connection

To create a new connection select Connect>New Connection as shown in Figure 1.

Figure 1. Select Connect then New Connection from pulldown

Figure 1. Connect>New Connection

The Create New Connection window gets displayed, as shown in Figure 2. With the Login tab selected specify Server name as the Public IP of the ECS Service instance, which is obtained as discussed in the first article. Select Authentication as SQL Server Authentication.  Specify Login as SA and Password as the password configured in the SA_PASSWORD environment variable when creating the container definition for the SQL Server 2017 based ECS service.  Next, we need to specify a database; the database name does not have to be known in advance. Click on the button for the Database field to select a database.

Figure 2. Create New Connection window

Figure 2. Create New Connection

Select a database (tempdb) from Browse Databases as shown in Figure 3.

 Figure 3. Selecting a database to connect to

Figure 3. Selecting a Database

From the Category drop-down select the Test category as shown in Figure 4.

Figure 4. Selecting the category as Test

Figure 4. Selecting Category as Test

Click on Connect to create a connection as shown in Figure 5.

Figure 5. Clicking on Connect

Figure 5. Create New Connection>Connect

A new connection gets created and gets added to the Connection Navigator, as shown in Figure 6.

Figure 6. A new connection is created.

Figure 6. New Connection Created

Creating a Table

In this section we shall create a database table (wlslog) for a WebLogic server log with columns logid, category, type, servername, code, and msg. The database table is an example table; any table may be created using the same procedure. To create a new table click on Create a table in Object Explorer as shown in Figure 7.

Figure 7. Clicking on Create a Table

Figure 7. Object Explorer>Create a table

The Create Table wizard gets started, as shown in Figure 8. Specify table name as wlslog in the Name field.

Figure 8. The Create Table wizard

Figure 8. Create Table Wizard

Modify the default column to logid of type int and set the column as a primary key column by clicking in the PK column of the wizard. Select the checkbox in the Not Null column of the wizard. Click on Add to add each of the other columns as shown in Figure 9.

Figure 9. Adding the PK column

Figure 9. Adding PK Column

After adding all the columns click on OK as shown in Figure 10.

Figure 10. Clicking on OK to create the table

Figure 10. Create Table>OK

A Toad for SQL Server dialog indicates that the table has been successfully created, as shown in Figure 11.

Figure 11. Toad for SQL Server dialog indicates that the table has been created

Figure 11. Table successfully created

The new table gets listed in the Object Explorer, as shown in Figure 12.

Figure 12. The new table is listed in the Object Explorer

Figure 12. Table added to Object Explorer

Right-click on the table in Object Explorer to display the several options available for the new table as shown in Figure 13.

Figure 13. Right click on the table to show the options available for it

Figure 13. Table Options

Importing Table Data

In this section we shall add data to the wlslog table using the Import wizard. Create a data file wlslog.csv and save the following listing in which the first row is for column headers to the file.

logid,category,type,servername,code,msg

1,Notice,WebLogicServer,AdminServer,BEA-000365,Server state changed to STANDBY

2,Notice,WebLogicServer,AdminServer,BEA-000365,Server state changed to STARTING

3,Notice,WebLogicServer,AdminServer,BEA-000365,Server state changed to ADMIN

4,Notice,WebLogicServer,AdminServer,BEA-000365,Server state changed to RESUMING

5,Notice,WebLogicServer,AdminServer,BEA-000361,Started WebLogic AdminServer

6,Notice,WebLogicServer,AdminServer,BEA-000365,Server state changed to RUNNING

7,Notice,WebLogicServer,AdminServer,BEA-000360,Server started in RUNNING mode

Next, launch the Explorer from the toolbar as shown in Figure 14.

Figure 14. Click the icon to launch the Explorer.

Figure 14. Explorer

Select the dbo.wlslog table in the Explorer as shown in Figure 15.

Figure 15. Selecting the table in the Explorer

Figure 15. Selecting Table in Explorer

Select the Data tab. Just the column names are listed, as a new table is empty, as shown in Figure 16.

Figure 16. The data tab

Figure 16. Data Tab

Select Tools>Import>Import Wizard to launch the import wizard as shown in Figure 17.

Figure 17. Launching the Import Wizard

Figure 17. Tools>Import>Import Wizard

The Import Wizard gets launched, as shown in Figure 18. Click on Next in the Welcome screen.

Figure 18. Import Wizard welcome screen

Figure 18. Import Wizard Welcome Screen

Next, the Add Import Source page is displayed, as shown in Figure 19. Click on Add File…

Figure 19. On the Add Import Source page, click on Add File

Figure 19. Add Import Source>Add File

Select the wlslog.csv file and click on Open. The File Preview displays a preview of the data to be imported, as shown in Figure 20.  Keeping the default settings, click on Next.

Figure 20.  File Preview

Figure 20. File Preview

Next, define or select the columns to be included in the import. The columns are listed with Sample Data, as shown in Figure 21. A column may be removed with Remove and added with Add. To select all the columns keep the default columns and click on Next.

Figure 21. Define Columns

Figure 21. Define Columns

Next, select Target database as dbo.wlslog as shown in Figure 22.

Figure 22. Selecting the target database

Figure 22. Selecting Target Database

The Source field to Target table column mapping gets displayed, as shown in Figure 23. Click on Next.

Figure 23. Source field to target table column mapping

Figure 23. Source Field to Target Table Column Mapping

The db.wlslog file gets added as an import source, as shown in Figure 24.  Click on Next.

Figure 24. The import source file is added

Figure 24. Source file added

An option is provided to save settings as template or start import, as shown in Figure 25. Keep the default setting of Import Data Now and click on Finish.

Figure 25. Starting data import

Figure 25. Starting Data Import

The Import Finished dialog indicates that 7 rows have been imported, as shown in Figure 26. Click on OK.

Figure 26. The Import Finished dialog

Figure 26. Import Finished

Click on Refresh in the Explorer with the Data tab selected as shown in Figure 27. The data imported gets displayed.

Figure 27.  The imported data is displayed

Figure 27. Data Imported

Conclusion

In two articles we have discussed using Toad for SQL Server 6.8 with SQL Server 2017 on AWS ECS with Fargate launch type. The benefit of using AWS ECS is a hosted environment for Docker. The benefit of using Toad for SQL Server is an integrated development environment for connecting to and using SQL Server 2017.

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.