In-Memory OLTP – Three key points to entertain your watchdog

February 4, 2015 by Murilo Miranda

With the introduction of the in-memory technology, we need to think about what are the new challenges to keep the service up and running. In this article I will explore one of the three key points to monitor in order to maintain your SQL Server healthy.

Last week I spoke at my local SQL Server user group, in Lisbon. The session was nice and the good feedback received encouraged me to write about this and share information that is not too spread over the internet (so far…): Monitor environments using In-Memory OLTP.

Continuous Deployment using SQL Server Data Tools and Visual Studio Online

February 2, 2015 by Régis Baccaro

In the previous posts

Deployment to several databases using SQL Server Data Tools and TFS using a Custom Workflow file
Deployment to several databases using SQL Server Data Tools and Team foundation Server
Continuous integration with SQL Server Data Tools and Team Foundation Server

I have been mostly writing about the interaction between SQL Server Data Tools and Team Foundation Server. Microsoft provides a hosted version of Team Foundation Build Service called Visual Studio Online. The configuration and functionality is mostly the same than what I have previously been writing about but there are some specifics things that we need to be aware when using Visual Studio Online Build Service.

Creating Azure automation to start Azure VMs

February 2, 2015 by Régis Baccaro

When you have created a series of Azure VMs – for example for doing demos – you might want to be able to start it and shut it down easily. For convenience purposes but also for keeping the cost down while you’re not using the Azure VMs. In this case Azure Automation comes handy.

In-Memory OLTP Series – Introduction

January 30, 2015 by Luan Moreno M. Maciel

Introduced on SQL Server 2014, the new brand feature In-Memory OLTP a.k.a “Hekaton” is a Main-Memory Database Engine. Developed by Microsoft Research & Paul Larson (Database Research Group at MSFT) this feature have the ability to manage and held tables entirely In-Memory. In this Series we will pass through for all the new concepts and components that makes this New Engine the most excited acquisition of the new SQL Server Version (120).

SQL Server Confidential – Part II – SQL Server Cryptographic Features

January 29, 2015 by Jasmin Azemovic

Transparent Data Encryption (TDE)

SQL Server has two ways of encrypting data. One way is by protecting data on the table, record or column level, and the other way is by protecting data “at the rest”. One of the best crypto features in the database world today is known as a Transparent Data Encryption.

How to backup SQL Server databases to Microsoft Azure

January 22, 2015 by Daniel Calbimonte

In the last chapter, we explained how to create a Microsoft Azure Account and how to have a Microsoft Azure Portal.

This time, we will show how to backup our SQL Server Databases to a Microsoft Azure. We are assuming that you already have an Azure account.

Microsoft Azure, our first steps to migrate data

January 21, 2015 by Daniel Calbimonte

The cloud is a buzzword in the IT world. Oracle, Amazon and Microsoft with Microsoft Azure are offering Cloud Services to the public. Most of the companies plan to have part of their environments in the cloud to reduce the maintenance and security effort.

Getting started with data mining in SQL Server

January 19, 2015 by Steve Simon

Introduction

In past chats, we have had a look at a myriad of different Business Intelligence techniques that one can utilize to turn data into information. In today’s get together we are going to have a look at a technique dear to my heart and often overlooked. We are going to be looking at data mining with SQL Server, from soup to nuts.

Deployment to several databases using SQL Server Data Tools and TFS using a Custom Workflow file

January 16, 2015 by Régis Baccaro

In the previous blog post : Deployment to several databases using SQL Server Data Tools and Team foundation Server I illustrated how it is possible to use TFS and a batch file to deploy a database to several SQL Server instances or to deploy several SQL Server databases to several instances. The main way to achieve that in the previous post was using a batch file. For more information about this technique please have a look at that blog post.

In this post on the other hand I will demonstrate how the same functionality can be achieved using a Windows Workflow Foundation file (xaml) deployment file and Team Foundation Server.

Using SMB fileshare as a storage alternative

January 16, 2015 by Murilo Miranda

Do you know that you can store system and user databases in a fileshare? Even for a clustered instance this is an option now. In this article we will check how to implement this solution.

On the latest versions of SQL Server new possibilities to assign storage to a clustered instance became available. In this article, we will talk about one of these options, which is also an option for standalone instances by the way: The network-attached storage – SMB Fileshare.

How to utilize DAX queries against a Tabular Database

January 12, 2015 by Steve Simon

In an earlier “get together”, we had a quick look at the DAX language and how to construct useful queries. In today’s conversation we shall be concentrating on utilizing the knowledge that we obtained from the earlier article and seeing how these queries may be utilized for “multiple value” query selection criteria (against a tabular database).
Enough said, let us get started!

SQL Server security mechanism – How to control what data are users able to view within their reports

January 8, 2015 by Steve Simon

Introduction

A few years back, a client asked me to implement a quick and dirty “security mechanism” to control what data the myriad of users were able to view within their reports. There were numerous tables with multiple columns and all departments (within the enterprise) had their data within these tables.

SQLShack Industries has tasked us with creating a similar quick and dirty “security mechanism”. We shall attack this challenge by creating the necessary stored procedures (to extract the required data) and then utilize these stored procedures to render and consume the data within our reports.

Buffer Pool Extension (BPE) – How it works?

December 31, 2014 by Murilo Miranda

You already understand the benefits of the BPE and how to deal with the feature, now it’s time to better understand how it works.

Here we are, the last article of this Buffer Pool Extension series. We came from the explanation of what is the Buffer Pool, made an introduction of the new In-Memory technology and saw on what the Buffer Pool Extension can help in keep a stable environment, and finally, the part 3 showed how to implement the Buffer Pool Extension in your SQL Server 2014 system.

How to migrate the logins of a database to a different server

December 31, 2014 by Daniel Calbimonte

Sometimes, it is necessary to move the database from one server to another. The problem is that usually the logins and the database users are mapped. If that is the case, when you backup a Database and restore a database in another server, the logins are lost. In this article, we will show different alternatives to solve this problem.

Buffer Pool Extension (BPE) – Implementing another level of cache

December 30, 2014 by Murilo Miranda

This third part of the BPE series shows the basics of Buffer Pool Extension and know how to implement and troubleshoot it.

During all the Buffer Pool Extension (BPE) series we have been explaining base concepts related to this new technology, as well as its benefits. We understood how the Buffer Pool works and the main benefits of implement the Buffer Pool Extension together with In-Memory technology. I recommend you to read the previous articles, if you are not familiarized with those concepts, so everything into this article will make more sense 🙂

SQL Server Transaction Log – Part 2 – Top Reasons for Log Performance Problems

December 30, 2014 by Miroslav Dimitrov

In the previous article, we have examined the physical structure of the transaction log and discussed WAL algorithm. Now I will take you on a journey through the most common reasons for experiencing a log bottleneck in your systems.

Deployment to several databases using SQL Server Data Tools and Team foundation Server

December 26, 2014 by Régis Baccaro

In the previous post : Continuous Integration with SSDT and TFS I have described some techniques to allow continuous integration / deployment using SQL Server Data Tools and Team Foundation Server. The solution described works great for deploying to a single instance of a database.

How-to: Proactive capacity management

December 26, 2014 by Kaloyan Kosev

Capacity management is one of the most important tasks you have to perform as a DBA. Usually we rely on threshold alarms for the disks volumes and the databases, however I find it very suitable for me to take actions in advance – this way I am sure the SQL Servers under my supervision will have the needed space to operate without issues. And yes, there is the auto-grow, however it’s a safety mechanism which role is to ensure that even in critical situations you can have your database operational, however this does come with a negative performance impact which I try to avoid for my key production systems.

The SQL Server system views/tables/functions. Common questions and solutions to real life problems

December 25, 2014 by Daniel Calbimonte

Introduction

In this new article, we will talk about the system views/tables/functions and how to solve common questions using them.

The system views are views that contain internal information about a Database.

The master database for example contains information about the SQL Server itself, while the msdb database contain information about the SQL Server agent and each database has its own system views/tables.

In this article we will show how to get the list of tables, views, stored procedures, how to get a list of tables of all the databases, how to find a table in multiple datatabases, how to get the list of users, logins, mapped logins, how to detect a fragmentation in a table and more.

Reviewing the SQL Server Audit

December 25, 2014 by Kenneth Fisher

Deciding what method to use to collect audit information can be a task in and of itself. Depending on your needs there are a number of different ways to collect the information. There are settings such as Login Auditing and Common Criteria Compliance that are fairly simple to implement but only collect very specific sets of information. There are also coding solutions such as Triggers and Audit Columns that are very flexible but can be tricky to get right. Then there are Profiler Traces but those have been deprecated and will be removed in a future version of SQL Server. Their replacement, Extended Events, has become easier and easier to use in recent versions and can be used to collect a huge variety of information. However Extended Events does not include any Audit specific information. If you are setting up an auditing solution in SQL Server you are probably going to want to consider using SQL Server Audits. Audits are based on Extended Events and so have all of the benefits of Extended Events but also include the audit specific events. Audits have a different interface than Extended Events and a different set of T-SQL Commands. Fortunately they are very easy. Read more »

Create reports like a champion! Tips and tricks with Reporting Services

December 24, 2014 by Steve Simon

Introduction

Over the past few months we have covered a lot of ground in our little “get togethers”. We have seen a few of my favorite tips and tricks. In today’s discussion we are going to have a look at a few gotcha’s upon which I have banged my head many times.

Buffer Pool Extension (BPE) – In-Memory OLTP: The Memory Challenge

December 24, 2014 by Murilo Miranda

In continuation of the previous post “Buffer Pool Extension (BPE) – Introduction to the Buffer Pool”, we will keep diving into the Buffer Pool Extension, this time, talking about another important factor behind this feature – the memory. No, we will not review how the memory works literally, we are talking about the great SQL Server 2014 new feature: The In-Memory OLTP, or the known HEKATON.

Getting started with Data Analysis Expressions (DAX) in SQL Server

December 22, 2014 by Steve Simon

Introduction

In our SQLShack discussions over the past few weeks, we have dealt with a few of the more conventional SQL Server data manipulation techniques. Today we are going to be a bit more avant-garde and touch upon a subject dear to my heart.

With Power Bi becoming more and more important on the business side within major industries worldwide, it is not surprising that sooner or later every SQL programmer is going to have to learn and be able to ‘talk’ DAX.

In this article we are going to have a look at the a few of the more important ‘constructs’ and produce production grade queries for data extraction and for reports; enabling the reader to ‘hit the ground running’.

SQL Server Transaction Log – Part 1 – Log Structure and Write-Ahead Logging (WAL) Algorithm

December 18, 2014 by Miroslav Dimitrov

SQL Server transaction log is one of the most critical and in the same time one of the most misinterpreted part. While being neglected, it can easily become a bottleneck to our SQL Server environment. We need to have this in mind and to take care of our transaction logs in order to streamline the performance of our queries and increase log’s throughput.

Disaster Recovery Planning with Always-On Availability Groups

December 17, 2014 by Derik Hammer

When I configured my first Always-On Availability Group, I setup a Windows Cluster and started with SQL Server Management Studio’s New Availability Group Wizard, scripting out the steps along the way. This entire process took only a matter of minutes. The minimum required steps for configuring the cluster and getting a database into an Availability Group (AG) are very few. This process, however, is deceptively simple. What some don’t realize is that the majority of work required for setting up an AG needs to occur in the planning phase, before a server is even requisitioned. Let us examine the questions that you should ask before implementing an AG.