Davide Mauri, Silvano Coriani, Anna Hoffman, Sanjay Mishra and Jovan Popovic

Practical Azure SQL Database for Modern Developers

Building Applications in the Microsoft Cloud

1st ed.
Davide Mauri
Redmond, WA, USA
Silvano Coriani
Legnano, Milano, Italy
Anna Hoffman
Sherman Oaks, CA, USA
Sanjay Mishra
Redmond, WA, USA
Jovan Popovic
Belgrade, Serbia
ISBN 978-1-4842-6369-3e-ISBN 978-1-4842-6370-9
© Davide Mauri, Silvano Coriani, Anna Hoffman, Sanjay Mishra, Jovan Popovic 2021
This work is subject to copyright. All rights are reserved by the Publisher, whether the whole or part of the material is concerned, specifically the rights of translation, reprinting, reuse of illustrations, recitation, broadcasting, reproduction on microfilms or in any other physical way, and transmission or information storage and retrieval, electronic adaptation, computer software, or by similar or dissimilar methodology now known or hereafter developed.
The use of general descriptive names, registered names, trademarks, service marks, etc. in this publication does not imply, even in the absence of a specific statement, that such names are exempt from the relevant protective laws and regulations and therefore free for general use.
The publisher, the authors and the editors are safe to assume that the advice and information in this book are believed to be true and accurate at the date of publication. Neither the publisher nor the authors or the editors give a warranty, expressed or implied, with respect to the material contained herein or for any errors or omissions that may have been made. The publisher remains neutral with regard to jurisdictional claims in published maps and institutional affiliations.
Distributed to the book trade worldwide by Springer Science+Business Media LLC, 1 New York Plaza, Suite 4600, New York, NY 10004. Phone 1-800-SPRINGER, fax (201) 348-4505, e-mail [email protected], or visit www.springeronline.com. Apress Media, LLC is a California LLC and the sole member (owner) is Springer Science + Business Media Finance Inc (SSBM Finance Inc). SSBM Finance Inc is a Delaware corporation.

This book was written during one of the most difficult times faced by our generation – the COVID-19 epidemic that took lives of thousands of people across the world and impacted many other lives in several different ways; and the events leading to the #Whatmatters2020 movement.

What we need more is kindness and compassion toward different ideas, different points of view, and different experiences as in those lies the road to progress.

This book is dedicated to the spirit of kindness and compassion in every living being, which we need in abundance to get through these difficult times.

Foreword by Rohan Kumar

Today’s business environments are evolving rapidly. Keeping up with the speed of change requires companies to undergo a digital transformation that includes a plan to update their applications. Some organizations choose to update their legacy software and business processes as cloud solutions. Others choose hybrid approaches that combine the benefits of on-premise and cloud capabilities. All the while, cloud-native solutions proliferate. IDC predicts the creation of over 500 million new applications in the next five years.1

This trend creates enormous opportunity – and some significant challenges – for today’s development community. Developers want to bring their solutions to market as quickly as possible and differentiate themselves from other software vendors. To accommodate customers’ growing needs, developers need a proven back-end database solution that eases the coding process.

We like to describe the Azure SQL Database as an “all-inclusive” database. By saying that, we mean that the platform offers the components and capabilities needed to streamline and accelerate application development. Application vendors need essential features like an excellent database engine, highly responsive performance, exceptional scalability, and high security. They also need a solution that embraces modern application constructs like Geospatial, Graph, and JSON, expressed in their familiar T-SQL language. The platform must support multiple deployment options too. Developers need the freedom to design their applications to run in an elastic pool, a hyperscale scenario, as managed instances, or in a serverless environment. Benefits like these have encouraged thousands of developers to choose the Azure SQL Database as their preferred solution.

We want to extend our heartfelt thanks to so many people in the SQL community who shared their feedback over the years and helped us make Azure SQL all it is today. Here at Microsoft, our team remains focused on advancing the Azure SQL Database to offer even more value for developers. Thank you for giving us an opportunity to support your efforts and innovations!

—Rohan Kumar

Corporate Vice President, Azure Data at Microsoft

Foreword by Mark Souza

Today, embracing digital transformation and cloud computing is key to customer success. There is a critical need for organizations and technologies to evolve to meet new business requirements (and shifting priorities in response to the current COVID crisis) and new data ecosystems. Azure SQL Database has been a central technology in this evolution, and modern developers need to keep suit. If you’re an experienced database software engineer or a modern developer looking to upskill your knowledge and technical intensity in this space, this book is for you.

With over 30 years’ experience in the tech industry focused on customers and digital transformation, I’m thrilled to see this work come to fruition. With the benefit of hindsight, it’s not surprising that there has been tremendous evolution and growth in the database space over the last 25–30 years.

I joined Microsoft in July 1993 to help build a Microsoft database business. Microsoft SQL Server on Windows Server (then called Windows NT 3.1) had just released. At that time, we had only 17 Microsoft SQL Server software engineers. Today there are more than 2300. In 2000, I moved into the product group to establish one of the first highly technical, customer-facing teams within engineering at Microsoft, called SQLCAT – SQL Customer Advisory Team. The team was super technical and interacted directly with our top customers around the world. Our engineers listened and learned from these customers. They then took these learnings and drove customer feedback, feature suggestions, and bug reports into important product improvements and created best-practice guidance and made it available to all customers, large and small. In 2011, the SQLCAT team became AzureCAT and focused on helping our customers accelerate their journey to the cloud.

Microsoft SQL Server evolved significantly over the past 25+ years. We saw the creation of new enhancements and capabilities to the SQL platform such as Graph, Geospatial, JSON, columnstore compression, memory-optimized tables, temporal tables, and more. Its most significant evolution came as Microsoft evolved on-premise computing and storage services to the cloud, when SQL Server became the foundation for the cloud-based Azure SQL offerings like Azure SQL DB, SQL MI, and much, much more. The developer experience for SQL Server has really evolved and today supports all of the popular languages: Python, Node.js, Go, Ruby, PHP, Java, Native C/C++, and of course .NET languages C#, F#, and so on.

This book also caters heavily to the new generation of data analytics developers, especially if you are focused on your own digital transformation journey.

The best part is the authors are most of my old SQLCAT/AzureCAT friends who are still part of the Microsoft Data Group in engineering, so it promises to be technical, practical, and very valuable to jumpstart your cloud journey. I was honored to be asked to write this foreword. While I recently moved out of engineering to run the Microsoft Customer Success organization (part of the worldwide commercial business), Microsoft data services remains close to my heart. Thank you, team, for writing this timely book for our customers and data developers across the world. I know it will be a big hit.

—Mark Souza

Corporate Vice President – Microsoft Customer Success

Introduction

This book encapsulates our combined experience on what developers need to build resilient, scalable, and secured database applications. Azure SQL Database provides a solid foundation for database applications, and we believe this book will provide a solid foundation for the developers to build the applications for future.

Chapter 1 starts off with the motivation for the book and sets the stage for developer mindset.

Chapter 2 gets you kick-started with Azure SQL, how to provision and build your first database, and how to use the samples.

Once you have your database, you would like to connect to it and start querying it. Chapter 3 gets you there. It also takes you through on configuring the right connection attributes and building the connection retry logic for resiliency.

Chapter 4 takes you through the foundations of building the constructs and writing a database application.

Chapter 5 builds on Chapter 4 and discusses more advanced concepts.

Chapter 6 discusses practical use cases and best practices for using tables and indexes.

Chapter 7 discusses more advanced concepts, such as consistency, scalability, and performance.

Chapter 8 discusses the multi-model capabilities in Azure SQL that help you build modern applications.

Chapter 9 discusses advanced data storage capabilities, such as columnstore indexes, memory-optimized tables, temporal tables, and so on.

Having written your application, you would like to monitor its performance and debug it.

Chapter 10 takes you through that.

The journey of a developer isn’t complete without the discussion on DevOps.

Chapter 11 discusses DevOps technologies, tools, and processes for database applications involving Azure SQL database.

Acknowledgments

Davide Mauri

As this is my first book as main author, I’ll go for the most common and obvious path of saying a big, huge “thank you” to my wife Olga, my amazing kids Riccardo and Anna, and my whole family Maurizio, Ornella e Claudio for…well everything. From little things to big, life-changing events, you have always been supportive and encouraged me to be my best self, either with words or, even more important, showing the way. And while these thanks are common and obvious, they are nonetheless heartfelt and true. So thanks, thanks, thanks. I love you all.

I also want to say thanks to Lara, Chuck, and Sanjay: without you, my experience in Microsoft wouldn’t have been the dream experience I’ve had – and I’m still having – so I really want to say thanks to you for everything you have done to help and support me in my journey. Thanks, thanks a lot. I also want to say a big “thank you” to Itzik, Lilach and Lubor, for being so welcoming from the first day we moved into the United States. You made us feeling at home right away, thanks!

Silvano Coriani

When I wrote my first “solo” book on ADO.NET and SQL Server, around 17 years ago, I promised myself that it was also going to be the last ../images/493913_1_En_BookFrontmatter_Figg_HTML.gif but when my friend and colleague Davide proposed me this team effort together with a group of great professionals like Anna, Sanjay, and Jovan, I couldn’t say “no.” My first big “thank you” is clearly for them, for their help and support during this adventure. During my 25+-years-long career, I’ve been blessed to be surrounded by a lot of great folks and learn from all of them, but the years I’ve spent in SQLCAT with Mark, Sanjay, and team have been a great gift and helped me to grow as a human being and a professional. I’d like to thank each and every one of them! And the story continues now within the Azure SQL team, where I have the privilege of keep working with wonderful individuals, colleagues, and bosses: thank you all!

On a personal level, first and foremost I’d like to thank my family: my lovely wife Serena and my kids Caterina and Lorenzo. Their patience, love, and support, especially during those long lockdown days when I was writing this book, were invaluable. Last but not least, a big thank to my broader family, my parents and parents-in-law, for their support and encouragement.

Anna Hoffman

Being relatively new to Azure SQL and the world of developers, I am so grateful to Davide, Silvano, Jovan, and Sanjay for the amazing opportunity to work on both topics with some of the world’s greatest SQL veterans. Through this experience, I have learned and grown from all of them in big ways. I also want to thank the SQL community, inside and outside of Microsoft, for being unlike any other community I’ve been a part of. Every day, I learn from you all, so thank you. I am thankful to every PM and engineer on our team, but specifically, I’d also like to thank Alain Dormehl, Bob Ward, and Buck Woody for providing much time and patience as I learn about Azure SQL and SQL Server. I also want to thank Asad Khan, our fearless SQL leader, for taking a chance on me two years ago in joining this team and for his continued support. Personally, I want to acknowledge my husband, Phil, who is my biggest cheerleader and helps me with our puppy (Moose, the best quarantine decision we ever made) when I spend long hours obsessing over Azure SQL. Finally, I want to thank my parents, Shaji and Leigh Ann Thomas, who inspire me every day to be better (and who started the Computer Science trend in our family) and my brother, Zachary, who is continuing that tradition in his final year at Duke University (I am so proud of you and can’t wait to see what you accomplish!).

Sanjay Mishra

I recently completed 15 years at Microsoft. In my early years in Microsoft, two people went out of their way to help me get settled and grow in my role, and I would like to acknowledge their contribution and influence – Mike Ruthruff and Prem Mehra. Mike spent countless hours with me in my early days helping me understand the new database product I was dealing with, which was so similar, yet so different from the database product I knew so well coming into Microsoft. Mike was my go-to person for everything from installing Windows (yes, installing and configuring Windows wasn’t easy on those Itanium boxes) to attaching a debugger and debugging an early build in our SQLCAT lab, with a complex customer workload. Mike’s kindness and generosity with his time helped me cut my teeth in SQL Server; else, this book would have been a distant dream.

Prem took me under his wings and coached me on dealing with customers at all levels – from a database developer to a CTO. Every time Prem was in a meeting with a customer, I would just sit there and observe him deal with them – irrespective of whether the customer was angry or happy, frustrated or confused, Prem’s mood would always be constant; I never saw him get unsettled. Customers respected him a lot. If I had to meet a customer alone, and I knew that Prem had worked with them earlier, I would introduce myself saying that “I work with Prem,” and it would immediately earn me a camaraderie with the customer that would have taken days and months to build. In later years, I earned a lot of kudos from various people for my customer empathy, but all of it has been Prem’s coaching.

Prem is enjoying his retired life, and Mike is having fun at his startup, but I miss them every single day at work. My 15 years at Microsoft and this book would not have happened if not for Mike and Prem’s influence in my early days. Thank you, Mike! Thank you, Prem!

Jovan Popovic

I would like to thank my daughters and wife for their patience and support during this adventure.

Table of Contents
Index 319
About the Authors
Davide Mauri
../images/493913_1_En_BookFrontmatter_Figb_HTML.jpg

is Program Manager in the Azure SQL Database product group. He has been working in the IT field since 1997 and was awarded Data Platform MVP status for 12 consecutive years. He started his career as a full-stack and back-end developer and then focused on databases and data science for 15 years while still keeping alive his passion for development (mainly in C# and Python). He then moved to the Internet of Things (IoT) and big data space where ingesting, processing, and analyzing millions of data points in near real time was his everyday challenge. Building from that experience, he joined Microsoft to help companies worldwide to leverage stream processing at scale. He now works for Azure SQL Database as the developer’s voice inside the product group, working to make sure that Azure SQL Database is, and will be, the best database option for developers.

 
Silvano Coriani
../images/493913_1_En_BookFrontmatter_Figc_HTML.jpg

has worked on SQL and other data technologies for more than 25 years. He started as an embedded systems developer in industrial automation and moved to data acquisition, processing, and analysis in distributed solutions. He has worked on several RDBMS engines (SQL Server 6.0 and later releases, Oracle, IBM DB2, and more recently PostgreSQL) and on NoSQL and other analytical engines such as Spark and Hadoop. He also has experience in C/C++, Visual Basic, Java, and .NET. He has worked in several industries, including manufacturing, retail, and financial services. Since joining Microsoft in 2003, he has been busy helping ISVs and enterprise customers to learn and adopt Microsoft technologies. His experience includes being a Developer Evangelist and Software Engineer in the SQL Customer Advisory Team and most recently working in SQL Customer Success Engineering for the Azure Data team. He is also an active speaker at industry conferences such as PASS Summit and Microsoft Ignite.

 
Anna Hoffman
../images/493913_1_En_BookFrontmatter_Figd_HTML.jpg

is Data and Applied Scientist on the Azure Data team at Microsoft. She has worked in Microsoft Research, AI Engineering, and Microsoft Services in her time at Microsoft. She spent several years working on .NET, Python, R, and Java to democratize AI via tools and services such as Azure Cognitive Services, Azure Machine Learning, and SQL Server (Machine Learning Services and Big Data Clusters). She now spends most of her time working on the Azure SQL Database product team and participating in the community. She has an undergraduate engineering degree and a Master of Science in Analytics from Georgia Institute of Technology. You can follow her on Twitter at @AnalyticAnna.

 
Sanjay Mishra
../images/493913_1_En_BookFrontmatter_Fige_HTML.jpg

is a product leader in the Microsoft Azure Data organization. He leads product management for the Azure SQL Database. Prior to this role, he served as Technical Advisor to the VP of Azure Data PM and led the eminent SQL Customer Advisory Team (SQLCAT).

 
Jovan Popovic
../images/493913_1_En_BookFrontmatter_Figf_HTML.jpg

is Senior Program Manager working on Microsoft SQL Server, Azure SQL Database, and Azure Synapse Analytics. He is working in Microsoft Development Center Serbia as Program Manager on several developer-facing SQL Server/Azure SQL features such as T-SQL language enhancements, JSON/temporal support, columnstore/in-memory technologies, and built-in intelligence. He has more than 15 years of experience in various Microsoft technologies with a focus on SQL Server, Azure, ASP.NET, C#, and JavaScript. He has been working in various Microsoft Data Platform teams since 2012. He is a former Microsoft MVP for ASP.NET and has a PhD in software engineering from the University of Belgrade. He is a public speaker at top Microsoft conferences and is an author of several books and open source projects. He enjoys exploring technologies (especially SQL Server capabilities) and finding the best ways to implement the most efficient solutions using them.

 
About the Technical Reviewer
Drew Skwiers-Koballa

is a Program Manager at Microsoft for the Azure Data SQL Tools and Experiences team, which focuses on graphical and command-line tools for Azure SQL and SQL Server. Prior to joining Microsoft, he had a decade of experience as a database administrator and developer, including publishing several open source extensions for Azure Data Studio.

 
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.119.143.4