Recently I reviewed options for how to migrate Access databases to the Microsoft cloud. There are multiple target platforms to consider.
At a high level I see four category of MS Access databases. Each had it’s own target platform to match.
Simple (SharePoint List) – Table storage with rows and columns. Data entry such as a tracker, log, or flat input.
Relational (Dynamics 365) – Multiple entities with relationships such as one-to-many and many-to-many. Navigate data and advanced reporting.
Application (Azure App) – Advanced input, form validation, triggers, scripting, and code. Need flexbility of Azure Web App, SQL Azure, and PowerShell runbook to accommodate all features.
Reporting (Power BI) – Presentation of data into chart and table format. Drill down, filter, merge data sources, etc.
Last week many great technical guides were released on the MS Download Center. They describe the business benefits of new technology and technical steps to implement. Below are links with description and download for each (ZIP/PDF/PPT/DOC).
Fantastic visuals here quickly communicate the benefits of Azure, Dev Ops, and modern IT design.
Check it out. Enjoy!
Guides
Hybrid IaaS technical guides – These documents provide a technical overview of Hybrid Infrastructure as a Service solutions.
IoT technical documentation – This document provides technical guidance on deploying cloud services for IoT.
Power BI technical guides – This zipped folder provides technical guidance for deploying Power BI, creating dashboards, and other technical details of managed services.
SQL Backup to Azure GTM materials – This zipped folder contains campaigns, sample service offers, and templates for SQL backup to Azure.
Analytics business documentation – This zipped folder includes an overview of Analytics on Azure, including the Analytics vision and data platform scenarios.
Taking my cloud education further … I wanted to explore the SQL Server relational database offering. The first trick was to create a new “server” from the Azure portal. After that I opened the web based SQL Azure portal to confirm it was online and healthy.
I was surprised by how easy it was to create a connection. Simply create a firewall exclusion to your current IP address … and boom … you can open the SQL Azure with SQL Management Studio locally. Pretty cool!
The Azure Management Portal is proving very capable. It even has a handy popup dialog with connection strings for convenient copy/paste. Here is the process I used to import sample Northwind data:
Open both instances with SQL Management Studio
Right click local tables to build “CREATE TABLE” statements. Copy/paste those from local to Azure and execute. This creates the schema. There are some limitations of Azure so you may have to double check your TSQL syntax (http://www.tewari.info/2009/09/10/sql-azure-notes/)
Use the SQL Import wizard to migrate data. For Azure be sure to select “.Net Framework Data Provider for SqlServer” destination.
To resolve red errors, ignore [ntext] or [image] data types during migration. SQL Azure does NOT support [ntext] or [image] so I just removed those columns. Not sure of the best way to migrate this type of data yet. [ntext] should probably cast to VARCHAR(8000) and truncate anything longer. [image] should probably be saved to the filesystem (JPG/PNG/etc.) and then a URL string pointer to the image file kept in SQL.
Click next and finish. Congratulations! You now now have sample data in SQL Azure.
Today I deployed my first Azure application and documented the step-by-step process with detailed screenshots.
First I downloaded the Azure SDK and prepared Visual Studio 2010. Azure is a cool idea because of the support for highly scalable custom code. One frequent objection to cloud hosting is the basic functionally. Cloud providers tend to offer what’s easy and convenient … while avoiding custom code. Hybrid models may be the resolution. Architects who can connect multiple vendors seamlessly or blend on-premise with in-cloud servers will be in demand.
Locate the first Hands On Lab (HOL) “Introduction to Windows Azure”
Code and build in Visual Studio 2010
Verify local Azure Emulator is working (files / SQL database / Compute)
Deploy to Azure in the cloud (NOTE – this is lot of work the first time, must get connected and trusted)
We begin with the "Guest Book” sample application which allows you to post messages on a common wall with a picture attached. Simple enough.
Here you can see the tray Azure Emulator running the website locally on my laptop. For development you want to run things locally, test, and deploy to the cloud when stable.
Right clicking on the tray icon allows you to view Emulator status, logs, and details. You can restart and control instances here.
Out of curiosity I opened SQL Management Studio and viewed the “SQLEXPRESS” instance (which the default for the Azure SDK examples). Here you can see the databases and tables this sample created. The schema appears very abstracted. From Visual Studio 2010 all I saw were strongly typed Classes and Objects for data. Somehow that is being flattened to a database table using the Azure binaries, pretty cool.
Here is the Visual Studio 2010 Solution Explorer showing the 3 projects and related files inside of the “Guest Book” sample application.
OK, let’s get to the fun part. Open a web browser, navigate to https://manage.windowsazure.com/ and login to Windows Azure Platform Management. There is some new vocabulary which be confusing at first. First, create a new “Hosted Service. This can take 5-10 minutes to provision so be patient.
Once the “Hosted Service” shows “Created” then we must add a “Storage Account” for the ability to hold data in the cloud.
Certificate trusting the local laptop’s Visual Studio 2010 to publish. This is a LOT of work, but only a one time operation. Let’s get started. Right click on your Azure project within the solution explorer and click Publish.
The first time here we need to Add Credentials.
To pair the local Visual Studio with the Azure cloud, we need a certificate. Since this is our first time, create a new one. Name it whatever you like. I chose to name mine after the development laptop “JEFF-PC”
Click “copy the full path” to populate the clipboard with the local file path.
We’re going to leave Visual Studio 2010 for a minute, but keep it open in the background since we’ll be back. In the web browser go back to Windows Azure Platform Management. Click “add certificate” and paste the local .CER file path from the previous setup.
Click on the “Subscription” line item to view the “Subscript ID” number. Copy this to the clipboard. You will need to give that to Visual Studio 2010 locally for publishing.
Back in Visual Studio 2010 we need to finish the Add dialog by giving it two values: Subscription ID and Name.
Congratulations!!
You’ve successfully connected your local Visual Studio 2010 to the Azure Subscription. You should now see three cascading drop down menus with the Azure cloud deployment destinations.