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:

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  

Note:  This Connector is available from the TIBCO Scribe® Online Marketplace. See Marketplace TIBCO Scribe® Certified 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:

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:

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 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.

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

TIBCO Scribe® Online Cloud Agent

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

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:
  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 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 Scribe® Online. Use the Additional Parameters and Object Filter fields on the Connection dialog for the Microsoft SQL Server Connector to specify the objects that should be returned from the database. See TIBCO Scribe® Online Connector For Microsoft SQL Server.

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.

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

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 Replication Services Solution is executed.

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.

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

Duplicate Records

Important: Modifying Microsoft SQL database table Index Properties can cause duplicate records during subsequent executions of an Replication Solution when Microsoft SQL is the target for a Replication Solution. 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.

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

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 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 Scribe® Online 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 IS Target

Consider the following when using the Cvent HData Connector as an Integration Solution target.

Batch Processing

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.

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.