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

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 Scribe® Online users. Depending on the entities supported, a TIBCO Scribe® Online user could alter user accounts in the target system.

  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 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 Microsoft SQL Server 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.