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 
    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
FROM EventsReports
-- where [name] like '% object%'
ORDER BY sort;

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


Then I have built the DDL trigger.

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

Create DataBase AdminDB;
use AdminDB;
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]

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()  )


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)