Monday, December 27, 2010

Register and Delete Data-tier Applications in SQL Server 2008 R2

SQL Server 2008 R2 allows its databases to be registered as Data-tier Applications In-Place; in other words without creating a DAC package and then deploying it. You can delete a registered Data-tier Application from SQL Server 2008 R2 instance in the same way. There are two ways to register and delete a Data-tier Application i.e. using the wizards available in SQL Server 2008 R2 Management Studio (SSMS) and using PowerShell commands, though in this article I will be talking about using these wizards, click here for more details.

Upgrading Data Tier Applications in SQL Server 2008 R2

In my last article, Creating Data Tier Applications in SQL Server 2008 R2, I gave you an overview of Data Tier Applications, then I talked about how it differs from database projects and finally I showed how you can create a DAC package using Visual Studio 2010 and SQL Server 2008 R2 Management Studio (SSMS). In another article, Deploying Data Tier Applications of SQL Server 2008 R2, I talked about the different methods of deploying Data Tier Applications.
Changes are inevitable and like many other things in life, your application will also change over time, either to add new objects, update existing objects, drop deprecated objects, etc. So now, the question is how to upgrade an already deployed Data Tier Application to a newer version; what are different methods available for upgrade and what considerations should you take? Click here for more detail.

Deploying Data-Tier Applications of SQL Server 2008 R2

Data-Tier Applications of SQL Server 2008 R2 make database development, deployment and management much easier. When you create a Data Tier Application and build it, it creates a single, self-contained unit of deployment called a DAC package in compressed format that contains SQL Server instance objects that are associated with the database, database objects and deployment intents.
In my last article Creating Data-Tier Applications in SQL Server 2008 R2 I gave you an overview of Data-tier applications, then I talked about how it differs from database projects and finally I showed how you can create a DAC package using Visual Studio 2010 and SQL Server 2008 R2 Management Studio (SSMS). In this article my focus is to deploy the created DAC package and discuss the different methods of deployment, click here for more detail.

Creating Data-Tier Applications in SQL Server 2008 R2

SQL Server 2008 R2 with Visual Studio 2010 has introduced a new feature called Data-Tier Applications (called DAC for short), which makes database development, deployment and management much easier. When you create a Data Tier Applications project and build it, it creates a self-contained unit of deployment called the DAC package (which has a dacpack extension) that contains SQL Server instance objects, which are associated with the database, database objects and deployment intent (also called pre-requisite checks or deployment requirements of the application), for more information click here.

Monday, November 29, 2010

Developer Dashboard in SharePoint 2010

Prior to SharePoint 2010, tracking down performance bottlenecks might involve attaching a debugger to code and enabling a trace from the SQL Server Profiler. Now, with SharePoint 2010, you can use a cool new feature called Developer Dashboard. The SharePoint 2010 Developer Dashboard displays tracing and diagnostic information about page rendering and includes information about page components, controls, queries and execution time (an analogy of ASP.NET page tracing), for more details click here.

Unattached Content Database Recovery in SharePoint 2010

In earlier versions of SharePoint, to restore or recover content from a backup file, we had to restore the backed up file to a database server and had to attach that restored database to a another SharePoint farm. Then we needed to export the required content from this new temporary farm and then migrate it to the original farm where we wanted to recover it. This whole process required a huge time investment and rigorous planning.

SharePoint 2010 removes much of the administrative overhead with the inclusion of a Granular Backup and Restore feature. In the above mentioned scenario, we don't need to setup another temporary farm just to restore some content from the backup. We can simply restore a content database backup on any SQL Server instance (or can create database snapshot of the current database before making changes to the web application) and then use the unattached content database recovery feature from the Central Administration to browse the content of the backup. Then we can export it to a file and import it whenever we want, for more details click here.

Exporting/Importing Site/Library/List in SharePoint 2010

As a SharePoint Administrator, you would often need to deploy content from one site to another. So what are different options available to export the contents of a site, library, or list from SharePoint and to import it back whenever required?
Granular Backup and Restore functionalities in SharePoint 2010 have grown up tremendously from previous versions. Now you can export sites, libraries, and lists from the Central Administration tool itself. You can even use new PowerShell cmdlets to script out export and import operations. PowerShell cmdlets are optimized and provide more new features and options than the old STSADM tool. For more details, click here.

Document Versioning in SharePoint 2010

There might be a time when you would need to restore files back to an older version if any inadvertent change happens. You would also like to track changes for auditing purposes. So how does SharePoint allow creating different versions of documents? How does version history work? What are the different types of versions we can maintain for our library or list and how can we revert back to an older version? For more details click here.

Granular Backup and Restore in SharePoint 2010

In my last article, Backup and Restore in SharePoint 2010, I talked about options to backup a SharePoint farm, web applications and different components and then restore them back whenever required. But what if I have a web application with multiple site collections in it and one of the them is only getting changed frequently hence I want to backup only a specific site collection instead of backing up the complete farm or web application on regular basis? What if I want to create a copy of the site collection to another SharePoint server? What new options are available in SharePoint 2010 for site collection backup and restore?

How Check-in and Check-out work in SharePoint

SharePoint is a collaborative platform. A central feature is the ability to share documents with other users. But what if multiple users try to edit the same document at the same time? This can lead to versioning conflicts and confusion about changes. How can this be prevented?
SharePoint provides a feature called document Check-In and Check-Out. The purpose is to exclusively lock a document for modification by a user. Once the changes are complete the user can check-in the updated document to release the lock and make the changes visible to other users. For more details click here.

Running a SSIS Package from SQL Server Agent Using a Proxy Account

When we try to execute a SSIS package from a SQL Server Agent job it fails with the message "Non-SysAdmins have been denied permission to run DTS Execution job steps without a proxy account." if the account under which SQL Server Agent Service is running and the job owner is not a sysadmin on the box or the job step is not set to run under a proxy account associated with the SSIS subsystem. What is this exception about? What causes it and how do I resolve it? For more details, click here.

Getting Started with Extended Events in SQL Server 2008

In my last article, "An Overview of Extended Events in SQL Server 2008" I gave you an overview of Extended Events (XEvents) in SQL Server 2008, then I talked about its capability in terms of the number of events to trace and its deeper level of troubleshooting and finally I talked about the different components of Extended Events.
In this article, I am going to give some hands on examples to understand how this powerful tracing feature can assist you with your performance problems, for more details click here.

Thursday, October 28, 2010

An Overview of Extended Events in SQL Server 2008

As a DBA, we are faced with performance problems which we need to diagnose, trace and troubleshoot. Although there are several different tracing and troubleshooting mechanisms (DBCC, SQL Trace, Profiler, etc...) available but none of these tools provide deep levels of tracing/troubleshooting, like identifying page splits, high CPU utilization, etc... So how can we diagnose, trace and troubleshoot these kinds of performance problems in SQL Server 2008. In this tip I talk about Extended Events of SQL Server 2008 and how it is different from earlier tracing and troubleshooting methods, for more details click here.

Backup and Restore in SharePoint 2010

As a SharePoint Administrator, you need to set up a backup plan so that you can restore your SharePoint farm or SharePoint web applications without any hassles if a disaster happens or even to create a replica of your production environment for QA and development purposes.
So what are the 'out of the box' Backup and Restore options available in SharePoint 2010? How do we backup the SharePoint farm, web applications and different components? How have Backup and Restore processes have improved from previous versions of SharePoint to SharePoint 2010? for details click here.

Master Data Services (MDS) model Versioning, Permission and Security

Master Data Services (MDS) is a master data management platform, which allows you to create a centralized hub for your master data. This single and centralized authoritative master data source provides consistent master data to all the business applications aligned to it. In this article I am going to demonstrate how model versioning works in MDS and then I will be talking about how security works in MDS, for more details click here.

Friday, October 8, 2010

How to Import, Browse And Export Master Data Service's Data

Master Data Services (MDS) is a master data management platform, which allows you to create a centralized hub for your master data. This single and centralized authoritative master data source provides consistent master data to all the business applications aligned to it. In this article, I am going to demonstrate how you can import data to MDS, browse it and export it via subscription views for consumption by reporting and analytical applications, for more details click here.

Thursday, September 30, 2010

Creating Master Data Services Applications/Objects

Master Data Services (MDS) is a master data management platform that allows you to create a centralized hub for your master data. Keeping master data in an external centralized system as a single authorative source helps to remove several master data challenges. This single authorative master data source acts as a System of Record by integrating master data from all the upstream sources and as a System of Entry by providing consistent master data to all the downstream applications. This article talks about how to get started on Master Data Services, understand different components of it and create master data services' objects. For details, click here.

Monday, September 27, 2010

Event Notifications in SQL Server

SQL Server 2005 and onwards provides event notifications mechanism for tracking all events or changes happening at the database or instance level. This can be done with DDL triggers or SQL traces, but event notifications have the advantage of being asynchronous in nature and running outside of the scope of the transaction and hence it can run inside (or be a part of) a database application to capture defined events without using the resources allocated to the transaction. For more details, click here.

Thursday, September 16, 2010

SQL Server - Physical Joins Operators

We use logical operators when we write queries to define a relational query at the conceptual level (what needs to be done). SQL implements these logical operators with different physical operators to implement the operation defined by the logical operators (how it needs to be done). Although there are dozens of physical operators but for logical joins it uses three physical join operators. Although we have different kinds of logical joins at the conceptual/query level, but SQL Server implements them all with three different physical join operators, click here for details.

Getting started with SQL Server 2008 R2's Master Data Services

The Master Data Management (MDM) platform, called Master Data Service (MDS), is built on top of the SQL Server 2008 R2 database and Windows Communication Foundation (WCF). This article talks about what it is in detail, then offers a step-by-step guide to installing and configuring this new capability, called Master Data Services (MDS), click here for more details.

A First Look at SQL Server 2008 R2 Master Data Services

Redundancy, caused by integrating multiple definitions of master data across different lines of business into a data warehouse can cause reports to depict inaccurate results. Master Data Services, introduced in SQL Server 2008 R2, overcomes this problem, allowing you to create, organize and manage master data centrally. This article talks about Master Data Management, Master Data Services (MDS), MDS Architecture, MDS components etc click here for detail.

Tuesday, September 7, 2010

Configuring Proactive Caching in SQL Server

The proactive caching feature ensures a cube or a measure group partition or dimension reflects current data on its own. Depending on your choice of proactive caching enabled storage mode, interception of data change notification, storage location and update frequency will vary.

You use the Storage Settings dialog box in BIDS (Business Intelligence Development Studio) to set the proactive caching feature, storage location, and notification settings for a dimension, cube, measure group, or measure group partition, for more details click here.

Introduction to SQL Server Proactive Caching

Mostly data warehousing and Business Intelligence (BI) applications work on historical data, which are normally refreshed once in a daily, weekly or monthly basis. Although there are some near real time data warehousing and BI applications, their query performance is painfully slow. To help in this scenario, Analysis Services (Microsoft SQL Server 2005 Analysis Services and later versions) provides an amazing feature called "Proactive Caching". This new feature gives a query performance, similar to what you had with historical data, with near real time data and minimal impact on the overall system. As a BI developer, you just need to configure proactive caching, and then SQL Server Analysis Services (SSAS) will ensure that you get the performance of a fully processed data warehouse on near real time data, for more details click here.

Manage Database Projects With Visual Studio 2010

Prior to Microsoft Visual Studio 2005, managing database projects had been a real challenge. Microsoft Visual Studio 2005 Database Professional Edition (also known as Data dude) simplified this process and Microsoft Visual Studio 2008 Database Edition GDR 2 further, along with several bug fixes, enhanced its capability to support newest feature of SQL Server 2008. New release i.e. Microsoft Visual Studio 2010 brings a whole lot of new features and enhancements compared to its earlier versions. For example, you can now connect to SQL Server from Visual Studio IDE (Integrated Development Environment) itself with new in-built T-SQL Editor without actually going to SQL Server Management Studio (SSMS), you will get T-SQL Intellisence support while writing or modifying your T-SQL code etc. To learn more click here.

Tuesday, August 31, 2010

SQL Data Comparison with Visual Studio 2010

We often need to compare and synchronize the data that tables (which have the same structure) contain. For example we normally define some master data in a development environment during development and would like the same data to be deployed/inserted/updated to Test/QA/UAT/Production environments after development. So how can we do this, how we can compare data of the tables between different databases and generate data synchronization or incremental data deployment scripts? Click here for more details...

SQL Schema Comparison with Visual Studio 2010

Often we need to compare databases across two different environments (or even sometimes in the same environment) to identify the schema object differences, so that one database can be brought in sync with another one. For example, we normally do development on the development box; then during the build preparation we compare the development database with the QA/UAT/Production database to identify differences and generate schema objects for synchronization or incremental deployment scripts. How we can compare schema objects between databases and generate synchronization or incremental deployment scripts? Click here for more details...

Understanding and creating libraries in SharePoint 2010

A library is kind of container/list for creating, organizing and managing different types of documents/contents and share it with other team members. It allows to store files and meta information about the files so that it can be used among different team members. You can create, store and manage almost every type of file in different kind of libraries for example word documents, spreadsheets, presentations, forms etc. By default a Shared Library is created when you create a site and then on additionally you can create as many as type of libraries you want. This article talks abour different types of libraries and covers creating/managing document library in details, for more details click here.

Creating Web Applications, Site Collections and Sites in SharePoint 2010

SharePoint 2010 introduced several new features, one of them is enhancement to Central Administation site. This article discusses about using SharePoint 2010 Central Administration to create web application, site collections and sites and how they relate to each other. To learn more, click here.

Instant File Initialization for SQL Server 2005/2008

This article discusses Database Instant File Initialization, which instructs the OS to allocate the required disk space immediately before actually zero-ing out the content from the allocated space. This means the content of the allocated disk area remains there until SQL overwrites it, improving the performance and reducing the downtime. Read on to learn more...

Thursday, August 5, 2010

Developing Service Broker Application when Initiator and Target are in same database

SQL Server Service Broker (SSBS), introduced with SQL Server 2005 and enhanced in SQL Server 2008, allows you to write queuing/message based applications within the database itself. In this article, I will be creating an Service Broker application in which Initiator and Target both are in the same database, for more details click here.

Transfer Jobs Task and Transfer Logins Task in SSIS

Making a replica of your production server to create a dev or test environment or moving your production data to new hardware are tasks a DBA needs to perform. So what are different methods to perform these tasks efficiently and without much effort? In this article I am going to demonstrate how you can use the Transfer Jobs Task and the Transfer Logins Task to perform some of these operations, for more details click here.

Transfer Database Task and Transfer SQL Server Objects Task in SSIS

Making copies of databases, moving databases to another SQL Server instance and transferring SQL Server objects to another database are frequent tasks a DBA performs. This article discusses how you can perform these operations by using the Transfer Database Task and Transfer SQL Server Objects Task in SSIS without having to write any code, for more details click here.

Friday, July 9, 2010

Installing and Configuring Windows SharePoint Service (WSS) 3.0

Windows SharePoint Service is an add-on service of the Windows Server OS which provides collaborative development, content management, search features, etc., without any additional licensing. So how do we install this service, configure it and get started to leverage these powerful features? Installation cane be done either in...click here for details.

Window SharePoint Service (WSS) 3.0 Vs Microsoft Office SharePoint Server (MOSS) 2007

Window SharePoint Service (WSS) 3.0 and Microsoft Office SharePoint Server (MOSS) 2007 both provide a platform for communication, collaborative development, content management, search capability etc. But how do they differ from each other, what benefits do each of them provide over other, in which scenario to use WSS and in which to use MOSS? To learn about these, click here.

Tuesday, July 6, 2010

Using a Data Access Application Block (DAAB)

Microsoft Enterprise Library provides several reusable software components (application blocks) which help developers simplify common development tasks, minimize development efforts and help address common problems related to performance/optimization/reusability release over release. These components are provided along with source code which developers can use "as-is" or customize per their projects need. Data Access Application Block (DAAB) is one of these components which simplifies the data access mechanism. Data Access Application Block provides a consistent data access mechanism to execute both Stored Procedures and inline SQL statements across projects/enterprises along with transaction support also. For more details, click here for Part1 article and click here for Part 2 article.

SQL Server Service Broker Components - Service, Conversation, Conversation Groups, Routes, SEND and RECEIVE Commands

In my last article SQL Server Service Broker Components - Message Types, Contracts and Queue, I discussed the Initiator, Target, Message Types, Contract and Queue components of SSBS. In this article I will be talking about the Service, Conversation, Conversation group, SEND and RECEIVE commands, Route and how these components are related to each other. For more details, click here.

Tuesday, June 29, 2010

SSIS Multicast Transformation vs Conditional Split Transformation

In a data warehousing scenario, it's not rare to replicate data of a source table to multiple destination tables, sometimes it's even required to distribute data of a source table to two or more tables depending on some condition. For example splitting data based on location etc. So how we can achieve this with SSIS? SSIS provides several built-in transformation tasks to achieve these kinds of , for details click here.

Tuesday, June 8, 2010

Writing a Data Access Layer (DAL) in ADO.NET

Almost every application being developed stores data in some or other form to make it persistent, this could be either storing it in file system, Excel sheet, XML format or in relational database. So each time, when you develop an application, you are required to write code for storing and retrieving data from these data sources.
In this article, I am going to show you how you can create a Data Access Layer (to store, retrieve and manage data in relational database) in ADO .NET. I will show how you can make it data provider independent, so that you don't have to re-write your data access layer if the data storage source changes overtime and also you can reuse it in other applications that you develop. For details, click here.

Friday, June 4, 2010

SQL Server 2008 System Functions to Research Tables, Views, Indexes, Columns, Stored Procedures, etc.

SQL Server provides several system meta data functions to get property values for different object types. Each system meta data function has a set of properties you can query using the system meta data function. In this article, I am going to outline examples on the following functions, for details click here.

  • OBJECTPROPERTY and OBJECTPROPERTYEX
  • INDEXPROPERTY
  • COLUMNPROPERTY

SQL Server Service Broker Components - Initiator, Target, Message Types, Contracts and Queue

In my last article, I talked about SQL Server Service Broker at introductory level. In this article I am going to cover some of the components of Service Broker viz. Initiator, Target, Message Types, Contract and Queue, for more details click here.

Wednesday, May 5, 2010

SQL Server Service Broker - An Introduction

SQL Server Service Broker (SSBS or SSB) is a new architecture (introduced with SQL Server 2005 and enhanced further in SQL Server 2008) which allows you to write asynchronous, decoupled, distributed, persistent, reliable, scalable and secure queuing/message based applications within the database itself. This integration of queuing and message based infrastructure into the database provides several advantages from other message/queuing based applications out of the database. In this introductory article on SSBS, I discuss SSBS on a very basic level, how it differs from MSMQ, SSBS architecture and its different components. For more details, click here.

Thursday, April 29, 2010

SQL Server 2008 System Functions to Monitor the Instance, Database, Files, etc.

SQL Server provides several system meta data functions which allow users to obtain property values of different SQL Server objects and securables, for example
  • SERVERPROPERTY
  • DATABASEPROPERTY
  • FILEGROUPPROPERTY
  • FILEPROPERTY
  • OBJECTPROPERTY
  • COLUMNPROPERTY
  • INDEXPROPERTY
  • CONNECTIONPROPERTY
  • SESSIONPROPERTY
Although you can also use the SQL Server catalog views or Dynamic Management Views to obtain much of this information, in some circumstances the system meta data functions simplify the process. In this tip I am going to demonstrate some of the available system meta data functions and their usage in different scenarios, for more details click here.

Understanding Dynamic Management Views and Functions in SQL Server 2008

Performance monitoring and optimization is an inseparable part of a DBA's activities. To optimize a poorly performing system/query or to troubleshoot the performance issues you need to know the root cause. Prior to SQL Server 2005, we had several system tables to monitor the state of the system. Though these system tables are still available in SQL Server 2005 and in later versions but SQL Server 2005 introduced whole new set of Dynamic Management Views (DMV) and Dynamic Management Functions (DMF) to monitor the health of a SQL Server instance, diagnose and tune the performance issues. For details, click here.

SQL Server CROSS APPLY and OUTER APPLY

SQL Server 2005 introduced the APPLY operator, which is very much like a join clause and which allows joining between two table expressions i.e. joining a left/outer table expression with a right/inner table expression. The difference between join and APPLY operator becomes evident when you have a table-valued expression on the right side and you want this table-valued expression to be evaluated for each row from the left table expression. In this tip I am going to demonstrate what APPLY operator is, how it differs from regular JOINs and what are few of its applications, for more details click here.

Thursday, March 4, 2010

Understanding Catalog Views in SQL Server 2005 and 2008

Understanding Catalog Views in SQL Server 2005 and 2008
SQL Server stores meta data about its configuration options, objects, data type, constraints, etc. in system tables, which cannot be directly queried in SQL Server 2005 and 2008. To access this meta data, SQL Server offers several options. The option I have selected in this tip is the Catalog Views. These provide the most efficient and generic interface to obtain, transform and present customized forms of the persisted system meta data. In this article, I am going to discuss in details about Catalog Views and how they differ from other options like using Compatibility Views, Information Schema Views, etc click here for more details.

SQL Server 2005 and 2008 Ranking Functions - DENSE_RANK and NTILE

SQL Server 2005 and 2008 Ranking Functions - DENSE_RANK and NTILE
SQL Server introduced four new ranking functions either to rank records in a result-set or to rank records within groups of records of a result-set. With these new functions, we are no longer required to write several lines of code to get ranking data. It does not only help in simplifying the query but also improves the performance of the query. So now the questions are: what are these ranking functions, how they work and how they differ from each others? This article talks about DENSE_RANK and NTILE in detail, click here for more details.

SQL Server 2005 and 2008 Ranking Functions - Row_Number and Rank

SQL Server 2005 and 2008 Ranking Functions - Row_Number and Rank
SQL Server introduced four different ranking functions either to rank records in a result-set or to rank records within groups of records of a result-set. With this inclusion we are no longer required to write several lines of code to get ranking. It does not only help in simplifying the query, but also improves the performance of the query. So now the questions are, what are these ranking functions, how do they work and how do they differ from each other? This article talks about Row_Number and Rank in detail, click here for more details.

Different strategies for removing duplicate records in SQL Server

Different strategies for removing duplicate records in SQL Server
In data warehousing applications during ETL (Extraction, Transformation and Loading) or even in OLTP (On Line Transaction Processing) applications we are often encountered with duplicate records in our table. To make the table data consistent and accurate we need to get rid of these duplicate records keeping only one of them in the table. In this article I am going to discuss different strategies which you can take for this, along with its pros and cons. Click here for more details.

Iterating through SQL Server databases and database objects without cursors

Iterating through SQL Server databases and database objects without cursors
There are times when you need to loop through all the databases or database objects to perform some tasks. For example you want to run a DBCC command against all the databases or take backups of all the databases on the server or you want to rebuild all the indexes of all the tables in the databases or you want to know the size of each table in a database. The simplest approach would be to create a cursor and loop through it, which requires you to write several lines of code. Is there any way to simplify the coding efforts for these kind of works? Click here for more details.

How to upgrade SSIS 2005 Packages to SSIS 2008

How to upgrade SSIS 2005 Packages to SSIS 2008
There are several enhancements in SSIS 2008 such as enhanced lookup transformation, the development environment for Script Task and Script Component changing from VSA to VSTA, etc. If you plan to upgrade your SSIS 2005 packages to SSIS 2008 it may be a challenging task especially if it uses the Script Task or Script Component because of the development environmental changes (from VSA to VSTA) . SSIS provides several different options to upgrade SSIS packages, for more details click here.

Friday, February 12, 2010

Transfer SQL Server database schema objects and data to another server or database with SMO

Transfer SQL Server database schema objects and data to another server or database with SMO
Have you wondered how Database Copy Wizard works behind the scenes? Do you have a requirement to create a copy of your database (say copy of your production database for development or testing) programmatically? In this article, I am going to show you how you can use SMO (SQL Server Management Objects) classes to transfer database objects and data to another server or database, to learn detail click here.