Popdock Data Lake Upload Tool Installation and Usage Guide for SQL Server

Overview

The Data Lake Upload Tool is designed to help customers move their on-premises data into the cloud. It can be used to perform regular data backups or migrate legacy data to cloud storage for faster access through Popdock. Once data has been transferred to the data lake, it is stored, managed, and accessed from this central repository.

This installation guide will support eOne Partners and services consultants through setting up and using the Data Lake Upload Tool to migrate customer data from SQL Server. This guide includes instructions on preparing for the installation, installing the tool, and using the tool to port data from SQL Server to an Azure data lake or an Amazon S3 storage account.

In addition to running the Popdock Data Lake Upload Tool, we recommend storing a backup of your database someplace (can be within the same Data Lake) in the event you need it in the future.

Prepare for the installation

To ensure a successful install and migration of your systems using the tool, you will want to:

• Complete pre-installation tasks
• Meet minimum hardware/software guidelines
• Follow network recommendations
• Create an account credentials list

Pre-installation tasks

There are two pre-installation tasks the Data Lake Upload Tool requires and should be completed before installing it. An existing Azure Data Lake or Amazon S3 setup is required along with an Azure Data Lake or Amazon S3 connector configured in Popdock which connects to it.

The tool needs a running Data Lake with storage containers/buckets. You can use this article to set up an Azure Data Lake or this article for setting up Amazon S3.

Once the data lake containers or buckets have been created, you will add an Azure Data Lake Connector or an Amazon S3 Connector to your Popdock configuration. After either your Azure Data Lake or Amazon S3 Connector is created, you will have the necessary pre-installation tasks completed.

Hardware/Software Guidelines

The Data Lake Upload Tool requires that the computer running the tool is connected to the same network as the database server, has enough processing power, storage space and memory for the tool’s data transfer processes and meets the minimum supported Operating System version guidelines.

Minimum hardware requirements:

CPU1GHZ Processor
Memory8 GB
Disk Space (free)Recommend 100 GB

Minimum software requirements:

Client OSWindows 8 or higher
ServerWindows Server 2012 or higher

Minimum database requirements:

SQL ServerSQL Server 2012 or higher

For technical support with earlier versions of SQL Server, contact support@eonesolutions.com.

Network Recommendations

The internet connection should be at least 5 Mbps upload to support the file transfer processes of the tool. The network and internet connection play a key role in the data transfer experience because low bandwidth internet and poor network connections can lead to slower transfer speeds and connection timeouts. While the 5 MBPs internet speed is a minimum guideline, a faster internet connection may not always guarantee faster data transfer speeds.

Account Credentials List

The installation process requires login information for multiple accounts to provide access to resources and systems involved in the upload process.

Use the list below to populate the necessary account information and have it available during the Data Lake Upload Tool installation.

Popdock Credentials – This is login information for the Popdock account.

System: PopdockDescriptionEnter your information
Username[The Popdock admin account username.] 
Password[The Popdock account password.] 
Account[The login account name] 

Azure Data Lake Connector Credentials – This information can be gathered from either the Azure Data Lake Connector or the Azure Console if using an Azure Data Lake. If using Amazon S3 storage, this information can be gathered from the Amazon S3 Connector.

For Azure Data Lake:

System: Azure Data LakeDescriptionEnter your information
Data Lake Connector[The name of your Popdock Data Lake Connector] 
Storage Account[The name of the storage account in the Azure console or your Popdock Data Lake Connector configuration] 
Table Container[The storage account container, in the Azure console, where all tables/views will be copied] 
List Container[The storage account container, in the Azure console, where all lists will be copied] 
Shared Key[The Shared key for the storage account in the Azure console] 

For Amazon S3:

System: Amazon S3DescriptionEnter your information
Data Lake Connector[The name of your Popdock Data Lake Connector] 
AWS Region[The name of the region your AWS S3 bucket is stored in] 
Bucket[The storage account container, in Amazon S3, where all lists will be copied] 
Access key[The Access key for the S3 account in AWS] 
Secret key[The Secret key for the S3 account in AWS]   

Database Credentials – This information is from your database server.

SystemSQL SERVEREnter your information
Server[The hostname of the SQL Server] 
Port[The port being used by the SQL Server] 
System Database[The name of the SQL Server system database] 
Username[A SQL Server user with read access to the system database] 
Password[The password for the SQL Server user] 

Run the setup wizard installation

The Data Lake Upload Tool Setup Wizard is an installer that places the files necessary to run the upload tool on the computer.

Before running the setup wizard installation, make sure you have completed the required tasks under prepare for the installation.

To install the Data Lake Upload Tool:

1. This is the welcome page, select Next to continue.


2. In Select Installation Folder, a default installation folder for the Data Lake Upload Tool files is selected.

You can change the installation folder location, where the files to run the tool are copied, by selecting Browse and selecting another folder.


3. Select the default installation folder location, and then Next to continue.


4. The Setup Wizard will install the software required by the upload tool.


5. If the Windows Desktop Runtime 6.0.13 is not installed, on the computer that will run the Data Lake Upload Tool, it will be installed when you press the Install button.


6. You will know the .NET Runtime installation is complete when you see this window, press the Close button.


7. The setup will resume the remaining steps in the installation automatically.


8. When you see this window, the setup wizard has successfully completed the installation, select Finish.


Copying Tables from SQL Server

To use the tool, make sure you have completed the tasks under prepare for the installation and run the setup wizard installation.

Once the setup wizard is completed successfully, the files necessary to run the Data Lake Upload Tool are installed on the computer and the tool is ready to use.

When porting your legacy data, it is recommended that you migrate both the tables and views on your SQL Server. In this section, you will migrate the tables and in the next section the views will be selected.

You will need the account credentials list to use the Data Lake Upload Tool. If you do not have the list, go back to the prepare for the installation section and complete the list.

Go to the Start Menu and select Popdock – Data Lake Upload Tool.


Welcome Page

On the welcome page, you will have the option to copy from different systems. Here we will look at options that apply to SQL Server.

To get started, you will want to make a copy of the tables from your SQL Server. Once you have a copy of these tables, you will re-run the tool to copy all the views.

1. Select the dropdown arrow on the right, to view the available options.


2. Select SQL Server- Copy tables, then select Continue.


Log in to your Popdock account

At the Log in to Popdock screen, you will provide the Popdock username, password, and account information for your account, which will be validated by the tool. The Popdock login entered should be the admin of the account.

1. At the log in screen, enter the Username and Password for the Popdock account admin, then select Log in to validate the credentials.

2. If the login information cannot be confirmed as entered, you will see the error message below. Check your Popdock login username/password, then enter the information and select Log in.

The installation will not move to the next page until your Popdock credentials are successfully verified.

3. If you have more than one account, an Account field will appear on a new page. Select the dropdown arrow, below account, to view the list of available accounts.


4. If the default account populated in the Account drop down is the Account you want, and then select Continue.


5. If the default account populated is not the account you want to continue with, select the new Account from the account dropdown menu and then select Switch account to move forward.


6. Once the login information and account is successfully validated, the installation process will automatically move to the next configuration page.

Connect to your storage account

At the Data Lake screen, you will provide connection information for your Azure data lake and connector in Popdock. The installer will verify the information entered then proceed to the next step in the installation.

You will need the account credentials list for this section.

For Azure Data Lake (for Amazon S3 storage go here):

1. Here you will select a data lake connector, enter the storage account, enter the container, and paste the shared key.


2. Select the dropdown arrow under Data Lake connector to display the list of Azure data lake connectors configured in your Popdock account.


3. Select a Popdock Azure Data Lake connector.


4. Enter the name of your Storage account.


5. Enter the name of your Table container.


6. Copy and paste the contents of your Shared key.


7. After entering the Azure Data Lake information, select Connect to validate.


8. If the storage account, container, or shared key information is entered incorrectly, the installation will not move to the Database page and the error message below will be displayed.


9. Check your credential list Azure data lake information, then re-enter information, and select Log in to validate.


1.0 Once the connection to your Azure Data Lake connection is successfully validated, the installation process will automatically move to the next page.

For Amazon S3:

1. Here you will select a data lake connector, enter the AWS region, enter the bucket, and paste both the access key and the secret key. 


2. Select the dropdown arrow under Data Lake connector to display the list of Data Lake connectors configured in your Popdock account.


3. Select a Popdock Amazon S3 Data Lake connector.


4. Enter the AWS region of your storage bucket (example format: us-east-2).


5. Enter the name of your AWS S3 Bucket.


6. Paste the contents of your Access key.


7. Paste the contents of your Secret key.


8. Select if you want to Use gzip. If selected, this will convert each list or table into a gzip file type. Gzip will provide performance gains when accessing the data via Popdock.


9. After entering your Amazon S3 information, select Connect to validate.


10. Once the connection to your Amazon S3 connection is successfully validated, the installation process will automatically move to the next page.


Connect to your database

At the Database screen, you will provide connection information for the SQL Server database. The installer will use the server and login information to test the connection to your database server.

You will need the account credentials list for this section.

1. Under Server, enter the hostname of your database server.


2. Enter the Port being used by your SQL Server to provide database services. The default port for database services is 1433, but before using it, confirm to find the TCP Port number your SQL SERVER Instance is listening on.


3. Enter the name of the system database.


4. Enter the SQL Server user credentials, under Username and Password.

This SQL Server login user does not need administrative privileges or be the sa account.

5. Once all the information for the database server is entered, select Connect to validate.


6. If any of the database server connection information is not entered correctly, an error message will appear, and the installer will not move to the next page.


7. To find additional information on common error messages, go to the troubleshoot the tool section.


8. If a successful connection is made, the installer will immediately run the tool.


Run

At the Run screen, the upload tool will make the necessary connections, download the data to the computer running the data lake upload tool, then upload it to the data lake.

Once the tool has finished running, you will see the Report screen.

1. The Report screen shows the final status of the import process.

NOTE: The tables that were selected have finished copying and the status shows that it was a Success. There were two lists with 0 records that were Skipped.

2. Now that the SQL tables have been copied, proceed to copying SQL Server views.


3. You can select Run Again on the Report page, to run the tool without having to re-enter your login credentials.


4. Select Run Again, the installer will return to the Welcome screen and start the configuration process again.


Copying Views from SQL Server

Migrate both the tables and views on your SQL Server to ensure all server data has been copied to your data lake. The tables were uploaded in the previous section, now select the views for migration.


Welcome Page

1. Select the dropdown arrow to view the Application/Process options.


2. Select SQL Server- Copy views, then select Continue.


Log in to your Popdock account

The Log in to Popdock screen will display the Popdock username, password, and account information you logged in with previously. You have two options at this screen, update your Popdock credentials or use the same authenticated credentials by selecting Next: Edit data lake settings.

1. To make changes to your Popdock Username and Password, re-enter your credentials, then select Log in to validate.

To use the same Popdock credentials, simply select the Log in button.


2. The Account used in the prior process displays by default. Select Continue to move forward with this account.


Connect to your storage account

The Data Lake screen will display the storage account information you logged in with previously. At this screen, you can update your data lake information or use the same information.

1. To update your data lake connector settings, enter the updated storage account credentials, then select Connect to re-validate.

To use the same Data Lake settings, select Connect.


Connect to your database

The Database screen will display the SQL Server database information you logged in with previously. At this screen, you can update your DB server and/or login information, or you can use the same authenticated information.

1. To update your database server information, make changes to your settings then select Connect to re-validate.

To continue with the same database information, select Connect to authenticate and move to the next screen.


2. If a successful connection is made, the installer will load the views.


Select Views

At the Select views page, you will choose the views you want to copy from the database.

1. Choose the Views you would like to upload, by selecting the box next to each view.

When selecting views, only choose the ones you use frequently.


2. Select Next: Run.


Run

The Data Lake Upload Tool will start the import process for the SQL Server views selected.

Below are the options available on the Run page:

Cancel the download

Select Cancel to stop the upload and make changes to the settings in the upload tool, i.e., Data Lake, Database, etc.


Track upload progress

1. The Progress section shows the overall status of the upload.

You can view the statuses below in the Progress section:

• “Running” is the total number to upload.
• “Pending” is the remaining number to upload.
• “Completed” is the number that uploaded successfully.
• “Failed” is the number that failed to upload due to an error.
• “Zero rows” is the number that were not uploaded due to having zero rows of data.


2. The Current process section displays the ongoing tasks the upload tool is performing and their status.


3. The Report screen will appear at the end of the upload.

You can view the statuses below in the Progress section:

• “Success” means the upload was successfully.
• “Failed” means the upload failed due to an error.
• “Skipped” means the upload was skipped due to zero rows of data.
• “Cancelled” means the upload was cancelled.


4. Select Run again to execute the tool, without having to re-enter login credentials.


5. Select View logs to access more detailed information about the uploaded lists or to investigate any list copy failures.


6. The View logs button will open the folder containing the log files. You can open the log files in a text editor to view detailed information on failures. You can use the log files to fix the upload errors or send them to support@eonesolutions.com for additional support.


7. After fixing the errors in the log file, you can select Re-run selected failures to retry copying the failed lists.

If you have a disruption in network access that stops the upload, you can use the Re-run selected failures button to re-run the process.

Troubleshoot the tool

Database “Login Failed” Error

If you get the “login failed for user” error, check that your username and password are:

• Typed correctly
• SQL Server login credentials
• Have access to the system database

For further assistance with connecting to your database server, contact support@eonesolutions.com.

Database “Cannot open database” Error

The “cannot open database requested by the login” error will appear, if the computer running the data lake upload tool cannot connect to the system database.

In that case, you should:

• Make sure the server name is spelled correctly.
• Confirm that the server port entered matches the port on the SQL Server.
• Check that the SQL Server is up and running.
• Check that the system database name is entered correctly.

For further assistance with connecting to your database server, contact support@eonesolutions.com.




Questions on the Data Lake Upload Tool? Email support at support@eonesolutions.com

Feeling stuck? Get the support and guidance you need to help you power through any data challenge

We're on your integration team. Connect with our people and let us know how we can help you.