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 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 »
Ivan Stankovic

SQL Server backup – models and types

May 26, 2014 by
A SQL Server backup is one of the most important features DBAs should fully understand and use. It’s not uncommon that the process of backing up SQL Server data is considered as simple and therefore easily overlooked as essential when it comes to disaster or other recovery scenarios. Realizing that a backup is not valid (outdated, corrupted, or even does not exist) at the moment when it’s needed is highly undesirable scenario for any DBA. Read more »
Ivan Stankovic

SQL Server Policy Based Management – Categories and Database Subscriptions

May 21, 2014 by
In the recent articles (see below) about SQL Server Policy Based Management, we have explained the terms used by the feature, how it can be used in different environments and for various scenarios. We have also described how its basic purpose, evaluating declared rules against specific SQL Server instances and their objects, can be extended on other aspects of SQL Server such as SQL Server Audit is. Read more »
Evan Barke

SQL Server Business Intelligence features – creating reports based on OLAP cubes

May 19, 2014 by

Introduction

In order to build a SQL Server business intelligence solution one needs to:

  1. Design a de-normalized data warehouse
  2. Build and schedule an Extract, Transform and Load (ETL) package that will feed the data warehouse at regular intervals with new data from the OLTP database.
  3. Setup, personalize and process a cube based on the data warehouse.
  4. Add the processing step to the ETL schedule to ensure the whole chain is automated.
Note: These steps are covered in detail in other articles in the series. If you do not yet have an OLAP cube set up and automated and you need more info as to how to do so; please refer back to the relevant step.
Read more »
Muhammad Imran

SQL Server Management Studio – A step-by-step installation guide

May 14, 2014 by
SQL Server Management Studio (SSMS) is the official and preferred client user interface which can be used to manage, configure, deploy, upgrade and administer a SQL Server instance. The tool interface is quite user friendly and comprehensive. It is shipped with every SQL Server version and is regularly updated and enhanced. If you are planning on learning how to use SQL Server from scratch, consider using SQL Server Management studio as a first step on your journey to learning SQL Server. Read more »
Sifiso Ndlovu

Introduction to pagination in SQL Server

May 12, 2014 by
Microsoft SQL Server distinguishes between paging and pagination. Paging refers to handling of memory bottlenecks whereas pagination, the focus of this article, refers to dividing T-SQL query result set into discrete parts. The general concept of Pagination is prevalent in various professional disciplines (i.e. journalism, publishing, web development etc.). According to Wikipedia Pagination is the process of dividing content (i.e. website search results, newspaper article etc.) into separate yet related pages. The major benefit to such dividing of content is usually a cleaner and clearer structuring of content which enhances the reading (or browser-navigation) experience to the consumer of such content. Read more »
Evan Barke

SQL Server Business Intelligence Features – Creating a Simple OLAP Cube

May 9, 2014 by

Introduction

Multidimensional cubes and transactional databases are two very different things. From experience I have seen even veteran DBAs avoid the subject of cubes completely because it is too much of an unknown area for them. Often cube work is passed on to developers because of their comfort with using Visual Studio. This is a great pity because it is, in reality, not very difficult at all to create an OLAP cube. It is safe to say that most of the work needs to be done in a traditional SQL Server database engine / SSIS environment from creating the data warehouse model to keeping it fed with ETL packages. This article assumes you already have a data warehouse and uses AdventureWorksDW2012 as an example. Following these steps should put you on the road to a decent SQL Server business intelligence solution based on a read-optimized OLAP cube. Read more »
Ivan Stankovic

SQL Server Policy Based Management – applying policies to non-compliant targets

May 8, 2014 by
SQL Server Policy Based Management provides means to declare certain states and properties for SQL Server instances and their objects in a form of policies. These policies can be evaluated against a set of SQL Server instances, or against a specific SQL Server instance and a set of its objects that can be narrowed even more using additional policy conditions. However, even an evaluation of declared policies against their targets (SQL Server instances and their objects) produces viable results, in a form of evaluation reports that shows which target does and which one does not comply with the policy, this is not enough. After non-compliant targets are identified, they need to be altered in order to comply with the evaluated policies. Read more »
Ivan Stankovic

SQL Server Policy Based Management – best practices

May 5, 2014 by
In SQL Server Policy Based Management, best practices represent guidelines in a form of Policy Based Management policies that are in common SQL Server use scenarios considered as the best way to configure SQL Server instances and their objects. For example, it is considered a best practice for most SQL Server environments to use and enforce Windows Authentication mode. Violations of these policies can indicate SQL Server configurations that can result in low reliability, poor performance, increased security risks, unexpected conflicts, or other potential problems. Read more »
Evan Barke

SQL Server Business Intelligence Features – SQL Server Data Tools – Business Intelligence

April 30, 2014 by

Introduction

In our previous article on the introduction to SQL Server business intelligence we covered the general structure of an enterprise business intelligence solution. The tools needed to build these solutions were briefly mentioned. The purpose of this article is to provide you with a deeper understanding into the creation of an ETL (Extract, Transform and Load) dataflow. To do this one needs to use SQL Server Data Tools – Business Intelligence (previously known as BIDS or Business Intelligence Development Studio). In this article we’ll take a look at the basic functionality of SQL Server Data Tools and how to use it to keep your data warehouse up to date. It’s worth noting that there are many different ways to go about building your ETL solution. This article gives sound advice and pointers as to how to approach the problem. Read more »
Milena Petrovic

Inaccurate SQL Server statistics – a SQL query performance killer – updating SQL Server statistics

April 28, 2014 by
As shown in the previous part of this series, inaccurate statistics can degrade SQL Server performance. We described how to work with SQL Server statistics using SQL Server Management Studio options and T-SQL. In this article, we will show how to update SQL Server statistics, what are the updating costs, and when updating is recommended. Read more »
Ivan Stankovic

SQL Server Policy Based Management – The On Change evaluation mode

April 25, 2014 by
The Policy Based Management feature we described in the series of articles (see below) provides an efficient method to declare and enforce policies related to SQL Server instances and their objects. The main purpose of the feature is to facilitate maintenance of a single and, what’s more important, multiple SQL Server instances across the enterprise. Policies can be created on one SQL Server instance, and applied to other SQL Server instances afterwards, as explained in details in the SQL Server Policy Based Management – evaluating policies on multiple SQL Server instances article. Read more »
Sifiso Ndlovu

Sequence objects feature in SQL Server

April 24, 2014 by

Introduction to Sequences

Sequence is a new object that was introduced in SQL Server 2012 for a key-generating mechanism. It has been made available across all editions of SQL Server 2012. It was added as an alternative to IDENTITY columns feature that has been prevalent in the previous versions of SQL Server. Despite being newly introduced in SQL Server 2012, sequences have long been prevalent in other database platforms such as Oracle and IBM’s DB2. Thus, the data migration into SQL Server 2012 from other database platforms is now more convenient and simplified.
Read more »
Evan Barke

SQL Server Business Intelligence – Introduction

April 23, 2014 by

What a load of Bl…

You may be wondering what Business Intelligence is. If you’re reading this article you probably have prior experience with Microsoft SQL Server or at least one other relational database management system (RDBMS). If this is the case you’re probably used to managing a lot of valuable data. If you’re a DBA you may be used to maximizing performance by rewriting stored procedures, creating indexes or running profiling traces.
Read more »
Milena Petrovic

Frequent query recompilations – a SQL query performance killer – detection

April 11, 2014 by
In the previous part of this article, we presented query recompilation basics. We explained when a query is compiled, when recompiled, and what preferred behavior is. We also showed the how to trigger a recompilation using T-SQL and provided examples. In this part, we will focus on detecting frequent query recompilations and frequently recompiled queries. Read more »