A common challenge for any sort of SharePoint migration is how to address integration with other systems. Most systems can be migrated fairly easily in isolation but finding the best ways to handle interfaces to other systems, and the data logistics around performing such a migration without business impact, often prove a challenge.
I was recently engaged to perform a SharePoint data migration for a leading Leisure and Tourism sector client who was migrating from SharePoint 2013 to SharePoint Online. I encountered a number of Lists, stored within SharePoint, but utilising the Business Connectivity Services service application (BCS) to provide several External Data columns for which the list of available values was selected from external SQL Server database tables.
This was an occasion when it would have proved beneficial and cost-effective for the client to have engaged the services of a migration specialist at an earlier stage, as the business stakeholders were not aware that this external integration existed until issues were encountered mid-migration.
I would always recommend that a Readiness Assessment and SharePoint Migration Plan are prepared by a specialist so that complications such as these are identified and planned for upfront and business expectations can be set accordingly. In this case, my initial remit was only to perform data movement, in line with an internally prepared plan.
It was decided that these Lists should be migrated as-is to SharePoint Online and the SQL tables migrated to Azure SQL Server. The organisation’s SQL Server architects would then implement a mechanism to synchronise the database values between SQL Server and Azure SQL Server.
Key challenges faced within this SharePoint Migration:
- Changing External Data Column References from the on-premises SQL Server to the Azure SQL Server.
- Migrating Mail-Enabled Security Group references stored in ‘Person’ columns.
- Building Azure SQL Server Instance and Databases.
- SharePoint Online Business Connectivity Services integration with Azure SQL Server.
Preparing the Azure SQL Server
The first requirement was to prepare the Azure SQL Server. Azure SQL Server was a new thing for me, although I have substantial familiarity with traditional SQL Server. Creating and configuring the Azure SQL Server instance didn’t present any real challenges – the Azure console guides you through provisioning Databases (just selecting a Resource Group, Collation and Pricing tier depending on the required capacity) and prompted to create a new Virtual SQL Server Instance as one wasn’t already present in the tenant.
Once created, you then administer it through Management Studio like a traditional SQL Server instance. The first time you connect to the server, it prompts you to add a firewall rule – and handles it automatically. Once connected, to open up access from SharePoint Online to Azure SQL Server, I ran the following for each database, in line with Technet guidance:
exec sp_set_database_firewall_rule @name = N'AzureSPOFirewallRule', @start_ip_address = '0.0.0.0', @end_ip_address = '0.0.0.0'
Azure SQL Server database permissions are SQL Authentication only by default; as this met my requirements, I didn’t try to do Windows Integrated.
I kept the permission model straightforward and just created a DataReader user for each database (I only needed to expose data read-only):
CREATE USER FirstDB_Read WITH PASSWORD = '' ALTER ROLE db_datareader ADD MEMBER FirstDB_Read
That was the Azure SQL Server ready and I just needed to create some tables, which I did by exporting the data from SQL on-premises and importing into Azure SQL through SQL Server Integration Services (SSIS), into a table schema defined by the client.
Preparing the BCS Data Model in SharePoint Online
Next was the preparation of the BCS Data Model in SharePoint Online Business Connectivity Service. In SharePoint Designer, I created a Secure Store Target App for each set of SQL credentials, and a BCS Model with an External Content Type (ECT) for each Azure SQL Server table I needed to expose – which behaved in the same way as it does in SharePoint 2013 on-premises.
Once I remembered the gotcha that SharePoint Designer can’t actually use a Target App itself, and needs to be given an SA account credentials to connect to your SQL Server (this ‘feature’ occurs exactly the same when working with SharePoint Online/Azure as it does on-premises), this worked nicely and the Azure SQL Server tables could be surfaced by SharePoint Online using the Target App to access the data with shared credentials.
This is when it got interesting in terms of the SharePoint data migration…
I first performed a migration of the list in the normal way with Metalogix Content Matrix. The External Data columns migrated correctly in themselves, and all the data was intact in the target.
But as you would expect, the association with the External Content Type broke. Editing an item was impossible – it wouldn’t accept any value for this column. Unfortunately, I could find no way to change the External Data Source for the column, so couldn’t re-attach it to the new SharePoint Online External Content Type.
To overcome this, I deleted and re-created all the External Data columns in each list with associations to the new External Content Types, then deleted all the data and performed another migration. Except, this time migrating at item-level into the shell of the new list, mapping the old columns to the new columns. This worked well and resulted in the data appearing correctly.
The only oddity was that the first time you edit an item, you have to re-specify the External Data values. This seems to stem from the way that Lists store the value that has been Selected in a hidden column as well as the column specified, in the same way, they do for Termset-driven values.
Issues with Mail-Enabled Security Groups (MESG)
Just when I thought I’d cracked this migration, I identified one more issue: some of the lists had a column called Recipient, of type ‘Person’, but which often held references to Mail-Enabled Security Groups (MESG), rather than to a user.
Even though these Mail-Enabled Security Groups were synchronised to Azure Active Directory, the Content Matrix tool didn’t handle these correctly and instead replaced them all with a placeholder.
To work around this, I configured the migration tool to map the email addresses of these Mail-Enabled Security Groups to a new, temporary text column in the target list, then wrote a Workflow that fired on item creation and populated the Recipient column with the contents of the temporary email address column.
The Workflow fired as the migration created each item, and copied the email addresses into the Recipient column, automatically translating the email address to the Mail-Enabled Security Group object for each item, so resulting in these items correctly migrating.
A trick worth remembering…
This is a fairly straightforward example of how external connectivity and system integration can create complications at migration time and explains how these problems were overcome when encountering External Content Types while moving to SharePoint Online.