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 »
Milica Medic

Database table partitioning in SQL Server

April 4, 2014 by
 

What is a database table partitioning?

Partitioning is the database process where very large tables are divided into multiple smaller parts. By splitting a large table into smaller, individual tables, queries that access only a fraction of the data can run faster because there is less data to scan. The main of goal of partitioning is to aid in maintenance of large tables and to reduce the overall response time to read and load data for particular SQL operations.

Read more »
Milena Petrovic

SQL query performance killers – understanding poor database indexing

March 31, 2014 by
SQL Server performance is affected by many factors. The most common SQL Server performance killers are poor database design, poor indexing, poor query design, not reusable execution plans, frequent query recompilation, excessive fragmentation, and more. With these factors present on your machine, even adding additional hardware resources might not help, as these SQL Server performance killers can use all available resources. Read more »
Ivan Stankovic

SQL Server security and Policy Based Management – Alerting

March 24, 2014 by
In the previous article related to the SQL Server Policy Based Management feature, we discussed and provided steps and tips to create conditions and policies that help maintaining SQL Server security in large enterprises with a number of SQL Server instances. Now, we are going to go further as once set policies are not much of a use if their violations are left unnoticed. Read more »
Marko Radakovic

Using an XML file to configure an SSIS package

March 16, 2014 by
To configure an SSIS package means to choose property/value pairs added to a completed package, and include them in the configuration file for the further modifications. If for any reason some of the values has been changed after the deployment is finished and the package run, they can be added later, opening the package in Business Intelligence Development Studio (BIDS), and changing the values Read more »
Milica Medic

SQL Database design: Choosing a primary key

March 16, 2014 by
A primary key is a column or a combination of columns that uniquely defines each row in a table. When designing a database choosing a primary key is one of the most important steps. A table can contain one or more columns suitable to be a primary key and that column or columns are called a candidate key. Only one candidate key can be selected as a primary key Read more »