Migrate SQL Server Database to Azure SQL Database using Data Migration Assistant

We will be using the Data Migration Assistant to assess and then migrate an on-prem SQL Server 2012 SP2 database to an Azure SQL Database

Part 1: Download and install Data Migration Assistant

To install DMA, download the latest version of the tool from the Microsoft Download Center, and then run the DataMigrationAssistant.msi file.

Click next

Accept the license agreement

Click install

Wait for the install to complete

Check the box beside Launch Microsoft Data Migration Assistant and click Finish

Part 2: Open Data Migration Assistant and create assessment

We will now start with assessing our on-prem SQL Server

  1. Click the +New icon and select Assessment project type
  2. Give your assessment a name
  3. We will leave the Source server type and Target server type as is.
  4. Click create

I left the defaults checked and clicked next

Fill in the server name and authentication type. *Notice that the credentials used to connect must be a member of the sysadmin role

Click connect

Choose the sources and click add. I am only interested in the CentralDB database at this time

If you are satisfied with the sources that you have added, click Start Assessment

Obviously, the assessment run time depends on how many and what size of the sources

When the assessment completes

You can see the results on the screen or you can export the report as a json or csv file.

In this example, you can see that there are 3 Unsupported features and 1 Partially supported feature.

With each issue the details section will give the impact. Recommendation and more info

One last screenshot to show the assessment options

You can open, restart or delete an assessment

In part 3, we will look at Migrating a SQL Server database to Azure SQL Database.

 

 

 

 

SQL Server Reporting Services Timeout

The other day we ran in to an issue where the report was running 30 minutes, this may be the only report that we have that runs this long, but then it fails. The only error message was “Report processing has been canceled by the user. (rsProcessingAborted), but after checking, no one canceled the report processing. What just happened?

It was determined that the Report timeout option was set to “Use the default setting” like most of our reports are.

 

Well, where is this default setting and what is it set to? The default settings timeout is set in the site settings page of your SQL Services Reporting Services and it was set to 1800 seconds or as you guessed, 30 minutes.

 

You have two options to fix the the timeout issue.

1. Change the default settings timeout for the whole site in the site settings page.

2. Change the Report timeout setting just for that report. This is done by going to the report, clicking the … and choosing manage.

You then scroll down to Advanced and either choose “Allow the report to run for (add amount of seconds here) seconds before timing out” and changing the seconds to a higher value that will allow the report to run or choose the “Allow the report to run indefinitely (no timeout)” option.

 

Click Apply and your report should not complete.

 

 

 

Starting Again!

I was recently talking with a co-worker and he was talking about creating a blog, and I had mentioned that I have had a blog since 2007. Which got me thinking that I have not done a blog post in at least a year. So, I decided to try and start the blog back up. Unfortunately, it looks like all of my old posts are gone.

Most of the content of this blog will be about Microsoft Business Intelligence tools, SQL Server and a few other technology items.

Let’s hope I can keep this blog updated.