Alpha Cloud SQL Databases

 

Typically when you publish an application to Alpha Cloud your application will need to access a cloud SQL database. For production applications, it is typical to use a database service, such as Amazon RDS, or Microsoft Azure SQL Server, etc. to host your database. However, to help you get started quickly Alpha Software offers a free alternative to services such as RDS. You can create a free SQL database (either MariaDB, SQL Server or Postgress) that is ideal for use during development.

NOTE: It is possible for Alpha Cloud applications to access on-premises SQL databases, but this requires that the firewall that protects the on premises database allow access from the Alpha Cloud egress IP addresses.

Alpha Software recommends that these free SQL cloud databases should not be used for production applications for several reasons: a) there is no automated backup and restore of the database data, b) the free databases are hosted in the Amazon Virginia region and if your Alpha Cloud app is hosted in a different region you will see increased latency and c) the databases are not designed to scale.

NOTE: For more information on why Alpha Software does not recommend using your free Cloud Development Server in production, click this link.

 

In order to set up a free Alpha Cloud SQL database you will need an Alpha Cloud subscription.

This document explains the steps to setup you Alpha Cloud SQL database (once you have signed up for an Alpha Cloud subscription).

 

Videos

 

Alpha Cloud SQL Database Setting Up a Cloud SQL Database Alpha Software provides a free cloud SQL database for use during development.

In this video we show how to set up a MariaDB cloud SQL database server and how to install the sample Northwind database.

Watch Video

Date added: 2020-07-10
Alpha Cloud SQL Database Creating a Named Connection String for your Alpha Cloud SQL Database In this video we show how to create a Named Connection string that points to your Alpha Cloud SQL database so that you can use the database in your Alpha Anywhere applications.

Watch Video

Date added: 2020-07-10
Alpha Cloud  SQL Database Using 3rd Party Tools (e.g. Navicat) with your Alpha Cloud SQL Database It is common for developers to maintain the SQL databases used in their Alpha Anywhere applications using 3rd party tools (such as Navicat, SQL Server Management Studio, pgAdmin, etc.)

In this video we show how you can connect Navicat to an Alpha Cloud MariaDB server.

Watch Video

Date added: 2020-07-10

 

 

Creating the Database Server

To get started, open the Web Projects Control Panel, click the Alpha Cloud button and select Advanced, Subscription Test Databases.

 

If you have not yet signed into your Alpha Cloud account you will get a sign in dialog.

 

 

Once you have signed in you will get the Manage Subscription Database Servers dialog. Initially, this dialog will not show any existing servers.

 

Click the Actions.. button to display a menu.

 

 

Select the Create Server menu option to create a new server.

Select the type of database server you want to create. The options are MariaDB, SQL Server and Postgress

 

 

Then click the Create button.

 

 

 

After a short while you will see a Success dialog and the Manage Subscription Database Server dialog will show you new database server.

 

 

Once you have created the new server you will probably want to either create a new (empty) database, or install the sample database (which is the Northwind database)

Installing a Sample Database

To install the sample Northwind database check box for the server that you want to operate on in the left-most column in the Database Servers table (in this case we only have only created a single server, but you might have created multiple servers, for example a MariaDB server and a SQL Server server), then click the Actions... button.

NOTE: You should wait a few seconds after creating the new database server before trying to perform any actions on the server to give the new server time to spin up.

Select Install Sample Database from the menu.

 

 

You will then need to specify the name for the sample database. You can choose any name that you want. In this example, we have entered northwind.

 

Then click the Install Sample Data button.

 

 

After a short while you should get the Success dialog box.

 

 

Connecting to Your Alpha Cloud Database

Now that you have created a sample database in your Alpha Cloud SQL Database you will want to be able to connect to i in your Alpha Anywhere application. In order to do this you will need a connection string. Alpha Anywhere can automatically create this connection string for you.

 

To create a connection string make sure you have checked the server in the checkbox on the left side of the dialog,

 

 

Then press the Actions... button and select the Get Connection String menu command.

Alpha Anywhere will generate a connection string as shown below. Note however, that this connection string does not specify the name of the database. You will be able to specify the name of the database in the next step when you save this connection string as a Named Connection. To do this, click the Save as Named Connection button.

 

The next dialog allows you select which database you want to connect to and you can also specify the name for your Named connection string.

Once you have selected the Server Database and specified the Connection Name, click the Save button.

 

 

Now that you have created a named connection string, you can test it from the Interactive window in Alpha Anywhere.

Open the Interactive window and then type:

 

dim cn as sql::Connection

?cn.open("::Name::AlphaCloud_MariaDB_northwind")
= .T.

 

After you type the opening ( in the cn.open() method, you will be able to right click on the parameter name (SQLConnectionString as C) to get a list of available connection strings. The connection string you just created should be listed.

 

 

Select the connection string.


 

 

After a short delay the cn.open() method should return .t. to indicate that the connection was successfully opened.

NOTE: The first time you open a connection there is a short delay. Subsequent opens should be much faster because the SSH tunnel to the cloud database is already open.

 

You can now test your connection by entering this command:

?cn.listtables()
 

You should get a listing of all of the tables in your cloud database, as shown below:

 

= categories
customercustomerdemo
customerdemographics
customers
employees
employeeterritories
order details
orders
products
region
shippers
suppliers
territories

 

 

Using 3rd Party Tools to Manage Your Database

It is common for Alpha Anywhere developers to use 3rd party tools (e.g. Navicat for MariaDB and MySQL, SQL Server Management Studio for SQL Server) to manage their databases.

You can easily connect a 3rd party tool to your cloud database. For example, let's connect Navicat to our new MariaDB cloud database.

First, we will need to open a SSH tunnel to the cloud database and get the database credentials.

NOTE: You do not need to explicitly open the SSH tunnel when connecting to your cloud SQL database from Alpha Anywhere. Alpha Anywhere will automatically open the SSH tunnel. It is only necessary to manually open the SSH tunnel to the cloud database when you are using a 3rd party tool. Furthermore, the 3rd party tool will only be able to connect to the cloud database while Alpha Anywhere is open.

Make sure you have checked the MariaDB server, then click the Actions... button and then select the Open SSH Tunnel command.

 

 

 

After the SSH tunnel has been opened, click the Actions... button again and this time select the SSH Tunnel Details menu command.

 

 

 

The dialog shows the port, user name and password to connect to your cloud database.

 

 

 

Next, open Navicat and select the option to create a new connection. Enter the connection details as shown below.

NOTE: Even though you are connecting to a cloud SQL database, the host address is localhost. That's because Navicat will be communicating to the cloud database through the SSH tunnel.

 

 

 

Once Navicat is connected you will be able to see all of the tables in your cloud database.

NOTE: Remember, the SSH tunnel that allows Navicat to communicate with the cloud database will be closed when Alpha Anywhere is closed.

 

 

 

Creating a Cloud SQL Server Instance

Now that you have created a cloud MariaDB server, you might also want to create a SQL Server instance.

NOTE: You do not have to create a MariaDB server before you create your SQL Server instance.

To get started, click the Actions... button and select the Create Server menu command.

 

Select the Database Type (since you have already created a MariaDB server, you will note that the dropdown only includes database types that have not yet been created).

 

After the database is created, wait a short while and then install the sample Northwind database using the steps described above for installing the sample Northwind database in your MariaDB server.

Once the sample database has been created, you might want to use SQL Server Management Studio to manage your cloud database.

Connecting SQL Server Management Studio to your Cloud SQL Server Database

To connect SQL Server Management Studio to your cloud SQL database you must first open the SSH tunnel to the cloud database and then get the credentials for your database.

To open the SSH tunnel, click the Actions... button (be sure you have checked the SQL Server database)

 

Then select the Open SSH Tunnel command. Finally select the SSH Tunnel Details command and copy the details to the clipboard by clicking the Copy to Clipboard button.

 

The details will look something like this:

 

Server: SQLServer
Host Name: localhost
Port: 51433
User Name: sa
Password: a^e49F$3QMSo1Dv9fzx4X#^13j~~$Ru4

 

Next, open SQL Server Management Studio, click the Connect button and select the Database Engine... command.

 

 

The Connect to Server dialog will open.

Specify the server name as localhost,51433

 

NOTE: In SQL Server Management studio when you want to connect to a database that is not on the standard default port of 1433 you specify the port after the server location separated by a comma.

 

 

Enter the user name (Login) and password and then click the Connect button.

 

The Object explorer will then show your cloud SQL Server database. You can use SQL Server Management studio to explore data, create new tables, modify existing tables, etc.