For JDBC and ODBC connections, Hybrid Data Pipeline supports failover when an Azure SQL Database is used as an external system database.The primary database can be replicated as a failover database in another Azure region or to another logical server.

Setting up failover for the Azure SQL system database

To set up failover, do the following.
  • Create a primary database in Azure. This is the database to be used as the Hybrid Data Pipeline system database. It must be available to the user deploying Hybrid Data Pipeline.
  • Create a schema in the primary database. See the example in Create a schema in the primary database.
  • Create a failover database in Azure. Follow the steps in Create a failover database in the Azure documentation. Note that the login credentials and firewall settings must be the same as those used in the primary database.
  • Deploy Hybrid Data Pipeline with the following configurations:
    • Select Microsoft SQL Server database as the external database.
    • For the hostname, specify the Read/write listener endpoint of your Azure SQL failover group.
  • Test failover by following the steps in Test planned failover in the Azure documentation.
Note: For more details on Azure SQL Database failover, refer to Failover groups overview and best practices (Azure SQL Database) in the Azure documentation.

During failover, when nodes transition from primary to secondary, Azure may temporarily set the nodes to READ_ONLY mode. In this scenario, the connection remains active, but INSERT, UPDATE, and DELETE operations are unsuccessful. To reduce the likelihood of unsuccessful operations during failover, Hybrid Data Pipeline pauses the connection for 30 seconds before executing SQL operations.

Create a schema in the primary database

The following example provides SQL commands for creating a Hybrid Data Pipeline schema and users in a primary database called HDPSystemDB.
Note: The schema name and user credentials you create must be provided during installation or deployment of Hybrid Data Pipeline.

USE HDPSystemDB
CREATE SCHEMA hdp_schema;
GO

CREATE USER hdp_admin WITH PASSWORD = 'A@aa1AaA1!';
ALTER USER hdp_admin WITH DEFAULT_SCHEMA = hdp_schema;
GO

GRANT CREATE TABLE TO hdp_admin;
GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE, ALTER ON SCHEMA :: hdp_schema TO hdp_admin;
ALTER AUTHORIZATION ON SCHEMA :: hdp_schema TO hdp_admin;
GO

CREATE USER hdp_user WITH PASSWORD = 'B@bb1BbB1!';
ALTER USER hdp_user WITH DEFAULT_SCHEMA = hdp_schema;
GO

GRANT CONNECT TO hdp_user;
GO

For more information on contained databases, refer to Contained database user model in the Azure documentation.