- You can achieve all this with only a small amount of development effort.
- You don't have to write mundane user management and authentication code for your application, allowing you to focus on your application's features and get to market quicker.
- People will be more likely to use to your application or website because they can log in with their existing credentials and don't have to register.
Friday, 16 October 2009
Tuesday, 21 April 2009
For example, say you had a document management system and you wanted the ability to return all documents in a given folder, as well as the need to return an individual document. This would normally require two seperate stored procedures:
sp_GetDocumentsInFolder @folderId int
sp_GetDocument @docId int
Whilst effective, this approach means that if the query changes in one stored procedure, you will also have to update the corresponding stored procedure to ensure that they both continue to return the same columns. The difficulty of this change is directly proportional to the complexity of the underlying query.
Another approach is to have one stored procedure that generates a dynamic SQL statement as a string variable that is passed to the sp_execute system stored procedure. This approach is even more cumbersome because it involves concatenating strings and you lose all the performance benefits of a stored procedures this way too, because the query plan cannot be cached by the database engine; it potentially leaves you open to SQL injection attacks also.
Wouldn't it be better if you could write the query once and only include conditions in the WHERE clause if necessary? Well, you can do just that using the following technique:
DECLARE @docId int, @folderId int
SET @docId = null
SET @folderId = 100
WHERE (@docId IS NULL OR ID = @docId)
AND (@folderId IS NULL OR FolderID = @folderId)
In the example above, two variables are defined: one to hold the ID of the required document and the other to hold the ID of the folder whose documents are required. The WHERE condition includes logic to omit a condition if the corresponding variable is set to NULL, and in this example, the document ID is ignored because it is set to NULL and only documents in folder ID 100 will be returned.
To return a single document instead, we simply set the @docID variable to the primary key ID of the required document and set the @folderId to NULL, as follows:
SET @docId = 5
SET @folderId = null
You could of course set the @folderId value as well to only return the document if it existed in the specified folder.
Likewise, if you set both variables to NULL, all documents would be returned regardless of their ID or Folder ID values.
This query can be used in a stored procedure as follows:
sp_GetDocumentDetails @folderID int, @docId int
Passing a value to the first argument and NULL to the second argument will result in all documents in that folder being returned. Passing NULL to the first argument and a value to the second argument results in only a single document being returned (if a matching ID was found of course). And finally, passing NULL to both arguments will return all documents.
Using this approach, the query to return document details only ever needs to be maintained in one place and the results from the stored procedure varies depending on the values passed in to the arguments. You also get all of the speed and security benefits of using stored procedures.
Copyright © 2009 Software Nation
Tuesday, 3 February 2009
Thursday, 29 January 2009
Since the release of Sql Server 2005, Microsoft added a whole raft of features for storing, reading and manipulating XML data to their enterprise database platform.
If you are writing a stored procedure and want to pass a collection of data as an argument, one way to achieve this is to pass the data in as an XML string, whose contents can then be parsed into a rowset, like so:
DECLARE @xmlData XML
SET @xmlData = '<root><data id="1" text="Dave"/><data id="2" text="Neil"/><data id="3" text="Anthony"/></root>'
SELECT ParamValues.myData.value('./@id','int') as ID,
ParamValues.myData.value('./@text','nvarchar(50)') as [Text]
FROM @xmlData.nodes('/root/data') as ParamValues(myData)
ORDER BY [Text]
This results in the following output:
The resultant rowset can be used as part of an IN clause or can be iterated through via a cursor or as part of an INSERT...SELECT statement, for example.
This approach can also be used in Sql Server 2008.
Tuesday, 27 January 2009
“Flow” is a term used in psychological circles to describe a human state of high concentration, which most people refer to as being “in the zone.” Whenever a person is engrossed by a task they become immersed in a state of flow and are oblivious to the passage of time, whilst experiencing a mild state of euphoria.
Effective creative workers enter a state of flow for several hours, during which time their productivity skyrockets, but flow requires silence to be achieved and cannot be switched on and off like a tap. The average person takes around 15 minutes to start flowing, whilst it only takes a matter of seconds to be distracted and thus removed from flow.
Software developers must enter a state of flow on a regular basis in order to write code that solves a specific problem. Roughly half of a developer’s working day involves solving problems on their own and as such they need to work in an environment that encourages and sustains flow. The remainder of their day is spent communicating with one of more people, either collaborating or attending meetings etc.
Consider that it takes 15 minutes for a developer to enter a state of flow. If you were to interrupt a developer to ask a question and it takes five minutes for them to answer, it will take a further 15 minutes for them to regain that state of flow, resulting in a 20 minute loss of productivity. Clearly, if a developer is prevented from flowing several times during the day their work rate declines substantially.
Such interruptions needn’t be direct communication with a developer and can result from ambient noise levels, such as people talking to each other at elevated volume, a fire alarm test, a shredder or photocopier, the sound from a colleague's PC or the constant ringing of an un-answered phone. A noisy environment is a sure fire way to disrupt flow, which is why the large open plan office is the scourge of creativity and why a lot of developers wear headphones. Listening to music uses a different part of the brain than that required to solve problems and can be used to filter out ambient noise.
A minority of creative professionals are oblivious to their surroundings and cannot easily be distracted once in a state of flow, but the majority need a quiet and calm environment in which to prosper. Extreme concentration almost always requires complete silence.
The cost of office space can be very high and companies try to make the best use of their available room by putting as many people as possible into the same office. Whilst this approach may seem fiscally sensible it actually has a negative impact on creative thinking and probably costs more in terms of lost productivity and delayed projects due to innumerable daily disturbances.
© 2009 Software Nation
Monday, 26 January 2009
Hosted software applications are typically accessed via a web browser over the Internet, but this needn’t be the case. A user could install a SaaS application on their PC in the traditional manner, but it will only operate as long as they have a valid subscription.
Benefits of SaaS
A customer may baulk at the idea of paying a subscription for software because it breaks away from “the norm” when procuring software; however the benefits are legion:
Low initial capital outlay = low risk
Normally when implementing a software application the customer must pay large fees upfront for software licences and pay specialist consultants pots of money to install and configure the software at their premises; they also have to provision suitable hardware on which to run the new software, which can add massively to the total cost.
With SaaS the potential customer can try the software free of charge before making a decision whether to buy. If the application runs in a web browser there is no additional software to install so they can be up and running in minutes. If they decide to buy, there may be a set up or administration fee in the hundreds or thousands of pounds, rather than the tens of thousands, after which they pay a set fee for the duration of their service agreement, either monthly, quarterly, bi-annually or annually, for example.
No special hardware requirements
With hosted SaaS applications the vendor takes responsibility for hosting the application and the storage of all data. As such the customer doesn’t have to pay for expensive hardware infrastructure to host the application themselves.
No more backups
Because a SaaS application vendor looks after the customer’s data they do not have to worry about scheduling regular backups, disaster recovery, business continuity or physical access to data by unauthorised people.
Regular updates and bug fixes
Normally a user can wait several months or years to receive bug fixes or feature enhancements to their installed software applications. With browser-based SaaS applications there is normally nothing special to install, so regular updates to fix any bugs, add new functionality and improve existing functionality are easy to deploy. Best of all, no effort is required by the customer when a new release is made; they simply log into the new release of the application in the exact same manner as before.
Access the application anywhere in the world
With installed software you can only access the application on PCs where it has been physically installed. With the hosted SaaS model a user can access their applications anywhere in the world with an Internet connection: from the office, from home, an Internet café on holiday or an Internet terminal in their hotel lobby when away on business, even from their seat on a plane that has in-flight Internet connectivity.
As software vendors, there are many benefits to developing hosted SaaS applications. So much so that Microsoft and other big software companies are trying desperately to move from installed software to a SaaS model for these reasons:
No piracy concerns
With hosted software, application files are never released to the outside world so they can never be copied or used illegally. Therefore the vendor doesn't have to waste valuable resources developing and testing anti-piracy measures that can take a lot of time, whilst providing no benefit to the end user whatsoever.
No need to write installers
As surprising as it may seem, creating the simple setup.exe installer program used to install software on a PC is a project in itself and can take many months to develop and test, directly proportional the complexity of the application associated. With the hosted SaaS model the customer never has to install the software so the vendor never has to write an installer for it.
There is only ever one version – the current version
A big problem for software companies is providing support for historical versions of each of their applications. For example, Microsoft Support staff must provide assistance for Office 2007, 2003, 2000 and so on until an old version is retired. A lot of support time can be spent simply trying to determine what version of the application the user is running before actually finding a solution to their problem.
With the hosted SaaS model every user accesses the most recent release of the application, meaning support staff can concentrate on finding a solution to the problem, rather than discovering the application version involved.
For software companies the nirvana is the ability to generate recurring revenues from their applications in the form of regular automated subscription payments. Before the ubiquity of the Internet, such subscription models were not really feasible without the installation of specialised communications hardware on the client's premises. Now, with almost every PC in the civilised world having a high-speed Internet connection, accessing a server via the Internet, either to look at a website, download a file or access a hosted software application is easy.
With SaaS, the recurring revenue model allows the vendor to continually improve their software in accordance with their clients’ needs and to continually re-invest in their hosting facilities to increase performance. It also goes towards the research and development of new applications and in bolstering technical support capabilities for improved customer service.
When a new release of a hosted SaaS application is ready for launch it is very easy to deploy it for public consumption with minimal or no downtime whatsoever. The release process is completely invisible to the user and they get all of the benefits of the new release with no effort on their part whatsoever.
Multiple language support
A software vendor with an international customer base must create a separate version of their installed applications for each language they support. With the hosted SaaS approach this overhead is not required, and as such different languages can be supported by simply providing a dictionary of terms used by the application in the required language.
No manufacturing and distribution costs
Because hosted SaaS applications are accessed over the Internet there is no need to produce boxed software containing installation discs or costly printed manuals that must be shipped around the world and sold through traditional retail channels.
Disadvantages of SaaS
Of course, no technical paradigm is perfect, but with the hosted SaaS model the perceived disadvantages are few and far between:
Where is my data?
Potential users of SaaS applications will almost always ask “where is my data and is it safe?” Clients are used to installing software and keeping their sensitive data onsite under their jurisdiction. With the hosted SaaS model all customer data is stored on shared centralralised servers and naturally this can raise concerns about the security of data and whether it can be accessed by others or copied to disc and lost in the post, for example.
This fear is usually allayed once the customer is made aware of the security precautions undertaken at the hosting company’s premises where their data is physically located, which can put some military bunkers to shame. Oftentimes the security of a dedicated hosted environment
will be far greater than that of a locked server room in the customer’s building.
A customer's service level agreement (SLA) with a SaaS vendor should always include security guarantees and the customer must also have the ability to obtain a copy of their data should they terminate the agreement further down the line. The SLA should also include measures to prevent the vendor from ramping the price up too high at contract renewal time, once the application becomes a vital part of the customer's day-to-day operations.
Application availability concerns
With hosted SaaS software the vendor should endeavour to make their applications available 24x7x365; however no hosting environment is 100% downtime free. Every server needs regular software patching to keep it secure and all hardware is susceptible to failure; while the former is an unavoidable necessity, the latter can be mitigated by building hardware redundancy into the server infrastructure. This basically means “at least two of everything”: two power supplies; two internet connections; at least two hard drives etc, etc. This way, if one power supply fails the other will take over, or if one internet provider loses connectivity the other provider will continue service as normal, often with no perceptible impact on the user.
With traditional installed software, the client can schedule maintenance and downtime to suit their own schedule, but with hosted SaaS the vendor must ensure to notify all customers of planned downtime well in advance so that they can either object strongly or work around it.
Again, all of this will be covered in the service level agreement between the customer and the vendor.
For a hosted SaaS vendor, the following disadvantages are far outweighed by the benefits:
Building software that is secure and scalable
All customers access their hosted applications on the same server(s) and their data will typically be stored alongside each others’ in the same database – an approach known as single instance, multi-tenancy, meaning a single installation (instance) of the software serves many users (tenants).
This approach presents a unique set of challenges when designing applications that scale to serve many hundreds or thousands of concurrent users efficiently, whilst enforcing a strong security
mandate. These challenges are not insurmountable but they do require a different mindset from creating standalone applications that are installed on a PC and have access to powerful dedicated resources, such as a local hard disk drive, a powerful graphics card and the latest multi-code processor – all for just one user. A SaaS application on the other hand must use a server’s precious (and expensive) finite hardware resources judiciously whilst handling many users at the same time.
Hardware and hosting costs
The investment in server hardware for a hosted SaaS vendor is substantial, especially when you factor in the need for redundancy, meaning a server must contain “at least two of everything”, as discussed earlier.
Furthermore, the cost of hosting the server infrastructure is considerable because vendors have to effectively rent floor space at the hosting company’s secure premises where their server hardware will sit. They then have to rent power usage and pay for the bandwidth consumed by their customers. Of course, if a SaaS vendor has the resources they can build and operate their own hosting environment, but it's not unusual for vendors to outsource this part of their business, leaving them to concentrate on building, selling and supporting their applications.
All of these costs are recouped by the licence fee income and vendors can even charge customers extra when they exceed the monthly bandwidth allowance in their SLA, but if they were to skimp on the infrastructure customers would soon go elsewhere when performance drops and availability is reduced because the servers are overburdened by traffic.
None of the above is cheap.
It is hard to find talented technical people who understand SaaS, such as developers, testers and systems engineers. Designing, building, testing and hosting SaaS applications is different from standalone software and requires a certain mindset and technical skills. There is a scarcity of such people in the marketplace at any given time, so recruiting is a never-ending task.
Providing customer support that is second to none
When a client is paying for software on a subscription basis they expect to receive an excellent level of service in correlation to their subscription fees. When they need to call for telephone support they expect to get through quickly, not sit on hold for hours and when they do speak to someone they expect them to be knowledgeable and expedient. As with hardware, this requires significant investment to recruit, train and retain remarkable support personnel.
SaaS is being embraced the world over by IT executives who are looking to reduce risk and overhead by allowing software vendors to host their applications and look after their data. Dramatic savings can be made in terms of salary and hardware costs when comparing a SaaS application subscription to the ongoing costs involved with in-house developed and hosted software.
Of course, not all software benefits from the SaaS model, but there are an increasing number of useful applications out there today for those of us that follow the edict of "buy rather than build".
Copyright © 2009 Software Nation