Breaking up with SQL Server

I was inspired to write this after reading a post from Dave Mason regarding breaking up with Big Tech companies. Yet again I haven't wrote for some time, my last post was about the release of SQL Server 2022 which I felt was rather underwhelming and certainly not helped by some of the issues with it.

It also came at a funny time in my career. For anyone who doesn't know me I've worked with SQL Server for many years and from my own perspective have had quite a remarkable career when I think back to my early days dabbling with MS Access. What followed those early days was a long journey developing and honing my skills after completely by accident falling into the wonderful world of SQL Server overnight, and it became my dedication.

But a couple of years ago things changed. At the time I was an avid blogger and occasional presenter who lived and breathed SQL Server as anyone who knows me well enough would testify. Unfortunately there were some things going on in my personal life which meant I had to stop a lot of those activities. I was still working but the blogs and everything else just stopped, and weirdly after a short time I realised that I didn't really miss any of it either. I definitely tried; I wrote a few blog posts here and there, I even put together a couple of new sessions but just couldn't seem to get that spark back to really get back into it.

Which leads me to the breaking up part...and I have to start off by saying, SQL Server...it's not you, it's me!

I know, I rolled my eyes a little too but I'm going to be even more cliché, deep down I do still love [working with] SQL Server, we've been in a relationship for a long time but over time things and people change. There's just something that isn't there any more and to be truthful, it's time I moved on.

OK, that's enough of the technical equivalent of a Dear John letter as I was getting emotional.

The fact is that whilst I'll still be working with SQL for sure I realised I do actually miss the blogging, the speaking and the rest of it, it's just the subject matter that I had grown tired of and so it's time for something a little different.

Over the last couple of years I've been working with some different platforms and technologies; AWS, GCP, PostgreSQL, Python and a whole bunch of other stuff. It's not just me of course, this is how the technical landscape is these days so if anything it was very natural I suppose to diversify my technical knowledge and skill set. But when it comes to really getting into a technology I'm shifting focus specifically towards some of those areas and reigniting what I once had, and it's been a while since I said this, but I can't wait to see how things develop :-)

DP-900 Microsoft Azure Data Fundamentals - Exam Prep

 

As promised in my last post after passing the DP-900 Azure Data Fundamentals exam here's my exam preparation guide which provide links to the various resources I used in studying for the different topics.

DESCRIBE CORE DATA CONCEPTS (15-20%)

Describe types of core data workloads

Describe batch data:

https://www.talend.com/resources/batch-processing/

https://docs.microsoft.com/en-us/azure/architecture/data-guide/big-data/batch-processing

Describe streaming data:

https://hazelcast.com/glossary/stream-processing/

Describe the difference between batch and streaming data:

https://docs.microsoft.com/en-us/learn/modules/explore-core-data-concepts/4-describe-difference

Describe the characteristics of relational data:

https://docs.microsoft.com/en-us/learn/modules/explore-core-data-concepts/3-identify-types-storage

Describe data analytics core concepts

Describe data visualization (e.g., visualization, reporting, business intelligence (BI)):

https://powerbi.microsoft.com/en-us/data-visualization/

Describe basic chart types such as bar charts and pie charts:

https://www.klipfolio.com/resources/articles/what-is-data-visualization

Describe analytics techniques (e.g., descriptive, diagnostic, predictive, prescriptive, cognitive):

https://docs.microsoft.com/en-us/learn/modules/data-analytics-microsoft/2-data-analysis

https://www.weirdgeek.com/2018/11/types-of-analytics/

Describe ELT and ETL processing:

https://www.talend.com/resources/elt-vs-etl/

Describe the concepts of data processing;

https://www.mbaknol.com/management-information-systems/concept-of-data-processing/

DESCRIBE HOW TO WORK WITH RELATION DATA ON AZURE (25-30%)

Describe relational data workloads

Identify the right data offering for a relational workload:

https://docs.microsoft.com/en-us/azure/architecture/guide/technology-choices/data-store-overview

Describe relational data structures (e.g., tables, index, views):

https://web.csulb.edu/colleges/coe/cecs/dbdesign/dbdesign.php?page=sql/views.php

Describe relational Azure data services

Describe and compare PaaS, IaaS, and SaaS delivery models:

https://www.bmc.com/blogs/saas-vs-paas-vs-iaas-whats-the-difference-and-how-to-choose/

https://docs.microsoft.com/en-us/learn/modules/principles-cloud-computing/5-types-of-cloud-services

Describe Azure SQL Database:

https://azure.microsoft.com/en-gb/services/sql-database/

Describe Azure Synapse Analytics:

https://azure.microsoft.com/en-gb/services/synapse-analytics/

https://docs.microsoft.com/en-us/learn/modules/examine-components-of-modern-data-warehouse/3-explore-azure-data-services-warehousing

Describe SQL Server on Azure Virtual Machine

https://docs.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/sql-server-on-azure-vm-iaas-what-is-overview

Describe Azure Database for PostgreSQL, Azure Database for MariaDB, and Azure Database for MySQL:

https://docs.microsoft.com/en-us/learn/modules/explore-relational-data-offerings/6-postgresql-mariadb-mysql

Describe Azure SQL Managed Instance:

https://docs.microsoft.com/en-us/azure/azure-sql/managed-instance/sql-managed-instance-paas-overview

Identify basic management tasks for relational data

Describe provisioning and deployment of relational data services:

https://docs.microsoft.com/en-us/learn/modules/explore-provision-deploy-relational-database-offerings-azure/4-describe-provision-postgresql-mysql

Describe method for deployment including the Azure portal, Azure Resource Manager templates, Azure PowerShell, and the Azure command-line interface (CLI):

https://docs.microsoft.com/en-us/learn/modules/deploy-azure-sql-database/3-deploy-single

Identify data security components (e.g., firewall, authentication):

https://docs.microsoft.com/en-us/learn/modules/explore-provision-deploy-relational-database-offerings-azure/5-configure-relational-data-services

Identify basic connectivity issues (e.g., accessing from on-premises, access with Azure VNets, access from Internet, authentication, firewalls):

https://docs.microsoft.com/en-us/azure/azure-sql/database/troubleshoot-common-errors-issues

https://medium.com/@vmehmeri/troubleshooting-connectivity-in-microsoft-azure-2035c7a43d3f

Identify query tools (e.g., Azure Data Studio, SQL Server Management Studio, sqlcmd utility, etc.):

https://docs.microsoft.com/en-us/sql/azure-data-studio/what-is?view=sql-server-ver15

https://docs.microsoft.com/en-us/sql/ssms/sql-server-management-studio-ssms?view=sql-server-ver15

https://docs.microsoft.com/en-us/sql/ssms/scripting/sqlcmd-use-the-utility?view=sql-server-ver15

https://code.visualstudio.com/

Describe query techniques for data using SQL language

Compare DDL versus DML:

https://docs.microsoft.com/en-us/learn/modules/query-relational-data/2-introduction-to-sql

https://stackoverflow.com/questions/2578194/what-are-ddl-and-dml

Query relational data in PostgreSQL, MySQL, and Azure SQL Database:

https://docs.microsoft.com/en-us/learn/modules/explore-relational-data-offerings/6-postgresql-mariadb-mysql

https://azure.microsoft.com/en-gb/blog/fast-and-easy-dev-with-azure-db-for-mysql-and-azuredb-for-postgresql/?WT.mc_id=DOP-MVP-5003451

DESCRIBE HOW TO WORK WITH NON-RELATIONAL DATA ON AZURE (25-30%)

Describe non-relational data workloads

Describe the characteristics of non-relational data:

https://docs.microsoft.com/en-us/azure/architecture/data-guide/big-data/non-relational-data?WT.mc_id=DOP-MVP-5003451

Describe the types of non-relational and NoSQL data:

https://azure.microsoft.com/en-gb/overview/nosql-database/

Recommend the correct data store:

https://docs.microsoft.com/en-us/azure/architecture/data-guide/technology-choices/data-storage

Determine when to use non-relational data:

https://azure.microsoft.com/en-gb/overview/nosql-database/

https://docs.microsoft.com/en-us/azure/architecture/data-guide/technology-choices/data-storage (capability matrices)

Describe non-relational data offerings on Azure

Identify Azure data services for non-relational workloads:

https://docs.microsoft.com/en-us/learn/modules/explore-non-relational-data-offerings-azure/

https://docs.microsoft.com/en-us/azure/architecture/data-guide/technology-choices/data-storage

Describe Azure Cosmos DB APIs:

https://docs.microsoft.com/en-us/learn/modules/choose-api-for-cosmos-db/

https://nordcloud.com/azure-cosmos-db/

Describe Azure Table storage:

https://docs.microsoft.com/en-us/learn/modules/explore-non-relational-data-offerings-azure/2-explore-azure-table-storage

Describe Azure Blob storage:

https://docs.microsoft.com/en-us/learn/modules/explore-non-relational-data-offerings-azure/3-explore-azure-blob-storage

Describe Azure File storage:

https://docs.microsoft.com/en-us/learn/modules/explore-non-relational-data-offerings-azure/4-explore-azure-file-storage

Identify basic management tasks for non-relational data

Describe provisioning and deployment of non-relational data services:

https://docs.microsoft.com/en-us/learn/modules/explore-provision-deploy-non-relational-data-services-azure/

Describe method for deployment including the Azure portal, Azure Resource Manager templates, Azure PowerShell, and the Azure command-line interface (CLI):

https://docs.microsoft.com/en-us/learn/modules/explore-provision-deploy-non-relational-data-services-azure/

Identify data security components (e.g., firewall, authentication, encryption):

https://docs.microsoft.com/en-us/learn/modules/explore-provision-deploy-non-relational-data-services-azure/5-describe-configure-non-relational-data-services

https://cloudacademy.com/course/configuring-azure-application-and-data-security/configuring-cosmos-db-security/

Identify basic connectivity issues (e.g., accessing from on-premises, access with Azure VNets, access from Internet, authentication, firewalls)

https://docs.microsoft.com/en-us/learn/modules/explore-provision-deploy-non-relational-data-services-azure/5-describe-configure-non-relational-data-services

Identify management tools for non-relational data:

https://docs.microsoft.com/en-us/learn/modules/explore-non-relational-data-stores-azure/

DESCRIBE AN ANALYTICS WORKLOAD ON AZURE (25-30%)

Describe analytics workloads

Describe transactional workloads:

https://docs.microsoft.com/en-us/azure/architecture/data-guide/relational-data/online-transaction-processing

Describe the difference between a transactional and an analytics workload:

https://www.guru99.com/oltp-vs-olap.html

Describe the difference between batch and real time:

https://www.datasciencecentral.com/profiles/blogs/batch-vs-real-time-data-processing

Describe data warehousing workloads:

https://docs.microsoft.com/en-us/learn/modules/explore-data-ingestion-azure/

https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-workload-classification

Determine when a data warehouse solution is needed:

https://docs.microsoft.com/en-us/azure/architecture/data-guide/relational-data/data-warehousing#:~:text=These%20steps%20help%20guide%20users,transaction%20systems%20for%20performance%20reasons.

Describe the components of a modern data warehouse

Describe Azure data services for modern data warehousing such as Azure Data Lake, Azure Synapse Analytics, Azure Databricks, and Azure HDInsight:

https://docs.microsoft.com/en-us/learn/modules/examine-components-of-modern-data-warehouse/3-explore-azure-data-services-warehousing

https://azure.microsoft.com/en-gb/solutions/data-lake/

https://azure.microsoft.com/en-gb/services/databricks/

Describe modern data warehousing architecture and workload:

https://www.jamesserra.com/archive/2014/12/the-modern-data-warehouse/#:~:text=A%20modern%20data%20warehouse%20consists,%2C%20MapReduce%20tools%2C%20and%20HDFS.

https://docs.microsoft.com/en-us/learn/modules/examine-components-of-modern-data-warehouse/

https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-workload-importance

Describe data ingestion and processing on Azure

Describe common practices for data loading:

https://docs.microsoft.com/en-us/learn/modules/explore-data-ingestion-azure/2-describe-common-practices-for-data-loading

https://www.xplenty.com/blog/etl-vs-elt/#:~:text=ETL%20stands%20for%20Extract%2C%20Transform,the%20need%20for%20data%20staging.

Describe the components of Azure Data Factory (e.g., pipeline, activities, etc.):

https://www.cathrinewilhelmsen.net/2019/12/03/overview-azure-data-factory-components/

https://docs.microsoft.com/en-us/learn/paths/data-integration-scale-azure-data-factory/?WT.mc_id=DP-MVP-5001430

Describe data processing options (e.g., HDI, Azure Databricks, Azure Synapse Analytics, Azure Data Factory):

https://docs.microsoft.com/en-us/learn/modules/examine-components-of-modern-data-warehouse/3-explore-azure-data-services-warehousing

Describe data visualization in Microsoft Power BI

Describe the role of paginated reporting:

https://docs.microsoft.com/en-us/power-bi/paginated-reports/paginated-reports-report-builder-power-bi

Describe the role of interactive reports:

https://powerbi.microsoft.com/en-us/desktop/

Describe the role of dashboards:

https://docs.microsoft.com/en-us/power-bi/create-reports/service-dashboards#:~:text=A%20Power%20BI%20dashboard%20is,tells%20a%20story%20through%20visualizations.&text=Dashboards%20are%20a%20feature%20of,available%20in%20Power%20BI%20Desktop.

Describe the workflow in Power BI:

https://docs.microsoft.com/en-us/learn/modules/get-started-with-power-bi/3-building-blocks-of-power-bi

SQL Server 2019, the vision is released.

As you probably know by now Microsoft announced the General Availability release of SQL Server 2019 earlier this week, here's the official announcement. For me it's the most significant release of SQL Server EVER; SQL 2005 was certainly groundbreaking and the 2016 version was amazing, sure, but for me SQL 2019 is the real game changer for Data Platforms.

There's a lot of material and blog posts out there detailing the new functionality and enhancements that come in SQL 2019 (and if you haven't already, you really need to start getting up to speed) but what I wanted to do in this post was look back at a post on the SQL Server blog from back in early 2014 by the then VP of the Data Platform Group at Microsoft, Quentin Clark.

The post is titled 'What Drives Microsoft's Data Platform Vision' and I've used this article quite often when I've been outlining how we should approach the concept of a data platform. It's a very interesting read and whilst it's very focused on the Azure offerings at the time we can see how this new release shows how the data platform vision outlined in this article shaped the future of SQL Server. I'll pull out a few sub-headings and points:

"Data types are diverse", "Examples include the rise of JSON, the embracing of Hadoop by enterprises" 

And (from the 2019 main page), we now have SQL Server fully embracing Hadoop:

"Manage your big data environment more easily with Big Data Clusters. They provide key elements of a data lake - Hadoop Distributed File System (HDFS), Apache Spark..."

Grabbing another snippet:

"Analytics usage is broadening", "Deep analytics and automated techniques, like machine learning, are being used more often"

OK so whilst Machine Learning Services were introduced in SQL Server 2017 the addition of Big Data Clusters coupled with R and Python brings a whole new level of analytical capabilities inside SQL Server.

The 2014 article also mentions the concept of "Modern Transaction Processing", to quote again "data services that modern applications need are broader now than traditional RDBMS". This statement for me sums up databases in general and now SQL Server has evolved from being traditional "place to store data" shall we say to being a true hub for all of your organisations enterprise data, regardless of its form - although PolyBase came along in 2016 (when it could connect to Hadoop) it's potential has grown massively in 2019 enabling external data connections to the likes of Oracle, Teradata and MongoDB.

The 2014 article is a great read and there's so many different points in it that have shaped this release of SQL Server and now that 2019 is released and here, Microsoft's vision for the Data Platform has become a reality. Bearing in mind that figures suggest the last release of SQL Server has a high adoption rate is an encouraging sign that organisations will also embrace the potential of SQL 2019 to fully realise, and extract real value from their own Data Platforms. 

DATA:Scotland 2019. Unleashing the Guillotine!!!

Friday 13th of September will be my first ever appearance at a conference as a speaker at DATA:Scotland. Needless to say I cannot wait and I'd like to thank the organisers, selectors or whoever it was that drew out the short straw that is me.

I'll be presenting my intriguingly titled 'Guillotines, Sat-Nav and the Query Optimiser' session which I presented at the Manchester and Leeds User Groups earlier this year. The user groups have proved to be a really useful experience as some of the questions that were raised from the audience I've taken onboard and added in, basically I've been stealing their ideas.

My session starts at 12 which unfortunately for me is when my daily caffeine intake usually starts to wear off right at the same time I start to get hungry so I might take up a flask of espresso and a couple of wagon wheels to keep me going. I'll be in conference room 7 which also happens to my lucky number, so that's good news.

Something very new to me is that there will be presenters presenting their sessions at the same time as me and as such I need view these SQL superstars as, well, the competition I guess. I'm not entirely sure what tactics to deploy to get people to come and see me instead; media smear campaigns, good old fashioned kidnapping or perhaps just go really heavy on the propaganda...a bit like this blog post.

The truth is I'm very much overwhelmed at seeing my name up there on that schedule surrounded by people who in all honesty I hold in the absolute highest regard, and I also promise not to kidnap anyone (is their an emoji for fingers crossed behind your back?).

So what can people expect? Well at the moment we're in the midst of an amazing shift in the technical landscape. We've got all this funky cool new stuff like AI, the cloud, containers, Kubernetes etc etc etc and obviously I thought not to cover any of that. No, apparently vintage is all the rage these days so I'm jumping right on that bandwagon!

It may be old school but the query optimiser to me is like the magic box of SQL Server. We all throw queries at it and it goes off and does what it does, that's the beauty of it but at the same time we don't often get chance to open the box and have a good root around - and that's where this session comes in. So I'll be covering how the optimiser works, some of the magic tricks that it performs, how we can use SQL to keep a very close eye on what the optimiser is doing and of course how to break it!

It's worth adding that I will also be trying my best to seamlessly link the query optimiser to guillotines and satellite navigation - that'll teach me to come up with a great title before the actual presentation. I say that but I am currently working on a new session called "fixing your availability groups with sticky back plastic", take note Blue Peter (is that even still on?).

As well as the actual learning stuff (which I promise, there's plenty) there's a few ice breakers in there which I'll not give away right now and there's a good sense of humour throughout, which is mostly directed at the expense of myself.

So if you are attending DATA:Scotland all that's left is for me to beg, plead, perhaps even bribe you to come along to my session; if you choose otherwise I'll not hold it against you...and if you're not attending but stumbled on to this post by accident then thanks for reading if you got this far!