Workshop: SQL Ground-to-Cloud

A Microsoft workshop from the SQL Server team

06 - What to Use When: Designing a Solution

In this workshop you have covered using SQL Server both on-premises and in-cloud configurations, as well as hybrid applications as a solution for data processing. The end of this Module contains several helpful references you can use in these exercises and in production.

This module can be used stand-alone, and does not require any prerequisite other than a laptop and some sort of design software (such as Microsoft Visio) - although you may also just use a whiteboard or paper for your design.

There are many elements in a single solution, and in this module you'll learn how to take the business scenario and determine the best resources and processes to use to satisfy the requirements while considering the constraints within the scenario.

In production, there are normally 6 phases to create a solution. These can be done in-person, or through recorded documents:

01 Discovery: The original statement of the problem from the customer
02 Envisioning: A "blue-sky" description of what success in the project would look like. Often phrased as "I can..." statements
03 Architecture Design Session: An initial layout of the technology options and choices for a preliminary solution
04 Proof-Of-Concept (POC): After the optimal solution technologies and processes are selected, a POC is set up with a small representative example of what a solution might look like, as much as possible. If available, a currently-running solution in a prallel example can be used
05 Implementation: Implementing a phased-in rollout of the completed solution based on findings from the previous phases
06 Handoff: A post-mortem on the project with a discussion of future enhancements

Throughout this module, you can use various templates, icons, stencils and other assets to assist you with each phase and also use these with your exercises. These assets can also be used in your production workloads: https://github.com/microsoft/sqlworkshops/tree/master/ProjectResources

For this module, you'll focus on the Discovery and the Architecture Design Session phases only. If you wish to develop your solution further after the course, you can use the assets above to complete all phases.

6.1 Understand the Problem Space

The first step in any project is to fully understand the problem the company needs to solve, and any requirements and constraints they have on those goals. This is often in the form of a "Problem Statement", which is a formal set of paragraphs clearly defining the circumstances, present condition, and desired outcomes for a solution. At this point you want to avoid exploring how to solve the problem, and focus on what you want to solve.

Begin with as complete an examination of the company and organization as you can. Gather information from as many sources as possible, and simplify the descriptions to have specific measurements and depictions of the environment.

From there, lay out the problem, and then review that with all stakeholders.

After everyone agrees on the problem statement, pull out as many requirements (goals) for the project as you can find, and then lay in any constraints the solution has. At this point, it's acceptable to have unrealistic constraints - later you can pull those back after showing a cost/benefit ratio on each requirement and constraint.

Activity: Review Business Scenarios

In this activity you will review three business scenarios, and pick one to focus on for the rest of this module. The company descriptions, project goals, and constraints have already been laid out for you.

After you make your choice, copy the problem statement into your working documents (see the Resources for examples) and make any changes or additions you want to make to the scenario. Feel free to adapt it to have more information where you want clarity - you can make assumptions about any part of the scenario. Are there sub-goals that have been left out? Any other constraints you can think of?

AdventureWorks

Adventure Works Cycles is a large, multinational manufacturing company. The company manufactures and sells metal and composite bicycles to North American, European and Asian commercial markets. While its base operation is located in Bothell, Washington with 290 employees, several regional sales teams are located throughout their market base.

Starting in the year 2000, Adventure Works Cycles bought a small manufacturing plant, Importadores Neptuno, located in Mexico. Importadores Neptuno manufactures several critical subcomponents for the Adventure Works Cycles product line. These subcomponents are shipped to the Bothell location for final product assembly. In 2001, Importadores Neptuno, became the sole manufacturer and distributor of the touring bicycle product group.

Coming off a successful fiscal year, Adventure Works Cycles is looking to broaden its market share by targeting their sales to their best customers, extending their product availability through an external Web site, and reducing their cost of sales through lower production costs. They are also looking to modernize their data estate.

Project Goals

Modernize to a newer SQL version
Move to Cloud wherever possible
Cloud Integration
Increase Performance
Publish Product Catalog to the Web
Enable Business-To-Business (B2B) systems

Project Constraints

Some systems must stay on-premises
In some cases, no code change is possible
The B2B system should be a "Pull" from partners

Contoso

The Contoso company is a multi-national business with headquarters in Paris, France. It is a conglomerate manufacturing, sales, and support organization with over 100,000 products. They are embarking on a multi-year process of migrating from company-owned datacenters to a cloud provider. They have narrowed the list of potential vendors to three, including Microsoft. They have high security and interoperability with mobile device concerns. There is also an Open-Source (OSS) investigation at the company.

Project Goals

Move everything to Cloud
Multi-Cloud strategy desired - standards-based
All client apps should be available worldwide
Server-side should be API's by default
Interest in parity for platforms (OSS Support)

Project Constraints

High Security and Auditing capabilities required
International Compliance required
Access Tracking required
Must be user-friendly on mobile devices

Wide World Importers

Wide World Importers (WWI) is a wholesale novelty goods importer and distributor operating from the San Francisco bay area in the United States.

As a wholesaler, WWI's customers are mostly companies who resell to individuals. WWI sells to retail customers across the United States including specialty stores, supermarkets, computing stores, tourist attraction shops, and some individuals. WWI also sells to other wholesalers via a network of agents who promote the products on WWI's behalf. While all of WWI's customers are currently based in the United States, the company is intending to push for expansion into other countries.

WWI buys goods from suppliers including novelty and toy manufacturers, and other novelty wholesalers. They stock the goods in their WWI warehouse and reorder from suppliers as needed to fulfil customer orders. They also purchase large volumes of packaging materials, and sell these in smaller quantities as a convenience for the customers.

Recently WWI started to sell a variety of edible novelties such as "chili chocolates". The company previously did not have to handle chilled items. Now, to meet food handling requirements, they must monitor the temperature in their chiller room and any of their trucks that have chiller sections.

Project Goals

Enable Big Data processing
Enable Machine Learning and Artificial Intelligence prediction capabilities
Cloud platform desired, but may need to consider on-premises options

Project Constraints

Short timeframe
Unknown Data Sources
Use only API calls for the predictions

6.2 Understand the Technologies and Processes for Solving the Problem

With a firm understanding of what the customer needs, you can now consider the technologies and processes at your disposal for the solution. Each technology will have benefits and considerations, so at this point you just want to list out all of your options. Everything is on the table at this phase, and ensure that you check the list you create with other professionals to ensure you have considered everything that could solve the problem.

Activity: List the Technologies and Processes for the Problem Space

In this activity you will list out all of the options you have for your problem space.

Steps

Open your Architecture Design Session (ADS) document and detail all technologies you have studied in this workshop, and list them out. (Order is not important during this step)
Next, write the problem element next to each technology that it could solve
Document any processes you should follow when using each technology

6.3 The Decision Matrix

Following the process, you now know the problems you want to solve, the desired outcomes for the solution, and several tools and technique options that you can use to achieve your goals. In most situations, there are several ways to solve a given problem. Sometimes the "best" solution is too costly, inconvenient or unworkable due to the requirements or constraints the customer puts on the solution.

Because most solutions are fairly complex, and there are mutiple technology and process choices, considerations and requirements, a Decision Matrix that lists these elements is useful. It contains columns for the technology and process options you have, and the requirements and constraints as rows. Each column gets a score you assign from a low number (does not meet this requirement) to a higher one (does meet the requirement). These numbers are summed at the end of each row, per requirement. The highest number is usually the best technology for that aspect of the solution.

As an example, assume you have an application that is written using T-SQL statements, and you want to store data that has high security requirements and is available online:

Requirement/Constraint	SQL Server in Azure VM	Azure SQL DB	Postgres as a Service
Low Cost	2	3	3
Easy to Manage	2	3	3
Highly Securable	3	3	2
Fully Supports T-SQL	3	3	0

Score:	10	12	8

In this simple example, Azure SQL DB is a high candidate for your solution. (In production, there would be far more requirements and constraints, and you may need to use a 1-5 scale rather than 1-3)

Activity: Create a Decision Matrix

In this activity you will use the scenario you selected from above and create a Decision Matrix using a spreadsheet.

Steps

Open this reference and read through it.
Create a spreadsheet for your Decision Matrix, or download one of the samples from the reference.
Fill out the Decision Matrix based on the problem requirements and constraints using the technologies and processes you developed in the previous steps

6.4 Explain the Solution

The architecture design session above is most often conducted by the technical staff with representation from the business. After the design session is complete, the findings should be condensed into an instrument (slides, notes or other graphics tool) that allow the team to explain the proposal along with a set of options. At the end of the presentation, your team should also include a description of the project, timelines, and responsibilities.

Activity: Create a Solution Presentation

In this activity your team will create a solution briefing with options and timelines.

Steps

Open this reference: https://github.com/microsoft/sqlworkshops/tree/master/ProjectResources
You may use the PowerPoint template provided (6 - Handoff.pptx) or create your own briefing tool, using anything you like.
Make sure you include at least one alternative option and explain why you chose your original one.
If time permits, review the schedule documents in the Resources area and assign reasonable timelines to your soluition.

References

How to Write a Problem Statement - Article on writing effective problem statements
Decision Matrix Analysis - Article on creating a Decision Matrix
Azure Pricing Calculator - Create a cost analysis of your solution
Azure Data Architecture Guide - This guide presents a structured approach for designing data-centric solutions on Microsoft Azure. It is based on proven practices derived from customer engagements
Azure Reference Architectures - Recommended practices, along with considerations for scalability, availability, manageability, and security
Microsoft Cloud Adoption Framework for Azure - Article on writing effective problem statements
Microsoft Azure Trust Center - Full reference site for Azure security, privacy and compliance

Congratulations! You have completed this workshop on "SQL Ground-to-Cloud". You now have the tools, assets, and processes you need to extrapolate this information into other applications.

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Uh oh!