Microsoft SQL Server Specific Information

• Currently, the SQL Server proxy only supports SQL Server logins. If the proxy is running on Windows, it is possible to configure internal authentication on the SQL Server JDBC driver. Please contact support for more details if this is necessary.

• When using the Microsoft Azure Database, the setCatalog() function is not usable to change the catalog on a connection. To support an application selecting the database on the initial connection, use the string ${database} in the JDBC URL to set the database, i.e. ";databaseName=${database}" at the end of the url. The value ${database} will be extracted from the client login to populate this value. Since connection pooling will not know what value was used for the database, please insure database pooling is disabled if using this feature.

• When performing explain extraction, the option checkTableMismatch can be used (any value) to trigger testing and printing if a the tables extracted from the explain match what is used internally by Heimdall. This can be used to track down table extraction issues.

• If using Mirroring (not Always-On) for redundancy, transactions will fail. This can apply if using SQL Server in AWS for versions prior to 2016, or older instances spun up before May 2019 with failover. Please use Always-On, as transaction handling with mirroring is not easily supported.

Schema Assist for Dependencies

When using SQL Server, parsing is performed to detect view dependencies along with fixed (not dynamic) dependencies in stored procedures, in order to automate invalidation. This is only performed at proxy start at this time.

Trigger Invalidation with SQL Server

Connection properties:

• dbTriggerTracking=true
• dbChanges=select ts,name from heimdall.table_change where lastupdate > @ts
• dbTimeMs=select heimdall.get_time_ms()

CREATE DATABASE [heimdall]
GO

/*** create the trigger tracking table ***/
USE [heimdall]
CREATE TABLE [dbo].[table_change](
    [ts] [bigint] NULL,
    [name] [varchar](64) NOT NULL,
    CONSTRAINT [PK_table_change] PRIMARY KEY ([name]));

CREATE FUNCTION [dbo].[get_time_ms]() RETURNS BIGINT
AS
BEGIN
DECLARE @timems BIGINT
set @timems= (SELECT cast(DATEDIFF(s, '1970-01-01 00:00:00', GETUTCDATE()) as BIGINT)*1000+cast(DATEDIFF(ms,CONVERT(VARCHAR,GETUTCDATE(),120),GETUTCDATE()) as BIGINT))
return @timems
END

/*** Create the tracking procedure, which will be used by the triggers ***/
CREATE OR ALTER PROCEDURE [heimdall].[dbo].[track_changes] 
    @tablename NVARCHAR(128)
as
    SET NOCOUNT OFF
    UPDATE [heimdall].[dbo].[table_change] SET ts=[heimdall].[dbo].get_time_ms() WHERE name = @tablename
    IF @@ROWCOUNT != 0
        RETURN
    INSERT INTO [heimdall].[dbo].[table_change] VALUES ( dbo.get_time_ms(), @tablename )
GO

/*** trigger to apply to each table that needs trigger based eviction ***/
/**** for table vevo.dbo.Product ***/
USE [vevo2]; CREATE OR ALTER TRIGGER [dbo].[Product_changed]
    ON [vevo2].[dbo].[Product]
    AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    exec [heimdall].[dbo].[track_changes] 'vevo2.dbo.Product'
END
GO