Gunnar Peipman's ASP.NET blog

ASP.NET, C#, SharePoint, SQL Server and general software development topics.

Sponsors

News

 
 
 
DZone MVB

Links

Social

January 2012 - Posts

Windows Home Server needs new licensing scheme

Fast development of technology and quickly spreading fast internet with new devices and technology open new ways for us to connect our homes with other family members. Windows Home Server is product that needs heavy refresh and one thing that Windows Home Server team must take seriously is changing current very limited licensing scheme. Here is the overview of situation and what needs to be done to get better licensing that fits for families better today.

Family is connected now

One main point that is changed over time: our houses are connected better than before.

By example, my brother has some computers at home, my cousin has some computers at home, my girlfriend parents have some computers at home etc. Homes have good connections now and more people are able to stay connected with my home. People I’m talking about are also called my family.

For some people in family all this computer stuff is complex and they need something simple to share their media with others and to see what others have put online. This is perfect scenario for Windows Home Server.

Why should we all have separated accounts to keep and share same media we all like? Why should we exchange files over chat clients? Why we should use public services that may come and go? We usually don’t share many aspects of our lives with public space. This is where we want one central for family – something that is easy for everyone to use.

Better and cheaper hardware

The other thing is hardware – it is getting more and more powerful. I have 2TB of mirrored disk space available in my home server. It’s huge space for me and I can extend it. If I discard mirroring then my home server may have up to 8TB of free space. Believe me, it’s huge task for me and my family to get this 2TB of space filled with something valuable.

I am sure that during next couple of years way more storage will be available to buy for same price. Also other hardware is getting cheaper and cheaper. And also web connections getting better and better. It means that we have more and more devices in our homes that we want to connect.

Big picture

This is where we are standing now:

  • we have good internet connections at our homes,
  • we are able to but powerful hardware that we are not able to fully use with only small subset of our family,
  • kids and their parents know way more about computers than before,
  • we have different devices we use to browse the web and communicate with each other,
  • still family IT-guy is usually almost the only one who manages devices of more than one home,
  • families produce way more multimedia than before and members of family want to share their media between each other,
  • it is also good to take control over backups and maintenance of all family computers so also other homes that form my family have their computer safe and updated.

As you can see, situation is changed.

New licensing model

New licensing model for Windows Home Server should be package based like it is for Windows Server. Base package should be 10 licenses as it is right now. But there must be option to buy more licenses. Five license packs should be fine. It it is possible to buy one license at time it is also okay. It may even be better because then we can but exactly the number of licenses we need.

Creating dynamic pivot reports on SQL Server

In one of my current projects I have some pivot reports that show count of issues in different states. Grouping depends on report. States are dynamic – admins can define new states and save them to states table. This means that I cannot hardcode names of states to queries. Instead I need dynamic pivots. In this posting I will show you how to write reporting procedures that offer dynamic pivoting capabilities.

The trick here is to create dynamic list of all possible states and use this list in dynamically generated pivot query. Here is fragment of my database, names of tables should be self-describing.

MSSQL pivot reports: Tables

Here’s the query:


CREATE PROCEDURE XSP_REPORT_REQUESTS_BY_CATEGORY
AS
BEGIN

DECLARE @PivotColumnHeaders VARCHAR(MAX)
 SELECT @PivotColumnHeaders =
    COALESCE(
  @PivotColumnHeaders + ',[' + cast(STATUS_NAME as varchar) + ']',
  '[' + cast(STATUS_NAME as varchar)+ ']'
   )
 FROM DOC_STATUSES

DECLARE @PivotTableSQL NVARCHAR(MAX)
 SET @PivotTableSQL = N'
SELECT
*
FROM
(SELECT
    dc.CATEGORY_NAME AS [Category],
    ds.STATUS_NAME As name,
    req.ID as ID
FROM
    REQUESTS_FOR_PROPOSAL req
    INNER JOIN DOC_STATUSES ds ON
        req.STATUS_ID = ds.ID
   
INNER JOIN DOC_CATEGORIES dc ON 
       
req.CATEGORY_ID = dc.ID
) AS Source
PIVOT(
    COUNT(ID)
    FOR name IN ('+@PivotColumnHeaders+')
) AS PIVOTTBL'

EXECUTE(@PivotTableSQL)

END

Output of this reporting procedure is table like this:

MSSQL pivot report: Results

I don’t like this dynamic SQL in stored procedure because it can be hard to debug. But this far this solution works fine for me and I can always use views to get more complex querying logic out from reporting procedures.

More Posts