Our software is a collection of Windows applications that connect to a SQL database. Currently all our client sites have their own server and SQL Server database, however I'm working on making our software work with Azure-hosted databases too.
I've hit one snag with it, and so far not found anything particularly helpful while Googling around.
The current SQL Server version includes a database auditing system I wrote, which does the following:-
The C# Applications include in the connection string information about which program and version it is, and which User is currently logged in.
Important tables have Update and Delete triggers, which send details of any changes to a Service Broker queue. (I don't log Inserts).
The Service Broker then goes through the queue, and records details of the change to a separate AuditLog table.
These details include:-
Table, PK of the row changed, Field, Old Value, New Value, whether it was an Update or Delete, date/time of change, UserID of the user logged in to our software, and which program and version made the change.
This all works very nicely, and I was hoping to keep the system as-is for the Azure version, but unfortunately SQL Azure does not have Service Broker.
So, I need to look for an alternative, which as I mentioned is proving troublesome.
There is SQL Azure Managed Instances, which does have Service Broker, however they are way too expensive for us to even consider. Not one of our clients would pay that much per month.
Anything else I've looked at doesn't seem to have everything I need. In particular, logging which program, version and UserID. Note that this isn't the SQL login UserID, which will be the same for everyone, this is the ID from the Users table with which they log in to our software, and is passed in the Connection String.
So, ideally I'd like something similar to what I have, just with something else in the place of the Service Broker:-
The C# Applications include in the connection string information about which program and version it is, and which User is currently logged in.
Important tables have Update and Delete triggers, which send details of any changes to an asynchronous queue of some sort.
Something then goes through the queue outside the normal program flow, and records details of the change to a separate AuditLog table.
The asynchronous queue and processing outside the normal program flow is important. Obviously I could very easily have the Update and Delete triggers do all the processing and add the records to the AuditLog table, in fact that was v1.0 of the system, but the problem there is that SQL will wait until the triggers have finished before returning to the C# program. This then causes the C# program to slow down considerably when multiple Updates or Deletes are happening.
I'd be happy to look into other logging systems instead of the above, however something which only records data changes without the extra information I pass, specifically program, version and UserID, won't be of any use to me. Our Users always want to know this information whenever they query something they think is an incorrect change.
So, any suggestions for an alternative to Service Broker for SQL Azure please? TIA!
Ok, looks like I have a potential solution: Temporal Tables
Temporal Tables work in Azure, and record a new row in a History table whenever something changes:-
CREATE TABLE dbo.LMSTemporalTest
(
[EmployeeID] INT NOT NULL PRIMARY KEY CLUSTERED
, [Name] NVARCHAR(100) NOT NULL
, [Position] NVARCHAR(100) NOT NULL
, [Department] NVARCHAR(100) NOT NULL
, [Address] NVARCHAR(1024) NOT NULL
, [AnnualSalary] DECIMAL (10,2) NOT NULL
, [UpdatedBy] UniqueIdentifier NOT NULL
, [UpdatedDate] DateTime NOT NULL
, [ValidFrom] DateTime2 (2) GENERATED ALWAYS AS ROW START HIDDEN
, [ValidTo] DateTime2 (2) GENERATED ALWAYS AS ROW END HIDDEN
, PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.LMSTemporalTestHistory));
GO
I can then insert a record into the table...
INSERT INTO LMSTemporalTest(EmployeeID,Name,Position,Department,Address,AnnualSalary, UpdatedBy, UpdatedDate)
VALUES(1, 'Bob', 'Builder', 'Fixers','Oops I forgot', 1, '0D7F5584-C79B-4044-87BD-034A770C4985', GetDate())
GO
Update the row...
UPDATE LMSTemporalTest SET
Address = 'Sunflower Valley, Bobsville',
UpdatedBy = '2C62290B-61A9-4B75-AACF-02B7A5EBFB80',
UpdatedDate = GetDate()
WHERE EmployeeID = 1
GO
Update the row again...
UPDATE LMSTemporalTest SET
AnnualSalary = 420.69,
UpdatedBy = '47F25135-35ED-4855-8050-046CD73E5A7D',
UpdatedDate = GetDate()WHERE EmployeeID = 1
GO
And then check the results:-
SELECT * FROM LMSTemporalTest
GO
EmployeeID Name Position Department Address AnnualSalary UpdatedBy UpdatedDate
1 Bob Builder Fixers Sunflower Valley, Bobsville 420.69 47F25135-35ED-4855-8050-046CD73E5A7D 2019-07-01 16:20:00.230
Note: Because I set them as Hidden, the Valid From and Valid To don't show up
Check the changes for a date / time range:-
SELECT * FROM LMSTemporalTest
FOR SYSTEM_TIME BETWEEN '2019-Jul-01 14:00' AND '2019-Jul-01 17:10'
WHERE EmployeeID = 1
ORDER BY ValidFrom;
GO
EmployeeID Name Position Department Address AnnualSalary UpdatedBy UpdatedDate
1 Bob Builder Fixers Oops I forgot 1.00 0D7F5584-C79B-4044-87BD-034A770C4985 2019-07-01 16:20:00.163
1 Bob Builder Fixers Sunflower Valley, Bobsville 1.00 2C62290B-61A9-4B75-AACF-02B7A5EBFB80 2019-07-01 16:20:00.197
1 Bob Builder Fixers Sunflower Valley, Bobsville 420.69 47F25135-35ED-4855-8050-046CD73E5A7D 2019-07-01 16:20:00.230
And I can even view the History table
SELECT * FROM LMSTemporalTestHistory
GO
EmployeeID Name Position Department Address AnnualSalary UpdatedBy UpdatedDate ValidFrom ValidTo
1 Bob Builder Fixers Oops I forgot 1.00 0D7F5584-C79B-4044-87BD-034A770C4985 2019-07-01 16:20:00.163 2019-07-01 16:20:00.16 2019-07-01 16:20:00.19
1 Bob Builder Fixers Sunflower Valley, Bobsville 1.00 2C62290B-61A9-4B75-AACF-02B7A5EBFB80 2019-07-01 16:20:00.197 2019-07-01 16:20:00.19 2019-07-01 16:20:00.22
Note: the current row doesn't show up, as it's still Valid
All of our important tables have CreatedBy, CreatedDate, UpdatedBy and UpdatedDate already, so I can use those for the UserID logging. No obvious way of handling the Program and Version as standard, but I can always add another hidden field and use Triggers to set that.
EDIT: Actually tested it out
First hurdle was: can you actually change an existing table into a Temporal Table, and the answer was: yes!
ALTER TABLE Clients ADD
[ValidFrom] DateTime2 (2) GENERATED ALWAYS AS ROW START HIDDEN NOT NULL DEFAULT '1753-01-01 00:00:00.000',
[ValidTo] DateTime2 (2) GENERATED ALWAYS AS ROW END HIDDEN NOT NULL DEFAULT '9999-12-31 23:59:59.997',
PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
GO
ALTER TABLE Clients SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.ClientsHistory))
GO
An important bit above is the defaults on the ValidFrom and ValidTo fields. It only works if ValidTo is the maximum value a DateTime2 can be, hence '9999-12-31 23:59:59.997'. ValidFrom doesn't seem to matter, so I set that to the minimum just to cover everything.
Ok, so I've converted a table, but it now has two extra fields that the non-Azure table doesn't, which are theoretically hidden, but will our software complain about them?
Seems not. Fired up the software, edited a record on the Clients table and saved it, and the software didn't complain at all.
Checked the Clients and ClientsHistory tables:-
SELECT * FROM Clients
FOR SYSTEM_TIME BETWEEN '1753-01-01 00:00:00.000' AND '9999-12-31 23:59:59.997'
WHERE sCAccountNo = '0001064'
ORDER BY ValidFrom
Shows two records, the original and the edited one, and the existing UpdatedUser and UpdatedDate fields show correctly so I know who made the change and when.
SELECT * FROM ClientsHistory
Shows the original record, with ValidTo set to the date of the change,
All seems good, now I just need to check that it still only returns the current record in queries and to our software:-
SELECT * FROM Clients
WHERE sCAccountNo = '0001064'
Just returned the one record, and doesn't show the HIDDEN fields, ValidFrom and ValidTo.
Did a search in our software for Client 0001064, and again it just returned the one record, and didn't complain about the two extra fields.
Still need to set up a few Triggers and add another HIDDEN field to record the program and version from the Connection String, but it looks like Temporal Tables gives me a viable audit option.
Only downside so far is that it creates an entire record row for each set of changes, meaning you have to compare it to other records to find out what changed, but I can write something to simplify that easily enough.