Using SQL Server Aliases

Recently we set up a SharePoint 2010 development environment at work.  I was tasked with adding the SSRS 2008 R2 server to the farm and setting it up to run in integrated mode.

I followed the instruction in MSDN, firstly installing SharePoint then tried to join it to the farm.  However, it kept giving an error when I hit apply on the config database.  I digged around, and found in SharePoint it not using the actual server name of the config database.  Instead, it is using a alias to specify the SQL server host name.  It worked without problems after I create the same alias on the SSRS box and using it as the config database server.

You can create these aliases from SQL Server Configuration Manager.  Or for machines that don't the SSCM installed, you can use "cliconfg.exe", or SQL Server Client Network Utility, instead (see screenshot below).  This utility seems to be shipped with pretty much all current versions of Windows from XP/2003 onwards.

In the past I have been advocating the use of DNS entries instead of actual server names where the server could change.  This has the advantage that if you need to move the database (or whatever function the machine serves) to another server, it's a simple matter of updating the DNS entry.  In addition, this also allows you to provide a nice, easy to remember name if users will be accessing it directly.

Using SQL Server Aliases provides a similar facility that allows you to change the actual server with minimal change.  The difference is that you may need to update the alias in multiple places.  However, it is still a good option if, for some reason, it is not possible or not easy to make changes to the DNS.

Why CurrentMember Can Be Used Only On Hierarchies

I used to wonder why the MDX function CurrentMember applies only to hierarchies and not to levels.  For example, the Members function can be used on either a hierarchy or a level, and returns the appropriate set of members.  I thought it would make sense if CurrentMember worked in a similar way.

Today while reading Chapter 10 - MDX Concepts of the book Microsoft SQL Server 2008 Analysis Services Unleashed, it suddenly hit me.  I realised why CurrentMember should only be applied to hierarchies and not to levels.

The book was explaining the concept of Execution Context and how SSAS works out the current coordinates for a query.  The SSAS engine starts by setting the current coordinates to the default member of each attribute hierarchy.  In most cases this would be the All member.  Then it takes the members that have been specified in the query and overwrites the appropriate attributes.  However, the coordinates for those attributes not specified in the query will remain at their default member.  Therefore in most cases, these attributes' CurrentMember is still the All member.


So that's why the CurrentMember function can be applied only to hierarchies and not to a specific level: because it needs to be able to return any member from the whole hierarchy, including the All member.  Applying the function to a specific level could return invalid results.


Rethinking about this, it all seems be a bit obvious now, but I guess it never clicked for me before.  So I am happy to share it in case you have wondered about the same thing.