SQL Server 2008 Release Candidate 0

Download SQL Server 2008 Release Candidate - Here

SQL Server 2008 RC0 will automatically expire after 180 days.

SSIS Task Host Container

What is Task Host Container in SSIS?

1. It’s a default container where every single tasks fall into.
2. If we don’t specify a container then the task will fall into Task Host container.
3. The Task Host is not configured separately, instead, it is configured when we set the properties of the task it encapsulates.
4. SSIS extends variables and event handlers to the task through task host container

By Harsh Shah

MaxConcurrentExecutables property in SSIS Packages

Let’s learn about the MaxConcurrentExecutables property in SSIS

1. It’s a property of Control Flow. The value of the property is an Integer that contains the number of threads that a package can create.
2. Gets or sets the maximum number of threads a package can create.
3. Default value is -1 that means add 2 to the number of processor and use that value to execute the number of tasks in parallel. For example , if server has 2 processors and the default value is specified, SSIS will allow upto 4 tasks to be run parallel.
4. Valid values are 1 or higher and -1
5. It is used when parallelism exists in the workflow or package. If workflow is a series of sequential precedence constraints than this property has no effect.
6. If the number of possible parallel tasks are more than the number of allowable parallel tasks then some of the tasks will have to wait till the parallel threads are available.

Happy Learning !!!

By Harsh Shah

Difference between Control Flow and Data Flow

Let us understand the basic difference between Control Flow and Data Flow in SSIS 2005. 

Control Flow:

1. Process Oriented
2. Doesn’t manage or pass data between components.
3. It functions as a task coordinator
4. In control flow tasks requires completion (Success.,failure or completion)
5. Synchronous in nature, this means, task requires completion before moving to next task. If the tasks are not connected with each other but still they are synchronous in nature.
6. Tasks can be executed both parallel and serially
7. Three types of control flow elements in SSIS 2005
   1. Containers
      • Provides structures in the packages
   2. Tasks
      • Provides functionality in the packages
   3. Precedence Constraints
      • Connects containers, executables and and tasks into an ordered control flow.
      • We can control the sequence execution for tasks and also specify the conditions that tasks and containers run.
8. It is possible to include nested containers as SSIS Architecture supports nesting of the containers. Control flow can include multiple levels of nested containers.

Data Flow

1. Streaming in nature
2. Information oriented
3. Passes data between other components
4. Transformations work together to manage and process data. This means first set of data from the source may be in the final destination step while at the same time other set of data is still flowing. All the transformations are doing work at the same time.
5. Three types of Data Flow components
   1. Sources
      • Extracts data from the various sources (Database, Text Files etc)
   2. Transformations
      • Cleans, modify, merge and summarizes the data
   3. Destination
      • Loads data into destinations like database, files or in memory datasets.

Happy Learning…!!!

By Harsh Shah

Difference Between SET and SELECT commands in SQL Server

SET	SELECT
Can assign the value of an expression to the variable	Can assign the value of an expression to the variable
Cannot retrieve data from data source. It can only retrieve data from the expressions.	Can retrieve data from data source and also uses other SELECT clauses (WHERE, FROM etc)
Use SET only when you want to assign value of a function result or constant to a variable	Can use for both i.e. retrieve value of function result

Example

–Variable declaration
DECLARE
    @vName VARCHAR(10),
    @vobject_id VARCHAR(30),
    @vName1 VARCHAR(30);

–assigning a single value
SET @vName1=’Test Value’;

–Selecting and retrieving multiple values in a single select statement
SELECT
    @vName=name,
    @vobject_id=object_id
FROM
    sys.objects
WHERE
    name =’sysrowsetcolumns’

–printing the retrieved values
SELECT  @vName1,@vName, @vobject_id

–By Harsh Shah

Visual Studio 2008 Overview

Visual Studio 2008 Overview 

By this time we all are aware about the new product launch from Microsoft for the developer community. Yes, it’s Visual Studio 2008 and SQL Server 2008. Today we are going to have a quick overview on the new feature set of the product- Visual Studio 2008. 
  

Microsoft vision of smart client application is achieved by enabling developers to create connected applications rapidly and with the highest quality and the rich user experiences. Microsoft Visual Studio 2008 delivers on the Microsoft’s vision of smart client applications. 
  

Visual Studio 2008 enables organizations of every size to rapidly create more secure, manageable, and reliable applications that take advantage of Windows Vista™ and the 2007 Office system. 

  

Visual Studio 2008 delivers key advances for developers in three primary pillars: 

  

·      Rapid application development  

·      Effective team collaboration  

·      Break through user experiences 

  

Developers can take advantage of advanced development tools, debugging features, database functionality, and innovative features introduced in Visual Studio 2008 to create innovative products. 

  

Visual Studio 2008 includes enhancements such as  

  

·      Visual designers for faster development with the .NET Framework 3.5,  

·      Improvements to Web development tools  

·      Language enhancements that speed development with all types of data.  

·      Create AJAX enabled web applications  

  

Microsoft Visual Studio 2008 enables organizations to take full advantage of the .NET Framework 3.5 and the 2007 Microsoft Office system to create client, Office, Web, and mobile applications that deliver high-quality, rich user experiences that add to effective business processes and decision making. 

 

What’s New in Visual C# 

  

There are several new features introduced in C# 3.0.  

  

Visual C# IDE Features 

  

Multi-targeting 

  

with Multi-targeting you can specify the .Net Framework for your project i.e. you can write / develop programs for .NET 2.0/3.0/3.5 from the same IDE. 

  

New Project Types and Templates 

  

·      Several new project templates are provided for  

·      Windows Presentation Foundation,  

·      Windows Communication Foundation  &  

·      Web projects   

  

IntelliSense support for C# 3.0 

  

The Visual C# code editor provides statement completion and Quick Info to support the following new language constructs in C# 3.0: 

 

·         Implicitly Typed Local Variables 

·         Query Expressions 

·         Extension Methods 

·         Object/Collection Initializers 

·         Anonymous Types 

·         Lambda Expressions 

·         Partial Methods 

 

Refactoring support for C# 3.0 

 

The refactoring features, Rename, Signature Change, Extract Method, and Promote Local have been updated to support the following new language constructs: 

 

·         Query Expressions 

·         Extension Methods  

·         Lambda Expressions 

 

Code Formatting 

 

The code editor supports formatting options for several new C# 3.0 language constructs including query expressions. 

 

 

Happy Learning !!! 

Harsh Shah (Sr. Consultant)

SQL Server 2008 - A Quick Overview

SQL Server 2008 Overview

SQL Server 2008 delivers on Microsoft’s Data Platform vision by helping your organization manage any data, any place any time. So before we discuss more about SQL Server 2008 new features, let’s talk about what is Microsoft Data Platform Vision?

Microsoft Data Platform Vision

Today we live in the world of information explosion where in we come across many types of data on a day to day basis. We store, retrieve and transact data into different types such as images, video, audio, digital information, sensor information from RFID tags and the list goes on. As we now know that organizations have become considerably large with the global presence and due to this the need to make data more secure and regulatory compliance has become mandatory. Also the data stored should be available Any Where - Any Time.

Today the user and application has become completely mobile and we want our data/information to be viewed on any device -any time - anywhere. So the question here is how to deal with this kind of complexities and manage the information explosion in an effective and efficient way.

The answer to the above challenges is Microsoft Data Platform Vision. It helps organization to overcome the challenges of information explosion by providing a solution that organization can use to store many types of data, including the following

1. XML
2. e-mail
3. time/calendar
4. File
5. Documents and Geospatial

SQL Server 2008 provides reach set of services to interact with the data , query the data , performing data analysis , reporting, integration and robust synchronization. Above all , users can access information on any device.

What SQL Server 2008 Delivers?

SQL Server 2008 delivers on Microsoft’s Data Platform vision by helping your organization manage any data, any place, any time. It enables you to store data from structured, semi-structured, and unstructured documents such as images, music, documents directly within the database.

Top New Features

• Use Resource Governor to manage concurrent workloads
• Enforce policy compliance consistently across the enterprise with the Policy-Based Management
• Reduce storage requirements and increase query performance with data compression and Sparse Columns
• Protect sensitive data with Transparent Data Encryption and advanced auditing
• Troubleshoot, tune, and monitor SQL Server 2008 instances across the enterprise with Performance System Analysis
• Build high performance analysis solutions with scalability, performance, data mining and user interface enhancements in SQL Server Analysis Services
• Take advantage of improved performance, usability, visualization, and integration with the 2007 Microsoft Office system in SQL Server Reporting Services
• Integrate location-enabled applications through support for spatial data 

 

Top New Security Features

· Use the Declarative Management Framework to help enforce security polices for data services across the enterprise.

· Encrypt data without modifying applications, using Transparent Data Encryption.

· Employ enterprise-wide encryption solutions with Extensible Key Management and Hardware Security Modules.

· Audit all actions with the new Audit object.

Top New High Availability Features

• Recover corrupt data pages from a mirror server with enhanced database mirroring features. Watch the short demo.
• Take advantage of failover clustering enhancements in Windows Server 2008.
• Add new nodes to a peer-to-peer replication solution without taking replication offline.
• Improve restore times, and reduce backup volumes with new support for backup compression.
• Improve concurrency with locking enhancements.
• Reduce downtime for hardware maintenance with hot-add CPU capabilities.
• Use Resource Governor to proactively control workload prioritization.

Top New Performance and Scalability Features

• Take control of resource allocation with Resource Governor.
• Troubleshoot, tune, and monitor SQL Server 2008 instances across the enterprise with Performance Data Collector.
• Build high-performance analysis solutions with scalability and performance enhancements in SQL Server Analysis Services.  Take advantage of on-demand processing and better performance with the re-engineered reporting engine for SQL Server Reporting Services.
• Increase extract, transform, and load (ETL) performance in SQL Server Integration Services with scalable lookup tasks.

Happy Learning…..I will be sharing few more information in my next article.

Harsh Shah
(Sr. Consultant)

Download SQL Server 2008 and SQL Server 2005 Sample Database

SQL Server 2008 Sample Database for SQL Server 2008 February CTP

Following is the link to the CodePlex (CodePlex is Microsoft’s open source project hosting web site) site from where you can download the examples

http://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=10901

SQL Server 2005 Sample Database - For SQL Server 2005 SP2a

http://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=4004

SSIS: What is LineageID in SSIS?

LineageID:

• It’s an integer pointer to the column within the buffers
• In Data Flow every column has atleast one LineageID.
• A column can have more than one LineageID. This is because when column passes through various transformation outputs in a Data Flow, it holds the pointer to the column in a buffer in data flow. In the below image you can see the column EmpNo (Sort Input)  has a LineageID: 58

  Image 1: LineageID for the EmpNo column in a Sort Input

Image 2: LineageID for the EmpNo column in a Sort Output is different

Visual Studio 2005 for SQL Server 2008

This CTP enables developers to use Visual Studio 2005 for SQL Server 2008 CTP5.

Please note the following:

Please test in a test environment only

This CTP addresses the following issue:

“This server version is not supported. Only servers up to Microsoft SQL Server 2005 are supported.”
This CTP addresses this issue, and enables the following Visual Studio functionality for SQL Server 2008 CTP5 :

• Server Explorer successfully connects to SQL Server 2008, and database objects such as stored procedures and table data can be viewed and edited. Note that table schemas still cannot be viewed or edited in this release.
• SQL CLR projects that target SQL Server 2008 CTP5 can be created and deployed to the server.
• T-SQL and SQL CLR debugging are now enabled for SQL Server 2008 CTP5.
• Data binding features in Client and Web Projects are enabled.

This CTP does not support the following features for SQL Server 2008 Nov CTP:

• Creating and editing table schemas in Table Designer or Database Diagrams. The table designer feature in SQL Server Management Studio 2008 can be used to edit table schemas in SQL Server 2008 CTP5.

For more information please visit the following link.

http://www.microsoft.com/downloads/details.aspx?FamilyID=e1109aef-1aa2-408d-aa0f-9df094f993bf&displaylang=en