søndag den 25. august 2013

At a customer's site for the next months

This week I will be starting a project with a new customer. It is expected to last the rest of 2013.

So my MCSA certification and other projects will have to be paused until next year. There might be time for blogging from time to time, but the client shall have my full attention, so we will have to see.

torsdag den 22. august 2013

Have been looking into TFS 2012 a bit

Having seen that experience with Microsoft Team Foundation Server (TFS) is appreciated in several potential freelance projects, I decided to take a look at TFS 2012.

I did that by installing TFS 2012 on a virtual server and connecting VS Ultimate 2012 to it. The basics about work items, source control and builds looked straigt forward enough, and in no time flat I had TFS automatically building on every check in.

To get a better overview of what TFS 2012 has to offer, I read this book:

It stays quite high level and describes what is possible, without getting into details about how to actually set up the procedures. Lots of screenshots though.

It is clear that the TFS ecosystem is indeed a very strong player in the Application Lifecycle Management (ALM) area. No question about Microsoft taking agile development practices seriously.

TFS and Visual Studio handles everything one would need with respect to issue handling, sprint monitoring and (manual and unit) testing, but also offers some quite advanced code visualization tools. I especially found it interesting the way one could describe (by drawing diagrams) the intended code organization and layering, and then have TFS/Visual Studio validate the code on check in against these intentions. How well this works is in practice, I am not too sure of. Anybody got any real experience with this?

It should be possible to create a very automatic and reliable build/test setup, by automatically deploying to virtual machines (in a "lab") as part of the build process. However, to enable this and get the most from TFS one also must install Sharepoint, SQL Server Reporting Services, Microsoft Test Manager, System Center Operations Manager and System Center Virtual Machine Manager - IMHO a pretty big setup for a small development organization.

fredag den 16. august 2013

PowerShell detour

The "MCSA: SQL Server 2012" certification has been temporarily paused, while I investigate some tools that could prove useful. 
I am looking into Team Foundation Server - especially the parts of Continuous Integration and Deployment. First step in that direction was learning Powershell scripting, which looks as a great way of doing automated deployments on Windows and now I just finished reading the book "Windows Powershell 3.0 First Steps":

Having experience with both .NET and scripting in general (mostly Bash/Unix-style scripting) learning the basics of Powershell was not hard. Of course, there are hundreds of commands (called cmdlets) that it will take time to get really comfortable with, but the fundamentals of Powershell seems easy enough.

One very nice thing about Powershell is the discoverability of commands and parameters. Tab-completion is very Visual Studio-like and is way more advanced than the traditional path-expansions in other shells. The help system also appears quite polished, but perhaps the part I like best, is the focus on (typed) objects.

The pipeline plays just as important a role as it does in Unix-scripting, but instead of piping text lines, it pipes typed objects. This allows very easy access to rich information about files, processes etc. without all the "awk"-text parsing. Easier, more robust and probably faster too.

Powershell also includes quite powerful remoting capabilities, perhaps finally bringing practical SSH'ish functionality to Windows. It appears pretty easy to use, but it requires one to have Active Directory configured right. It can also run on HTTPS.

So, a question begs to be asked: is a mono-based powershell for Linux a desireable thing? Does it exists?

mandag den 12. august 2013

Connecting to SQL Server from another domain

Have you ever had to connect to an SQL Server using SSMS or perhaps from an ASP.NET application, where the SQL Server is set up to only use Windows Authentication (not Mixed Authentication) from your client PC, web server or whatever, and that machine was not part of the same domain as the SQL Server?

Eg. if I want to connect from my laptop (OHTXPS13, not a member of any domain) to the server "SQL-B-462" (part of the Contoso domain) then it appears I am out of luck.

Notice that the "User name" and "Password" fields are disabled. Even though I have the username "Contoso\Kim_Akers" and password "Pa$$w0rd" there seems to be no way to provide it.

Trying SQLCMD.exe does not improve the situation. There are -U and -P switches to allow you to provide username and password, but those requires the SQL Server to allow mixed mode authentications.


The recommended solution I think is to establish a domain trust relationship between the SQL Server domain and the "client" domain. However, since this must involve a Domain Administrator it might not be a practical solution in all circumstances.

A very simple quick 'n' dirty solution is this:

That is, simple use the command (from powershell or plain cmd.exe):
runas /netonly /user:<domain>\<username> ssms.exe

Enter the password of the domain user and SQL Server Management Studio will start up running under the specified user account. Now simply connect to the desired SQL Server with Windows Authentication.

torsdag den 8. august 2013

Querying SQL Server 2012 is PASS

Just finished the 70-461 Exam "Querying Microsoft SQL Server 2012" and fortunately it was a (comfortable) PASS.

Much of the topics is something I have used extensively before, but these items are more or less new for me and I am happy to know a bit more now:
  • Pivoting and unpivoting data
  • Common table expressions (covered in this previous post)
  • Windows functions (eg. PARTITION, OVER, ROW_NUMBER())
  • Full-text data
  • XML queries
  • The MERGE statement
  • OUTPUT-clauses for INSERT, UPDATE and DELETE.
  • TRY-CATCH error handling
  • Extended events

So now I have begun on this book:
 The exercises in this book requires no less than six Windows 2008 R2 servers (one DC, 4 ordinary servers and 1 server core), so the first step will be to punish my ESXi by creating this setup.

mandag den 29. juli 2013

SQL Server: Common Table Expressions

One nice thing I stumbled upon reading the "Querying Microsoft SQL Server 2012" book is a feature called "Commen Table Expressions" or CTE's.

As it turns out the feature was already available in SQL Server 2005. If other people than me managed to be ignorant of its existence then let me try to explain what it is about.

The basic form of a CTE is this:
WITH <CTE_name> AS

This form allows us to assign a name (CTE_name in the above) to a query and then use that in the <outer_query>.

Of course, this is just different kind of view or subquery (derived table). It allows one to avoid creating permanent database objects (as with views). It it also arguably more readable than named subqueries and - more importantly - avoids the need to repeat the definition of a subquery as is sometimes necessary.

Consider this example:
select ...
from (<subquery>) T1
inner join (<subquery>) T2 on ...

When self-joining the <subquery> one must repeat the defintion of the <subquery>, which is of course redundant.

For any functional programmers out there, CTE's play the role of let-expressions.

Recursive CTE

Nice as it is, the above form of CTE is mostly syntactical sugar, but there is another form of CTE that does something entirely different and makes it easier and faster to deal with hierachies - something that has always been notoriously difficult in SQL.

Enter the recursive CTE:
WITH <CTE_name>
   SELECT col_1, ..., col_n
   FROM <table_expression_1>


   SELECT col_1, ..., col_n
   FROM <table_expression_2>
This is interesting because the <table_expression_2> is allowed to refer to CTE_name.What happens is that SQLServer keeps evauating the <table_expression_2> and append rows to the final CTE until <table_expression_2> evaluates to an empty set. References to name of the CTE (CTE_name in this example) in table_expression_2 referes to the previous iteration.


Consider a table with some sort of hieracy (eg. a folder structure):
Id Parent_id
1 null
2 1
3 1
4 3
Say we want to retrieve the node 4 and all the parent nodes all the way to the root. Using "ordinary"< SQL we can only retrieve a constant number of leves (using the appropriate number of self-joins), but retriving an arbitrary number of levels is a challenge.

An elegant solution using recursive CTE's is:
with CTE_nodes as
   select n.id, n.parent_id
   from nodes n
   where n.id=4

   union all

   --- this is the "magic part"
   from CTE_nodes n2              
   inner join nodes n on n.id=n2.parent_id
select id, parent_id from CTE_nodes   
The result is:
id parent_id

The obvious use case is retrieving data from hierarchical tables, but it seems very exciting to have recursive querying capabilities. This has been also been possible using user defined functions, but I have more confidence that the optimzer will be able to handle recursive CTE's more efficiently than recursive user defined functions. I would be happy to hear about real world experience with recursive CTE's.

Certification in progress

It is probably around time I got some certifications taken. In 2009 I took the "Sitecore .NET Developer" certification, but considering how little sitecore work I have done and plans to be doing I could use some more.

SQL Server is a technology I have been working with in depth previously and I should be able to complete the certification without taking too much time off.

At the same time I should get up to date with new enhancements in SQL Server 2012. I have used 2008 a bit but only done serious stuff in SQL Server 2005.

The certification I am aiming for is the "MCSA: SQL Server", so right now I am plowing through this book:

I will post blogs on some of the interesting pieces as I progresses.

lørdag den 27. juli 2013

Upgraded front page

Ok, this was long overdue. I am primarily a back end developer and making pretty layouts on my own is not among my core strengths.

Anyway, my company website tolsit.com was simply based on a standard theme which might be many things but for sure not very impressive.

Thus the front page at least has now been shined up a little bit. This allowed me to refresh a bit of CSS, Gimp and also scratch the surface of Drupal theming.

I am very much aware that the website still does not appear fancy or anything, so hopefully I can improve the rest of the site from time to time.

Keep in mind that I make no claims of being a cool front end developer, so please do not infer much about eg. my database skills from the appearance of the website.

mandag den 15. juli 2013

The Pragmatic Programmer

I actually bought this fine books years ago, but only just now have found time to read it.

Reading it was a pleasure and I can recommend most of the advices it provides. It seems very much in favor of agile techniques (eg. the topics "Tracer Bullets", "The Requirements Pit" and "Refactoring") even though it curiously it does not use the term. Agile methodologies like Scrum were available before 2000 when this book was written, but the "Agile menifesto" appeared in 2001, which might introduced the term?

Many of the tips in the book does not come as surprises at all, but tips like "Your Knowledge Portfolio", "The Power Of Plain Text" and "It's Just a View" are worth keeping in mind.

I am in two minds about the encouragment of code generators. They can be simple but also incredible powerful and it is not for everybody to wrestle with such beasts.

I definitely do not suggest that code generators should be avoided at all times but especially as an independent software developer and consultant I feel a great responsibility to enable the (employees of the) client to maintain and build on whatever I leave behind.

lørdag den 29. juni 2013


For running my business an online file server will be very handy. Somwhere to put files, keep repositories for subversion and git and run bugtracking.

Partially for reasons of cost efficiency, but mostly for flexibility and learning, I will go with a linux server. I have many years of experience with Fedora as I like to keep close to the "bleeding edge" - at least when non-critical servers are concerned.

However, this also means that Fedora is upgraded at a very fast pace, and things are changed so frequently, that upgrades are somewhat shaky, and - in my opinion - most servers be reinstalled perhaps every 2 years and preferably perhaps every 6-12 months.

A possible solution for avoiding spending too much time performing reinstalls is to use a configuration mangement automation tool. I have previously looked a little bit at both puppet and chef, but have for now settled on CFEngine - mostly because it seems to be quite light weight with respect to both dependences and runtime overhead.

Keeping a server configured with CFEngine requires most configuration to be written as promises.Those are small scripts that contains instructions about what software (eg. RPM-packages) to be installed, which services to run and - perhaps most importantly - which configuration files to be modified and how.

All the promises could then be kept in a repository for change tracking.

Especially while learning the CFEngine language configuring servers this way will take considerably more time than simply installing software and editing files by hand. But in addition to being able to install a new server (perhaps running a newer version of Fedora) in far less time, it forces me to document the configuration of the server. Even with my best intentions I will gladly admit, that I have not ever succeeded in keeping configuration documentation completely up-to-date.

So now I am spending some time getting to know CFEngine. Hopefully I will be able to blog about the results another time.

mandag den 24. juni 2013

So many opportunities, so little time

My previous projects have ended, and I am back from a great holiday in Thailand.

Now pondering what projects to begin. I am open for discussions, challenges, great ideas and of course (freelance) job offerings.

I do now doubt that I will find ways to spend the time. The big question is how to choose from all the infinite possibilities - making good choices is perhaps the first project to be undertaken...

I will try to post here on my progress and - perhaps more numerous - sidetracks.