I moved my blog to new address http://www.tobepioneers.com: 2012-02-26

3/01/2012

Exploring SSIS Architecture and Execution History Through Scripting

When you are using SSIS, there soon comes a time when you are confronted with having to do a tricky task such as searching for particular connection strings in all your SSIS packages, or checking the execution history of scheduled SSIS jobs. You can do this type of work effectively in TSQL as Feodor explains.

My previous article on SSIS was focused on the architecture and the functioning of the product. I had also provided a few essential T-SQL scripts which provide certain ways for documenting the SSIS environment.

In this article I will focus more on the T-SQL scripting and the ways to reveal configuration, performance and architectural information through scripting.

Exploring SSIS’s Metadata Objects

SSIS Metadata Objects in sys.objects

Let’s start simple by exploring the metadata objects that are related to the SSIS installation.
If we look at the Integration Services metadata objects in SQL 2005 we will notice that the objects contain the ‘DTS’ phrase in their names. By executing the following script in SQL 2005 we will get all objects related to the SSIS metadata (notice that the script is executed in the MSDB context):

USE msdb ;
SELECT *FROM sys.objectsWHERE name LIKE '%dts%'

Later on, in the SQL 2008 and later we have objects containing the phrase ‘DTS’ as well as ‘SSIS’ in the names. Execute the following script to view the objects (again, in the context of the MSDB database):

USE msdb ;
SELECT *FROM sys.objectsWHERE name LIKE '%dts%'
OR name LIKE '%ssis%'

Why is this? In SQL Server 2005 you will find dbo.sysdtspackages and dbo. sysdtspackages90, which help SQL Server distinguish between Integration Services packages created in BIDS and legacy packages inherited and transferred from the old SQL Server 2000 DTS (Data Transformation Services).
In SQL Server 2008 and up we find dbo. sysdtspackages and dbo. sysssispackages, where the first table contains legacies, and the second – the BIDS packages with versions from 2005 and 2008.

SSIS Metadata Objects in other system tables

In SQL 2008 and up we have:

name - 2008
sysdtscategories	One row for each category description
sysdtspackagelog	Legacy
sysdtspackages	Legacy
sysdtssteplog	Legacy
sysdtstasklog	Legacy
sysssislog	One row per entry generated by SSIS package at runtime (when the SQL Server log provider is used)
sysssispackagefolders	One row for each folder in the SSIS structure
sysssispackages	One row for each SSIS package

… and in SQL 2005 there is …

name - 2005
sysdtscategories	One row for each category description
sysdtslog90	One row per entry generated by SSIS package at runtime
sysdtspackagefolders90	One row for each folder in the SSIS structure
sysdtspackagelog	Legacy
sysdtspackages	Legacy
sysdtspackages90	One row for each SSIS package
sysdtssteplog	Legacy
sysdtstasklog	Legacy

Structure and contents of the SSIS packages

As we know, the SSIS packages are just structured XML files that contain all information needed for the package to carry out its tasks. In other words, the SSIS package itself contains the objects in the flows, the precedence, the connections and their configurations.
SSIS Packages may be saved on the file system, or in MSDB repository. In the case of the package being saved in MSDB, the package definition is saved in the packagedata column of the dbo.sysssispackages table (or in dbo.sysdtspackages90 in SQL Server 2005).
The column itself is of the image datatype, hence in order for us to retrieve the contents, we need to cast it as a VARBINARY(MAX) first, and then as a XML data type.
Depending on the security level of the package, however, it might not be very easy to explore the contents of the package definitions in MSDB; in case the package is encrypted, the package definition will begin with the EncryptedData tag.

Retrieving the definitions of the SSIS Packages

So, here is how to retrieve the definitions of the SSIS packages in MSDB:

In 2005:

SELECT     p.[name] AS [PackageName]
           ,[description] AS [PackageDescription]
           ,CASE [packagetype]
               WHEN 0 THEN 'Undefined'
               WHEN 1 THEN 'SQL Server Import and Export Wizard'
               WHEN 2 THEN 'DTS Designer in SQL Server 2000'
               WHEN 3 THEN 'SQL Server Replication'
               WHEN 5 THEN 'SSIS Designer'
               WHEN 6 THEN 'Maintenance Plan Designer or Wizard'
           END     AS [PackageType]
           ,CASE [packageformat]
               WHEN 0 THEN 'SSIS 2005 version'
               WHEN 1 THEN 'SSIS 2008 version'
           END AS [PackageFormat]
           ,p.[createdate]
           ,CAST(CAST(packagedata AS VARBINARY(MAX)) AS XML) PackageXML
          FROM      [msdb].[dbo].[sysdtspackages90]     p

In 2008 and up:

Now that we have the definition, what can we do with it? We can parse it and extract some useful data.

Extracting connection strings from an SSIS definition

Here is how to retrieve the data connection strings:

In SQL 2005:

;WITH XMLNAMESPACES ('www.microsoft.com/SqlServer/Dts' AS pNS1,
  'www.microsoft.com/SqlServer/Dts' AS DTS) -- declare XML namespacesSELECT c.name,
  SSIS_XML.value('./pNS1:Property [@pNS1:Name="DelayValidation"][1]',
    'varchar(100)') AS DelayValidation,
  SSIS_XML.value('./pNS1:Property[@pNS1:Name="ObjectName"][1]',
    'varchar(100)') AS ObjectName,
  SSIS_XML.value('./pNS1:Property[@pNS1:Name="Description"][1]',
    'varchar(100)') AS Description,
   SSIS_XML.value('pNS1:ObjectData[1]/pNS1:ConnectionManager[1]
/pNS1:Property[@pNS1:Name="Retain"][1]', 'varchar(MAX)') Retain,
   SSIS_XML.value('pNS1:ObjectData[1]/pNS1:ConnectionManager[1]
/pNS1:Property[@pNS1:Name="ConnectionString"][1]', 'varchar(MAX)') ConnectionStringFROM  --( SELECT    id ,
                    CAST(CAST(packagedata AS VARBINARY(MAX)) AS XML) PackageXML
          FROM      [msdb].[dbo].[sysdtspackages90]
        ) PackageXML
        CROSS APPLY PackageXML.nodes('/DTS:Executable/DTS:ConnectionManager') SSIS_XML ( SSIS_XML )
        INNER JOIN [msdb].[dbo].[sysdtspackages90] c ON PackageXML.id = c.id

In SQL 2008 and up:

;WITH XMLNAMESPACES ('www.microsoft.com/SqlServer/Dts' AS pNS1,
  'www.microsoft.com/SqlServer/Dts' AS DTS) -- declare XML namespacesSELECT c.name,
  SSIS_XML.value('./pNS1:Property [@pNS1:Name="DelayValidation"][1]',
    'varchar(100)') AS DelayValidation,
  SSIS_XML.value('./pNS1:Property[@pNS1:Name="ObjectName"][1]',
    'varchar(100)') AS ObjectName,
  SSIS_XML.value('./pNS1:Property[@pNS1:Name="Description"][1]',
    'varchar(100)') AS Description,
   SSIS_XML.value('pNS1:ObjectData[1]/pNS1:ConnectionManager[1]
/pNS1:Property[@pNS1:Name="Retain"][1]', 'varchar(MAX)') Retain,
   SSIS_XML.value('pNS1:ObjectData[1]/pNS1:ConnectionManager[1]
/pNS1:Property[@pNS1:Name="ConnectionString"][1]', 'varchar(MAX)') ConnectionStringFROM  --( SELECT    id ,
                    CAST(CAST(packagedata AS VARBINARY(MAX)) AS XML) PackageXML
          FROM      [msdb].[dbo].[sysssispackages]
        ) PackageXML
        CROSS APPLY PackageXML.nodes('/DTS:Executable/DTS:ConnectionManager') SSIS_XML ( SSIS_XML )
        INNER JOIN [msdb].[dbo].[sysssispackages] c ON PackageXML.id = c.id

Extracting connection strings from an SSIS definition

Here is how to retrieve the package configurations:

In SQL 2005:

;WITH XMLNAMESPACES ('www.microsoft.com/SqlServer/Dts' AS pNS1,
  'www.microsoft.com/SqlServer/Dts' AS DTS) -- declare XML namespacesSELECT c.name,
  SSIS_XML.value('./pNS1:Property [@pNS1:Name="ConfigurationType"][1]',
    'varchar(100)') AS ConfigurationType,
  CASE CAST(SSIS_XML.value('./pNS1:Property[@pNS1:Name="ConfigurationType"][1]',
    'varchar(100)') AS INT)
    WHEN 0 THEN 'Parent Package'
    WHEN 1 THEN 'XML File'
    WHEN 2 THEN 'Environmental Variable'
    WHEN 3 THEN 'Registry Entry'
    WHEN 4 THEN 'Parent Package via Environmental Variable'
    WHEN 5 THEN 'XML File via Environmental Variable'
    WHEN 6 THEN 'Registry Entry via Environmental Variable'
    WHEN 7 THEN 'SQL Server'
  END AS ConfigurationTypeDesc,
  SSIS_XML.value('./pNS1:Property[@pNS1:Name="ConfigurationVariable"][1]',
    'varchar(100)') AS ConfigurationVariable,
  SSIS_XML.value('./pNS1:Property[@pNS1:Name="ObjectName"][1]',
    'varchar(100)') AS ConfigurationName,
  SSIS_XML.value('./pNS1:Property[@pNS1:Name="ConfigurationString"][1]',
    'varchar(100)') AS ConfigurationStringFROM  ( SELECT    id ,
                    CAST(CAST(packagedata AS VARBINARY(MAX)) AS XML) PackageXML
          FROM      [msdb].[dbo].[sysdtspackages90]
        ) PackageXML
        CROSS APPLY PackageXML.nodes('/DTS:Executable/DTS:Configuration') SSIS_XML ( SSIS_XML )
        INNER JOIN [msdb].[dbo].[sysdtspackages90] c ON PackageXML.id = c.id

In SQL 2008 and up:

;WITH XMLNAMESPACES ('www.microsoft.com/SqlServer/Dts' AS pNS1,
  'www.microsoft.com/SqlServer/Dts' AS DTS) -- declare XML namespacesSELECT c.name,
  SSIS_XML.value('./pNS1:Property [@pNS1:Name="ConfigurationType"][1]',
    'varchar(100)') AS ConfigurationType,
  CASE CAST(SSIS_XML.value('./pNS1:Property[@pNS1:Name="ConfigurationType"][1]',
    'varchar(100)') AS INT)
    WHEN 0 THEN 'Parent Package'
    WHEN 1 THEN 'XML File'
    WHEN 2 THEN 'Environmental Variable'
    WHEN 3 THEN 'Registry Entry'
    WHEN 4 THEN 'Parent Package via Environmental Variable'
    WHEN 5 THEN 'XML File via Environmental Variable'
    WHEN 6 THEN 'Registry Entry via Environmental Variable'
    WHEN 7 THEN 'SQL Server'
  END AS ConfigurationTypeDesc,
  SSIS_XML.value('./pNS1:Property[@pNS1:Name="ConfigurationVariable"][1]',
    'varchar(100)') AS ConfigurationVariable,
  SSIS_XML.value('./pNS1:Property[@pNS1:Name="ObjectName"][1]',
    'varchar(100)') AS ConfigurationName,
  SSIS_XML.value('./pNS1:Property[@pNS1:Name="ConfigurationString"][1]',
    'varchar(100)') AS ConfigurationStringFROM  ( SELECT    id ,
                    CAST(CAST(packagedata AS VARBINARY(MAX)) AS XML) PackageXML
          FROM      [msdb].[dbo].[sysssispackages]
        ) PackageXML
        CROSS APPLY PackageXML.nodes('/DTS:Executable/DTS:Configuration') SSIS_XML ( SSIS_XML )
        INNER JOIN [msdb].[dbo].[sysssispackages] c ON PackageXML.id = c.id

There are many other aspects to be explored in the definitions of the SSIS packages, and it is all matter of finding the node names and parsing them.
In the remaining part of this article, I would like to shift our attention to these areas: the interaction between SQL Agent and the SSIS packages and some scripts to gather performance statistics.

Overriding the package internal configurations

The SSIS packages can be executed in several ways: as a scheduled job from the SQL Server Agent or from the command line (or even from a batch file).
Regardless of which method is used for the execution, it is always the DTExec.exe who carries the task.
Before executing the SSIS package, the SQL Server Agent or the command line script have to form an execution string and pass parameters to the DTExec, and thus control the execution of the package.
Here is a script which shows all SQL Agent jobs steps which execute SSIS packages and the custom configurations provided through the SQL Agent job:

USE [msdb]
GOSELECT j.job_id,
       s.srvname,
       j.name,
       js.subsystem,
       js.step_id,
       js.command,
       j.enabled,
       js.output_file_name,
       js.last_run_outcome,
       js.last_run_duration,
       js.last_run_retries,
       js.last_run_date,
       js.last_run_time,
        js.proxy_id FROM   dbo.sysjobs jJOIN   dbo.sysjobsteps js
   ON  js.job_id = j.job_id JOIN   MASTER.dbo.sysservers s
   ON  s.srvid = j.originating_server_id--filter only the job steps which are executing SSIS packages WHERE  subsystem = 'SSIS'--use the line below to enter some search criteria
--AND js.command LIKE N'%ENTER_SEARCH%'GO

As you noticed, you can even use the script above to filter and search through the configurations of the SQL Agent Jobs. For example, you can search for all jobs which are executing encrypted SSIS packages by using …

AND js.command LIKE N'%/DECRYPT%'

…as a search criteria in the above script. You may also want to search for a server name, for example.

Exploring execution history

Finally, let’s look into some execution history of the SSIS packages which are scheduled as SQL Server Agent jobs.
The following script will return all SQL Server Agent Jobs, which are currently (as of the moment of the execution of the script) executing SSIS packages and also the last execution time and duration, as well as the execution command.

SET NOCOUNT ON-- Check if the SQL Server Agent is runningIF EXISTS ( SELECT  1
            FROM    MASTER.dbo.sysprocesses
            WHERE   program_name = N'SQLAgent - Generic Refresher' )
    BEGIN
        SELECT  @@SERVERNAME AS 'InstanceName' ,
                1 AS 'SQLServerAgentRunning'
    END
ELSE
    BEGIN
        SELECT  @@SERVERNAME AS 'InstanceName' ,
                0 AS 'SQLServerAgentRunning'
        RAISERROR('The SQL Server Agent is not running.', 16, 1) WITH SETERROR ;
    END-- Execute the scriptIF EXISTS ( SELECT  *
            FROM    tempdb.dbo.sysobjects
            WHERE   id = OBJECT_ID(N'[tempdb].[dbo].[Temp1]') )
    DROP TABLE [tempdb].[dbo].[Temp1]
GOCREATE TABLE [tempdb].[dbo].[Temp1]
    (
      job_id UNIQUEIDENTIFIER NOT NULL ,
      last_run_date NVARCHAR(20) NOT NULL ,
      last_run_time NVARCHAR(20) NOT NULL ,
      next_run_date NVARCHAR(20) NOT NULL ,
      next_run_time NVARCHAR(20) NOT NULL ,
      next_run_schedule_id INT NOT NULL ,
      requested_to_run INT NOT NULL ,
      request_source INT NOT NULL ,
      request_source_id SYSNAME COLLATE database_default
                                NULL ,
      running INT NOT NULL ,
      current_step INT NOT NULL ,
      current_retry_attempt INT NOT NULL ,
      job_state INT NOT NULL
    )DECLARE @job_owner SYSNAME
DECLARE @is_sysadmin INT
SET @is_sysadmin = ISNULL(IS_SRVROLEMEMBER('sysadmin'), 0)SET @job_owner = SUSER_SNAME()INSERT  INTO [tempdb].[dbo].[Temp1]
        EXECUTE MASTER.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner
        UPDATE  [tempdb].[dbo].[Temp1]SET     last_run_time = RIGHT('000000' + last_run_time, 6) ,
        next_run_time = RIGHT('000000' + next_run_time, 6) ;-----SELECT  j.name AS JobName ,
        j.enabled AS Enabled ,
        CASE x.running
          WHEN 1 THEN 'Running'
          ELSE CASE h.run_status
                 WHEN 2 THEN 'Inactive'
                 WHEN 4 THEN 'Inactive'
                 ELSE 'Completed'
               END
        END AS CurrentStatus ,
        COALESCE(x.current_step, 0) AS CurrentStepNbr ,
        CASE x.running
          WHEN 1 THEN js.step_name
          ELSE NULL
        END AS CurrentStepName ,
        CASE WHEN x.last_run_date > 0
             THEN CONVERT (DATETIME, SUBSTRING(x.last_run_date, 1, 4) + '-'
                  + SUBSTRING(x.last_run_date, 5, 2) + '-'
                  + SUBSTRING(x.last_run_date, 7, 2) + ' '
                  + SUBSTRING(x.last_run_time, 1, 2) + ':'
                  + SUBSTRING(x.last_run_time, 3, 2) + ':'
                  + SUBSTRING(x.last_run_time, 5, 2) + '.000', 121)
             ELSE NULL
        END AS LastRunTime ,
        CASE h.run_status
          WHEN 0 THEN 'Fail'
          WHEN 1 THEN 'Success'
          WHEN 2 THEN 'Retry'
          WHEN 3 THEN 'Cancel'
          WHEN 4 THEN 'In progress'
        END AS LastRunOutcome ,
        CASE WHEN h.run_duration > 0
             THEN ( h.run_duration / 1000000 ) * ( 3600 * 24 )
                  + ( h.run_duration / 10000 % 100 ) * 3600 + ( h.run_duration
                                                              / 100 % 100 )
                  * 60 + ( h.run_duration % 100 )
             ELSE NULL
        END AS LastRunDuration ,
        js.command AS SSISPackageExecutionCommandFROM    [tempdb].[dbo].[Temp1] x
        LEFT JOIN msdb.dbo.sysjobs j ON x.job_id = j.job_id
        JOIN msdb.dbo.sysjobsteps js ON js.job_id = j.job_id
        LEFT OUTER JOIN msdb.dbo.syscategories c ON j.category_id = c.category_id
        LEFT OUTER JOIN msdb.dbo.sysjobhistory h ON x.job_id = h.job_id
                                                    AND x.last_run_date = h.run_date
                                                    AND x.last_run_time = h.run_time
                                                    AND h.step_id = 0WHERE   x.running = 1
        AND js.subsystem = 'SSIS'
DROP TABLE [tempdb].[dbo].[Temp1]

In conclusion, SSIS is a vast product which provides significant amount of metadata available to the SQL Server administrator. In this article I have shown the way to explore the SSIS metadata through some scripts and hopefully they will make the daily administration of your SSIS environments much easier.

The Road to Professional Database Development: Set-Based Thinking

by Peter Larsson

Under the pseudonym of 'SwePeso', Peter Larsson is famous on SQL forums for the amazing performance he can get from SQL. How does he do it? In the first of a series of articles, Peter explains his secrets.

The single most common barrier to writing efficient database code, for a developer versed in a typical client language (such as C#), is an inability to leave behind the habit of working on one row at a time, in favor of working with a set (a collection of rows).

What is the difference? Let's examine a very simple example of summing up 5 rows in a table. The following code creates a table variable (@Sample) and populates it with the rows of data.

DECLARE @Sample TABLE ( Data INT )
INSERT @Sample( Data )VALUES ( 1 ),
( 2 ),
( - 4 ),
( 5 ),
( 8 )
SELECT DataFROM @Sample

For a .NET developer this pseudo-code would be the way to perform this simple sum:

Dim intS AS Integer = 0, 'Initialise the sum to zero
objR AS Row

For Each objR In coll.Rows
intS += objR.Data
Next

Debug.Print intS

This is the standard approach for a procedural language; we not only tell the compiler what we want (the sum of all objects) but also how we want to approach the objects when calculating the sum. In this example, the coll variable holds a collection with objects of type Row. The For-Next loop iterates through all the objects and, for each object, fetches the current value from the Data property and adds the value to the intS variable. In other words the code operates on one row at a time.
T-SQL is a declarative language which means that the programmer is supposed to tell the database server what is required but let SQL Server decide the best way to finalize the task. However, many programmers force the procedural approach on SQL Server, with code such as the following:

DECLARE curSum CURSOR FOR
SELECT DataFROM @Sample
DECLARE @s INT ,@Total INT = 0
OPEN curSum
FETCH NEXTFROM curSumINTO @s
WHILE @@FETCH_STATUS = 0 BEGIN
SET @Total + = @sFETCH NEXTFROM curSumINTO @sEND

CLOSE curSumDEALLOCATE curSum
SELECT @Total

Even though this code is written in T-SQL, it still operates on one object (row) at any given time. This approach is commonly referred to as Row-by-Agonizing Row (RBAR). If we increase the number of rows tenfold, the time to complete the task would increase by a factor of ten; for a very large table (100 million rows or more) this sum could easily take hours to complete.
So how do you do this in a set-based way? Luckily for us, T-SQL has an aggregate function available to do the job for us.

SELECT SUM ( Data )FROM @Sample

The following table compares the performance of the cursor method, versus the set-based method, for an increasing numbers of rows.

Rows	CURSOR		SET BASED		Performance Factor
	DURATION (mS)	READS	DURATION	READS	DURATION	READS
1k	47	4009	0	7	–	953
10k	492	40004	3	42	164	953
100k	5189	400389	28	388	179	1039
1000k	51358	4003848	286	3847	180	1041

As you can see, both solutions scales linearly i.e. when we increase the number of rows by a factor of ten, we increase the query duration by roughly the same factor. However, there is one big difference, and that is that the set-based solution is about 180 times faster, and puts only about 1/1000th of the read pressure on the server!
So why is the performance difference so vast? We'll discuss this in full detail in a later article in this series, but in essence, the set-based approach allows SQL Server to return the all the required rows very efficiently, fully exploiting the underlying data storage mechanisms. The internal structure where the data is stored in SQL Server is called a Page and it defines the minimum amount of data read or written to the database, regardless of whether you want all rows in that Page or only one row. So, with a set-based approach, we only need to fetch a page once to get all x rows stored within, whereas with the RBAR approach, we must re-fetch the page x times. We'll also see in a later article how, with appropriate indexes in place, we can speed up this process still further.
This paradigm shift is all there is to it! SQL Server has some built-in aggregate functions to accomplish the task of summing a number of rows, and many others. The key difference is that you, in set-based thinking, instead of working with individual rows, we work with sets which can contain any number of rows.
To become a professional database developer you have to abandon the old habit of thinking what you want to do with each row, and start thinking instead about what you want to do with the set, by manipulating the columns. Think vertical instead of horizontal!
If we take this example a step further, considering the task of multiplying together all the rows, we will encounter the second biggest barrier to becoming a professional database developer, and that is math skills.
For a .NET developer, this pseudo code would be the way to go:

Dim intP AS Integer = 1, ' Initialise the product to 1
objR AS Row

For Each objR In coll.Rows
intP *= objR.Data
Next

Debug.Print intP

However, we just established that we no longer want to work with single rows, right? Unfortunately, however, while SQL Server provides a handy SUM aggregate function, it does not provide a MULTIPLY aggregate function. So, how do we perform this task in a set-based fashion? This is where your math skills become handy.

A * B * C equals e^(ln(A)+ln(B)+ln(C))

Having transformed the multiplication into a sum, it's easier to see that the final query should look something like this:

SELECT EXP ( SUM ( LOG ( Data )))FROM @Sample

But we still have an issue here; logarithmic operations are only allowed on positive values, and not including zero (logarithmic operations on zero and negative values are undefined in natural numbers and will give an Invalid Floating Point operation in T-SQL), and multiplication with zero, under any circumstances, is equal to zero. Also, remember that a multiplication with an odd number of negative numbers results in a negative product and multiplication with an even number of negative number results in a positive product. Here is one example of how to solve this problem:

IF EXISTS( SELECT * FROM @Sample WHERE Data = 0 )SELECT 0.0E -- If any value is zero, the total product is zero.ELSE
SELECT CASE IsNegativeProductWHEN 1 THEN - EXP ( theSum )ELSE EXP ( theSum )END
FROM (SELECT SUM ( LOG ( ABS ( Data ))), -- Sum all exponents
-- Keep track if product is negative or positiveSUM ( CASE WHEN Data < 0 THEN 1 ELSE 0 END ) % 2FROM @Sample
) AS d ( theSum , IsNegativeProduct )

The main reason for the checks in the above code is the limitations of the LOG function. So, can we complete the task in another fashion and still produce the correct result? Yes, we can:

DECLARE @p INT = 1
UPDATE @SampleSET @p * = Data
SELECT @p

This code, which looks very much like the C# pseudo code, produces the right answer, a value of -320, but how did it work? The difference is that we tell T-SQL to multiply the values but we do not force T-SQL to retrieve the rows in any order. Another reason we can use the code above is due to the nature of a product. If we want the product of A and B (a * b), the reverse is also valid (b * a); it doesn't matter which order we take the values in the multiplication, as long they are of same data type.
This leads us to the third 'light bulb' moment on the road to becoming a professional database developer: understanding that rows in a database have no order. This is very different to the way .NET developers think. A collection of objects is normally a double-linked list, where you can traverse forward and backward. Rows in a table can be logically ordered by assigning a sequence number or a sequence denoted by date and time, but you can never rely on the physical order of rows in a set.

Summary

Think in sets and not in single rows
Brush up your math skills as much as you possibly can
Rows have no determined physical order

Coming Next….
Efficient storage and retrieval: Normalization rules.

Scott Hanselman - Visual Studio 11 Beta in Context

Today Visual Studio 11 Beta is released and available for download. Don't want to read a big blog post? Phooey on you then! ;)

Made it this far? OK, cool. I wanted to do a post that would not only point you to a bunch of other resources, but more generally answer the obvious questions. The questions that I asked before I went to work for Microsoft four years ago. I always ask: What's changed, and why should I care?

"One ASP.NET"

One of the things that the fellows and I are working on that will be more obvious after the beta and even a little after the final release is this idea of One ASP.NET. We're sweeping through the whole framework, samples, templates and NuGet to make sure things work together cohesively. You'll hear more about this as we firm it up.
Some guiding principles for ASP.NET are these:

Every reasonable combination of subsystems just works
The model is easily extended by community projects (not just us!)
Every subsystem or application type includes a *.Sample that works together with others

Here's the boxes diagram I've been playing with.

These principles are some of the things that drove (and continue to drive) ASP.NET this development cycle. We are trying to give you great mobile options, great HTML5, CSS3 and JavaScript support and also including open source libraries like Modernizr, jQuery, jQuery UI and Knockout.
We are working towards a more pluggable, more friendly but still powerful ASP.NET. Again, more on this soon and some surprises. We'll see more interesting uses of NuGet, more plug-ablity, more examples, and more systems working together.
You want to mix and match a ASP.NET Web API, serialization with JSON.NET, use MongoDB, run Web Pages and Web Forms side by side, add some some SignalR and a few WCF enterprise Web Services? Add in ELMAH, Glimpse, Image Resizer and your favorite NuGet packages? Totally. It's Encouraged. It's all One ASP.NET.

.NET Framework 4.5 Beta

For the most part in my experience, .NET 4.5 is a very compatible release. .NET 4.5 upgrades .NET 4 as .NET 3.5 upgraded .NET 3 (and 2, although we're trying to play by the versioning rules now, thank goodness.) The vast majority of .NET 4 apps should work fine on .NET 4.5 unless you are doing something exotic. I haven't had any problems myself, but I've heard of some unusual edge cases with folks doing ILMerge and a few other things.
There's a number of new improvements. Some of my personal favorites and features I'm most interested in are (these are somewhat obscure, but nice fixes, IMHO):

Ability to limit how long the regular expression engine will attempt to resolve a regular expression before it times out.
Zip compression improvements to reduce the size of a compressed file.
Better performance when retrieving resources.
Updates to MEF to better support generics.
new Asynchronous methods in I/O classes for Asynchronous File Operations
Support for Internationalized Domain Name parsing
WPF Ribbon Control
WCF HTTPS protocol mapping
WCF Asynchronous streaming support
WCF Contract-first development as well as ?singleWSDL for service URLs

Test your apps and PLEASE tell us if you have trouble. This is a beta and there is a still time to fix things.

Application Compatibility in the .NET Framework 4.5 Developer Preview lists technologies that may cause migration issues for your application.
What's Obsolete in the .NET Framework shows types or members that have been made obsolete, and the recommended alternatives.
What's New in the .NET Framework 4.5 Developer Preview for descriptions of new features that you may want to add to your application.

Please don’t hesitate to post a comment on team blogs, or at one of the forums that are actively monitored: Connect (report bugs), UserVoice (request features) and MSDN Forums (ask for help). I know that folks have issues with Connect sometimes, but folks are actively monitoring all these places and are trying to get back to you with clear answers.

ASP.NET Core Framework

Here's a detailed release notes document about what's new in ASP.NET 4.5 and Visual Studio "Web Developer" 11 Beta. The core ASP.NET framework has a lot of new support around asynchrony. Asynchrony has been a theme throughout the whole Visual Studio 11 process and ASP.NET is full of improvements around this area.
There's support for the await keyword, and Task-based modules and handlers.

private async Task ScrapeHtmlPage(object caller, EventArgs e) 

{    

   WebClient wc = new WebClient();    

   var result = await wc.DownloadStringTaskAsync("http://www.microsoft.com");   

   // Do something with the result

}

Even IHttpAsyncHandler (a classic, and a difficult thing to get right) has a friend now:

public class MyAsyncHandler : HttpTaskAsyncHandler

{    

    // ...     

    // ASP.NET automatically takes care of integrating the Task based override    

    // with the ASP.NET pipeline.    

    public override async Task ProcessRequestAsync(HttpContext context)    

    {        

       WebClient wc = new WebClient();        

       var result = await             

       wc.DownloadStringTaskAsync("http://www.microsoft.com");        

       // Do something with the result    

    }

}

There's security improvements with the inclusion of core encoding routines from the popular AntiXssEncoder library, and you can plug in your own.
ASP.NET also has WebSockets support when running on Windows 8:

public async Task MyWebSocket(AspNetWebSocketContext context) 

{    

   WebSocket socket = context.WebSocket;    

   while (true)    

   {

   ...

   }

}

Bundling and Minification is built in and is also pluggable so you can swap out own techniques for your own, or your favorite open source alternative.
There's lots of performance improvements including features for dense workloads that can get up to a 35% reduction in startup time and memory footprint with .NET 4.5 and Windows 8.
ASP.NET 4.5 also supports multi-core JIT compilation for faster startup and more support for tuning the GC for your server's specific needs.

ASP.NET Web Forms

There's lots of refinements and improvements in Web Forms. Some favorites are strongly-typed data controls. I blogged about this before in my Elegant Web Forms post. There's two way data-binding in controls like the FormView now instead of using Eval() and you'll also get intellisense in things like Repeaters with strongly typed modelTypes.
Web Forms also gets Model Binding (all part of the One ASP.NET strategy) which is familiar to ASP.NET MVC users. Note the GetCategories call below that will bind to a View with IQueryable.

public partial class Categories : System.Web.UI.Page

{

    private readonly DemoWhateverDataContext _db = new DemoWhateverDataContext();

 

    public void Page_Load()

    {

        if (!IsPostBack)

        {

            // Set default sort expression

            categoriesGrid.Sort("Name", SortDirection.Ascending);

        }

    }

 

    public IQueryable<Category> GetCategories()

    {

        return _db.Categories;

    }

}

In this example, rather than digging around in the Request.QueryString, we get our keyword parameter this way:

public IQueryable<Product>GetProducts([QueryString]string keyword) 

{    

  IQueryable<Product> query = _db.Products;

  if (!String.IsNullOrWhiteSpace(keyword))    

  {        

    query = query.Where(p => p.ProductName.Contains(keyword));    

  }

  return query; 

}

Web Forms also get unobtrusive validation, HTML 5 updates and elements, and those of you who like jQuery but also like Web Forms Controls (as well as Ajax Control Toolkit fans) will be thrilled to check out the JuiceUI project. It's an open-source collection of ASP.NET Web Forms components that makes jQuery UI available in a familiar way for Web Forms people.

ASP.NET MVC and Web API

Last week I blogged about Making JSON Web APIs with ASP.NET MVC 4 Beta and ASP.NET Web API. ASP.NET MVC 4 includes these new features (and a few more) and is included in Visual Studio 11 Beta.

ASP.NET Web API
Refreshed and modernized default project templates
New mobile project template
Many new features to support mobile apps
Recipes to customize code generation
Enhanced support for asynchronous methods
Read the full feature list in the release notes

Matt Milner has a great post on where ASP.NET Web API and WCF proper meet and diverge, and why you'd use one over the other. I'll be doing a more detailed post on this also, but I like Matt's quote:
WCF remains the framework for building services where you care about transport flexibility. WebAPI is the framework for building services where you care about HTTP.

ASP.NET Web Pages 2

New features include the following:

New and updated site templates.
Adding server-side and client-side validation using the Validation helper.
The ability to register scripts using an assets manager.
Enabling logins from Facebook and other sites using OAuth and OpenID.
Adding maps using the Mapshelper.
Running Web Pages applications side-by-side.
Rendering pages for mobile devices.

There's lots more to talk about in Razor and Web Pages 2 that I will talk about when Web Matrix 2 comes out.

Visual Studio - for Web Developers

Lot of new Web Features - Hello Opera users!

There's an extensive list of features and fixes on the Web Developer Tools team Blog. Here are my favorites.
The HTML Editor is smart about HTML5 and you can develop smart HTML5 sites with any ASP.NET technique.
The CSS Editor has a new formatter, color picker, better indentation, smart snippets and vendor-specific IntelliSense. That's Webkit and Opera in the screenshot over there.
The Javascript Editor has IE10's Javascript engine and supports Javascript as a 1st class citizen with all the support you get in other languages like Go To Definition, brace matching, and more.
Page Inspector is all new and lets you to see what elements in the source files (including server-side code) have produced the HTML markup that is rendered to the browser. IIS Express is now the default web application host.

All the Links

General Info

Download

Visual Studio 11 - Web Installer
Visual Studio 11 - ISO image

VS 11 Beta will expire on June 30, 2012.

Windows 8 Development Tools

If you have Windows 8, you'll want this to developer Metro style apps.

Team Foundation Server 11

Secondary Downloads (for the IT folks)

Got Visual Studio issues? Complain (kindly) and vote up features and concerns at their UserVoice site.
Got ASP.NET issues? Complain to me (kindly) and vote up features and concerns at our UserVoice site or ask questions in the ASP.NET forums. There will also be new videos, tutorials and information at http://asp.net/vnext and we are continuing to update the site with fresh content.
Hope you enjoy the Beta. Please do take a moment to install it, try it out, and offer feedback. There is time for us to make changes, fixes and improvements but only if you give feedback.

Sponsor: My thanks to DevExpress for sponsoring this week's feed. There is no better time to discover DevExpress. Visual Studio 11 beta is here and DevExpress tools are ready! Experience next generation tools, today.

View article...