Maintenance

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 »

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 »
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 »
Sifiso W. 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 »
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 »
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 »
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 »