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 Service.
- Replicate data from a Cloud based application into an on-premise version of Microsoft SQL Server using a Cloud Service.
- Use a Cloud Service to integrate Cloud based data, such as Salesforce, with Microsoft SQL Azure, which is a cloud based application.
- Use a Cloud Service 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 |
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:
- 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
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 Service 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 Service.
Note: If you use a Cloud Service with an on-premise datastore, you may need to whitelist the IP addresses of the Cloud Service managers to allow the Cloud Service to access your network. See Whitelisting Requirements.
TIBCO Scribe® Online Cloud Service
Requirements for using the TIBCO Scribe® Online Cloud Service with the Microsoft SQL Server Connector are as follows:
- Firewall rules must be updated to allow the Cloud Service 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
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.
- Select More > Connections from the menu.
- From the Connections page select Add
to open the Add a New Connection dialog.
- 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_??;
- 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 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.
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.
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:
- 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
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:
- 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 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
- 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 Service with an on-premise datastore, you may need to modify settings on the server where the datastore is located to allow the Cloud Service 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 |
|
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.