Estimated reading time: 6 minutes
It’s no secret that databases are incredibly powerful, and when used efficiently, they can significantly enhance your capabilities. The strong integration between Aria Automation (formerly vRealize Automation – vRA) and Aria Automation Orchestrator (formerly vRealize Orchestrator – vRO) allows us to connect and interact with external SQL Databases seamlessly. This integration enables us to expand our capabilities using Service Broker Forms, Day Two Actions, and more. In this post, we’ll focus on establishing connectivity to the database, with use cases to be covered in future articles.
Prerequisites
- A SQL Server database instance should be deployed. The exact configuration (i.e., version, edition, naming convention, etc.) is expected to vary significantly between environments, so I will not cover it here.
- Credentials which are privileged to access the database. For best results, ensure the user has adequate permissions to add/modify/remove database tables and records.
Getting Started
Using a web browser of choice, navigate to your Aria Automation instance and authenticate as needed. Once authenticated, you will be presented with the “My Services” page, which appears similar to the below screenshot.
Click on the “Orchestrator” tile to launch Aria Automation Orchestrator.
The Aria Automation Orchestrator Dashboard page is displayed. Click on the “Workflows” link, located within the left-side navigation pane.
The Workflows page is displayed and presents a variety of “out of the box” Workflows, which appear as tiles/cards on the screen. Note that the exact workflow names and quantity may vary between this demonstration and your own instance.
Although not required, it is often easier to find the Workflow(s) you need by switching to the “tree” view, which displays the Workflows in a cascading folder view instead of cards/tiles. You can easily toggle between views by clicking on the “Tree View” icon, located at the far right corner of the page, as shown below.
The navigation pane on the left-side of the page now displays a number of folders, which can be expanded by clicking on the “chevron” icon to the left of the folder name. Let’s expand the “SQL” folder.
Once expanded, you will see a subfolder named “Configuration“. Click on the chevron icon to expand this folder as well. Notice that additional Workflows have appeared, as shown below.
Click on the “Add a Database” workflow object, located beneath the “Configuration” folder. Notice that the workflow opens on the remaining two-thirds of the window. You may re-size the panes as needed for legibility.
In this case, running the Workflow means Orchestrator will perform an action on itself, which makes sense: we want to add/link an external database to Orchestrator. Click on the “Run” button, located along the top ribbon.
The “Add a Database” Workflow will now consume the window and display a series of tabs and input fields. The “Database” tab should be selected by default, displaying three input fields beneath:
- Name
- This is the name you wish to refer to the connection as in Orchestrator. Think of this as a “display name” so you can distinguish between this connection and others in the future. For this demonstration, we will use the name “SQL_vratest”
- Database type
- This is a dropdown list which allows selection of Oracle, MS SQL, PostgreSQL, or MySQL. Select the appropriate option from the list. In this demonstration, we will select “MS SQL”.
- Connection URL
- Based on your selection from the “Database Type” dropdown menu, the Connection URL prefix will automatically populate into the Connection URL field. In this demonstration, the “Database type” is “MS SQL”, therefore the “Connection URL” field was pre-populated with “
jdbc:jtds:sqlserver://“. Do not remove this text; simply place your cursor at the end (far right) of it, and type the Fully Qualified Domain Name (FQDN) of your SQL Server with the database name expressed with a forward slash (i.e., mysqlserver.domain.local/mydb). In this demonstration, the Connection URL is “jdbc:jtds:sqlserver://dbsvr01.domain.local/vratest“.
- Based on your selection from the “Database Type” dropdown menu, the Connection URL prefix will automatically populate into the Connection URL field. In this demonstration, the “Database type” is “MS SQL”, therefore the “Connection URL” field was pre-populated with “
Once all three fields are populated, click on the “User Credentials” tab, which displayed three new input fields:
- Session mode
- There are two options: Shared Session and Per-User Session. The difference is explained in the text label above the field.
- Shared Session: The credentials you specify below will be used for each action you execute in the future. For example, if you intend to leverage a Workflow to add a database table, it would be achieved with the below user credentials. Think of this as a service account model.
- Per-User Session: The credentials are retrieved from the currently logged-in user. This differs from the “Shared Session” mode which I likened to a “service account” model in that all actions executed on the database server using an Orchestrator Workflow will be authenticated by the user. Only select this option if your user(s) have sufficient privileges to add/modify/remove database objects. Note that selecting “Per-User Session” will hide the “User name” and “Password” fields on the screen.
- There are two options: Shared Session and Per-User Session. The difference is explained in the text label above the field.
- User name
- The user ID of an account with privileges to add/modify/remove database objects. This can be a database local or domain account. In this demonstration, the “User name” will be “vra-user” which is a local database account.
- Password
Once you have provided values for each field, click on the “Run” button, located at the bottom-left corner of the window.
The Workflow will now execute and display the progress. This may take a moment to complete, depending on various factors in your environment. After a moment, the status label at the top the screen should indicate “Completed“, as shown below.
If you encounter an error, click on the “Logs” tab, located beneath the Workflow Canvas. Review the log output for indications as to what went wrong. Ensure the credentials are valid using SQL Server Management Studio (SSMS) or similar. In addition, ensure that additional characters, including blank spaces, were not copied into the “Password” field, if you used copy and paste from another source. If you need to try the workflow again, simply click on the “Run Again” button, located along the top ribbon. This will save you time, as most fields will be pre-populated from the last run.
Assuming the Workflow completed successfully, click on the “Close” button, located at the bottom-left corner of the window. The “Workflow Runs” page will now display.
Click on the “double chevron“/”double arrows” button, located at the top-left corner of the page, which will expand the navigation pane.
Click on the “Inventory” menu item, located beneath the “Administration” heading.
Click on the “chevron” icon to expand the “SQL” menu item. Notice that “SQL_vratest” now appears as a linked database! Once the database is populated with tables, they will appear beneath the database object as well. You may also click on the database object (“SQL_vratest”) to view its configuration (details provided from the Workflow run above, with exception to the Password field).
This “Inventory” view simply indicates that the database was successfully linked and can be browsed by Orchestrator. We will use SQL Databases like this one in future articles – see you then!