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. Prototype code for passing through domain logins is available, but please contact Heimdall support if this is required as well.

• 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.

• 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.

• Applications that depend on session recovery are likely to not work properly, in particular if session data is used as part of record/table locking mechanisms, such as the SP "applock_mode" with the lock owner of "session". One example of a library that uses this is "Hangfire".

• Applications that require MARS (multiple active result sets) may not work properly. The proxy will actively negotiate this option to be off with clients, which normally is sufficient to allow the applications to work. In some rare cases however, code needs to be modified to read one full result-set into memory before opening a new result-set.

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