Category Archives: Deep Thoughts by SharePoint Jack

Using a Mac in a Microsoft world: SQLPro from Hankinsoft

While most of this blog covers my professional experience with SharePoint running on the windows platform, I’ve also spent a bunch of time as a Mac user.

In the past, I’ve almost always remoted into windows machines to get work done – SharePoint, Powershell, SQL Management Studio, etc.. all required this approach.

This was pretty much the only option when things were on premise but now that the cloud is such a focus, there was one tool I was particularly interested in:

A tool for interacting with Microsoft SQL Server in Azure from my mac.

I tried a handful of options including some open source cross platform tools, some paid cross platform tools and a mac only gem called SQLPRO for MSSQL.

The free options were kinda 1990’s java ugly. One wouldn’t even connect, and one connected but I ran into problems enumerating fields.

Of the paid options, the best bang for the buck for me was Hankinsoft’s SQLPro for MSSQL.


This tool is a native mac application (not a java port) and it shows: the app is fast, responsive, and looks like a mac app, supporting newer features such as the mac’s special full screen mode.

I had no trouble connecting to PaaS SQL in Azure, and was able to do all the things I’d normally want to do. The tool has code completion,  the ability to drag and drop field and table names from the navigation pane on the left to the editor, some nice features like the ability to auto indent sql code, and the ability to run multiple queries (separated by semicolons;) and display the results from each one in the results window, just like MS SQL Management Studio does!

SQLPro is a pretty stable product, and that’s no surprise, as the developer has been making database tools for quite some time now. (There are versions for MySQL, SQLite, and Postgres, as well as a ‘studio’ version that supports multiple types of databases)

SQLPro was $79.99 as of this writing – This is a one time, buy it, it’s yours price, and it’s very competitive vs other tools that also offer the ability to connect to and work with Microsoft SQL server.

So far I’m really enjoying being able to run queries against SQL in Azure natively on a mac – This is a great tool and highly recommended!

SQLPro for MSSQL for Mac
by Hankinsoft Development

FINALLY! It’s now possible to migrate data to SharePoint Online without data loss.

After 18 months of persistence, we’re finally able to migrate to SPO.

Up until yesterday, it wasn’t possible to migrate user data from SharePoint if the user was missing from Active Directory. It is now if you’re migrating to SharePoint online and using the new migration API!


Early on we ran into what was a pretty glaring problem for us, and I suspect for anyone else trying to migrate using a client side migration tool.

The Problem:

One of the data types in SharePoint is the “user” data type.
This is most commonly used/seen in document libraries – it shows you who last edited/updated a document.
It’s also commonly used as a field type in a SharePoint list.
For example, you might have a sharepoint list named “legal cases”

The problem is, using the client side API used for most migration tasks involving SharePoint Online, you can’t insert a name of a person if that name can’t be found in Azure Active Directory.

The 18 month Path to Resolution:

While it didn’t take Microsoft 18 months of actual work to fix this, it was about 18 months from start to finish.

I won’t bore you with the details, but the take away here is to never give up. We were told ‘no’ or ‘it can’t be done’ or ‘that’s by design’ multiple times.  This was an important issue so we pressed on, reaching out to every contact we knew, from multiple levels within our organization to multiple levels within Microsoft. All that persistence paid of!


One conversation along the way that was particularly interesting was a special presentation Microsoft had arranged where we got to talk to the person who led Microsoft’s internal migrations from on premise to office 365. I was eager to ask them how they had moved list data with abandoned user data – I was certain they had some internal tool, or did some back door load that wasn’t available to outsiders, or maybe had some script that identified every instance of the missing users and recreated them in AD so the migration could complete.  When I asked, they skirted the issue. I pressed on. After they told me, I understood why. They didn’t do it. When pressed they said they had to leave some data on premise because there was no way to move this data to SPO. On that day, I felt both validated and let down at the same time!

The Solution:

At ignite 2015, Microsoft announced a new Migration API for SharePoint Online.

On the same day, ShareGate announced that it would support this new API with it’s new ‘insane mode’. I spoke with a few people, some thought this new API would resolve the issue, while others said it would not – it didn’t at that time. 🙁

Shortly after our case must have reached one of the senior escalation engineers at Microsoft – I remember being told that the new API resolved the issue, then going back with evidence that it didn’t and I think that’s when traction really picked up. We supplied a Business impact statement and Microsoft added the fix to the list of things they were working on.  The feedback I got down the road was that this ended up being a huge undertaking for them. It wasn’t nearly as easy as one would think, due to how SharePoint is structured internally.  There were multiple setbacks, but we received excellent communication and updates. The time line didn’t bother me – I was thrilled to know it was being worked on.

Fast forward to fairly recently and we received word that the fix was approved and would be moved into our tenant. This was great news! Work didn’t end there however.

Microsoft went into depth about the work done to fix this issue.

While I had expected some new API, or an option when sending data like “override if missing” no such changes were needed. Microsoft updated the migration API to handle all of the needed back end stuff seamlessly. They did not update the CSOM. This meant that for this to work, the new API had to be used.

We were already using ShareGate coupled with insane mode which uses the API. I remember from past conversation with them that ShareGate uses a combination of insane mode and CSOM – even when insane mode is being used – I figured this would mean ShareGate would need to be aware of the changes to the migration API and would need to handle things differently. For example, in the past, ShareGate could replace a missing user with a user of our choice, this would no longer be necessary.

ShareGate was great to work with – they had long been aware of the user data migration issue and understood what I was talking about almost immediately.  Once the API had been updated, the three of us worked together to ensure that Sharegate’s test tenant also had the new Mirgration API updates so they could code up a solution.

I’m soo pleased to say that Sharegate turned this around in about a month, and we received a beta last Friday.  Even more impressive is that the very first beta from ShareGate with support for this worked like a charm!

Even more impressive is that the very first beta from ShareGate with support for this worked like a charm!

Sharegate has released version 5.8 with this functionality Today (2/29/2016).

Confirmed: Microsoft has already rolled this out to all O365 tenants. (as of 2/29/2016)

Special kudos and thanks to Brent Vezzoso and the unnamed hero’s within Microsoft who worked so tirelessly to make this happen. I can’t tell you enough how much this means to me, my company, the SharePoint community, and to Microsoft. You’ve done a great thing here!

  • Jack

A quick update on where I’ve been…

I noticed today that I haven’t posted anything in a while here- notably the entire month of August. That’s partly due to vacation schedules in the summer – I went on a family vacation then went to Laracon for 4 days in Kentucky to learn more about PHP the programming language that powers more websites in the world than any other language. (Like 81%!)

Investing in On premise SharePoint today feels like putting new tires on a car you’re going to junk in 2 months.

Another reason though, is that as we move to office 365, my job role is changing. It’s been a good year now since I’ve built up a new on premise farm for work, and even the announcement of SharePoint 2016 on-premise doesn’t really excite me.  It appears that on-premise has a limited future, and the cloud is the direction of the future, not just for SharePoint but for lots of technologies.  Investing in On premise SharePoint today feels like putting new tires on a car you’re going to junk in 2 months.

So as I work more with o365 and less with on premise, the issues I face are different.  In August I created a series of 20 or so SPO training videos and put them up in the Office 365 video portal (that I’ll be talking about in my talk at SPSTC in October) That took a good part of my free time.

I’ve spent a lot of time opening issues with Microsoft. Today I have a success story to share, which I’ll post separately on. I believe I have another success story coming in Late October that’s HUGE for the SharePoint community, and critical for the migration to o365 from on premise.

As always, thanks for stopping by!


Is it time for an end-of-year email “Flush”?

The end of the year 2014 is nearing and I’m starting to get some ‘what a great year’ email in my inbox.

As I reflect back on the year, I’d have to agree.

As I look at my work inbox, It’s sitting at 71 items – that’s not exactly ‘inbox zero’ but it’s a low enough number that I can quickly scan all of it at the end of the day to make sure nothing slipped by.

There are some great advantages of “inbox zero” and while I rarely get to ‘true zero’, having a small number of items in my inbox does provide a certain amount of peace and comfort that lets me focus my energy on other things, instead of having a looming feeling that I’ve forgotten to address something critical.

I’m sure there are plenty of books and blog posts on how to achieve such inbox nirvana, And I can’t say that I’ve read any of that. I do have one suggestion…
When your inbox gets overwhelming, it’s time for a flush.

When your inbox gets overwhelming, it’s time for a flush.

Now this doesn’t mean you just delete everything, but why not make a folder, call it “Stuff I didn’t get to in 2014” and drag EVERYTHING in from your “inbox” to this new folder? Then start out 2015 with a clear inbox and see if that helps your productivity and focus.

– Jack

Filed under “Deep thoughts by SharePoint Jack”