Database Link Usage

During Archiving execution testing, the database administrator needs to refer to the Archiving (destination) database that will be receiving data from the production (source) database. To refer to the destination database, a special string called a Database Link is required (which is also used when setting up an Archiving execution). The proper syntax and usage of Database Links is described in the following subsections.

Microsoft SQL Server Database Link

The following sample syntax refers to the Archive database on a Microsoft SQL Server if it is on the same server as the source database:

FLX_ARCHIVE_DST.dbo.

wherein FLX_ARCHIVE_DST is the name of the destination database.

The dot at the end of the above Database Link is mandatory.

To be able to archive data to another instance of the MSSQL Server, a linked server on the source database server needs to be created. These are the commands that create the linked server:

sp_addlinkedserver @server='<ArchivingLinkName>', @srvproduct='', @provider='SQLOLEDB', @datasrc='<DestinationDBServerName>'
GO

sp_addlinkedsrvlogin '<ArchivingLinkName>', 'false', '<LocalDBLogin>', '<DestinationDBLogin>', '<DestinationDBPassword>'
GO

EXEC master.dbo.sp_serveroption @server=N'<ArchivingLinkName>', @optname=N'rpc', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'<ArchivingLinkName>', @optname=N'rpc out', @optvalue=N'true'
GO

Please refer to Books Online for Microsoft SQL Server for more information.

If you have the destination database on another MSSQL Server instance, you could receive the following errors during Archiving:

The Operation could not be performed because OLE DB provider "SQLNCLI" for linked server <ServerName> was unable to begin a distributed transaction.
Archiving for <GraphName> begins OLE DB provider "SQLNCLI" for linked server <ServerName> returned message "The partner transaction manager has disabled its support for remote/network transactions."
OLE DB provider "SQLNCLI" for linked server <ServerName> returned message "No transaction is active."

These errors occur because of the incorrect configuration of Microsoft Distributed Transaction Coordinator (MSDTC). Please perform the following steps to be able to run DELMIA Apriso Archiving:

First, make sure that the Distribute Transaction Coordinator Service is running on both the source and destination database servers.

Go to Administrative Tools | Services.
Turn on the Distribute Transaction Coordinator Service if it is not running.

If it is running and the client application is not on the same computer as the database server, on the computer running database server:

Go to Administrative Tools | Component Services.
On the left navigation tree, go to Component Services | Computers | My Computer | Distributed Transaction Coordinator | Local DTC (you may need to double-click and wait, as some nodes need time to expand).
Right-click Local DTC and select Properties.
Select the Security tab.
Make sure you check Network DTC Access, Allow Remote Client, and Allow Inbound/Outbound (please note that some options may not be necessary for your specific configuration).

The service will restart.

You may need to reboot your server if it still does not work.

On your client computer use the above procedure to open the Security Configuration setting in order to check Network DTC Access and the Allow Inbound/Outbound option. Restart the service and computer if necessary.

On your SQL server service manager, click the Service drop-down and select Distribute Transaction Coordinator. It should be also running on your server computer.

When the Microsoft SQL Server database is on a different server, the Database Link should look as follows:

archiveserver.FLX_ARCHIVE_DST.dbo.

wherein archiveserver is the name of the Database Link and FLX_ARCHIVE_DST is the name of the destination database.

Whenever a Database Link consists of three parts separated by periods (e.g., archiveserver.FLX_ARCHIVE_DST.dbo.), the Archiving Engine assumes that the destination database is located on a different server and then skips all commands, even SET IDENTITY_INSERT. In this scenario, the database administrator needs to make sure that the Identity properties are removed from all of the tables that obtain data on the Archiving database, even if the destination server is located on the same server. Therefore, if the Archiving database is located on the same server as the source database, it is more convenient to not use the server name in the Database Link and instead only use an abbreviated form such as:

FLX_ARCHIVE_DST.dbo.

The abbreviated form, consisting of only two parts, enables running the SET IDENTITY_INSERT instruction, thus eliminating the necessity of the database administrator manually removing the Identity properties.

Oracle Database Link

To create a Database Link on Oracle, you need to have the appropriate database permissions on the source (operational) database, which can be granted by executing the following statement:

GRANT CREATE DATABASE LINK TO “SOURCE_SCHEMA”

To create a Database Link you need to execute the following statement:

CREATE DATABASE LINK FlxArchiveDatabase
CONNECT TO FLX_ARCHIVE_DST IDENTIFIED BY oracle
USING 'tns to archiveserver'

USING in most cases should contain the definition of oracle services(sid) from the tnsnames.ora file (path NETWORK\ADMIN).
tnsnames.ora contains the sid service_name definition as below:

service_name =
(DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = server_name)(PORT = service_port))
   (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SERVICE_NAME = service_name)
   )
)';

To refer to the Database Link in the above example, the database administrator needs to use the Database Link created:

FlxArchiveDatabase