SQL Server versions

Daniel Calbimonte

How to use BULK INSERT to import data locally and in Azure

November 16, 2017 by

Introduction

BULK INSERT is a popular method to import data from a local file to SQL Server. This feature is supported by the moment in SQL Server on-premises.

However, there is a new feature that is supported only in SQL Server 2017 on-premises. This feature allows importing data from a file stored in an Azure storage account to SQL Server on-premises using BULK INSERT. This feature will be supported in Azure SQL versions in the future.

In this article, we will show two examples. The first example will show how to use the traditional BULK INSERT statement from a local CSV file to Azure and the second example will show how to import data from a CSV file stored in Azure to SQL Server on-premises.

Read more »
Prashanth Jayaram

The evolution of SQL Server towards Digital Transformation challenges

October 9, 2017 by

In a data-driven world, where every second see a transfer of billions of pieces of data, enterprises are focused on making the access to data fluid and capable of being accessed on demand on a myriad of devices. Leveraging these capabilities to deliver better business results is now the prime focus. Information Technology is no more what it has been. What the web saw during the dot-com boom is what enterprise data is seeing now. Social, mobile, analytics, cloud, Big Data, Internet of Things… They’ve been enabling organizations to scale.

Read more »
Prashanth Jayaram

How to configure Always Encrypted in SQL Server 2016 using SSMS, PowerShell and T-SQL

October 2, 2017 by

In an era of remote storage and retrieval of data, including the cloud, data security plays a vital role, especially since it’s vulnerable during the transit. Situations like database backup or copy from or to the cloud, there is always a risk of data exposure to outside world lurking around one corner or the other. We have seen a noticeable surge in the technologies around protection and security of data from the world full of unsafe hands. Efforts are being made to protect data at a very granular level of the encryption hierarchy. Protection of business data cannot be stressed upon more.

Read more »
Ahmad Yaseen

SQL Server 2016 Maintenance Plan Enhancements

September 18, 2017 by

SQL Server Maintenance Plans is a SQL Server Management Studio built-in feature that helps in creating a workflow of variant database administration tasks, which can be run automatically using a predefined schedule or manually triggered by the user.

SQL Server Maintenance Plans allow you to use typical database maintenance tasks or customize your own task using a T-SQL script that runs on the local server or group of SQL Servers, providing more flexibility to the database administration tasks.

Read more »
Samir Behara

Querying Microsoft SQL Server 2012/2014 – Preparing for Exam 70-461

September 8, 2017 by
In today’s competitive world, technical certifications play an important role in advancing your career. It is a great way to validate and sharpen your technical skills, thus helping you in being more productive in your assignments. It also helps you to stand out from the crowd and get more visibility. A few months back I decided to appear the Microsoft Exam 70-461: Querying SQL Server 2012/2014 and was able to successfully pass the exam. Read more »
Thomas LeBlanc

Using Many-to-Many Relationships in SQL Server Analysis Services (SSAS) 2016

September 7, 2017 by

The Multidimensional Cube option of Analysis Services has handled many-to-many relationships with ease for many versions before 2016. The Tabular had a work around using DAX formulas until the release of SQL Server 2016. There are still some limitations to many-to many in Tabular but of course, there are some “tricks” to overcome the limitations. But, the many-to-many relationship will be in businesses data for many years to come. A solution has to be provided when it comes to Analysis Service databases.

Read more »
Ahmad Yaseen

In-Memory OLTP Enhancements in SQL Server 2016

August 22, 2017 by

SQL Server In-Memory OLTP, also known as Hekaton when it was introduced in SQL Server 2014, provides us with the ability to move specific database tables and suitable stored procedures into memory and compile the stored procedures into native x86 code. As a result, you can easily query these database objects directly from memory with the best performance and the least possible data access latency. In addition to that, the SQL Server Engine will no longer use the old latching and locking mechanism to control the data access concurrency. Instead, a high performance row versioning mechanism will be used to control the concurrency. This optimistic concurrency mechanism is 5 times to 20 times faster than the normal disk-based processing, due to reading the data from the memory directly.

Read more »
Prashanth Jayaram

A Quick start Guide to Managing SQL Server 2017 on CentOS/RHEL Using the SSH Protocol

August 8, 2017 by

In one of my previous articles Installation of SQL Server vNext CTP on Linux Distribution CentOS 7, I spoke about the installation of SQL Server 2017 on a Linux computer. Let us now look at another way to showcase that a SQL Server installation and configuration can also be managed using the Secure Shell (SSH) protocol using PuTTY. We shall look at the important details, such as, how to get PuTTY, SQL Server, how to install/uninstall SQL 2017 CTP2.1, Upgrade to SQL 2017 RC1, and install/remove SQL Tools.

Read more »
Prashanth Jayaram

How to Configure TDE database with AlwaysOn using the Azure Key Vault in SQL 2016

July 28, 2017 by

One of the recent tasks I undertook on configuring Transparent Data encryption (TDE) using asymmetric key protection with Azure Key Vault with Always On opened a different dimension on securing data for me. Even though it seems slightly complex, if you have the key details, the steps are in fact, really straight forward.

Read more »
Craig Porteous
C:\Users\craig.CRAIGFLIX\AppData\Local\Microsoft\Windows\INetCache\Content.Word\SSMS_17.png

What’s New in Reporting Services (SSRS) 2017

July 26, 2017 by

To further the discussion related to Mohamed’s post on Reporting Services 2016 What’s new in SQL Server 2016 Reporting Services (SSRS), I wanted to cover the new features and functionality we see in Reporting Services 2017 as RC1 was released last week SQL Server 2017 Reporting Services Release Candidate now available, separately from SQL Server 2017 which reached RC1 a few days prior.

Read more »
Ahmad Yaseen

SQL Server 2016 Memory-Optimized Tables – The Checkpoint operation

June 21, 2017 by

The SQL Server Database Engine stores data changes in the buffer pool, in memory, before applying it to the database files, for I/O performance reasons. After that, a special kind of background process, called Checkpoint, will write all of these not reflected pages, also known as Dirty Pages, to the database data and log files periodically.

Read more »
Prashanth Jayaram

How to use Python in SQL Server 2017 to obtain advanced data analytics

June 20, 2017 by

On the 19th of April 2017, Microsoft held an online conference called Microsoft Data Amp to showcase how Microsoft’s latest innovations put data, analytics and artificial intelligence at the heart of business transformation. Microsoft has, over the last few years, made great strides in accelerating the pace of innovation to enable businesses to meet the demands of a dynamic marketplace and harness the incredible power of data—more securely and faster than ever before.

Read more »
Prashanth Jayaram

Top SQL string functions in SQL Server 2017

June 8, 2017 by

SQL Server 2017 has been in the talk for its many features that simplify a developer’s life. With the previous versions, developers had to write T-SQL, or user-defined functions using temporary tables, and build complex logic, just for string manipulation. In almost every database I work with, I see many user-defined functions for string manipulation and string aggregation.

Read more »
Ahmad Yaseen

Compression and decompression functions in SQL Server 2016

June 2, 2017 by

The concept of data compression is not a new on for SQL Server Database Administrators , as it is was introduced the first time in SQL Server 2008. In that SQL Server version, you were able to configure the compression at the row and page levels on the table, index, indexed view or the partition. The row and page level compression is not the best choice in all cases, as it does not work well on the Binary Large Objects (BLOB) datatypes, such as the videos, images and text documents.

Read more »
Prashanth Jayaram

Installation of SQL Server vNext CTP on Linux Distribution CentOS 7

May 10, 2017 by

It was a paradigm shift in December 2016, when Microsoft made their SQL Server database available for Linux; it was the first time in history that Microsoft ever designed SQL Server to run on a non-Windows operating system. SQL Server vNext was released for public preview so the user community could test and deploy SQL Server on a Linux operating system. Microsoft took a major step in diversifying the database technology into the non-windows platform for the first time.

Read more »
Daniel Calbimonte

SQL Server vNext – Does Microsoft love Linux?

April 21, 2017 by

Introduction

In the 90s and at the beginning of the 21 century, Linux and Windows were decidedly working at cross purposes

However, starting on 2009, Microsoft started to contribute with code in the Linux Kernel. On 2013, Azure supported Virtual Machines in Ubuntu and later came the support to .NET in Linux, Visual Studio for Linux, PowerShell in Linux, support to monitor Linux server to monitor in SCOM (System Center Operation Manager) and now, the support for SQL Server that is on the previous version when this article was published and it is planned to be released this year (2017). Microsoft is a Platinum member of the Linux Foundation now.

Read more »
Prashanth Jayaram

SQL Server 2016 enhancements – SQL Truncate Table and Table Partitioning

April 18, 2017 by

The idea behind this article is to discuss the importance and the implication of SQL Partition and understand the SQL truncate command partitioning enhancements in SQL 2016

One of the biggest challenges for a DBA is to identify the right candidate for table partitioning, as it requires expertise in design and implementation.

Read more »
Ahmad Yaseen

How to Configure Read-Only Routing for an Availability Group in SQL Server 2016

March 23, 2017 by

The SQL Server Always On Availability Groups concept was introduced the first time in SQL Server 2012 as an enterprise-level high availability and disaster recovery solution that will replace the database mirroring feature. Always On Availability Group provides a high availability solution on the group level, where each group can contain any number of databases that can be replicated to multiple secondary servers known as Replicas.

Read more »

Some uncommon but useful T-SQL and Database Engine Enhancements in SQL Server 2016

March 20, 2017 by

SQL Server 2016 is the most advanced version of Microsoft’s Data Platform released yet. This is obviously my favorite one as it has tremendous capabilities and enormous features. These new additions not only enhance the productivity of its users (Database Developer, DBA or Application Developer), but also enable the enterprise to use its data more effectively and efficiently.

Read more »
Marko Zivkovic
C:\Users\Marko\AppData\Local\Microsoft\Windows\INetCacheContent.Word\JSON.PNG

How to import/export JSON data using SQL Server 2016

March 7, 2017 by

JSON is an abbreviation for JavaScript Object Notation. JSON is very popular and currently the most commonly used data exchange format. Most modern web and mobile services return information formatted as JSON text, all database web services, web browsers (Firefox, Internet Explorer) return results formatted as JSON text or accept data formatted as JSON. Since external systems format information as JSON text, JSON is also stored in SQL Server 2016 as text. You can use standard NVARCHAR columns to store JSON data in SQL Server 2016.

Read more »