Getting Started with Data Quality Services of SQL Server 2012 Using SSIS

In my earlier articles, I talked about the Data Quality Service (DQS) in SQL Server 2012, which helps ensure data integrity and quality by data profiling, matching, cleansing, correcting and monitoring overall status of the data cleansing process. We also talked about using the interactive Data Quality Client tool and in this article we will walk through how to do data cleansing in a automated mode using SSIS, for more information click here.

Creating a knowledge base and cleansing data using Data Quality Services in SQL Server 2012

This is third article of the series. In the first article I talked about data quality challenges and its source, then I talked about the Data Quality Service in SQL Server 2012 and its processes. In the second article, I talked about different components of DQS and how to go about installing and configuring DQS and then I talked, how DQS works. In this article, I am going demonstrate step by step how you can create a knowledge base and do data cleansing, for more click here.

Getting Started with SQL Server 2012 Data Quality Services

The amount of data is increasing on a regular basis and so is the responsibility to ensure the quality of data remains intact. Data quality issues consist of inconsistency, incompleteness, invalidity, inaccuracy, duplicates, etc. These data quality issues can arise from different sources like erroneous data entry, corruption in transmission or storage, data consolidation issues from different sources with different standards or formats, etc. These types of issues can lead to incorrect data analysis, data mining and reporting, which ultimately impacts the business. I have heard that SQL Server 2012 has a brand new feature called Data Quality Service (DQS) which greatly helps to ensure the data integrity and quality by data profiling, matching, cleansing, correcting data and monitoring overall status of the processes. Let's explore this feature in this tip series:

Getting Started with SQL Server 2012 Data Quality Services - Part 1

Getting Started with SQL Server 2012 Data Quality Services - Part 2

Getting Started with Master Data Services (MDS) In SQL Server 2012

Master Data Services is a new feature introduced in SQL Server 2008 R2 and further enhanced in SQL Server 2012. Master Data Services is an extensible Master Data Management (MDM) platform, which is built on top of the SQL Server database engine and Windows Communication Foundation (WCF).

MDS allows you to create a centralized hub for creating and managing enterprise master data and provides many functionalities, to learn more click here.

Snapshot Isolation Level in SQL Server - What, Why and How Part 2

Introduced in SQL Server 2005, Snapshot Isolation levels improve performance, however, there are some caveats to consider when using this feature. Some people use it frequently, as it minimizes blocking and improves performance/concurrency, without knowing its impact on maintaining versions in tempdb. Others stay away from it because of this extra overhead. Some people are confused by the two variants of snapshot isolation level (Read Committed Snapshot Isolation (RCSI) and Snapshot Isolation (SI)) and use one variant where the other is needed or vice versa. In my last article (Snapshot Isolation Level in SQL Server - What, Why and How – Part 1) I talked about basic of isolation levels and snapshot based isolation levels and how they differ from each other in terms of performance and cost; in this article I am going to demonstrate how to use these isolation levels with examples; for more information click here.

Snapshot Isolation Level in SQL Server - What, Why and How - Part 1

Snapshot Isolation level was introduced in SQL Server 2005 and has been available ever since. Snapshot isolation levels improve performance but there are couple of things to take into consideration when using this feature. Some people use it frequently as it minimizes blocking and improves performance/concurrency without knowing its impact on maintaining versions in tempdb, whereas some people stay away from it because of this extra overhead. Some people get confused about the two variants of snapshot isolation level (Read Committed Snapshot Isolation (RCSI) and Snapshot Isolation (SI)) and use one where the other is needed or vice versa.  In this article, I am going to discuss what snapshot isolation levels are, their variants, why and when we should use them and how we should start using this feature with examples, for more information click here.

Managing and Connecting to AlwaysOn Availability Groups

There are several ways to effectively manage and monitor availability groups, replicas and databases in SQL Server 2012 but the most efficient is the inbuilt dashboard which displays information in very a intuitive way for high-level monitoring and management. In addition, you can also use the newly introduced catalog views and dynamic management views, extended events as well as system performance counters, for more information click here.

Configuring and Creating An AlwaysOn Availability Group in SQL Server 2012

In the previous article on AlwaysOn Availability Groups in SQL Server 2012, we looked at The Environmental Setup for An AlwaysOn Availability Group. In this article we turn our attention to the creation and configuration of the Availability Group. For more information, click here.

SQL Server 2012 AlwaysOn Availability Group Environmental Setup

Continuing from AlwaysOn Part 1 the series continues with a step-by-step guide to setting up the environment for an AlwaysOn availability group. In this article we will break down AlwaysOn environment preparation into three steps :
1. Installation of SQL Server 2012 on each node/replica/server.

2. Installation of the failover clustering feature on each node/replica/server.

3. Creation of a failover cluster and joining all nodes/replicas/servers to the cluster.
For more information click here.

Using The AlwaysOn Feature of SQL Server 2012

Prior to SQL Server 2012, SQL Server had several high availability and disaster recovery solutions for an enterprise’s mission critical databases such as failover clustering, database mirroring, log shipping or combinations of these. Each solution typically has a major limitation, in the case of failover clustering for example, its configuration is very tedious and complex and you arguably have single shared storage or single point of failure. Database mirroring is relatively easy to configure in comparison with failover clustering, but you can have only one database in a single mirroring setup and you cannot read from the mirrored database. Log shipping does not provide automatic failover (higher availability) though it be used for disaster recovery with some expected data loss.

SQL Server 2012 (Code name SQL Server Denali) introduces a new feature called AlwaysOn which combines the best of failover clustering and database mirroring and overcomes major of the limitations imposed in failover clustering or a database mirroring setup, for information click here.

New User-defined Server Roles in SQL Server 2012

As we all know, we have fixed server roles (total nine) in SQL Server and if we make a group of DBAs a member of sysadmin, so that they can query DMVs and run traces/profile, we are actually giving them more than the permissions needed. Being part of sysadmin, they will have complete control on the instance. Unfortunately there is no way to change these fixed server roles to limit/give more permissions than needed in earlier versions of SQL Server. This means there is no way to separate DBAs into groups according to their roles, other than those that are available as fixed server roles. Starting with SQL Server 2012, apart from using the fixed server roles, we can also create user defined server roles and assign only server level/scope permissions needed to this role, for more information click here.

New Built-In TSQL Functions in SQL Server 2012

SQL Server 2012 (Code named SQL Server Denali) introduces 14 new built in functions in four different categories with one existing function (LOG) being modified to have an optional second base parameter, for more information click here.

Contained Database Authentication feature in SQL Server 2012

SQL Server 2012 introduces a new feature called Contained Database Authentication. The Contained Database Authentication feature allows the database to partially contain the data that was previously stored outside the database. In other words, once you enable the containment feature at the SQL Server instance level, you can create a database that can store user information/credentials inside the database itself (rather than having a login at instance level, which gets stored in the master database) and hence you don't need to create logins on the target instance (and do mapping of SID) again during database movement. Quite a relief...isn't it? For more information click here.

Usage and Benefits of Using WITH RESULT SETS In SQL Server 2012

SQL Server 2012 (Code name SQL Denali) introduces the WITH RESULT SETS clause with the EXECUTE statement, which lets you redefine the name and data types of the columns being returned from the stored procedure. This comes in very handy when you want to insert the records coming in a result set to a temporary table with a specific column name and data type and you don’t want to rely on what is being returned by the stored procedure.
The WITH RESULT SETS clause can also be used with a stored procedure, which returns multiple result sets and for each result set you can define the column name and data types for each column separately, for more information click here.

Managing and Monitoring SQL Server Service Broker Environments

Service Broker applications run in the background. You send a message and your command returns immediately. In the background, Service Broker keeps on trying to send the message to the destination service (Queue) until it puts the message there, times out or you end the conversation. These things all happen transparently to you and your sending application. So how would you troubleshoot your Service Broker applications if they're not working as expected, and how would you identify if something goes wrong? For for information click here.

Understanding Sequence Object in SQL Server Denali

Until SQL Server 2008 R2, the Identity column was used to generate sequential numbers to identify records in a table, mostly used as a primary/foreign key. The scope of the identity column is the table on which it has been created, and the next sequential number is created when the DML statement is executed. But what if you want to have sequential generation of numbers across tables (instead of tying the numbers with just one table), and you want to have the next sequence number even before execution of the DML statement? SQL Server Denali has a new feature called Sequence object for these purposes, while retaining Identity column functionality too, for information click here.

SSIS Package Deployment Model in SQL Server 2012 - Denali

Deployment has always been a challenge for SSIS developers to deploy packages. SSIS developers are envious of SSRS/SSAS developers as they have an easy way to create a single unit of deployment (deployment package) that contains everything needed for the deployment. The good news is the inclusion of the SSIS Package Deployment Model in SQL Server 2012 code name Denali. In these articles I cover what it is and how to get started to simplify your SSIS package deployments.

SSIS Package Deployment Model in SQL Server Denali (Part 1 of 2)

SSIS Package Deployment Model in SQL Server Denali (Part 2 of 2)

SSIS Enhancements in SQL Server 2012 - Denali

I heard there are couple of enhancements in SQL Server Denali for SSIS. What are these enhancements and how does improves the usability, productivity, manageability and performance? Check out this articles to learn more.

SQL Server Denali SSIS Enhancements Part 1 of 2

SQL Server Denali SSIS Enhancements Part 1 of 2

SQL Server Denali THROW statement to raise an exception

Raising exception with THROW statement in SQL Server Denali

Prior to SQL Server 2005, writing code for exception handling was painful for T-SQL developers. SQL Server 2005 introduced structured exception handling with BEGIN TRY...BEGIN CATCH blocks. Though this inclusion made managing exceptions in T-SQL programming quite easier, it has some limitations. In this tip I am going to talk about some of these challenges and limitations, then show how a new command THROW in SQL Server Denali overcomes those items, click here for more info.

Understanding Ad-hoc paging in SQL Server Denali

SQL Server Denali Server Side Paging

When designing a User Interface (UI) you normally display 10, 20, 50 or 100 rows on the screen for readability purposes and easy navigation. One way to do this, is to bring all the data from SQL Server to local memory and do the navigation locally. This approach looks simple, but has a couple of issues. First, it could choke the network during a huge data transfer from the server to the local machine (or machine running UI). Second, you could need much more memory to accommodate the data locally. Third, when you navigate to the next page you are actually viewing data stored locally and you will not be able to see updated records on the server until you re-fetch all the data again. So what are some options for server side paging and how does SQL Server Denali improves on this process? Check out this tip to learn more.