Azure as a SQL Server time machine

Reading Time: 5 minutes

In this post I want to cover how I used Azure as if it was a SQL Server time machine. Because I used Azure like one recently to resolve a Master Data Services issue.

By the end of this post, you will see how I used an Azure virtual machine to help resolve a SQL Server Master Data Services issue. In addition, why you can think of doing things like this as using Azure as a SQL Server time machine.

Master Data Services issue

I was trying to import a few Master Data Services packages created for SQL Server 2014 into SQL Server 2019. So that I could test some things relating to Data Vault 2.0 in a SQL Server 2019 Master Data Services environment.

However, I experienced a couple of issues whilst using the MDSModelDeploy executable to do the import.

First issue I had was that a large import was failing due to a timeout. I initially fixed this issue by increasing the timeout setting in the Master Data Services Configuration Manager.

Second issue was an error I was getting during an import. It was stating that an attribute data type was not valid. When I looked in the logs, I could not identify the direct cause in this large package.

So, in the end I used the Master Data Services Model Package Editor helped me narrow down the cause.

By this stage I realised that this investigation could take a while and I questioned if it was worth the time and effort? Especially for something I just needed up and running short term.

SQL Server virtual machine in Azure

So, I decided to use Azure as a time machine and created a virtual machine with SQL Server 2014 already installed from the Azure Marketplace. Making sure that the virtual machine had a good specification because I only intended to use it short term.

After connecting to the virtual machine I went to the Web Application Requirements (Master Data Services) web site in a browser on my laptop. I then copied the two lines of PowerShell that are on that site to configure the Master Data Services prerequisites in the virtual machine.

Once I had done that I setup Master Data Services by going into Master Data Services Configuration Manager. I then copied over zip files containing the packages I wanted to import onto the server.

Afterwards, I ran the import commands inside the virtual machine, which worked first time.

Once the imports had finished, I explored the SQL Server 2014 Master Data Services portal in a web browser to check the Models were there.

I had to install Silverlight the SQL Server 2014 virtual machine in order to explore using the below portal.

Something that I covered in a post which was part of a series of SQL Server 2019 posts. Which covers the fact that Master Data Services says hello to HTML in SQL Server 2019. Because in SQL Server 2019 you no longer have to install Silverlight to browse the Master Data Services portal.

Anyway, after checking Master Data Services I backed up the SQL Server database for the Master Data Services instance that was running on the SQL Server 2014 virtual machine. I then copied the backup to the computer running the SQL Server 2019 instance locally and restored it onto there.

SQL Server 2019

To get the database working with SQL Server 2019 I first restored the database on the instance.

Because the database was populated with fairly large Models, I performed some maintenance in the database first. By rebuilding some of the indexes on the larger tables and shrinking the transaction log. I did this to make the next step faster.

Once I had done the work on the database, I went into SQL Server 2019 Master Data Services Configuration Manager. I then selected the newly restored database. It informed me that the database had to be upgraded first. So, I clicked the ‘Upgrade database’ button and went to get myself a coffee.

Some advice about doing the above upgrade, you have to temporarily disable CLR strict security to get it to work. You can view how to do this in the guide from Microsoft about the clr enabled Server Configuration Option.

After I returned from my coffee, I opened up the Master Data Services Configuration Manager again and selected the database. I then completed the rest of the setup to get Master data Services to use the new database.

From there, I opened up Master Data Services and checked it. All the Models appeared to be OK, and the one that seemed to be causing the issues at the start looked to be fully populated.

In reality, I could have kept the Models on the virtual machine in Azure. However, I wanted to have the Models running on a SQL Server 2019 instance locally for various reasons.

Using Azure as a time machine

You can think of doing things like this as using Azure as a time machine. Because you can create virtual machines there that run older versions of Operating Systems and applications that you need for short term use.

You can usually do this by creating an image from the marketplace or by creating an image yourself.

It’s very useful for doing things like the above SQL Server example. Where you encounter problems with things that were designed for older versions, and you want to migrate them to newer versions like SQL Server 2019 or SQL Server 2022 gracefully.

In addition, it can save you a large amount of time investigating why they are not upgrading as well. Plus, it saves having to find somewhere locally to install your older application.

One key point to remember, if you do create virtual machines for short term use remember to tidy up after yourself and delete them to save costs.

Final words about using Azure as a SQL Server time machine

I hope my example of how I used Azure as a SQL Server time machine helps some of you save time and effort in the future.

Because it is useful to use Azure this way as well.

Of course, if you have any comments or queries about this post feel free to reach out to me.