Régis Baccaro

Automating database tests with Visual Studio and Team Foundation Server

October 31, 2014 by

This is the third post in the series about database development and testing using SQL Server Data Tools and Team Foundation Server.

Post 1: Continuous Integration with SSDT and TFS

Post 2: Unit testing with SQL Server Data Tools

You can run test in Visual Studio to test the quality of your build. In VS 2012 and 2013 there are 5 kind of tests that are available for the user Read more »

Daniel Calbimonte

SQL Server Database Snapshots

October 30, 2014 by

Introduction

MS SQL Server 2005 and later versions include the Database Snapshot feature to have snapshot of the database for reports, as a copy in different periods.

The Database Snapshot can be created multiple times and it can only be created using the T-SQL.

In this article, we will show how to create a Database Snapshot, how to see the snapshot created in the SQL Server Management Studio (SSMS), how to recover objects dropped or data removed using the snapshot. Read more »

Steve Simon

How to clean data using Data Quality Services and SQL Server Integration Services

October 29, 2014 by

Introduction

A year or so ago, I worked for an online web grocery software house located in the northern United States. At that time I had my ‘baptismal’ exposure to ‘genuinely dirty data’. Granted most of the data entry was done manually and many times from offshore. The point being that I could not fathom just how many ways there were to spell the brand name of a major cereal manufacturer. Why is this such an issue? The answer is fairly straight forward. Imagine the scenario that you are trying to ascertain the dollar value of breakfast cereals sold in the country from the local supermarket standpoint all the way up to national sales. Imagine this utilizing a SQL Server Multi-dimensional cube. The ‘eagle – eyed’ reader will recognize that the results will not aggregate correctly should our aggregation attributes have a plethora of different ways of being spelt. Read more »

Derik Hammer

Backup testing with PowerShell – Part 2: Reporting results

October 22, 2014 by

Now that Karla’s restore process is mature, it is time to pre-stage the means of reporting on these events. Two major actions have been taken for each database tested. First a restore operation occurred. The restore operation validates that the backup file is well formed and that there is nothing wrong with the data which would cause a failure. Next CheckTables was executed to thoroughly inspect for data corruption and any consistency errors. Each of these two actions will have their own methods of providing evidence of successful completion. Recall the requirements set by Karla, in part 1 of this series, for the reporting piece. Read more »

Derik Hammer

Backup testing with PowerShell – Part 1: The test

October 21, 2014 by

Karla is a production database administrator and she has a lot in common with you. Being responsible for database backups and recovery, she has implemented a well-structured automated backup system. Maybe she’s using Ola Hallengren’s Maintenance Solution, custom T-SQL stored procedures, or a set of PowerShell scripts. She has heard the saying, “a DBA only needs one thing, either a backup or a resume, but never both,” and is confident that she won’t be dusting off the resume any time soon. Read more »

Steve Simon

Quick and dirty server monitoring with SQL Server Reporting Services

October 8, 2014 by

Introduction

A few month back, I found myself in a position where the client wanted a ‘monitoring tool’ to utilize on a daily basis to ascertain the status of SQL Server Systems and to continually monitor disk space capacity. Being a typical Monday morning quarter back and utilizing my favorite SQL Server Tool, SQL Server Reporting Services, I came up with the following ‘ah-ha’ solution. Read more »

Kenneth Fisher

Intro to Auditing in SQL Server

September 30, 2014 by

In the world of information, auditing serves an important purpose. It helps to provide an assurance that the data involved is accurate and safe. The level of assurance of course depends on any number of factors including the level of trust in those performing an audit (or collecting the data for the audit), the frequency the data is collected, and the types of data collected.

Any time the collection of data is this important to a process you can bet that a DBA is going to be involved. We get asked to create and run queries to pull data (frequently ridiculously complex queries, and rarely some simple ones). You could almost make it into a joke. Read more »

Evan Barke

SQL Server Commands – Dynamic SQL

July 4, 2014 by
Warning: This article contains examples of SQL injection. The example queries can be harmful to data and are run on the AdventureWorks2012 database DO NOT run equivalents on your production database. Backup your AdventureWorks2012 database if you don’t want to lose data. Please note that the techniques are purely for education purposes and we do not condone the use of them for any other purpose. Read more »
Sifiso W. Ndlovu

Background to exception handling in SQL Server

June 26, 2014 by
Since the advent of SQL Server 7.0, the best way of raising T-SQL related errors back to calling applications has been through the usage of the RAISERROR statement. Figure 1 demonstrates a combination of mandatory parameters (i.e. msg_id/msg_str, severity, state) as well as optional parameters (i.e. WITH option) that ought to be provided for the purposes of successfully using the RAISERROR function. Read more »
Evan Barke

SQL Server cursor performance problems

June 18, 2014 by

Introduction

In a previous article we discussed the how to set up a basic cursor. We explained that a cursor is a row-based operation that takes a given SELECT statement and breaks downs the processing of the data into looping executions. People that do not have the background in Database Administration or who did not enjoy database classes while studying will often find SQL Server cursors handy and fun to write. This is because they break free of the shackles of set-based logic that is the normal when writing most T-SQL scripts. It is for this reason that we so often find SQL Server cursors written into the business logic of an application and it is a real pity because they are real performance hogs. Yes, there are times when cursors are OK to use and they can be tuned slightly by playing with their different types but, as a general rule of thumb, one should try to avoid them at all costs. Read more »
Muhammad Imran

SQL Server Management Studio tutorial – Configuring the environment

June 11, 2014 by
Configuration of the application plays a significant role in any application, when installed. However, some applications are configured at the time of installation (with default settings) while in other cases, manual configuration is done to achieve its real benefits. In the case of SQL Server Management Studio (SSMS), it is configured by default with the standard settings, and it can be further configured to make the work easier.
Read more »
Evan Barke

SQL Server cursor tutorial

June 4, 2014 by

Introduction

Most people that work with Microsoft SQL Server will have at least heard talk of cursors and often, even if people know on a basic level what SQL Server cursors do, they are not always certain when to use them and how to write the code behind them. So this article takes a step back and provides an explanation as to what SQL Server cursors can be used for as well as a basic example that you can run for yourself to test. Read more »
Evan Barke

SQL Server Business Intelligence – Using recursive CTE and persisted computed columns to create a calendar table

June 2, 2014 by

Introduction

Those of you that have worked extensively with dates in SQL Server (or any other relational database management system (RDBMS)) will know how finicky and complicated it can be to use DATE functions, DATEPART, DATENAME, DATEADD, GETDATE(), CURRENT_TIMESTAMP etc. Personally, I find it very useful to have a calendar table that stocks all of the necessary, pre-calculated fields in one place. Whether you’re doing BI or web, it can be very helpful to have a fixed ID for a date in order to really optimize your data analysis and processing. Read more »
Evan Barke

SQL Server Business Intelligence – Expanding fact tables in an established data warehouse

May 30, 2014 by

Introduction

As in often the case in life, things that sound simple are not always the easiest things to do. In computer science this is even more often the case. In fact, one of the most challenging things about information technology work is often the communication with bosses that know little about technology and require justification for time spent on seemingly simple tasks. However, by the same token, tasks that seem impossible to the untrained eye are often fairly straightforward and quick to implement and can earn you easy respect. Read more »
Sifiso W. Ndlovu

Columnstore Index in SQL Server

May 28, 2014 by

Prerequisite

The discussion pertaining to SQL Server 2012 columnstore indexing is better explained through theoretical and practical measures. Thus, for the practical measure part – I will be using the AdventureWorksDW2012 sample database. The rest of the prerequisites for a comprehensive understanding of this topic are as follows:
Read more »