Reporting in SQL Server – create a matrix based sub-report called by the previously created main report

March 4, 2015 by Steve Simon

Introduction

As you will remember from our last “get together” we created an application that permitted us to report upon financial data based upon an unorthodox financial year. In fact, our fiscal year started in July and ended in June. We created a chart to display the data.

In today’s “get together” we are going to push our application a bit further and build in a subreport which will bring up the underlying data when the end user clicks upon the chart for any particular month. Thus should the user click on February 2015, then all of February’s data (for the selected funds) is shown in a matrix. If the user chooses March, then March’s data is shown.

Integrate On-Premises and Azure using a site-to-site VPN with RRAS (step-by-step)

March 3, 2015 by Murilo Miranda

In order to connect on- premise and Azure we have two options, a point-to-site and a site-to-site VPN. On this article I will be showing how to configure a site-to-site VPN using a routing and remote access, step-by-step.

Reporting in SQL Server – create a chart based on the data extracted for a given date range

February 27, 2015 by Steve Simon

Introduction

I recently heard from a lady from overseas who wanted to find a quick and dirty mechanism of extracting data for a given date range (based upon a fiscal year that started July 1^st and ended June 30^th). The idea interested me and as always, I had to try it out.

In today’s “get together”, we are going to have a look at how this may be achieved.

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

February 24, 2015 by Murilo Miranda

When creating a hash index in a memory optimized table we need to define a value for the bucket count. Think twice before set a random value, you may pay the price later… On this article we will explain the effects of a bad defined bucket count and how to monitor it.

Using a cursor to correctly extract SQL Server data and place it in a Reporting Services matrix

February 23, 2015 by Steve Simon

Introduction

In our last two chats, we discussed enterprises that have had financial years that began in July and ended at the end of June. One of our clients works with this fiscal calendar and their financial folks are Excel “Fundi’s” (Fundisa is a Nguni word for “expert”). Many of their reports contain the current month’s sales, in addition, carrying running totals from the beginning of the fiscal year to date. Read more »

Using Master Data Services in SQL Server to quickly create a GUI that may be maintained by the end user

February 20, 2015 by Steve Simon

Introduction

Oft times we are forced into situations where we clearly need to think outside of the box. A case at hand arose early in 2014 where one of our client’s required a “quick and dirty” front end to modify data within a table that reflected the outstanding balances (of their clients) and the attempts that they had made to recover these funds. Master Data Services seemed to be the way to go!

Monitoring SQL Server Reporting Services

February 19, 2015 by Steve Simon

Introduction

In our last get together I mentioned that oft times SQL Server reports are created due to a dire business need to be used once and never again. Further, some reports that we believe are not often used could be “top of the pops” unbeknown to us. A guess as to a number of times a report is used per month, in addition to the statistics behind each report should not be guesswork, but rather monitored actively to ensure that frequently used reports are both efficient and effective. Further, those reports that are either not used or have not been run in quite some time, should perhaps be removed in order to keep the server clean and not cluttered.

Creating dynamically generated CSV files containing SQL Server data

February 12, 2015 by Steve Simon

Introduction

A few months back, I presented a paper at SQL Saturday 327 in Johannesburg, South Africa. Late last month I received an email from one of the attendees. His issue was quite interesting and I decided to share it with you. The gentleman wanted a SSIS script that would permit him to extract data from a SQL Server database table and place it in a CSV file with a dynamically allocated name. Being a strong advocate of using the SSIS toolbox, I experimented with an alternative solution. We are going to construct THIS SOLUTION in today’s get together.

Let’s get started.

SQL Server Reporting Services Best Practices

February 11, 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 try to pull all these techniques together, rationalize our development plans, and moreover, look at some good habits to adopt or for the want of better words utilize SQL Server Reporting Services Best Practices.

Reading the SQL Server Transaction Log

February 9, 2015 by Minette Steynberg

Introduction

There has always been some debate as to whether or not there are real benefits to be gained from accessing the information in the transaction log. This article will endeavor to answer that question by looking at the following:

What is the SQL Server Transaction Log?
What information is stored in the transaction log?
What can be gained by accessing the information in the transaction log?
How does the transaction log work?
What tools are available for reading the transaction log?
And ultimately, is this something we should be doing at all?

Understanding the SQL Server Audit

February 9, 2015 by Minette Steynberg

Introduction

With the advent of the Information Era, data is being collected on a massive scale. Information Technology systems have made access to this data faster and easier. It has also made it easier for data to be misused.

Creating a successful auditing strategy for your SQL Server databases

February 9, 2015 by Minette Steynberg

The purpose of security auditing is to identify all attacks, unlawful or malicious activities which might be taking place on your server. Criminals have become increasingly inventive and as a DBA you might not have considered or even be aware of all activities which may be putting your data at risk.

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

February 5, 2015 by Murilo Miranda

In sequence of the first article about the server memory importance, we will check another In-Memory OLTP key point: The Checkpoint files. Check what wrong can happen, and how to avoid this!

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.