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.
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:
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):
CREATE TABLE IF NOT EXISTS `elmah_error` ( `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 , `TimeUtc` DATETIME NOT NULL , `Sequence` INT(10) NOT NULL AUTO_INCREMENT , `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) ) ENGINE InnoDB DEFAULT CHARACTER SET latin2 partition by range columns(`TimeUtc`) ( partition before20130719 values less than ('2013-07-19 00:00') );
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) )
In this post I would like to present you a simple way to implement changelog system for database objects using NHibernate session interceptor and database triggers. This system will inform which rows were created, deleted and which columns were updated and what were their previous values.
If you are a database application developer you often face the problem of using database constants in your application code. I would like to present you a way how to use such constants and avoid hard-coding data-table primary key values.