For training courses and general lets have a look at updates to Power BI, an Adventureworks database instance has been set up on my machine, complete with 10 years of data that is great for using for training courses.
However, this database needs moving from its local machine to Azure
Previously bacpac files have been created to import into Azure via SQL Server Management Studio but they have always errored.
Its time to have a look at Azure Data Migration
Source Database
- Product – Microroft SQL Server Express (64bit)
- Operating System – Windows 1 Pro
- Version: 14.0.2027.2
- Release: SQL Server 2017
Attempt 1
Microsoft Data Migration Assistant
The Microsoft Data Migration Assistant allows you to upgrade to Azure by detecting compatibility issues.
The first thing to do is to download the msi package and then you can get started
Open the Data Migration Assistant
Click New to create a new Project
Then click create
The server would be connected to using Windows Authentication. However there are errors for this. the credentials must have control server permission.
In SSMS go to Security and Logins to find your own user Login.
Switch to the master database and run the following SQL
GRANT CONTROL SERVER TO [User Name];
Unfortunately the error comes back Securable class ‘server’ not supported in this version of SQL Server.
SSMS is not able to Migrate Express Databases
Attempt 2
b
Move to your Azure connection in SSMS and right click on databases Import Data Tier Application
go through the Import Data Tier guide
Currently we are moving to an S2 model. (Purchase model DTU) which is around £54 a month. When its not being used I will change it to S0, around $10 a month.
There are compatibility issues happening between the Azure database and the SQL Express data base.
The first is regards to COLUMNSTORE. Apparently COLUMNSTORE support is available in S3 and above
S3 costs around £109 pounds a month, twice the cost of S2.
The Solution
Simply Import as S3 and then let Azure Scale to another Standard option by clicking on Pricing tier
You could also move to vCore purchasing options too which is the preferred route as it offers more personalisation.
The DTU-based purchase model are differentiated by a range of compute sizes with a fixed amount of included storage, fixed retention period for backups, and fixed price.
The virtual core (vCore) model provides several benefits:
- Higher compute, memory, IO, and storage limits.
- Control over the hardware generation to better match compute and memory requirements of the workload.
- Pricing discounts for Azure Hybrid Benefit (AHB) and Reserved Instance (RI).
- Greater transparency in the hardware details that power the compute; facilitates planning for migrations from on-premises deployments.
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-service-tiers-dtu
I can finally move my Express database into Azure and manage the scaling. All sorted.
However you should be aware that if its Under S3 the index gets disabled and Power BI cant use it in this state
So the question is, If you are importing the data into power BI, will you be happy to keep the database at S3 level when its in use OR would be be OK with removing the columnstore index?
this is something to think about……
Debbies Microsoft Power BI, SQL Fabric and Azure Blog 