How you SHOULD perform a manual fail-over of a SQL AlwaysOn Availability Group

Since the arrival of BizTalk Server 2016, Microsoft added support for SQL AlwaysOn. It still is new to many BizTalk operators, administrators and developers. Often having been dealing with older versions of BizTalk.
Not many people have been dealing with SQL AlwaysOn already, so this post is for non-BizTalk geeks as well…

There was always the need for high available SQL environments. Companies want their data and systems running in a way that downtime can be minimized and SQL Server has a lot of solutions to that problem, one of them being SQL AlwaysOn.

When having dealt with a SQL Failover Cluster¬†before, you might be tempted to use the Failover Cluster Manager to fail-over an availability group, however this is NOT a good choice when dealing with SQL AlwaysOn…

Continue reading

What you need to know about SQL identity columns

As every other developer working with Microsoft SQL Server on-premise, you probably already came in contact with SQL IDENTITY columns. They are commonly used and are pretty straightforward… or at least they seem that way.

As an architect I regularly use them in my designs, but there are a few quirks I learned along the way that some of you might not have encountered yet.

Disclaimer: I’m not a DBA nor is this article intended for DBA’s, this is mainly directed towards developers.

So, now that we got our DBA disclaimer up….. ūüėČ
Here are some of the things you might now know yet…

Continue reading

Windows 2016 Failover Cluster Services now has load balancing (enabled by default)

Having a production system with “new technology” is always keen to give you some issues you didn’t know about earlier on.

Lately, due to the arrival of BizTalk Server 2016 and related support for Windows Server 2016 and SQL Server 2016, we’ve seen some things we did calculate for.

It seems the Failover Cluster Manager now has built-in support for automatic load balancing:


Continue reading

Earned MCSA SQL Server 2012/2014!

msft_partner2016Codit is a Microsoft Gold Partner in Application Development, Application Integration and Cloud Platform and a Silver Partner for Data Analytics.

This means we are required to have staff certified, which in turn requires us to take (and pass) Microsoft exams once in a while. This is not mandatory on a personal level, but still it is necessary for some people in order to retain or even improve our Microsoft Partnership.

Although it may be a lot of work to prepare for the exam, most of us are quite keen on getting certified actually. As one of the more ‘senior’ guys at Codit, I¬†try to get certified¬†or pass an exam at least once a year. Although¬†it means that you will need to do some research, buy and read … and re-read the book(s), spend additional time looking up facts on MSDN, etc… I do agree it’s a nice recognition to have passed an exam.


In the last years I have been able to pass 70-461: Querying Microsoft SQL Server 2012/2014 and 70-462 Administering Microsoft SQL Server 2012/2014 Databases. Both exam preparations were actually great ways to get some recognition on working with SQL Server since 2001 (the year I graduated).
Along the way I have learned and picked up a lot from (ex-)colleagues, numerous blogs and various sources and books. Still, I was shocked at how much I was still able to learn by grabbing and reading the preparation books, doing the exercises, watching SQL courses on Pluralsight and looking up more information. It was an invaluable experience, learning so much. Some of it already came in very handy during the last year.

Since a couple of years Microsoft features the MCSA. The Microsoft Certified Solutions Associate. One of the tracks is MCSA: SQL Server 2012/2014.
It requires you to pass 3 exams: the ones mentioned above (70-461 and 70-462) and one other. Although it mentions 70-463: Implementing a Data Warehouse with Microsoft SQL Server 2012/2014 as the third exam, you can actually substitute it with any of the following as well:

Since I don’t feel that much for BI in SQL Server, I thought it was a good idea to certify for C# and took the Programming in C# exam last week.


With great pride I can now tell you that I also passed this last exam and earned the MCSA: SQL Server 2012/2014 title!

It even comes with a badge! :^)

I can only recommend it to be honest: I learned a great deal and find it very valuable in my day-to-day work.

If you have any questions, let me know and I’ll be happy to share my experience with you.

Now that SQL Server 2016 is out, Microsoft is already working on an upgrade path. Not one, but two! I know what is on the menu for 2017-2018! ;^)

TMIL Nov 2016- This Month I Learned

Earlier this month, I had a “great” idea… or at least I think I had one: Let’s put a monthly blog post up on what I learned during the previous month. If I keep doing this every month, I will not only feel some pressure to¬†actually learn something (to be able to put it up), but will possibly see the¬†progress (if any) I made.

So, without further ado, here is what I learned/experienced last month, in no particular order.

Connecting BizTalk to CRM (Online or on-premise) is not trivial

This is a great blog post containing a very nice example on connecting BizTalk to CRM for basic CRUD operations. This is part 2, where part 1 (linked in the article) is about connecting to CRM using the SDK.

Basically, what one needs to know is that,¬†certain entities/values like a country for example, will not return a value, but rather a GUID. This GUID must then be¬†queried for into CRM again, to know it’s value (in itself an entity again).

To connect to CRM, there are several options:

  • Instance Web API with a URL to the Web API and ODATA4Metadata.xml
    This contains the CSDL (Common Schema Definition Language), which defines an XML representation of the entity data model exposed by an OData service.
  • Organization Service with a URL to a SOAP service providing access to CRM, with a ¬†WSDL you can download (Organization.svc?singlewsdl).
  • One can use the CRM SDK libraries (download the CRM SDK or use the CRM SDK Nuget packages) to connect straight to CRM and let the SDK do the hard work for you

I general it just seems like a very good idea to build a facade API in front of the CRM services, since BizTalk uses typed schemas and CRM untyped ones. You can call this facade using SOAP/Web API for example.

I learned a lot from the following resources:

BizTalk Branch Edition has several limitations, but HIS is not one of them.

In some very specific scenario’s you might use BizTalk Branch Edition instead of the Standard or Enterprise versions. Although the Branch Edition comes with several limitations, Host Integration Server (HIS) is not one of them.

Some limitations are:

  • Only 1 BizTalk application is allowed.
  • WCF and SQL is allowed, but other adapter packs are not supplied.(e.g. SOAP Send)
  • Single server only, so no HA/clustering, etc…

However, HIS 2016 still comes with the BizTalk Branch license, you are still allowed to install the DB2 adapter for example!

Azure DocumentDb is a real piece of work

For an internal project I (finally) had the ideal use case to work with Azure DocumentDb. As a very keen user of SQL Server and normalization, i must say I had to bridge some gaps in order to appreciate the beauty of NoSQL. Schemaless databases are actually quite awesome when you think of it. Not ideal in all circumstances, but amazing none the less.

There is an excellent tutorial on getting started with DocumentDb on Microsoft Docs.

The BizTalk Server 2016 configuration wizard (still) hasn’t changed

This month I installed my first BizTalk Server 2016 Developer Edition. I noticed the configuration wizard actually hasn’t changed at all in regard to previous versions. When creating the installation documentation, I will have to update all of my screenshots however, since the titlebar and logos did get updated!

I was hoping the RTM version might contain some ‘easy’ AlwaysOn configuration. I suppose this will take some time in order to streamline it.

SQL Server 2016 installation comes without SQL Server Management Studio

When installing SQL Server 2016 Developer Edition for the first time, I only noticed after the completed installation that SSMS (SQL Server Management Studio) was not installed. There is now a separate entry in the setup pages that links to a certain URL where the latest version of SSMS can be downloaded and installed.

It seems SSMS as such no longer requires a SQL license of it’s own. It might have never required one in the first place to be honest: I don’t know.

Windows Server 2016 may cause issues with older Remote Desktop Management tools

I have been using Royal TS for a whole while now.¬†I have actually been using the last freeware version available (v1.5.1) and even put it up on once. Not (only) because I’m a cheapskate sometimes, but mainly because I just like the look and feel of that version. The simplicity and ease of use really does it for me.

Anyway, this month I installed my first Windows Server 2016, in order to prepare for the installation of SQL Server 2016 and BizTalk Server 2016. The first thing I tend to do on these VM’s is to connect via RDP, go to Server Manager, Remote Deskop Management and uncheck the checkbox which requires extra authentication. Reason is that Royal TS – and presumable some other, older tools as well –¬†is not able to provide this extra layer of security. However, I was not able to connect to the¬†Windows Server 2016 VM, no matter what I did. Nowadays, I am still looking for some new tool or solution/workaround. Let me know

Learning to work with Git

As an avid fan of Team Foundation Server (TFS) and Team Foundation Version Control (TFVC), I had the opportunity of working with Git for the first time. I wrote a blog post about this as well.

It allowed me to compare the two and actually, I tend to like Git some more due to the fact that you don’t have to be connected all the time before committing. However, as most of my work is still done involving BizTalk Server, TFVC will remain the standard for now.

Bluetooth allows more than 1 connection at the same time

Somehow I figured my laptop only could handle 1 bluetooth connection at the same time. I was very pleasantly surprised to see both my Bluetooth mouse and wireless headphones worked at the same time this month. Took me a minute to realize that this is probably known by a lot of people, so I didn’t quite make a big fuss of it… but I was honestly amazed that this was possible! Silly me right?

I learned that a practical limit for the number of bluetooth connections is mostly 3 to 4.