如何配置Jenkins将作业构建数据同步插入两个不同数据库？

Hey there! Let me walk you through how to set up Jenkins to push build status, start/end times, and other critical build data to two separate MSSQL Server databases. Here's a practical, step-by-step breakdown:

Before diving into configuration, make sure you have these covered:

• Network access: Ensure your Jenkins server can reach both MSSQL instances (firewall ports 1433 open, DNS resolves correctly).
• Database permissions: Create dedicated SQL accounts with INSERT permissions on both target databases (for writing build logs).
• Jenkins plugins & drivers:
  • Install the Database Plugin via Jenkins' Plugin Manager (Manage Jenkins > Plugins).
  • Download the latest MSSQL JDBC driver (e.g., mssql-jdbc-12.4.2.jre8.jar) and place it in your Jenkins WEB-INF/lib directory. Restart Jenkins to load the driver.

First, set up connections to both MSSQL databases:

1. Go to Manage Jenkins > Configure System.
2. Scroll to the Database section and click Add Database.
   • Name: Give it a clear label (e.g., PrimaryMSSQL-BuildLogs).
   • Database Type: Select Microsoft SQL Server.
   • JDBC URL: Use the format jdbc:sqlserver://<DB_HOST>:1433;databaseName=<DB_NAME>;encrypt=true;trustServerCertificate=true; (adjust encryption settings per your security policy).
   • Username/Password: Enter the dedicated SQL account credentials you created.
   • Click Test Connection to confirm connectivity works.
3. Repeat this process to add the second database connection (e.g., SecondaryMSSQL-BuildLogs).

Choose the method that fits your Jenkins project type:

Method 1: For Freestyle Projects (Post-build Actions) #

If you're using a freestyle job, use post-build actions to trigger SQL inserts:

1. Open your job's configuration page.
2. Scroll to Post-build Actions and click Add post-build action > Execute SQL script.
   • Select your primary database connection (PrimaryMSSQL-BuildLogs).
   • Write your INSERT query using Jenkins environment variables to populate build data. Example:

     INSERT INTO BuildLogs (JobName, BuildNumber, BuildStatus, StartTime, EndTime)
     VALUES ('${JOB_NAME}', ${BUILD_NUMBER}, '${BUILD_STATUS}', '${BUILD_TIMESTAMP}', GETDATE())
     

     Note: Map Jenkins variables to your database table structure. Use CASE statements if you need to convert Jenkins status strings (SUCCESS/FAILURE) to numeric values.
3. Add a second Execute SQL script post-build action, select the secondary database connection, and use the same (or adjusted) INSERT query for the second database.
4. Check Run regardless of build result if you want logs to be written even when builds fail.

Method 2: For Pipeline Projects (Flexible Scripting) #

Pipeline gives you more control over error handling and data formatting. Here's a sample script:

pipeline {
    agent any
    stages {
        stage('Build Application') {
            steps {
                echo 'Running build steps...'
                // Add your actual build commands here (e.g., mvn clean install)
            }
        }
    }
    post {
        always {
            script {
                // Reusable function to insert build data into a database
                def pushToDB(String dbUrl, String credsId) {
                    def dbCreds = credentials(credsId)
                    def sqlInstance = groovy.sql.Sql.newInstance(
                        dbUrl,
                        dbCreds.username,
                        dbCreds.password,
                        'com.microsoft.sqlserver.jdbc.SQLServerDriver'
                    )
                    try {
                        sqlInstance.execute("""
                            INSERT INTO BuildLogs (JobName, BuildNumber, BuildStatus, StartTime, EndTime)
                            VALUES (?, ?, ?, ?, ?)
                        """, [
                            env.JOB_NAME,
                            env.BUILD_NUMBER.toInteger(),
                            env.BUILD_STATUS,
                            new Date(Long.parseLong(env.BUILD_TIMESTAMP)),
                            new Date()
                        ])
                        echo "Successfully logged build to target database"
                    } catch (Exception e) {
                        echo "Failed to write build log: ${e.message}"
                        // Optional: Uncomment below to fail the build if DB insert fails
                        // throw e
                    } finally {
                        sqlInstance.close()
                    }
                }

                // Push to primary database (use your JDBC URL and credential ID)
                pushToDB(
                    'jdbc:sqlserver://primary-db:1433;databaseName=BuildLogsDB;encrypt=true;trustServerCertificate=true;',
                    'mssql-primary-creds'
                )

                // Push to secondary database
                pushToDB(
                    'jdbc:sqlserver://secondary-db:1433;databaseName=BackupBuildLogs;encrypt=true;trustServerCertificate=true;',
                    'mssql-secondary-creds'
                )
            }
        }
    }
}

Notes: Store your database credentials in Jenkins' Credentials Manager (Manage Jenkins > Manage Credentials) and reference them via their ID. Adjust the INSERT query to match your table schema.

• Trigger a test build in Jenkins.
• Query both MSSQL databases' BuildLogs tables to confirm the build data was inserted.
• Check the Jenkins build log for any error messages if inserts fail (e.g., permission issues, typos in SQL).

If you don't have the target table yet, run this SQL in both databases:

CREATE TABLE BuildLogs (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    JobName NVARCHAR(255) NOT NULL,
    BuildNumber INT NOT NULL,
    BuildStatus NVARCHAR(50) NOT NULL,
    StartTime DATETIME NOT NULL,
    EndTime DATETIME NOT NULL,
    CreatedAt DATETIME DEFAULT GETDATE()
)

内容的提问来源于stack exchange，提问作者Hi_Dhinesh