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.