This topic describes how to create a Logtail configuration in the Log Service console to collect SQL Server query results.

Prerequisites

  • Logtail V1.2.1 or later is installed on your Linux server. For more information, see Install Logtail on a Linux server.
    Important Make sure that the server on which Logtail is installed can connect to the database from which you want to collect SQL Server query results.
  • The IP address of the server on which Logtail is installed is added to a whitelist of the SQL Server database from which you want to collect SQL Server query results.

    For more information about how to configure an IP address whitelist for an ApsaraDB RDS for SQL Server instance, see Configure an IP address whitelist for an ApsaraDB RDS for SQL Server instance.

Principle

Logtail executes the SELECT statement that is specified in a Logtail configuration on a regular basis, and then uploads the query results to Log Service.

After Logtail obtains query results, Logtail saves the value of the CheckPoint field in the results to the Logtail server. The next time Logtail executes the SELECT statement, Logtail adds the value of the CheckPoint field to the SELECT statement. This way, Logtail can collect incremental data.

Important If you use checkpoints during data collection, you must sort the values of the CheckPoint field in the SELECT statement. Otherwise, data duplication or other issues may occur.
Principle

Features

  • You can collect query results from SQL Server databases.
  • You can configure paged query settings.
  • You can specify time zones.
  • You can specify timeout periods.
  • You can save the values of the CheckPoint field.
  • You can specify the maximum number of logs that can be collected at a time.

Scenarios

  • Collect incremental data based on marks such as an auto-increment ID or a point in time.
  • Synchronize data based on filter conditions.

Procedure

  1. Log on to the Log Service console.
  2. In the Import Data section, select Custom Data Plug-in.
  3. Select the project and Logstore. Then, click Next.
  4. In the Create Machine Group step, create a machine group.
    • If a machine group is available, click Use Existing Machine Groups.
    • If no machine groups are available, perform the following steps to create a machine group. In this example, an Elastic Compute Service (ECS) instance is used.
      1. On the ECS Instances tab, select Manually Select Instances. Then, select the ECS instance that you want to use and click Create.

        For more information, see Install Logtail on ECS instances.

        Important If you want to collect logs from an ECS instance that belongs to a different Alibaba Cloud account, a server in an on-premises data center, or a server of a third-party cloud service provider, you must manually install Logtail. For more information, see Install Logtail on a Linux server. After you manually install Logtail, you must configure a user identifier for the server. For more information, see Configure a user identifier.
      2. After Logtail is installed, click Complete Installation.
      3. In the Create Machine Group step, configure the Name parameter and click Next.

        Log Service allows you to create IP address-based machine groups and custom identifier-based machine groups. For more information, see Create an IP address-based machine group and Create a custom identifier-based machine group.

  5. Select the new machine group from Source Server Groups and move the machine group to Applied Server Groups. Then, click Next.
    Important If you apply a machine group immediately after you create the machine group, the heartbeat status of the machine group may be FAIL. This issue occurs because the machine group is not connected to Log Service. To resolve this issue, you can click Automatic Retry. If the issue persists, see What do I do if no heartbeat connections are detected on Logtail?
  6. In the Specify Data Source step, configure the Config Name and Plug-in Config parameters. Then, click Next.
    • inputs specifies the collection configurations of your data source. This parameter is required.
      Important You can specify only one type of data source in the inputs parameter.
    • processors specifies the processing configurations that are used to parse data. You can extract fields, extract log time, desensitize data, and filter logs. This parameter is optional. You can specify one or more processing methods. For more information, see Overview. 
    {
  "inputs": [
    {
      "type": "service_mssql",
      "detail": {
        "Address": "rm-****.sqlserver.rds.aliyuncs.com",
        "User": "****",
        "Password": "*******",
        "DataBase": "****",
        "Limit": true,
        "PageSize": 100,
        "StateMent": "select * from LogtailTestTable where id > ? order by id",
        "CheckPoint": true,
        "CheckPointColumn": "id",
        "CheckPointStart": "0",
        "CheckPointSavePerPage": true,
        "CheckPointColumnType": "int",
        "IntervalMs": 1000
      }
    }
  ]
}
    ParameterTypeRequiredDescription
    typestringYesThe type of the data source. Set the value to service_mssql.
    AddressstringNoThe address of the SQL Server database. Example: rm-****.sqlserver.rds.aliyuncs.com.

    Default value: 127.0.0.1:1433.

    UserstringNoThe username of the account that is used to log on to the SQL Server database.

    Default value: root.

    PasswordstringNoThe password of the account that is used to log on to the SQL Server database. This parameter is empty by default.
    If you have high requirements for data security, we recommend that you set the username and password to xxx. After your configurations are synchronized to the Logtail server, find the User and Password parameters in the /usr/local/ilogtail/user_log_config.json file and change the values based on your business requirements. For more information, see Modify the Logtail configuration on the Logtail server.
    Important If you modify this parameter in the Log Service console, the parameter setting in the Logtail configuration on the Logtail server is overwritten after the modification is synchronized to the server.
    DataBasestringNoThe name of the SQL Server database.
    DialTimeOutMsintNoThe timeout period for connections to the SQL Server database. Unit: milliseconds.

    Default value: 5000.

    ReadTimeOutMsintNoThe timeout period for reads from the SQL Server database. Unit: milliseconds.

    Default value: 5000.

    StateMentstringNoThe SELECT statement.

    If you set the CheckPoint parameter to true, you must include the column specified by the CheckPointColumn parameter in a WHERE clause of the SELECT statement that you specify for the StateMent parameter. You must also set the value for the column to a question mark (?). For example, if you set the CheckPointColumn parameter to id, you must specify the value of the StateMent parameter in the SELECT * from ... where id > ? format.

    LimitbooleanNoSpecifies whether to use a LIMIT clause to paginate query results.

    Default value: false. This value indicates that no LIMIT clause is used.

    We recommend that you set the Limit parameter to true. If you set the Limit parameter to true, a LIMIT clause is automatically added to the SQL statement that you specify for the StateMent parameter when Logtail executes the SQL statement.

    PageSizeintNoThe maximum number of logs that can be returned on each page. If you set the Limit parameter to true, you must configure this parameter.
    MaxSyncSizeintNoThe maximum number of logs that can be synchronized at a time.

    Default value: 0. This value indicates that the number is unlimited.

    CheckPointbooleanNoSpecifies whether to use checkpoints during data collection.

    Default value: false. This value indicates that no checkpoint is used.

    CheckPointColumnstringNoThe name of the column that stores checkpoints. The column is also referred to as the checkpoint column.
    If you set the CheckPoint parameter to true, you must configure this parameter.
    Warning Values in the checkpoint column must be incremental. Otherwise, some data may not be collected. The maximum value in the results of a query operation is used as the input for the next query operation.
    CheckPointColumnTypestringNoThe type of the checkpoint column. Valid values: int and time. If you set this parameter to int, the values in the checkpoint column are of the int64 type. If you set this parameter to time, the values in the checkpoint column can be of the date, datetime, or time type that is supported by SQL Server.

    If you set the CheckPoint parameter to true, you must configure this parameter.

    CheckPointStartstringNoThe initial value of the checkpoint column.

    If you set the CheckPoint parameter to true, you must configure this parameter.

    CheckPointSavePerPagebooleanNoSpecifies whether to save checkpoints each time query results are paginated. Valid values:
    • true: saves checkpoints each time query results are paginated.
    • false: saves checkpoints each time query results are synchronized.
    IntervalMsintYesThe synchronization interval. Unit: milliseconds.
  7. Preview data, configure indexes, and then click Next.
    By default, full-text indexing is enabled for Log Service. You can also configure field indexes based on collected logs in manual mode or automatic mode. To configure field indexes in automatic mode, click Automatic Index Generation. This way, Log Service automatically creates field indexes. For more information, see Create indexes.
    Important If you want to query and analyze logs, you must enable full-text indexing or field indexing. If you enable both full-text indexing and field indexing, the system uses only field indexes.
  8. Click Log Query. You are redirected to the query and analysis page of your Logstore.
    You must wait approximately 1 minute for the indexes to take effect. Then, you can view the collected logs on the Raw Logs tab. For more information, see Query and analyze logs.

Modify the Logtail configuration on the Logtail server

If you do not enter real information for parameters such as Address, User, and Password in Plug-in Config when you create a Logtail configuration, you can modify the parameters after the Logtail configuration is delivered to the Logtail server.

  1. Log on to the Logtail server.
  2. Find the service_mssql keyword in the /usr/local/ilogtail/user_log_config.json file and modify parameters such as Address, User, and Password.
  3. Run the following command to restart Logtail: 
    sudo /etc/init.d/ilogtaild stop; sudo /etc/init.d/ilogtaild start

Troubleshooting

If no data is displayed on the preview page or query page after logs are collected by using Logtail, you can troubleshoot the errors based on the instructions that are provided in What do I do if errors occur when I use Logtail to collect logs?

Sample database table and sample logs

This section provides a sample database table of an SQL Server database and sample logs that are collected by Logtail.
  • Sample database table
    IF NOT EXISTS(SELECT * FROM sys.databases WHERE name = 'LogtailTest')
BEGIN
CREATE DATABASE [LogtailTest]
END
GO
USE [LogtailTest]
GO

IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='LogtailTestTable' and xtype='U')
BEGIN
CREATE TABLE LogtailTestTable (
      id INT PRIMARY KEY IDENTITY (1, 1),
      name NVARCHAR(50),
      quantity INT
    )
END
GO

INSERT INTO LogtailTestTable (name, quantity) values('banana', 1);
INSERT INTO LogtailTestTable (name, quantity) values('banana', 2);
INSERT INTO LogtailTestTable (name, quantity) values('banana', 3);
INSERT INTO LogtailTestTable (name, quantity) values('banana', 4);
SELECT * FROM LogtailTestTable;
GO
  • Sample logs Sample logs