From June through September, 3Cloud went from about 70 people to 170. We are now the largest Azure “pure-play” consulting company in the United States. And this is just the beginning…
So, what are my thoughts on this?
I am truly excited about the opportunity to grow a consulting company that is focused on Azure. Sure there is a lot of change, but change is not bad. As we bring our three companies together to become one, there are challenges and successes. All of us have already benefited from the merging of skills and teams to create a more complete solution team for our customers.
I look forward to seeing how we evolve over the next few months and years. Exciting times are ahead!
While working for a data centric company like Pragmatic Works was great, the shift to cloud technologies and Azure data services required us to expand our capabilities beyond data and SQL Server. This merger allows us to immediately add value to our customers by adding application development and infrastructure capabilities to our toolbox. Beyond that, 3Cloud and ACS bring a mature managed services offering including the ability to host and manage customer resources in Azure (CSP).
I think our customers get a significant boost in services as we become a more complete Azure company.
Some Final Thoughts
I will miss working directly with Brian Knight and Tim Moolic, two of the founding partners at Pragmatic Works. Their vision helped shape a great organization over 12 years. In case you did not realize it, Pragmatic Works will continue on as a training organization. You can still expect excellent technical training from the team there. We all will continue to learn and grow with their support.
If you are interested in joining our team or learning more about what 3Cloud offers, reach out to me at email@example.com. I look forward to seeing you on a webinar or working with you in the future.
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 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 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.
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
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.
On August 4, 2020, I presented this on the weekly Pragmatic Works webinar series. You can view that presentation here.
As part of that presentation, I committed to give you access to the databricks notebooks so you can run through this as well. You can find the notebook on my Github. It is stored as a dbc (Databricks notebook) file. You will need Databricks to open the file.
Two questions were asked during the session that I wanted to handle here. The first was related to connecting to relational databases. The short answer is yes. You can use the JDBC driver to work with SQL Server or Snowflake for instance. Details can be found in the data sources article on the Databricks site.
The next question was related to Databricks ability to work with SFTP. While I cannot speak to the specifics, I was able to find the following Spark library that may be able to provide the support you need. To be clear, I have not implemented this myself but wanted to provide a potential resource to help with this implementation. I found this in a Databricks forum and it may work for you: https://github.com/springml/spark-sftp. If one of you finds this useful, feel free to post a comment here for others to refer to.
Thanks again for everyone who was able to attend. We look forward to continuing to work with Databricks more.
Pragmatic Works has done it again. Microsoft has recognized us for our work this year with two finalist awards — Power BI and PowerApps and Power Automate. This recognizes work we have done for our customers with these products.
This follows awards over the last three years for Data Analytics (2019) and Data Platform (2017). I am proud to work for a great company who strives to be industry leaders in data and analytics in the cloud and on-premises. I am truly excited to see where we go from here!
My company, Pragmatic Works, is working through a Dynamics 365 migration. As part of that migration, we need reports. We have made the decision, seemingly obvious given who we are, to use Power BI for all of our internal reporting. Through the process, I have been working with our migration team a lot and have been tasked to handle key reporting for the consulting team. We are implementing both CRM and PSA (Project Service Automation) as part of the rollout. I am responsible for reporting that supports the non-Sales operations for the consulting organization. This series of posts will follow my journey to get a good solution in place. I will give you the resources I used, the advice from the pros on my team, and anything else I can share to help your journey along as well.
I want to caveat that this is my journey through the process. I am sure some mistakes will be made along the way, but we should all learn together. I am not doing this in a vacuum. I have some very talented Power BI team members helping me with strategy and design. Their contributions will be reflected throughout the process, but want to give them credit now for sure.
Evaluating Power BI Dataflows vs Power BI Shared Datasets
I started the process by trying to determine what is the best option for building data models that can be used for ongoing report creation within our company. I was familiar with shared datasets and with the latest improvements in the service, it was a good place to start. However, I have been talking with the team about Power BI Dataflows (and Azure Data Factory Dataflows, but that is not relevant here). I put it out to the group above to discuss pros and cons. Overall, the team pointed out that Dataflows with Dynamics would be the best fit, however, there is not much out there on Dataflows in action.
Brian Knight and I were having a different conversation about the Common Data Model (CDM) and PowerApps for other projects. During that conversation, Brian also mentioned that the CDM was ideal for working with Dynamics data.
Coming full circle, the team agreed that CDM with Dynamics and Dataflows is a good way to go. And so starts the journey. I will be documenting the research and progress along the way here. I will be posting whenever I can. Here we go!