Setting up your Alpha Cloud Test SQL Database

Description

This document explains the steps to setup your test SQL database, included as part of your Alpha Cloud subscription.

Discussion

Many applications published to Alpha Cloud need to access a test SQL database. Production applications usually use a database service, such as Amazon RDS or Microsoft Azure SQL Server, to host the database. However, to help you get started quickly Alpha Software offers a free alternative to services such as RDS. You can create a free test SQL database (either MariaDB, SQL Server or PostgreSQL) that is ideal for use during development.

We do not recommend using Alpha Cloud test databases included with your subscription for production applications for the following reasons:

1. There is no automated backup and restore of the test database data.

2. The test databases are hosted on a Kubernetes cluster on Google Cloud in the Virginia region. If your Alpha Cloud app is hosted in a different region, you will see increased latency.

3. The test databases are not designed to scale.

4. Data is not encrypted in transit or at rest.

Setting up an Alpha Cloud SQL database requires an Alpha Cloud subscription.

Creating the Database Server

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

Log in to your Alpha Cloud account if prompted.

Once you are logged 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 database server type to create. The options are MariaDB, SQLServer, and PostgreSQL.

Then click Create.

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 create a database. The database can be a new, blank database or the sample Northwind database.

Installing a Sample Database

To install the sample Northwind database, check the box for the server you want to add the database to in the Database Servers list. Then, click Actions....

Select Install Sample Database from the menu.

Specify the name for the sample database. You can choose any name you want. In this example, we have entered "northwind".

Then, click Install Sample Data.

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 connect to it 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 checkbox for the server in the Database Servers list.

Then, click Actions... and select Get Connection String.

Alpha Anywhere will generate a connection string as shown below. Note 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 save the connection string as a Named Connection, click Save as Named Connection.

The next dialog allows you select the database to connect to and the name for your Named connection string.

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

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 type:

dim cn as sql::Connection
? cn.open("::Name::AlphaCloud_MariaDB_northwind")
= .T.

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

Select the connection string.

Then, execute the cn.open() code.

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

You can now test your connection by entering this command:

? cn.listtables()

This command lists 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

3rd party tools (e.g. Navicat for MariaDB and MySQL, SQL Server Management Studio for SQL Server) are often used to manage 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.

Checked the MariaDB server. Then, click Actions... and then select Open SSH Tunnel.

After the SSH tunnel has been opened, click Actions... and select SSH Tunnel Details.

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.

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

Creating a Cloud SQL Server Instance

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

To get started, click Actions... and select Create Server.

Select the Database Type (note that the dropdown only includes database types that have not been created.)

After the database server has been created and is ready, and install the sample Northwind database using the steps described above for installing the sample Northwind database in the MariaDB server.

Once the sample database has been created, you can use SQL Server Management Studio to manage your cloud database over the SSH tunnel.

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 get your database credentials.

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

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

The details will look something like this:

Server: SQLServer
Host Name: localhost
Port: 51433
User Name: sa
Password: 0^e49F$3nMSo1Dvwfzx4X#^91j~~$Ru4

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

The Connect to Server dialog will open.

Specify the server name as localhost,51433

Change the Authentication to SQL Server Authentication. Then, enter the user name (Login) and password and click Connect.

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

Videos

Setting Up a Cloud SQL Database

Alpha Cloud includes a SQL database for use during development as part of your subscription.

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

• Watch Video

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

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

See Also

• Managing Test Databases