MDS 2016 Upgrade : Timeout issue

Question

Hi. I am trying to upgrade from MDS SQL Server 2012 to 2016 version. I have the database in one server ( database is restored in SQL 2017 ) and the Application server in another server. While executing the upgrade wizard for MDS in the database option - I am getting error :

Microsoft.MasterDataServices.Configuration.ConfigurationException: Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

CREATE UNIQUE CLUSTERED INDEX [pk_tbl_3_X_EN] ON [mdm].[tbl_3_X_EN] ([Version_ID] ASC, [ID] ASC) WITH (DROP_EXISTING = ON, ONLINE = ON) ON udpsModel_3([Version_ID])
EXEC mdm.DropIndexIfExists N'mdm', N'tbl_3_X_EN', N'ix_tbl_3_X_EN_Status_ID'
DROP INDEX [ix_tbl_3_X_EN_Status_ID] ON [mdm].[tbl_3_X_EN]
EXEC mdm.DropIndexIfExists N'mdm', N'tbl_3_X_EN', N'ix_tbl_3_X_EN_Version_ID_Status_ID'

I have also checked this link - https://social.msdn.microsoft.com/Forums/en-US/ef80edc0-09be-4d11-adfb-6ffeebd63ce5/mds-database-upgrade-from-2014-to-2016-fails-with-timeout?forum=sqlmds , but has not solved the issue.

How to fix this?
Thanks

Answer 1

Amelia suggested that you should also apply CU14 for SQL 2016 SP2. But to be honest, I would not expect that to resolve the issue.

The root problem is that the MDS wizard has an execution timeout which is non-zero. And apparently your table is larger what the timeout permits. Or your hardware is too slow.

I am not very acquainted with MDS, so I'm hesitant to suggest tricks. You could copy the data in this table [mdm].[tbl_3_X_EN] somewhere else and the delete a lot of it before running the wizard. But then you need to know how to put it back. Can be tricky if the schema changes with the upgrade.

You can also look into improving the hardware. If this runs on a VM with humble resources, maybe speak with the IT department if they have something more powerful that you can borrow for a while. Or, if this is at a cloud provider, upsize your environment temporarily.

In my opinion, this is a deficiency in the product, and thus if you open a support case, you should be refunded. This is after all a blocking error.

Answer 2

Thanks. I will install these and will provide an update on this soon.

Answer 3

Hi venkateshpadmanabhan-5594,

Could you please try to install SQL Server 2016 SP2 and CU14 for SP2?
Please make sure you use the administrator account that was specified when creating the MDS database to upgrade the database.

Best Regards,
Amelia