I have been living with ALS for the past two years. I was diagnosed in September 2022 but had my first symptoms and started researching what was going on in July 2021. As I begin to understand my diagnosis more, I realize that this is a progressive disease, and my needs and situation will change over time. In my situation, I lost the use of my arms and hands first and I’ve begun to lose functionality in my legs and torso over the past few months. To learn more about my journey to this point check out this video on YouTube which tells my personal story.
Launching the YouTube Channel
Now that we’ve mentioned YouTube, Data on Wheels is launching its YouTube channel focusing first on my ALS story. While this YouTube channel will support technical content like the Data on Wheels and the Data on Rails blogs, it will also support ALS awareness and accessibility technology.
I realized early on that a blog post was not the best way to share the story or tools that I use while working with ALS. We will have a couple of playlists that are directly related to this part of my story. We will have a playlist called Living with ALS that focuses on my personal journey and various things we’ve learned or experienced in the process. We will also have a playlist that’s called Working with ALS that will focus on the tools I use to keep me working and how I keep working as my disease progresses.
While I wish we could have started this much earlier, the disease itself has slowed my ability to complete some of these tasks as quickly as I wished. It is with a lot of help from my family who have supported me through video editing, content support, and motivation that we’ve been able to get this launched. I hope you enjoy it for what it is, and I look forward to having a number of you follow and learn more about accessibility.
Global Accessibility Awareness Day 2023
I had the opportunity to work with the Voice Access team from Microsoft on a story video that was used on Global Accessibility Awareness Day, May 19, 2023. I am truly impressed with the work Microsoft has done with Windows and Office to create more accessible experiences for those of us who need help doing our day-to-day job. When I think about how Voice Access has truly helped me be productive in my day-to-day work needs such as IMs in Teams, it was a pleasure to work with them to do a video to promote this tool. You can check out the video in the Voice Access area here. You can also learn more about the other accessibility options available in Windows 11.
Supporting the Cause -ALS Walk Lexington, August 2023
As part of the effort we’re doing around ALS Awareness this month, Data on Wheels is the team name for our team that will be doing the ALS Walk in Lexington KY this year. This is an opportunity for us to do our part to help the local community continue to do great things for the ALS community. Check out our team site and support if you can. Every little bit counts. If you’re in the area and want to join us for the short walk that happens in August feel free to sign up on the team.
3Cloud – Inclusive and Supportive Workplace
I was employed at 3Cloud when my disability first surfaced. As you can imagine, it was an emotional roller coaster and I really needed to determine how much it would affect my ability to do work for the company. As you have seen above or in one of my stories, this disease first affected my hands and arms and my ability to type. Without knowing the speed or rate of the disease and the impact to my current workload, I was able to work with my bosses to find a good place for me in the company which allowed me to contribute while I sorted this out. With their help, I have been able to find a place to continue to contribute and participate in the technology that I love to work with. For example, just this past week, Microsoft released a new product called Microsoft Fabric that I’ve been working with them and our team on to understand how to best work with it in our field. The company has trusted me with this and other initiatives that have allowed me to continue to be productive and generally contribute to the growth and success of 3Cloud.
Wrapping It Up
ALS has changed a lot about how I work and how I view life. I hope that what I share about what I learn helps others in similar situations. I will continue to be as active as I can both through work and in the community. Each day is a new day. Some days bring good things and some days bring bad, but God is in control of it all. Thank you for your continued support!
This blog is intended to be a follow up from the SQL Saturday 2022 in Oregon & SW Washington. In that session I presented an introduction to FHIR and JSON data produced from the Azure Health Services API’s.
With the recent updated mandates in the healthcare environment in the United States, Microsoft has continued to expand its capability to support the FHIR standard for integrating healthcare data. While the standard is well documented and Microsoft’s capabilities are expansive, it falls on data professionals to interpret that data and build meaningful reports and produce meaningful insights from the data as it is collected and integrated across environments. This requires a good working knowledge of JSON in SQL to manipulate complex data models. In the session, we did a short review of the FHIR standard and the overall implementation of FHIR in Azure. From there we reviewed the resulting data in the data lake and in Synapse. That was followed up with an overview into the heart of complex SQL using JSON functions in Synapse. Whether or not you are active in healthcare today, this will be an enlightening session on how to use JSON SQL functions within the Azure SQL platforms.
What is FHIR and why should you care?
FHIR stands for Fast Healthcare Interoperability Resources. this is the latest specification for interoperability in healthcare produced by HL7. To be clear the word fast has nothing to do with performance, but more about the ability to implement and integrate data quickly. With the latest regulations around the world in health care, this standard is the established standard for integrating healthcare data and we’ll continue to be on the forefront of this work. If you do any work in health care, you will need to understand FHIR because you will likely run across data formatted to the standard from many different sources.
FHIR is very well documented. In many ways when the standard is properly followed the JSON documents or other supported formats are effectively self-documenting. It is commonly understood that the core FHIR specification handles about 80% of the use cases in healthcare. It is designed to be flexible so that it can support specialized needs within regions or healthcare areas. For example, in the US there is a need to support race and ethnicity. The U.S. Core Implementation Guide provides guidance on the specification enhancements to support this need for U.S. healthcare organizations. You will find similar support for other countries as well as specific implementations for healthcare vendors such as Epic.
Neither the notebook, the presentation, or this blog is expected to be and exhaustive coverage of FHIR. before we move on to some of the other implementation pieces, it is important to understand one key aspect of FHIR is the basic building block called a resource. A resource is the core exchangeable content within the specification. All resources share the following characteristics:
A common way to define and represent the resource including data types and patterns
A common set of metadata which can be discovered easily
A human readable part
For more detailed information on the supported resources and other details around FHIR implementation, you should visit the following website:
Azure Health Services and the FHIR API
I will not be digging into a lot of the health care services information nor the FHIR support within Azure in this post. The important things to understand is that Microsoft has made a concerted effort to support this specification which includes technology and architectures for the extraction of data from various healthcare systems which will then use the FHIR APIs to standardize that extracted data into the FHIR spec typically in JSON files in the data lake. Because of the standardized format, Microsoft is able to supply a set of common schemas that can be used in serverless synapse to create external tables and views to accelerate the implementation and usage of data produced from the APIs. It is from this starting point that we are able to start working with the data in reporting and analytics solutions.
At this point I want to put a plug in for the company I work for. If you're interested in learning how Azure health services and the FHIR specification can be implemented at your company, we have FHIR Quick Start and FHIR Data Blueprint solutions. These solutions have been used by many other customers to achieve high levels of integration in their health care data estate. If you're interested in learning more, please reach out to us at: https://3cloudsolutions.com/get-started/
Working with the data from the FHIR API using JSON in SQL
As noted in the previous section, Azure Health Services comes with setup serverless tables and views to be used with the extracted data. However due to the complexity of FHIR, there are a number of columns within those tables and views which still contain JSON snippets. For example, there is one field for name which has several objects and arrays to support the specification. You cannot simply select the name from the table and use that as you move forward. There are many different fields like this throughout the data. For the rest of this blog and in the notebook, we will work through a number of scenarios to build a view of the patient resource that can be used for simple reporting. This view will contain a few JSON functions from SQL Server and solve simple to complex scenarios in the illustration.
The functions we will be using:
ISJSON
JSON_VALUE
OPENJSON
In addition to these functions, we will also be using the CROSS APPLY operator in SQL to join our data with relational data.
The examples in the notebook are built on the tables resulting from working with the Azure FHIR API. I am unable to provide a sample of the data to use with the set of information in the notebook currently. However, the SQL will work if you have your own FHIR implementation and a Patient resource to work with. rather than rewrite the entire contents of the notebook in the blog post, here is a link to the notebook.
If you plan to implement this in the same way, you will need Azure Data Lake, Azure Synapse serverless, and Azure Data Studio. the notebook can be opened in Azure Data Studio. If you are unfamiliar with working with notebooks inside of Azure Data Studio, you are not alone. Check out this post which discusses how to implement your first notebook in Azure Data Studio.
Building our view and SQL with JSON functions
If you decide not to open the notebook but are curious what the view looks like here is a finished product that we created in the notebook.
SELECT TOP (20) p.resourceType + '/' + p.id as PatientResourceID
, p.resourceType as ResourceType
, p.id as ResourceID
, cast(p.[meta.versionId] as int) as VersionID
, cast(p.[meta.lastUpdated] as DATETIME2(7)) as LastUpdated
, JSON_VALUE(p.[name], '$[0].family') as LastName
, JSON_VALUE(p.[name], '$[0].given[0]') as FirstName
, cast(p.active as bit) as IsActive
, p.gender as Gender
, CAST(p.birthDate as date) as BirthDate
, CASE WHEN p.[maritalStatus.coding] is null THEN NULL
WHEN JSON_VALUE(p.[maritalStatus.coding], '$[0].system') = 'http://terminology.hl7.org/CodeSystem/v3-MaritalStatus'
THEN JSON_VALUE(p.[maritalStatus.coding], '$[0].code')
ELSE NULL
END as MaritalStatus
, CASE WHEN JSON_VALUE(p.[address], '$[0].use') = 'home' THEN JSON_VALUE(p.[address], '$[0].state')
WHEN JSON_VALUE(p.[address], '$[1].use') = 'home' THEN JSON_VALUE(p.[address], '$[1].state')
WHEN JSON_VALUE(p.[address], '$[2].use') = 'home' THEN JSON_VALUE(p.[address], '$[2].state')
WHEN JSON_VALUE(p.[address], '$[3].use') = 'home' THEN JSON_VALUE(p.[address], '$[3].state')
ELSE NULL
END as HomeStateOrProvince
, e.Ethnicity
, r.Race
FROM fhir.Patient p
INNER JOIN (SELECT id, max([meta.versionId]) as currentVersion FROM fhir.Patient GROUP BY id) cp
ON p.[meta.versionId] = cp.currentVersion
AND p.id = cp.id
LEFT JOIN
(SELECT p.id
, CASE WHEN JSON_VALUE(ext.value,'$.extension[0].url') = 'ombCategory'
THEN
CASE WHEN JSON_VALUE(ext.value, '$.extension[1].valueString') IS NOT NULL THEN JSON_VALUE(ext.value, '$.extension[1].valueString')
WHEN JSON_VALUE(ext.value, '$.extension[0].valueString') IS NOT NULL THEN JSON_VALUE(ext.value, '$.extension[0].valueString')
ELSE JSON_VALUE(ext.value, '$.extension[0].valueCoding.display')
END
ELSE JSON_VALUE(ext.value, '$.valueCodeableConcept.coding[0].display')
END AS Ethnicity
FROM
(
SELECT fp.id, fp.extension FROM fhir.Patient fp
INNER JOIN (SELECT id, max([meta.versionId]) as currentVersion FROM fhir.Patient GROUP BY id) cp
ON fp.[meta.versionId] = cp.currentVersion
AND fp.id = cp.id
WHERE ISJSON(fp.extension) =1
) p
CROSS APPLY
OPENJSON(p.extension,'$'
) as ext
WHERE JSON_VALUE(ext.value,'$.url') = 'http://hl7.org/fhir/us/core/StructureDefinition/us-core-ethnicity'
) e on e.id = p.id
LEFT JOIN
(SELECT p.id
, CASE WHEN JSON_VALUE(ext.value,'$.extension[0].url') = 'ombCategory'
THEN
CASE WHEN JSON_VALUE(ext.value, '$.extension[3].valueString') IS NOT NULL THEN JSON_VALUE(ext.value, '$.extension[3].valueString')
WHEN JSON_VALUE(ext.value, '$.extension[2].valueString') IS NOT NULL THEN JSON_VALUE(ext.value, '$.extension[2].valueString')
WHEN JSON_VALUE(ext.value, '$.extension[1].valueString') IS NOT NULL THEN JSON_VALUE(ext.value, '$.extension[1].valueString')
WHEN JSON_VALUE(ext.value, '$.extension[0].valueString') IS NOT NULL THEN JSON_VALUE(ext.value, '$.extension[0].valueString')
ELSE JSON_VALUE(ext.value, '$.extension[0].valueCoding.display')
END
ELSE JSON_VALUE(ext.value, '$.valueCodeableConcept.coding[0].display')
END AS Race
FROM
(
SELECT fp.id, fp.extension FROM fhir.Patient fp
INNER JOIN (SELECT id, max([meta.versionId]) as currentVersion FROM fhir.Patient GROUP BY id) cp
ON fp.[meta.versionId] = cp.currentVersion
AND fp.id = cp.id
WHERE ISJSON(fp.extension) =1
) p
CROSS APPLY
OPENJSON(p.extension,'$'
) as ext
WHERE JSON_VALUE(ext.value,'$.url') = 'http://hl7.org/fhir/us/core/StructureDefinition/us-core-race'
) as r on r.id = p.id
Here is a sample of the results from that view:
PatientResourceID
ResourceType
ResourceID
VersionID
LastUpdated
LastName
FirstName
IsActive
Gender
BirthDate
MaritalStatus
HomeStateOrProvince
Ethnicity
Race
Patient/d8af7bfa-5008-4a0f-85d1-0af3448a31dd
Patient
d8af7bfa-5008-4a0f-85d1-0af3448a31dd
2
2022-05-31 18:07:03.2150000
DUCK
DONALD
1
male
1965-07-14
NULL
ON
NULL
NULL
Patient/78cf7725-a0e1-44a4-94d4-055482781afb
Patient
78cf7725-a0e1-44a4-94d4-055482781afb
1
2022-05-31 18:07:30.7490000
Gretzky
Wayne
NULL
NULL
1990-05-31
NULL
NULL
NULL
NULL
Patient/9e909e52-61a1-be50-1878-a12ef8c36346
Patient
9e909e52-61a1-be50-1878-a12ef8c36346
4
2022-05-31 18:39:58.1780000
EVERYMAN
ADAM
NULL
male
1988-08-18
M
NULL
Non Hispanic or Latino
White+Asian
Patient/585f3cc0-c727-4989-9214-a7a7b60a2ade
Patient
585f3cc0-c727-4989-9214-a7a7b60a2ade
1
2022-05-31 13:14:57.0640000
DUCK
DONALD
1
male
1965-07-15
NULL
ON
NULL
NULL
Patient/29a819c4-f553-8189-2354-9441b86d37ef
Patient
29a819c4-f553-8189-2354-9441b86d37ef
1
2022-05-18 15:18:40.1560000
FORD
ELAINE
NULL
female
1992-03-10
NULL
NULL
NULL
NULL
Patient/d5fe6802-a680-e762-8f43-9659340b00ac
Patient
d5fe6802-a680-e762-8f43-9659340b00ac
3
2022-05-18 14:39:52.2550000
EVERYMAN
ADAM
NULL
male
1961-06-15
S
NULL
NULL
C
Patient/4d661053-a8d0-148c-7023-54508fd04a52
Patient
4d661053-a8d0-148c-7023-54508fd04a52
1
2022-05-21 13:48:24.9720000
EVERYMAN
sam
NULL
male
1966-05-07
M
NULL
Not Hispanic or Latino
White
Wrapping it up
As you can see, understanding the specification well enough to build a complex SQL statement using JSON functions is required to work within FHIR effectively. Due to the complex nature of the nested JSON, you may not be able to reconcile this in tools such as power BI. Being able to build this out in SQL guarantees that you have provided you will report writers and analysts with a solid result set which can be used with confidence.
The step-by-step process below walks through connecting to data housed in Azure Blob Storage from Power BI using a SAS token. There are many ways to grab your data from Blob Storage, but this is the most efficient, scalable, and secure way that I found (with some security restrictions from watchful DBAs).
Resources Needed:
Base URL for container
SAS Token (must have read AND list permissions)
Check out the link in resources for a tutorial on generating SAS Tokens.
File Path (should end with .csv)
Power BI Desktop
Notes:
You can skip ahead to the sample M script if you have all your elements. Simply swap out the BaseURL, SASToken, and FilePath and you’re good to go. Otherwise, feel free to walk through the steps below to gain a deeper understanding of the process.
Make sure your Base URL ends with a “/”, your SAS Token starts with “?”, and your file path ends with “.csv”
Keep the double quotes around each parameter value, this forces Power BI to recognize it as text.
Process:
In Power BI Desktop, go to Get Data and select the Web option.
Switch to the advanced view and put the base URL in the first box.
Put in the second box the SAS token.
In a third box (click add part to get the third one), put “&restype=container&comp=list” (this will allow you to list all the blobs in that container).
Expand the blob down then filter the name on the file path.
Create a custom column to create the entire URL for the file (M code samples are below).
FileURL = BaseURL & [Name] & SASToken
Create another custom column to access the web contents of your FileURL column.
BinaryURLContents = Web.Contents([FileURL])
Remove all columns except the BinaryURLContents.
Click on the “Binary” value and watch Power BI expand out your CSV file.
Manipulate data from there as needed.
Final M Code:
let
BaseURL = "BASE_URL_HERE"
,SASToken = "SAS_TOKEN_HERE"
,FilePath = "FILE_NAME_HERE_(Note do not include section of the URL from Base URL)"
,Source = Xml.Tables(Web.Contents(Text.From(BaseURL) &Text.From(SASToken) & "&restype=container&comp=list")),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Attribute:ServiceEndpoint", type text}, {"Attribute:ContainerName", type text}}),
#"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Blobs"}),
#"Expanded Blobs" = Table.ExpandTableColumn(#"Removed Other Columns", "Blobs", {"Blob"}, {"Blob"}),
#"Expanded Blob" = Table.ExpandTableColumn(#"Expanded Blobs", "Blob", {"Name", "Properties", "OrMetadata"}, {"Name", "Properties", "OrMetadata"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Blob", each ([Name] = Text.From(FilePath))),
#"Added Custom" = Table.AddColumn(#"Filtered Rows", "FileURL", each BaseURL & [Name] & SASToken),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "BinaryURLContents", each Web.Contents([FileURL])),
#"Removed Other Columns1" = Table.SelectColumns(#"Added Custom1",{"BinaryURLContents"}),
BinaryURLContents = #"Removed Other Columns1"{0}[BinaryURLContents],
#"Imported CSV" = Csv.Document(BinaryURLContents,[Delimiter=",", Columns=24, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(#"Imported CSV", [PromoteAllScalars=true])
in
#"Promoted Headers"
//Use this query to validate your file path
let
Source = Xml.Tables(Web.Contents("BASE URL" & "SAS TOKEN" & "&restype=container&comp=list")),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"NextMarker", type text}, {"Attribute:ServiceEndpoint", type text}, {"Attribute:ContainerName", type text}}),
#"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Blobs"}),
#"Expanded Blobs" = Table.ExpandTableColumn(#"Removed Other Columns", "Blobs", {"Blob"}, {"Blob"}),
#"Expanded Blob" = Table.ExpandTableColumn(#"Expanded Blobs", "Blob", {"Name", "Properties", "OrMetadata"}, {"Name", "Properties", "OrMetadata"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Blob", each [Name] = "FILE PATH")
in
#"Filtered Rows"
One of the tasks, we often do with migration projects is move large volumes of data. Depending on how you are configured, you may need to do the migration project in a development or UAT environment as opposed to a production environment. This is particularly true if you have policies in place on your production subscription that don’t allow the individuals doing the migration and validation tasks to work in that subscription.
Just Copy It… Nope
So you can copy Azure SQL Database using the Azure Portal, PowerShell, Azure CLI, and T-SQL. However, this functionality is limited to Azure SQL Database and does not work for Azure Synapse databases (a.k.a. SQL Pools). Early in 2021, the ability to use the copy functionality to copy databases between subscriptions is also supported but requires security work to make sure the permissions in the database servers and networking allow that to happen.
You can find out more about copying Azure SQL Database in this Microsoft Doc.
Just Restore It… Nope
You can restore to the current server or another server on the same subscription. However, you are unable to restore across subscription boundaries at this time. If you need to move to another server in the current subscription, the process is straightforward, you can use the restore process in Synapse to restore to the current server using a different name. You can also restore to a different server in either the current or different resource group in the same subscription. The restore technique is used in our move process, so details on how to restore a Synapse database will be in the next section.
Let’s Move a Synapse Database
The process to move a Synapse database to another subscription requires some planning and pre-work. The first thing you need to do is create a new SQL Server in the same subscription you have your current Synapse environment. Because you can’t simply create servers, I would recommend that you add an Azure SQL Database to the server as a placeholder. An S0 should be sufficient to keep this server in place for what we are doing. DO NOT ADD anything to this server that will not be migrated. This is a temporary holding place for migrating databases. (This also works for other SQL Databases, but other options may work as well but are not the focus of this post.)
Now that you have the migration server created, the next step is to create a restore point. While this is not required because you can use the automatic restore points, creating a user-defined restore point is recommended. A user-defined restore point, allows you to choose the status of the database you want to migrate, rather than relying on the automatic points and trying to make sure you pick the right time (in UTC of course).
Once you have set the restore point, in the database you want to migrate, select Restore to open the panel to restore your database.
On the restore page, you have a number of options to complete.
Restore point type: Choose User-defined restore points
SQL pool name: This is not a big deal. The name is the database name used during the migration process and is not the final name used in the target server. Make sure it is something you know.
Restore point: Select the restore point you created for this purpose.
Server: Choose the migration server you created as the target.
Performance level: This one is more interesting. I typically choose a smaller performance level for this restore. Keep in mind that Azure needs to allocate resources to support the restore. Because this is not a final deployment, smaller may go faster. However, NO SLAs exist for this process. That means your mileage will vary. We have seen restores happen in 30 minutes one day and over 5 hours the next. It will be very dependent on the data center and how busy it is. This time variation must be accounted for in your planning.
The next step is to move the server using the Move operation on the server page. You have the option to move to another resource group or another subscription. In our case, we will choose another subscription. IMPORTANT: You will need Contributor permissions in the target subscription in order to move the server to that subscription.
After you have moved the server to the target subscription, you need to set a restore point for that database on the migration server. Then you can restore that database to the target server. It is very important that you use the naming convention and performance levels that you need for this restore as it is the final step in the process. Once again the restore process has no Microsoft SLA and as a result may take longer than planned. You need to have contingencies in place if you are working in a deployment window or have time restrictions.
Finally, you need to clean up the migration server. I would recommend either scaling down or pausing the Synapse database to give you a backup for a while if needed. Once the database is validated on the target server, you can remove the Synapse database (removes storage costs). I would recommend keeping this server as your migration server to use in the future. You can use this process to create copies of databases for development and UAT or similar needs from production instances.
Other Thoughts and Considerations
Here are my final thoughts on this process. First, the fact that no SLA on the restore process is provided by Microsoft has created issues for us in some cases. We have had to extend deployment windows during production deployments on occasion. My recommendation is to plan for the worst case and finish early if all comes together on time.
This process works! You can use it with other SQL assets and you can use it in multiple directions. Keep the migration server around so you can support other processes. If you clean most of it out, the cost of maintaining it is the S0 SQL Database.
One final thought, this is Azure. Thus, this guidance could change tomorrow. We have been using this for about 12 months when this was written. I hope this helps some of you move these databases to support your business and development needs.
This blog covers the content and points to the code used to create the demos in my Azure SQL Database Elasticity presentations. As of today, I have presented this at the Minnesota SQL Server User Group (PASSMN) in September 2020 and as a webinar for 3Cloud in October 2020.
Elastic Queries
Elastic queries allow developers to interact with data from multiple databases supported on the Azure SQL database platform including Synapse. Elastic queries are often referred to as Polybase which is currently implemented in SQL Server 2019 and Azure Synapse. The key difference is that elastic queries only allow you to interact with other Azure SQL Databases but not Hadoop or other database implementations (e.g. Teradata or Oracle). Part of the confusion comes from the fact that the implementation looks very similar. Both toolsets use external tables in SQL Server to interact with the connected data sources. However, Polybase requires additional components to run whereas elastic queries are ready to go without additional setup.
Be aware elastic queries are still in preview. Also, elastic queries are included in the cost of Azure SQL Database in standard and premium tiers.
Elastic Query Strategies
Elastic queries support three key concepts and will influence how you implement the feature.
Vertical partitioning. This concept uses complete tables in separate databases. It could be a shared date table or dimensions in a data warehouse solution. Vertical partitioning is a method to scale out data solutions. This is one method to use Azure SQL database for larger data solutions.
Horizontal partitioning or sharding. Whereas vertical partitioning keeps tables together, horizontal partitioning shards or spreads the data from a single table across multiple Azure SQL Databases. This is the most complex type of partitioning as it requires a shard map. This is typically implemented with .NET or Java applications.
Data virtualization. This concept is a mix of the partitioning solutions to achieve the goal of virtualizing the data. The idea with data virtualization is that we can use a single Azure SQL Database to interact with data from multiple databases. While this concept is limited due to the limit to use Azure SQL Databases, it is a concept to look for more improvements as the product matures even more.
Elastic Query Demo
The demo used in the presentations is configured as shown here:
Three S1 Azure SQL Databases on the same Azure SQL Server. I used ADF (Azure Data Factory) to move Fact.Purchase to WideWorldDW_2 and the three related dimensions (dimDate, dimStockItem, dimSupplier) to WideWorldDW_3. I then used WideWorldDW_3 to implement the external tables to work with the data. The WideWorldImportersDW-Standard was used as the original restore of the sample database. It is the source of the data but is not used in the demos.
One note on the demo. I did not include the ADF jobs. Use the Copy activity to move the tables to the target databases. You can find more information here.
The demo code to set up the environment can be found here.
Elastic Jobs
Elastic jobs is the alternative to SQL Server Agent Jobs in Azure SQL Database. While Agent is included in Azure SQL Managed Instance, the rest of the platform needed an option to create jobs. Elastic jobs solves that issue. Currently this is also in preview and is also included with Azure SQL Database. The only additional cost is that a dedicated job database is required to support elastic jobs.
The best comparison is still with SQL Server Agent. Elastic jobs are structured with jobs which have job steps. The only limitation at the moment is that job steps must be T-SQL. Jobs can be created in the Azure portal, with PowerShell, with REST, or with T-SQL.
Elastic Transactions
One of the key pieces that was originally missing from the Azure SQL Database rollout was cross database transactions that were supported in SQL Server with MSDTC. Elastic transactions add this functionality to Azure SQL Database and is built into the platform. This functionality is application driven and currently supported in the latest .NET libraries. Overall, this will allow you to support transactions across 100 databases or fewer. While there is no limit, Microsoft currently recommends only using this to support distributed transactions over 100 or less databases due to potential performance issues.
There are a few limitations to be aware of:
Only supports Azure SQL Databases
Only supports .NET transactions
Does not support T-SQL Distributed transactions
Does not support WCF transactions
Wrap Up
Microsoft continues to improve the functionality in Azure SQL Database. These elastic features are part of that process. While I typically do not have many uses for distributed transactions, we have actively implemented elastic queries and elastic jobs for customers and look to use them more in the future.
Data on Wheels - Kristyna Ferris & Steve Hughes 