Transparent Data Encryption (TDE) was originally introduced in SQL Server 2008 (Enterprise Edition) with a goal to protect SQL Server data at rest. In other words, the physical data and log files along with the database backup sitting on file system are protected (encrypted).
Read more »
The SQL Server transaction log is akin to a ‘Black box’ in an airliner. It contains all of the records of transactions made against a database. This information is a proverbial goldmine for database audits, recoveries etc but it was never meant to be exposed to end users let alone visualized in an easy to read manner nor used for DBA tasks. As such, utilizing this information can be a challenge, to say the least.
Read more »
Data replication has been around for many decades. There are two primary types of data replication, logical and physical. This article covers a high-level view of logical replication, the differences between logical and physical replication, and the specifics of SQL Server transactional replication.
Read more »
SQL Server 2017 brings a new query processing methods that are designed to mitigate cardinality estimation errors in query plans and adapt plan execution based on the execution results. This innovation is called Adaptive Query Processing and consist of the three features:
This article explores SQL Sort, Spill, Memory and Adaptive Memory Grant Feedback mechanism in SQL Server.
Read more »
In this post, we are going to look at the new feature in SQL Server 2017 – interleaved execution. You need to install SQL Server 2017 CTP 1.3 to try it, if you are ready, let’s start.
Now, when a CTP 2.0 of SQL Server 2017 is out, you don’t need to turn on the undocumented TF described further, and the plans are also different, so the examples from this post use CTP.1.3, probably not actual at the moment (I was asked to hold this post, until the public CTP 2 is out, and interleaved execution is officially announced). However, the post demonstrates Interleaved execution details and might be still interesting.
Read more »
In this post, I continue the exploration of SQL Server 2017 and we will look at the nonclustered columnstore index updates.
Columnstore index has some internal structures to support updates. In 2014 it was a Delta Store – to accept newly inserted rows (when there will be enough rows in delta store, server compresses it and switches to Columnstore row groups) and a Deleted Bitmap to handle deleted rows. In 2016 there are more internal structures, Mapping Index for a clustered Columnstore index to maintain secondary nonclustered indexes and a deleted buffer to speed up deletes from a nonclustered Columnstore index.
Updates were always split into insert + delete. But that is now changed, if a row locates in a delta store, now inplace updates are possible. Another change is that it is now possible to have a per row (narrow) plan instead of per index (wide) plan.
Let’s make some experiments.
Read more »
Some time ago, SQL Server 2017 was released and issued as CTP. The most exciting release in that CTP was that SQL Server now supports Linux! This is awesome and I consider it to be great news for many people.
I am personally interested in the new features of query processing, and finally I had some time to install the SQL Server 2017 and dig a little bit into it. Currently, it is CTP 1.2 available, and I will use this version for my experiments.
While exploring new extended events, I’ve found an interesting event compilation_stage_statistics and one of the columns of this event was trivial_plan_scanning_cs_index_discarded with the following description “Number of trivial plans discarded or could have been discarded which scan Columnstore index”. That pushed me to do some investigations of the topic.
Read more »
Database Mail is a convenient and easy way to send alerts, reports, or data from SQL Server. Failures are not obvious to the us though, and developing a process to monitor these failures alongside other failures will save immense headaches if anything ever goes wrong.
Database Mail is a component of SQL Server that is available in every edition, except for Express. This feature is designed to be as simple as possible to enable, configure, and use.
Database Mail relies on SMTP to send emails via a specified email server to any number of recipients. When configuring, you provide a mail server, credentials (if needed), and then the service is ready to use. We’ll be focusing here on failure reporting and not configuration. If you need help setting up or configuring this feature, check out some of the references at the end of this article.
Read more »
This article will explain the main features in SQL Server 2017, 2016, 2015, 2014, 2012, 2008, 2005, 2000, 7, 6.5, 6.0, 4.2, 1.1 and 1.0.
In the past, the first SQL Server versions supported OS/2 (an operative system created by Microsoft and IBM) and Windows.
Now, the new versions of SQL Server (vNext and SQL Server 2017) can be installed in Linux. 15 years ago, it was impossible to think that. Linux and Microsoft were just like oil in water and now, Microsoft loves Linux.
Also, we now enjoy full integration with Azure, Tabular Databases, SSIS, SSAS and more. In this article, we will talk about all these changes and improvements.
Read more »
The OpenQueryStore is an Open source implementation of the popular Query store functionality introduced in SQL Server 2016 CTP2. The OpenQueryStore was first introduced in June 2017. Its main contributors are William Durkin and Enrico van de Laar
Read more »
The SQL Server Query Store is a relatively new feature introduced in SQL Server 2016. It is basically a SQL Server “flight recorder” or “black box”, capturing a history of executed queries, query runtime execution statistics, execution plans etc. against a specific database. This information helps in identifying performance problems caused by query plan changes and troubleshooting by quickly finding performance differences, even after SQL Server restart or upgrade. All data that SQL Server Query Store capture are stored on disk.
Read more »
PowerShell has become the ultimate choice for many database administrators because of its efficient way of handling and managing automation in a simple, quick way. It’s built on .NET Framework and uses Object Models such as COM, ADSI, ADO, and WMI. PowerShell has replaced the traditional way of scripting that used many legacy scripting practices to monitor SQL instances.
I’ve been asked on several occasions about how to store the output of PowerShell WMI data into the SQL table. The question comes up so frequently that I decided to write this article.
When sending data within a system (such as a PowerShell object to a cmdlet), the process is straightforward. However, with non-native data interchange (for instance, WMI to SQL), the process can potentially get complicated. Due to this, many purists suggest sticking to simple interchange formats, such as CSV, JSON or in some cases, XML.
Read more »
In the first part of this article, we will discuss about parallelism in the SQL Server Engine. Parallel processing is, simply put, dividing a big task into multiple processors. This model is meant to reduce processing time.
Execution plans are the equivalent to highways and traffic signs of T-SQL queries. They tell us how a query is executed.
Read more »
Database Mail, as you would expect from its name, is a solution for sending e-mail messages from the SQL Server Database Engine to users. Using Database Mail, database applications can send e-mail messages that can, for example, contain query results or simply alert a user about an event that occurred in the database.
The process of Database Mail configuration has three main steps. In order to complete this successfully, we need to:
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 »
Log shipping is a high-availability configuration that perhaps most of us are familiar with. It’s one of the oldest techniques wherein we ship transaction logs from a Primary database to a Secondary database. Log Shipping is still a vital feature used in case of applications that use warm standby for Disaster Recovery. We can see many articles which discuss the process of configuring Log shipping using T-SQL or SSMS.
Read more »
The Bulk copy program aka bcp is the console application used to export and import data from text files to SQL Server or vice versa.
It is very popular because it is fast and easy to download.
Read more »
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 »
Sometimes there are tasks that require too much time to finish during business hours and we need to leave the office with some still pending like the migration of data, backups or long running queries. In such cases, we would still want to receive an email in our smartphones to make sure that the job was completed successfully.
Read more »
With the latest versions of Azure SQL database, Microsoft has introduced a number of new mechanisms to help users and administrators better optimize their workload.
Automatic index management and Adaptive query processing provide us with the possibility to rely on the built-in intelligence mechanism that can automatically tune and improve the performance of our workload.
Read more »
The annual developer Microsoft Build Conference was held at the Washington State Conference Center in Seattle during May 10-12, 2017. The event had over 5,000 attendees and received a lot of love from the technical community. There were a number of announcements made during this 3-day conference, and in this article I will recap my learnings from a SQL Developer perspective. In the Data Platform, there were a number of new innovations, products and capabilities announced and a lot of emphasis was on using data to fundamentally transform your business. There was focus around serving Artificial Intelligence with Data, which seemed to me to be the future of Data Platform.
Read more »
The SQL Server Query Processing Engine is the most complex and sophisticated part of SQL Server. This engine has many responsibilities, but one of the most important parts is to design the query execution plan and execute it in a best and most efficient possible manner.
Read more »
SQL Server Transparent Data Encryption, also known as TDE, is a “data at rest” encryption mechanism that is introduced in SQL Server 2008 as an Enterprise Edition feature. TDE is used to perform a real-time I/O encryption for the SQL Server database data, log, backup and snapshot physical files, rather than encrypting the data itself, using either Advanced Encryption Standard (AES) or Triple DES (3DES) encryption.
Read more »© Quest Software Inc. ALL RIGHTS RESERVED. | GDPR | Terms of Use | Privacy