AWS EC2 - Enzo Server RTM 3.1 - QUICK START
Welcome to the AWS EC2 - Enzo Server 3.1 - Quick Start online documentation. You will find general information about your VM configuration and helpful hints on how to use Enzo.
Trying Enzo Server is best done by provisioning an EC2 Virtual Machine from the a cloud Marketplace. In this section, we show you how to create a new Enzo Server VM in AWS, and connect to it. This VM comes pre-configured with a dozen adapters, some of which are pre-configured for use. In addition, this section provides a high level overview of the services configured on this Virtual Machine.
Create a New Enzo Server VM
If you have not done so already, create your own Enzo Server 3.1 EC2 instance in AWS from the Marketplace . To find Enzo in AWS, simply log into your AWS Console, choose to create a new Resource, and search for Enzo. The 30-day trial edition of Enzo Server begins when the VM is first started..
Note that this Enzo Virtual Machine runs a pre-configured Enzo Server Standard Edition, free for 30-days. While the Enzo Server license is free for 30 days, you may incur charges for hosting the VM itself in AWS. Enzo Unified is not responsible for the AWS-related costs you may incur, including but not limited to storage and compute costs. Please read the Enzo EULA carefully.
Virtual Machine Configuration
The Enzo Server VM is pre-configured with the following software and options:
- SQL Server Express Edition with SSMS (SQL Server Management Studio)
- FTP Server
- Microsoft MSMQ
- Office 365 Data Access (for accessing Excel documents)
- Enzo Server 3.1
- Google Chrome
Enzo Server is pre-loaded with the following adapters. Some adapters are already configured for use, while others require further configuration to authenticate on remote systems, such as the SharePoint adapter. The adapters that are pre-configured and available immediately upon starting the VM are marked with an asterisk (*).
- CSV (*)
- Files (*)
- FTP2 (*)
- HTTPEndpoint (*)
- MSMQ (*)
- Parquet (*)
- XLS (*)
Sample SQL Commands
The Enzo Server VM comes with a few sample files so you can try some of the adapters immediately. The sample files are located under the c:\tmp\csv\, c:\tmp\parquet\ and c:\tmp\excel\ folders.
The following sample SQL files are available on the Enzo Server VM upon startup, located in the c:\init\sql\ folder:
For example, you can immediately query CSV files using the EnzoDemo_CSV_FTP.sql sample SQL code. A few sample CSV files are available on the virtual machine for testing purposes. Here is a sample command provided:
SELECT * FROM bsc.csv.[file@firestations] WHERE _path = 'c:\tmp\csv\usfa-registry-FL.txt'
Enzo SQL Commands Primer
In this section, we will connect to Enzo Server directly from SQL Server Management Studio and explore a few Enzo commands.
Connect to Enzo
Start SSMS and Login to EnzoBecause Enzo Server emulates parts of the TDS protocol, you can connect to Enzo just like you would connect to a SQL Server database: by providing the IP address (or server name) and port of Enzo Server, and a login account/password. In addition, since Enzo Server emulates SQL Server, it has its own 'sa' account/password as well. Last but not least you can create additional logins using Enzo Manager, the management interface for Enzo Server.
NOTE: Active Directory accounts are supported with the Enterprise Edition of Enzo Server.
Enter Enzo's IP Address and port (IP,Port) in the Server Name field; this can also
be a server name (try localhost,9550 if Enzo runs locally on port 9550).
WARNING: Do not enter a SQL Server name; this must be an Enzo Server.
Keep the SQL Server Authentication option for this test, and enter the 'sa' account and password (this is the default on this VM).
The Standard edition of Enzo Server only allows the use of the 'sa' account
Click on Connect and you will be connected to an Enzo Server instance.
Explore the Help Command
Once logged in to Enzo Server, you can start running SQL commands. Let's start with a command unique to Enzo Server: the help command. Enzo Server provides an inline help mechanism allowing you to explore what you can do. Let's discover the list of adapters that are loaded on the Enzo Server you are connected to by executing this command:
EXEC HelpBy default, all commands execute against the BSC database, the default database for an Enzo Server, so the above command is equivalent to EXEC bsc.help.
The output of this command shows the list of adapters currently loaded in Enzo Server on the VM. Your output may show a different list. If you are not seeing an adapter you expected to see in this list, the adapter failed to load and you should look at the Enzo log or the Windows Event Log to see why.
You can run this command to explore the SharePoint adapter:
EXEC SharePoint.helpHowever, let's use the _handlers virtual table so we can use a SELECT command that we can further filter:
SELECT * FROM SharePoint._handlers
In Enzo, a handler is equivalent to an Object in SQL Server; it is usually a Stored Procedure and/or a Table. Some of the Procedures can be called as a Table using a SELECT statement providing filtering and ordering options that the Stored Procedure equivalent may not provide.
For example, the GetListItemsEx procedure found in the SharePoint adapter is the handler that can be used with an EXEC command to return list items from SharePoint. If you look at the 'SELECT' column of the _handlers output you will see 0; this means this specific handler does not support a SELECT equivalent. To figure out how to call the GetListItemsEx handler, and see what input parameters and returned columns are, run this command:
EXEC SharePoint.GetListItemsEx help
Explore the SharePoint Adapter
The SharePoint adater is a powerful connector for SharePoint Server and SharePoint Online, with support for a large number
of operations, including managing lists, documents, users, groups, folders, and sites. The SharePoint adapter also allows accessing
and managing Sub Sites and Document Lists with support for large file transfers.
Before you can use this adapter, you must configure it to access an existing SharePoint site.
To configure the SharePoint adapter from SSMS, use the SharePoint._configCreate command as such:
EXEC Sharepoint._configcreate 'site1', 1, 'email@example.com', '__USER1_PASSWORD__', 'https://mycompany.sharepoint.com/teamsite/'
Let's look at the lists handler; its Procedure and TableName are the same
and it is a SELECT operator. This means we can call this handler this way:
SELECT * FROM SharePoint.lists
Note that this command will only succeed if you have configured the Enzo Adapter to connect to a SharePoint site. The simplest way to configure Enzo adapters is to use Enzo Manager.
When you start Enzo Manager you must first login; you can only login using an administrative account into Enzo Manager (the 'sa' account). Once logged in, you will see a list of adapters loaded in the Enzo Server you connected to. To configure an adapter, click on the adapter name. For example clicking on the SharePoint adapter brings up the configuration section of SharePoint.
Manage Configuration Setting
In order to communicate with underlying services most adapters require creating at least one configuration setting. Each login defined in Enzo Manager can optionally have one default configuration setting that will automatically be loaded when running SQL commands.
The Standard edition of Enzo Server only provides the 'sa' account
In this screenshot you can see the SharePoint configuration settings defined for the 'sa' account.
From this screen you can create multiple configuration settings. However only one setting can be marked as the default configuration. The user interface allows you to duplicate settings. Changes to configuration settings take effect immediately.
The first configuration in the list was named default, although it could have been given any other name. The star (*) next to the configuration name means this is the configuration that is automatically loaded for the adapter.
To create a configuration setting, simply click on the NEW icon and enter the necessary settings.
If you do not have a default configuration setting, or would like to switch the configuration setting to use during a session, use the SharePoint._configUse SQL command. Note that using Linked Server to call Enzo requires the use of a default configuration setting; using the _configUse command through Linked Server does not work.
View Adapter Activity Log
Each adapter stores its own log information to disk in a specific directory. Enzo Manager allows you to view the current day's worth of activity log which includes successful and failed calls. To view the log, click on Environment->View Current Adapter Log
This screen provides a view into the current adapter log (SharePoint in this case). You can choose another adapter if desired, and filter by message severity.
Accessing Enzo through Linked Server
One of the main benefits of using Enzo is that most of the functionality of the adapters is available through Linked Server. This means that you can leverage the power of T-SQL to program processes and integrate your systems.
Your Enzo Server VM comes pre-configured with a linked server to Enzo called [localhost,9550]. To try calling Enzo through a Linked Server, you must first open a new Query Windows, and make sure to connect to your local SQL Server Instance (not Enzo).
SELECT * FROM [localhost,9550].bsc.sharepoint.lists
By leveraging Linked Server you can also call Enzo adapters from Stored Procedures, Views, Triggers, Functions and even from SQL Server Agent jobs.
See the Linked Server section for more information.