SQL Server Always Encrypted

Always Encrypted is a feature of the SQL Server 2016/Azure SQL which allows you to take full control over the encryption process of the sensitive data stored in your SQL databases. Thanks to this mechanism the encryption key is stored only on the client side and is never revealed to the SQL Server. In consequence, data traveling from the server to the client is also encrypted (although I would not rely too much on this fact and always use encrypted connections to the SQL Server). That is a very different approach to Transparent Data Encryption or Cell-level Encryption, in which it is the server role to encrypt/decrypt data received/sent to the client. Server-side encryption is completely transparent to the client and does not impact the way the client builds SQL queries. In Always Encrypted model, any query against an encrypted column will perform comparisons on byte arrays of cipher text. As you can imagine this raises some challenges when building a data model. In this post, I am going to cover some details of how the Always Encrypted feature is implemented and, hopefully, help you use it effectively.

Continue reading

Meet Bazik – a SQL Server monitoring application

Some time ago I developed a simple diagnosing tool to monitor our production SQL Server and today I have a pleasure to present it to the public:) It’s a web application that you can deploy on IIS or run locally on IIS Express. On the main page it shows all currently running requests and open sessions. The screenshot in this case will serve better than a text description so just have a look:

Continue reading

Diagnosing a collation issue in a MySql stored procedure

We are using Elmah on production to log problems in our web applications. Elmah logs are stored in a MySql table which structure is a slightly modified version of the elman_error table from the original Elmah script (I changed the engine to InnoDB and added partitioning over TimeUTC column):

  `ErrorId` CHAR(36) NOT NULL ,
  `Application` VARCHAR(60) NOT NULL ,
  `Host` VARCHAR(50) NOT NULL ,
  `Type` VARCHAR(100) NOT NULL ,
  `Source` VARCHAR(60) NOT NULL ,
  `Message` VARCHAR(500) NOT NULL ,
  `User` VARCHAR(50) NOT NULL ,
  `StatusCode` INT(10) NOT NULL ,
  `AllXml` TEXT NOT NULL ,
  PRIMARY KEY (`Sequence`, `TimeUtc`) ,
  INDEX `IX_ErrorId` (`ErrorId`(8) ASC) , -- UNIQUE can't be created if it does not include all partition columns
  INDEX `IX_ELMAH_Error_App_Time_Seql` (`Application`(10) ASC, `TimeUtc` DESC, `Sequence` DESC) ,
  INDEX `IX_ErrorId_App` (`ErrorId`(8) ASC, `Application`(10) ASC) )
partition by range columns(`TimeUtc`)
  partition before20130719 values less than ('2013-07-19 00:00')

Continue reading

Be careful with varchars in Dapper!

In this post I will show you an interesting problem that we experienced when querying a SQL Server database with Dapper. I will use a simplified data model and a sample application so you could reproduce the issue on your own. Our sample table will look as follows:

create table Stats (
    StatsDay datetime not null,
    EventName varchar(100) not null,
    Item varchar(100) not null,
    Value int null,
    constraint PK_Stats primary key(Item,EventName,StatsDay)

Continue reading