TIBCO Scribe® Online Connector For Microsoft SQL Server

Use the Microsoft SQL Server Connection as a source or target Connection for Integration and Migration Solutions, or as a target Connection for Replication.

Possible use cases for the Microsoft SQL Server Connector include:

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

Connector Specifications

	Supported
Agent Types
On Premise	X
Cloud	X
Replication Services
Source
Target	X
Integration Services
Source	X
Target	X
Migration Services
Source	X
Target	X
Maps
Integration	X
Request-Reply	X
Message

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 Scribe® Online requires a dedicated Microsoft SQL Server database for each data source you replicate. TIBCO Scribe® Online supports either Windows or Microsoft SQL Server authentication with the following versions of Microsoft SQL Server:

• Microsoft SQL Server 2019: Enterprise, Standard, and Express
• 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

Selecting An Agent Type For Microsoft SQL Server

This Connector supports both the Cloud Agent and the On-Premise Agent for the supported Solution 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 Scribe® Online On-Premise Agent or Provisioning A TIBCO Scribe® Online Cloud Agent.

TIBCO Scribe® Online Cloud Agent

Requirements for using the TIBCO Scribe® Online Cloud Agent with the Microsoft SQL Server Connector are as follows:

• Firewall rules must be updated to allow the Cloud Agent to access your network. See Whitelisting Requirements for a list of IP addresses.
• TIBCO recommends installing 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 Scribe® Online Microsoft SQL Server Connection dialog to encrypt the connection between TIBCO Scribe® Online and Microsoft SQL Server:

  Encrypt=true;TrustedServerCertificate=true;

  Refer to Microsoft's documentation on these parameters for additional information.

Connecting To Microsoft SQL Server

1. Select More > Connections from the menu.
2. From the Connections page select Add to open the Add a New Connection dialog.
3. Select the Connector from the drop-down list in the Connection Type field, 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_??;

4. 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.
5. Select OK/Save to save the Connection.

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 Scribe® Online 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 Scribe® Online only support one filter. For those Connectors you can use either Net Change or one filter on the Filter tab, not both.

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.

Naming

Connection metadata must have unique entity, relationship, and field names. If your Connection metadata has duplicate names, review the source system to determine if the duplicates can be renamed.

Microsoft SQL Server Connector As RS Target

Net Change

The first time a Replication Services Solution 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 Scribe® 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.

Deleted Records In Microsoft SQL Server RS Solutions

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.

Entities Without Modification Dates

Typically, entities without modification date fields are not included in the Recommended Entities list, but can be chosen using the Selected Entities option on the Replication Solution edit page. If a selected source entity does not have a date for tracking deleted records, when the Replication Solution executes, TIBCO Scribe® Online 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 Replication Solution 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

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.

TIBCO 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:

1. In Microsoft SQL Server, change the importdataidname column of the importlog table attribute from nvarchar(100) to ntext.
2. Remove the existing records from the importlog table to ensure that the TIBCO Scribe® Online Replication Solution 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

3. The next time the TIBCO Scribe® Online Replication Solution runs, the records should replicate correctly.

Goal Entity

See the TIBCO Scribe® Online Knowledge base 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 Scribe® Online Knowledge base 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 IS Target

Consider the following when using the Microsoft SQL Server Connector as an Integration Solution 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 Scribe® Online 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 Scribe® Online Agent. If you are using a Cloud Agent with an on-premise datastore, you may need to modify settings on the server where the datastore is located to allow the 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 Scribe® Online Agent that is running this Solution 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:

Login failed for user ‘DOMAIN\username’. The user is not associated with a trusted SQL Server connection.

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 Scribe® Online API Considerations

To create Connections with the TIBCO Scribe® Online API, the Microsoft SQL Server Connector requires the following information:

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

TIBCO Scribe® Online Connection Properties

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

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

License Agreement

The TIBCO Scribe® Online End User License Agreement for the SQL Connector describes TIBCO and your legal obligations and requirements. TIBCO suggests that you read the End User License Agreement.

More Information

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