Managing an AWS Database

A guide to managing an AWS account, including additions and changes relating to Well Seeker Pro.

1.0 - Introduction

Once a user has set up their AWS account, an RDS and an EC2, they then require to manage that account. As time goes on the software is updated, company personnel change, and the way the user uses the software may change. This document is intended to give the admin in charge of managing the AWS account a guide as to how to manage certain additions and changes relating to Well Seeker Pro.

The system diagram below details how the AWS SQL Server Database and EC2 fit in to the structure of data flow within Well Seeker Pro.

2.0 - Getting Started

Before you start, to save time, there are some programs which should be downloaded and installed, as access to these will be required at various points throughout the document.

These programs and the relevant links are detailed below.

2.1 - PuTTY

• https://www.chiark.greenend.org.uk/~sgtatham/putty/

• Download the 32-bit MSI

2.2 - WINSCP

• https://winscp.net/eng/download.php

• During installation, select the Commander interface style

2.3 - SQL server management studio (SSMS)

• https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-2017

• Installation of SQL Management Studio can take a while, which is normal and expected.

2.4 - Google Authenticator App

In addition to installing the above programs on your computer, install the Google Authenticator App on a suitable mobile phone.

3.0 – Adding a New Database

There are several reasons a company may require an additional database, the most likely is so they can have a “Dirty” database which the field engineers populate remotely via the Real Time Data Exchange, and a “Clean” database where only QAQC’d data is stored. Another popular reason would be to allow a third party to access server data without them having access to the company’s master database.

An additional database is created using SQL Server Management Studio:

1. Open SQL Server Management studio. Login as using the below information

a. Server Type: Database Engine

b. Server Name: This is the Endpoint followed by a comma, followed by the Port number

c. The Login and Password for the database are the same as when the database was created

2. Right click on the databases folder and click create new database

3. Give the database a name and click add then click ok

a. It is important to take a note of the database name, exactly as it has been entered, as this is case sensitive and will be required to access the Db via Well Seeker

4. You should now see the database in the tree. If you do not see the database you have just created, hit refresh and it should appear

5. When you expand the database and open the tables tree item you will see there is nothing there

6. You need to connect to the database with Well Seeker to create the tables and set the database up. Well Seeker does this automatically on the first connection to the database

7. Until you create a new user, you will need to login with the same details used for your primary database

a. NOTE: You need to make sure the Database Name is the name of the new database you have just created

8. Click connect:

a. It is likely it will say the Db schema is out of date. Select yes to update

b. It will say there are no admins and ask if you want to make this user an admin. Select yes

9. On first connecting, Well Seeker will create all the relevant tables in the SQL database

10. You can now close Well Seeker

11. Open the server database in SQL Server management studio.

12. In the Object Explorer Tree, navigate to the tables folder under your database.

13. Scroll down till you get to the dbo.DB_SETTINGS table.

14. Right click on the table and select Edit Top 200 Rows.

15. Insert a single record in to the table: ./icdsFileDir/

a. This sets the field IMAGE_PATH for the remote Db, which is where any logos and attached files will be stored.

4.0 – Setting up an additional ICDS Server

With a new (additional) SQL server database created it may be necessary for the user to set up an ICDS server for this database to allow data exchange between it and other databases. The following section details the steps required to achieve this.

4.1 – WinSPC

1. Open WinSCP and login. See Appendix A – Login To WinSCP for details

2. You will now have the below, where you have created a drag and drop file explorer interface to the AWS EC2. If you do not see the Ubuntu folder on the right, its likely because you are already in it. Just select the only folder showing and it should then display the ubuntu folder

Note: If you have been using the original database prior to setting this second database up, and have been performing data fetch operations, you may find there are a lot of zipped folders named “Pull Files” now present in the window, where there were previously none. This is expected

3. Create a new directory called icdsServer2

4.2 – ICDS Server and .json Files

1. Take a copy of the 2 files (icdsServer and configICDS.JSON) which are currently in the drop file explorer interface

2. Take the configICDS.JSON and do the following:

a. Open this file in a text editor e.g. Notepad

b. Change the sqldbname to the name of the new database created in section 3.0

c. Change the Port to 42001. This is ESSENTIAL as this is the port which will be entered into the Well Seeker RT Data exchange for communication with this ICDS. 42000 is already being used so it must therefore be different

d. Keep the rest of the information the same INCLUDING the name of the file. For more details see Appendix C – configICDS.json

3. Once completed, drag both files into the new icdsServer2 folder

4. Right click on the icdsServer file and select Properties. Then ensure all the below permissions are checked

a. Note: If these are not all checked, you may receive an error message when starting the pm2 in the next section

5. In the same folder as the above two files create a new folder called icdsFileDir

6. Close WinSCP

4.3 - PuTTY

1. Open PuTTY. See Appendix B – Login to PuTTY, for instructions on how to login to the PuTTY interface

2. Check pm2 is running by typing pm2 ls. This should display as below, showing the icdsServer as being online

3. Type pwd, which will show the current location you are in. This should be /home/ubuntu

4. You now need to change the location by typing cd /home/ubuntu/icdsServer2

5. Now start the new instance: pm2 start icdsServer –-name icdsServer2

6. Within pm2 the new ICDS is now referred to as icdsServer2, which differentiates it from the original ICDS that was already running on the EC2

7. You should now see the below where BOTH icdsServer files are Online

8. Type pm2 ls to check the icdsServer2 still has a status of online

9. Save the current pm2 settings so it starts correctly after a server restart: pm2 save

10. Close Putty

4.4 – EC2 Security Rules

With the addition of the new ICDS Server, which is using a new port (42001), this port needs to be added to the AWS incoming and outgoing security rules in the EC2. Failure to do this will result in a connection error, and the user will not be able to use the data fetch and transfer features. Add the below rule to both the incoming and outgoing EC2 Security rules.

1. Additional Rule:

a. Type: Custom TCP

b. Port: 42001

c. All public IP addresses to be allowed

i. The reason to allow all public IP addresses is because it is difficult to know the relevant public IP addresses for each rig computer which is out with the company’s network. Setting a range here could mean some rig computers are unable to communicate with the ICDS

ii. For larger companies who have the relevant IT structure, they may want to set an IP range for computers in their network. This is more secure

iii. If a company does select an IP range, and we end up having to provide support, they may need to add our IP addresses in as a rule otherwise we will not be able to access

2. The Inbound and Outbound security rules in the EC2 should now look like the below

5.0 – Creating an Additional DB Username / Login

The original master user which was created with the RDS has access to all parts of the RDS and has full admin control. Supplying login details like this to everyone in the company is not ideal. To get round this, you can create a new login, which will have more restricted access.

An additional Login is created using SQL Server Management Studio.

1. Open SQL Server Management studio and go to the tree on the left and under Security right click on logins and select New Login

a. In the Login Properties dialog, select SQL Server Authentication and enter a login name and password. Choose something sensible here, like the name of the company and Guest after e.g. InnovaGuest

b. Select Enforce Password Policy, but leave the other password options unchecked

c. Select the default database from the dropdown

i. Select the new database here

d. Keep a note of these new login details:

i. Login: InnovaGuest

ii. P/W: InnovaGuest

2. Select Server Roles on the left-hand side of the dialog, and check Public

3. Select User Mapping on the left-hand side of the dialog:

a. In the top section, you will see the available databases. Check ONLY the database you want the user to have access to

i. You will see the new username populate in the cell to the right of the database name

b. In the bottom section, check public and db_owner

4. Select Securables on the left-hand side of the dialog. This section will be empty but will populate after the login has been created

5. Select OK at the bottom of the dialog and this will create the new login

6. Right click on the new login and select properties. Then go to Securables

a. This time the section will be populated

b. Connect SQL is already checked with the Grantor being rdsa

c. In the row above, there is another Connect SQL option. Check the Grant box for this and then OK.

6.0 – Creating Additional Well Seeker (WS) Users

WS users are designated to give each user individual login credentials and controlled user permissions when they access the server database via Well Seeker Pro. The below steps will outline how to add WS users and explain the permissions options.

1. Open Well Seeker Pro

2. Select File > SQL Server Databases > Connect to remote database

3. Fill in the login credentials and select Connect

4. Once you have connected to the remote database select Tools > User Permissions. This will open the User Permissions dialog

5. To add a new WS user select Add User. This will add a new blank row to the table. Input the new users username

6. Leave the Password cell blank. This will be populated after the new user logs on for the first time and has chosen their password. If at any time a user has forgotten their password, the admin can delete the information in the password cell and the next time the user logs in they will be asked to create a password like a first time login

7. Check the relevant permissions boxes for the WS user. Below is a description of each permission:

a. Create New Items – The user will be able to add new data to the database

b. Delete Items – The user will be able to delete existing data from the database

c. Edit Items – The user will able to edit existing data from the database

d. Administrator – An administrator can add, remove and edit user permission details

e. Approver – An approver can approve bid sheets

8. Once the user permissions have been set select Apply. The dialog will automatically close

9. If you wish to remove a user, select the relevant row and select Delete User

7.0 – Updating the ICDS executable version

As with Well Seeker Pro, the ICDS is updated periodically with improvements and additional functionality. As such, the ICDS may require to updated in order to gain access to this functionality. The below steps outline how to update the ICDS executable to the latest version.

1. Open WinSCP and login to the EC2. See Appendix A – Login To WinSCP for details

2. You will now have the below, where you have created a drag and drop file explorer interface to the AWS EC2. In the right hand panel you should now see the default folder on the EC2 / virtual machine that contains the current icdsServer executable file. On the left panel is your local PC

3. Download the ICDS files via the link provided by Innova

4. Remove the .linux extension from the icdsServer.linux file

5. Delete the existing icdsServer file from the EC2 (right hand panel)

6. Drag the new Linux executable icdsServer file from the local PC (left hand panel) to the EC2 / virtual machine folder(right hand panel)

7. You will get the below left popup. Select OK and you get the below right

8. Right click on the icdsServer file and select Properties. Then ensure all the below permissions are checked

a. Note: If these are not all checked, you may receive an error message when you restart the pm2

9. If required update the .json file. See Appendix C – configICDS.json for details

10. Close WinSCP. You will get the below. Selected No, so that the workspace is saved

11. Open and login to PuTTY. See Appendix B – Login to PuTTY for details

12. Restart the ICDS server. See Appendix D – Restarting the ICDS Server for details

8.0 – Restoring an SQL database from a backup

Within AWS, there is the potential to restore an RDS to the condition it was in at a previous timestamp. The AWS account administrator is in control of the settings that determine if and when these restore points are created in the AWS account.

The purpose of this section is to guide the user through a couple of scenarios where restoring to a previous database condition may be useful and some suggested steps to carry out the restore:

 Database restoration required to completely replace the existing version of the SQL database

 Database restore required for a particular data table, or small number of data tables within the SQL database

The below steps are required to be carried out, regardless of the level of restoration that is required to be undertaken.

1. Log on to the AWS account where the SQL database is hosted

2. Navigate to the RDS > Databases page

3. Select the database that requires restoring

4. Select the Maintenance & backups tab and scroll to the bottom section called Snapshots

5. Select the snapshot that you want to restore to by selecting the checkbox and the selecting Restore

6. When restoring a snapshot, you do not overwrite your existing database. The restored information will be created as an additional database. In the DB Instance Class cell select the same instance class as your existing database

7. In the DB Instance Identifier cell, input the name you want to call the restore point database. This is the name it will have when viewed in the AWS console. If left blank then a random name will be generated

8. In the Public accessibility section select Yes. Very important to select this, as the restored database will effectively be useless if you don’t. Double check this before restoring the database, as selecting some options reverts this back to No

9. In the Availability zone section select the zone your current database is in

10. In the VPC security groups section select the security group that your current database has selected

11. Leave the rest of the options as default, double check point 8 and then select Restore DB Instance

12. AWS will now create the restore point database and will have a status of Creating until the database is ready. This process will take varying amounts of time depending upon the size of the database that is being restored

13. Once the status of the database has changed from Creating to Available the user can move on to the next step.

14. All of the restored database login credentials should be the same as your original database instance, with the exception of the endpoint/url. From the Databases table select the restored database

15. Take a note of the endpoint details in the Endpoint & port section of the Connectivity & security tab

16. If your original database is irreparable and it is required to use the restored database going forward then see section 8.1. If the majority of the original database is correct and only a small portion of the original database requires restoring, then see section 8.2

8.1 – Complete Database Restoration Process

If the original database is such that it is best to abandon it and use the restore point database going forward, then follow the below steps.

1. The new restored database, the users must input the new endpoint in the Connect to remote database dialog within Well Seeker Pro, overwriting the previously used endpoint. All other details should remain the same. This process will need to be completed on each computer logging in to the restored database

2. In order to have computers pushing and pulling data from the restored database via the Real Time Data Exchange and Remote Data Fetch dialogs, the admin must update the endpoint in the .json file on the EC2. In order to do this:

a. Log on to the EC2 using WinSCP. See Appendix A – Login To WinSCP for details

b. Locate the configICDS.json file and open in a text editor

c. Edit the endpoint in the configICDS.json file. See Appendix C – configICDS.json for details. Save the change and close the file

d. Close WinSCP

e. In order for the changes in the .json file to take effect, the ICDS server needs to be restarted. Restart the ICDS server. See Appendix D – Restarting the ICDS Server for details

3. Logging in to the restored database via SQL server management studio will require the new endpoint to be included in place of the old database endpoint

8.2 – Partial Database Restoration Process

If only a small portion of the database needs to be replaced i.e. a few data tables, then it may be easier to replace the problematic data in the original database with good data from the restored database. This has the benefit of not having to get all of the users to change their endpoint login details and the admin would not be required to update the EC2 .json file either.

1. Take note of the names of the database tables that contain data that needs to be replaced with the restored data

2. Open SQL Server Management Studio. In the login window input the new endpoint followed by , 41433 with 41433 being the RDS port number. All other login details remain the same as the original database. Select Connect

3. This will open the restored RDS. Navigate within SQL Server Management Studio and open a required table in the required database, by right clicking on the table in the tree and selecting Select Top 1000 Rows

4. If there are more than 1000 rows of data in the table that the user wants to export, the user can increase the command line to display a greater number of rows in the table. The user then selects Execute to run the command, which will update the table display with the inputted number of rows, or all of the rows in the data table, if that number is smaller than the input number. In the below example the command will allow the user to see the top 9000 rows in the selected table

5. Select the top left cell in the data table, right click and select Copy with Headers

6. Open a blank excel document and paste in the copied data. In excel select File > Save As and save the document as a .csv file

7. Repeat this process for all required database tables

8. When importing this data to the original database the data is added to the existing table, it does not overwrite it. With this in mind, edit the data in the .csv file to include only the data that you plan on adding to the original database

9. Log out of the restored database in SQL Server Management Studio

10. Log in to the original database using SQL Server Management Studio

11. Right click on the required database and select Tasks > Import Flat File…

12. Select Next

13. Browse to the saved file and select. NOTE, this process is going to create a NEW table in your database. It does not pull the data into an existing table. You will be asked to enter a table name. Choose something suitable so it is easy to find. Also make sure it is not the same as an existing table. Then hit Next

14. Preview the data. Have a check and select Next

15. The user will proceed to the modify columns section. Be careful here as the data types are decided automatically by the program. Well Seeker has what’s called nvarchars opposed to Floats. If anywhere you see Float, change this to nvarchar. You will see that there are several options here which differ with a number at the end e.g. nvarchar(1), nvarchar(100) etc. Select the same nvarchar number as the one above and below in the table

16. Select Next and then Finish. Once the results section indicates Operation Complete select Close

17. Navigate through the tree within the original database. The user will see there is now a new table with all the imported data. It will be named whatever was entered in step 12

18. From the toolbar select New Query

19. The user can now edit and use the following query to insert all the data from the new table into the required table in the database:

insert into [PhilEuroDb1].dbo.DAILY_ACTIVITY select * from [PhilEuroDb1].dbo.restoredData

In the above, the command is inserting data into the DAILY_ACTIVITY table in the PhilEuroDb1 database from the restoredData table, which is also in the PhilEuroDb1 database. If the table containing the restored data is in a different database, then just enter the appropriate database name

20. From the toolbar select Execute

21. The Messages section will update to show the query was executed successfully

This data will not overwrite the information in the table it is writing to, but rather just add to the data that is currently there. As a result, if the user is restoring some specific data, opposed to the whole table, which has maybe been wiped, then you take the data from the restore to excel, delete all the info the user doesn’t need, leaving only the specific info to be restored, and then follow the instructions as normal. This ensures that the data that’s added to the table is only what is needed

22. Repeat this process for each table that requires data to be imported to

23. Once completed, delete your backup tables. The process is now complete

A good guide to SQL commands can be found at the below web link

SQL SELECT Statement (w3schools.com)

9.0 – Storing WITS data on a second database

When storing WITS data to a databases tables, the database size becomes large very quickly, which can affect the performance of the database. It is advised that if a user wishes to save WITS data to a server database that they do so on a second database to avoid this issue. Below are outlined steps to achieve this:

1. Ensure a second server database has been set up. See section 3.0 – Adding a New Database for details

2. Open and login to WinSCP. See Appendix A – Login To WinSCP for details

3. Open and edit the configICDS.json file, specifically the sqldbnamewits and transferWits inputs. See Appendix C – configICDS.json for more details

4. Save and close the .json file

5. Close WinSCP

6. Restart the ICDS server to utilise the new details entering in the .json file. See Appendix D – Restarting the ICDS Server

7. Any WITS data pushed back to the server will now also be stored in the CURWITS_DATA table in the second database every 5 seconds

10.0 – Only Push Data to Active Wells

If you have historically had issues where field personnel push data from an old well in their local database, there is the potential that they could overwrite QCed or finalised data on the server. To avoid this there is the option to only allow data to be pushed to wells that have a status of Active, Upcoming, or Standby. This obviously requires the well status to be updated accordingly by the appropriate users for it to be successful. This option is facilitated by an input in the configICDS.json file located on the EC2. See below steps to enable this option:

1. Open and login to WinSCP. See Appendix A – Login To WinSCP for details

2. Open and edit the configICDS.json file, specifically the allowActivePush input. See Appendix C – configICDS.json for more details

3. Save and close the .json file

4. Close WinSCP

5. Restart the ICDS server to utilise the new details entering in the .json file. See Appendix D – Restarting the ICDS Server

11.0 – Lock the Database Version

The SQL server database at any one time has a fixed database schema, or structure. This structure will be the same for all users using the same version of Well Seeker Pro. When a user logs on to the server database, Well Seeker Pro runs a check to see if the users current version of Well Seeker Pro matches the database structure of the server database. If it does not then it will ask the user if they wish to update the database schema. If the user selects yes, then the server database structure is updated to match the version of Well Seeker Pro.

Potential issues can arise (error messages) when a company does not roll out a new version update of Well Seeker Pro correctly and multiple users are running different versions of the software at the same time and accessing the server database.

In order to prevent this issue during a Well Seeker Pro roll out the database admin can lock the version / structure of the server database, so that the users cannot change the structure upon logging in to the server. Once the roll out was completed the database admin can then unlock the version / structure and it can be update to the latest version. See below for the steps involved.

1. Open SQL Server Management studio and log on to the RDS in question

2. Right click on the database in question and select New Query

3. Input INSERT INTO DATABASE_INFO (UPDATE_NUM, UPDATE_VERSION) VALUES (‘999’, ‘-999.25’)

4. Select Execute

5. The database version is now locked until the below steps are followed

6. Right click on the database in question and select New Query

7. Input DELETE FROM DATABASE_INFO WHERE UPDATE_NUM = ‘999’

8. Select Execute

9. The database version can now be updated as usual

12.0 – Editing AWS Security Rules

Security rules may be required to be updated after the initial setup, for example if IP public addresses of users changes. Separate security rules apply for the RDS and EC2 and will need to be edited separately.

12.1 – RDS

Below are some recommended settings. The RDS database has communications with:

• The ICDS (located on the EC2 Instance)

• Well Seeker / SQL Server Management Studio

When creating a security rule, the 2 main pieces of information required are the Port and the IP address. Set the RDS Incoming and Outgoing Security rules as detailed below:

1. Rule one:

a. Type: Custom TCP

b. Port: 41433

c. All public IP addresses that require access to the RDS via either Well Seeker or SQL Server Management Studio

i. For larger companies who have the relevant IT structure, they may want to set an IP range for computers in their network. This is more secure

ii. If there are just a few IP addresses to be added, the smaller companies can find each Public IP by accessing the web browser on each computer and typing “ip4.me” into the address bar at the top of the page

iii. If a company does select an IP range, and we end up having to provide support, they may need to add our IP addresses in as a rule otherwise we will not be able to access

iv. End the IP addresses with /32

2. Rule Two:

a. Type: Custom TCP

b. Port: 41433

c. Security group assigned to the EC2 instance

12.2 – EC2

The EC2 instance has inbound communications from:

• WS - data fetch and exchange

• RDS database

• WinSCP and PuTTY

When creating a security rule, the 2 main pieces of information required are the Port and the IP address. Set the EC2 Incoming and Outgoing Security rules as displayed below:

1. Rule One (This should already be present when you open the rules dialog):

a. Type: SSH

b. Port: 22

c. Only public IP addresses of admins who will be using WinSCP or Putty to access the EC2

2. Rule Two:

a. Type: Custom TCP

b. Port: 42000 or whichever port is used for push and pull traffic

c. All public IP addresses to be allowed

i. The reason to allow all public IP addresses is because it is difficult to know the relevant public IP addresses for each rig computer which is out with the company’s network. Setting a range here could mean some rig computers are unable to communicate with the ICDS

ii. For larger companies who have the relevant IT structure, they may want to set an IP range for computers in their network. This is more secure

iii. If a company does select an IP range, and we end up having to provide support, they may need to add our IP addresses in as a rule otherwise we will not be able to access

3. Rule Three:

a. Type: Custom TCP

b. Port: 41433

c. Select the security group assigned to the RDS

13.0 – Uploading a Local Database to the Server

This function is intended to allow a user to upload an existing local database (.mdb file), or a selection of its data tables to the server. This is useful when a user has migrated all of their data from historical wells in to a local database format.

This should only be used at the very start of a server database’s existence, as the upload will overwrite all data in any selected tables of the server database.

1. Log on to the server database within Well Seeker

2. Select File > SQL Server Databases > Access To SQL Server Transfer

3. Select the local database file to be uploaded to the server and select Open

4. A message window will allow the user to upload all tables, or select which to upload. If the user selects Yes then proceed to step 6, if No then continue from step 5

5. If the user selects No then the Select Data dialog opens. Select the Import box of each table that the user wishes to import. When the user has completed their selection select OK

6. The next message window reminds the user that any existing data on the server database tables will be overwritten by the data that is being uploaded. Select Yes

7. Depending on the size of the transfer and the latency to the server the upload can take a wide range of time. E.g. for larger database migrations of ~1GB the upload can take up to 10 hours. During this time do not close Well Seeker or turn off your PC. An internet connection is required throughout. Once completed the below message will appear

14.0 – Server Database Latency Optimization

A server database brings many benefits, but one down side is that it will always be slower in operation than a local database. The reasons for this are purely time and distance related. For Well Seeker to carry out a programmed action on a local database, the process is carried out within the user’s computer, a physically short distance, with minimal system interrupts. When the same action is carried out on a server database the process has to be transmitted from the user’s computer, to the server and back. Depending on the server set up this results in varied increased latency times and as such Well Seeker will appear to run slower, particularly for very data intensivee tasks. The steps below will help identify the network latency and if possible suggest ways of improving it.

1. Login to the server database using Well Seeker Pro

2. Select Tools > Real Time Data Exchange. The Data Exchange dialog will open

3. Select the Network Latency button. A window will open displaying the average round trip time for 10 queries to the server database

4. For an AWS database, anywhere between 20-40ms is a very good latency. No further actions are required

5. If the latency is greater than this, especially if >100ms then Well Seeker will appear to operate slowly for certain actions and attempts should be made to improve this

6. Log on to the AWS account, select the RDS and check which region it is in (found in the top right of the AWS account screen). This is where the RDS is hosted by AWS and latency is directly affected by geographically how far this is away from the user’s computer. If AWS offer a location to host the RDS that is closer to the majority of the users locations, then a new RDS should be created in the closer location to reduce latency

7. On the same screen also check the RDS Class. If the class is db.t2.micro then upgrading to a more powerful version may improve latency

8. A computer’s or company’s network firewall settings can also have an impact on latency ( it runs checks on communication to and from the computer, which add time to the operation). As a result, changes in firewall settings may reduce latency

15.0 – Well Permissions

Well permissions have been put in place to give the admin control over what computers can pull what wells via the remote data fetch functionality from the SQL server database, if required. This might be the case when a company employs consultant rig personnel, who they do not want to have access to their historical well data. In order to access and setup well permissions follow the below steps:

1. Log on to the SQL server database via Well Seeker Pro

2. Once connected, select Tools > Well Permissions. This will open the Well Permissions dialog. Note that only Well Seeker users with Admin permissions can access the Well Permissions dialog

3. Well Permissions can be set to two states:

a. Enable Individual Well Permissions disabled. To select this option uncheck the Enable Individual Well Permissions check box. This allows any company computer with Well Seeker installed on and a user who has the EC2 IP address and port number details to pull any and all well data from the SQL server database via the Remote Data Fetch dialog

b. Enable Individual Well Permissions enabled. To select this option check the Enable Individual Well Permissions check box. When this is selected no computers are able to pull down well data via the Remote Data Fetch dialog, even if the user has a company computer with Well Seeker installed and the user has the EC2 IP address and port number details. Only computers entered in the table will be able to pull down a specified well or job number, when the active check box is selected.

The Computer Name for a computer can be found by right clicking on This PC and selecting Properties. Depending on the windows version the user has, the computer name will come under one of a number of different titles. These details will need to be updated on a well by well basis in order to allow the rig personnel access to the required wells

16.0 – Storing external files on an Amazon S3

During the RDS and EC2 setup process, the user sets any external files to be stored in a folder on the EC2. If you are using AWS for your storage then Amazon offer a storage device called an S3. The S3 is much cheaper per GB of storage than an EC2. It also makes all files available to any service or person that can access the S3. The S3 does not require the use of Putty and can also be accessed through the Innova Web Portal. This gives many advantages to using an S3 for storing external files.

Follow the below steps:

1. Create an S3 bucket in the users AWS account. This guide doesn’t cover the setup of an AWS S3. For more information see the below web address:

https://docs.aws.amazon.com/AmazonS3/latest/userguide/Welcome.html

2. Create an IAM User with access to the S3 service. Keep a note of the AWS access key and AWS secret key

3. Update the configICDS.json file below fields. See Appendix C – configICDS.json for details

a. externalPath input useS3. This specifies using the S3 as the storage service

b. awsAccessKeyID Input the AWS Access Key ID of the IAM user

c. awsSecretKey Input the AWS secret key for the IAM user

d. awsRegion Input the region where the AWS service is hosted eg “us-east-2” or “us-west-2”

e. s3Bucket Input the name of the S3 bucket which was created to store the Well Seeker external files

4. If the ICDS server executable is less than version 1.9.4 then this will need to be updated. See 7.0 – Updating the ICDS executable version for details

5. If not already done so in the above step, restart the ICDS server. See Appendix D – Restarting the ICDS Server for details

6. With the above steps followed, the ICDS will use the S3 to store and retrieve files

17.0 – Backup and Restore an EC2’s Data

Use the below method if the physical data on the EC2 has been corrupted, but the network and security settings of the EC2 are still functioning correctly.

The files stored on the EC2 are not automatically backed up like the RDS, so the user must first create manual snapshots of the EC2’s storage device (volume). When required the volume snapshot can be restored, so the data on the EC2 is the same as when the snapshot was created. Be aware that having a snapshot stored will be an AWS chargeable extra.

This process does not deal with restoring the EC2 network and security group settings, only the data stored on the EC2.

17.1 – AWS Console – Create Volume Snapshot

1. User logs on to their AWS console

2. In Services select EC2

3. Select Instances (running)

4. Select the relevant EC2 instance and select the Storage tab. Take a note of the Volume ID. Think of the volume as the virtual hard drive of your virtual machine and is therefore where all of your files and folders are stored on your EC2. This is what you want to backup

5. Under Elastic Block Store select Volumes

6. Select the volume that the EC2 is using. Select Actions and Create Snapshot

7. Enter a Description and select Create Snapshot

8. Once created, the snapshot of the volume will be located under Snapshots

17.2 – AWS Console – Restore the Volume Snapshot

9. Select Instances (running)

10. Select the relevant EC2 instance and select the Storage tab

11. Select Replace root volume

12. Select the desired snapshot from the dropdown menu and select Create replacement task

13. Once the replacement has been completed the files and folders stored on the EC2 will have been restored to the state they were in when the snapshot was created

14. After changing the volumes the ICDS Server will need to be started again. Log on to PuTTY and start the ICDS Server. See Appendix D – Restarting the ICDS Server

18.0 – Troubleshooting Connectivity Issues

There may be times when a user is experiencing issues with logging in to the SQL server database via SSMS or Well Seeker, or where the external files / data push and pull functions are not working. The below sections will guide the user through some problem solving steps that will help isolate and solve the issue in question.

18.1 – SQL Server Management Studio (SSMS) RDS Login

If the user receives an error message and cannot log in to the RDS via SSMS then follow the below steps:

1. Check the user’s computer has internet connectivity

2. Check the Server type is Database Engine

3. Check the Server name is the RDS endpoint, immediately followed by a comma, then a space and then the RDS port number, which is usually 41433. For example:

a. RDS Endpoint = exampleendpoint

b. RDS Port = 41433

c. Server name entered = exampleendpoint, 41433

4. Check the authentication type is SQL Server Authentication

5. Check the Login entered is the RDS Username the user created when setting up the RDS

6. Check the Password is the RDS password the user created when setting up the RDS

7. If the above checks still haven’t fixed the problem the remaining potential cause is the RDS security settings. Log on to the AWS account, select the RDS in question and review the security settings. See section 12.1 RDS for more details.

Note that if individual IP addresses have been specified in the security rules, it is not uncommon for a network router provided by a third party to change a computers public IP address. To see a computers public IP go to ip4.me in your web browser

18.2 – Well Seeker Pro Database Login

If the user receives an error message and cannot log in to the SQL server database via Well Seeker Pro then follow the below steps:

1. Check the user’s computer has internet connectivity

2. Check the Database IP / URL is the RDS endpoint

3. Check the Database Name is the name of the SQL server database as it appears when viewed in SSMS. This should not be confused with the name of the RDS when viewed in the AWS account

4. Check the Port is the port number of the RDS, usually 41433

5. Check the DB Username is the RDS Username the user created when setting up the RDS. If the user has since created an additional user in SSMS, this user name can also be used, as long as they have the clearance to access the database entered in Database Name cell

6. Check the DB Password is the RDS password the user created when setting up the RDS. If the user has since created an additional user in SSMS, this user password can also be used, as long as they have the clearance to access the database entered in Database Name cell

7. Check the WS Username is correct. Each WS username has been created in the User Permissions dialog by a user with administrator permissions.

If the administrator cannot log on to the SQL server database via Well Seeker then they can log on to the RDS via SSMS and look in the dbo.USER_PERMISSIONS table to see if the WS username exists

8. Check the WS Password is correct. If the user has forgotten their password then the admin can delete the users password from the User Permissions dialog. The next time the user logs in they will be asked to enter a new password

9. If the above checks still haven’t fixed the problem the remaining potential cause is the RDS security settings. Log on to the AWS account, select the RDS in question and review the security settings. See section 12.1 RDS for more details.

Note that if individual IP addresses have been specified in the security rules, it is not uncommon for a network router provided by a third party to change a computers public IP address. To see a computers public IP go to ip4.me in your web browser

18.3 – Real Time Data Exchange / Remote Data Fetch

The Real Time Data Exchange is design to give a user the ability to push data for a specific well from their Well Seeker database, back to the SQL server database. The Remote Data Fetch is designed to give a user the ability to pull data for specific wells from the SQL server database to their Well Seeker database. In order to achieve this there are many things that have to be set up correctly

For both the Real Time Data Exchange and the Remote Data Fetch:

• Correct IP and port number input in Data Exchange / Data Fetch dialog. These are the IP of the EC2 and the port number specified in the .json file on the EC2

• Correct credentials entered in the .json file on the EC2. See Appendix C – configICDS.json for details

• The ICDS server has to be running without error on the EC2. See Appendix D – Restarting the ICDS Server

• The EC2 security settings have to be correct within the AWS account. See 12.2 – EC2 for details

• The RDS security settings have to be correct within the AWS account. See 12.1 – RDS for details

For the Real Time Data Exchange only:

• Correct well selected in the Data Exchange dialog. This well must already exist on the server data base

• If the .json file on the EC2 has allowActivePush as false then a computer can push data back to a server database regardless of the well status. If true is entered then a computer can push data back to a server database only when the well status is Upcoming, Active or Standby. The well status is selected in the Daily Reports dialog. See 10.0 – Only Push Data to Active Wells for details

For the Remote Data Fetch only:

• If the Well Permissions dialog on the SQL server database has Enable Individual Well Permissions selected, then only wells included in the Well Permissions table will be able to fetch specific wells. In the case that the user does not have permission to download well data, the user will be able to connect to the SQL server database and see the database tree, but there will be no structures below facility level to select, as below.

See 15.0 – Well Permissions for further details

18.4 – External Files Not Saving

External files for the SQL server database are stored on the EC2, or an S3 and rely upon a similar data transmission method as the real time data exchange and remote data fetch functions. If you are experiencing issues saving or accessing external files and logos, either from the SQL server database, or when the data has been pulled down to a local database, follow the below steps:

1. Ensure the real time data exchange and remote data fetch functions are working correctly. See 16.3 – Real Time Data Exchange / Remote Data Fetch for details. If they are then it rules out a number of potential causes for the issue. If both these functions are working, but you are still experiencing issues with the external files functions then proceed to the next step

2. Ensure that during the database creation the step was followed that created the external files image path in the dbo.DB_SETTINGS table

3. Ensure that the option Push External Files is selected on all computers trying to use the functionality

4. If using an S3 to store external files, ensure that the .json file on the EC2 has been filled in correctly. See Appendix C – configICDS.json for details

5. If any changes were made in step 4, ensure that the ICDS server is restarted. See Appendix D – Restarting the ICDS Server for details

Appendix A – Login To WinSCP

1. Open WinSCP. If you have a saved session select it, login and continue. If not follow the below steps

2. Select protocol as SFTP.

3. Enter the EC2 ipv4 address in the hostname.

4. Username is ubuntu

5. Password is blank

6. Port is 22

7. Click on advanced -> SSH -> Authentication and select the browse option to the right of the private key input cell and select the .ppk file you have saved. Select OK.

8. Click login and click yes to the security notice.

9. You will now have the below, where you have created a drag and drop file explorer interface to the AWS EC2. If you do not see the Ubuntu folder on the right, its likely because you are already in it. Just select the only folder showing and it should then display the ubuntu folder.

Note: If you have been using the original database prior to setting this second database up, and have been performing data fetch operations, you may find there are a lot of zipped folders named “Pull Files” now present in the window, where there were previously none. This is expected.

Appendix B – Login to PuTTY

1. Open PuTTY

2. In the Host Name Cell: Type ubuntu@ then the EC2 ipv4 public IP we took note of earlier. Add this information to the login details section of this document.

a. Select the port as 22 and name the session in the saved sessions box.

3. Then click on Connection - SSH – Auth.

4. Click Browse and select the ppk file you have saved

5. Click Open

Appendix C – configICDS.json

The .configICDS.json file is stored on the EC2. The user requires to log on to the EC2 using WinSCP to access and edit the .json file. See Appendix A – Login To WinSCP for details. The .json file stores all of the details that the ICDS requires to remotely push and pull data from and to the server database. It is critical that these details are input correctly, otherwise the data push and pull functions will not work. Below are details of each item in the file.

1. CompanyID: Type Company Name (this will be supplied by Innova and must be written in EXACTLY the same way as its supplied). Make sure Company Name is inside double quotes

2. Port: Usually enter the port field as 42000 (no quotes) – this is the port which will be entered into the Well Seeker RT Data exchange for communication with the ICDS

3. devmode: Leave as false (no quotes)

4. sqluser: Set to the SQL Server master username used to access the database. Make sure this is inside double quotes

5. sqlpass: Set to the SQL Server master password used to access the database. Make sure this is inside double quotes

6. sqlsvr: Set to the SQL Server RDS database Endpoint. Make sure this is inside double quotes.

7. sqldbname: Set to the name of the database you wish the ICDS to connect to. This is the database name that you see when logged in to SQL server Management Studio. Make sure this is inside double quotes

8. sqlport: Set to the port used to connect to the SQL server database (no quotes), usually this is 41433

9. externalPath: Specifies where external files are stored. Input either useDatabase (stores files on the EC2) or useS3 (stores files on an S3)

10. sqldbnamewits: If a user is pushing and storing WITs data to a database separate to the Well Seeker database, insert the name of the database here. Make sure this is inside double quotes. If this function is not used then leave a the doubles quotes empty

11. transferWits: If pushing WITs data to a second database then enter true, if not enter false

12. allowActivePush: If true is entered then a computer can push data back to a server database regardless of the well status. If false is entered then a computer can push data back to a server database only when the well status is Upcoming, Active or Standby. The well status is selected in the Daily Reports dialog

13. awsAccessKeyID: If storing external files on an s3, input the AWS Access Key ID of the IAM user. If not leave blank

14. awsSecretKey: : If storing external files on an s3, input the AWS secret key for the IAM user. If not leave blank

15. awsRegion: : If storing external files on an s3, input the region where the AWS service is hosted eg “us-east-2” or “us-west-2”. If not leave blank

16. s3Bucket: If storing external files on an s3, input the name of the S3 bucket which was created to store the Well Seeker external files. If not leave blank

17. Leave all other fields as they are

Appendix D – Restarting the ICDS Server

1. In order for the changes in a .json file to take effect, the ICDS server needs to be restarted. Open and login to PuTTY. See Appendix B – Login to PuTTY for details

2. Check pm2 is running by typing pm2 ls This should display as below, showing the icdsServer as being online

3. Stop the ICDS server. Type: pm2 stop icdsServer

4. Now start the ICDS server. Type: pm2 start icdsServer

5. You should get the below. Note that the “icdsServer” is the name of the linux file which was placed in the EC2 instance via WinSPC. If you have changed the name for any reason, then you will need to adjust the command by replacing “icdsServer” with whatever the name of the file you added is.

6. Check there are no errors. Type: pm2 ls This will display the icdsServer as being online. If it reads errored then the process has not worked correctly and will need to troubleshot

7. If the ICDS server is online then save the current pm2 settings so it starts correctly after a server restart: pm2 save

8. Close Putty

Appendix E – Useful PuTTY Commands

The following is a list of useful commands which can be used in PuTTY and relate to pm2. These may be helpful if there are any issues / errors when starting the pm2 application.

1. Stop any current pm2 icds processes: pm2 stop icdsServer

2. Delete old pm2 config params: pm2 delete icdsServer

3. Kill any pm2 process which is running: pm2 kill

4. View list of pm2 running apps: pm2 ls

5. Save pm2 settings: pm2 save

6. View logs: pm2 logs