Matija Lah

String Concatenation Done Right – Part 1 – Dubious Practices

April 15, 2015 by

Having to represent sets of data as strings is a very common requirement in information management, even in modern times where a variety of more or less elaborate standards for storing, and moving, data are at our disposal. For instance, XML, JSON, or similar techniques, allow the data to be extracted from one data source, using a well-known standard, and be stored temporarily until being loaded into a destination data store, or until being consumed in some other way. Actually, both XML as well as JSON might even be used as a standard way of storing data permanently; especially, if the consumers expect the data to use one or the other format.

Read more »
Steve Simon

How to import data into SQL Server databases using the OData source for SQL Server Integration Services

April 6, 2015 by

Introduction

A few days back I was looking at ways to access raw data from within Microsoft Dynamics CRM in an effort to extract the raw data and to place it in our data warehouse. I started to explore utilizing OData and SSIS to pull the necessary data from the cloud to our local warehouse.

Whilst there are known authentication issues between Dynamics CRM and the Microsoft OData SSIS data source (and thus we could not utilize this access method), I thought it to be so very powerful, that I began looking for other constructive manners in which to utilize the OData Source.

Read more »
Daniel Calbimonte

How to migrate your database to an Azure Virtual Machine

March 31, 2015 by

In one of my articles about Microsoft Azure, I show how to create credentials and how to connect to an Azure Database using SQL Server Management Studio (SSMS). In this new article, we will talk about the Azure Virtual Machines and we will learn how to connect to them using our local SSMS. You will require unblocking ports, add endpoints and other tasks that I will explain later. We will also learn how to export a local database to an Azure Virtual Machine with SQL Server installed.

Read more »
Steve Simon

How to automate SQL Server database restores

March 30, 2015 by

Introduction

A few days back I encountered an interesting challenge. The client wanted to have copies of the nightly backups of the transactional databases restored on a warehouse server, to be utilized to update the warehouse.

The over all process

Prior to the pushing the daily backup to the warehouse server, the previous days restore is deleted. The important point being that the “SQLShackFinancial” database is no longer present on the warehouse server. Having been deleted, downloading of the backup file begins and the restore of the current backup version begins. Normal warehouse processing then ensues and so the cycle continues.

Read more »
Johan Ludvig Brattås

Performance tuning an SSAS Tabular model

March 19, 2015 by

Modeling for the xVelocity/Vertipaq engine is a completely different beast than modeling for your trusty multi-dimensional SSAS cubes.
In-memory = blazingly fast; At least that’s what you would think.
As Tabular models gain popularity with business users and developers alike, we’re starting to see that this isn’t always the case.
We’re going to take a look at some of the common errors and mistakes and how to avoid them.
And since the PowerPivot engine is the same – you will learn how to tune your PowerPivot-based Excel workbooks as well.

Read more »
Steve Simon

Which Reporting Services dataset fields are being utilized by the reports?

March 18, 2015 by

Introduction

Have you ever felt like pulling your hair out, trying to ascertain exactly which fields in your existing Reporting Services datasets are being utilized by your reports. This happened to me recently during a corporate conversion and cleanup exercise for a database migration to the cloud.

The “aha moment came after having presented a paper at the PASS SQL Server Nordic Rally (March 2015), when one attendee came up to me and asked if I knew of a method to do this. As they say ‘necessity is the mother of invention’ and spiking my interest, I played around until I came up with the solution that we are going to chat about today. The end solution may be seen below

Read more »
Steve Simon

How to convert data format into a valuable dataset using SQL Server Reporting Services

March 16, 2015 by

Introduction

Oft times we are forced into situations where we must clearly think outside of the box. In today’s “get together”, we are going to discuss a challenge that I encountered during the last week of February of this year. The client had been charting weekly business calls placed by his sales reps. Our client had been tracking these results within an Excel spreadsheet (see the screen dump below) and he would be using this spreadsheet to report the sales reps progress going forward. My task was to source this data for the corporate reports in Reporting Services, from this spreadsheet and do so on a weekly basis. The client, being resistant to change, was not willing to change the format of the spreadsheet to something more conducive to be utilized by the chart that he wished to produce (see immediately below).

Read more »
Murilo Miranda

SQL Server 2014 – Install a clustered instance – step-by-step (3/3)

March 12, 2015 by

This article is divided in three parts, if you started here, check the first and second part to find the previous steps.

Now we need to define if the instance will work in “Windows Authentication Mode” or “Mixed Mode”. I usually choose “Mixed Mode” because there’s always an application that don’t work with domain accounts. By choosing this option you need to specify the password for the famous “sa” login. Notice that you can change the authentication mode anytime! You also need to add accounts to receive the Sysadmin privilege, this is very important… Even more if you choose the “Windows Authentication Mode”.

Read more »
Murilo Miranda

SQL Server 2014 – Install a clustered instance – step-by-step (1/3)

March 9, 2015 by

Do you want to install a SQL Server 2014 clustered instance? Not sure how to proceed? What are the requisites? This series of articles will show this, step-by-step.

In this article I’m going to explain, step-by-step, how to install clustered SQL Server instance. The SQL Server version that we will be using is the 2014, or SQL Server 12, the latest SQL Server version, so far…

Read more »
Daniel Calbimonte

How to create a Database Mirroring

March 6, 2015 by

Sometimes our Database fails and we need to reestablish the Database services immediately. Is there a SQL Server solution for high availability?

With Database Mirroring, we can have a replica of the database ready to be used if the main database fails. In this article, we will give a step-by-step tutorial about Database Mirroring. There are other solutions for high availability like high AlwaysOn, Clusters and Log Shipping, but in this chapter, we will start with Mirroring.

Read more »
Steve Simon

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

March 4, 2015 by

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.

Read more »
Steve Simon

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

February 27, 2015 by

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 1st and ended June 30th). 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.

Read more »
Steve Simon

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

February 23, 2015 by

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 »