Some Considerations about SQL Server editions and pricing

Firts of all let me say I definitively love SQL Server and I'm really impressed about the product technical improvements in the last years, including the stunning 2016 Version. But at the same time I am worried about the SQL Server  market positioning on non-enterprise customers.
Standard Edition price is growing too much, by my opinion.
The free SQL Server Express edition is really too limited in terms of resources to work as a company central repository; Standard Edition can do this job well (altough the 128 GB RAM limitation can easly become a bottleneck), but, in many cases, affording a 10K licences price + the SA fee really becomes a serious obstacle  to the product adoption.
Please take in consideration that there are millions of small customers, using only SQL Server standard features as backend for their ERP, accounting software, stock management and so on, that your pricing strategy is pushing away (and this is not only true for SQL Server). 
So, although I am quite sure that my words will be lost in the wind, I would like to give some non-technical suggestion:
1) In SQL Server 2012 MS has introduced the BI Edition, why don’t cut off the Standard Edition  price as well as the BI features?
2) MS is still selling SQL Server as a product (license) and as a service (Software Assurance); now is giving free licenses to customers migrating from Oracle, but they still have to pay the service (SA). Why don't do a step forward and decide to sell the whole product, for all customers, as a service eliminating the initial licence price?
3) MS is already selling SQL Server nearly as a as a service in contexts like SPLAs,Enterprise Agreements and, of course, Azure
4) By my opinion Software Assurance is creating problems to both MS and Customers: MS is quite obligated to release a new Version every 2 years, and customers have to afford upgrading side costs; I think that adopting a more relaxed versioning can simpify the life to both parts
5) Software Assurance covers the whole product price in about 3 years, please consider the benefits of a costant lifetime entroy and don't be so aggressive in terms of fees
6) In the next years a lot of customers will move to cloud, Azure fee can be seen as a Computing service + a Software Service fee, now if your clients are already paying a monthly SQL Server fee it would be very easy to move from/to the public cloud to/from the private cloud simply adding or removing the computing fee
Other SQL Server experts at the same time are complaining about the Standard Editions features limitations.    
I think you have really developed a wonderfull product, but at the some time a discutable marketing strategy, that is positioning your product as an Enterprise solution even for the entry level editions; I hope I shouldn’t reskill myself on PostGress in the next years.

SQL Server: Audit Security events

Few days ago a customer asked me to implement a security auditing solution for their production instances of SQL Server, to track users and logins management events. I immediately thought about the native SQL Audit function, but there was a problem, all users gain access to SQL Server with the same login, so to identify exactly who I had to capture the hostname field; unfortunately, the available fields in the SQL Server audits are predefined and not mdifiable (at least not easily) and among them the hostname is missing.
At that point I decided to use DDL triggers, which unlike the DML, have not a significant impact in terms of performances.
First of all we have to understand which events intercept, using this query you can have a hierarchical view of available events:

WITH EventsReports (name, PARENT_TYPE, type, level, sort) AS
(
    SELECT CONVERT (varchar (255), type_name), PARENT_TYPE, type, 1, CONVERT (varchar (255), type_name)
    FROM sys.trigger_event_types 
    WHERE PARENT_TYPE  IS NULL
    UNION ALL
    SELECT CONVERT (varchar (255), REPLICATE ( '|', level) + e.type_name),
        e.parent_type, e.type, level + 1,
    CONVERT (varchar (255), RTRIM (sort) + '|' + e.type_name)
    FROM sys.trigger_event_types  as e
        INNER JOIN EventsReports as d
        ON  e.parent_type =  d.type
  )
SELECT PARENT_TYPE, type, name
FROM EventsReports
-- where [name] like '% object%'
ORDER BY sort;

The event name is quite explicative, but in the dubt has a look here

So we decided to track these events: DDL_DATABASE_SECURITY_EVENTS, DDL_DATABASE_EVENTS, DDL_SERVER_SECURITY_EVENTS

Then I have built the DDL trigger.

First let's create a DB and the table that will store our events:

Create DataBase AdminDB;
go
use AdminDB;
go
CREATE TABLE dbo.DDL_Security_Audit_LOG  ( EventId int NOT NULL IDENTITY (1, 1) PRIMARY KEY, EventTime datetime , DbName nvarchar(100), EventType nvarchar(100) , UserName nvarchar(100) ,HostName nvarchar (100) , EventTSQL nvarchar(3000) , Data_XML xml);


Then our DDL Trigger:


Create TRIGGER [DDL_Security_Audit]
ON ALL SERVER
FOR DDL_DATABASE_SECURITY_EVENTS, DDL_DATABASE_EVENTS, DDL_SERVER_SECURITY_EVENTS
AS
BEGIN

Declare @ObjectName nvarchar(100) =  EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(100)')

Declare @EventType nvarchar(100) =  EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)')

Declare @DbName  nvarchar(100) = isnull( EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(1000)'),'Master')

Declare @Command nvarchar(1000) =  EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(1000)')

INSERT INTO AdminDB.dbo.DDL_Security_Audit_LOG
 VALUES ( GETDATE() ,  @DbName, @EventType, suser_sname() , HOST_NAME(), @Command, EVENTDATA()  )

END
GO

Then remember to create a JOB to purge old data (if you want to)

Declare @CutOffDate datetime =  DATEDIFF(d, -30, getdate())
Delete from AdminDB.dbo.DDL_Audit_Security_TABLE_LOG where EventTime < @CutOffDate

Now you have all info you need to monitor your security events at Server and DataBase level.

Just notice in SQL Server there a security hole: no DDL events avalaible for disable Trigger action.

(Thanks to Alessandro, Matteo, Simone and "Rocco" for allowing me to publish this)

SQL Server: Asynchronous DML Triggers

Great solution using SQL Server Service Broker:
http://www.madeirasql.com/service-broker-asynchronous-triggers/

Use this version of SB_AT_HandleQueue (the original one doesn't work well):

ALTER PROCEDURE [dbo].[SB_AT_HandleQueue]

AS

       SET NOCOUNT ON;

       SET ARITHABORT ON

       DECLARE @msg XML

       DECLARE @DlgId UNIQUEIDENTIFIER

       DECLARE @Info nvarchar(max)

       DECLARE @ErrorsCount int

       SET @ErrorsCount = 0

       -- Set whether to log verbose status messages before and after each operation

       DECLARE @Verbose BIT = 1

      

       -- Allow 10 retries in case of service broker errors

       WHILE @ErrorsCount < 10

       BEGIN

            

             BEGIN TRANSACTION

             BEGIN TRY

                    -- Make sure queue is active

                    IF EXISTS (SELECT NULL FROM sys.service_queues

                                     WHERE NAME = 'SB_AT_Request_Queue'

                                     AND is_receive_enabled = 0)

                           begin

                             ALTER QUEUE SB_AT_Request_Queue WITH STATUS = ON;

                           end

                    -- handle one message at a time

                    WAITFOR

                    (

                           RECEIVE TOP(1)

                                  @msg         = convert(xml,message_body),

                                  @DlgId       = conversation_handle

                           FROM dbo.SB_AT_Request_Queue

                    );

                   

                    -- exit when waiting has been timed out

                    IF @@ROWCOUNT = 0

                    BEGIN

                           IF @@TRANCOUNT > 0

                           begin

                                  ROLLBACK TRANSACTION;

                                  BREAK;

                           end

                          

                    END

                   

                    -- Retreive data from xml message

                    DECLARE

                           @ProcedureName      VARCHAR(1000),

                           @inserted           XML,

                           @deleted            XML

                    

                    SELECT

                           @ProcedureName             = x.value('(/Request/ProcedureName)[1]','VARCHAR(1000)'),

                           @inserted                  = x.query('/Request/inserted/inserted'),

                           @deleted                   = x.query('/Request/deleted/deleted')

                    FROM @msg.nodes('/Request') AS T(X);

                   

                    -- Log operation start

                    IF @Verbose = 1

                    begin

                           INSERT INTO SB_AT_ServiceBrokerLogs(ErrorSeverity,ErrorMessage,QueueMessage)

                           VALUES(0,'Starting Process',@msg);

                    end

                    -- Encapsulate execution in TRY..CATCH

                    -- to catch errors in the specific request

                    BEGIN TRY

                   

                           -- Execute Request

                           EXEC @ProcedureName @inserted, @deleted;

                   

                    END TRY

                    BEGIN CATCH

                   

                           -- log operation fail

                           INSERT INTO SB_AT_ServiceBrokerLogs(ErrorSeverity,ErrorMessage,ErrorLine,ErrorProc,QueueMessage)

                           VALUES(ERROR_SEVERITY(),ERROR_MESSAGE(),ERROR_LINE(),ERROR_PROCEDURE(),@msg);

                          

                    END CATCH

                   

                    -- commit

                    IF @@TRANCOUNT > 0

                    begin

                           COMMIT TRANSACTION;

                    end

                    -- Log operation end

                    IF @Verbose = 1

                    begin

                           INSERT INTO SB_AT_ServiceBrokerLogs(ErrorSeverity,ErrorMessage,QueueMessage)

                           VALUES(0,'Finished Process',@msg);

                    end

                    -- reset xml message

                    SET @msg = NULL;

             END TRY

             BEGIN CATCH

            

                    -- rollback transaction

                    -- this will also rollback the extraction of the message from the queue

                    IF @@TRANCOUNT > 0

                    begin

                           ROLLBACK TRANSACTION;

                    end

                    -- log operation fail

                    INSERT INTO SB_AT_ServiceBrokerLogs(ErrorSeverity,ErrorMessage,ErrorLine,ErrorProc,QueueMessage)

                    VALUES(ERROR_SEVERITY(),ERROR_MESSAGE(),ERROR_LINE(),ERROR_PROCEDURE(),@msg);

                   

                    -- increase error counter

                    SET @ErrorsCount = @ErrorsCount + 1;

                   

                    -- wait 5 seconds before retrying

                    WAITFOR DELAY '00:00:05'

             END CATCH

      

       END