Condividire...

Condividere ha il suo costo, ma alla fine finisce per arricchire tutti


Sharing has its cost, but at the end leads everyone to a better knowledge

venerdì 6 maggio 2016

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.

mercoledì 10 febbraio 2016

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)






venerdì 26 giugno 2015

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