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

Data Modelling Jazz

When we often think of data modelling it is a pretty picture created in Visio and for the some one more serious about data modelling it is representing entities, attributes and relationships in a meaningful manner. I didn’t realise modelling languages are different and a tool such as Visio supports/ works on such languages. e.g IDEFIX(Integration Definition for Information Modelling) is modelling language .. Woah that definition really woke me up .. and as usual this was developed in the US Airforce in 1985

The primary tool of a database designer is the data model. It’s such a great tool because it can show the details not only of single tables at a time, but the relationships between several
entities at a time. Of course it is not the only way to document a database;

• Often a product that features a database as the central focus will include a document that lists all tables, data types, and relationships. (developers think can’t be bothered)
• Every good DBA has a script of the database saved somewhere for re-creating the database. (developers think am still not bothered)
• SQL Server’s metadata includes ways to add properties to the database to describe the objects. (developers by now would think oh get a life will you).

Some common terms you would come across are Entities which are synonymous to tables in database, attributes which are synonymous to column definitions in a table and relationships represent how two entities relate to each other.  We represent these pictorially or grammatically in written text Anyway my idea of blogging about Data Modelling was to drop a few notes on some practices we could adopt while modelling data.

• Entity names There are two ways you can go about these: plural or singular. Some argue tables names should be singular , but many feel that the table name refers to the set of rows and should be plural. Whatever convention you choose be consistent with it, mixing and matching could end up confusing the person reading the data model.
• Attribute names: It’s generally not necessary to repeat the entity name in the attribute name, except for the primary key. The entity name is implied by the attribute’s inclusion in the entity. The chosen attribute name should reflect precisely what is contained in the attribute and how it relates to the entity.
• Relationships: Name relationships with verb phrases, which make the relationship between a parent and child entity a readable sentence. The sentence expresses the
  relationship using the entity names and the relationship cardinality. The relationship sentence is a very powerful tool for communicating the purpose of the relationships with non technical members of the project team (e.g., customer representatives.
• Domains: Define domains for your attributes, implementing type inheritance wherever possible to take advantage of domains that are similar. Using domains gives you a set of standard templates to use when building databases that ensures consistency across your database.
• Objects: Define every object so it is clear what you had in mind when you created a given object. This is a tremendously valuable practice to get into, as it will pay off later when questions are asked about the objects, and it will serve as documentation to provide to other programmers and/or users.