Microsoft SQL Server
Visier can retrieve data through this connector if the following requirements are met.
Note: The Microsoft SQL Server connector can also be used to retrieve data from Azure SQL Database.
If table sources are enabled, you can select the source type during connector credential creation. Select one of:
- File sources: File-based containers that use regex and override behavior to manage data. For more information about acceptable files, see Data File Guidelines.
- Table sources: Row-based containers that support incremental updates and SQL transformations during sync. For more information, see Use SQL with Table Sources.
The following table describes the differences between the behavior of each source type.
|
File sources |
Table sources |
|---|---|
|
Each data transfer or extraction outputs to a unique file. |
Each data transfer or extraction directly modifies the same table. |
|
Use Visier Extraction Language to query records in a source's Records tab or SQL to query sources in the Data > Query room. For more information, see Query Source Data. |
Use Spark SQL to interact with the table data. For more information, see Use SQL with Table Sources. |
|
To delete an individual's data, such as for GDPR compliance, open a Visier Technical Support ticket to remove the information one-by-one from every extracted file. |
To delete an individual's data, such as for GDPR compliance, use a DELETE SQL query on the table without contacting Visier Technical Support. Note: The snapshot retention period specifies how long to keep previous snapshots of tables before permanently deleting them. You can set the retention period between 1 and 180 days. The default is 3 days. |
|
The data is available in the Data Transfers room. For more information, see Navigate Studio. |
The data is available in the associated table source in the Sources room. For more information, see Sources. |
|
Exclude or delete uploaded files individually. Files load in order by snapshot time. |
Use the Reset action to roll the table back to a particular timestamp. This rolls back all changes since the timestamp. You can't easily undo actions or apply changes out of order. Tables do not keep a complete history of every action. |
|
Optionally enable Snap to midnight setting to move the snapshot time to the start of the day. |
Actions are applied strictly in order. |
|
Files uploaded manually or by SFTP connect to the source using a file regex. |
Because you upload files directly into a table source, files uploaded manually in the Data Transfers room or by SFTP cannot connect to the source. |
You cannot change the source type after creating a credential. To use a different source type, create a new credential.
Prerequisites
- Create an integration user in Microsoft SQL Server.
- Allow network and data traffic between Visier and your source system. For more information, see Allow Visier Connections.
Integration user
Create an integration user in your source system and assign it the appropriate security access so that Visier can retrieve your source data through this user.
|
Access |
Description |
|---|---|
|
SELECT access |
Assign SELECT access to the tables you want Visier to retrieve. |
Connector credentials
Create a connector credential in Visier to authenticate and authorize access to your source system. Connector credentials can be created in Studio or through an API. For more information, see Set Up Data Connectors and "Create a connector credential" in API Reference. You will need to provide the following credentials in your connector credential configuration.
|
Credential |
Description |
Example |
|---|---|---|
|
Host name |
The host name of your server. |
sql-server.ewqancyhfdhb.us-west-2.rds.amazonaws.com |
|
Port |
Your server’s port. The default is 1433. |
1433 |
|
Username |
The integration user’s username. |
IntegrationUser |
|
Password |
The integration user’s password. |
Password1234 |
|
Database (Optional) |
The database to retrieve data from. If unspecified, Visier will retrieve data from all accessible databases. |
sample |
Data files
The Microsoft SQL Server data connector only retrieves data stored in SQL Server for use in Visier.
For other data, you must provide that data through alternative methods such as SFTP or Visier’s other data connectors. For more information about sending data to Visier, see File Upload.
How the connector loads data
Initially, data connectors retrieve a full history for each subject to generate an initial history of events. In each subsequent data retrieval, each connector handles the data load differently.
- Full load: All data from the source system is loaded into Visier.
- Delta load: Only changed data is loaded into Visier. A delta load may take either the entire history or a specific number of years of history for each changed record.
- Snapshot load: All data at a particular point in time. A snapshot load does not contain historical records or show changes over time.
- Partial load: A subset of data is loaded into Visier.
After the initial load, this connector only retrieves data for changed records.