Integrating with a database

This tutorial explains how to work with cmdlets that get or set the target database.

Set the deploymentName variable

First set the deploymentName variable:

$deploymentName="InfoShare"

Getting the database integration

The Get-ISHIntegrationDB cmdlet extracts information on the deployment's database integration:

Get-ISHIntegrationDB -ISHDeployment $deploymentName

This cmdlet would output:

RawConnectionString     Engine
-------------------     ------
connectionstring        sqlserver2014

Setting the database integration

The Set-ISHIntegrationDB sets the deployment's database integration. Valid target databases are:

• Oracle
• SQLServer 2012
• SQLServer 2014

SQL Server

In the following example we want to connect with a database, e.g. ISHDatabase, hosted on a SQL Server 2014, e.g MSSQLServer. This example has two variations, one with username/password security and one using SQL Server's integrated security:

$initialCatalog="ISHDatabase"
$dataSource="MSSQLServer"

$userId="isource"
$password=""

# SQL example with user/password security
$connectionString="Provider=SQLOLEDB.1;Password=$password;Persist Security Info=True;User ID=$userId;Initial Catalog=$initialCatalog;Data Source=$dataSource"
Set-ISHIntegrationDB -ISHDeployment $deploymentName -ConnectionString $connectionString -Engine sqlserver2014 -Raw

# SQL example with integrated security
$connectionString="Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=$initialCatalog;Data Source=$dataSource"
Set-ISHIntegrationDB -ISHDeployment $deploymentName -ConnectionString $connectionString -Engine sqlserver2014 -Raw

Note: The -Raw parameter is no longer supported since the version 1.6 of the ISHDeploy module.

Oracle

In this example we want to connect with a service, e.g. ISHDatabase, hosted on an Oracle Server, e.g OracleServer:

$serviceName="ISHDatabase"
$host="OracleServer"

$userId="isource"
$password=""

# Oracle example
$connectionString="Provider=OraOLEDB.Oracle.1;Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=$host=)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=$serviceName)));User Id=$userId;Password=$password"
Set-ISHIntegrationDB -ISHDeployment $deploymentName -ConnectionString $connectionString -Engine oracle -Raw

Testing the database integration

The Test-ISHIntegrationDB cmdlet tests if the deployment's database integration is valid:

Test-ISHIntegrationDB -ISHDeployment $deploymentName

Note: When an oracle engine is defined, the Test-ISHIntegrationDB will skip and just raise a warning.