There are two flavors of HDInsight: Windows Azure HDInsight Service and Microsoft HDInsight Server for Windows (recently quietly killed but lives on in a different form).  Both were developed in partnership with Hadoop software developer and distributor Hortonworks and were made generally available in October, 2013.

Windows Azure HDInsight Service (try) is a service that deploys and provisions Apache Hadoop clusters in the Azure cloud, providing a software framework designed to manage, analyze and report on big data.  It makes the HDFS/MapReduce software framework and related projects such as Pig, Sqoop and Hive available in a simpler, more scalable, and cost-efficient environment.  It uses Windows Azure Blob storage as the default file system (or you can store it in the native Hadoop Distributed File System (HDFS) file system that is local to the compute nodes).

Microsoft HDInsight Server for Windows was killed shortly after it was released but lives on in two flavors: Hortonworks Data Platform (HDP) (try) and Microsoft’s Parallel Data Warehouse (PDW).  Both are on-premise solutions.  With HDP, it includes core Hadoop (meaning the HDFS and MapReduce), plus Pig for MapReduce programming, Hive data query infrastructure, Hortonworks’ recently introduced HCatalog table management service for access to Hadoop data, Scoop for data movement, and the Ambari monitoring and management console.  All of the above have been reengineered to run on Windows and all are open-source components that are compatible with Apache Hadoop and are being contributed back to the community.  With PDW, you can add an HDInsight region into the appliance, and this region includes HDP and can be accessed via Polybase.

The HDInsight Server is designed to work with (but does not include) Windows Server and Microsoft SQL Server.  In the case of Windows, HDInsight is integrated with Microsoft System Center for administrative control and Active Directory for access control and security.

More info:

Microsoft Releases Hadoop On Windows

Hadoop and HDInsight: Big Data in Windows Azure

Video HDInsight: Introduction to Hadoop on Windows

Video Introduction To Windows Azure HDInsight Service

Let there be Windows Azure HDInsight

Working With Data in Windows Azure HDInsight Service

HDInsight patterns & practices Windows Azure Guidance

Hortonworks Makes HDP 2.0 for Windows Server Generally Available

Windows Azure HDInsight supports preview clusters of Hadoop 2.2

Windows Azure HDInsight Supporting Hadoop 2.2 in Public Preview