TIBCO Scribe® Online Connector For Microsoft SQL Server

Use the Microsoft SQL Server Connection as a source or target Connection for On schedule or On event apps, or as a target Connection for Data replication apps.

Possible use cases for the Microsoft SQL Server Connector include:

Replicate data from a Cloud based application into Microsoft SQL Azure using a Connect cloud agent.
Replicate data from a Cloud based application into an on-premise version of Microsoft SQL Server using a Connect cloud agent.
Use a Connect cloud agent to integrate Cloud based data, such as Salesforce, with Microsoft SQL Azure, which is a cloud based application.
Use a Connect cloud agent to integrate Cloud based data, such as Salesforce, with an on-premise version of Microsoft SQL Server.

Connector Specifications

	Supported
Agent Types
Connect on-premise	X
Connect cloud	X
Data Replication Apps
Source
Target	X
On Schedule Apps
Source	X
Target	X
On Event Apps
Source	X
Target	X
Flows
Integration	X
Request-Reply	X
Message

Note: This Connector is available from the TIBCO Cloud™ Integration Marketplace. See Marketplace Connectors for more information.

Setup Considerations

When you configure the Microsoft SQL Server Connection, you need to specify how you log into Microsoft SQL Server, which can be either:

Microsoft Windows account
Microsoft SQL Server account

TIBCO Cloud™ Integration - Connect requires a dedicated Microsoft SQL Server database for each data source you replicate. TIBCO Cloud™ Integration - Connect supports either Windows or Microsoft SQL Server authentication with the following versions of Microsoft SQL Server:

Microsoft SQL Server 2017: Enterprise, Standard, and Express
Microsoft SQL Server 2016: Enterprise, Standard, and Express
Microsoft SQL Server 2014: Enterprise, Standard, and Express
Microsoft SQL Server 2012: Enterprise, Standard, and Express
Microsoft SQL Server 2008 R2: Enterprise and Standard
Microsoft SQL Server 2008: Enterprise and Standard
Microsoft SQL Azure

Note: Microsoft Azure SQL Data Warehouse is not supported by this Connector.

Note: The Microsoft SQL Server user must have Insert, Select, Update, and Delete access on the selected database. In addition, this user must be able to create tables and indexes for this database.

Selecting An Agent Type For Microsoft SQL Server

This Connector supports both the Connect cloud and the Connect on-premise agent for the supported app types listed in the Connector Specifications table, regardless of the location of the data. Data can be in the cloud or on-premises. See Installing A TIBCO Cloud™ Integration - Connect On-Premise Agent or Provisioning A TIBCO Cloud™ Integration - Connect Cloud Agent.

Note: If you use a Connect cloud agent with an on-premise datastore, you may need to whitelist the IP addresses of the Connect cloud agent managers to allow the Connect cloud agent to access your network. See Whitelisting Requirements.

Connect Cloud Agent

Requirements for using the Connect cloud agent with the Microsoft SQL Server Connector are as follows:

Firewall rules must be updated to allow the Connect cloud agent to access your network. See Whitelisting Requirements for a list of IP addresses.
Best practice is to install a valid Public SSL Certificate on the server where Microsoft SQL Server is installed. Private certificates are not permitted.
If you install a Certificate, consider adding the following parameters in the Additional Parameters field on the TIBCO Cloud™ Integration - Connect Microsoft SQL Server Connection dialog to encrypt the connection between TIBCO Cloud™ Integration - Connect and Microsoft SQL Server:
Encrypt=true;TrustedServerCertificate=true;
Refer to Microsoft's documentation on these parameters for additional information.

Connecting To Microsoft SQL Server

Note: Best practice is to create Connections with credentials that limit permissions in the target system, following the principle of least privilege. Using Administrator level credentials in a Connection provides Administrator level access to the target system for TIBCO Cloud™ Integration - Connect users. Depending on the entities supported, a TIBCO Cloud™ Integration - Connect user could alter user accounts in the target system.

Select Connections from the menu.
From the Connections page select Create to open the Create a connection dialog.
Select the Connector from the list to open the Connection dialog, and then enter the following information for this Connection:
- Name — This can be any meaningful name, up to 25 characters.
- Alias — An alias for this Connection name. The alias is generated from the Connection name, and can be up to 25 characters. The Connection alias can include letters, numbers, and underscores. Spaces and special characters are not accepted. You can change the alias. For more information, see Connection Alias.
- Server Name — Name of the SQL Server to which you want to connect.
- Database Name — Name of the database to which you want to connect.
  Note: If your Microsoft SQL Server databases are collated as case-sensitive, make sure you use the correct spelling for the database name here.
- Authentication —The authentication modes are:
  - SQL Server — Select if using a SQL login account to authenticate the database Connection.
  - Windows — Select if using a Windows user account to authenticate the database Connection.
    Note: For information about which authentication mode to use, see your System Administrator.
    Windows Authentication is not supported for Microsoft Azure SQL.
- Login — The user name for this Microsoft SQL Server instance. For Windows Authentication, the login name must include the fully qualified domain name in one of the following formats:
  - domain\Username
  - Username@domain.com
- Password — The password for this Username.
- Additional Parameters — Optional field where you can specify one or more parameters to enable or disable the use of the following:
  - AllowSprocs — Show or hide stored procedures, default is false.
  - AllowTables — Show or hide tables, default is true.
  - AllowViews — Show or hide views, default is true.
  - QueryTimeout — Controls the timeout value for a query of the source data, default is 600.
  - TargetTimeout — Controls the timeout value for Update, Insert, or Delete operations on the target data, default is 30.
    When this field is blank, default settings shown above are used. True shows the objects and false hides them. Syntax for the Additional Parameters field is as follows:
    - Parameters are case sensitive.
    - If misspelled or invalid, they are ignored.
    - Separate each parameter with a semicolon (;).
    - To modify timeouts, add them in the parameters field as follows:
      QueryTimeout=700;TargetTimeout=100;
    - To turn off or explicitly provide access to one or more objects, add them in the parameters field. For example:
      AllowSprocs=true; AllowTables=false;
      For a list of other Connection parameters that can be used, see the MSDN reference site: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnectionstringbuilder(v=vs.110).aspx. For example:
      AllowSprocs=true;LoadBalanceTimeout=100;
      For information about using stored procedures, see Execute Block and Native Query Block.
- Object Filter — Use this field to select the stored procedures, tables and/or views returned in metadata when you connect to your SQL database. Options available in this field are based on the settings in the Additional Parameters field. For example, if Additional Parameters is set to show tables, but hide views and stored procedures, then this field is only concerned with tables. Settings include:
  - Blank — Returns all tables, views and stored procedures when set to true in Additional Parameters.
  - Tables= — Returns selected tables when tables are set to true in Additional Parameters.
  - Views= — Returns selected views when views are set to true in Additional Parameters.
  - Sprocs= — Returns selected stored procedures when stored procedures are set to true in Additional Parameters.
  Syntax for the Object Filter field is as follows:
  - All three options, Tables, Views, and Sprocs, can be used at the same time, but each option must be separated by a carriage return.
  - Options are case sensitive, but database object names are not.
  - To specify multiple values for a parameter, separate each value with a semicolon (;).
  - Wildcard values, such as * and ? can be used. For example, use the * symbol to load all tables that start with aa by entering aa*. Use the ? symbol to represent single characters in the object name, such as Tables=CUST???? to retrieve tables that start with CUST and have four trailing characters.
```
Tables=account;customer;
Views=vw1;vw2*;
Sprocs=sp1;sp2;special_??;
```
Select Test to ensure that the agent can connect to your database. Be sure to test the Connection against all agents that use this Connection. See Testing Connections.
Select OK/Save to save the Connection.

Note: For a database with a large number of tables and stored procedures, it is recommended that you limit the information returned to TIBCO Cloud™ Integration - Connect. Use the Additional Parameters and Object Filter fields on the Connection dialog for the Connector to specify the objects that should be returned from the database.

Metadata Notes

Filtering

When a datetime is configured on the Query block on the Block Properties Net Change Tab to query for new and updated records, that configuration is treated as an additional filter. The Net Change datetime filter is applied as an AND after any other filters specified on the Block Properties Filter Tab. TIBCO Cloud™ Integration - Connect builds a query combining both the Net Change filter and the filters on the Filter tab. See Net Change And Filters for an example.

Some Connectors for TIBCO Cloud™ Integration - Connect only support one filter. For those Connectors you can use either Net Change or one filter on the Filter tab, not both.

Note: The Net Change date is ignored when previewing data on the Preview tab. Filters on the Block Properties Filters tab are used to filter the data on the Preview tab.

In the Microsoft SQL Server Connector, the filter operator IsLike is treated the same as an equals operator. It requires an exact match to return data.

Microsoft SQL Server Connector As A Data Replication Target

Net Change

The first time a Data replication app executes it creates the tables and adds all the records for the selected or recommended entities in the target Microsoft SQL Server datastore. Subsequent replications attempt to process only new and updated records. To filter for only new and updated records, TIBCO Cloud™ Integration - Connect does the following:

Checks the SCRIBE_MODIFIEDON field in the target Microsoft SQL Server datastore
Locates the newest date for each replicated table.
Uses that date as filter to query the source datastore and select only those records that are newer than the date in the target datastore.

Note: In cases where there are entities that do not have an associated date field, all records for those entities are processed each time a Data replication app is executed.

Deleted Records In Microsoft SQL Server Data Replication Apps

Records deleted in the source datastore are not deleted in the corresponding table in the target Microsoft SQL Server replication database. Instead, each record has an associated value in the SCRIBE_DELETEDON field for the replicated table. The SCRIBE_DELETEDON field contains a datetime stamp that corresponds to the date and time the record was deleted from the source datastore.

Note: Occasionally, some deleted records are reprocessed because the SCRIBE_MODIFIEDON field determines the date used to filter for new, changed, and deleted records from the source datastore. If the most recent SCRIBE_MODIFIEDON date is older than the most recent SCRIBE_DELETEDON, the deleted records between the two dates are reprocessed.

Entities Without Modification Dates

Typically, entities without modification date fields are not included in the Recommended Entities list, but can be chosen using the Manually Selected option in the Data replication App Details Entities section. If a selected source entity does not have a date for tracking deleted records, when the Data replication app executes, TIBCO Cloud™ Integration - Connect does the following:

Compares the source records to the records in the target.
Determines which records are missing from the source, but still exist in the target.
Updates the SCRIBE_DELETEDON field for the corresponding records in the target database using the datetime of the Data replication app execution.
Deletes the remaining records that have NULL in the SCRIBE_DELETEDON field from the Microsoft SQL Server table.
Appends all of the source records to the table. This guarantees that existing records, updated records, and new records are included in the replication.

Duplicate Records

Warning: Modifying Microsoft SQL database table Index Properties can cause duplicate records during subsequent executions of a Data replication app when Microsoft SQL is the target for a Data replication app. See Duplicate Records When Using SQL As Replication Service Target for additional information.

Delete And Reinsert Records Multiple Times

TIBCO has identified some metadata issues that cause Microsoft SQL Server to delete and then re-insert records multiple times during a replication. The following error message may be generated:

Failed to insert record on first attempt.

Development is working to resolve these metadata issues. Record errors have most frequently been noticed when inserting data into the following entities:

importlog
goal
kbarticle

Because this error is caused by metadata discrepancies, each entity has a different fix, as follows:

Importlog Entity

The first time you replicate a Microsoft SQL Server database that includes the importlog entity:

In Microsoft SQL Server, change the importdataidname column of the importlog table attribute from nvarchar(100) to ntext.
Remove the existing records from the importlog table to ensure that the TIBCO Cloud™ Integration - Connect Data replication app correctly replicates the records the next time it runs. In your Dynamics CRM organization, you can either truncate the importlog table or delete the records from the table, as follows:
```
truncate table importlog
```
or
```
delete from importlog
```
The next time the TIBCO Cloud™ Integration - Connect Data replication app runs, the records should replicate correctly.

Goal Entity

See the TIBCO Cloud™ Integration - Connect Knowledgebase article Record Errors In Goal Entity In A SQL Replication Database.

This article contains a script to correct metadata errors in the goal entity along with instructions on running the script.

Kbarticle Entity

See the TIBCO Cloud™ Integration - Connect Knowledgebase article Record Errors On Kbarticle Entity During Replication.

This article contains a script to correct metadata errors in the kbarticle entity along with instructions on running the script.

Microsoft SQL Server Connector As An App Target

Consider the following when using the Microsoft SQL Server Connector as an app target.

Batch Processing

Supports Batch Processing for the following operations:
- Create
- Delete — The maximum batch size for Delete is 2000. If the batch size is set higher than 2000, TIBCO Cloud™ Integration - Connect creates batches of 2000.
See Batch Processing for additional information.
When mapping attachment fields, the batch size must be reduced to 200 to prevent memory issues on the agent. If the batch size selected for the operation is too large, an error similar to the following is generated:
Specified batch size (2000) exceeds the maximum batch size (200) for mapping a field of type Byte Array. Check field "varbinarymaxtest" (type Byte Array).

Miscellaneous

Errors

Fatal Error

An error from a Microsoft SQL Server Connection that includes the following main message along with any of the detailed messages shown below, indicates a communication problem between Microsoft SQL Server and the TIBCO Cloud™ Integration - Connect agent. If you are using a Connect cloud agent with an on-premise datastore, you may need to modify settings on the server where the datastore is located to allow the Connect cloud agent to access it.

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections.

(provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) 
(provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) 
(provider: TCP Provider, error: 0 - A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.) 
(provider: TCP Provider, error: 0 - No connection could be made because the target machine actively refused it.)

Resolution: Test your Connection with the TIBCO Cloud™ Integration - Connect agent that is running this app and make sure that the agent always has connectivity to your Microsoft SQL Server database. If the problem persists, see your DBA or Network Administrator.

Record Error

If you receive the following error:

Could not allocate space for object 'XYZ' in database 'Your Database' because the 'PRIMARY' file group is full.

Resolution: Your Microsoft SQL Server database has exceeded the maximum size. Either the database is too large or the size has been restricted. See your Microsoft SQL Server Database Administrator.

Login Error

Error thrown when connecting to Microsoft SQL Server:

Resolution: You have chosen an incorrect authentication method in the Connection dialog for the Microsoft SQL Server Connector. Options include SQL Account or Windows Account.

TIBCO Cloud™ Integration - Connect API Considerations

To create Connections with the TIBCO Cloud™ Integration - Connect API, the Microsoft SQL Server Connector requires the following information:

Connector Name	SQL
Connector ID	AC103458-FCB6-41D3-94A0-43D25B4F4FF4

TIBCO Cloud™ Integration - Connect Connection Properties

In addition, this Connector uses the Connection properties shown in the following table.

Note: Connection property names are case-sensitive.

Name	Data Type	Required	Secured	Usage
Server	String	Yes	No
Database	String	Yes	No
UserName	String	Yes	No
Password	String	Yes	Yes
WindowsAuthentication	String	Yes	No	Supported values: true false
ExtendedConnectionProperties	String	No	No

More Information

For additional information on this Connector, refer to the Knowledge Base and Discussions in the TIBCO Community.