Environment Structure: Multiple PB Servers with a Shared SQL Server

Scenario Overview

This setup involves multiple PB Servers accessing a single shared SQL Server. This configuration is beneficial for managing distributed workloads while centralizing data storage and processing. Each PB Server relies on SQL Server stored procedures to handle operations consistently across all servers, preventing conflicts and ensuring efficient resource management.

In this environment, it’s essential to utilize several dedicated SQL stored procedures to manage IDs and transactions across the PB servers. This ensures that each server can generate unique identifiers and manage resources without data collision.

Environment Structure Diagram

The following diagram illustrates the connection between multiple PB servers and the shared SQL Server:

Required SQL Stored Procedures

Below are the necessary SQL stored procedures used in this configuration, which are executed to generate unique identifiers across PB Servers:

1. Stored Procedure: sp_getnextjobid

USE [PrintBOS]
GO
-- Description: Get next job ID
CREATE PROCEDURE [dbo].[sp_getnextjobid] AS
BEGIN
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    BEGIN TRANSACTION;
    BEGIN TRY
        DECLARE @result int;
        EXEC @result = sp_getapplock @Resource = 'getnextjobid', @LockMode = 'Exclusive', @DbPrincipal = 'dbo';
        IF @result = -3
            BEGIN
                -- Handle lock failure
                SELECT CAST(SUBSTRING(CAST(CAST(599266080000000000 + (864000000000 * CAST(GETDATE() AS FLOAT)) AS BIGINT) AS VARCHAR(20)), 10, 20) AS INT)
                ROLLBACK TRANSACTION;
            END
        ELSE
            BEGIN
                UPDATE AutoNumbers SET AutoNumbers.JobId = AutoNumbers.JobId + 1;
                SELECT AutoNumbers.JobId FROM AutoNumbers;
                COMMIT TRANSACTION;
            END
    END TRY
    BEGIN CATCH
        -- Handle error
    END CATCH
    EXEC @result = sp_releaseapplock @Resource = 'getnextjobid';
END
GO
    

2. Stored Procedure: sp_getnextadvpackid

USE [PrintBOS]
GO
-- Description: Get next advanced package ID
CREATE PROCEDURE [dbo].[sp_getnextadvpackid] AS
BEGIN
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    BEGIN TRANSACTION;
    BEGIN TRY
        DECLARE @result int;
        EXEC @result = sp_getapplock @Resource = 'getnextadvpackid', @LockMode = 'Exclusive', @DbPrincipal = 'dbo';
        IF @result = -3
            BEGIN
                -- Handle lock failure
                SELECT CAST(SUBSTRING(CAST(CAST(599266080000000000 + (864000000000 * CAST(GETDATE() AS FLOAT)) AS BIGINT) AS VARCHAR(20)), 10, 20) AS INT)
                ROLLBACK TRANSACTION;
            END
        ELSE
            BEGIN
                UPDATE AutoNumbers SET AutoNumbers.AdvPackageId = AutoNumbers.AdvPackageId + 1;
                SELECT AutoNumbers.AdvPackageId FROM AutoNumbers;
                COMMIT TRANSACTION;
            END
    END TRY
    BEGIN CATCH
        -- Handle error
    END CATCH
    EXEC @result = sp_releaseapplock @Resource = 'getnextadvpackid';
END
GO
    

3. Stored Procedure: sp_getnextadvjobid

USE [PrintBOS]
GO
-- Description: Get next advanced job ID
CREATE PROCEDURE [dbo].[sp_getnextadvjobid] AS
BEGIN
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    BEGIN TRANSACTION;
    BEGIN TRY
        DECLARE @result int;
        EXEC @result = sp_getapplock @Resource = 'getnextadvjobid', @LockMode = 'Exclusive', @DbPrincipal = 'dbo';
        IF @result = -3
            BEGIN
                -- Handle lock failure
                SELECT CAST(SUBSTRING(CAST(CAST(599266080000000000 + (864000000000 * CAST(GETDATE() AS FLOAT)) AS BIGINT) AS VARCHAR(20)), 10, 20) AS INT)
                ROLLBACK TRANSACTION;
            END
        ELSE
            BEGIN
                UPDATE AutoNumbers SET AutoNumbers.AdvJobId = AutoNumbers.AdvJobId + 1;
                SELECT AutoNumbers.AdvJobId FROM AutoNumbers;
                COMMIT TRANSACTION;
            END
    END TRY
    BEGIN CATCH
        -- Handle error
    END CATCH
    EXEC @result = sp_releaseapplock @Resource = 'getnextadvjobid';
END
GO
    

SQLite Configuration Parameters for PBAppParams.IDB

The following SQLite script configures necessary parameters in the file:PBAppParams.IDB

Script:

DELETE FROM SQL_PARAMS WHERE (SECTION_NAME_S255 = 'SQL' AND VALUE_NAME_S255 = '2001');
DELETE FROM SQL_PARAMS WHERE (SECTION_NAME_S255 = 'SQL' AND VALUE_NAME_S255 = '2002');
DELETE FROM SQL_PARAMS WHERE (SECTION_NAME_S255 = 'SQL' AND VALUE_NAME_S255 = '2037');
DELETE FROM SQL_PARAMS WHERE (SECTION_NAME_S255 = 'SQL' AND VALUE_NAME_S255 = '2038');
DELETE FROM SQL_PARAMS WHERE (SECTION_NAME_S255 = 'SQL' AND VALUE_NAME_S255 = '2040');
DELETE FROM SQL_PARAMS WHERE (SECTION_NAME_S255 = 'SQL' AND VALUE_NAME_S255 = '2041');

INSERT INTO SQL_PARAMS (SECTION_NAME_S255, VALUE_NAME_S255, VALUE_S2048) VALUES ('SQL', '2001', NULL);
INSERT INTO SQL_PARAMS (SECTION_NAME_S255, VALUE_NAME_S255, VALUE_S2048) VALUES ('SQL', '2002', 'EXEC |QUOTE_S||SCHEMA_NAME||QUOTE_E|.|QUOTE_S|sp_getnextjobid|QUOTE_E|');
INSERT INTO SQL_PARAMS (SECTION_NAME_S255, VALUE_NAME_S255, VALUE_S2048) VALUES ('SQL', '2037', NULL);
INSERT INTO SQL_PARAMS (SECTION_NAME_S255, VALUE_NAME_S255, VALUE_S2048) VALUES ('SQL', '2038', 'EXEC |QUOTE_S||SCHEMA_NAME||QUOTE_E|.|QUOTE_S|sp_getnextadvjobid|QUOTE_E|');
INSERT INTO SQL_PARAMS (SECTION_NAME_S255, VALUE_NAME_S255, VALUE_S2048) VALUES ('SQL', '2040', NULL);
INSERT INTO SQL_PARAMS (SECTION_NAME_S255, VALUE_NAME_S255, VALUE_S2048) VALUES ('SQL', '2041', 'EXEC |QUOTE_S||SCHEMA_NAME||QUOTE_E|.|QUOTE_S|sp_getnextadvpackid|QUOTE_E|');
    

Conclusion

By structuring the environment with multiple PB Servers accessing a shared SQL Server and utilizing dedicated stored procedures, you can ensure consistent ID generation and efficient resource management. This setup reduces data conflicts and centralizes key operations, making it ideal for distributed workloads.