SQL Server FILESTREAM - Is it for you?

SQL Server 2008 introduced a new feature called FILESTREAM, which in simple terms is an attribute for varbinary(max) data type. It allows the unstructured data such as BLOBs beyond a certain size to be stored on the file system instead of DB. This brings in new choices for storing BLOB data.

FILESTREAM in a few words:
- Stores BLOBs in the file system, but through the database thereby providing transactional consistency
- BLOB size is limited only by the available file storage space [Previously, BLOB size limited to 2gb]
- The underlying file system storage mapping can be to SAN or network paths.
- The BLOBs stored in file system can be accessed through DB, directly accessing file folds on OS, or by streaming the files through a network share.

FILESTREAM in more detail:
- The data belonging a DB instance enabled with FILESTREAM stores data on the file system in what is called a Data Container. The data container has the files created using cryptic names (based on guid) and maintained by the SQL Server, as the BLOB data undergoes change. Since the data container is also accessible through the file system, any incorrect tweaking of the container could lead to data corruption and render the container useless. This is a risk to monitor all the time and take preventive actions to avoid.
- We could create multiple data containers and map them to the SQL Server as filegroups thereby providing load balancing with multiple data storage sinks.

Some of the key questions from a broader deployment, maintainability, integrity perspective:

1. How to handle data recovery in case of data container corruption?
2. Can we just backup the DB without the file system data? [assuming data is in SAN, which need not be moved]
3. I would like to back up or restore the DB and just remap the filegroup path information [that maps to SAN]. Is this possible?

Read the reference articles for more info.

References:
- http://msdn.microsoft.com/en-us/library/cc949109.aspx
- “How to: Set Up FILESTREAM on a Failover Cluster” ( http://msdn.microsoft.com/en-us/library/cc645886.aspx).
- http://research.microsoft.com/apps/pubs/default.aspx?id=64525

HealthVault application architecture - Quick notes

Image via Wikipedia

HealthVault application architecture primary revolves around the following:
- Where is data stored?
- When is data accessed?
- How is user authenticated?

Where is data stored?
#1 Stored only in HealthVault
#2 Most of the data stored in HealthVault and remaining in application data store
#3 Data duplicated in HealthVault and application data store

Among above, #1 and #2 doesn't require synchronization between HealthVault and Application data store. #3 does require synchronization between them, which makes the system either simple or complex.

When is data accessed?
- The application requires permission from user everytime accessing the data from HealthVault
- The application requires permission only once from user and can access HealthVault data anytime from then without requiring permission from the user

How is user authenticated?
- Require the user to authenticate every time the application is accessing HealthVault data
- Require authentication once and store the relevant information for authentication-free subsequent access

Conclusion:
Requiring permission from user everytime to access their health record would be an impediment to doctors or any health care staff for adopting EMRs. Also, this wouldn't work in times of medical emergency. On the other hand, given a free hand to EMR would make the system more user friendly by performing underlying authentication in a transparent manner. But, users should be cautious about such access requests.

For those interested in processes, CodePlex has a HealthVault solutions development framework here.

About HealthVault

With healthcare stimulus in spotlight, healthcare industry has seen a buzz of activities with players ranging from software giants[Microsoft, Google, IBM etc], already established healthcare software companies[GE, Cerner, Eclipsys etc] to surging small players [eClinicalWorks etc] entering the fray for electronic health records and the ecosystem such as HIE, Electronic Records Bank etc.

With the brief intro above left behind, we will find more about HealthVault and it's impact on the healthIT ecosystem. Primarily, HealthVault can be considered as a health records bank where personal health records(PHR) are stored online and can be shared with others.

Usage of HealthVault depends on your need and purpose to access the health data on the HealthVault. It can be:
- [You are a patient] I am here to store my PHR online and i want to share it with my doctors and family members.
- [You are a doctor] I am here to view the health data of my patient and update their health record.
- [You are a software engineer] I am here to write a healthIT software(EMR, HIE) that interfaces with HealthVault data either to read or update or do both.
- [You are a healthIT manager or executive] I am here to understand how i can employ/benefit using HealthVault in my hospital/state healthIT systems.

If you are a patient or a doctor, you should read about PHR and EMR to know the benefits and visit HealthVault site for signup and usage. Primarily HealthVault allows entering health information as text, uploading past health data in CCR/CCD formats to name a few.

If you are a software engineer or healthIT manager or executive, then you are in the right place to know more about HealthVault. Check my labels cloud for more posts on HealthVault.

GhostDoc - Comment generator for VS.NET

Silverlight Spy -

Service endpoint binding not using the HTTP protocol

If you are invoking a WCF service and if you are getting the error

"This could be due to the service endpoint binding not using the HTTP protocol. This could also be due to an HTTP request context being aborted by the server (possibly due to the service shutting down). See server logs for more details."

Then, most probably you have a problem with enums exposed via WCF. [Even otherwise, this post will be useful to you].

Either case, SvcTraceViewer is your life saver. It helps trace the internal method invocations, plumbing etc in the WCF calls.

Steps to run traceviewer:
1) Configure the WCF to enable trace
2) Run the SvcTraceViewer
3) Open the trace log per the settings in your WCF config file
4) Look for errors. Mostly highlighted in red color.
Info for the above steps available here in the microsoft site.
5) You are not done yet. If it's caused due to enums(as in my case), you must read this blog post that explains the deeper issue underneath using enums in WCF.

Checking NULL in LINQ and SQL

I stepped on a bit field in my table which has NULL as default value.

Checking in my LINQ code for not true condition as below just failed:

p.IsValidLicense != true

The row in the table had the value NULL and therefore i expected my query to return true, but in vain.

Then i executed it as SQL query in SSMS as below. Again unexpected result.

Where IsValidLicense <> 1

Time for google. Seems, the NULL issue has caused lot of miseries in the lives of a lot of people. Everyone just jumped in to answer my question.

The correct way to construct the query..

In SQL:

(IsValidLicense IS NULL OR IsValidLicense = 0)

In LINQ:

(p.IsValidLicense == null || p.IsValidLicense == false)

Hope this helps!