T-SQL Tuesday 131 - Data Analogies, or: Explain Databases Like I’m Five!

I have a habit of coming up with intriguing titles for sessions before I actually come up with any content. Last year I came up with Guillotines, Sat-Nav and the Query Optimiser and then had the challenge of tying it all together somehow. This months T-SQL Tuesday topic (#131) comes by the way of it's rather fantastic host Rob Volk and it gives me a great opportunity to describe the session because...

This month we’ll explore analogies, or how would you explain database concepts to someone who’s not technologically savvy.

Guillotines = execution but what I did find out whilst I was conducting minutes of research in Wikipedia was that the guillotine was put together by a dream team of a doctor and harpsichord maker to provide a much quicker (and more humane) execution compared to a big old axe. In terms of SQL Server that is exactly what the query optimiser is designed to do, give the most optimal execution (and I suppose in a way much more humane too).

The optimiser does this by using all kinds of magic to come up with an execution plan. What's an execution plan I hear you shout? If you haven't shouted it then please do it as loud as possible, you'll feel great. Well, with a bit of imagination it's a bit like Satellite Navigation...which helps massively for the session title.

We don't tell the query optimiser how to do things. We ask it very nicely for the results we want and because we've asked nicely it determines what is probably the best way of doing it in the form of an execution plan (which it shoves over to the execution engine) and most of the time it does a splendid job too.

This plan is just like having a set of directions and basically it will tell the execution engine exactly what to do. It's just like when we ask sat-nav how to get from A to B via C avoiding toll roads (which is always, I'm from Yorkshire) the execution plan contains the instructions for things like how to join this and that table to whatever table etc etc.

There's a whole load of phases and stages and other stuff to explain but for now, execution plan = directions. I'll stick with the theme (because now I'm on a roll) to describe trivial plan optimisation. You wouldn't use sat-nav to give someone directions to the top of your street would you? No. It's too much hassle and also it's blinking obvious. Well the optimiser won't bother with the hassle of full optimisation if there's one straightforward obvious way of executing your query either and will use a trivial plan instead. It saves effort, a bit like my kids when I ask them to do ANYTHING.

Want more analogies? Sure. If you're like me you'll occasionally think that you know better than satellite navigation and take the directions into your own hands. This might work now and again but most of the time it just takes longer. Query hints. This is exactly what is going on here, you're overruling sat-nav and using the B1263 country road because it was faster last month only to get stuck behind a tractor.

One more? Go on then. Speaking of sat-nav; have you ever noticed sat-nav not telling you to use the brand new bypass that cuts right through the middle of the nearest picturesque village? That's because if the maps aren't up to date it doesn't know the road is there. In a much similar way this is why you should keep SQL's statistics up to date, having up to date information helps inform the optimiser to make better decisions when coming up with optimal directions.  

Make sense?

OK I'm seriously out of ideas now but as if by incredible timing I have just recently published a rehearsal recording of my guillotines session on my brand spanking new YouTube channel. Here you see checking word order is very important; my brand new spanking channel could have been something rather embarrassing.

Anyway, I've got sidetracked, here's the link to a rehearsal of Guillotines, Sat-Nav and the Query Optimiser if you'd like to know more about guillotines, because you really should, there's even a quiz! Oh and there's some other optimiser stuff too.

Thanks for reading!

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

Passed! DP-900 Microsoft Azure Data Fundamentals - Overview

Today I passed the Microsoft Azure Data Fundamentals exam (DP-900) and this post is going to be a quick overview of how I prepared, links to some of the training material I used and general thoughts. I'll publish a more comprehensive exam prep guide very soon.

As the name clearly states this is a fundamentals exam and whilst I'd say that the exam content isn't particularly deep at a technical level it does cover a broad range of content so without a doubt it definitely requires putting some work in.

To start, here's the official link for the exam page. This page will highlight any upcoming changes to the exam material (currently scheduled 22/10), you can schedule the actual exam here and most importantly for the purpose of studying see the skills measured and download the exam outline.

The skills measured show the percentage breakdown of exam topic material; at the time of writing it is as follows:

• Describe core data concepts (15-20%)
• Describe how to work with relational data on Azure (25-30%)
• Describe how to work with non-relational data on Azure (25-30%)
• Describe an analytics workload on Azure (25-30%)

Already we can see a split between concept based questions and then workload based which is broken down between relational and non-relational data in Azure as well Analytics. The exam outline link gives us much more information on what data topics are covered so definitely have a read through. Very important, the link allow highlights forthcoming changes so make sure you check those if you're planning on taking the exam after the changes are implemented.

Going back to the exam page under the skills measured we can see the exam preparation options; either instructor led (not covered) or online via the Microsoft Learn platform. Directly from this course page we have [completely free] learning material broken into four learning paths for each of the Azure Data Fundamentals skills areas. 

Each learning path is further broken down into a number of modules of material which will be text based content explaining concepts, some videos and perhaps the most useful feature of Microsoft Learn, hands-on exercises aka learn by doing, which make use of a Microsoft Azure sandbox so you can really get stuck into actually using the different solutions. At the end of each module you get a knowledge check which is a couple of multiple questions before a summary.

For the exam, the exercises definitely need completed so you can understand how things are setup and how things work. In addition, I seriously recommend the further official documentation material that is linked to on each module summary. Although it's a fundamentals exam, it definitely helps to understand some of the detail. 

I do need to give two shout outs; Gregor Suttie for his exam prep guide for DP-900 and John Savill for his exam prep whiteboard video. Right now my normal training providers don't have courses running for DP-900 so I've also been using material from the DP200 and DP201 exams which I will doing next, of course they're a little deeper.

So there's a whole bunch of links to get you started if you're looking at taking the DP-900 Azure Data Fundamentals exam. Overall it was pretty straightforward, I got a really good score which I am delighted with and I will post up a much more comprehensive study guide pretty soon.

Thanks for reading and good luck!

Microsoft Role-Based Certification

Last week Microsoft announced the retirement of the MCSA, MCSD and MCSE certifications (June this year) and followed that up with an announcement that there will be no more dedicated certification for Windows or SQL Server 2019 (see the Q&A at the bottom of this page).

I was initially surprised, SQL Server 2019 is what I would consider a major release and it seemed odd not to make certification for it available in the future. So what's the alternative? 

Step forward role-based certification.

Role-based certification isn't actually new, Microsoft first started focusing on this in September 2018 only now it's going to be pretty much the only route available, and I totally get it.  Yes, it sucks for those currently studying towards certification that will cease to exist in under four months, that to me seems really harsh and I can see why people are frustrated, 100%.

But for a long time I've been an advocate of people focusing their learning on the cloud. Until recently I hadn't taken an exam since the SQL Server 2005 MCTS but have since passed the Azure Fundamentals exam and I've seen lots of other people doing the same. To me it makes perfect sense; lots of businesses have either moved, are in the process of, or certainly thinking about moving to the cloud; in IT - you have to be part of that.

Role-based certification is also a great model for those new to IT. I frequently get asked how to get started in an IT career and I think role-based certification helps people visualise career paths and map out relevant certification. 100% certs don't guarantee jobs but they most definitely help to open doors.

It's all too easy for those of us who have been in a career for a long time to devalue certification and whilst I understand where that view comes from, when looking at it from the point of view of someone starting out, I'd say they're massively important, vital even.

In April 2020 the new Azure Database Administrator Associate certification starts and whilst SQL Server 2019 content will be part of this exam the title of the exam shows quite clearly what the priority is, but here's my concern. Things move really fast in the cloud, a lot faster than on premise and certainly a heck of a lot faster than exams, so can the new certification stay completely up to date be totally relevant to the roles they are based on? 

Whilst I'm surprised dedicated SQL Server 2019 certification has been binned I do like the new direction. I know the official blurb says the role based exams suit seasoned professionals too (and I'm sure they do to some degree) the biggest benefit is for those starting out on a career and for those people, I think it's a great move. 

What I've been reading.

SQL Server 2019 Revealed from Bob Ward is my most current read, here's the Apress link which allows you to view a short preview of each chapter as well as directly downloading all of the source code for the book.

Anyone who uses SQL Server should be reading this. SQL Server 2019 is a very different release and there's a lot of new technology to understand but what I found great about this book is that it also explains the "why" behind the evolution of SQL Server into being a more complete Data Platform. 

The book introduces Big Data Clusters which includes a big technical stack including Apache Spark, Hadoop, Containers, Linux and the current top trend, Kubernetes. Naturally the book also includes all of the feature enhancements made inside the SQL 2019 engine and throughout is packed with example and it's nice to see Azure Data Studio feature in those too.

As said, if you're working with SQL Server you need this book, it's as simple as that!

If you haven't read it already then I can also recommend Pro SQL Server on Linux by Bob Ward (again), here's the Apress link (which again features previews). This book is a bit older and focuses on the 2017 release of SQL Server which was the first release to support Linux. It's a much heavier read (as in literally) and it does cover a lot of SQL Server functionality that seasoned professionals may be already aware of. 

That said the book is concise, again full of examples and introduces a lot of Linux code too that depending on background you might not be familair with. Linux is way more than just an option for SQL Server, it's a fundamental piece of the Data Platform vision so I would personally view this book as a necessity. In addition to this book I also purchased a copy of the Linux Bible which has been recommended by a good friend of mine, the copy I have dates back to 2015 and I've just seen that a newer edition is scheduled for release in June this year.

These two books perfectly demonstrate how the scope of SQL Server is embracing different technologies, none more so than Containers and Kubernetes, both of which (in my opinion) Microsoft are betting big on. With that in mind I'd recommend a couple of books from Nigel Poulton, Docker Deep Dive and The Kubernetes Book both of which I referred to numerous times since originally reading them. The Kubernetes book is more of an introductory read and covers many examples to get you going. As the name suggests, Docker Deep Dive is much more of a technical read but a technology as important as this does warrant a thorough understanding.

My final read is based purely on a personal learning choice and it's the official study guide for the AWS Certified Solutions Architect exam. Last year I passed the Azure Fundamentals exam and I always intended to take the AWS equivalent next (Cloud Practitioner) but this book covers that and the architect material as well and so far it's been really useful. 

So that's what I've been reading, I recommend all of the above, especially the SQL Server reads if you work on that platform. Would love to hear about any other recommendations!

Passed: AZ-900 Azure Fundamentals

October 2019. I hadn't sat a technical exam since I passed the MCTS Database Administration one for SQL Server 2005, that feels a long time ago. That said, for a long time I just didn't have the need to take any further exams, I was still learning day in day out (and still am) but certification wasn't really that important.

I've blogged about IT certification before whether it's useful or not is a very common and sometimes divisive question. My view has definitely changed over time and in these days of rapidly evolving platforms and solutions I've certainly found certification paths to be extremely beneficial.

In preparation for this exam I used a number of different resources. Microsoft Learn was a fantastic starting point and I liked the structure of the course because it is quite broad in content, especially if you're new to cloud concepts. I coupled that with Learn Azure in Month of Lunches, a free PDF that you can download from Microsoft here. 

As with any technical course there's layers of detail. Microsoft Learn is great but I also dove into the technical documentation of each subject area to gain a better understanding. Of course there's no substitution for practical learning and actually doing it, so a free Azure trial subscription comes in very handy (although that is limited to a month).

Then there's practice tests. We used a couple of providers for these because I felt one was fairly limited (btw I'm happy to recommend materials/providers). I try to avoid going over the practice exams too often, it's easy to memorise the questions so I get to a point where I'm fairly happy, hit a test exam and then work on any weaker areas.

Although the exam is on the fundamentals don't be fooled in thinking that it's super easy. It does require study and preparation but at the same time I didn't find any nasty surprises in the exam so full marks to the revision materials. I'd say that by sticking to the guidelines you can't go far wrong but take the time to go a level deeper, and definitely practice and get used to the environment.

And that was that, exam done and dusted, certification gained and on to the next one, which I'll cover in a future post.

Top SQL Interview Questions for 2020 (really).

I was determined to get in early this year with this post but I'm seemingly late and already seeing "Top SQL Interview Questions" posts on social media. 

Anyway, here's my list:



OK I'll be honest, THERE IS NOT LIST!

Seriously, I've been interviewed a lot and I've conducted a lot of interviews too and I have to tell you that there is no list out there that you can revise the answers for that will get you through that interview. I'm sorry.

There are of course certain guidelines; know your current role (you'll probably get asked about that), know and understand the key skills listed in the job description that you've applied for and relate them to relevant experience and/or training etc etc etc.

Now I'm not saying you can't prepare, of course you can and you really should, just don't think you can wing it. You might still get asked the difference between a clustered and non-clustered index but then again you probably won't.