Determine a Leap Year?

Armando Prato Armando Prato had written a SQL tip on how to determine a leap year the trick used is pretty neat, His example is in TSQL as below , but i guess we could use the idea in any language

   1: create function dbo.fn_IsLeapYear

   2:  (@year int)

   3: returns bit

   4: as

   5: begin    

   6:    return

   7: (select 

   8:     case datepart(mm, dateadd(dd, 1, cast((cast(@year as varchar(4)) + '0228') as datetime)))     

   9:     when 2 then 1     else 0     

  10:     end

  11: )

  12: end

  13: go

I like the idea of appending 0228 to the year and finding out if it is a leap year, The function takes in the year, appends '0228' to it (for February 28th) and adds a day. If the month of the next day is a 2, then we're still in February so it must be a leap year!  If not, it is not a leap year.

In C# this could be something like , I just thought its worth mentioning this on the blog for my record at the least

   1: bool isLeapYear = ((new DateTime(<int year value>, 02, 28)).AddDays(1).Date.Month.ToString() == "2")

Tracking v/s Capturing changes

Change Data Capture has by far been my favourite feature everytime i think about SQL Server 2008. The really neat bit is something I missed during my learning process, There are two flavours to capturing changes, the change itself and the data that has changed, this is what distinguishes Change Data Tracking and Change Data Capture in SQL Server 2008

Change data capture provides historical change information for a user table by capturing both the fact that DML changes were made and the actual data that was changed. Changes are captured by using an asynchronous process that reads the transaction log and has a low impact on the system.. When you want to stage data in logical blocks such as a website publishing engine, or a clearing system this feature could prove very useful. This is mainly because of the granularity of the changes that are captured and the nature in which they are stored providing no coupling to the object whose changes are captured.

Change tracking on the other hand captures the rows in a table that changed, but does not capture the data itself. This allows applications to determine rows that have changed only with the latest row data being available in the user tables. Therefore, change tracking is more limited in the historical questions it can answer compared to change data capture. However, for applications that do not require historical information, there is far less storage overhead because of the changed data not being captured. It is the data captured which causes the database to grow. A synchronous tracking mechanism is used to track the changes and has been designed to have minimal overhead to the DML operations.

Either of these features can be used to synchronize applications or there database engines. Synchronization can be implemented in applications in two directions, one-way and two way.

In One-way synchronization applications, such as a client or mid-tier caching application, can be built that use change tracking. e.g, a caching application requires data to be stored in the database and to be cached in other data stores. In this scenario the application must be able to keep the cache up-to-date with any changes that have been made to the database. There are no changes to pass back to the Database Engine.

In two way synchronization, the data in the Database Engine is synchronized with one or more data stores. The data in those stores can be updated and the changes must be synchronized back to the database. A good example of two-way synchronization is an application which is occasionally connected such as a mobile application. In this type of application, a client application queries and updates a local store. When a connection is available between a client and server, the application will synchronize with a server, and changed data flows in both directions. In two-way synchronization applications must be able to detect conflicts. A conflict would occur if the same data was changed in both data stores in the time between synchronization's. With the ability to detect conflicts, an application can make sure that changes are not lost.

So my misinterpretation that change capture and change tracking meant the same proved to be wrong. This really useful feature could be put to use effectively in a scalable manner by choosing the right flavour of change capture which is based on the needs or nature of your application. There is no denial that applications on SQL Server versions prior to 2005 will need a major overhaul if there is an existing mechanism in place, that said it is best not to underestimate the implementation of Change Data Capture for a existing application. However new applications could base there designs around this feature and seek to benefit rapidly.

On this note a quick note to people who use Log shipping, The feature is useful when batch processing of transactions is to be done on a regular frequency, however it is still limited in not being able to identify each transaction individually, there is no denial however that is the best choice for Disaster Recovery options.

Concurrency Control in .Net Data Tiers

Most of our data in applications is stored in relational databases, although there are other ways of storing data I guess about 75% of applications use RDBMS. While using these databases we write .Net code which functions correctly but may incorrectly write, read or handle data in the run time. e.g. I have found a common case where developers tend not to use some of the features built into the RDBMS system under the covers, and concentrate so much on getting the application tier right, that they fall into pitfalls that arise out of data handling , One such issue is concurrency of data, It is one of those things developers don’t look at when they hit a data handling issue. a consequence is a lack of transaction capabilities , sometimes they don’t analyse that the root cause could be data handling. Agree or deny I at the least cannot think of applications used for business not to have transactions. More often than not business processes in a application translate into a transaction block or unit.

Concurrency is one such issue which can be controlled and exercised. It can be in an optimistic or pessimistic mechanism, whatever be the mode exercised it is important to understand the concept and how it can be exercised.

Optimistic concurrency lets the last update succeed and can make data viewed by the end user dirty, if a update happened since data was displayed to the user. It is the applications responsibility to determine stale data and then decide to perform an update based on the decision it takes. Optimistic concurrency is used extensively in .NET to address the needs of mobile
and disconnected applications, where locking data rows for prolonged periods of time would be infeasible. Also, maintaining record locks requires a persistent connection to the database server, which is not possible in disconnected applications

In the pessimistic scenario, read locks are obtained by the consumer of the data and any updates to the same data are prevented, Pessimistic concurrency requires a persistent connection to the database and is not a scalable option when users are interacting with data, because records might be
locked for relatively large periods of time.

These are the mechanisms you adopt. To implement this in a data tier of a .Net application developers use transactions

Most of today’s applications need to support transactions for maintaining the integrity of a system’s data. There are several approaches to transaction management; however, each approach fits into one of two basic programming models:

Manual transactions. You write code that uses the transaction support features of either ADO.NET or Transact-SQL directly in your component code or stored procedures, respectively.

Automatic transactions. Using Enterprise Services (COM+), you add declarative  attributes to your .NET classes to specify the transactional requirements of your objects at run time. You can use this model to easily configure multiple components to perform work within the same transaction.

To decide if u want to use transactions in SQL code, ADO.Net or Automatic transaction I found a simple decision tree which allows you to make a decision on which method to use to implement transactions, of course you make the decision if you need to use transactions in .Net

Astoria Data Services – IIS + SQL Server ?

A few months ago I had the opportunity to preview Astoria Data Services in a NextGen User Group meeting and I was thinking I have seen this before, but i failed to recollect where at that time, read the following you will see why i am pointing this out. At the time of preview MS promised to add security to Astoria, Basically all I could see was SQL queries on the browser URL / request URL

When you install IIS on a machine , in management console of windows you have an option to configure SQL XML support for IIS. Do this and then configure a Virtual directory in IIS, which offers quite a list of decent security settings. Configure these and browse to the URL with a SQL query in the URL and see what happens, there ain’t much difference?

I am not cynical but i want to point out this is not new, we will however have to wait and see what additional features are offered by Astoria before we can comment

Oracle 9i and 11g Data Access Components for .Net

I am currently working with SQL Servers and Oracle servers and products communicate with both databases, In my previous job where we used only SQL Server everything was easy as it was native to Microsoft’s products, however the world of .Net with Oracle has its pain points. A particular scenario i came across today was having a web server with Oracle 9i Data Access Components and 11g Data Access Components on the same server and getting them to work for two different ASP.Net sites. I initially installed the ODAC components without the client thinking it was enough for the ASP.Net site to work, however as it turns out there is more to it than i thought it would be

When i started Oracle 9i client components where already installed on the server and the Products using 9i components where working fine. However the moment I installed the 11g Data Access Components the Products using 9i stopped working throwing exceptions and neither where products using 11g components working. So i had to uninstall 11g Data Access components and leave Product A working. So sort this problem i googled’ a lot and found information scattered all over the place, In my need to have everything in one place i thought i jot down a few notes. So to fix the issue I had to do the following

Install a Oracle 11i client home.

Install Oracle 11i Data Access components into the home created in the previous step

The we have to make sure the odp.net folder which resides under the home directory has permissions for ASP.Net and Network Service accounts for read and execute on all sub folders and files.

It doesn’t end there if your connection strings use TNS names configure tnsnames.ora in the network\admin folder of the 11i client folder. In our case the 11i Components where used to access 9i databases so i just copied the 9i tnsnames.ora file into the 11i client.

Once all this is done you should be good to go with your applications. Most provider related exceptions should be resolved when you follow this procedure.

Through with the 2nd year, 1 more to go

La la lalalalalala !!! Just checked my 2nd year MBA results, quite a relief to have passed with some good scores.. I was a bit worried I wouldnt get through... considering the power naps between assignment work ...

Processes, Engineering and Organisations

I reflect on my miniscule career of eight years and some management studies and I find myself thinking about how i would want to do business or run an organisation, I understand profit is the basis for any business being brutally factual about it , but my point is as profit increases and businesses grow rate of returns decrease due to scalability and efficiency issues. As I start my rant I have to mention there is a clear difference in engineering process and quality processes. Processes which are used to engineer a product are different from processes using to perform business. Trying to smudge one with the other in the name of organisational vision is in itself a game most service providers play and the IT industry is full of such innuendos , does any organisation care to mention what engineering processes are going to be used in there project tenders, I guess not.. some how the most important things for engineering which is the task in hand fail to make the list of priorities. We are too busy to pay attention to detail and want the bare bones in place.

I have been working on improving engineering processes for sometime now and have been at both ends, creating and consuming processes. Some organisations use the fixed cost project model and would probably say I will pass as the stakeholders are only interested in there projects and not the engineering process itself, however what they do like is a accreditation such as ISO etc. Even though the PMO as a functional body exists, it doesn't really work as a unified body other than trying to sort out dependencies and integration issues and they wonder why most projects are over budgeted and running longer they should be , If these organisations join the bandwagon of adopting a process like Scrum, Prince or CMM they are not going to find much mileage unless they have there fundamentals sorted out , simple things like infrastructure, configuration management and engineering processes some how manage to get to the list of requisites of a project only at the end of a disaster, or in some cases where you have outsourced your development in a Root Cause Analysis, the funny thing about the RCA is in my experience no one bothers or cares to look at it and there are no lessons learnt either, funny the person who has to come up with it also has mask the most obvious factors which increase cost..

To run an organisation with innovative engineering and quality processes takes more than training a bunch of individuals on some process and asking them to get on with it, Organisational leaders such as CTO’s and CEO’s need to understand that unless it is pipelined form the top in the form of actions, they are not going to maximise return on investment. Unless they do so , PMO' bodies will not action anything because at the moment every organisation is so concerned about short term goals and profits that they have laid the rules of sustainability in business to rest, It is not that organisations don’t do enough , it is just that they try to do something only when the eggs are rotten..

Oh i work in IT so the references to CMM/ Scrum/ Prince you can take anything for an example Six Sigma, ISO, TQM , I am on a train at 10 in the night and this is so not what i should be thinking about