SQL - Change tracking

Most of the applications which I worked till now, had one or the other requirement to handle auditing feature to track the changes on data. There are different ways to achieve this. Very often we use triggers to update another table whenever there is a change on primary table. Or handle it on the stored proc which is used for inserting or updating data.


Now, with Sql 2008, this made very easy. The new feature introduced on SQL 2008 “Change tracking” which enables change tracking without any coding required.

First, to enable this change tracking, we need to update the setting on DB

After that we need to enable this on the table whichever needs to be tracked.

Once the table is enabled with tracking, you will be able to track the changes by version.

We can get all the changes by using function called CHANGETABLE. sample use of function will look like below.

SELECT * FROM CHANGETABLE


(CHANGES dbo.Employees,0) as CT


This query will provide details with SYS_CHANGE_VERSION, SYS_CHANGE_OPERATION, SYS_CHANGE_COLUMNS, SYS_CHANGE_CONTEXT, ID columns.

Take a look at this article http://www.sql-server-performance.com/articles/audit/change_tracking_2008_p1.aspx  which talk about this article in detail.

TDE (Transparent Data Encryption) on Sql 2008

Most of the Db design, we consider about security on data level. What are the critical data, if need to save them encrypted or hashed etc. There are somethings which might need more attention than just storing the data. What if the backup taken been stolen by someone? If someone gets mdf file, they can easily restore the data on to any server and get access to the data.


One of the new feuture introduced with Sql 2008 deals with this security. This is called transparent data encryption. This stores the mdf and ldf files encrypted. Data is encrypted while writing to disk and decrypted while read form the disk. The "transparent" aspect of TDE is that the encryption is performed by the database engine and SQL Server clients are completely unaware of it

Here is more details on how we can use that on our DBs http://msdn.microsoft.com/en-us/library/bb934049.aspx

WCF Exceptions and faults

The exception handling is an integral part of any application. It depends on the situations and the severity of the exception that defines what we want to do with the exception. When working on webservices, especially while interacting with third party application, I always thought its better idea to catch any exception in method and return that as string. But this doesn’t really work if we have to return some other datatype and also we need to insist client apps to check for Error string to get the error message. There are chances that client apps don’t care about the Error string that was returned by service. So the exception thrown on service will still be treated as valid process on the client apps.


When I started working with WCF, I could find different ways to handle the exceptions.

• Catch the exception on service and return as string. (I have already explained what the part of using this way)

• Throw the exception directly. But how does this effect on client apps

• And convert the exceptions to Faults which client apps can understand.

Let’s see if throwing exceptions on service really help us

If there is any exception on wcf service, the client app will always receive a fault exception as “The server was unable to process the request due to an internal error. For more information about the error, either turn on IncludeExceptionDetailInFaults (either from ServiceBehaviorAttribute or from the <serviceDebug> configuration behavior) on the server in order to send the exception information back to the client, or turn on tracing as per the Microsoft .NET Framework 3.0 SDK documentation and inspect the server trace logs.” This does not provide any information about the exception. If we enable the detail on server , it will expose implementation details to the client.

As a summary exposing exceptions to client has many limitations, so it’s always a better idea to map the exceptions on the wcf service to faults and let the client apps to deal with it.

Method overloading in WCF service

Consider a scenario where we need to expose the service method GetAccount. The method can accept accountid in some cases or username or some other reference. One way to create this on wcf is to create different methods as GetAccountbyID, GetAccountByName etc. But creating overloading methods provides better way of handling it.

When we were using webservice, the only way to specify the alias name in webmethod attribute but the client app has to refer the service with alias name. So this doesn’t really qualify as overloaded method on client side but it’s an overloading method on the server side.

Sample overloaded method on webservice

[WebMethod(MessageName="GetAccountByID")]
public string GetAccount(int accountID)
{
//return account data
}






[WebMethod(MessageName="GetAccountByName")]
public string GetAccount (string userName)
{
//return account data
}

Now let’s see how we can do this with WCF service methods. Let’s consider the approach of webservice here. The WCF service would look like

My service interface would look like below

[OperationContract(Name = "GetAccountByID")]
string GetAccount(int value);


[OperationContract(Name = "GetAccountByName")]
string GetAccount(string userName);

and the service class will follow as

public string GetAccount(int value)
{
return string.Format("You entered: {0}", value);
}


public string GetAccount(string username)
{
return string.Format("You entered: {0}", username);
}


Lets refer this service on client side and see how we can consume it

As you can see, it is split as different methods on client side. So one thing clear here is that the method which is referred on the client side is referring to the name attribute defined on operationcontract.

GDI memory leak in Windows applications

I have had many bitter experiences with memrory leaks on winwos applications. Had spent many day and nights figuring out the actual issues. This can occur due to many reasons. Most of the time came to conclusion that it was either Bitmap objcts or any COM objects.

Found a very interesting article which explains one of the approaches to handle this error http://megakemp.wordpress.com/2009/02/25/gdi-memory-leak-in-windows-forms/

Uniqueidentifier (GUID) on SQL

Uniqueidentifiers are alternate way to have them as primary keys. Till recent past, I always used to recommend Identity column on our SQL designs . So I was curious to find out what are the advantages and limitations of using uniqueidentifiers than using identity.


Only advantage I could find by using uniqueidentifier is that the number which is generated is unique throughout the world. Another advantage is that you don’t have rely on SQL to create this uniqueidentifier, we could even generate using out C# code and insert to sql tables. The value which is generated by C# and SQL will be unique anyways.

Is uniqueidentifier suitable for primary key?

The uniqueidentifier can be used as primary key but we need to consider some of the conditions.

• The primary key will have clustered index by default which doesn’t work very well for uniqueidentifier. The uniqueueidentfier doesn’t create the ids in sequential order unless we use newsequentialid() (which is available from sql 2005 onwards). When we create clustered index, the records will be physically sorted. This means that, if you put a clustered index on a column that does not sequentially grow, SQL Server will have some work making sure that the records are correctly ordered physically when you insert new records.

• Some of the applications expect the identifier of last inserted record. E.g if we are using identity column, we can get the last inserted value using scope_identity or @@identity. We will not be able to get the uniqueidentifier value unless using a query to get the latest record.

Above considerations are also kinds of limitations for some of the DB designs. Another important note here is that the uniqueueidentifier column stores 16 byte so it bigger than int.

But if you consider replicating the database and having identity column in your tables, it would create lot of issues. So I would say uniqueidentifier really helpful on the DB’s which requires replications.