Welcome to Part 1 of this data scenario demonstration for a fictional sports retail company called Velocity Sports that takes them from having a on-premise database to having data in the cloud and gaining intelligent insights that help drive business decisions.
In this blog post, we’re going to tackle the following objective
Currently, Velocity Sports stores their sales data on a on-premise Access Database. However, they are looking for a cloud-based solution to securely store and manage their data.
In order to do this, let’s dive a little deeper into Velocity Sport’s current situation:
- Velocity Sports currently have a Access database that stores key sales information including Unit Sales, Product information, Manufacturer information, Store Locations etc.
- This sales data is currently stored as relational data.
- The CEO wants to move it from an on-premise solution to ensure that data storage is reliable and secure.
- Velocity Sports are want a database engine that doesn’t require overheads for patching or upgrading.
For the purposes of this demonstration, I’m going to migrate Velocity Sport’s existing Access Database to a SQL Azure Database. We can do this using SQL Server Migration Assistant for Access, which is a simple tool that allows us to migrate tables from Access to either a on-premise SQL Server or SQL Azure instance. To do this, we will need to create a Azure SQL Database as a target for our Access Database.
This is a fairly simple process, just login into your Azure portal to get started and click the New button. You can find SQL Databases in the new page. From here, enter the name of your new database, choose a subscription for it, allocated a new or existing resource group to it and select a source for the data (In our case, we’re creating a blank database). We will also need to provision it on a server. In this case, I’ve created a new server located in West US 2 (The datacenter region). Make sure that you allow Azure services to access the server. Enter a name for the server and set up authentication details. Make sure that you remember these as you’ll need it to perform the migration later.
For the purposes of this demo, I created a free database as the dataset I worked with wasn’t that large. One of the big advantages of the premium tier is that you have more flexibility in regards to recovery options in case of disasters etc. Full details of pricing can be found here.
Now that we have a SQL Database in Azure, we can now begin to migrate our Access data to the cloud. To do this, open up SQL Server Migration Assistant for Access which will start the Migration Wizard.
Enter a name for your migration project, choose a location to save it and select a target. For this demo, we’re selecting SQL Azure but you can migrate Access tables to SQL Server premises as far back as 2008.
We now have to select the Access databases that we wish to migrate to SQL Azure. This is just a case of finding the directory in which the Access DB is stored.
Once this is done, we can now choose the tables that we want to migrate to SQL Azure like so:
We now have to connect to SQL Azure to make the migration happen. In order to do this, we need to type in the server name, the database that we want to migrate the data to and provide our authentication details for the server.
If everything has worked, we should see the tables in a meta explorer. This shows what tables have been migrated, the columns of a table, data type etc.
We can now verify if our data has been migrated to SQL Azure by using Query Editor. This is just a basic feature that allows us to run T-SQL queries within the Azure portal. In this screenshot, I’m just making sure that our data has been migrated by selecting the top 1000 rows from our products table:
We can also get an overview of our SQL Database by looking at our dashboard:
From here, we can set up some important administrative tasks, such as setting firewall rules on our server to restrict access on the server via IP addresses, enable SQL Database auditing and threat detection.
That wraps up this part of the tutorial. We’ve taken a Microsoft Access Database that was on-premise and we’ve migrated it to a Azure SQL database in the cloud. Now that this is set up, we can move onto the next phase of this tutorial, getting that data from Azure SQL and putting it in Power BI. This will allow us to work with the data and help us get more meaningful insights from our data.