Import and analyze IIS Log files using SQL Server

IIS generates logs where are recorded many information about HTTP requests such as what Url was called, when the request happened, what is the origin, etc. If you want to analyze information from log files you can use use text search, regular expressions, or some log analysis tools; however, this might be tedious job. SQL Server… Read more

Loading JSON files from Azure File Storage

Azure File Storage supports SMB protocol, so you can map your local virtual drive to the Azure File storage share using the following procedure: Create file storage account (e.g. mystorage), file share (e.g. logs), and folder using Azure portal or Azure PowerShell SDK. Create firewall outbound rule in Windows Firewall on your computer that allows… Read more

Loading line-delimited JSON files in SQL Server 2016

Loading Line-Delimited JSON files in SQL Server One of the problem with JSON is the fact that you cannot continuously append JSON messages. If you want to have valid array of JSON objects you will need to surround them with brackets. Once you add final bracket, you cannot add new data. Line delimited JSON (aka. JSON lines or Newline-delimited… Read more

Clustered columnstore Index: Data Load Optimization – Parallel Bulk Import

In the previous blog , we looked at minimal-logging when bulk importing data into a table with clustered columnstore index. In this blog, we will look at parallel bulk import. Recall that on rowstore tables (i.e. the tables organized as rows not as columnstore), SQL Server requires you to specify TABLOCK for parallel bulk… Read more

Clustered Columnstore Index: Data Load Optimizations – Minimal Logging

When bulk importing large number of rows, you want to make sure it can be done efficiently with minimal resource usage so that that you can finish the data load quickly and start running your analytics. SQL Server provides following three key optimizations during bulk import Minimal logging Reduced overhead of locks Concurrent Inserts Just… Read more

Loading non-relational data formatted as JSON text

Introduction Data load is one of the most important processes in databases. However, relational databases are not optimal for loading complex relational data. JSON can be used to improve performance and reduce complexity in data load process if you serialize some entities as JSON collections. In this post we will see how you can use… Read more

Minimal logging and MERGE statement

In my earlier blog, we discussued how we can get minimal logging using TF-610 when using TSQL INSERT statement. Interestingly, you can get minimal logging when using MERGE statement as well. Here is a simple example to illustrate it. use bulktestgo alter database bulktest set recovery SIMPLE — enable the trace flag 610dbcc traceon… Read more