SQL 2008 Change Data Capture – The basics

Continuing on the previous article on SQL 2008 Data Audit – so wittingly called The beginning now we explore the basics of Change Data Capture in SQL server 2008. The first thing we need to do is to enable change data tracking for our database. Unfortunately there is no UI to do this so you have to do it with T-SQL command.

Enabling on database level

USE Test;
GO;
EXECUTE sys.sp_cdc_enable_db;
GO

This line of code will enable your database for change data capture. You enable and disable change data capture with two system stored procedures sys.sp_cdc_enable_db and sys.sp_cdc_disable_db. The changes made to your database you can check by running the next command.

SELECT name, is_cdc_enabled
FROM sys.databases

You can see in the picture above that the Test database has been enabled for change data capture. Right now you database has not been changed by SQL server the stored procedure has just updated the database property is_cdc_enabled in the sys.databases to true.

Enabling on table level

USE Test;
GO;
EXECUTE sys.sp_cdc_enable_table
    @source_schema = N'dbo'
    , @source_name = N'Test'
    , @role_name = NULL
GO

This line of code will enable your table for change data capture which you can check by running the next command. You enable and disable change data capture for tables with two system stored procedures sys.sp_cdc_enable_table and sys.sp_cdc_disable_table. When you enable the first table in the database for change data capture SQL server will create several tables, functions and jobs for your database. The changes made to your database should be as so.

SELECT name, is_tracked_by_cdc
FROM sys.tables

Here highlighted in red is the table that will actually capture your data, other tables in this picture are supporting tables for data capture and are created only once per database when you enable your first table for change data capture. Check more in detail about tables, stored procedures and functions.

Change data capture table

The data stored in data capture tables consists of some change data capture columns and your table columns.

The columns displayed here are from the change data capture table for our dbo.Test table. We have columns __$start_lsn and __$end_lsn which mark the first log sequence number and the last log sequence number under which the changes were recorded in the transaction log, then there are __$seqval which is the order of the row change in a transaction, the __$operation signals the type of the operation which was executed and last is the __$update_mask is a bit mask showing which columns have been updated in the row change. These columns appear in any change data capture, after that come your table columns with their respective data type. So let’s make a change to our table and see what happens.

Recording change data capture

INSERT INTO dbo.Test
(Col1, Col2)
VALUES(500, 600) 

INSERT INTO dbo.Test
(Col1, Col2)
VALUES(501, 601)

So let’s what has been recorded in our change data capture table cdc.dbo_Test_CT

SELECT *
FROM cdc.dbo_Test_CT

Now let’s perform a few more operations and see what we get.

UPDATE dbo.Test
SET Col1 = 503
WHERE ID = 1 

UPDATE dbo.Test
SET Col1 = 504
WHERE ID = 2 

DELETE dbo.Test
WHERE ID = 1

Not what you have expected we have executed in total 5 DML statements but the change data capture table contains 7 operations, how come? Let’s make an another query which will make things clearer.

Reading change capture data

SELECT
    CT.__$start_lsn,
    CT.__$operation,
    CASE CT.__$operation
        WHEN 1 THEN 'Delete'
        WHEN 2 THEN 'Insert'
        WHEN 3 THEN 'Update - Pre'
        WHEN 4 THEN 'Update - Post'
    END AS Operation,
    CT.ID,
    CT.Col1,
    CT.Col2,
    LSN.tran_begin_time,
    LSN.tran_end_time,
    LSN.tran_id
FROM
    cdc.dbo_Test_CT AS CT INNER JOIN
    cdc.lsn_time_mapping AS LSN ON CT.__$start_lsn = LSN.start_lsn

What we have done here is joined the change data capture table for our Test table with the cdc.lsn_time_mapping table which stores information about the log sequence number and time values and allowing us to tie time with the specific log sequence numbers. One more important thing we have here is the tran_id field which we will use in the next article. Now we can se why we have 7 records instead of 5 records which we expected to have. Change data capture when capturing data from the UPDATE statement will create two records one with __$operation value of 3 and one with value of 4. The record with value 3 represents the data that was in the row which was there before the update has been committed and the record with the value 4 represents the data after commit.

One more thing you can see here is the times when the transactions where started and completed. This is very useful when trying to get changes in a specific time frame which can be done by using change data capture functions provided by SQL server.

Reading interval change capture data

The first thing we want to do is to find out which log sequence numbers are related with our time frame we want to look at.

SELECT
    sys.fn_cdc_map_time_to_lsn ('smallest greater than or equal', '2009-06-26 15:37:46.577')
    ,sys.fn_cdc_map_time_to_lsn ('largest less than or equal', GETDATE())

Here we use the sys.fn_cdc_map_time_to_lsn function to retrieve the log sequence numbers for the start time of our interval (smallest where time is grater than or equal to ‘2009-06-26 15:37:46.577′) and the end log sequence number of our interval (largest where time is less than or equal to GETDATE()). Next up we have to use those log sequence numbers to get something useful from the table we can do it by using an another function cdc.fn_cdc_get_all_changes and cdc.fn_cdc_get_net_changes, the difference between the two will be apparent immediately.

SELECT *
FROM cdc.fn_cdc_get_all_changes_dbo_Test (
    0x00000066000000A00004
    , 0x0000006A000001F70001
    , 'all') 

SELECT *
FROM cdc.fn_cdc_get_net_changes_dbo_Test (
    0x00000066000000A00004
    , 0x0000006A000001F70001
    , 'all')

This is the result:

The cdc.fn_cdc_get_all_changes function returned all the changes that happened in our chosen interval giving us 4 total recorded changes. The second query using cdc.fn_cdc_get_net_changes returned only 2 records in the same interval why so? Well simple all_changes function returns as it names says all changes that fall between our lsn boundaries, but the net_changes returns only the last changes that happened in the interval, but what does that mean? As you can see we have only one record by each row and that is this the last operation executed on that row in the given interval.

One more thing to notice is that when executing this functions records with __$operation = 3 will not appear in the result set although it does fall into our selected interval. Since this functions will only return changes the __$operation = 3 (which is the delete operation of the update columns) will not appear.

Reading changed columns

Useful thing to now is which column has changed in an operation. The thing to use here is an another scalar valued function provided by change data capture sys.fn_cdc_has_column_changed, this function will return 1 or 0 depending if the column has been changed.

SELECT
    *
    , sys.fn_cdc_has_column_changed ('dbo_Test', 'ID', __$update_mask) AS is_id_changed
    , sys.fn_cdc_has_column_changed ('dbo_Test', 'Col1', __$update_mask) AS is_col1_changed
    , sys.fn_cdc_has_column_changed ('dbo_Test', 'Col2', __$update_mask) AS id_col2_changed
FROM
    cdc.fn_cdc_get_all_changes_dbo_Test (
        0x00000066000000A00004
        , 0x0000006A000001F70001
        , 'all')

This way we can see which columns have been changed in each operation and perform actions required according to our business rules. Thing to note here is that when you perform the insert and delete operation (__$operation = 2 || 1) the changed column signaled is all columns because you have either created values for all columns or deleted values for all columns. When looking at the changes for update (__$operation = 4) then you see only the actual updated column signaled.

This concludes this session, till next time….

SQL 2008 Change Data Capture – The beginning

Data auditing is a requirement of most enterprise systems and was a pain to implement with SQL server up to now.When data audit was required in pre SQL server 2008 databases you had to relay on solutions like triggers or some custom change tracking on the application layer. With the dawn of SQL server edition 2008 Microsoft has provided with change data capture mechanism which is an integral part of the SQL server 2008 data engine. The change data capture is a powerful mechanism for gathering changes made to data on the SQL server platform and provides it’ functionality with little overhead.

How this works

Well in short simple. Implementation of change data capture is based on an already existing element of the data engine the transaction log and does not require you to alter your db, schema or tables. When you enabled change data capture on a db SQL server will create a new schema called “cdc” with several tables, stored procedures and functions. With the changes made to your db the enabling process also creates two jobs in you SQL agent for your db one for capture and one for cleanup. These two jobs are the base for the asynchronous performance for the change data capture. The capture job is executed by default every 5 seconds, it reads the transaction log for changes from the last job execution and stores them in the change data capture tables. The cleanup job works by default once a day and cleans the stored data in the data capture tables, meaning that you have to gather that data and transfer it somewhere for permanent storage before the cleanup job runs.

Performance

Microsoft published on MSDN a SQL Server Best Practices Article on “Tuning the Performance of Change Data Capture in SQL Server 2008″ going in length on why, how and where to tune the performance of change data capture. The general conclusion of this article and many articles on the web is that the Change Data Capture has very little overhead and can be used comfortably on OLTP database with no fear of grinding your database server to a stand still. You will see a slight decrease in performance through the increased disk writes from the change data capture job but the decrease in performance if the capture job is properly configured should not be more than 5% by my test. You will see an increase in the disk queue length for the log file but only a slight for the data disk.

Problems

There are of course problems with the SQL change data tracking. The three main problems that I have faced in working with change data tracking are storing contextual information, gathering change data in one place, schema altering.

Contextual information

Storing contextual information about the change is a pain with change data capture. In most cases when performing audit you want to store some contextual data about the current operation which is changing the data like the userid, data, reason of change (form, command, etc.) …. Since the change data capture relies on an asynchronous read of the transaction log, in the time of the read SQL server does not have any information about the context under which the change has occurred and therefore that data can not be gathered.

Gathering change data in one place

The change data capture as is designed and implemented (as I can figure it) is intended to capture data for temporary storage before you transfer it to data warehouse for permanent storage. The data warehouse is intended to have the same schema (no primary or foreign keys) like the OLTP database and you simply copy the rows in the data warehouse. There is no built in way as it was not intended for such use to gather the data all in one place for review.

Schema altering

This brings us to the last problem of altering the schema. If you alter the schema of your OLTP db, you have to change the schema for your data warehouse db. That is no problem when adding columns but what when you delete the columns, and how to then display the data for the user? There is one more thing I think is just pure laziness that Microsoft did not implement. The problem with schema changing is that when you change the schema the change data capture continues to track the same columns (as well as there data type) as before with no input to you that you should do something to start capturing the new columns. It would suffice that when changing the table under change data capture you would get an error that you can not do that until you drop the change data capture for that table, but it seams that was to much for Microsoft.

There are workaround for all of this problems and I will show them in the next article on change data tracking.

Conclusion

SQL server 2008 change data tracking is a well welcome addition to SQL server feature list which helps us to create audits for your databases easily with little overhead. With some missing features which have simple workarounds this feature can be used very effectively for auditing your database.

Check back for more articles on change data tracking with the details of implementation and workarounds need to make the whole thing functional.

Two faces of cloud computing

There is a big hype about cloud computing these days, all major web companies are investing heaps of cash into cloud computing. The big 4 (Microsoft, Google, Yahoo, Amazon) and a whole bunch of fresh startups are playing on the cloud computing card as the next big thing in the world of IT. They are all expecting that in the next year more and more web application and enterprises will migrate to the cloud computing platforms. There is a lot of predictions for cloud computing in 2009 like this one from marketwire.com:

A rise in serverless companies with 1000+ employees. In 2009, the market will start to hear about more and more companies going completely server-less. While this is already happening at smaller companies, larger and larger companies will optimize their business processes and cut IT expenses by outsourcing to cloud providers.

In the upcoming year more and more companies will have a focus on reducing costs because of the economical breakdown in 2008. The first enterprises that will turn to cloud computing platforms as their primary provider of IT infrastructure will be the IT companies. Why wouldn’t they go for cloud computing when it offers savings on the costs of IT infrastructure and personnel required to maintain that infrastructure. If you look at the prices offered by the big 4 in cloud computing are setting for the use of their infrastructure you will see massive savings. Enough said is that Flickr host all of its photos on the Amazon S3 storage infrastructure to show the capabilities of cloud computing platforms. All that for just a fraction of the costs of owning, running and maintaining company owned servers or datacenters.

Is it all that great like it is presented? Well not so when you dig a little bit under the surface. What is the problem then? The problem is with the data and applications that you put in the cloud, there are issues with security, ownership and access to the data. In the security I don’t mean if your data is secure from the outside rather from the inside from the company which is providing the infrastructure. You must be aware that personnel from the cloud provider company have access to your data stored in the cloud. Therefore you should make sure that data is encrypted when stored in the cloud.

The problem with data in the cloud can be summed up in this excerpt from the Amazon web service Customer Agreement.

3.7.3. In the Event of Other Suspension or Termination. Except as provided in Sections 3.7.1 and 3.7.2 above, we shall have no obligation to continue to store your data during any period of suspension or termination or to permit you to retrieve the same.

Now what this means is that in the case of contract suspension or termination Amazon has no obligation to keep or provide you access to your own data, which basically translates to you depending on the good will of people working at Amazon to give you access to your data. What happens if your cloud storage provider goes out of business, what happens to your data?

In a recent article Richard Stallman the GNU founder has expressed similar concerns claming saying that cloud computing is:

It’s stupidity. It’s worse than stupidity: it’s a marketing hype campaign,

Software engineering is bad engineering

This a common phrase that is used in reference to software in general. I have heard it from my friends, customers and even co-workers all of which are often ignorant of the complexity and the amount of different factors that are involved in the execution of a software application. I’m especially amassed when my programing colleagues criticize some software with no objective reason and fully knowing that their own software is as equally flawed. The very same question was raised on my new favorite programing Q&A site stackoverflow.

Bugs in software is something that we have to be aware will happen as long as there is software. Now most people that are not familiar with programing always ask why software can’t be bug free? That’s when I ask them if they have experienced something of the following:

• Oil leak
• Engine trouble
• Leaky faucet
• Wobbly chair
• Squeaky doors
• VCR eating the tape

Off course the answer is yes, we have all experienced some of this if not all and this are the equivalent of software bugs. Oil is not meant to leak from your car – bug, chairs are not meant to be wobbly – bug. In short my answer is no we can’t have bug free software because nothing around us is bug free even not ourselves we can only try to keep bugs down to the minimum.

You are not meant to be sick but you still are is that a bug in you immune system or was it caused by something else out of your body’s control? Strange question you might think but what it comes down to is that our body starts to malfunction because something that is out of the body’s control has influenced it and disrupted the normal way of functioning same apply’s to software.

Software developers constantly combat the external influences on their applications be it human input, operating environment, incompatible software or whatever other external source of influence. This all invites so many variables and possibilities in an already highly complex system that is being developed by humans and is thus by default faulty. When developing software you have to take into account different hardware and software combinations on which your software is supposed to run, users that are supposed to use it and when you factor all of that in you get massive number of possibilities. Now as I see it most applications today are quite stable for what & where they are meant to run.

Software engineers are trying to combat all of those possibilities and the bugs that arise from such a number of different scenarios and so far we have seen with each new release of software that they are managing to decrease the number of bugs rapidly and are producing really quality code.

Anybody remember Windows 95?

Microsoft Windows release cycle

First of all be for you start reading I’m not an employee of Microsoft and this text represents my opinions and is not representative of official Microsoft policy.

Yesterday I have been at a Microsoft where several of us have among other seen the M2 build of Windows 7 Client. Now I’m not going to write about what we have seen, Windows 7 features and etc. since I’m not at liberty to do so because of an non disclosure agreement. But there was an interesting discussion on the new Microsoft release cycle for Windows Client operating system. Microsoft has set that after the release of Vista a new release cycle for Windows OS to be 2-3 years cycle release to release. Latest information from Microsoft that can be found on the Internet say that the release of Windows 7 client will be on track with that release cycle with the commercial release date somewhere in fall/winter 2009 or beginning of 2010, internal information from Microsoft have the target of the first availability of RTM set somewhere in summer of 2009.

Microsoft has decide that they will no longer do large changes from release to release like they have done when moving from Windows XP to Windows Vista, rather they will the releases of Windows will be developed in a somewhat incremental fashion. The release will be far from service packs, but we can expect less service packs (Windows XP had 3), rather just one service pack for each version with the major release following soon after. This is a simply due to the requirements of the consumer market that requires a new version of a product with new shiny glittering features that every one will want to have, something like what Apple is doing with MacOS. Now this speed of releasing new OS versions is something that is a requirement of the consumer brings problems to the enterprise market that certainly does not have the need and resources to switch to a new versions of an OS every 3 years or so.

This was the discussion that arose how will the enterprise market react to this change in OS releases, since we who work with enterprise user are aware that our users are sometimes still using Windows 98 and Windows 2000 operating system and most of them are using Windows XP and have no will/need to upgrade to either Windows Vista or the upcoming Windows 7. Microsoft should considered this when they have decided to shift to the new development and release cycle. The way I see the situation now is that they have not considered this or that they have and deiced that their enterprise user will be able follow the new cycle, or that they will skip a release and then wait for the next one.

There is one more problem with the faster release cycle and for the enterprise user it is not a small one when it comes to the enterprise user. Unlike the average Joe consumer the enterprise customer emphasizes ROI and the resource that the company needs to spend on upgrading their software and possibly hardware infrastructure which also accompanied by the expense of training the user and the traditional drop in productivity until the users get accustomed to the new OS. This poses a big risk and big investment to the enterprise user too big in my opinion for them to take the change likely. Another change that the enterprise user will not take lithely is that with the faster release cycle the support periods for the products on the fast track will be shorter which will force the enterprise to change the OS and etc because support is for the enterprise user what the shiny user is for the consumer.

So for the enterprise user from now on they have these two options:

1. Take the risk and upgrade every 3 years
2. Upgrade every 6 years with no updates in the mean time
3. Hope that Microsoft will change their policy

Maybe there has come a time to split the development of Windows into 2 separate tracks, one for the consumer market and one for the enterprise market.

Windows Consumer track

This track should cover the Windows for the average Joe which would be released on 2 – 3 year basis and would include all the latest glittering features that we all like including all the multimedia, game and etc. features that a consumer needs. This edition of Windows would be concentrated on the showstopper features and would be limited to only a 5-6 year support period, since the users will be more likely to change to OS in the meantime be it with new hardware or simply a change of software on the same hardware.

1. Windows Client
2. Windows Home Server

Windows Enterprise track

This track should cover the Windows for the large enterprise customers which would be released on 5 – 6 year basis and would include the latest security and stability features and also increased testing and compatibility checking that is extremely important to the enterprise customer. This edition of Windows would be concentrated to be able to support a longer usage life span and would be on a 10 year support period, since the users will be unlikely to change to OS in the meantime as the cost of doing so are high and the change it self does not bring enough added value to the enterprise customer.

• 1. Windows Enterprise Client
  2. Windows Server

So one day we might finally wake up in a world where enterprise and consumer markets don’t mix with support and release dates but will share the minimal common features that are need to reduce the development and testing costs for each of the OS versions.

Using TransactionScope for handling transactions

Implementing transactions from C#/VB code today might seam easier than ever before, but the path to easier and effective transaction handling has many traps on the way. Microsoft made the life of developers seemingly easier by implementing the TransactionScope class in the .net framework 2.0. I say seemingly easier because you sometimes might find your self baffled by the way your code will behave.

The TransactionScope class uses MS Distributed Transaction Coordinator to manage transactions, so before you start using the TransactionScope, make sure you have DTC running on the Web and Database server and that you have granted enough security permission for the DTC to be able to communicate on thoes machines.

So to get started with TransactionScope, here is a simple code example that seams perfectly OK.

using(TransactionScope outerScope = new TransactionScope())
{
    // Execute query 1

    using(TransactionScope innerScope = new TransactionScope())
    {
        try
        {
            // Execute query 2
        }
        catch (Exception)
        {
        }

        innerScope.Complete();
    }

    outerScope.Complete();
}

Now if query 2 which is inside of the try/catch block, rasies an error you will catch the exception in the try/catch block and handle it, but here is a supprise the application will throw an ObjectDisposedException on line 15 when you try to complete the transaction. This is because the DTC has already caught the exception and although you have handled it, the TransactionScope objects where already disposed by the .Net code and the transaction has been rolled back. Notice that I have said “objects” this is because both of the TransactionScope object have been disposed as they are a part of the same transaction. This is just one example where you might seam surprised that the code is not behaving as you would expect.

The TransactionScope implements something that is referred to as an “Ambient transaction”. This is the transaction in which all database queries are executed. When I say all queries I mean all queries that are executed between the creation of the TransactionScope object and the calling of the Complete() method, including all method calls and their method calls and so on, that are executed between those two lines. That means that you don’t need to handle the transaction in every method that participates in the transaction unless you want to.

You can control the manner in which a method participates in a transaction quite easily. Here is a piece of code to demonstrate:

using(TransactionScope outerScope = new TransactionScope())
{
    // Execute query 1

    using(TransactionScope innerScope =
          new TransactionScope(TransactionScopeOption.Required))
    {
        // Execute query 2
        innerScope.Complete();
    }

    using (TransactionScope innerScope =
           new TransactionScope(TransactionScopeOption.RequiresNew))
    {
        // Execute query 3
        innerScope.Complete();
    }

    using (TransactionScope innerScope =
           new TransactionScope(TransactionScopeOption.Suppress))
    {
        // Execute query 4
        innerScope.Complete();
    }

    outerScope.Complete();
}

This demonstrates the use of the TransactionScopeOption Enumeration to control the way some piece of code will participate in a transaction. The enumeration consist of three options Required, RequiresNew and Suppress. The default options for the TransactionScope constructor is “Required” which states that an ambient transaction is required for this operations, if non exists the transaction will be created. The options “Suppress” states thata the this TransactionScope will not participate in the transaction and therefore all database queries are executed without a transaction. The option of “RequiresNew” states that a new ambient transaction will allways be created and become the root transaction.

There are further options that can be modified when using the TransactionScope class by using a different constructor TransactionScope Constructor (TransactionScopeOption, TransactionOptions) that takes the TransactionOptions struct as a parameter here is an code example.

TransactionOptions options = new TransactionOptions();
options.Timeout = TimeSpan.FromMinutes(1);
options.IsolationLevel = IsolationLevel.Serializable;

using(TransactionScope scope =
      new TransactionScope(TransactionScopeOption.Required, options))
{
    scope.Complete();
}

The TransactionOptions struct has 2 properties Timeout and IsolationLevel. The first is self explanatory the timeout prior to completition of the transaction. The second one is more important, I would say my self crucial to effective handling of transactions. The IsolationLevel Enumeration consist of several levels of isolation: Serializable, RepeatableRead, ReadCommitted, ReadUncommitted, Snapshot, Chaos and Unspecified. With the “Serializable” isolation level being the strictest and “ReadUncommitted” begin the least strict form of isolation.

Serializable means that all transactions occur in a completely isolated fashion; i.e., as if all transactions in the system had executed serially, one after the other. That means no other transaction can be run until the one running is completed. This type of isolation should be used very carefully since it can cause massive timeouts etc. if used with long lasting transactions.

RepeatableRead is a step down from Serializable isolation level, where multiple transaction can run at the same time, but all data read during the transaction can not be modified (exclusive row/page locks) and volatile data cannot be read.

ReadCommitted is a further step down in isolation where the transaction allows for reading and modifying of data that has been used in a select statement, but volatile data cannot be read or changed by other queries.

ReadUncommitted is the least strict isolation level where the transactions can see uncommitted changes performed by other transactions, allowing for dirty reads.

More on isolation levels on Wikipedia.

That sums up the basic use of the TransactionScope class with more to come

Putting C# into SQL

In my latest project we have come across a need to get results from a web service inside SQL Server. First basic reason why we wanted to have such an integration of web services is so that we could use them transparently in Microsoft Reporting Services. That of course is not the only reason we also wanted to use the same results as an input to some of our stored procedures and to do that we need to be able to get the results from the web service through a stored procedure or a table valued function.

The process it self is quite simple if you know where to begin. The beginning is in Sql servers implementation of CLR, with the use of CLR you can easily show those DBA’s how real programing is done . Unfortunately for the DBA’s this is the only way this can be done so they need to trust us “developers” . Seriously this is not such a problem to implement.

Pop open up Visual Studio and create a new “Sql Server Project” in the language of your preference (VB or C#) it’s all the same, but I will use C#. To start us off we need to configure the project first, so open “Project Properties” go to the “Build” section and find the option “Generate serialization assembly” set the option from auto to on. You have to do this because Sql server clr does not allow dynamic xml serialization, I guess it a security thing. Now when you build your project you will end up with two dlls one for you code “project.dll” and the other one for the serialized xml “project.serialization.dll”.

Now add your web service to the project just like in any other project trough the “Add web reference” menu, after you have done sou let’s add a new item to the project, add a stored procedure item to the project. Here is some sample code for the stored procedure.

[Microsoft.SqlServer.Server.SqlProcedure]
public static void WebServiceTest()
{
	// Initialize web service, adjust it for your service
	using (WebService ws = new WebService())
	{
		// Create a template record for the result set
		SqlDataRecord record = new SqlDataRecord(
			// The strings are the names of the columns of the result set
			// first field of type int
			metaData[0] = new SqlMetaData("field1", SqlDbType.Int),
			// second field of type varchar, equivalent of varchar(max)
			metaData[2] = new SqlMetaData("field2", SqlDbType.VarChar, -1),
			// third field of type decimal equivalent of decimal(12,4)
			metaData[4] = new SqlMetaData("field3", SqlDbType.Decimal, 12, 4));

		// Mark the begining of the result-set.
		SqlContext.Pipe.SendResultsStart(record);

		// Send rows back to the client.
		// Get the results from the web service, query a method on your web service
		foreach (Result item in ws.GetResult())
		{
			// set the value for field1
			record.SetInt32("field1", item.Value1);
			// set the value for field2
			record.SetString("field2", item.Value2);
			// set the value for field3
			record.SetDecimal("field3", item.Value3);

			// Send the row back to the client.
			SqlContext.Pipe.SendResultsRow(record);
		}

		// Mark the end of the result-set.
		SqlContext.Pipe.SendResultsEnd();
	}
}

Let’s look at this code. On line 4 is the initialization of the web service you will have to adjust it to your web service. Then you have to initialize a template record for your result set as is done on line 8 trough line 15. We initialize there columns of your result set column one is named “field1″ and has a type of int, column two is named “field2″ and has a type of varchar with length -1 or varchar(max), column three is named “field3″ and has a type of decimal with length 12 and precision 4 or decimal(12,4). On line 18 we start the return of our result set and tell sql that we will be returning items based on record template we have just created. On lines 22 through 33 we iterate through our result set, set the values and use the Sql pipe to send the results. On line 36 we end the sending of results. That is it for the code part, so now we need to register the assembly in the sql server.First you have to set the database trustworthy option to on.

ALTER DATABASE DatabaseName SET TRUSTWORTHY ON

If we do not set this option to on we can’t register our assemblies as UNSAFE, and if we cant do that Sql clr wont allow us to connect to the web service. I think that you could use the EXTERNAL_ACCESS option but I have not tried that.

CREATE ASSEMBLY [AssemblyName]
FROM'C:\AssemblyPath\AssemblyName.dll'
WITH PERMISSION_SET = UNSAFE
GO
CREATE ASSEMBLY [AssembyName]
FROM'C:\AssemblyPath\AssemblyName.Serialization.dll'
WITH PERMISSION_SET = UNSAFE
GO
CREATE PROCEDURE [SchemaName].[WebServiceTest]
AS
EXTERNAL NAME [AssemblyName].Namespace.WebServiceTest

That is it now you can test out your new sql clr procedure.

Connecting to TFS

Like so many things in the programing world, establishing a connection to a TF server can be done in a couple of different ways. I have previously specified everything that you need to start developing an application that will interface with TF server, so I will skip the introduction to TFS development and get my hands dirty right away.

From you code, C# code that is, you can establish a connection to TF server in two basic ways. Both of two create an instance of a TeamFoundationServer class. One is trough a service class TeamFoundationServerFactory and the other is creating an instance of a TeamFoundationServer class manually through a public constructor.

The main difference between the two is that the TeamFoundationServerFactory static class will cache the connected servers, so that every time you request a server, a request is not being sent to the TF server, thus not clogging the server with requests.

TeamFoundationServer server = TeamFoundationServerFactory.GetServer("http://" + serverName + ":" + serverPort);

Now you have your instance of a TeamFoundationServer. Each time you call the GetServer(String) method it will check if the server has been already accessed, and if it was the method will return a cached instance otherwise it will create a new one.You other option is to create an instance of a TeamFoundationServer on your own.

TeamFoundationServer server = new TeamFoundationServer("http://" + serverName + ":" + serverPort);

If you it this way a request will be sent to the TF server to check if it exists, if you do that every time you need something from the server, you will generate a lot of request to the server.Basically if you use the TeamFoundationServerFactory class the framework will handle the caching of the server for you, if you create an instance on your own you will have to store that instance and keep track of it your self.

Authentication

The TeamFoundationServer constructor way has one benefit over the GetServer(String) method. The benefit is seen when you get around to authenticating the users. When you want to provide user authentication through the GetServer method, you will use the overload that has looks like GetServer(String, ICredentialsProvider)
using the UICredentialsProvider class like so:

TeamFoundationServer server = TeamFoundationServerFactory.GetServer("http://" + serverName + ":" + serverPort, new UICredentialsProvider());

server.EnsureAuthenticated();

What this will do, it will create a pop up requesting the user credentials every time the user needs to be re-authenticated. The EnsureAuthenticated() method will first check if the user has already been authenticated, and if not it will create a login pop up for user credentials then authenticating the user.In your software you might want to save the user credentials to use them any time the user needs to authenticate. That’s when TeamFoundationServer Constructor (String, ICredentials, ICredentialsProvider) constructor comes into play. With this constructor you can specify in addition to a UICredentialsProvider object that is used as a fall back if the authentication fails, separate NetworkCredential object to be used for authentication.

NetworkCredential networkCredentials = new NetworkCredential(
   userName,
   password,
   domain);

TeamFoundationServer server = new TeamFoundationServer(
   "http://" + serverName + ":" + serverPort,
   networkCredentials,
   new UICredentialsProvider());

server.EnsureAuthenticated();

This way the code will try to authenticate first with the selected NetworkCredentials and if that fails it will use the UICredentialsProvider to create the pop up and ask the user for credentials. This way you can save and reuse the users credentials when you want to, and if the authentication fails the framework will ask the user for credentials on its own.

Proxy

I was having trouble connection to my company’s TF server, since our workstations are in one domain and the TF server is in a separate domain, with an ISA and a Web filtering server in between. I was getting the HTTP error code 407 “proxy authentication failed”, until I found a way to deal with it I lost around 4 hours. The solution is quite simple you just add this to to your config file.

<system.net>
 <defaultProxy enabled="true" useDefaultCredentials="true">
  <bypasslist>
   <add address="serverName" />
  </bypasslist>
 </defaultProxy>
</system.net>

When you put this in the application will by pass the proxy for the specified network addresses.

Top Gear

That one show about speed, cars and octanes that we all love to watch.

I watched a lot of different shows about cars and related stuff, but one show is the cream of the crop, the kingpin, the leader of the pack. The show is running on BBC now for about two decades set the standard for all others to try to follow, many of them did and have succeeded to a certain degree, but they could only do so much.

I found a nice torrent, and I’m downloading it as I am writing this. The torrent is the seasons of the show as I can figure it from 2002 to 2006. Of course I have already downloaded the latest 2007 shows, and watched them all already. You can see most of the shows on YouTube, if you have not already seen them. There are a lot of clips and most of them contain whole episodes.

Here are two that I especially like, episodes that capture the brilliance of this show.

The race to the North pole, Toyota Hilux vs sled, the episode where they go head to head with the Aston Martin Vantage V8, BMW M6 and Porsche 911 Carrera S and my favorite The destruction of Toyota Hilux.

The core of the show today are the three presenters, that think up the shows and the test, which lets be honest are not that scientific, but they are so fun. The thing that makes this show stand out is the witty and classy British humor and the pure passion of the presenters for what they are doing and they don’t see the making of the show as a job, rather as a fun time. That is what first attracted me to the show, and I believe that this a must for making a good show.

Blogged with Flock

The “Desktop”

Today I read an article on Coding Horror about our precious desktop space, and how we use. That got me thinking about my desktop, and my monitor configuration. Since in the article on coding horror I saw an amazing workspace, showing of 3 monitors on a triple arm monitor stand, looking very sharp and impressive. Since I have bought my laptop I did not think much about my desktop space, since somehow I accepted the fact that I don’t do that much work at home, so I don’t need a multi monitor setup. Lately however I started to think about organizing my self some sort of a home office environment where I could all I need to do, and I don’t mean necessarily only work, rather all my computer related activities. So I should have an environment that meets these basic requirements:

1. Ergonomically shaped
2. Easily organized and cleaned
3. Effective space usage
4. Well illuminated
5. And above all cool and stylish

I am one of those that is willing to sacrifice a bit of ergonomics and everything else listed for more stylish and cool look and feel of my desktop space, on second thought anything of mine related with computers. I believe that a good workspace environment has to be appealing to the eye, since you will be looking at that desktop setup, and your desk space for the entire time you work at your desk. So to lessen the distraction and remove any unnecessary clutter from your desk, you should always design your workspace to find it more appealing to you. My logic is this, if you buy a super cool gadget and put it on your desk, you will want it to be visible to anyone that comes in to the area (like duh ) so you will clean your desk more regularly, and so on. I read a nice article about how to design your own workspace, something like general guide lines to help you with some pointers what to watch out for.

Now to go to the most visible and by some most important part of your workspace, your monitor(s). It is imperative for me to have high quality monitors and a well organized desktop. That got me thinking about expanding my desktop space. My laptop screen (15,4″ wide) is OK for browsing, writing some text and such, but as far as any programing or such goes it is completely inadequate. For development purposes you almost always need to have 2 side by side windows opened, being it the development environment, and the database editor or some documentation file, etc. Some times it would be nice to have 3 windows opened side by side, but it’s not a must, I’m quite satisfied with 2 monitors, and if I need a third there’s always my laptop screen. I’m currently looking at three monitors, all of them wide screen, since I always prefer wider screen for working with code, and the 16:9 ration is perfect for that. We developers are always looking for some extra width, because of all our side windows with tools, properties etc. So here they are, I picked this monitors since they look “pretty” , they are all 20″ wide screen monitors

Dell E207 Wfp

I like this dell monitor for its stylish looks and the nice “Dell” logo on the bottom, haven’t yet read any reviews but knowing Dell I have no reason to doubt it’s quality.

HP L2045w
I have many stuff at home that are HP made, like my laptop (and laptop gear), printers, web cam, IPaq and so on, all of them are very well made, and are easy to operate. By all this you can figure out that I like my HP devices very much , so I see no reason why this monitor would be of bad quality.

and the Samsung 206bw

Samsung monitors have always been high quality monitors, but have never been very stylish, but these new wide screen monitors, look very sharp and sleek.

Now picking from these three is very difficult, because the Samsung is the cheapest and probably high quality, but the Dell and HP look better , I’m sure you understand my dilemma

Of course to connect everything to my laptop I need some sort of a external graphic card since on my dock I can only attach one extra monitor. The choice here unlike with the monitors is very simple, the Matrox DualHead 2 Go Digital is the one I’m looking at. As far as I can figure it is simple to connect and use, and that is all that I want form it.

Matrox DualHead 2 Go Digital

No desktop is complete without a perfect and ergonomic keyboard and mouse. Here I always prefer Logitech products, they are easy to use, mostly ergonomically designed and Logitech support is the best I used ever.

Logitech Desktop Wave keyboard

Stylish, ergonomic, new design, sets of extra key to setup, what could you ask more.

Logitech MX Revolution mouse

The best mouse ever, I currently use the MX 1000 mouse and I extremely happy with it, carrying it around with me all the time. The only thing about the mouse is that I would prefer the bluetooth version of the mouse rather the RF version, so I don’t have to have any extra receivers plugged into my laptop. That would mean that I can charge it at home, and carry it around with me, with out the need for the base station which is quite large, and oddly shaped so it does not fit into my laptop bag seamlessly.

Now when you sum up all the figures for the devices listed here, it is hefty amount of $, but what you need, you need, and there is no avoiding the costs:)

Blogged with Flock