Automated data transfer

Setting up automated data transfer

Introduction

Ausvet has established an agreement with a number of aquaculture companies in Chile to create an integrated Research Platform. This is a secure on-line database that integrates all relevant data from each company’s production database (e.g. FishTalk or Mercatus), along with other available data sources, to support rapid and powerful research on sanitary issues faced by the industry. The system will progressively integrate more data, such as records managed using spreadsheets or captured using a mobile application.

Two important features of the Research Platform are that it should not increase the workload of company staff, and that it should be sustainable and able to be used to address disease problems that may occur in the future. For these reasons, transfer of data from the company’s servers to the Research Platform should be completely automated.

This guide explains how to set up the automated data transfer. Setting up the automated transfer process should take no more than 15 or 20 minutes.

Contents

Quick start

Overview

Step-by-step instructions

Downloading previous backup copies

Help

Quick start

This is a quick guide for the impatient. It contains the key steps, but the following sections describe the same steps in more detail.

  1. Get your security credentials in an encrypted zip file from Ausvet
  2. Download and install the AWS CLI Client
    64 bit: https://s3.amazonaws.com/aws-cli/AWSCLI64.msi
    32 bit: https://s3.amazonaws.com/aws-cli/AWSCLI32.msi
  3. Configure the AWS CLI client
    At the command prompt, type: aws configure
    Enter your Key ID and Secret Key provided by Ausvet
    Default region: sa-east-1
    Default format: json
  4. Create a batch file by editing the following example to match your backup file and your private S3 bucket name (provided by Ausvet) and save it as copydb2s3.bat
Automated database backup transfer batch file: copydb2s3.bat
echo off
set filepath=C:\Backup
set company=empresa
for /f "tokens=*" %%a in ('dir %filepath%\*.bak /b /od') do set newest=%%a
set filename=%filepath%\%newest%
set destname=%newest%
set bucketname=s3://data.piisac.omnisyan.com/%company%/
aws s3 cp %filename% %bucketname%%destname%
  1. Test the batch file to make sure it works
  2. Create a Windows Scheduled Task to call the batch file once a week, after the backup has been created.

Read the rest of this document for more detailed instructions.
Go to the top of the page.

Overview

The objective is to transfer the most recent database backup securely and automatically from the company’s server to a secure S3 bucket in Ausvet’s research platform hosted in the Cloud on Amazon Web Services.

Approach

Two approaches are possible: push and pull.

The push approach is preferred. The transfer is initiated by the company’s server, and no changes to the company’s firewall or security settings are required. However, it does require setting up a scheduled task, and installing a simple utility to perform the encrypted transfer to AWS S3.

The pull approach involves Ausvet initiating the transfer but requires the company to modify its firewall and access permissions. This means more work for the company and is more complex, as care has to be taken to avoid security risks.

This document describes the recommended push approach. If for any reason, you would prefer to use the pull approach, contact Ausvet for detailed instructions.

Amazon Web Services

Amazon Web Services (AWS) is one of the world’s leading Cloud hosting providers. They provide a wide range of inexpensive virtual hardware, including virtual services (Elastic Cloud Computing or EC2) and virtual shared drives (Simple Storage Service or S3 buckets). All components are grouped in a secure virtual private cloud (VPC) with its own firewall and detailed access permissions.

Data transfer

To securely transfer data from any server to a specified S3 bucket, utility software is required, as well as security keys. Several software options are available, including interactive graphical user interface (GUI) programs. However, the most convenient and flexible for automated scripting is AWS’s command line interface (CLI) client. This allows users to interact directly with the AWS cloud from their local server.

Once the AWS CLI is installed, a batch file or script is created. This identifies the most recent database backup and initiates the transfer to the specified S3 bucket using the required security credentials. A standard script can be used, but several parameters (file paths, security credentials) need to be manually set before it can be used.

Finally, an email is automatically sent by AWS S3 to the technical contact at the company to confirm that the file has be successfully transferred.

Automation

In order to run the script automatically at a regular interval (once a week), a Scheduled Task is created. Once that has been done, no further action is necessary, and the data will be transferred automatically until the scheduled task is disabled.

Security

Ausvet will provide each company with a set of security credentials for use with the automated data transfer. This will provide you with permission to write the backup file to a company-specific folder in the S3 bucket but will not provide access to any other part of the system. It is important to keep these security credentials safe to avoid unauthorised access to your S3 folder.

All data transfers from your server to the S3 bucket are automatically encrypted, preventing unauthorised ‘man-in-the-middle’ attacks.

Keeping copies in the cloud

By default, Ausvet does not store previous copies of your data. Once a new version is received, the previous version is deleted.

However, for those companies that do not already have effective off-site data backup systems, you can ask Ausvet to keep some or all of your previous backup files. This means that a copy of your data will always be available in case of local system failure.

Instructions for downloading a previous backup from the S3 bucket are provided in the guide below.

Assumptions

This guide makes the following assumptions

  • Windows operating system. If your server is running a different operating system (e.g. Mac or Linux), the setup procedure is very similar, Ausvet can provide the necessary details.
  • Database backup. We have assumed that you have existing systems to create an automated backup of your database at least once per week, and that you know the name and file location of this backup. If your database backup system is not automated, or if you want to automate a separate database backup to generate the file for export to the Research Platform, let us know and we can help create an automated backup script.

Go to the top of the page.

Step-by-step instructions

Step 1:               Get your security credentials from Ausvet

When you are ready to set up data transfer, contact Ausvet and let us know the following information:

  • The email address for your technical contact. Automatic notifications of successful data uploads will be sent to this address
  • Whether you want us to keep copies of your data as off-site backups, or to replace each backup with the latest version so we keep only one copy.

Ausvet will then configure the S3 bucket and your AWS account and send you your security credentials. These will be sent as a password protected zip file. Ausvet will give you the password over the phone to protect your security.

The credentials in the zip file looks like this:

S3 bucket       : your_company_name

Access key ID    : AKIAILO2JNVD3EXAMPLE

Secret access key: MyoqZ/JrSyra6eQteeFXnrTKFo1ksUzKDEXAMPLE

Default region   : sa-east-1

Default format   : json

The name of the S3 bucket is the same as your company name, all in lowercase. The access key ID and the secret access key are used to set up the AWS CLI client in the next step.

Step 2:               Accept notifications subscription

When your AWS account is set up, the system also sets up automatic email notifications to confirm that the data has been uploaded. You will receive an email to your nominated technical account that looks like this:

From: AWS Notifications no-reply@sns.amazonaws.com

Subject: AWS Notification - Subscription Confirmation


You have chosen to subscribe to the topic:

arn:aws:sns:sa-east-1:287971726489:test1_notification


To confirm this subscription, click or visit the link below (If this was in error no action is necessary):

Confirm subscription

Please do not reply directly to this email. If you wish to remove yourself from receiving all future SNS subscription confirmation requests please send an email to sns-opt-out

Click on the Confirm Subscription link to open a page in your web browser confirming subscription to the notifications.

Step 3:               AWS CLI client installation

Install the AWS CLI client to manage the transfer of the database backup file from the local server to a secure S3 bucket. A full guide to installation of the AWS CLI client can be found here: https://docs.aws.amazon.com/cli/latest/userguide/awscli-install-windows.html#install-msi-on-windows

To install the AWS CLI using the MSI installer:

1. Download the appropriate MSI installer.

Note:  The MSI installer for the AWS CLI does not work with Windows Server 2008 (version 6.0.6002). Use the Python pip installation method described here instead: https://docs.aws.amazon.com/cli/latest/userguide/awscli-install-windows.html#awscli-install-windows-pip

2. Run the downloaded MSI installer.

3. Follow the instructions that appear.

If you have any problems, alternative download instructions, methods to check successful installation and instructions on setting up the environment path if required are available here: https://docs.aws.amazon.com/cli/latest/userguide/awscli-install-windows.html

Step 4:               AWS CLI Client configuration

This step saves your security credentials (from the zip file received from Ausvet) and default AWS region so you can use the AWS CLI client without needing to specify them every time:

  1. Open the Windows Command Processor from the Start menu (Windows System/Command Prompt).
  2. Type: aws configure
  3. Enter the following details.
AWS Access Key ID [None]: AKIAIOSFODNN7EXAMPLE

AWS Secret Access Key [None]: wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY

Default region name [None]: sa-east-1

Default output format [None]: json
  • The AWS Access Key ID, and the AWS Secret Access Key will be provided to you separately by Ausvet in a password protected zip file. These are the security credentials which allow you to access your private S3 bucket.
  • The default region name is sa-east-1. This specifies the default AWS data centre region.
  • The default output format is json for computer-readable output.

For more information about configuring the AWS CLI Client, see: https://docs.aws.amazon.com/cli/latest/userguide/cli-chap-getting-started.html

Step 5:               Manual data transfer

To test the transfer system, first do a manual data transfer. To do this you need to know:

  • The path and name of your database backup file.
    • For example: c:\MSSQL Server\backup\fishtalk20180212.bak
  • The name and path of your private S3 bucket folder. This will be given to you by Ausvet separately.
    • For example: s3://data.piisac.omnisyan.com/companyname/

To copy the file to the S3 bucket:

1  Open the Windows Command Processor from the Start menu, and type (all on one line):

aws s3 cp “c:\MSSQL Server\backup\fishtalk20180212.bak” s3://data.piisac.omnisyan.com/companyname/

Note that the inverted commas around the full path and file name are required if there are spaces in the path or file name. The file will be transferred. The time taken to transfer depends on the file size and the speed of your internet connection. Large files may take many minutes or longer over a slow connection.

2  After the file has been transferred, you should receive an email at the address of the registered technical contact confirming that the file has been added to the S3 bucket.

The email is in JSON format and will look a little like this:

{"Records":[{"eventVersion":"2.0","eventSource":"aws:s3","awsRegion":"sa-east-1","eventTime":"2018-02-10T03:54:23.964Z","eventName":"ObjectCreated:Put", "userIdentity":{"principalId": "AWS:AIDAJJANTCC2YTLSNQ77C"},"requestParameters":{"sourceIPAddress":"59.101.206.137"},"responseElements":{"x-amz-request-id":"C9822952C076D9C6","x-amz-id-2":"9bKUzChBs6QRB8b9mztbKMZwJeMQ/ EknT2ZBhB536F2ehceGulihA/Xk9ZTHXn7NfSTee0PAnkg="} ,"s3":{"s3SchemaVersion":"1.0","configurationId":"File uploaded","bucket": {"name": "data.piisac.omnisyan.com", "ownerIdentity": {"principalId":"A2C2HSQ707BA4J"},"arn": "arn:aws:s3:::data.piisac.omnisyan.com"},"object":{"key":"company/fishtalk20180210.bak","size":9003,"eTag":"1af52b928515f630b18cd6d44747a8b1","sequencer":"005A7E6CEF5EB9CBDB"}}}]}

If you wish to stop receiving notifications from this topic, please click or visit the link below to unsubscribe:

https://sns.sa-east-1.amazonaws.com/unsubscribe.html? SubscriptionArn=arn:aws:sns:sa-east-1:998690342977:PIISAC_FileUpload:6d556fb4-a682-4e06-a483-503115e4a15e&Endpoint= angus@ausvet.com.au

Please do not reply directly to this email. If you have any questions or comments regarding this email, please contact us at https://aws.amazon.com/support

For more information about the commands that can be used with the AWS CLI client, see https://docs.aws.amazon.com/cli/latest/userguide/cli-chap-welcome.html

Step 6:               Creating the batch file

To automate the process, you need to create a batch file with the command to transfer the file. You can write the batch file with your favourite text editor, for example Notepad. Don’t use Word or other software that saves files in special formats – you need to save in plain text format with a ‘.bat’ file extension. (Note that Notepad automatically adds ‘.txt’ and may hide this depending on your settings for windows explorer. Change your settings to remove the options ‘Hide extension for known file types’).

The batch script identifies the most recent backup file (origin file) and saves it to S3 with the same file name.

This is an example of a batch file.  Modify the items in red and save it as copydb2s3.bat in an appropriate folder.

Automated database backup transfer batch file: copydb2s3.bat
echo off
set filepath=C:\Backup
set company=empresa
for /f "tokens=*" %%a in ('dir %filepath%\*.bak /b /od') do set newest=%%a
set filename=%filepath%\%newest%
set destname=%newest%
set bucketname=s3://data.piisac.omnisyan.com/%company%/
aws s3 cp %filename% %bucketname%%destname%

To test the file, open the Windows Command Processor from the Start menu, change to the folder where you saved the batch file, and run it:

copydb2s3.bat

If you see a message something like this:

upload: .\fishtalk.bak to s3://data.piisac.omnisyan.com/company/database20180210.bak

it means the batch file is working.

If you see a message like this:

The user-provided path c:\backup\fishtalk20180210.bak does not exist.

there is a problem with your file name. Contact Ausvet if you need help configuring your batch file.

Step 7:               Creating a scheduled event

The last step is to configure Windows to run the batch file once a week. It should be run shortly after the backup file has been created, and at a time when the large file transfer will not interfere with other users (for example, at night or on the weekend).

1 Open the Task Scheduler from the start menu or search bar

2. On the Actions pane on the right, click on Create Basic Task

3. In the Wizard, enter a name and description, then click Next

4  Select Weekly for the trigger. Click Next

5  Set the start date, start time, and the days to run, then click next

6  For the Action, select “Start a program”, and click Next

7  Use the Browse button to find the location where you saved the script (copydb2s3.bat) and click Next.

8  Check the details, and check the box “Open Properties dialog for this task when I click Finish”, then click Finish

  1. Make the following changes:
    1. Check the option “Run whether the user is logged on or not”
    2. Make sure the user is the same user as was used to set up the AWS CLI configuration credentials.
    3. Check the option “Run with the highest privileges”. Then click OK.

9  You need to confirm you user password for Windows:

  • When the screen below pops up, select your Window user name from the drop-down list (you should be the default), and enter your Windows login password
  • Click OK.

10  Test the task by running it manually. On the main task scheduler window,

  • click on the Task Scheduler Library on the left pane,
  • click on the defined task (“Copy database…”) in the middle pane
  • Click on Run on the right pane

The task status should change to ‘Running’, and after the upload is complete, you should receive an Upload notification message from AWS S3.
Go to the top of the page.

Downloading previous backup copies

If you have asked Ausvet to retain previous backup copies, you can retrieve these files from S3 with the same cp command, but copy from the S3 bucket to your local server:

aws s3 cp s3://data.piisac.omnisyan.com/companyname/fishtalk20180212.bak  “c:\backup\”

Help

If you have any problems setting up the automated transfer, please contact Ausvet.
Go to the top of the page.