The HipChat Stress

Email stress has been widely acknowledged for some years. Email is a major source of pressure in workplace as people feel the obligation to respond quickly. While we are still searching for solutions to cope with email stress, unfortunately, a newer and even worse source of pressure has emerged: the HipChat stress.

Here is a real story. I was having an in-person conversation with a lady lately on a weekday afternoon. She works for another software company where HipChat is used as much as emails, if not more. Our conversation was about an important matter, so I put aside all my electronic gadgets to focus on the conversation, as well as to pay respect. The lady had her laptop opened next to her while we were talking, as she said she wanted to stay online. Our conversation was interrupted for several times, because she noticed that someone was "at-ing" her on HipChat. Since it wasn't an emergency (e.g. live site incident), I asked her why she felt the obligation to respond right away. She said that's because it was on a group channel.

Later that day, I couldn't stop wondering what's the difference between (a) a group channel on HipChat or Slack, vs. (b) an email thread which has the whole team on it. It appeared to me that our brains seem to equate a group channel in HipChat to a real team meeting which has everybody in the same room (btw, such illusion is a testimony of HipChat and Slack for successfully bringing the team closer together.) In a real team meeting, of course we feel obligated to respond when our names are called. Hence we feel the same when being at-ed in a group channel in HipChat or Slack.

As the instant messaging services like HipChat and Slack are gaining popularity at an unprecedented pace, I guess that the HipChat stress that I observed on that lady will also soon become very common and probably dwarf the email stress. A hundred years after Henry Ford installed the first moving assembly line in the world, HipChat and Slack are becoming the new assembly line, for the software engineers.

The Self-Link Nonsense in Azure DocumentDB

Lately I have been playing with the Azure DocumentDB. It's really good: it's more truly a Database-as-a-Service than other hosted/managed NoSQL providers; it supports many good features that not everyone supports, like the distinction between replace vs. update; its price seems substantially lower than the others' (MongoLab, RavenHQ, MongoHQ).

However, I am not very happy with DocumentDB's client side programming experience. Overall, the implementation of the data access layer on top of DocumentDB is probably the most bloated among all the document store databases that I've used, including MongoDB, RavenDB and RethinkDB. In particular, there is one thing really annoying in Azure DocumentDB: the self-link. Due to the need of self-link in various places, the data access layer code for DocumentDB is fairly awkward.

What I meant by "bloated" was that to achieve the same thing (e.g. to implement the DeleteOrderByNumber method in the below example), I only need 1 line code on MongoDB but a lot more code on DocumentDB:

For MongoDB:


collection.DeleteOneAsync(o => o.OrderNumber == orderNumber).Wait();

For DocumentDB:



Order order = client.CreateDocumentQuery(collection.DocumentsLink)
.Where(x => x.OrderNumber == orderNumber)
.AsEnumerable().FirstOrDefault();
Document doc = client.CreateDocumentQuery(collection.DocumentsLink)
.Where(x => x.Id == order.Id).AsEnumerable().FirstOrDefault();
client.DeleteDocumentAsync(doc.SelfLink);


Let's go through the full example. I have an Order document, in which the Id is a GUID generated by the database during insert and the OrderNumber is a user-friendly string, such as "558-4094307-8688964".

    public class Order
    {
        public string Id; 
        public string OrderNumber; 
        public string ShippingAddress;
    }

The next thing I want to do is to implement the data access layer to add, get, update and delete an Order document in a document store database. In particular, I need a Get method and a Delete method which takes OrderNumber as parameter, because the client will also need to call the REST API using the order number. So basically I need to implement the below methods (I'm using C#):

    void AddOrder(Guid order);
    Order GetOrder(string id);
    Order GetOrderByNumber(string orderNumber);
    void UpdateOrder(Order order);
    void DeleteOrder(string id);
    void DeleteOrderByNumber(string orderNumber);

For each method, I compared the implementation on each database. The comparison is mainly focused on the amount of code and how straightforward and intuitive it is to code.

MongoDB RethinkDB RavenDB DocumentDB
AddOrder
GetOrder
GetOrderByNumber
UpdateOrder
DeleteOrder
DeleteOrderByNumber

Here is the code and why I gave these ratings:

1. AddOrder

/* MongoDB */
void AddOrder(Order order)
{
    collection.InsertOneAsync(order).Wait();
}

/* RethinkDB */
void AddOrder(Order order)
{
    order.Id = conn.Run(tblOrders.Insert(order)).GeneratedKeys[0];
}

/* RavenDB */
void AddOrder(Order order)
{
    using (IDocumentSession session = store.OpenSession())
    {
        session.Store(order);
        session.SaveChanges();
    }
}

/* DocumentDB */
void AddOrder(Order order)
{
    Document doc = client
        .CreateDocumentAsync(collection.SelfLink, order).Result;
    order.Id = doc.Id;
}

Not too bad. Extra credit to MongoDB and RavenDB: their client lib can automatically back-fill the DB generated value of Id to my original document object. On DocumentDB and RethinkDB, I need to write my own code to do the back-fill. Note: I'm using the latest official client driver for MongoDB, RavenDB and DocumentDB. For RethinkDB, they don't have official .NET driver, so I am using a community-supported .NET driver for RethinkDB.

2. GetOrder

/* MongoDB */
Order GetOrder(string id)
{
    return collection.Find(o => o.Id == id).FirstOrDefaultAsync().Result;
}

/* RethinkDB */
Order GetOrder(string id)
{
    return conn.Run(tblOrders.Get(id));
}

/* RavenDB */
Order GetOrder(string id)
{
    using (IDocumentSession session = store.OpenSession())
    {
        return session.Load(id);
    }
}

/* DocumentDB */
Order GetOrder(string id)
{
    return client.CreateDocumentQuery(collection.DocumentsLink)
        .Where(x => x.Id == id).AsEnumerable().FirstOrDefault(); 
}

One up for RethinkDB and RavenDB. When I use Id to query, I shouldn't need to write a search condition like x => x.Id == id.

3. GetOrderByNumber

/* MongoDB */
Order GetOrderByNumber(string orderNumber)
{
    return collection.Find(o => o.OrderNumber == orderNumber)
        .FirstOrDefaultAsync().Result;
}

/* RethinkDB */
Order GetOrderByNumber(string orderNumber)
{
    return conn.Run(tblOrders.Filter(o => o.OrderNumber == orderNumber))
            .FirstOrDefault();
}

/* RavenDB */
Order GetOrderByNumber(string orderNumber)
{
    using (IDocumentSession session = store.OpenSession())
    {
        return session.Query()
            .Where(x => x.OrderNumber == orderNumber).FirstOrDefault();
    }
}

/* DocumentDB */
Order GetOrderByNumber(string orderNumber)
{
    return client.CreateDocumentQuery(collection.DocumentsLink)
        .Where(x => x.OrderNumber == orderNumber)
        .AsEnumerable().FirstOrDefault();
}

4. UpdateOrder

/* MongoDB */
void UpdateOrder(Order order)
{
    collection.ReplaceOneAsync(o => o.Id == order.Id, order).Wait();
}

/* RethinkDB */
void UpdateOrder(Order order)
{
    conn.Run(tblOrders.Get(order.Id.ToString()).Replace(order));
}

/* RavenDB */
void UpdateOrder(Order order)
{
    using (IDocumentSession session = store.OpenSession())
    {
        session.Store(order);
        session.SaveChanges();
    }
}

/* DocumentDB */
void UpdateOrder(Order order)
{
    Document doc = client.CreateDocumentQuery(collection.DocumentsLink)
        .Where(x => x.Id == order.Id).AsEnumerable().FirstOrDefault();
    client.ReplaceDocumentAsync(doc.SelfLink, order).Wait();
}

DocumentDB needs an extra step! I have to do a separate query by Id first, to get back a Document object, then use the SelfLink value on the Document object to call ReplaceDocumentAsync. I don't understand why the syntax has to be like that.

5. DeleteOrder

/* MongoDB */
void DeleteOrder(string id)
{
    collection.DeleteOneAsync(o => o.Id == id).Wait();
}

/* RethinkDB */
void DeleteOrder(string id)
{
    conn.Run(tblOrders.Get(id).Delete());
}

/* RavenDB */
void DeleteOrder(string id)
{
    using (IDocumentSession session = store.OpenSession())
    {
        session.Delete(id);
        session.SaveChanges();
    }
}

/* DocumentDB */
void DeleteOrder(string id)
{
    Document doc = client.CreateDocumentQuery(collection.DocumentsLink)
        .Where(x => x.Id == id).AsEnumerable().FirstOrDefault();
    client.DeleteDocumentAsync(doc.SelfLink);
}

Same as in GetOrder, extra point for RethinkDB and RavenDB for not needing a search condition x => x.Id == id when Id is used.

6. DeleteOrderByNumber

/* MongoDB */
void DeleteOrderByNumber(string orderNumber)
{
    collection.DeleteOneAsync(o => o.OrderNumber == orderNumber).Wait();
}

/* RethinkDB */
void DeleteOrderByNumber(string orderNumber)
{
    conn.Run(tblOrders.Filter(o => o.OrderNumber == orderNumber).Delete());
}

/* RavenDB */
void DeleteOrderByNumber(string orderNumber)
{
    using (IDocumentSession session = store.OpenSession())
    {
        var order = session.Query()
            .Where(x => x.OrderNumber == orderNumber).FirstOrDefault();
        session.Delete(order);
        session.SaveChanges();
    }
}

/* DocumentDB */
void DeleteOrderByNumber(string orderNumber)
{
    Order order = client.CreateDocumentQuery(collection.DocumentsLink)
        .Where(x => x.OrderNumber == orderNumber)
        .AsEnumerable().FirstOrDefault();
    Document doc = client.CreateDocumentQuery(collection.DocumentsLink)
        .Where(x => x.Id == order.Id).AsEnumerable().FirstOrDefault();
    client.DeleteDocumentAsync(doc.SelfLink);
}

MongoDB and RethinkDB are the best for DeleteOrderByNumber. They both only need 1 call. RavenDB needs 2 calls: it first needs to query by OrderNumber, then do the Delete (which presumably will use the Id). DocumentDB is the worst as I need to do 3 calls! Before I can call DeleteDocumentAsync, I first need to do a query by OrderNumber to get the Id, then use Id to query again to get the self-link of this Order document! DocumentDB's client driver seems to only have one method for delete: DeleteDocumentAsync, which only takes a SelfLink string.

I don't understand why there isn't an overload of DeleteDocumentAsync which can take Id. It doesn't seem to be just me. There are 300 votes on feedback.azure.com asking for the support of deleting a document by id.

Summary

Overall, the data access layer implementation on DocumentDB is a bit inferior experience than on the other three. I hope the DocumentDB team can improve it in the near future.



Foot Note 1:

I was advised that if my Order object is extended from the Microsoft.Azure.Documents.Resource type, it will already have the SelfLink property on it and I will not need the extra step in UpdateOrder and DeleteDocumentAsync.

It works but not acceptable to me. Having the Order object extended from Resource will pollute my domain model. Usually we want our domain model objects to be free of dependencies, so that it works the best for the interoperability across different layers and stacks.

Although strictly speaking, the Order object isn't in 100% purity on MongoDB. I needed to put a [BsonId] attribute on the Id property. But an attribute is much better than additional member fields introduced by extending from a type in a specific DB's client driver. For example, one of the major difference is that: in JSON serialization, attributes won't show up but member fields will.

Foot Note 2:

The Order object was defined slightly differently on each DB. For completeness, here is the exact definitions:

    /* MongoDB */
    [Serializable]
    public class Order
    {
        [BsonId(IdGenerator = typeof(StringObjectIdGenerator))]
        public string Id;
        public string OrderNumber;
        public string ShippingAddress;
    }

    /* RethinkDB */
    [DataContract]
    public class Order
    {
        [DataMember(Name = "id", EmitDefaultValue = false)]
        public string Id;
        [DataMember]
        public string OrderNumber;
        [DataMember]
        public string ShippingAddress;
    }

    /* RavenDB */
    public class Order
    {
        public string Id = string.Empty;
        public string OrderNumber;
        public string ShippingAddress;
    }

    /* DocumentDB */
    public class Order
    {
        [JsonProperty(PropertyName = "id")]
        public string Id;
        public string OrderNumber;
        public string ShippingAddress;
    }

WhatDidIDo.com

In 2008, I wrote a small tool WhatDidIDo for myself, because I wanted to know where I spent my time. In those days my stress level was a bit high, spending a lot of hours in front of my PC while I still felt that I didn't have enough time to get all the work done.

The WhatDidIDo program was simple. It was written in Visual C++. It ran in the background, using SetWindowsHookEx() to capture the events which a window was activated and GetWindowThreadProcessId() and GetModuleBaseName() to find out which application does the activated window belong to. Then the program just wrote the data into a comma deliminated CSV file.

The tool worked well and later used by a few other team members too, as they also wanted to understand where they wasted spent their time. Here is the data from one of them, who was among the best developers we had at that moment:

2015-04-whatdidido

A few days ago I saw someone recommending the RescueTime app. It instantly reminded me about WhatDidIDo. Isn't it the same idea? How come I didn't even think of turning WhatDidIDo into a startup company?

On the other hand, of course, turning WhatDidIDo into a startup company wouldn't guarantee success. Actually there are quite a few similar apps out there, like ManicTime, which are not as hot as RescueTime. There would be a long way to go for WhatDidIDo.com to become today's RescueTime.

When LINQ-to-SQL Meets Partitioned View‏

When SQL Server (including SQL Azure) is the database for my projects, I like to use LINQ-to-SQL. Its attribute-based mapping is pretty neat. I also like to use Partitioned View, which makes it easy and fast to purge old data: just drop tables rather than run DELETE commands.

Recently, in a new project when I used LINQ-to-SQL and Partitioned View together, I ran into such an error:

System.Data.SqlClient.SqlException: The OUTPUT clause cannot be specified because the target view "FooBar" is a partitioned view.


I wasn't able to find a good answer in Bing/Google/StackOverflow. It seemed to me that I might have to look into the source code of System.Data.Linq to find out what was the exact SQL command that LINQ-to-SQL generated and why there was an OUTPUT in there. When I was about to start this laborious source code reading journey, I happened to look at my entity class again and suddenly realized “wait a second, could the problem be the IsDbGenerated = true and AutoSync = AutoSync.OnInsert flags?”:

[Column(Name = "guid_row_id", CanBeNull = false, 
        IsPrimaryKey = true, IsDbGenerated = true, 
        AutoSync = AutoSync.OnInsert)]
public Guid RowId;

"Yeah, that would make sense", I thought, because if I were to write LINQ-to-SQL by myself, I would probably too have used the OUTPUT clause to implement the IsDbGenerated and AutoSync flags. So I removed IsDbGenerated and AutoSync:
[Column(Name = "guid_row_id", CanBeNull = false, 
        IsPrimaryKey = true)]
public Guid RowId;

Voilà, the error was gone!

Eventually it turns out that the culprit was IsDbGenerated. So as a workaround, I changed my code to generate new row IDs with Guid.NewGuid() in the application code. It's fine for my project since it's just a Guid. I guess this issue, that IsDbGenerated in LINQ-to-SQL doesn't work with partitioned views, would be more troublesome if someone wants to use other DB generated value like GetUTCDate(), which could be quite useful to avoid the time drift issue on the client side, or auto increment integer.

"Whatever It Takes"

My eyebrows are knitted every time when I hear a manager telling the team "we will do whatever it takes" (e.g. to meet the project deadline).

When someone says "whatever it takes", it's likely s/he has little clue of what exactly it takes, although for most of the time it is a figurative phrase. The U.S. President may say "we will do whatever it takes to win the war against the terrorism". That's OK because indeed no one knew what would happen in the war. The President merely uses that phrase to express his conviction. But when it comes to meeting an approaching project deadline, there is no need for the manager to express his conviction to the team, since that's not the best way to mobilize the team, and there should be no unknown in what it exactly takes: the problem is simply that there are more remaining work than the time left. Assuming the time left won't change and the team is not becoming more productive over night, what it takes to meet the deadline is to 1) decide how much work needs to be cut and what work to cut, then 2) cut them and manage with the consequences. It's not going to be easy, for sure. But that's the manager's job.

When the manager says "we will do whatever it takes", it passes a negative micro message to the team. It reminds me about those stressed homes. Those home owners vowed "I will do whatever it takes to keep my home". We all know what happened to them: most of them eventually still lost their home. The need for a manager to say "do whatever it takes" indicates that things are already in a very bad situation. "We will do whatever it takes" gives the team a dismal picture, rather than seeing the hope.

When a manager says "we will do whatever it takes", s/he has lost the cool. The team can figure that the manager is in panic mode. S/he is in desperate. How can a country keep calm and carry on when her prime minister has lost the cool? How can a team keep calm and carry on when the manager has lost the cool? If a team has lost the calm, how can they remain productive and effective? If a team is unable to remain productive and effective, how can it save an already-late project?

When a manager says "we will do whatever it takes", s/he is willing to sacrifice the team's long term future. It's like a home owner who is withdrawing from the 401K account to keep up with monthly mortgage. Most financial advisors will advise against such way. They will suggest filing bankruptcy and keep 401K intact. A manager who says "do whatever it takes" is willing to quench a thirst with poison. S/he will have no hesitation to burn the team out if that may increase the chance to get the project done in time. That's scary.

My advice? If you are a manager, never say "we will do whatever it takes". If you are in a team where such "we will do whatever it takes" moment has happened more than once, you may want to think about whether that's a good place to stay, because the team seems unable to learn from it's own mistakes.

Choosing Between Loggly, Logentries and Elasticsearch

Lately I have been looking for a log management service for my team’s new project which is an engineering tool running as a website + REST API in Azure Websites, interacting with other engineering systems of my group and backed by SQL Azure and MongoDB. The need is basic: have one single place to store all the logs, traces and events from the different pieces in the application, so that my team and I can search the log and use it for troubleshooting. Down the road, we may also set up some simple alerts on top of the logs. For some reasons, I chose to not use the internal systems, but to try something outside.

I tried and compared Loggly, Logentries and Elasticsearch and eventually picked Elasticsearch:

Loggly Logentries Elasticsearch
Hosting Hosted Hosted Self-Hosted
Setup Easy Easy OK
Web UI Good OK Good
.NET Support Good Good OK
Official Documentation Good OK Good
Community & Ecosystem OK OK Good
Cost OK OK OK

Hosting

Both Loggly and Logentries are hosted. They are SaaS. Elasticsearch is an open source software. You have to host it in your own machines. In my case, I put Elasticsearch + Kibana on a Linux VM in Azure. On the other hand, nearly all the popular open source software has hosting providers. Just like there is MongoHQ for MongoDB, RavenHQ for RavenDB and GrapheneDB for Neo4j, there are also hosting providers for Elasticsearch, such as qbox.io and compose.io (formerly MongoHQ). I didn’t try them but it seems qbox.io is pretty decent and the price is reasonable (basically the underlying hosting cost in various public clouds, plus a premium).

Setup

Since Loggly and Logentries are hosted, the setup is really simple: just create an account, fill a form and you are good to go. Setting up Elasticsearch and Kibana for the first time on my own Linux VM took me about 30 minutes to carefully follow this 3rd party instruction step by step. Later, when I did the setup over again, the time was halved. Btw, that instruction is really good quality.

Web UI

Loggly and Elasticsearch (Kibana) tied. Loggly's UI is more like iPhone: it just works. It’s quite polished and easy to use for people who don't want to spend a lot of time on learning the tool itself (rather than using the tool to conduct business). Elasticsearch/Kibana is like Android: it’s very powerful and you can get a lot out of it if you know how to configure it and tweak your application’s logging. The analogy is not surprising: both Android and Elasticsearch/Kibana are open source software, while iPhone and Loggly are closed source.

Logentries’ UI is less satisfactory. It was quite clear to me after a very brief use for 10 minutes or so. The design is relatively less fine-tuned. There seems to be some glitch in the client side scripts, so that sometime some UI elements were not very responsive or behaving in the expected way. In particular, there are three downers in Logentries’ UI:

  1. The row doesn’t expand inline. Both Loggly and Kibana can, which is sometimes pretty convenient.
  2. The results don’t support sorting. It’s always sorted by the event time ascendingly. It’s quite painful that every time that I have to press Page Down or drag the mouse many times to get to the latest rows. In the opposite, both Kibana and Loggly support sorting by time in either ascending or descending way and by default they both show the latest rows on top.
  3. The “X DAYS left in trial” reminder keeps popping up in Logentries UI. It’s intrusive and annoying. For a startup like them, they should understand that the greater conversion rate should organically come from building greater product.

.NET Support

Loggly and Logentries tied. They both provide official log4net appenders, which are also available as NuGet packages. Their official websites both provide clear app/web.config code examples of how to configure their appenders. Their appenders both work in asynchronous mode, so they can be directly used without noticeable performance overhead. A simple test shows that when their appenders are enabled, continuously calling logger.Info() for 100 times takes less than 100ms, which means <1ms per call.

Elasticsearch doesn’t provide official log4net appender, nor appender for Logstash. That’s a bit disappointing. There are a couple choices on GitHub though, among which log4net.ElasticSearch is the most well-developed one. In my project, I used log4stash, which was forked from log4net.ElasticSearch. But I had to do some work to log4stash before I can use it in my project, because log4stash doesn’t support SSL and my Elasticsearch is exposed on Internet so that my application running in Azure Websites can write logs into it (note: it seems Azure Websites recently started to support Virtual Network, which may eliminate the need to expose my Elasticsearch on Internet). It wasn’t too hard to add SSL support to log4stash, though. I did it in my fork, it worked well in my project and I created a pull request (which hasn’t been accepted yet). Anyone who needs a log4net appender for Elasticsearch with SSL support can grab it from my repo.

Official Documentation

Both Loggly and Elasticsearch’s official documentations are pretty good. No confusion.

Logentries has some room to improve. Take .NET support for example. There is a section on their official website and there is also a documentation on GitHub. The doc on their official website is using the older settings name (LOGENTRIES_TOKEN and LOGENTRIES_ACCOUNT_KEY), while the doc on GitHub uses newer setting names (Logentries.Token and Logentries.AccountKey).

Community & Ecosystem

Elasticsearch has clear winner, although the three were born nearly the same time: Elasticsearch since 2010 (although its root, Lucene, has been around for 16 years); Loggly since 2009; Logentries since 2010. Searching them in StackOverflow and you will get:

Search them in GitHub:

It’s not surprising why Elasticsearch has a much bigger/active community: Elasticsearch is an open source software and self-hosted, while Loggly and Logentries are SaaS and closed source.

A plus for Logentries is that Logentries seems to provide better out-of-box integration with other services like Slack, HipChat, PagerDuty, etc. Loggly seems to have out-of-box integration with PagerDuty, but not HipChat or Slack. My quick search didn’t find any out-of-box integration of Elasticsearch with Slack, HipChat, etc., though I’m sure there are something ready for use in the community.

Cost

None of the three options is free, although Loggly and Logentries both offer a 30-day free trial period. After that, their entry level’s prices are:

Purely from cost saving perspective, if I were doing a side project, I would probably go for Logentries. In my current project, since Microsoft employees can use Azure for free (note: the charge goes to our department), a Linux VM running Elasticsearch+Kibana is for free to me.

Other Options

As mentioned this recent article, "Picking a cloud log management service", there are a couple other choices for a SaaS log management service providers, such as: Splunk, Sumo Logic and Papertrail. I agree with that article that Splunk seems overkill for small projects and Sumo Logic doesn't seem to fit. Papertrail looks a lot like Loggly and Logentries. I will give it a try when I get chance, though I don’t expect Papertrail to show too much difference than Loggly and Logentries.

Last but not least, none of the three big public cloud providers provide a comprehensive SaaS log management service as Loggly and Logentries do.

  • Amazon: AWS has the Amazon CloudWatch. But from what I read and confirmed by the Picking a cloud log management service article (written in Jan 2015), Amazon CloudWatch is only for EC2 instances.
  • Google: The recently announced Google Cloud Logging looks like a SaaS log management service, but relatively primitive, compared to Loggly, Logentries and Elasticsearch/Kibana. Plus, it seems to only support sending log from application in Google App Engine and VMs in Google Compute Engine.
  • Microsoft: Azure doesn’t seem to offer a log management service. Although a couple weeks ago as a part of the announcement of the new Azure App Service (which is kind of the v2 of Azure Websites), it provides the log collection, viewing and streaming.

It seems to be a common theme that Amazon, Google and Microsoft’s log management capability in their public cloud offering is only for the applications and VMs running in their own public cloud. That kind of lack of openness is a bit disappointing.

Finding the Compatible SQLCMD.exe

I wrote this post because I hope it can save other people some time. When I ran into this issue this week, I searched in Bing/StackOverflow/etc. and couldn't find a direct answer for it. So I spent some time to do my own troubleshooting, try different solutions and have figured out a workable one. This post captures the issue and my solution, so that hopefully in the future when other people run into the same issue, they will find this post by searching in Bing/Google.


The Issue

In my unit test's TestInitialize code, it runs such a sqlcmd.exe command:

sqlcmd.exe -S (LocalDB)\UnitTest -E -d Jobs_DBTNXXVKQ3K6 -i "..\src\SQL Database\Jobs\Tables\Jobs.sql"

It works fine on my laptop, but it fails and returns below error when running in the build in Visual Studio Online:

HResult 0xFFFFFFFF, Level 16, State 1
SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].
Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..
Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : Login timeout expired.

That's because this sqlcmd.exe was v10.0 (SQL Server 2008) and it's incompatible with LocalDB, which was introduced in SQL Server 2012 (v11.0). Only the sqlcmd from SQL Server 2012 or later works with LocalDB.

The Solution

The solution is to find a later version of sqlcmd.exe on the build host of Visual Studio Online, and pinpoint to it in my TestInitialize code. This and this page listed what's installed on the build host, but for obvious reasons, in my TestInitialize I must do a search instead of using a hard-coded path.

A little surprise was that to do the file search in the build host in Visual Studio Online, I couldn't use the DirectoryInfo.GetFiles() method with the SearchOption.AllDirectories parameter. That would throw exception when it gets denied access to some folders and there doesn't seem to be a way to let DirectoryInfo.GetFiles() just ignore any directory that it get access denied.

So I ended up writing a traversal by myself rather than using DirectoryInfo.GetFiles(). The traversal is kind of time consuming: it takes about 15-30 seconds on my laptop (probably because I've installed too many stuffs under the program files folder). So I added a shortcut: first try to look for it at a few known possible places; if found, then the time-consuming traversal can be saved.

Here is the full code for finding the compatible SQLCMD.exe: link.

I hope this will be helpful.

小胖子说 - 10

3/14/2015

小胖子看电视。

爸爸:我们设个timer吧
小胖纸:好的
爸爸:那设多少minutes呢?(之前小胖纸一直只会说six minutes的,所以我就让他说要看多久)
小胖纸:看thirty minutes吧
爸爸:好吧……爸爸言出必行……

小胖子说 - 9

3/7/2014

妈妈:明天什么计划
爸爸:去动物园
小胖纸:对!动物园很久没去了!
爸爸:还有飞火车(Monorail)
小胖纸:对,飞火车也很久没去过了
爸爸:那还有哪里很久没去过了呢
小胖纸:嗯… 英国!
爸爸:……… [擦汗][擦汗][擦汗]

小胖子说 - 8

3/4/2014

小胖纸坐车坐吐了。妈妈一边擦,小胖纸一边指着吐出来的东西说:“这是cheese,这是soup,这是soup里面的菠菜”。 [晕][晕][晕]

小胖子说 - 7

2/20/2015

早上在幼儿园门口…

我:下午妈妈来接你哦
小胖纸:哦,不要太早
我:为什么呢
小胖纸:太早就不能玩(翻译:在幼儿园跟其他小朋友玩)

小胖子说 - 6

1/31/2015

小胖纸现在习惯性跟我唱反调,我说要他就说不要,我说不要他就说要。今天我跟他说,

我:没关系,take your time, no rush
小胖纸:要rush!

(过了一会儿…)
小胖纸:爸爸,rush是什么东西?

小胖子说 - 5

1/30/2015

(明天小胖纸的幼儿园同学Derek要来家里玩)

我:明天你和Derek要吃什么?
小胖纸:Derek要吃hotdog,嘉嘉也要吃hotdog。
我:那嘉嘉的爸爸和妈妈吃什么呢?
小胖纸:嘉嘉爸爸、嘉嘉妈妈、Derek爸爸、Derek妈妈,都吃hotdog
我:那么Emily呢?(Emily是Derek的妹妹)
小胖纸(不假思索的):她不饿!

小胖子说 - 3

12/6/2014

我:为什么你喜欢吃薯条和薯片,但不喜欢吃这个土豆呢?可不可以谈谈你的想法?
小胖子:不好
我:什么叫“不好”?是你有想法但不想谈,还是没有想法
小胖子:没想法