Workshop: Modernizing Your Data Estate With SQL Ground-to-Cloud

A Microsoft workshop from the SQL Server team

02 - Auditing Your Data Estate Architecture

In this workshop you'll cover how to systematically create a modern data estate using SQL Server in on-premises, in-cloud and hybrid solutions. In each module you'll get more references, which you should follow up on to learn more. Also watch for links within the text - right-click each one and select "Open in New Tab" to explore that topic.

(Make sure you check out the Pre-Requisites page before you start. You'll need all of the items loaded there before you can proceed with the workshop.)

You'll cover these topics in the workshop:

2.1 - Business Process Analysis
2.2 - Identify Data Sources and Sinks
2.3 - Map Data Paths
2.4 - Gap Analysis

An audit of a data estate is a massive undertaking. It's rare to start with a new implementation, so in most cases you are asked to be a detective in locating your organization's current data elements so that you can most effectively secure, tune, and update them. In smaller organizations there are fewer people that can take on the task, and in larger organizations there are so many elements that even the most dedicated team finds it difficult to be successful. However, there are tools, processes and techniques you can use to create a useful audit.

Scenario: Wide World Importers

The Setup section introduced Wide World Importers and their growing challenges.

The IT team has begun a modernization effort, and the Data Team has been asked to survey their users to see if there are improvements that need to be considered. The Data Estate at Wide World Importers is a mix of 3rd-party applications which cannot be altered, custom applications that have legacy code that is being evaluated for security, performance and availability improvements, and also analytic workloads such as Business Intelligence and Data Science projects. The organization would also like to understand more about the Data Lake architecture to see if it would help in their environment.

The Data Team has compiled a list of objectives for evaluation to see if newer versions or platforms would be useful:

The ability to audit and track their Data Estate components
Improving query performance without having to make application changes
Classifying key data columns and being able to audit access to these columns to meet the needs of GDPR compliance
Providing better database availability when applications use long-running transactions
Allowing the WideWorldImporters team to access data from sources like Oracle, Azure SQL Database, and Azure CosmosDB without having to develop expensive ETL jobs
The database team at WideWorldImporters wants to evaluate SQL Server on Linux
WideWorldImporters also would like to know other capabilities exist in the latest versions of SQL Server that might help them before more efficient and extend the capabilities of T-SQL
WideWorldImporters also would like to learn more about how they can plan and execute a migration to the latest version of SQL Server and reduce their risk for upgrades
WideWorldImporters is also evaluating Azure SQL so they want to know what features in SQL Server also work in the Microsoft Azure Data Platform

To begin those improvements, the Wide World Importers (WWI) leadership team has tasked the Information Technology organization to create a comprehensive plan to audit where they are now, and how to accomplish all of the goals stated above.

One of the tools WWI (and your organization) can use an IT Framework to create an ontology and process map for your data estate. There are many different IT Frameworks for organizing your Information Technology platforms for your organization. While almost no one can fully implement a framework completely, they still have great value in creating a structure that you can use to get started and modify your own map.

A few primary industry IT Frameworks are:

Framework	Use
The Zachman Framework for Enterprise Architecture	Creates a common "vocabulary" using perspectives/views: Planner, Owner, Designer, Builder, Subcontractor, and User. The second dimension implements the six basic questions: what, how, where, who, when and why. This Framework is more of a description and language than a process. It's a good place to start understanding your organization's IT estate, including the data aspects.
The Open Group Architectural Framework (TOGAF)	Allows you to customize an approach for design through governing an enterprise IT architecture, modeled at four levels: Business, Application, Data, and Technology.

And of course there are many other Frameworks, some general-purpose, others specific to a particular use or industry. You can see a list of more Frameworks here.

Some of these Frameworks focus on a top-down, central IT team approach, providing a consolidated view of the entire estate - including the data platforms. Other that are more data-centric, such as the Data Mesh, push the responsibilities for each data area out to the affected part of the organization. In any case, these frameworks are ideals - and a complete and comprehensive view of any single aspect (compute, security, file storage, and so on) is fairly rare. It is a huge effort to locate and catalog each and every facet of the IT infrastructure.

Whichever framework your organization uses - even if there is no defined framework - you should have a general map of your Data Estate. A Data Estate (for the purposes of this workshop) is the processes, platforms, and people involved in creating, storing, processing and disposing of organizational data. This includes not only the data your organization maintains, but the data your organization leverages.

2.1 Business Process Analysis

Business Process Analysis (BPA) evaluates an organization's actions to achieve a goal. You can use this discipline to discover the sequences the organization's members (employees) are using to get things done. While BPA is most often used to improve the efficiency of a business or organization, you can leverage the results of BPA to find data Sources (where data originates) and Sinks (the destination(s) the data moves to next) in your data estate.

You can learn more about Business Process Analysis and find a simple checklist to start this process at this reference.

Working with a Business Analyst

In many cases your organization will already have a BPA completed. If your organization has a Business Analyst role, you should contact that team to start your data investigation. Data discovery and movement is at the core of this discipline. You can read more about the Business Analyst role here.

Mapping Business Processes to data with Business Process Model and Notation

You may not have a Business Analyst (BA) at your organization or have had a BPA completed. In this case, you can start with the main applications you are aware of in your organization and create a data map from there. If you do have a BA in your organization, they will often document processes using a specific tool.

Other professionals in your organization may not be as familiar with database and other technologies, and will normally describe these systems in terms if what actions they take, rather than in the systems they use. To bridge the gap between business processes and communicate effectively with your BA's, you can use Business Process Model and Notation (BPMN). BPMN uses a series of "swimlanes" or rectangles that capture a particular process, person, or group, and the actions they take represented by icons. When the process, person or group completes part of their process, another icon is added. If one swimlane passes off the results of an action to another process, person, or group, another rectangle is documented and arrows show the direction of flow.

Activity: Detail your Organization's Data Estate Goals

In this Activity, you will create a list of goals for your Data Estate similar to the one WWI created above.. You can use any tool for this Activity, including paper and pencil. This Activity can be completed alone or as a group.

Steps

Review the Wide World Importers Scenario.
Briefly review the IT Frameworks described above, and bookmark them for later reference for a discussion with your larger IT organization to see if you are using any of them, or should be leveraging them.
Open this reference in a new tab to see some basic BPMN icons.
Review the Contoso graphic shown earlier and review how the data elements show a process flow.
Document at least three areas of emphasis for your data estate for your current organization, similar to the one WWI did in this Module. You will return to this document throughout the course, so ensure that you make notes on it as you progress.

2.2 Identify Data Sources and Sinks

The focus of your architecture design is locating where your data originates, and how it moves through the applications and processes in your organization. In general, your organization's data resides in some strcutured or unstructured data storage, and some systems also include a data processing engine or engines. Identifying these locations is essential to proper security, processing, reporting and applications.

Creating a data map is a non-trivial task, and you should bias towards having the tier-1 application's data documented at the very least, as opposed to only creating the Architecture Diagram if everything is documented. You can always add information later.

Tools

You can create a Data Architecture Diagram using many different tools, from manual-entry systems or spreadsheets through automated systems that collect the location and information about your data Sinks and Sources. In any case, the discovery of data sources is the most labor-intensive part of the effort, followed by ensuring the Diagram is up to date, discoverable, and usable by those who need to access it.

You can see an example of a Data Architecture Diagram below:

For data source discovery, you can often start with the tier-1 applications you organization uses, and find the Sources and Sinks they use by tracing their path. You can also check with your storage team to evaluate data storage elements that do not fall within a data processing engine.

The MAP Toolkit

To discover database engines, there are multiple tools you can use. One of the oldest and simplest is the Microsoft Assessment and Planning (MAP) Toolkit. You can read more about it here.

The MAP Toolkit involves several Phases, which starts with collecting the data across your network or enterprise and creating several reports that you can customize.

Once you start the Collection action, the Toolkit produces several reports that you can review for the SQL Servers in your environment.

Activity: Evaluate and Bookmark the MAP Toolkit

In this Activity, you will complete a Learning Path for the MAP Toolkit. If time is limited, bookmark this Path for future use. This Activity can be completed alone or as a group.

Steps

Open this reference in a new tab and complete the Learning Path.

Microsoft Purview Data Map

Microsoft Purview is a suite of tools for data governance, risk assessment and management, and compliance that allow you to govern, protect, and manage your entire data estate. For Data Discovery, this section focuses on the Data Map feature.

The Process to create a Data Map using Purview involves:

Create an account in the Microsoft Purview governance portal
Create a collection and assign permissions
Create and manage Sources and Scan operations
Apply classifications and Labels on assets
Edit Data Catalog to add Information for the collected assets
Review Asset insights on your data in Microsoft Purview

You can see a list of supported sources here, and this list is constantly being expanded.

Activity: Complete an Overview of Microsoft Purview

In this Activity you will review the process for creating an on-premises or in-VM SQL Server Instance Data Source, and if time permits, complete an Overview Learning Path for Microsoft Purview.

Steps

Open this link and review the steps you see there. Bookmark for later reference.
If time permits, open this Learning Path and complete only the Introduction. Bookmark for later reference.

2.3 Map Data Paths

Earlier in the process you identified the sources and sinks for your data based on the applications that use them. Now you'll want to trace the path the data takes from creation to consumption. In some cases this involves mapping the network path of the creation of the data asset from the application directly to the sink, and in other cases you will need to trace the paths the data takes during backup operations, Extract, Transform and Load operations (ETL), and any cross-system queries that occur. This is one the most involved parts of the auditing process.

You can start with the application paths, documenting the connections that are made from the application to the data source. In some cases this will be a direct connection, and in others there are layers of systems that take data requests, process them, and return the result to the application.

For the systems that move data from one sink to another, there are more options for that process.

Data Movement options

There are multiple options your organization likely has to move data from one location to another. They are grouped into two general toolsets: a manual, scripted, or platform-specific tool (such as PowerShell or SQL Server Integration Services) and a system specifically designed to move and potentially operate on data, called a Pipeline.

The process to locate these processes and tools are to engage with the team that is responsible for creating and running them. You can often find those systems when you look at your Reporting and Business Intelligence outputs.

Pipelines

A Data Pipeline is any process or set of processes that moves data along a processing path. It might be as simple as copying data from one storage location to another, or as complex as extracting data from a database system, altering that data in some fashion, distributing it to several locations, all with retry logic, notification systems, and scheduling.

There are several decision points to consider when designing or purchasing a Data Pipeline:

Security: Does the system take into account the sensitivity of the data and ensure that security as it moves through the pipeline
Complexity: Is the system simple enough to ensure that it can be monitored and maintained
Interoperability: Does the system interact with all of the data sources and sinks you have, and will it interface with the ingestion and exports of other systems in a seamless way
Performance: Can the system move the data quickly enough through the Data Pipeline such that the ingestion and refresh of data sources do not clash
Cost: What is the budget of the organization - not just the licensing costs of the system, but the costs it generates and the personnel costs to operate it

Tools

There are various levels of tools you can use to build a pipeline, from using a scripting or command language, all the way to a fully dedicated Data Pipeline environment. Each has benefits and costs to consider.

Non-Dedicated Data Movement Systems

Processes, Scripts, and platform-specific tools move data from a sink to another sink either on a schedule, manually, or through some data trigger. They can push the data from one system to another, or pull the data from one system into another.

PowerShell is a tool shipped with all modern version of Microsoft Windows, and it is available for other platforms as well. It has the ability to work with files, e-mail, schedules, cloud environments such as Microsoft Azure, it can interface with SQL Server on-premises as well as all major database vendors, and much more. It is possible to build a complete Data Pipeline with a scripting language such as PowerShell, but care should be taken to ensure that security and maintainability are considered when defining a manual Data Pipeline.

You can find a large variety of code examples and libraries in the PowerShell Gallery.

SQL Server Integration Services

One of the most mature tools in the SQL Server ecostructure is SQL Server Integration Services, or SSIS. SSIS can run in an on-premises and in-VM SQL Server, and its packages can also work within the Azure Data Factory tool described next. SSIS is a complete platform for building data integration and data transformations solutions. SSIS can copy or extract files, load data into databases or data warehouses, clean and mine data, and manage SQL Server objects and data.

In SSIS, you define data connections, and tie them together with tasks. You have full visibility into the metadata of SSIS, and it has routing, notification, alerting, retry logic, and rudimentary path logic included.

You can find a complete programming overview of SQL Server Integration Services at this reference.

Microsoft Azure Data Factory

The Microsoft Azure Data Factory is a managed cloud service in Azure that's built for complex hybrid extract-transform-load (ETL), extract-load-transform (ELT), and data integration projects.

Although complex in its implementation, Azure Data Factory (ADF) has a simple set of primitives to learn:

You can learn more about Azure Data Factory at this reference.

Activity: SSIS and ADF Overview

In this Activity you have a choice of reviewing one of two training modules: one for Azure Data Factory, and the other for SQL Server Integration Services. You can bookmark this reference to do both courses on your own schedule later.

Steps

Open this reference on Azure Data Factory and review the Module you see there.
Open this reference on SQL Server Integration Services and review the Lessons you see there.

2.4 Gap Analysis

Gap Analysis is simply comparing your current state with the desired state in your organization. For the Data Estate, you began this process with the first Activity where you detailed some concerns you wanted to address using this Workshop.

After you complete the rest of the Modules in this Workshop, you should return to this section to develop your Gap Analysis using your system audit you created using this Module, and then create a business plan for your modernization effort.

For Further Study

Microsoft Purview Microsoft Learn Path: Govern data across an enterprise
The Microsoft Well-Architected Framework
Azure Data Architecture Guide
Online transaction processing (OLTP)
Non-relational data and NoSQL

Next, continue on to Module 03 - SQL Server Improvements

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Uh oh!