Azure Data Blog articles

The new frontier of data for the next generation of innovation

Shireesh_Thota — Tue, 18 Nov 2025 15:59:48 GMT

A decade ago, an agent that could gather insights from data, trigger actions and make intelligent decisions was science fiction. Today, that kind of intelligent technology is not only possible, but it’s becoming a business requirement. Enterprises must find new and meaningful ways to propel AI innovation that meets customer and business needs. The next generation of innovation requires a data foundation that is unified, secure, and addresses persistent challenges of latency, rigidity, and complexity, while infusing data with AI to optimize performance and accelerate development.

This week at Ignite, Microsoft is taking a bold step toward making a future-ready data foundation a reality, unveiling innovations that deliver performance, scale and flexibility, and bridge the gap between analytical intelligence and operational agility. The innovations we’re announcing are catalysts for businesses to modernize faster and build the intelligent applications of tomorrow. By leveraging a unified data strategy on Azure, BMW is much closer to their goal of predictive maintenance for vehicles and smart factories. With these releases announced today at Ignite, Azure is poised to deliver a resilient, scalable, and AI-integrated data foundation that will help you unlock innovation at scale.

Modeling a future-proofed data platform

A future-proofed data platform should deliver performance at scale, flexibility and openness, unified operations and analytics, streamlined management, and seamless integration with developer tools, all backed by security and trust. The innovations we’re announcing reflect these priorities.

Performance at any scale

We’re releasing performance enhancements across the database portfolio that let users easily scale performance to support intelligent agents and applications that can stand apart in any industry. These new features enable applications backed by Microsoft databases to seamlessly handle massive throughput and global user loads without performance bottlenecks.

Introducing Azure HorizonDB

We’re excited to unveil Azure HorizonDB in private preview, a new fully managed PostgreSQL service built for performance and AI workloads that will offer scaling up to 192 virtual cores and 128 TB of storage. Azure HorizonDB is built for business and engineered for developers. Ultra-low latency, high read scale, built in AI, and deep integration with developer tools including GitHub Copilot delivers performance, resilience and simplicity at any scale. With HorizonDB, teams can:

Build AI apps that perform at scale with advanced DiskANN vector indexing, pre-provisioned AI models, semantic search, and unified support for relational and graph data.

Accelerate app development with built-in extensions, including the PostgreSQL extension for Visual Studio (VS) Code integrated with GitHub Copilot. GitHub Copilot in VS Code is context aware of PostgreSQL and includes one-click performance debugging.

Unlock data insights with deep integrations with Microsoft Fabric and Microsoft Foundry.

Expect reliability with a service that is enterprise ready on day one, integrated with Entra ID, Private Link networking, and Azure Defender for Cloud.

Perfecting performance across the portfolio

We’re also addressing cloud-ready performance and scaling needs in Azure Database for PostgreSQL and Azure SQL. Elastic Clusters for Azure Database for PostgreSQL, now generally available, enables developers to easily scale a single database across a cluster of read and write nodes using a simple SQL command. Additionally, new v6 SKUs, which support up to 192 vCores, and general availability of PostgreSQL 18 gives Azure Database for PostgreSQL users a potent performance boost.

With the release of the next-generation Azure SQL Managed Instance, we’re helping you modernize SQL Server in the cloud with better performance and easier migration. You’ll now have access to the latest technology, unlocking better performance and scale with more storage and database capacity. Flexible compute, storage and memory options also enhance the ROI of migration and offer broad compatibility for unique workload demands.

Multi-modal, flexible and open

A comprehensive data strategy isn’t one-size fits all. Openness and flexibility are core tenants of a future-ready data platform. Flexibility means you get to choose the deployment model that works for your business, whether it’s on-premises, cloud only or hybrid. Beyond having flexibility for where your data lives, the modern data platform should also support multiple data models and open APIs to reduce complexity and enable extensibility as workload needs and team resources evolve. That’s why Azure fully embraces, supports, and contributes to open-source innovation.

Meet the new Azure DocumentDB

We’re excited to announce the general availability of Azure DocumentDB, the new name for our MongoDB-compatible NoSQL document database service with hybrid and multi-cloud flexibility. Powered by the open-source DocumentDB engine managed by the Linux Foundation, Azure DocumentDB is designed for enterprise workloads with the flexibility to build anywhere and run managed on Azure. It includes native vector search powered by DiskANN and full-text search, and it supports advanced search scenarios that combine fuzzy search and BM25 ranking for smarter, more accurate query results.

Support for translytical workloads

A translytical data platform is designed to support both transactional and analytical workloads. This combo is crucial for responsive, real-time AI applications. A future-proofed data strategy should natively bridge operational data and analytical insight; a capability we’re delivering with Microsoft Fabric.

Unifying data with Fabric databases

Fabric databases are now generally available, bringing together SQL database and Cosmos DB inside Microsoft Fabric. Built natively into Microsoft Fabric, Fabric databases bridge the gap between traditional databases and data lakes, enabling real-time analytics, transactional processing, and AI workloads to run side by side in one governed environment. Every Fabric Database automatically connects to your organizational data mesh, ready for Power BI, AI, and Copilot experiences.

Replicating databases with zero ETL, in near real time, with mirroring

If you prefer to keep your operational databases where they are, you can still take advantage of Fabric’s unified data foundation with database mirroring, which is now generally available in Microsoft Fabric, supporting SQL Server, Azure Cosmos DB, and Azure Database for PostgreSQL. With mirroring, you can replicate these databases in Fabric for business analytics and AI scenarios without migrating or refactoring.

Several early adopters are already experiencing real results with Fabric Databases and mirroring. AP Pension, a Danish pension fund, has consolidated decades of fragmented data using Microsoft Fabric, enabling a unified, governed analytics platform for actuarial, finance, and development teams. With Fabric, they’ve built a centralized medallion architecture, automated data delivery via APIs, and supported real-time write-back from Power BI through SQL Databases — all with strong governance and security baked in.

General availability of SQL Server 2025

SQL Server 2025 is now generally available following an outstanding preview with 10,000 participating organizations, double the download rates of SQL Server 2022, and more than one million databases created so far. Built on SQL Server’s foundation of trusted security, performance and availability, SQL Server 2025 redefines what's possible for enterprise data. With built-in AI and developer-first enhancements, SQL Server 2025 empowers customers to accelerate AI innovation using the data they already have, securely and at scale, all within SQL Server using the familiar T-SQL language.

AI at the core

We believe that AI is a force multiplier for the data platform itself, so every Azure database is deeply embedded with AI capabilities transforming them from passive data stores into active, intelligent engines for AI-powered applications. Azure databases are built to understand, reason and act, which translates to faster, more accurate search, smarter recommendations, streamlined developer workflows, and the ability to power agentic and generative AI workloads without friction.

Azure SQL integrates DiskANN

DiskANN, Microsoft’s cutting-edge vector search algorithm, is now natively integrated into SQL Server 2025, Azure SQL Database and Azure SQL Managed Instance. DiskANN delivers fast, scalable, and highly accurate approximate nearest neighbor (ANN) search, handling millions to billions of vectors with low latency and high recall. This enables developers to build intelligent, AI-powered applications more efficiently directly within the database engine, eliminating the need for external vector databases and simplifying the architecture for future AI-native apps.

Azure Cosmos DB supercharged by AI

Azure Cosmos DB continues to evolve as the backbone for AI-powered, globally distributed applications. At Ignite, we’re introducing a new wave of enhancements that make vector search, text retrieval, and semantic relevance faster, more intuitive, and more intelligent for modern AI workloads. One of the biggest improvements comes from advancements in Azure Cosmos DB vector search powered by DiskANN. The latest engine optimizations significantly boost throughput and reduce latency for vector insert and update operations. Additional enhancements include:

General availability of fuzzy search in Azure Cosmos DB full-text search, which enables more flexible text matching.

General availability of Azure Cosmos DB Fleets, allowing multi-tenant apps to share throughput capacity across multiple database accounts while maintaining full security and performance isolation for their tenants.

Public preview of fleet analytics that provide insights for multi-tenant workload optimization and growth planning.

Azure Database for PostgreSQL optimizes developer experiences

We’ve also made improvements to Azure Database for PostgreSQL to help developers streamline their workflows and build and scale next-gen AI solutions faster with confidence. The PostgreSQL extension for Visual Studio (VS) Code, now generally available, seamlessly unifies DBA and developer workflows for PostgreSQL databases—on Azure or anywhere. The improved extension, which already reached more than 250K installs in preview, gives developers a familiar, productive environment to work with PostgreSQL, complete with Azure AD authentication and GitHub Copilot AI assistance for SQL coding. Azure Database for PostgreSQL is also now natively integrated with Microsoft Foundry, enabling developers to build intelligent, secure AI apps and agents with minimal friction.

Grounded in security and trust

Innovation shouldn’t come at the expense of security. A unified data platform should have end-to-end governance and security built in for enterprise-grade resilience as you build what’s next. At Microsoft, we continue to deliver a secure cloud environment for your data with features like Microsoft Purview for data governance and unified identity and access controls across the entire Azure data estate. Most recently, we’ve announced:

Access token refresh with Entra ID for Azure Database for PostgreSQL, which enables database connections using AD credentials to automatically renew tokens, eliminating disruptions and ensuring strong identity-based security without added complexity.

Confidential Compute in Azure Database for PostgreSQL, which provides access to Confidential Virtual Machines (CVMs) to protect sensitive data even during processing.

The next frontier of data is here

BMW has already begun to embrace the next frontier of data. They modernized their Mobile Data Recorder (MDR) system on Azure to deploy multi-agent AI that enables their engineers to instantly analyze telemetry data. Azure Cosmos DB provides persistent storage for chat conversations and memory, while Azure Database for PostgreSQL supports structured telemetry analysis and feedback mechanisms. Both integrate seamlessly with Microsoft Foundry Agent Service, which BMW leverages to orchestrate specialized agents. This solution helped them deliver insights 12x faster, embed AI-driven workflows into daily engineering, and accelerate innovation across their global operations.

As someone who’s seen data technology evolve, I’m excited about how the latest capabilities and integrations across the Azure database portfolio simplify architectures while opening new doors. They represent Microsoft’s commitment to helping customers innovate with a unified, intelligent data estate. The organizations that lead in the AI era will be those that have their data house in order; our goal at Microsoft is to give you the keys to that house. With a unified data platform, you’re not just solving today’s problems—you’re building a foundation for endless innovation.

Join us online or in person at Microsoft Ignite November 18—21, 2025, to see these announcements in action and get insights on building your own future-ready data strategy.

Building Intelligent AI Apps with Microsoft Databases

Shireesh_Thota — Tue, 20 May 2025 18:23:22 GMT

We’re living in a transformative era—where AI is no longer a futuristic vision, but an urgent, present-day imperative. Organizations across every industry are racing to integrate intelligent solutions that can streamline operations, unlock insights, and deliver deeply personalized experiences. But here’s the truth: AI readiness doesn’t start with the model—it starts with the data. And for enterprises, having the right data infrastructure in place isn’t just helpful, it’s mission critical.

That’s where Azure databases come in. At Microsoft, we understand that to be truly AI-ready, organizations need a data foundation that’s built on trust, scale, and resilience, so they can innovate boldly, securely, and at scale. Modern AI solutions—from copilots to autonomous agents—thrive on data at scale. And not just traditional structured data like customer records or transactions, but massive volumes of unstructured data: documents, images, logs, conversations, sensor outputs, and more.

Today's announcements highlight our commitment to helping customers fully unlock the potential of their data, building upon a strong foundation for using AI to create new possibilities and opportunities.

Modernize your data for AI Readiness

Modernizing your data is a critical first step in making it more accessible, reliable and usable for AI app development. SQL developers love the scalability and agility they can achieve in the cloud, and we’ve brought many of those cloud benefits on-premises with SQL Server 2025. This release has already resonated well with more than 3,400 applicants for our private preview program, with full adoption coming twice as fast as SQL Server 2022. We’re excited to announce public preview of SQL Server 2025, our most significant release of SQL Server in the last decade.

SQL Server 2025 empowers developers to build modern AI applications using their own data. Built on a foundation known for best-in-class security and performance, SQL Server 2025 will accelerate time-to-market for your new applications with features designed to boost developer productivity while keeping your data safe. Make the most of Microsoft’s most significant release of SQL Server in the last decade to:

Boost search intelligence using advanced semantic search alongside full text search and filtering - allowing you to run generative AI models of your choice using your own data.

Process and manage data flows more simply and efficiently using native JSON support, built-in REST API, Change Event Streaming for real-time data updates.

Leverage the most secure database to improve credential management and reduce potential vulnerabilities with support for Microsoft Entra managed identities through Azure Arc.

Increase workload uptime and improve concurrency for SQL Server applications with enhanced query optimization, optimized locking and improved failover reliability.

Achieve zero-ETL, real-time analytics by replicating SQL Server data to Microsoft OneLake with Fabric database mirroring.

See SQL Server 2025 in action and learn how you can get started today.

“We are excited about the future of SQL Server 2025 and the transformative

improvements it brings to database performance and management.”

Madhab Paudel, Database Engineer, Entain

SQL Server Management Studio (SSMS) is the go-to tool for developers and database administrators managing, configuring and querying SQL Server databases. Today we’re taking a leap forward with the general availability of SQL Server Management Studio 21, now built on Visual Studio 2022 and including 64-bit support, Git integration, an enhanced user interface and more. We’re further enhancing the user experience with the preview of Copilot in SSMS 21, providing an AI-powered assistant to help with writing, editing and fixing T-SQL queries using natural language.

Visual Studio (VS) Code has become a favorite among developers for its lightweight design, powerful extensions, and seamless support for a wide range of programming languages and workflows. Today we’re announcing the preview of MSSQL extension for Visual Studio Code with GitHub Copilot capabilities. GitHub Copilot also brings natural language capabilities and helps developers explore schemas, optimize queries with intelligent suggestions, and streamline database interactions—all within VS Code.

For mission-critical workloads, performance and availability are non-negotiable. We’re excited to announce the general availability of two major performance upgrades in Azure SQL Database Hyperscale. Continuous priming now ensures faster failover recovery by proactively warming up secondary replicas, minimizing downtime and boosting resilience. Additionally, the log generation rate has increased to 150 MiB/s, enabling significantly faster data ingestion and improved performance for write-heavy workloads. These enhancements make Hyperscale even more powerful for critical applications.

“When we process new data, we have a massive amount that needs to go out immediately. Azure SQL Database Hyperscale was the only tier that allowed us to do a massive data push without affecting the user experience in our platform.”

Marco De Sanctis, Chief Technology Officer, Mondra Global Limited

Modern application development demands speed and flexibility and JSON enables developers to model complex, evolving data without rigid schemas. We’re happy to announce that the native JSON type and JSON aggregates in Azure SQL Database are generally available, enabling more efficient reads, writes and storage. JSON indexing, now in preview for Azure SQL Database, allows for more efficient searching of JSON documents using JSON functions and relational operators.

Experience open flexibility with Azure scale

Open source databases play a critical role in shaping the future of intelligent applications, with PostgreSQL being the most popular according to Stack Overflow’s 2024 Developer Survey. Microsoft proudly supports open source, and our recent investments into both the open-source Postgres project and our cloud services underscore our commitment to making the developer experience better than ever, using AI to shape how apps are built.

Today, we’re announcing the preview of a new and improved PostgreSQL extension for Visual Studio (VS) Code with GitHub Copilot capabilities. With this powerful tool, you can connect to PostgreSQL database instances, run queries, create and manage connection profiles, take advantage of Entra ID support, and more—all within VS Code. Designed to boost your productivity and streamline development, the extension includes the ability to chat with GitHub Copilot for PostgreSQL context and AI assistance and support for deployments in Docker, on premises or on Azure.

For AI app developers, database speed and availability are critical to ensuring AI models can access and process data quickly. We are pleased to announce that DiskANN, one of the fastest vector indexing algorithms on the market, is now generally available on Azure Database for PostgreSQL. With this release, developers can leverage the new product quantization optimizations in DiskANN to build high performance, low latency and scalable generative AI applications that outperform pgvector index types. Also coming soon, you can enable high availability (HA) with Azure Premium SSD v2 disks while deploying Azure Database for PostgreSQL flexible server. We’ve rearchitected HA for SSD v2 and most workloads will typically see less performance impact when using high availability in SSD v2 vs SSD v1.

“Azure Database for PostgreSQL gives us greater performance capabilities and flexibility, enabling smaller teams to do more—especially when managing large numbers of databases.”

Mike Jasperson, VP, IoT Software Operations and Support, PTC

Vector search is vital for generative AI but often misses semantic relationships in enterprise data. We’re introducing generative AI-powered reasoning in PostgreSQL with the preview of semantic operators in Azure Database for PostgreSQL. By adding semantic understanding to the levels of operational data that are not visible to vector search, customers can gain deeper insights from their data, allowing applications to reason in ways that were not possible before. 

Build no-compromise SaaS apps that scale

AI applications thrive on real-time, reliable, and always accessible data, making enterprise-grade availability and global scale foundational to their success. For multi-tenant SaaS platforms delivering intelligent experiences, even brief data outages or latency can disrupt AI model performance and degrade user trust. Azure Cosmos DB provides the distributed architecture, multi-region replication, low latency, and elastic scalability needed to meet these demands.

Today we’re announcing the public preview of per partition automatic failover for Azure Cosmos DB. This new capability intelligently routes requests at a more granular partition level during localized or regional outages, helping you achieve active-active 5’9s availability for any workload type, any consistency level from eventual to strong. You’ll benefit from stronger, built-in resiliency, reduced downtime, and less operational overhead—helping to ensure that your applications remain consistently available and reliable. 

We’re further optimizing queries on Azure Cosmos DB with a global secondary index, now in public preview. Previously known as materialized views, global secondary indexes are read-only containers that automatically sync data from a source container. They have an independent partition key, data model, and index policy, allowing you to fine-tune them for any query pattern. And now, you can create index containers using Azure portal.

Managing multi-tenant SaaS apps is complex—balancing diverse customer needs with performance, scalability, and security. With Azure Cosmos DB fleets, now in preview, you can streamline operations by pooling throughput (RU/s) across accounts, reducing overprovisioning while maintaining tenant-level isolation and security. It’s a simpler, more efficient way to scale and manage Azure Cosmos DB resources for multi-tenant scenarios.

Managing user access across tenants quickly becomes complex and error-prone without centralized identity control. Today we announced the general availability of Microsoft Entra ID authentication for Azure Cosmos DB for MongoDB (vCore), enhancing security and simplifying identity management. With this update, you can now add Entra ID accounts directly to your MongoDB vCore clusters and use them for secure database access.

Azure AI Foundry simplifies project management and deployments while integrating advanced AI capabilities. Developers can now use Azure Cosmos DB accounts to power AI solutions within the platform. Using the Azure AI Foundry SDK, customers can now securely store conversation threads between users and AI agents in Azure Cosmos DB accounts, enabling agents to recall and resume previous discussions. Threads storage is now generally available. Also, developers will soon have the capability to utilize data stored in their Azure Cosmos DB accounts for powering AI solutions in Azure AI Foundry. Customers will be able to connect and access their Azure Cosmos DB data using Azure AI Foundry within application code.

We’re also announcing the public preview of Azure AI Foundry connection for Azure Databricks, enabling Foundry Agents to use AI/BI Genie and run Azure Databricks Jobs. This can enhance knowledge retrieval and broadens how Foundry Agents deliver contextual answers grounded in enterprise data. Foundry Agents can now reason across Microsoft’s entire ecosystem, including Microsoft 365, Dynamics 365, Microsoft Fabric, and Azure Databricks— providing a robust foundation for building intelligent AI applications.

We’re making several announcements focused on enhancing the search capabilities of your applications. Now generally available, native full-text and hybrid search capabilities mean you can use Azure Cosmos DB for NoSQL to efficiently create sophisticated applications and GenAI solutions, reducing complexity and costs compared to using external search services.  We’re continuing to invest here with the previews of fuzzy text search and full-text search and scoring in multiple languages in Azure Cosmos DB for NoSQL. These new features enable developers to deliver more flexible and forgiving search experiences alongside full-text indexing and BM25-based ranking across a growing set of languages. Rounding out our search enhancements, phrase search is now generally available and makes your full-text search more precise and powerful by matching entire phrases rather than just individual keywords.

“We found Cosmos DB built-in hybrid search to be 50-60 times cheaper than other search services we evaluated, providing us performance and resiliency Docusign needs.”

Kunal Mukerjee, VP of AI Technology Strategy, Docusign

Finally, DiskANN is now generally available in vCore-based Azure Cosmos DB for MongoDB, bringing powerful vector search capabilities directly into the database. With support for up to 4,000 dimensions, you can efficiently search across high-dimensional data like text embeddings and images—enabling low-latency, high-accuracy AI experiences without the need for a separate vector store.

“With our virtual assistant in particular, we have experienced the higher scale, higher availability, and faster time to market of using Azure Cosmos DB.”

Viju Chacko, Head of Digital Architecture, Air India

Unify your data estate on Microsoft Fabric

With Microsoft Fabric, our vision is to simplify how you interact with data by bringing together all the tools you need on a single platform. We're expanding that vision with the unification of data estates with the preview of Cosmos DB (NoSQL) in Fabric. Based on Azure Cosmos DB, Cosmos DB in Fabric brings the same enterprise-grade dynamic scalability for unstructured data types that the world’s largest retailers and large-scale AI applications rely on today. Developers can now deploy Cosmos DB in Fabric in just a few clicks to build high-performance, distributed applications with ease. You can try Cosmos DB in Fabric today.

Organizations are under pressure to unify fragmented data estates and deliver insights faster than ever. Whether you're modernizing on-premises systems or building AI-powered applications, mirroring into Microsoft Fabric simplifies integration, enhances resilience, and accelerates time-to-insight across your entire data estate. We recently announced the preview of mirroring for Azure Database for PostgreSQL and Azure SQL Database behind a firewall. We are now excited to announce mirroring for all in-market versions of SQL Server from SQL Server 2016 to SQL Server 2022. And, with the preview announcement for SQL Server 2025, we are also announcing preview of mirroring for SQL Server 2025 in Fabric.

Azure databases are the foundation for AI innovation

Scalable, resilient, and secure data is the cornerstone of breakthrough AI innovation and Azure databases are built with the enterprise-grade performance modern apps demand. Whether you're supporting millions of users across a global SaaS platform or training intelligent models on real-time data, Azure’s fully managed database services provide the foundation to move fast without compromising trust or uptime. With built-in high availability, advanced threat protection, and elastic scalability, Azure databases empower organizations to innovate confidently and deliver intelligent, data-driven experiences at enterprise scale.

Join us at Microsoft Build from May 19 to 22, 2025 to see all these announcements in action across the following sessions:

BRK 197: Architecting highly resilient applications in Azure

BRK 202: Scale and secure MongoDB-compatible apps with Azure Cosmos DB

BRK203: Get faster LLM responses and low app latency with Azure Managed Redis

BRK 204: What's New in Microsoft Databases: Empowering AI-Driven App Dev

BRK206: Microsoft Fabric for Developers: Build Scalable Data & AI Solutions

BRK207: SQL Server 2025: The Database Developer Reimagined

BRK210: Build AI apps and unlock the power of your data with Azure Databricks

BRK211: Building advanced agentic apps with PostgreSQL on Azure

BRK212: Design scalable data layers for multi-tenant apps with Azure Cosmos DB

BRK213: Enable Advanced AI Scenarios with Unified Data Estates in Microsoft Fabric

COMM410: The Future of Databases & Developers: An AMA with Azure Database Leadership

Fuel AI Innovation with Microsoft Databases

Shireesh_Thota — Tue, 19 Nov 2024 17:00:00 GMT

If data is the fuel that powers AI, then AI is only as good as the data behind it. Right now, it’s never been more important to have a strong data analytics and management foundation in place.

To help our customers achieve real transformation with AI, we’ve invested heavily across the Microsoft databases portfolio. That includes developing a comprehensive vision for our databases focused on one goal: enabling you to build the next generation of intelligent applications.

This week at Microsoft Ignite 2024, you’ll hear about how Azure helps you create fast, secure, and scalable applications powered by the latest advances in AI. Let me share a quick summary of our top database announcements.

Delivering the best enterprise databases

At the heart of this vision is our commitment to providing the best enterprise databases, with a strong emphasis on reliability, resiliency, and security and the performance and availability needed to support modern, intelligent applications.

We’re proud of the ground-to-cloud flexibility we offer for your workloads, and that’s especially true when it comes to our SQL databases—they’re enterprise-ready and built on the same proven, industry-leading SQL engine, so you have a consistent SQL experience whether you’re on-premises or in the cloud. The latest release, SQL Server 2025, is now in private preview and features built-in AI to simplify intelligent application development and RAG patterns.

We also announced the general availability of instance pools in Azure SQL Managed Instance. Instance pools let you provision small, cost-effective 2-vCores instances within a pre-provisioned pool, helping you right-size your workloads when migrating or modernizing in the cloud.

In our flagship NoSQL database Azure Cosmos DB, dynamic autoscaling is now generally available, providing cost optimization for nonuniform workloads. With dynamic autoscaling, partitions and regions scale independently so you can scale all the data your AI applications are using in the most cost-efficient way.

We’ve also invested in our fully managed open-source databases. This includes new features in Azure Database for MySQL such as zonal resiliency by default, which will be generally available in December 2024. This feature helps you ensure seamless server recovery and business continuity in the face of zonal outages. Also coming to public preview in December, you can use Azure Migrate to discover MySQL instances and their attributes within your environment, assess their readiness for migration, and obtain recommendations on suitable compute and storage options.

In addition, we’ve made recent investments in Azure Database for PostgreSQL, including the public preview of elastic clusters on Azure Database for PostgreSQL – Flexible Server. This enables horizontal scaling through row-based and schema-based sharding, making it easy to build multitenant apps by offloading shard management and operations—such as tenant isolation, split, or online rebalancing of shards—to the service.

Unlocking the power of AI with SaaS-ified databases

Built on a SaaS foundation, Fabric Databases are a new class of cloud databases that bring together transactional and analytical workloads to create a truly unified data platform.

Now in preview, SQL database is the first database engine to come to Fabric, enabling customers to:

Build intelligent AI applications faster with built-in vector search, RAG support, and Azure AI integration.
Boost productivity with auto-optimizing and auto-scaling databases.
Accelerate innovation with Copilot assistance.
Support CI/CD using GitHub integration for source control.

Ready to give SQL database in Fabric a try? Starting December 3rd, you can join live sessions with database experts and the Microsoft product team and see just how easy it is to get started. View the schedule and register for the series here. You can also register today to join us from March 29 to April 3, 2025, at the Microsoft Fabric Community Conference in Las Vegas, Nevada to learn more.

Rounding out our Fabric Databases news, we’re also pleased to announce the public preview of Fabric integration with Microsoft Purview Information Protection, extending the benefits of central, policy-based governance to Fabric items, including the new SQL database in Fabric.

Meeting the needs of modern AI developers

Beyond a SaaS-ified experience, we also want to provide the best databases for AI developers. We have a lot of exciting developments to share that demonstrate our commitment to building an ecosystem that’s integrated with Azure AI services and tool support to make building AI applications even easier.

Let’s start with a recent innovation in Azure SQL Database and now in SQL database in Fabric – native vector support. We’re excited to announce the public preview of a vector data type that gives developers the ability to handle vector data, which is foundational when it comes to building scalable AI-enabled applications. This announcement also includes essential new vector functions, like VECTOR_DISTANCE, VECTOR_NORM and VECTOR_NORMALIZE to support advanced operations, particularly when the embedding model does not return normalized vectors. And, we’re working hard to make vector indexing on Azure SQL even faster with DiskANN, coming in the future. DiskANN is one of the fastest vector indexing algorithms on the market, and its performance and reliability characteristics are a great fit for our customers’ demanding requirements.

While Azure Cosmos DB is already fueling some of the most powerful AI applications on the market, we’re committed to making it even better. We're making a number of announcements to boost performance even further, to include the general availability of DiskANN vector index in Azure Cosmos DB for NoSQL. We’re also announcing two new search features available in public preview: full-text search, which enables efficient text searches and text-based ranking with BM25, and hybrid search, which combines the benefits of semantic vector search, with the text-based relevance of BM25. These new capabilities help retrieve the most accurate data from the database to power generative AI applications.

Azure Database for PostgreSQL is optimized for AI developers, and we continue to expand its capabilities with the addition of DiskANN, now available in preview. The new Semantic Ranker Solution Accelerator, now generally available, provides automated deployment scripts that can be used to provision a semantic ranker model as an Azure Machine Learning inference endpoint. Finally, we introduced graph processing capabilities within Azure Database for PostgreSQL with the Apache AGE graph extension and enhanced the accuracy of your generative AI applications with a solution accelerator that integrates GraphRAG with PostgreSQL graph query capabilities. Finally, automatic parameter tuning is coming soon to Azure Database for PostgreSQL. Using machine learning to optimize workload parameters, your AI applications will exhibit significantly higher performance and scalability.

We also have some exciting enhancements for MySQL developers. Azure Database for MySQL now supports the MySQL 9.1 Innovation release, which includes exciting new capabilities, such as JavaScript for stored procedures and vector datatype support, expanding your options for application development and advanced data processing.

Integrating your data estate

Microsoft Fabric is the foundation of an integrated data estate, bringing together everything from data science, engineering, and warehousing to real-time intelligence and operational databases in one environment. Mirroring provides a modern way of accessing and ingesting data continuously and seamlessly from any database into Microsoft Onelake in Fabric. This capability is now generally available for Azure SQL Database and in preview for Azure SQL Managed Instance. Mirroring is also in private preview for SQL Server 2016-2022, and you can sign-up to participate here.

The future of databases is now

Microsoft databases are empowering developers with essential tools to create groundbreaking AI applications. Whether you’re a large enterprise or a startup building your first application, Microsoft databases provide the performance, security, and reliability to help you make the most of your investments—and unlock valuable insights that drive business growth. Embracing these technologies today sets the stage for a more innovative, efficient, and secure tomorrow.

Data for now and next: Powering a new era of AI apps

Shireesh_Thota — Tue, 21 May 2024 15:30:00 GMT

In this era of AI, the power to transform human interactions and propel businesses forward lies in the hands of organizations that are ready to learn, adapt and scale fast. And as business applications become more critical to engaging with customers, to optimizing processes and team productivity, and to driving innovation, the role of developers is more important than ever.

To help set the foundation for innovating with AI, Microsoft is introducing a new way of managing data at any scale, anywhere. Where your on-premises investments and experiences extend to the cloud, while managed services do more on your behalf with modernized workloads for the next stage of growth. By harnessing Azure databases’ fully managed capabilities and integration with Microsoft proprietary and third-party solutions, developers can focus on what really matters: INNOVATION.

Azure cloud scale databases are designed to support any type and size of application, and direct integration into cloud scale analytics and AI help accelerate actionable insights, while simplified and intelligent security solutions protect your data across all layers. Always updated and secure, Azure databases help streamline modern, intelligent application development, empowering developers to build incredible digital experiences with agility and ease.

As AI applications become more mainstream, seamless database management can be paramount. Trusted solutions that can scale limitlessly and autonomously, respond fast, and offer unparalleled flexibility and reliability will shape the future of coding.

Azure cloud scale databases go beyond simplifying database operations – they are built and tuned for transformative AI innovation.

This week at Microsoft Build 2024, you’ll hear about breakthroughs in generative AI, building Copilots, securing applications and more. Let me offer you a glimpse of what’s in store for Azure databases and what sessions you won’t want to miss.

Building future-ready, scalable AI apps

Azure’s vision for cloud-scale data is grounded in fueling AI innovation. This week, we are introducing important features that help developers build and transform modern, intelligent applications. Organizations looking to innovate with AI can now rely on native vector search capabilities in Azure Cosmos DB for NoSQL. With semantic search powered by DiskANN, a powerful algorithm library, Azure Cosmos DB becomes the first cloud database to offer super lower latency at scale with vector database built-in.

Microsoft is also announcing the general availability of Azure Database for PostgreSQL Azure AI extension to help unlock innovation with AI. Using this extension, developers can connect PostgreSQL in Azure directly to Azure Open AI embedding models helping to simplify development of RAG apps using data in PostgreSQL.

Azure Database for PostgreSQL is also the industry’s first cloud database to provide local embedding creation directly in the database server using a SQL interface. Vector embeddings enable developers to determine the similarity between text strings and are foundational for building generative AI apps. Local embedding generation is now in public preview, and offer single-digit millisecond latency, eliminated throttling due to remote embedding model API's, and predictable costs, all while ensuring data remains within the security perimeter of your Postgres server.

New Accelerated Logs in Azure Database for MySQL, now generally available, are designed to boost the performance of crucial workloads within the Business-Critical service tier. This dynamic solution offers a substantial increase in throughput, a significant reduction in latency, and optimized cost efficiency.

Want to learn more about these announcements? Be sure to check out these sessions:

Simplifying application development

Microsoft is also applying AI directly in the database so you can benefit from assisted experiences that let you focus more on your application. This week at Microsoft Build, Azure Database is introducing in public preview, Copilot in Azure capabilities for Azure SQL Database. These AI-assisted experiences provide contextual self-help for management and operation of your database including complex performance tuning scenarios saving you time and effort. In addition, it allows you to use natural language to create complex SQL queries based on the schema of your database, making database interactions more intuitive.

Microsoft Copilot in Azure also extends capabilities to Azure Database for MySQL, which answers questions and troubleshoots conversationally by drawing on Microsoft Learn content and best practices. It empowers application developers with self-guided assistance to solve challenges without browsing through +100 public documentation. The new skills also enable database administrators to independently manage databases and resolve issues faster with Copilot assistance.

Attend these sessions to learn more:

Come see how Microsoft customers are powering apps with Azure Databases

We are happy to be joined on stage by organizations that have transformed the way they manage and innovate with data with Azure Databases. Attend one of the sessions below to see how TomTom is using Azure Cosmos DB to empower more modern, intelligent cars, how Scandinavian Airlines embarked on a modernization journey to Azure SQL, and Kinectfy built multi-tenant SaaS apps with Azure Cosmos DB.

Come chat with our data experts

If you’re attending Build this week, stop by the Expert Zone and meet our experts! This is your chance to ask questions, share feedback, and explore the topics that matter most.

Learn more about Azure Databases and the check below the journey that data goes on as it travels through the Microsoft Intelligent Data Platform.

Announcing the General Availability of AMD-based Confidential VMs on Azure Databricks

saira_shariff — Wed, 15 Nov 2023 16:00:01 GMT

We are excited to announce the general availability of AMD-based confidential virtual machines (VMs) for cluster nodes on Azure Databricks. Confidential VMs are part of the Azure confidential computing (ACC) portfolio and provide a trusted execution environment (TEE) for Azure Databricks clusters, protecting data while in use in memory. It is important to note that Azure already encrypts data at rest and in transit, and the introduction of confidential VMs provides an additional layer of security for sensitive data in use, helping organizations meet compliance requirements and protect their most valuable data.

By using Azure confidential computing on Azure Databricks, you gain the capability to encrypt your data end-to-end. This is valuable not only for confidential workloads but also for any scenario where you need to protect highly sensitive data residing in memory and prevent unauthorized access or tampering. The solution also supports Azure Managed HSM, a hardware security module that allows the customer to manage their own encryption keys for data at-rest, in-use, and in-transit.

To use confidential VMs on Azure Databricks, customers need to select one of the confidential VM types when creating a cluster. This type of cluster can then be used for any workload that requires the protection of highly sensitive data in memory.

For compute-optimized needs, DCasv5 confidential VMs are available, and for memory-optimized needs, ECasv5 confidential VMs can be used. These VMs are currently available in the following regions: East US, West US, North Europe, West Europe, Southeast Asia, Central India, East Asia, Switzerland North, Japan East and Italy North, and coming to additional regions soon.

Databricks partnership in Confidential Computing:

"We are thrilled to have collaborated with Microsoft to introduce Azure Databricks support for Azure confidential computing,” said David Meyer, Senior Vice President of Product, Databricks. “With the Databricks Lakehouse, customers can build an end-to-end data platform with increased confidentiality and privacy by encrypting data in use thanks to AMD-based confidential virtual machines.”

“Azure Databricks with confidential computing is our first choice for the robust protection of confidential customer data across multiple industries.

Our successful collaboration with Microsoft and Databricks enables our customers not only to unlock significant value from their data. It also emphasizes data privacy and ownership throughout the large-scale data analysis of sensitive information.”

Tune into Microsoft Ignite this week to learn more about the recent innovations with Azure confidential computing on Azure Databricks.

Learn more:

Watch the demo: Azure Databricks on Confidential VMs Overview and Demo

Read the documentation: https://aka.ms/CVM-ADB-docs.

A Data Professional's Guide to Build 2023

NatalieM — Tue, 23 May 2023 15:00:00 GMT

ChatGPT and Microsoft Copilot are stealing headlines worldwide—but data is the force that drives them both. That’s why this year at Microsoft Build, which begins on May 23rd, we’ll have lots of data and analytics-focused live segments, breakout sessions, Learn Live workshops, and more to help you learn about the latest advancements in data tech. This guide outlines all the data content at the event this year so you can get the most out of Build—and harness the newest insights to propel your organization forward.

On Day 1, join our very own Satya Nadella for an opening keynote that explores the next frontier of tech: AI. Imagine a not-so-distant future powered by data-driven AI copilots to learn how Microsoft is exploring the next generation of apps and tools. On Day 2, join Rajesh Jha and many others to discover how developers like you can leverage data to create the Microsoft 365 apps, Windows, and Microsoft Graph solutions of tomorrow. And if you’re joining us in person, close out Build on Day 3 with Mark Russinovich and Scott Hanselman to learn how to use data and AI to develop apps from scratch.

And of course, there’s lots of events on data and analytics. Check out our complete list of on-demand and live technical sessions below and click the links to get the full details on each session’s homepage. For the full agenda and more information, check out the Microsoft Build homepage—just make sure to register now to get the full attendee experience, like participating in live workshops, Q&A sessions, and more.

(Please note: schedules and speakers are subject to change.)

Core sessions and breakout sessions

Date	Type	Start, PDT	Session name	Speaker(s)
5/23/23	Digital, in person	9:00 AM PDT	Keynote: Microsoft Build Opening	Satya Nadella, Yusuf Mehdi
5/23/23	Digital, in person	9:25 AM PDT	Keynote: The era of the AI Copilot	Kevin Scott, Greg Brockman
5/23/23	Digital, in person	10:05 AM PDT	Keynote: Next generation AI for developers with the Microsoft Cloud	Scott Guthrie, Thomas Dohmke, Sarah Bird, Seth Juarez
5/24/23	Digital, in person	9:00 AM PDT	Keynote: Shaping the future of work with AI	Rajesh Jha, Panos Panay, Yina Arenas, Steven Bathiche, Cassie Breviu, Pavan Davuluri, Wamwitha Love, Shilpa Ranganathan, Archana Saseetharan
5/25/23	In person	12:00 PM PDT	Keynote: Scott and Mark learn to code	Mark Russinovich, Scott Hanselman
5/23/23	Digital, in person	4:00 PM PDT	Breakout session: Analytics in the age of AI	Arun Ulagaratchagan, Amir Netz, Patrick Baumgartner, Justyna Lucznik
5/24/25	Digital, in person	1:30 PM PDT	Breakout session: Build scalable, cloud-native apps with Kubernetes and Azure Cosmos DB	Mark Brown, Pavneet Ahluwalia, Fabiano Fernandes
5/25/23	In person	9:30 AM PDT	Breakout session: Increase developer velocity with Azure SQL Database, from data to API	Davide Mauri

On demand

Date & Type	Session name	Speaker(s)
On-demand (digital)	On-demand: Using Spark to accelerate your lakehouse architecture	Justyna Lucznik
On-demand (digital)	On-demand: Accelerate your potential with Microsoft	Swetha Manepalli
On-demand (digital)	On-demand: Empower every BI professional to do more with data	Christian Wade, Zoe Douglas
On-demand (digital)	On-demand: Modernize your enterprise data warehouse and generate value from data	Priya Sathy
On-demand (digital)	On-demand: Secure, govern, and manage your data at scale, in Power BI and Synapse	Arthi Ramasubramanian Iyer, Adi Regev, Anton Fritz
On-demand (digital)	On-demand: Sense, analyze, and generate insights with real-time analytics	James Hutton, Kevin Lam, Tzvia Gitlin
On-demand (digital)	On-demand: Modernize your data integration to enable petabyte scale analytics	Wee Hyong Tok, Shireen Bahadur
On-demand (digital)	On-demand: Models to outcomes with end-to-end data science workflows	Nellie Gustafsson
On-demand (digital)	On-demand: Transform productivity with AI experiences for analytics	Arun Ulagaratchagan, Patrick Baumgartner
On-demand (digital)	On-demand: Eliminate data siloes with the next gen data lake	Joshua Caplan, Adi Regev

Join lively discussions and product round tables after each breakout session to get the exact information you need from the experts themselves.

Discussions

Date	Type	Start, PDT	Session name	Speaker(s)
5/23/23	Digital, in person	11:30 AM PDT	Discussion: Build scalable and secure enterprise apps on OSS databases, Q&A	Dinesh Madhusoodanan, Sunil Agarwal, Charles Feddersen
5/23/23	Digital, in person	12:30 PM PDT	Discussion: Database capabilities you need for your next awesome app, Q&A	Andrew Liu, Ken Myers, Yoni Nijs, Nikisha Reyes-Grange
5/23/23	Digital, in person	1:30 PM PDT	Discussion: Azure Cosmos DB for MongoDB: Choosing the right architecture, Q&A	Gahl Levy, Jay Gordon, Thomas Kovarik
5/23/23	Digital, in person	2:45 PM PDT	Discussion: Modernize your applications on Azure SQL Managed Instance Q&A	Eric Hudson
5/24/23	Digital, in person	11:00 AM PDT	Discussion: Synapse data warehouse, Q&A	Priya Sathy, Faisal Mohamood, Bogdan Crivat
5/24/23	In person	12:15 PM PDT	Discussion: Distributed NoSQL vs. D-SQL: What to use for your cloud-native app Q&A	Charles Feddersen, Mark Brown, Estefani Arroyo
5/24/23	In person	2:45 PM PDT	Discussion: Do more on Azure SQL Database Hyperscale, Q&A	Morgan Oslake, Arvind Shyamsundar, Aditya Badramraju
5/24/23	Digital, in person	4:00 PM PDT	Discussion: All about data analytics, Q&A	Nellie Gustafsson, Mark Kromer, Justyna Lucznik, Wee Hyong Tok

Round Tables

Date	Type	Start, PDT	Session name	Speaker(s)
5/23/23	Digital	1:30 PM PDT	Round table: Azure Synapse Data Explorer and Delta Lake Integration	Avner Aharoni, Slavik Neimer, Anshul Sharma
5/23/23	Digital	5:15 PM PDT	Round table: Integrate your data with latest Data warehouse and Data Factory capabilities	Bogdan Crivat, Miquella de Boer, Sid Jayadevan, Swetha Mannepalli, Mohan Sankaran, Priya Sathy, Wee Hyong Tok
5/24/23	Digital	11:00 AM PDT	Round Table: Real-time analytics with Azure Synapse Data Explorer	Uri Barash, Tzivia Gitlin
5/24/23	Digital	5:15 PM PDT	Round table: Synapse Data Engineering, Data Science & OpenAI Roundtable	Nellie Gustafsson, Chris Finlan, Misha Desai, Avinanda Chattapadday, Martin Lee, Piero Morano, Eren Orbey, Santhosh Kumar Ravindran, Raj Rikhy, Narmeen Samad, Alexander van Grootel, Ted Vilutis

Practice makes perfect – so make sure you get as much of it as you need with Microsoft Build’s labs and demo sessions.

Labs

Date	Type	Start, PDT	Session name	Speaker(s)
5/23/23	Digital	1:30 PM PDT	Learn Live: Model data for Azure Cosmos DB for PostgreSQL	Cedric Derue, Gary Hope
5/23/23	In person	5:15 PM PDT	Lab: Democratize your data estate with the latest data warehouse (session one)	Priyanka Langade, Peri Rocha, Dharini Sundaram, Kevin Conan, Mariya Ali, Salil Kanade
5/23/23	In person	5:15 PM PDT	Lab: Analytics in the Microsoft Intelligent Data Platform DREAM lab (session one)	Sanjay Soni, Thasmika Gokal
5/24/23	In person	1:30 PM PDT	Lab: Get started with lakehouses	Paul DeCarlo, Graeme Malcolm
5/24/23	In person	2:45 PM PDT	Lab: Democratize your data estate with the latest data warehouse (session two)	Mariya Ali, Kevin Conan, Salil Kanade, Priyanka Langade, Peri Rocha, Dharini Sundaram
5/24/23	In person	4:00 PM PDT	Lab: Learn how to build a modern data stack in an hour	Emily Chen, Miguel Escobar, Estera Kot, Noelle Li, Abhishek Narain, Nikki Waghani
5/24/23	Digital	5:15 PM PDT	Learn Live: Migrate SQL workloads to Azure Managed Instances	Ajith Krishnan, Javier Villegas
5/24/23	In person	5:15 PM PDT	Lab: Analytics in the Microsoft Intelligent Data Platform DREAM lab (session two)	Sanjay Soni, Thasmika Gokal

Demos

Date	Type	Start, PDT	Session name	Speaker(s)
5/23/23	In person	11:30 AM PDT	Demo: Modernize MongoDB workloads with Azure Cosmos DB	Gahl Levy
5/23/23	In person	11:45 AM PDT	Demo: Build next gen intelligent retail with Azure OpenAI & Azure Cosmos DB	Mark Brown
5/24/23	In person	5:15 PM PDT	Demo: Further, faster with Azure Functions and Azure SQL integration	Drew Skwiers-Koballa
5/24/23	In person	5:30 PM PDT	Demo: Protect your data from tampering with ledger on Azure SQL	Pieter Vanhove

Learn More

Get even more out of Microsoft Build with these additional resources.

The Cloud Skills Challenge at Build begins May 23, 2023, at 4:00 PM PDT. Join to win your chance at a free Microsoft certification exam
Check out Collections, our curated set of training, documentation, certification, and additional resources.

Explore our data and analytics learning resources and documentation on Microsoft Learn, designed to help you upskill and grow in your career.

Introducing the Microsoft Intelligent Data Platform Partner Ecosystem

RohanKumar — Wed, 12 Oct 2022 16:00:00 GMT

In May, we introduced the Microsoft Intelligent Data platform which deeply integrates our databases, analytics, BI and data governance products into a unified platform. This platform, which is also integrated with the Microsoft Cloud, enables seamless experience and intuitive collaboration for Developers, DBAs, Data Engineers, Data Scientists, Business Analysts and Data Officers. The platform enables customers to “do more with less”, by helping them add layers of intelligence to their applications, unlock predictive insights and drive relevant action, and govern their entire data estate.

The customer response has been very positive, and we have received rich feedback on how we can further the value of this unified platform. Some common feedback we heard is that customers want the Microsoft Intelligent Platform to extend the deep integration with the Partner products they use.

Today at Microsoft Ignite, we announced the launch of a powerful new Partner Ecosystem for the Microsoft Intelligent Data Platform. Together with our partners, we deliver category-leading and cloud-native data and AI solutions integrated with the Microsoft Intelligent Data Platform. These solutions complement capabilities to address the diverse scenarios and investments of our customers.

The Microsoft Intelligent Data Platform is the natural choice for independent software vendor (ISV) partners to build and deliver integrated solutions. This is all made possible through the platform’s open and governed foundation.

The partner ecosystem enables customers across all industries to take advantage of the Microsoft Intelligent Data Platform capabilities. Together with our partners, we’ll build upon decades of experience in helping our customers digitally transform.

Thanks to partner solutions that span every layer of the Microsoft Intelligent Data Platform, we ensure our wide range of customers get a seamless, enriched, and cost-optimized experience that meets their specific needs.

The initial set of ISV partners aligned with the Microsoft Intelligent Data Platform partner ecosystem include:

Operational Databases: MongoDB as a leading NoSQL database, and Yugabyte as a cloud native NoSQL and Distributed SQL database, they enable integration into the broader enterprise data for Analytics and ML/AI and govern data estates with Microsoft Purview.

Analytics: These solutions further enhance low code/no code experiences to automate building and operating complex enterprise grade pipelines. They can govern data integration and transformation pipelines in Microsoft Purview. Examples of differentiated partner solution capabilities include:
- Informatica: Cloud-native data integration for enterprise systems
- Qlik: Data integrations for enterprise systems such as SAP, Oracle, and appliances
- Confluent: Analytics for data in motion from Apache Kafka and IoT sources
- Fivetran: Low code / No code, cloud-native, automated, reliable, and secure data pipelines
- Striim: Real-time data access with continuous, streaming data integration to analytics
- dbtLabs: SQL friendly modality for data transformation pipelines

Data Governance: Marketing leading solutions in this layer include:
- Profisee and CluedIn for Master Data Management
- Delphix data masking for pipeline data compliance
- OneTrust for privacy & security

In tandem with leading a powerful ecosystem of ISV partners to engage with our customers, we are also committed to deepening an enhanced first-party relationship with Databricks. We’re pleased to share that Azure Databricks, which aligns to the open and governed paradigm of Microsoft Intelligent Data Platform, also benefits from this partner ecosystem.

And this is just the beginning as we add more ISV partners in the coming months. We believe the Microsoft Intelligent Data Platform partner ecosystem approach is the first of its kind in the industry. Best of all, working with our valued partners, we set our customers up for success.

Learn more:

Innovate faster and achieve greater agility with the Microsoft Intelligent Data Platform

Microsoft and Databricks Partnership

Microsoft and Databricks deepen partnership for modern, cloud-native analytics

RohanKumar — Mon, 17 Oct 2022 06:04:42 GMT

Databricks has been a key partner for us in delivering a cloud native platform for Analytics in the Microsoft Intelligent Data Platform. Today, I am pleased to share that we have further strengthened our partnership with Databricks to evolve our Analytics platform to an Open and Governed Data Lakehouse foundation. This foundation will enable our customers to unify their most demanding Business Intelligence, Machine Learning, and Artificial Intelligence investments on a single data foundation for analytics viz. an open and governed data lakehouse. The foundation will also enable customers to responsibly democratize their analytics data products to accelerate digital transformation applications across their organizations.

“Databricks is a key partner for Microsoft, and together, we are delivering a modern, cloud-native data foundation in the Microsoft Intelligent Data Platform for the most demanding analytics and machine learning applications,” - Scott Guthrie, EVP, Cloud + AI, Microsoft.

Microsoft and Databricks have partnered to build this foundation in the Microsoft Intelligent Data Platform by integrating their hallmark capabilities to deliver an integrated solution for our customers.

Core to the foundation is the Open and Governed Data Lakehouse, a unified and cloud native data fabric capable of serving business intelligence, machine learning and artificial intelligence workloads at enterprise and cloud scales, without requiring organizations to invest in building, integrating, and operating point data fabrics for each.

This foundation is built by integrating the hallmark analytics capabilities in Microsoft’s Azure Synapse Analytics and Databricks, as well by integrating the governance foundations in Microsoft Purview and Databricks Unity Catalog to enable a single pane experience for Data and Analytics Governance in Microsoft Purview. The components of the integrated analytics foundation include:

Azure Data Factory in Azure Synapse Analytics for orchestrating data integration pipelines to integrate data from across hybrid, multi-cloud, SaaS, and legacy enterprise data source systems
Azure Data Explorer in Azure Synapse Analytics for real-time streaming analytics
Azure Databricks for building an open standard data lakehouse using the Delta format
Azure Machine Learning, Synapse ML, and Azure Databricks for machine learning
Azure Machine Learning, and Databricks MLflow for MLOps
Azure Synapse SQL and Databricks SQL for serverless SQL analytics
Azure Synapse SQL for a Data Warehouse modality for data serving
Power BI for Business Intelligence
Microsoft Purview and Databricks Unity Catalog Federation for Unified Data Governance in Purview spanning Operational, Analytics, and ML/AI data assets on the Microsoft Intelligent Data Platform

“This announcement between Databricks and Microsoft is a significant step forward in helping customers adopt an open, well governed data lakehouse on Azure with the entirety of the Microsoft Intelligent Data Platform capabilities. More than ever, customers are able to quickly and easily unify their data, analytics, and AI with a simple and open approach.” - Ali Ghodsi, Co-founder and CEO of Databricks

We, Microsoft and Databricks, are thrilled to be able to partner in deeply integrating our complementary strengths and capabilities, to deliver an open and governed analytics foundation in the Microsoft Intelligent Platform, to serve the most demanding analytics investments of our customers with unparalleled productivity, performance, and cost efficiencies that will enable our customers to do and achieve more with less. In a recent benchmark study, implementing an end-to-end analytics solution on Azure was up to 49% more cost efficient than on a top competitor cloud.

Learn more:

Microsoft and Databricks Partnership

Innovate faster and achieve greater agility with the Microsoft Intelligent Data Platform

Zero to Hero: Your Guide to Getting Skilled on Azure Data and AI

NatalieM — Tue, 07 Jun 2022 18:55:41 GMT

We are excited to highlight the Zero to Hero guide, our very own journey to help developers and engineers get started on Azure. We have a large collection of resources to help you ramp up in the cloud, and these guides curate all our best-in-class content in one place, making it easier to find and navigate. Technology is changing fast. With these guides you will ramp-up and level up your skill set to keep the upper hand in this competitive market. Our Zero to Hero guides ensure that you have a solid foundation as you begin to explore Azure.

The Zero to Hero guide will show you how to achieve expertise and prepare for certification in the following areas: data engineering, data science, artificial intelligence, and application development with MySQL. Within 4 weeks, regardless of where you are in your journey, we can propel you to the next level.

Each week you'll watch videos, learn from step-by-step training, and try skills for yourself with self-guided exercises. Don't forget to also sign up for the associated 30 days learning challenges to earn 50 percent off Microsoft certification.

Download the guides below and start learning today!

Are you a developer who builds apps with low-code techniques? Are you a solution architect defining the design and implementation of technology solutions in Azure? We have guides for you too!

Azure Skills Navigator for System Administrators and Solution Architects

Azure Skills Navigator for Developers

A Data and AI guide to Build 2022

NatalieM — Wed, 18 May 2022 22:47:05 GMT

Microsoft Build will begin Tuesday, May 24 at 8:30 AM PDT with our annual keynote. Attendees can then participate in live segments, breakout sessions, Learn Live workshops, and more.

We are excited to bring you this guide as your go-to map for discovering all the best content for Data and AI product releases, learning opportunities, and resources.

On Day 1 join Microsoft CEO Satya Nadella as he shares how Microsoft is creating new opportunities for developers across our platforms in our Microsoft Build Opening. Join us later that morning to hear from Microsoft’s CVP of Data, AI, and MR Jessica Hawk along with Rohan Kumar, CVP of Azure Data Engineering, in their core session: Accelerate innovation and achieve agility on a trusted integrated platform with hybrid and multicloud capabilities.

On Day 2 Microsoft CTO Kevin Scott will share how breakthrough research and ambitious AI models are empowering developers to do more during his keynote: The Future of AI Developer Tools. Shortly after, join our Into Focus: AI session with Microsoft CVP Engineering Eric Boyd as he dives into the latest technology announcements and customer stories from Azure AI.

Be sure to visit the Microsoft Build homepage for the full agenda and additional details. If you haven’t already, register now for the full event attendee experience so you can participate in live workshops and Q&A.

The complete line up of Azure Data and AI technical sessions is listed below for you to build a schedule that is unique to you and your interests. Many sessions will be available on-demand at the links below. Navigate to the session page to see full details.

Core Sessions + Breakout Sessions:

Date	Start, PDT	Session name	Speaker(s)
5/24/2022	8:30 AM	Microsoft Build Opening Keynote	Satya Nadella
5/24/2022	10:10 AM	Core session: Accelerate innovation and achieve agility on a trusted, integrated platform with hybrid and multicloud capabilities	Rohan Kumar, Jessica Hawk, Rafiq Jalal (KPMG)
5/25/2022	9:00 AM	Keynote: The Future of AI Development Tools	Kevin Scott
5/24/2022	12:30 PM	Into Focus: Preparing for the metaverse	Sam George, Alysa Taylor, Nicole Herskowitz, Andy Pratt, Matt Fleckenstein
5/25/2022	10:00 AM	Into Focus: AI	Eric Boyd, Seth Juarez
5/25/2022	11:00 AM	Breakout Session: Scaling responsible MLOps with Azure Machine Learning	Daniel Moth, Abe Omorogbe, Beatriz Stollnitz, Minsoo Thigpen
5/24/2022	1:30 PM	Breakout Session: Build and deploy containerized applications and databases for hybrid and multicloud with Azure Arc	Travis Wright, Jason Hansen,Jes Schultz, Lior Kamrat
5/25/2022	2:00 PM	Breakout Session: Building document intelligence applications with Applied AI and Cognitive Services	Ayşegül Yönet, Krishna Doss Mohan, Marco Casalaina, Neta Haiby
5/24/2022	11:30 AM	Breakout Session: Modernize your application with new innovations across SQL Server 2022 and Azure SQL	Bob Ward, Chuck Heinzelman, Drew Skwiers-Koballa, Sunetra Virdi
5/25/2022	1:00 PM	Breakout Session: Democratize your data at scale with Power BI	Arun Ulgaratchagan, Priya Sathy

Microsoft Build is also and foremost an amazing opportunity to connect with Microsoft experts. After each breakout session, you can join our Ask The Expert sessions and Product Roundtables to dive deeper into the subject and engage in live discussions.

Ask the Experts :

Date	Start, PDT	Session name	Speaker(s)
5/24/2022	12:30 PM	Ask the Experts: Modernize your applications with new features across SQL Server 2022 and Azure SQ	Bob Ward, Chuck Heinzelman, Drew Skwiers-Koballa, Miwa Monji, Sunetra Virdi
5/24/2022	2:30 PM	Ask the Experts: Build and deploy containerized applications and databases for hybrid and multicloud with Azure Arc	Dhananjay Mahajan, Dinakar Nethi, Jes Schultz, Lior Kamrat
5/25/2022	12:00 PM	Ask the Expert: Scaling responsible MLOps with Azure Machine Learning	Christian Gero, Minsoo Thigpen, Ilya Matiach, Richard Edgar
5/25/2022	1:00 PM	Ask the Experts: Deploy modern containerized apps and cloud native databases at scale	Anthony Chu, Daria Grigoriu, Deborah Chen, Kendall Roden
5/25/2022	2:00 PM	Ask the Experts: Democratizing your data at scale with Power BI	Charles Webb, Dharini Sundaram, Gautam Bharti, Priyanka Langade
5/25/2022	12:00 PM	Roundtable: Azure Machine Learning – Responsible AI and MLOps	Steve Sweetman, Ahmet Gyger, Abe Omorogbe, Cody Peterson
Post-event June 16	9:30 AM	Ask the Expert: Building AI - infused document processing applications	Ayşegül Yönet, Krishna Doss Mohan, Marco Casalaina

Take advantage of our guided, online learning workshops where our Microsoft experts will walk you through everything from analytics and databases to AI and Machine Learning.

Learn Live sessions and Intro to Tech Skills:

Date	Start, PDT	Session name	Speaker(s)
5/25/2022	2:00 PM	Azure Cosmos DB: Learn how to enable analytics over real-time operational data with Azure Synapse Link	Gary Hope and Rodrigo Souza
5/24/2022	1:30 PM	Develop applications with Azure Database for MySQL - Flexible Server	Marko Hotti and Shreya R. Aithal
5/25/2022	1:00 PM	Introduction to Azure Arc enabled Kubernetes	Orin Thomas, Vinicius Apolinario
5/24/2022	12:30 PM	Develop IoT solutions with Azure SQL Database	Anna Hoffman and Silvano Coriani
5/25/2022	6:00 AM (3:00 PM Central Europe Time)	Extract data from forms with Form Recognizer	David Glover and Vinod Kurpad
5/25/2022	10:00 AM	How to develop custom object models with data labeling tools and AutoML	Paul DeCarlo and Cassie Dreviu
5/25/2022	9:00 AM	Intro to Tech Skills: A guided journey into AI	Carlotta Castelluccio and Dmitry Shoshnikov
5/25/2022	2:00 PM	Intro to Tech Skills: The New Developer’s Guide to the Cloud	Someleze Diko, Nitya Narasimhan,Christoffer Noring

To go more in depth into specific topics, explore our on-demand recordings.

On-Demand recordings:

Date	Session name	Speaker(s)
On-demand	Analytics and Operational Data	Chuck Heinzelman
On-demand	Azure SQL and Azure Functions: Integration with SQL bindings	Drew Skwiers-Koballa

Learn more

The Cloud Skills Challenge is back at Build! Earn a free Microsoft Certification exam by completing a collection of interactive Microsoft Learn modules.
Interested in becoming a Data or AI engineer? Get started with one of our structured, self-paced courses to take you from zero to hero in less than an hour each day:
Discover more Azure Data and AI learning resources and documentation on Microsoft Learn

SQL DB maintenance activity using Automation accounts for Azure SQL DBs(Different scenarios)

Swati_Srivastava-MSFT — Mon, 13 Jun 2022 04:45:37 GMT

Infrastructure:

Multiple Azure SQL DB instances
Multiple databases in total
Multiple instances consist of multiple databases
Different subscriptions exist for different environments (Dev, UAT, Prod)

Requirement:

Schedule maintenance jobs for all instances
All of them should run parallelly
No dependency of one job on another
Per subscription one automation account can be created

Overview of Steps to be followed:

Create Azure automation account
Import SQLServer module
Add Credentials to access SQL DB
Add a runbook to run the maintenance
Schedule task

Create new automation account

Choose Automation Accounts from the results:

Click "create"
Fill the form, choose a name for your automation account, and choose in which resource group it will be placed.
Click "create" and wait for the account to be created.

Import SQLServer module

Click on "Modules" at the left options panel, and then click on "Browse Gallery" and search for "SQLServer"

Choose "SqlServer" by matteot_msft

Then click on "import" and the "OK"
Wait for the import to complete

Add Credentials to access SQL DB

This will use secure way to hold login name and password that will be used to access Azure SQL DB.

Note: You can skip this and use it as clear text if you like to use clear text skip to the next step.

Under "Shared Resources" click on credentials

Then click on "Add Credential"
Type "SQLCred"(or whatever name you want to provide for your credentials) as the name of the credential.
In the username field type the SQL Login that will be used for maintenance and its password.
Click "Create"

Add a runbook to run the maintenance

Click on "runbooks" at the left panel and then click "add a runbook"

Choose "create a new runbook" and then give it a name and choose "Powershell" as the type of the runbook and then click on "create"

Copy and paste the following row to the new runbook (in case of 1 Azure SQL Server and 1 Azure SQL DB).
Make sure you change your database properties.

(Thanks to my colleague Vasukhi K S to help me with the code testing in different scenarios)

$AzureSQLServerName = "<ServerName>" $AzureSQLDatabaseName = "<DatabaseName>" $AzureSQLServerName = $AzureSQLServerName + ".database.windows.net" $Cred = Get-AutomationPSCredential -Name "SQLCred" $SQLOutput = $(Invoke-Sqlcmd -ServerInstance $AzureSQLServerName -Username $Cred.UserName -Password $Cred.GetNetworkCredential().Password -Database $AzureSQLDatabaseName -Query "exec [dbo].[AzureSQLMaintenance] @Operation='all' ,@LogToTable=1" -QueryTimeout 65535 -ConnectionTimeout 60 -Verbose) 4>&1 Write-Output $SQLOutput

Click on Publish and confirm.
Create a new runbook again
Copy and paste the following row to the new runbook (in case of 1 Azure SQL Server and multiple Azure SQL DBs).

$ServerInstance = "<ServerName>" ## instance name $Cred = Get-AutomationPSCredential -Name "SQLCred" $Databases = Invoke-SqlCmd -ServerInstance $ServerInstance -Username $Cred.UserName -Password $Cred.GetNetworkCredential().Password -Database master -Query "SELECT [name] AS [Database] FROM sys.databases WHERE name not in ('master') ORDER BY 1 DESC;" foreach ($DB in $Databases) { Write-Output "Processing $($DB.Database)..." $SQLOutput = Invoke-SqlCmd -ServerInstance $ServerInstance -Username $Cred.UserName -Password $Cred.GetNetworkCredential().Password -Database $DB.Database -Query "EXECUTE dbo.IndexOptimize @Databases = $($DB.Database) ,@MinNumberOfPages = 1000,@FragmentationLow = NULL,@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',@FragmentationLevel1 = 5,@FragmentationLevel2 = 30,@logToTable = 'Y';" -OutputSqlErrors:$true -Verbose } Write-Output “Complete”

For running these PowerShell scripts on the databases, we should first have the required objects created in the database. Please refer to following pointers for creation of objects:

In case of 1 Server 1 DB

Create stored procedure “AzureSQLDBMaintenance” as per the attached script below:

/****** Object: StoredProcedure [dbo].[AzureSQLMaintenance] ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[AzureSQLMaintenance] ( @operation nvarchar(10) = null, @mode nvarchar(10) = 'smart', @ResumableIndexRebuild bit = 0, @RebuildHeaps bit = 0, @LogToTable bit = 0, @debug nvarchar = 'none' ) as begin set nocount on; --------------------------------------------- --- Varialbles and pre conditions check --------------------------------------------- set quoted_identifier on; declare @idxIdentifierBegin char(1), @idxIdentifierEnd char(1); declare @statsIdentifierBegin char(1), @statsIdentifierEnd char(1); declare @msg nvarchar(max); declare @minPageCountForIndex int = 40; declare @OperationTime datetime2 = sysdatetime(); declare @KeepXOperationInLog int =3; declare @ScriptHasAnError int = 0; declare @ResumableIndexRebuildSupported int; declare @indexStatsMode sysname; declare @LowFragmentationBoundry int = 5; declare @HighFragmentationBoundry int = 30; /* make sure parameters selected correctly */ set @operation = lower(@operation) set @mode = lower(@mode) set @debug = lower(@debug) /* any value at this time will produce the temp tables as permanent tables */ if @mode not in ('smart','dummy') set @mode = 'smart' --------------------------------------------- --- Begin --------------------------------------------- if @operation not in ('index','statistics','all') or @operation is null begin raiserror('@operation (varchar(10)) [mandatory]',0,0) raiserror(' Select operation to perform:',0,0) raiserror(' "index" to perform index maintenance',0,0) raiserror(' "statistics" to perform statistics maintenance',0,0) raiserror(' "all" to perform indexes and statistics maintenance',0,0) raiserror(' ',0,0) raiserror('@mode(varchar(10)) [optional]',0,0) raiserror(' optionaly you can supply second parameter for operation mode: ',0,0) raiserror(' "smart" (Default) using smart decision about what index or stats should be touched.',0,0) raiserror(' "dummy" going through all indexes and statistics regardless thier modifications or fragmentation.',0,0) raiserror(' ',0,0) raiserror('@ResumableIndexRebuild(bit) [optional]',0,0) raiserror(' optionaly you can choose to rebuild indexes as resumable operation: ',0,0) raiserror(' "0" (Default) using non resumable index rebuild.',0,0) raiserror(' "1" using resumable index rebuild when it is supported.',0,0) raiserror(' ',0,0) raiserror('@RebuildHeaps(bit) [optional]',0,0) raiserror(' Logging option: @LogToTable(bit)',0,0) raiserror(' 0 - (Default) do not log operation to table',0,0) raiserror(' 1 - log operation to table',0,0) raiserror(' for logging option only 3 last execution will be kept by default. this can be changed by easily in the procedure body.',0,0) raiserror(' Log table will be created automatically if not exists.',0,0) raiserror(' ',0,0) raiserror('@LogToTable(bit) [optional]',0,0) raiserror(' Rebuild HEAPS to fix forwarded records issue on tables with no clustered index',0,0) raiserror(' 0 - (Default) do not rebuild heaps',0,0) raiserror(' 1 - Rebuild heaps based on @mode parameter, @mode=dummy will rebuild all heaps',0,0) raiserror(' ',0,0) raiserror('Example:',0,0) raiserror(' exec AzureSQLMaintenance ''all'', @LogToTable=1',0,0) end else begin --------------------------------------------- --- Prepare log table --------------------------------------------- /* Prepare Log Table */ if object_id('AzureSQLMaintenanceLog') is null and @LogToTable=1 begin create table AzureSQLMaintenanceLog (id bigint primary key identity(1,1), OperationTime datetime2, command varchar(4000),ExtraInfo varchar(4000), StartTime datetime2, EndTime datetime2, StatusMessage varchar(1000)); end --------------------------------------------- --- Resume operation --------------------------------------------- /*Check is there is operation to resume*/ if OBJECT_ID('AzureSQLMaintenanceCMDQueue') is not null begin if /*resume information exists*/ exists(select * from AzureSQLMaintenanceCMDQueue where ID=-1) begin /*resume operation confirmed*/ set @operation='resume' -- set operation to resume, this can only be done by the proc, cannot get this value as parameter -- restore operation parameters select top 1 @LogToTable = JSON_VALUE(ExtraInfo,'$.LogToTable') ,@mode = JSON_VALUE(ExtraInfo,'$.mode') ,@ResumableIndexRebuild = JSON_VALUE(ExtraInfo,'$.ResumableIndexRebuild') from AzureSQLMaintenanceCMDQueue where ID=-1 raiserror('-----------------------',0,0) set @msg = 'Resuming previous operation' raiserror(@msg,0,0) raiserror('-----------------------',0,0) end else begin -- table [AzureSQLMaintenanceCMDQueue] exist but resume information does not exists -- this might happen in case execution intrupted between collecting index & ststistics information and executing commands. -- to fix that we drop the table now, it will be recreated later DROP TABLE [AzureSQLMaintenanceCMDQueue]; end end --------------------------------------------- --- Report operation parameters --------------------------------------------- /*Write operation parameters*/ raiserror('-----------------------',0,0) set @msg = 'set operation = ' + @operation; raiserror(@msg,0,0) set @msg = 'set mode = ' + @mode; raiserror(@msg,0,0) set @msg = 'set ResumableIndexRebuild = ' + cast(@ResumableIndexRebuild as varchar(1)); raiserror(@msg,0,0) set @msg = 'set RebuildHeaps = ' + cast(@RebuildHeaps as varchar(1)); raiserror(@msg,0,0) set @msg = 'set LogToTable = ' + cast(@LogToTable as varchar(1)); raiserror(@msg,0,0) raiserror('-----------------------',0,0) end if @LogToTable=1 insert into AzureSQLMaintenanceLog values(@OperationTime,null,null,sysdatetime(),sysdatetime(),'Starting operation: Operation=' +@operation + ' Mode=' + @mode + ' Keep log for last ' + cast(@KeepXOperationInLog as varchar(10)) + ' operations' ) -- create command queue table, if there table exits then we resume operation in earlier stage. if @operation!='resume' create table AzureSQLMaintenanceCMDQueue (ID int identity primary key,txtCMD nvarchar(max),ExtraInfo varchar(max)) --------------------------------------------- --- Check if engine support resumable index operation --------------------------------------------- if @ResumableIndexRebuild=1 begin if cast(SERVERPROPERTY('EngineEdition')as int)>=5 or cast(SERVERPROPERTY('ProductMajorVersion')as int)>=14 begin set @ResumableIndexRebuildSupported=1; end else begin set @ResumableIndexRebuildSupported=0; set @msg = 'Resumable index rebuild is not supported on this database' raiserror(@msg,0,0) if @LogToTable=1 insert into AzureSQLMaintenanceLog values(@OperationTime,null,null,sysdatetime(),sysdatetime(),@msg) end end --------------------------------------------- --- Index maintenance --------------------------------------------- if @operation in('index','all') begin /**/ if @mode='smart' and @RebuildHeaps=1 set @indexStatsMode = 'SAMPLED' else set @indexStatsMode = 'LIMITED' raiserror('Get index information...(wait)',0,0) with nowait; /* Get Index Information */ /* using inner join - this eliminates indexes that we cannot maintain such as indexes on functions */ select idxs.[object_id] ,ObjectSchema = OBJECT_SCHEMA_NAME(idxs.object_id) ,ObjectName = object_name(idxs.object_id) ,IndexName = idxs.name ,idxs.type ,idxs.type_desc ,i.avg_fragmentation_in_percent ,i.page_count ,i.index_id ,i.partition_number ,i.avg_page_space_used_in_percent ,i.record_count ,i.ghost_record_count ,i.forwarded_record_count ,null as OnlineOpIsNotSupported ,null as ObjectDoesNotSupportResumableOperation ,0 as SkipIndex ,replicate('',128) as SkipReason into #idxBefore from sys.indexes idxs inner join sys.dm_db_index_physical_stats(DB_ID(),NULL, NULL, NULL ,@indexStatsMode) i on i.object_id = idxs.object_id and i.index_id = idxs.index_id where idxs.type in (0 /*HEAP*/,1/*CLUSTERED*/,2/*NONCLUSTERED*/) and (alloc_unit_type_desc = 'IN_ROW_DATA' /*avoid LOB_DATA or ROW_OVERFLOW_DATA*/ or alloc_unit_type_desc is null /*for ColumnStore indexes*/) and OBJECT_SCHEMA_NAME(idxs.object_id) != 'sys' and idxs.is_disabled=0 order by i.avg_fragmentation_in_percent desc, i.page_count desc -- mark indexes XML,spatial and columnstore not to run online update update #idxBefore set OnlineOpIsNotSupported=1 where [object_id] in (select [object_id] from #idxBefore where [type]=3 /*XML Indexes*/) -- mark clustered indexes for tables with 'text','ntext','image' to rebuild offline update #idxBefore set OnlineOpIsNotSupported=1 where index_id=1 /*clustered*/ and [object_id] in ( select object_id from sys.columns c join sys.types t on c.user_type_id = t.user_type_id where t.name in ('text','ntext','image') ) -- do all as offline for box edition that does not support online update #idxBefore set OnlineOpIsNotSupported=1 where /* Editions that does not support online operation in case this has been used with on-prem server */ convert(varchar(100),serverproperty('Edition')) like '%Express%' or convert(varchar(100),serverproperty('Edition')) like '%Standard%' or convert(varchar(100),serverproperty('Edition')) like '%Web%' -- Do non resumable operation when index contains computed column or timestamp data type update idx set ObjectDoesNotSupportResumableOperation=1 from #idxBefore idx join sys.index_columns ic on idx.object_id = ic.object_id and idx.index_id=ic.index_id join sys.columns c on ic.object_id=c.object_id and ic.column_id=c.column_id where c.is_computed=1 or system_type_id=189 /*TimeStamp column*/ -- set SkipIndex=1 if conditions for maintenance are not met -- this is used to idntify if stats need to be updated or not. -- Check#1 - if table is too small update #idxBefore set SkipIndex=1,SkipReason='Maintenance is not needed as table is too small' where ( /*Table is small*/ (page_count<=@minPageCountForIndex) ) and @mode != 'dummy' /*for Dummy mode we do not want to skip anything */ -- Check#2 - if table is not small and fragmentation % is too low update #idxBefore set SkipIndex=1,SkipReason='Maintenance is not needed as fragmentation % is low' where ( /*Table is big enough - but fragmentation is less than 5%*/ (page_count>@minPageCountForIndex and avg_fragmentation_in_percent<@LowFragmentationBoundry) ) and @mode != 'dummy' /*for Dummy mode we do not want to skip anything */ -- Skip columnstore indexes update #idxBefore set SkipIndex=1,SkipReason='Columnstore index' where ( type in ( 5/*Clustered columnstore index*/, 6/*Nonclustered columnstore index*/ ) ) and @mode != 'dummy' /*for Dummy mode we do not want to skip anything */ raiserror('---------------------------------------',0,0) with nowait raiserror('Index Information:',0,0) with nowait raiserror('---------------------------------------',0,0) with nowait select @msg = count(*) from #idxBefore set @msg = 'Total Indexes: ' + @msg raiserror(@msg,0,0) with nowait select @msg = avg(avg_fragmentation_in_percent) from #idxBefore where page_count>@minPageCountForIndex set @msg = 'Average Fragmentation: ' + @msg raiserror(@msg,0,0) with nowait select @msg = sum(iif(avg_fragmentation_in_percent>=@LowFragmentationBoundry and page_count>@minPageCountForIndex,1,0)) from #idxBefore set @msg = 'Fragmented Indexes: ' + @msg raiserror(@msg,0,0) with nowait raiserror('---------------------------------------',0,0) with nowait /* Choose the identifier to be used based on existing object name this came up from object that contains '[' within the object name such as "EPK[export].[win_sourceofwealthbpf]" as index name if we use '[' as identifier it will cause wrong identifier name */ if exists( select 1 from #idxBefore where IndexName like '%[%' or IndexName like '%]%' or ObjectSchema like '%[%' or ObjectSchema like '%]%' or ObjectName like '%[%' or ObjectName like '%]%' ) begin set @idxIdentifierBegin = '"' set @idxIdentifierEnd = '"' end else begin set @idxIdentifierBegin = '[' set @idxIdentifierEnd = ']' end /* create queue for indexes */ insert into AzureSQLMaintenanceCMDQueue(txtCMD,ExtraInfo) select txtCMD = 'ALTER INDEX ' + @idxIdentifierBegin + IndexName + @idxIdentifierEnd + ' ON '+ @idxIdentifierBegin + ObjectSchema + @idxIdentifierEnd +'.'+ @idxIdentifierBegin + ObjectName + @idxIdentifierEnd + ' ' + case when ( avg_fragmentation_in_percent between @LowFragmentationBoundry and @HighFragmentationBoundry and @mode = 'smart')/* index fragmentation condition */ or (@mode='dummy' and type in (5,6)/* Columnstore indexes in dummy mode -> reorganize them */ ) then 'REORGANIZE;' when OnlineOpIsNotSupported=1 then 'REBUILD WITH(ONLINE=OFF,MAXDOP=1);' when ObjectDoesNotSupportResumableOperation=1 or @ResumableIndexRebuildSupported=0 or @ResumableIndexRebuild=0 then 'REBUILD WITH(ONLINE=ON,MAXDOP=1);' else 'REBUILD WITH(ONLINE=ON,MAXDOP=1, RESUMABLE=ON);' end , ExtraInfo = case when type in (5,6) then 'Dummy mode, reorganize columnstore indexes' else 'Current fragmentation: ' + format(avg_fragmentation_in_percent/100,'p')+ ' with ' + cast(page_count as nvarchar(20)) + ' pages' end from #idxBefore where SkipIndex=0 and type != 0 /*Avoid HEAPS*/ --------------------------------------------- --- Index - Heaps --------------------------------------------- /* create queue for heaps */ if @RebuildHeaps=1 begin insert into AzureSQLMaintenanceCMDQueue(txtCMD,ExtraInfo) select txtCMD = 'ALTER TABLE ' + @idxIdentifierBegin + ObjectSchema + @idxIdentifierEnd +'.'+ @idxIdentifierBegin + ObjectName + @idxIdentifierEnd + ' REBUILD;' , ExtraInfo = 'Rebuilding heap - forwarded records ' + cast(forwarded_record_count as varchar(100)) + ' out of ' + cast(record_count as varchar(100)) + ' record in the table' from #idxBefore where type = 0 /*heaps*/ and ( @mode='dummy' or (forwarded_record_count/nullif(record_count,0)>0.3) /* 30% of record count */ or (forwarded_record_count>105000) /* for tables with > 350K rows dont wait for 30%, just run yje maintenance once we reach the 100K forwarded records */ ) end /* create queue for heaps */ end --------------------------------------------- --- Statistics maintenance --------------------------------------------- if @operation in('statistics','all') begin /*Gets Stats for database*/ raiserror('Get statistics information...',0,0) with nowait; select ObjectSchema = OBJECT_SCHEMA_NAME(s.object_id) ,ObjectName = object_name(s.object_id) ,s.object_id ,s.stats_id ,StatsName = s.name ,sp.last_updated ,sp.rows ,sp.rows_sampled ,sp.modification_counter , i.type , i.type_desc ,0 as SkipStatistics into #statsBefore from sys.stats s cross apply sys.dm_db_stats_properties(s.object_id,s.stats_id) sp left join sys.indexes i on sp.object_id = i.object_id and sp.stats_id = i.index_id where OBJECT_SCHEMA_NAME(s.object_id) != 'sys' and /*Modified stats or Dummy mode*/(isnull(sp.modification_counter,0)>0 or @mode='dummy') order by sp.last_updated asc /*Remove statistics if it is handled by index rebuild / reorginize I am removing statistics based on existance on the index in the list because for indexes with <5% changes we do not apply any action - therefore we might decide to update statistics */ if @operation= 'all' update _stats set SkipStatistics=1 from #statsBefore _stats join #idxBefore _idx on _idx.ObjectSchema = _stats.ObjectSchema and _idx.ObjectName = _stats.ObjectName and _idx.IndexName = _stats.StatsName where _idx.SkipIndex=0 /*Skip statistics for Columnstore indexes*/ update #statsBefore set SkipStatistics=1 where type in (5,6) /*Column store indexes*/ /*Skip statistics if resumable operation is pause on the same object*/ if @ResumableIndexRebuildSupported=1 begin update _stats set SkipStatistics=1 from #statsBefore _stats join sys.index_resumable_operations iro on _stats.object_id=iro.object_id and _stats.stats_id=iro.index_id end raiserror('---------------------------------------',0,0) with nowait raiserror('Statistics Information:',0,0) with nowait raiserror('---------------------------------------',0,0) with nowait select @msg = sum(modification_counter) from #statsBefore set @msg = 'Total Modifications: ' + @msg raiserror(@msg,0,0) with nowait select @msg = sum(iif(modification_counter>0,1,0)) from #statsBefore set @msg = 'Modified Statistics: ' + @msg raiserror(@msg,0,0) with nowait raiserror('---------------------------------------',0,0) with nowait /* Choose the identifier to be used based on existing object name */ if exists( select 1 from #statsBefore where StatsName like '%[%' or StatsName like '%]%' or ObjectSchema like '%[%' or ObjectSchema like '%]%' or ObjectName like '%[%' or ObjectName like '%]%' ) begin set @statsIdentifierBegin = '"' set @statsIdentifierEnd = '"' end else begin set @statsIdentifierBegin = '[' set @statsIdentifierEnd = ']' end /* create queue for update stats */ insert into AzureSQLMaintenanceCMDQueue(txtCMD,ExtraInfo) select txtCMD = 'UPDATE STATISTICS '+ @statsIdentifierBegin + ObjectSchema + +@statsIdentifierEnd + '.'+@statsIdentifierBegin + ObjectName + @statsIdentifierEnd +' (' + @statsIdentifierBegin + StatsName + @statsIdentifierEnd + ') WITH FULLSCAN;' , ExtraInfo = '#rows:' + cast([rows] as varchar(100)) + ' #modifications:' + cast(modification_counter as varchar(100)) + ' modification percent: ' + format((1.0 * modification_counter/ rows ),'p') from #statsBefore where SkipStatistics=0; end if @operation in('statistics','index','all','resume') begin declare @SQLCMD nvarchar(max); declare @ID int; declare @ExtraInfo nvarchar(max); /*Print debug information in case debug is activated */ if @debug!='none' begin drop table if exists idxBefore drop table if exists statsBefore drop table if exists cmdQueue if object_id('tempdb..#idxBefore') is not null select * into idxBefore from #idxBefore if object_id('tempdb..#statsBefore') is not null select * into statsBefore from #statsBefore if object_id('tempdb..AzureSQLMaintenanceCMDQueue') is not null select * into cmdQueue from AzureSQLMaintenanceCMDQueue end /*Save current execution parameters in case resume is needed */ if @operation!='resume' begin set @ExtraInfo = (select top 1 LogToTable = @LogToTable, operation=@operation, operationTime=@OperationTime, mode=@mode, ResumableIndexRebuild = @ResumableIndexRebuild from sys.tables for JSON path, WITHOUT_ARRAY_WRAPPER) set identity_insert AzureSQLMaintenanceCMDQueue on insert into AzureSQLMaintenanceCMDQueue(ID,txtCMD,ExtraInfo) values(-1,'parameters to be used by resume code path',@ExtraInfo) set identity_insert AzureSQLMaintenanceCMDQueue off end --------------------------------------------- --- Executing commands --------------------------------------------- /* needed to rebuild indexes on comuted columns if ANSI_WARNINGS is set to OFF we might get the followin exception: Msg 1934, Level 16, State 1, Line 2 ALTER INDEX failed because the following SET options have incorrect settings: 'ANSI_WARNINGS'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations. */ SET ANSI_WARNINGS ON; raiserror('Start executing commands...',0,0) with nowait declare @T table(ID int, txtCMD nvarchar(max),ExtraInfo nvarchar(max)); while exists(select * from AzureSQLMaintenanceCMDQueue where ID>0) begin update top (1) AzureSQLMaintenanceCMDQueue set txtCMD=txtCMD output deleted.* into @T where ID>0; select top (1) @ID = ID, @SQLCMD = txtCMD, @ExtraInfo=ExtraInfo from @T raiserror(@SQLCMD,0,0) with nowait if @LogToTable=1 insert into AzureSQLMaintenanceLog values(@OperationTime,@SQLCMD,@ExtraInfo,sysdatetime(),null,'Started') begin try exec(@SQLCMD) if @LogToTable=1 update AzureSQLMaintenanceLog set EndTime = sysdatetime(), StatusMessage = 'Succeeded' where id=SCOPE_IDENTITY() end try begin catch set @ScriptHasAnError=1; set @msg = 'FAILED : ' + CAST(ERROR_NUMBER() AS VARCHAR(50)) + ERROR_MESSAGE(); raiserror(@msg,0,0) with nowait if @LogToTable=1 update AzureSQLMaintenanceLog set EndTime = sysdatetime(), StatusMessage = @msg where id=SCOPE_IDENTITY() end catch delete from AzureSQLMaintenanceCMDQueue where ID = @ID; delete from @T end drop table AzureSQLMaintenanceCMDQueue; end --------------------------------------------- --- Clean old records from log table --------------------------------------------- if @LogToTable=1 begin delete from AzureSQLMaintenanceLog from AzureSQLMaintenanceLog L join (select distinct OperationTime from AzureSQLMaintenanceLog order by OperationTime desc offset @KeepXOperationInLog rows) F ON L.OperationTime = F.OperationTime insert into AzureSQLMaintenanceLog values(@OperationTime,null,cast(@@rowcount as varchar(100))+ ' rows purged from log table because number of operations to keep is set to: ' + cast( @KeepXOperationInLog as varchar(100)),sysdatetime(),sysdatetime(),'Cleanup Log Table') end if @ScriptHasAnError=0 raiserror('Done',0,0) if @LogToTable=1 insert into AzureSQLMaintenanceLog values(@OperationTime,null,null,sysdatetime(),sysdatetime(),'End of operation') if @ScriptHasAnError=1 raiserror('Script has errors - please review the log.',16,1) end GO

In case of 1 Server multiple DBs

Create stored procedure “IndexOptimize” as per Ola Hallengren database maintenance scripts.
Create table “CommandLog” for storing output as per Ola Hallengren database maintenance scripts.
Create stored procedure “CommandExecute” to execute and log commands as per the Ola Hallengren database maintenance scripts.

Note: You will have to create these objects for each database.

Schedule task

Click on Schedules
Click on "Add a schedule" and follow the instructions to choose existing schedule or create a new schedule.

Choose a time when the application has less or no workload, as running the maintenance might impact on performance while it's executing.

Monitoring

You can monitor the success of the job by reviewing the Automation overview page

You can click on a specific execution and get more details about it including the output of the script

For checking the output, you can also check the tables created for output:

Azure Data Factory CI-CD using YAML template

Akshay_Attota — Thu, 17 Mar 2022 10:54:09 GMT

Purpose of this Blog

Defined the end to end Git integration and Deployment flow using YAML template from a project implementation perspective
This can be easily implemented as you just need to include this YAML template and do a few changes as this has been successfully implemented for Deployments across some projects.

Developer Workflow(CI/CD)

Integration of Code from Data Factory UI(Continuous Integration)

1. A sandbox Data Factory is created for development of data pipelines with Datasets and Linked Services.
The Data Factory is configured with Azure Dev-ops Git.(Collaboration and publish branch) and the root folder where the data factory code is committed

2. A feature branch is created based on the main/collaboration branch for development. The branch in the Data Factory UI is changed to feature branch.

3. After the developer tests the pipelines and is satisfied with the changes , a pull request is raised to merge
the changes with the main/collaboration branch

4. After the PR gets approved by the concerned product leads, the changes made in feature branch are
merged into main branch.

5. The branch is changed to main branch in Data Factory UI . The changes are published to main branch by
clicking on publish button.

6. The changes are reflected in ARM template located in adf_publish branch

Note: If some parameters are missing in the arm template follow the steps below :

I. then we should go to manage in ADF UI

II. click on the ARM template

III. click on Edit parameter configuration in the DataFactory UI and include parameters that were missing in the ARM template in the json configuration file.

IV. We need to include this json configuration file in the main branch at root folder configured in Git configuration, let us consider we keep it in 'src/DataFactory/{DataFactoryName}/arm-template-parameters-definition.json'

- Follow this documentation on how to use the use custom parameters with the Resource Manager template : Custom parameters in a Resource Manager template - Azure Data Factory | Microsoft Docs

7. A Release pipeline is built in YAML to deploy the Data Factory using ARM template(We need to give
location of ARM template file) to Dev ,QA ,UAT and Production environments

Automated publish of ADF ARM Template :

The "Automated publish" improvement takes the validate all and export Azure Resource Manager (ARM) template functionality from the ADF UI and makes the logic consumable via a publicly available npm package @microsoft/azure-data-factory-utilities. This allows you to programmatically trigger these actions instead of going to the ADF UI and do a button click 'Publish'. This will give your CI/CD pipelines a truer continuous integration experience.(Quoted from blog)
Please follow this tech community blog for more details : Automated publish improvement in ADF's CI/CD flow - Microsoft Tech Community
Please follow this Microsoft Documentation for the same : Automated publishing for continuous integration and delivery - Azure Data Factory | Microsoft Docs

YAML Release pipeline(Continuous Deployment)

Run the release pipeline for the specified target environment.
This will download the previously generated ARM template. It will also download secure connection strings
from Azure Key-Vault. .Then it will deploy to your
Target Data Factory using ARM template deployment.
The code below shows how to run your release agent on a Microsoft hosted agent

stages: - stage: Release displayName: Release stage jobs: - job: Release displayName: Release job pool: vmImage: 'Windows-2019'

Trigger:

This Pipeline will be triggered based on the changes made in adf_publish branch

trigger: branches: include: - adf_publish

Resources:

The branch where the ARM template is located is mentioned.
Referring to the previous point where it is mentioned that the ARM template will be picked from adf_publish (Publish) branch
Example:

resources: repositories: - repository: <repo name> type: git name: <repo name> ref: adf_publish

Steps:
1. Deployment Variables (variable declared)

#Deployment variables variables: KeyVaultName: <keyvaultname> azureSubscription: <ServiceConnection> SourceDataFactoryName : <Source Data Factory name from which code is published> DeployDataFactoryName : <Target Data Factory Name> DeploymentResourceGroupName : <Target Resource Group Name>

2. Keyvault task to fetch the secrets(parameters to override ARM paramters) (PowerShell Task):

- task: AzureKeyVault@1 inputs: azureSubscription: '$(azureSubscription)' KeyVaultName: $(KeyVaultName) SecretsFilter: '<secrets needed for overriding ARM parameters>' RunAsPreJob: true - checkout: <Repo Name>

3. Stopping the triggers of Target Data Factory before Deployment(Powershell Task)
Script:

- task: AzurePowerShell@5 displayName: Stop Triggers inputs: azureSubscription: '$(azureSubscription)' ScriptType: 'InlineScript' Inline: $triggersADF = Get-AzDataFactoryV2Trigger -DataFactoryName "$(DeployDataFactoryName)" -ResourceGroupName "$(DeploymentResourceGroupName)"; $triggersADF | ForEach-Object { Stop-AzDataFactoryV2Trigger – ResourceGroupName "$(DeploymentResourceGroupName)" -DataFactoryName "$(DeployDataFactoryName)" -Name $_.name -Force } azurePowerShellVersion: 'LatestVersion'

4. Deploying the ARM template to the Target Data Factory by passing some values to the parameters in
Incremental Mode. (Powershell Task)
Script:

- task: AzurePowerShell@5 displayName: Deploy ADF inputs: azureSubscription: '$(azureSubscription)' ScriptType: 'InlineScript' Inline: 'New-AzResourceGroupDeployment -ResourceGroupName "$(DeploymentResourceGroupName)" -TemplateParameterFile "$(System.DefaultWorkingDirectory)/$(SourceDataFactoryName)/ARMTemplateParametersForFactory.json" -TemplateFile "$(System.DefaultWorkingDirectory)/$(SourceDataFactoryName)/ARMTemplateForFactory.json" -factoryName "$(DeployDataFactoryName)" #<parameter-overridden> : <value-to-be-overridden> there are parameters in arm template and overriden by key vault secrets #<parameter-overridden> : <value-to-be-overridden> -Mode "Incremental"' azurePowerShellVersion: 'LatestVersion'

5. Starting the Triggers after Successful Deployment(Powershell Task)
Script:

- task: AzurePowerShell@5 displayName: Restart inputs: azureSubscription: '$(azureSubscription)' ScriptType: 'InlineScript' Inline: $triggersADF = Get-AzDataFactoryV2Trigger -DataFactoryName "$(DeployDataFactoryName)" -ResourceGroupName "$(DeploymentResourceGroupName)"; $triggersADF | ForEach-Object { Start-AzDataFactoryV2Trigger -ResourceGroupName "$(DeploymentResourceGroupName)" -DataFactoryName "$(DeployDataFactoryName)" -Name $_.name -Force } azurePowerShellVersion: 'LatestVersion'

Full ADF CD Template:

name: Release-$(rev:r) trigger: branches: include: - adf_publish resources: repositories: - repository: <repo name> type: git name: <repo name> ref: adf_publish variables: KeyVaultName: <keyvaultname> azureSubscription: <ServiceConnection> SourceDataFactoryName : <Source Data Factory name from which code is published> DeployDataFactoryName : <Target Data Factory Name> DeploymentResourceGroupName : <Target Resource Group Name> stages: - stage: Release displayName: Release stage jobs: - job: Release displayName: Release job pool: vmImage: 'Windows-2019' steps: - task: AzureKeyVault@1 inputs: azureSubscription: '$(azureSubscription)' KeyVaultName: $(KeyVaultName) SecretsFilter: '<secrets needed for overriding ARM parameters>' RunAsPreJob: true - checkout: <repo name> - task: AzurePowerShell@5 displayName: Stop Triggers inputs: azureSubscription: '$(azureSubscription)' ScriptType: 'InlineScript' Inline: $triggersADF = Get-AzDataFactoryV2Trigger -DataFactoryName "$(DeployDataFactoryName)" -ResourceGroupName "$(DeploymentResourceGroupName)"; $triggersADF | ForEach-Object { Stop-AzDataFactoryV2Trigger – ResourceGroupName "$(DeploymentResourceGroupName)" -DataFactoryName "$(DeployDataFactoryName)" -Name $_.name -Force } azurePowerShellVersion: 'LatestVersion' - task: AzurePowerShell@5 displayName: Deploy ADF inputs: azureSubscription: '$(azureSubscription)' ScriptType: 'InlineScript' Inline: 'New-AzResourceGroupDeployment -ResourceGroupName "$(DeploymentResourceGroupName)" -TemplateParameterFile "$(System.DefaultWorkingDirectory)/$(SourceDataFactoryName)/ARMTemplateParametersForFactory.json" -TemplateFile "$(System.DefaultWorkingDirectory)/$(SourceDataFactoryName)/ARMTemplateForFactory.json" -factoryName "$(DeployDataFactoryName)" #<parameter-overridden> : <value-to-be-overridden> there are parameters in arm template and overriden by key vault secrets #<parameter-overridden> : <value-to-be-overridden> -Mode "Incremental"' azurePowerShellVersion: 'LatestVersion' - task: AzurePowerShell@5 displayName: Restart Triggers inputs: azureSubscription: '$(azureSubscription)' ScriptType: 'InlineScript' Inline: $triggersADF = Get-AzDataFactoryV2Trigger -DataFactoryName "$(DeployDataFactoryName)" -ResourceGroupName "$(DeploymentResourceGroupName)"; $triggersADF | ForEach-Object { Start-AzDataFactoryV2Trigger -ResourceGroupName "$(DeploymentResourceGroupName)" -DataFactoryName "$(DeployDataFactoryName)" -Name $_.name -Force } azurePowerShellVersion: 'LatestVersion'

Azure Data Components Network Architecture with secure configurations

Akshay_Attota — Fri, 11 Feb 2022 12:04:32 GMT

Use Case :

•When there is a need for the Data Components – ADF,ADB and SQL-Pool code to be promoted to higher environment securely without public internet access this blog is useful

•We have integrated the data components to VNET’s, and public access has been disabled for the above use case

•We have built deployment(CI-CD) pipelines in such a way that they can only deploy securely via a Self-hosted agent which has the access to VNET

This Blog will guide you to setup the data components securely with Network diagram included

Network Architecture for Azure Data Resources in V-NET

The Network Architecture diagram below shows the Azure Data Components(Azure Data Factory, Azure Data bricks, Azure Synapse) in Secure Virtual Networks.
All these Azure Data Components cannot be accessible from public internet and are connected to each other securely.
Virtual Networks and components in the Network Architecture Diagram:
1. Azure Synapse workspace and Azure Data Factory are provisioned with Managed Virtual Networks(Azure Data Factory Managed VNET, Synapse Managed VNET)
2. The Azure Databricks is within a custom Virtual Network(Data VNET)
3. The Azure Storage Accounts, Azure Key Vault , Azure Synapse workspace and Azure Data Factory are connected to the Data VNET by using Private Endpoints so that data transfer between these components is secure.
4. A virtual machine (within the Data VNET/separate SUBNET) is configured as ADF SHIR(Self Hosted Integration Runtime) to run Azure Databricks notebooks from Azure Data Factory.
5. A virtual machine within the Data VNET is configured as Azure DevOps Self Hosted Build Agent for CI-CD (Continuous Integration, Continuous Deployment) Pipelines to run as these Data components cannot be accessible from public internet.
6. A virtual machine used as a jumpbox with Bastion login is configured so that application code can be accessed securely ONLY in the DEV environments. (This machine will not be present in any higher environments)

Azure Data Components Secure Network Setup

This section explains how the data components are configured securely so that only components within the virtual networks can access them and the public internet access is restricted.

Synapse Secure Network setup:

Synapse Workspace is setup with a managed VNET
Synapse Workspace is configured using private link hub
Synapse Workspace must be connected via private endpoints through the private link hub.
Public Network Access to workspace endpoints must be disabled
TDE(Transparent Data Encryption) needs to be enabled

Managed identity needs be provisioned during creation
SQL Active Directory Admin should be enabled as a group, not for one specific user
Azure Resource Locks should be turned on to prevent accidental user deletion

Databricks Workspace Secure Network Setup:

Enable Databricks IP access list API in order to:
1. Restrict Portal access to Databricks workspace for specific IP addresses
2. Restrict Databricks API calls to specific IP addresses
Configured Databricks Workspace with VNET injection and with no public IP (NPIP) enabled
Encrypt communication between Databricks nodes using global init scripts
1. Configure an <init-script-folder> with the location to put the init script.
2. Run the notebook below to create the script enable-encryption.sh.
3. Configure the Databricks workspace with the enable-encryption.sh global init script using the global init script REST API.
4. Below is the notebook to create enable-encryption.sh:

dbutils.fs.put("dbfs:/<init-script-folder>/init/enable-encryption.sh", """ !/bin/bash keystore_file="$DB_HOME/keys/jetty_ssl_driver_keystore.jks" keystore_password="gb1gQqZ9ZIHS" sasl_secret=$(sha256sum $keystore_file | cut -d' ' -f1) if [[ $DB_IS_DRIVER = "TRUE" ]]; then driver_conf=${DB_HOME}/driver/conf/spark-branch.conf echo "Configuring driver conf at $driver_conf" if [ ! -e $driver_conf ] ; then touch $driver_conf fi head -n 1 ${DB_HOME}/driver/conf/spark-branch.conf >> $driver_conf echo " // Authenticate">> $driver_conf echo " \\"spark.authenticate\\" = true" >> $driver_conf echo " \\"spark.authenticate.secret\\" = \\"$sasl_secret\\"" >> $driver_conf echo " // Configure AES encryption">> $driver_conf echo " \\"spark.network.crypto.enabled\\" = true" >> $driver_conf echo " \\"spark.network.crypto.saslFallback\\" = false" >> $driver_conf echo " // Configure SSL">> $driver_conf echo " \\"spark.ssl.enabled\\" = true" >> $driver_conf echo " \\"spark.ssl.keyPassword\\" = \\"$keystore_password\\"" >> $driver_conf echo " \\"spark.ssl.keyStore\\" = \\"$keystore_file\\"" >> $driver_conf echo " \\"spark.ssl.keyStorePassword\\" = \\"$keystore_password\\"" >> $driver_conf echo " \\"spark.ssl.protocol\\" = \\"TLSv1.2\\"" >> $driver_conf echo " \\"spark.ssl.standalone.enabled\\" = true" >> $driver_conf echo " \\"spark.ssl.ui.enabled\\" = true" >> $driver_conf echo " }" >> $driver_conf echo "Successfully configured driver conf at $driver_conf" fi spark_defaults_conf="$DB_HOME/spark/conf/spark-defaults.conf" echo "Configuring spark defaults conf at $spark_default_conf" if [ ! -e $spark_defaults_conf ] ; then touch $spark_defaults_conf fi echo "spark.authenticate true" >> $spark_defaults_conf echo "spark.authenticate.secret $sasl_secret" >> $spark_defaults_conf echo "spark.network.crypto.enabled true" >> $spark_defaults_conf echo "spark.network.crypto.saslFallback false" >> $spark_defaults_conf echo "spark.ssl.enabled true" >> $spark_defaults_conf echo "spark.ssl.keyPassword $keystore_password" >> $spark_defaults_conf echo "spark.ssl.keyStore $keystore_file" >> $spark_defaults_conf echo "spark.ssl.keyStorePassword $keystore_password" >> $spark_defaults_conf echo "spark.ssl.protocol TLSv1.2" >> $spark_defaults_conf echo "spark.ssl.standalone.enabled true" >> $spark_defaults_conf echo "spark.ssl.ui.enabled true" >> $spark_defaults_conf echo "Successfully configured spark defaults conf at $spark_default_conf" """, True)

Follow this documentation for further details to set up encryption as a global init script: https://docs.microsoft.com/en-us/azure/databricks/security/encryption/encrypt-otw

Azure Data Factory Secure Network Setup:

This ADF is provisioned with a managed VNET
The network access of ADF is set to connect via private endpoints to the Data VNET(Custom VNET)
Create a SHIR (Self Hosted Integration Runtime) for the Data Factory to access resources within the Data VNET.
SHIR in Linked Services
- Datafactory is connected to databricks via SHIR that is in the same databricks vnet, but on a seperate subnet. This is authenticated via managed identity and must be having contributor RBAC permissions on that subnet.
Example of databricks linked service

{
   "name": "ls_databricks",
   "properties": {
       "description": "Linked Service for connecting to Databricks",
       "annotations": [],
       "type": "AzureDatabricks",
       "typeProperties": {
           "domain": "https://adb-XXXXX.net",
           "authentication": "MSI",
           "workspaceResourceId": "/subscriptions/XXXXXX/resourceGroups/rg-dev/providers/Microsoft.Databricks/workspaces/XXXXX",
           "newClusterNodeType": "Standard_DS4_v2",
           "newClusterNumOfWorker": "2:10",
           "newClusterSparkEnvVars": {
               "PYSPARK_PYTHON": "/databricks/python3/bin/python3"
           },
           "newClusterVersion": "8.2.x-scala2.12",
           "newClusterInitScripts": []
       },
       "connectVia": {
           "referenceName": "selfHostedIr",
           "type": "IntegrationRuntimeReference"
       }
   }
}

Create Managed Private Endpoints for accessing resources outside the ADF managed V-net(which don't have public internet access). For Example, Synapse SQL pool cannot be accessed by the public internet and it will be outside the ADF managed vnet. Therefore a Managed Private Endpoint needs to be created for Data Factory access to Synapse SQL Pool.

Azure Key Vault

Azure KeyVault should be configured with a private endpoint to prevent access from the public internet.
In addition to using Azure KeyVault for secret scope management, it can be integrated with Azure Databricks for Azure KeyVault-backed scope.

Azure Datalake Storage Accounts

Public Access to all Data Lakes should be disabled.
Private Endpoint Access should be configured for all Data Lakes
VNET Access is configured where necessary for Azure Storage Explorer on custom VNET located VMs.
ACL permissions to containers are programmatically handled via PowerShell code
RBACs are restricted to Azure Resource Managed Identities when specifically required e.g Azure Data Factory Storage Blob Data Contributor Role.
Along with the above mentioned points, here are the storage exception and Network routing

Self Hosted Agent Installation Procedure:

Purpose:

In order to run CI and CD pipelines through a secure VNET. We need to install a VM(connected to a VNET/SUBNET) as a self hosted agent in Azure Devops.

Installation Procedure:

Log on to the VM you want the self hosted agent installed on via Bastion
Within the Virtual Machine, navigate to the Azure Devops website using a web browser and log in.
Create a new agent pool or use an existing pool
- Navigate to project settings/agent pools within azure devops to create a new pool or view the existing pool that you want to use
- Follow the add pool dialogue to create a new pool if needed
Within the pool you want to use, navigate to the agents tab and select new agent.
Follow the instructions outlined here: https://docs.microsoft.com/en-us/azure/devops/pipelines/agents/v2-windows?view=azure-devops
- This will show you how to create a PAT token to authenticate the agent (this token is only used once at authentication time and never used again)
- Make sure to install the agent to run as a service

Self Hosted Agent Dependencies for Pipelines:

Because the agent will be installed on a brand new blank windows image, other dependencies/packages need to be installed on the virtual machine in order for our CI/CD pipelines to run.

Example:

Let's consider an example scenario that we need to install some modules like below

Here is a list of packages to install and where to install them.

Log on to the VM using Bastion again and manually install these packages
- Packages:
  - sqlpackage
    - Link to .msi installation file: https://go.microsoft.com/fwlink/?linkid=2157201
    - Follow instructions detailed here: https://docs.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-download?view=sql-server-ver15
    - Make sure to add sqlpackage to the system PATH in order for azure devops to recognize this as a capability for this machine
- AZ CLI
  - Link to installer: https://aka.ms/installazurecliwindows
- PowerShell Modules:
  - Az.Accounts
  - Az.Resources
  - Az.Datafactory
  - For all these modules above, open up a powershell session as an adminstrator and type Install-Module 'name-of-module'

ADDING SELF HOSTED AGENTS IN OUR CI-CD YAML deployment PIPELINES:

•The code shows how to run your release agent on a specific self hosted agent(connected to VNET):

- Take note of the pool and demands configuration

Considering we are deploying Data bricks notebooks in the below case

stages: - stage: Release displayName: Release stage jobs: - deployment: DeployDatabricks displayName: Deploy Databricks Notebooks pool: name: DataPool demands: - agent.name -equals vm-ado environment: Data-SANDBOX

•In the continuous deployment pipeline, we must deploy the artifacts build to Dev , QA, UAT and prod environments. We will have approval gates setup before the deployment to each environment(stage) gets started.

Data bricks Notebook Deployment using YAML code

Akshay_Attota — Wed, 07 Sep 2022 15:34:42 GMT

Purpose of this Blog:

We have defined end-end workflow for code check-in using two methods
- Notebook Revision History (standard check-in process which was defined to check-in the notebook code)
- Azure Databricks Repos (No Repo Size Limitation from May 13th)
Changes made externally to the Databricks notebook will not automatically sync with the Databricks Workspace. Due to this limitation, it is recommended that developers sync the entire git repository as detailed in the process below.
We have written yaml template for CI and CD with Powershell code which can deploy notebooks from multiple folders and the powershell code in pipeline will create folder in the destination if the folder doesn't exist against the Data thirst extension which can only deploy notebooks in single folder.
One stop for whole Databricks deployment workflow from code-check-in to pipelines with detailed explanation(which is used by stakeholders)

Developer Workflow (CI/CD)

Git Integration:

Create a feature branch based on the main branch and link a work item to it.
Login into your Azure Databricks Dev/Sandbox and click on user icon (top right) and open user settings.
Click on Git Integration Tab and make sure you have selected Azure Devops Services
There are two ways to check-in the code from Databricks UI (described below)
1.Using Revision History after opening Notebooks
2.Work with notebooks and folders in an Azure Databricks repo(Repos which is a recent development - 13th May)

Code Check-in into the Git repository from Databricks UI

I. Notebook Revision History:

Go to notebook you want to make changes and deploy to another environment.

Note: Developers need to make sure to maintain a shared/common folder for all the notebooks. You can make all required changes in your personal folder and then finally move these changes to the shared/common folder. The CI process will create the artifact from this shared/common folder.
Click on the Revision history on top right.
If it is a new notebook, you will be able to notice that git is not linked to the notebook or Git might be linked to older branch which might not exist.
Click on the ‘Git:Not Linked’(New Notebook) or 'Git Synced'(Already existing notebook).
We need to configure the Git Repository. (screenshot below)

Select feature branch from the list of branches in the drop down
Mention the URL of the repository in the format
https://dev.azure.com/<organisationname>/<ProjectName>/_git/<Repo>
Mention the path of the notebook in the repository. In our case it is src/Databricks/ITDataEngineerADBDev2/notebooks/<folder name>/<notebook.py>

Click on Save Notebook by adding a comment to integrate the code to the repository.
We need to create a PR after the changes are reflected in the feature branch.
After PR gets approved, the code now is merged into the main branch and CI-CD process will start from here.

Note: Linking individual notebooks has the following limitation

Changes made externally to the Databricks notebook (outside of the Databricks workspace) will not automatically sync with the Databricks Workspace. This issue is documented here: https://forums.databricks.com/questions/6752/external-changes-to-git-in-synced-notebook.html*
Due to this limitation, it is recommended that developers sync the entire git repository as detailed below.

II. Azure Databricks Repos:

Introduction

Repos is a newly introduced feature in Azure Databricks which is in Public Preview.
This feature used to have a 100mb limitation on the size of the linked repository but this feature is now working with larger repositories as of May 13th.
We can directly link a repository to the Databricks workspace to work on notebooks based on git branches.

Repos Check-in Process:

Click on Repos tab and right click on the folder you want to work and then select "Add Repos".
Fill in the Repo URL from Azure Devops and select the Git provider as "Azure Devops Services" and click on create.
The repo gets added with folder name as repo name(Data in the screenshot below) and a selection with all the branch names(branch symbol with feature/ and down arrow in screenshot below). Click on the down arrow beside the branch name.
After clicking on down arrow(previous screenshot), search and select your existing feature branch OR create a new feature branch (as shown in screenshot below).
All the folders in the branch are visible (refer the screenshot below)
Open the folder which contains the notebooks(refer the screenshot below). Create a new notebook and write code(Right click on the folder and select "create"---->"Notebook" like screenshot below) or edit an existing notebook in the folder.
After creating a new notebook or editing an existing notebook, click on top left hand of the notebook which contains feature branch name. Then, new window will appear which will show the changes. Add Summary(mandatory) and Description(optional), then click on "commit and push".

Then a pop up with the following message appears that it is "committed and pushed" and then the user should raise a PR to merge the feature branch into the "main" branch.

After successful PR merge, the CI-CD pipeline is run for the deployment of the notebooks to the higher environments(QA/Prod).

CI-CD Process

Continuous Integration(CI) pipeline:

The CI pipeline builds the artifact by copying the notebooks from main branch to staging directory.

It has two tasks:

Copy Task - Copies from main branch to staging directory.
Publish Artifacts - publishes artifacts from $(build.stagingdirectory)

YAML Template

name: Release-$(rev:r) trigger: none variables: workingDirectory: '$(System.DefaultWorkingDirectory)/<path>' stages: - stage: Build displayName: Build stage jobs: - job: Build displayName: Build steps: - task: CopyFiles@2 displayName: 'Copy Files to: $(build.artifactstagingdirectory)' inputs: SourceFolder: '$(workingDirectory)' TargetFolder: ' $(build.artifactstagingdirectory)' - task: PublishBuildArtifacts@1 displayName: 'Publish Artifact: notebooks' inputs: ArtifactName: dev_release

Continuous Deployment(CD) pipeline:

Deployment with secure hosted agent

a. Run the release pipeline for the specified target environment.
This will download the previously generated Build Artifacts. It will also download secure connection strings from Azure Key Vault. Make sure your self hosted agent is configured properly as per Self-Hosted Agent.Then it will deploy notebooks to your Target Azure Databricks Workspace.

The code below shows how to run your release agent on a specific self hosted agent: Take note of the pool and demands configuration.

In the continuous deployment pipeline we have to deploy the artifacts build to Dev , QA, UAT and prod environments.
We will have approval gates setup before the deployment to each environment(stage) gets started.

stages: - stage: Release displayName: Release stage jobs: - deployment: DeployDatabricks displayName: Deploy Databricks Notebooks pool: name: OTT-DEV-FanDataPool demands: - agent.name -equals <agent-name> environment: <env-name>

We have two steps for the deployment:

Getting Key Vault Secrets PAT(Personal Access Token) and Target Data bricks Workspace URL from the Key Vault.
Importing the Notebooks to the target Databricks using import Rest API.(PowerShell Task with Inline Script)

We can perform multiple folder notebook deployments using this script.
We are creating a folder(Folder in Repository similar to Sandbox/Dev environment) if it does not exist in the target(dev/QA/UAT/Prod) data bricks workspace and then importing the notebooks into the folder.

YAML template

name: Release-$(rev:r) trigger: none resources: pipelines: - pipeline: notebooks source: Databricks-CI trigger: branches: - main variables: - name: azureSubscription value: '<serviceConnectionName>' - name: workingDirectory_shared value: '$(Pipeline.Workspace)/<path>/' stages: - stage: Release displayName: Release stage jobs: - deployment: DeployDatabricks displayName: Deploy Databricks Notebooks environment: DEV strategy: runOnce: deploy: steps: - task: AzureKeyVault@1 inputs: azureSubscription: '$(azureSubscription)' KeyVaultName: $(dev_keyvault) SecretsFilter: 'databricks-pat,databricks-url' RunAsPreJob: true - task: AzurePowerShell@5 inputs: azureSubscription: '$(azureSubscription)' ScriptType: 'InlineScript' Inline: | #Create secret and headers $Secret = "Bearer " + "$(databricks-pat)"; $headers = @{ Authorization = $Secret } #Clean workspace/delete existing folders $folderNames = Get-ChildItem $(setVars.notebooksDirectory) -dir $folderNames | ForEach-Object { #Create API Request to Delete Folder $folderpath = "/" + $_.Name + "/"; $DeleteFolderBody = @{ path = $folderpath recursive = $true } $DeleteFolderBodyText = $DeleteFolderBody | ConvertTo-Json $FolderDeleteAPI = "https://" + "$(databricks-url)" + "/api/2.0/workspace/delete" #Delete Folder try { $DeleteFolder = Invoke-RestMethod -Uri $FolderDeleteAPI -Method Post -Headers $headers -Body $DeleteFolderBodyText } catch [System.Net.WebException] { Write-Host "Folder does not exist"; } } #Upload existing files/folders $filenames = get-childitem $(setVars.notebooksDirectory) -recurse | where { $_.extension -eq ".py" }; $filenames | ForEach-Object { #API Endpoints $ImportNoteBookAPI = "https://" + "$(databricks-url)" + "/api/2.0/workspace/import"; $FolderCheckAPI = "https://" + "$(databricks-url)" + "/api/2.0/workspace/get-status"; $FolderCreateAPI = "https://" + "$(databricks-url)" + "/api/2.0/workspace/mkdirs" #Open and Import the Notebook to Workspace $BinaryContents = [System.IO.File]::ReadAllBytes($_.FullName); $EncodedContents = [System.Convert]::ToBase64String($BinaryContents); $notebookDirectory = "$(setVars.notebooksDirectory)".Replace('/','\'); $pathIndex=$_.FullName.IndexOf($notebookDirectory); $notebookpath = "/" + $_.FullName.Substring($pathIndex+$notebookDirectory.Length).Replace('\','/'); $folderIndex = $notebookpath.IndexOf($_.Name); $folderpath = $notebookpath.Substring(0,$folderIndex); #API Body for Importing Notebooks $ImportNoteBookBody = @{ content = "$EncodedContents" language = "PYTHON" overwrite = $true format = "SOURCE" path = $notebookpath } $ImportNoteBookBodyText = $ImportNoteBookBody | ConvertTo-Json #API Body for Creating Folder $CreateFolderBody = @{ path = $folderpath } $CreateFolderBodyText = $CreateFolderBody | ConvertTo-Json $CheckPath = $FolderCheckAPI + "?path=" + $folderpath; #Check if the folder exists, if not create folder try { $CheckFolder = Invoke-RestMethod -Uri $CheckPath -Method Get -Headers $headers; } catch [System.Net.WebException] { Write-Host "Creating Folder $folderpath"; Invoke-RestMethod -Uri $FolderCreateAPI -Method Post -Headers $headers -Body $CreateFolderBodyText } #Importing a notebook to the Folder in target DataBricks workspace Write-Host "Creating Notebook " + $notebookpath; Invoke-RestMethod -Uri $ImportNoteBookAPI -Method Post -Headers $headers -Body $ImportNoteBookBodyText } azurePowerShellVersion: 'LatestVersion'

Azure Databricks Artifacts Deployment

poornamishra — Wed, 02 Feb 2022 15:26:07 GMT

This article is intended for deploying Jar Files, XML Files, JSON Files, wheel files and Global Init Scripts in Databricks Workspace.

Overview:

In Databricks Workspace, we have notebooks, clusters, and data stores. These notebooks are run on data bricks clusters and use datastores if they need to refer to any custom configuration in the cluster.
Developers need environment specific configurations, mapping files and custom functions using packaging for running the notebooks in Databricks Workspace.
Developers need a global Init script which runs on every cluster created in your workspace. Global Init scripts are useful when you want to enforce organization-wide library configurations or security screens.
This pipeline can automate the process of deploying these artifacts in Databricks workspace.

Purpose of this Pipeline:

The purpose this pipeline is to pick up the Databricks artifacts from the Repository and upload to Databricks workspace DBFS location and uploads the global init script using REST API's.
The CI pipeline builds the wheel (.whl) file using setup.py and publishes required files (whl file, Global Init scripts, jar files etc.) as a build artifact.
The CD pipeline uploads all the artifacts to DBFS location and also uploads the global Init scripts using the REST API's.

Pre-Requisites:

Developers need to make sure that all the artifacts that need to be uploaded to Databricks Workspace need to be present in the Repository (main branch). The location of the artifacts in the repository should be fixed (Let us consider’/artifacts’ as the location). The CI process will create the build artifact from this folder location.

The Databricks PAT Token and Databricks Target Workspace URL should be present in the key vault.

Continuous Integration (CI) pipeline:

The CI pipeline builds a wheel (.whl) file using the a setup.py file and also creates a build artifact from all files in the artifacts/ folder such as Configuration files (.json), Packages (.jar and .whl), and shell scripts (.sh).
It has the following Tasks:

Building the Wheel file using setup.py file (Subtasks below):
- using the latest python version
- updating pip
- Installing wheel package
- Building the wheel file using command "python setup.py sdist bdist_wheel"
- This setup.py file can be replaced with any python file that is used to create .whl files

Copying all the Artifacts(Jar,Json Config,Whl file, Shell Script) to artifact staging directory
Publishing the Artifacts from the staging directory.
The CD Pipeline will then be triggered after a successful run.

The YAML code for this pipeline is included in next page with all the steps included.

CI- Pipeline YAML Code:

name: Release-$(rev:r) trigger: none variables: workingDirectory: '$(System.DefaultWorkingDirectory)/Artifacts' pythonVersion: 3.7 stages: - stage: Build displayName: Build stage jobs: - job: Build displayName: Build steps: - task: UsePythonVersion@0 displayName: 'Use Python version' inputs: versionSpec: $(pythonVersion) - task: CmdLine@2 displayName: 'Upgrade Pip' inputs: script: 'python -m pip install --upgrade pip' - task: CmdLine@2 displayName: 'Install wheel' inputs: script: 'python -m pip install wheel' - task: CmdLine@2 displayName: 'Build wheel' inputs: script: 'python setup.py sdist bdist_wheel' workingDirectory: '$(workingDirectory)' - task: CopyFiles@2 displayName: 'Copy Files to: $(build.artifactstagingdirectory)' inputs: SourceFolder: '$(workingDirectory)' TargetFolder: ' $(build.artifactstagingdirectory)' - task: PublishBuildArtifacts@1 displayName: 'Publish Artifact: DatabricksArtifacts' inputs: ArtifactName: DatabricksArtifacts

Continuous Deployment (CD) pipeline:

The CD pipeline uploads all the artifacts (Jar, Json Config, Whl file) built by the CI pipeline into the Databricks File System (DBFS). The CD pipeline will also update/upload any (.sh) files from the build artifact as Global Init Scripts for the Databricks Workspace.

It has the following Tasks:

Key vault task to fetch the data bricks secrets(PAT Token, URL)
Upload Databricks Artifacts
- This will run a PowerShell script that uses the DBFS API from data bricks -> https://docs.databricks.com/dev-tools/api/latest/dbfs.html#create
- Script Name: DBFSUpload.ps1

Arguments:

Databricks PAT Token to access Databricks Workspace

Databricks Workspace URL

Pipeline Working Directory URL where the files((Jar, Json Config, Whl file) are present

3.Upload Global Init Scripts

This will run a PowerShell script that uses the Global Init Scripts API from data bricks - > https://docs.databricks.com/dev-tools/api/latest/global-init-scripts.html#operation/create-script

Script Name : DatabricksGlobalInitScriptUpload.ps1

Arguments:

Databricks PAT Token to access Databricks Workspace

Databricks Workspace URL

Pipeline Working Directory URL where the global init scripts are present

The YAML code for this CD pipeline with all the steps included. and scripts for uploading artifacts are included in the next page.

CD-YAML code:

name: Release-$(rev:r) trigger: none resources: pipelines: - pipeline: DatabricksArtifacts source: DatabricksArtifacts-CI trigger: branches: - main variables: - group: Sample-Variable-Group - name: azureSubscription value: 'Sample-Azure-Service-Connection' - name: workingDirectory_utilities value: '$(Pipeline.Workspace)/DatabricksArtifacts/DatabricksArtifacts' stages: - stage: Release displayName: Release stage jobs: - deployment: DeployDatabricksArtifacts displayName: Deploy Databricks Artifacts strategy: runOnce: deploy: steps: - checkout: self - task: AzureKeyVault@1 inputs: azureSubscription: "$(azureSubscription)" KeyVaultName: $(keyvault_name) SecretsFilter: "databricks-pat,databricks-url" RunAsPreJob: true - task: AzurePowerShell@5 displayName: Upload Databricks Artifacts inputs: azureSubscription: '$(azureSubscription)' ScriptType: 'FilePath' ScriptPath: '$(System.DefaultWorkingDirectory)/Pipelines/Scripts/DatabricksArtifactsUpload.ps1' ScriptArguments: '-databricksPat $(databricks-pat) -databricksUrl $(databricks-url) -workingDirectory $(workingDirectory_utilities)' azurePowerShellVersion: 'LatestVersion' - task: AzurePowerShell@5 displayName: Upload Global Init Scripts inputs: azureSubscription: '$(azureSubscription)' ScriptType: 'FilePath' ScriptPath: '$(System.DefaultWorkingDirectory)/Pipelines/Scripts/DatabricksGlobalInitScriptUpload.ps1' ScriptArguments: '-databricksPat $(databricks-pat) -databricksUrl $(databricks-url) -workingDirectory $(workingDirectory_utilities)' azurePowerShellVersion: 'LatestVersion'

DBFSUpload.ps1

param( [String] [Parameter (Mandatory = $true)] $databricksPat, [String] [Parameter (Mandatory = $true)] $databricksUrl, [String] [Parameter (Mandatory = $true)] $workingDirectory ) Function UploadFile { param ( [String] [Parameter (Mandatory = $true)] $sourceFilePath, [String] [Parameter (Mandatory = $true)] $fileName, [String] [Parameter (Mandatory = $true)] $targetFilePath ) #Grab bytes of source file $BinaryContents = [System.IO.File]::ReadAllBytes($sourceFilePath); $enc = [System.Text.Encoding]::GetEncoding("ISO-8859-1"); $fileEnc = $enc.GetString($BinaryContents); #Create body of request $LF = "`r`n"; $boundary = [System.Guid]::NewGuid().ToString(); $bodyLines = ( "--$boundary", "Content-Disposition: form-data; name=`"path`"$LF", $targetFilePath, "--$boundary", "Content-Disposition: form-data; name=`"contents`";filename=`"$fileName`"", "Content-Type: application/octet-stream$LF", $fileEnc, "--$boundary", "Content-Disposition: form-data; name=`"overwrite`"$LF", "true", "--$boundary--$LF" ) -join $LF; #Create Request $params = @{ Uri = "$databricksUrl/api/2.0/dbfs/put" Body = $bodyLines Method = 'Post' Headers = @{ Authorization = "Bearer $databricksPat" } ContentType = "multipart/form-data; boundary=$boundary" } Invoke-RestMethod @params; } Function GetTargetFilePath { param ( [System.IO.FileInfo] [Parameter (Mandatory = $true)] $sourceFile ) switch ($sourceFile.extension) { ".json" {return "/FileStore/config/$($sourceFile.Name)"} ".jar" {return "/FileStore/jar/$($sourceFile.Name)"} ".whl" {return "/FileStore/whl/$($sourceFile.Name)"} } } #Loop through all files and upload to dbfs $filenames = get-childitem $workingDirectory -recurse; $filenames | ForEach-Object { if ( $_.extension -eq ".json" -OR $_.extension -eq ".whl" -OR $_.extension -eq ".jar") { $targetFilePath = GetTargetFilePath -sourceFile $_; Write-Host "Uploading $($_.FullName) to dbfs at $targetFilePath."; UploadFile -sourceFilePath $_.FullName -fileName $_.Name -targetFilePath $targetFilePath; } }

DatabricksGlobalInitScriptUpload.ps1

param( [String] [Parameter (Mandatory = $true)] $databricksPat, [String] [Parameter (Mandatory = $true)] $databricksUrl, [String] [Parameter (Mandatory = $true)] $workingDirectory ) Function UploadFile { param ( [String] [Parameter (Mandatory = $true)] $uri, [String] [Parameter (Mandatory = $true)] $restMethod, [String] [Parameter (Mandatory = $true)] $sourceFilePath, [String] [Parameter (Mandatory = $true)] $fileName ) #Grab bytes of source file $base64string = [Convert]::ToBase64String([IO.File]::ReadAllBytes($sourceFilePath)) #Create body of request $body = @{ name = $fileName script = $base64string position = 1 enabled = "false" } #Create Request $params = @{ Uri = $uri Body = $body | ConvertTo-Json Method = $restMethod Headers = @{ Authorization = "Bearer $databricksPat" } ContentType = "application/json" } Invoke-RestMethod @params; } Function GetAllScripts { #Create Request $params = @{ Uri = "$databricksUrl/api/2.0/global-init-scripts" Method = "GET" Headers = @{ Authorization = "Bearer $databricksPat" } ContentType = "application/json" } return Invoke-RestMethod @params; } #Loop through all files and upload to databricks global init $scripts = GetAllScripts $filenames = get-childitem $workingDirectory -recurse; $filenames | ForEach-Object { if ( $_.extension -eq ".sh") { #Check if file name already exists in databricks $scriptId = ($scripts.scripts -match $_.Name).script_id if (!$scriptId){ #Create Global init script Write-Host "Uploading $($_.FullName) as a global init script with name $($_.Name) to databricks"; UploadFile -uri "$databricksUrl/api/2.0/global-init-scripts" -restMethod "POST" -sourceFilePath $_.FullName -fileName $_.Name; } else{ #Update Global init script Write-Host "Updating global init script with name $($_.Name) to databricks"; UploadFile -uri "$databricksUrl/api/2.0/global-init-scripts/$scriptId" -restMethod "PATCH" -sourceFilePath $_.FullName -fileName $_.Name; } } }

End Result of Successful Pipeline Runs:

Global Init Script Upload:

Conclusion:

Using this CI CD approach we were successfully able to upload the artifacts to the Databricks file system.

References:

Get a 360-degree view of data sharing lineage with Azure Data Share and Purview

jiefeng — Wed, 24 Mar 2021 17:22:02 GMT

As companies embark on the journey to be data-driven, they need access to data both within and outside of their organization. It is critical to be able to share data easily and securely with external business partners and internally between different departments. Azure Data Share enables easy and secure sharing of files, folders, and databases with just a few clicks. Through the integration with Azure Purview, you can now have a 360-degree view of data sharing including what data is shared with or received from other organizations, which helps you perform impact and root cause analysis on your datasets.

Scenario 1: 360-degree view of data sharing

Whether you are sharing data with business partners or within your organization, you can see bi-directional sharing relationships in the Purview asset lineage graph. You can discover all the datasets that are shared to or received from a specific organization.

Scenario 2: Impact analysis on data shared with another organization

When you make a change to a dataset, such as changing the file format or the schema of a database table, it is important to know who is using that data and will be impacted by the change. Lineage lets you easily understand the impact of the downstream internal or external users who you have shared the data with.

Scenario 3: Root cause analysis for data received from other organizations

When troubleshooting a data issue, it is important to find out source of the data. Using lineage, you can identify where the data is originally coming from, and if it is from within or outside your organization.

Get started

It just takes a few simple steps to get data sharing lineage in Purview.

Connect your Azure Data Share account to a Purview account
Trigger scheduled or on-demand snapshots in Azure Data Share
Browse and select an asset in Purview
View the asset lineage in Purview

This quick demo shows the steps:

Resources

Connect to Azure Data Share - Azure Purview | Microsoft Docs

Azure Data Share | Microsoft Docs

Share IoT and Log data in real time using Azure Data Share and Azure Data Explorer

jiefeng — Mon, 02 Mar 2020 21:32:35 GMT

In today's digital world, more and more data is generated from devices and software deployed at home and work. This could be data collected from applications, websites, IoT devices and equipment. Often, this data needs to be shared and enriched with 3rd party data in real-time to derive insights. Traditionally, data is shared by generating a data feed made available via file share, FTP, etc. This process requires organizations to invest in maintaining the data pipelines and introduces latency into the system.

Azure Data Explorer is a fully managed data analytics service for real-time analysis on large volumes of data streamed from applications and devices. You can use Azure Data Explorer to collect, store, and analyze diverse data on the fly to quickly identify patterns, anomalies and trends. Azure Data Share enables easy and secure data sharing within or cross organizations. You can share data code-free while maintain full visibility into your data sharing relationships including what data is shared and who it is shared with.

With the integration between Azure Data Explorer and Azure Data Share, data can be easily and securely shared with partners, service providers or customers for near-real-time collaboration.

Here is an example of how organizations with manufacturing plants are using Azure Data Explorer and Azure Data Share for near-real-time analytics. Data is streamed from IoT Hub into Azure Data Explorer cluster and stored in databases. It is then shared through Azure Data Share to the service provider’s Azure Data Explorer cluster for analysis and dashboarding.

How data sharing works

Azure Data Share enables sharing of databases in-place from Azure Data Explorer clusters. Data provider can create a share by specifying the databases they want to share, who to share with and terms of use. The same data can be shared with multiple data consumers. Azure Data Share service sends an email invitation to each data consumer, who can then accept the invitation and specify a target Azure Data Explorer cluster in the same Azure data center to access the data.

After sharing relationship is established, Azure Data Share creates a symbolic link between the provider and consumer's Azure Data Explorer clusters. This enables data consumer to read and query the data, but not write to the shared databases. Access to the data uses compute resources from the consumer's Azure Data Explorer cluster. Data consumer can also configure its own access and caching policies to the shared databases, as well as view logs of its own query. When caching policy is configured, data is cached on the consumer's Azure Data Explorer cluster for improved query performance, but is not copied into the blob storage powering the consumer's Azure Data Explorer cluster.

Data provider can stop sharing by revoking access. This will remove the symbolic link and terminate access to the shared database(s).

Data provider share data

To share data, user needs write permission and permission to add role assignment to the source Azure Data Explorer cluster. These permissions typically exist in the Owner role.

The following are steps to share data:

Create an Azure Data Share resource
Within the Azure Data Share resource, create a share by specifying the following information:
- Name
- Share type: In-place
- Description (optional)
- Terms of use (optional)
- Azure Data Explorer cluster or databases - When a cluster is shared, all databases on the cluster including future databases created by the data provider are made available to the data consumer. When database is shared, only the individual database is available to the data consumer.
- Recipients’ Azure login email address

Data consumer receive data

To receive data, user needs write permission and permission to add role assignment to the target Azure Data Explorer cluster. These permissions typically exist in the Owner role.

The following are steps to receive data:

Receive email invitation and follow the link to log into Azure
Agree to the terms of use and accept invitation into its own Azure Data Share resource
Specify an Azure Data Explorer cluster in the same Azure data center as the source to access data

Data consumer can now go to its Azure Data Explorer cluster to grant user permissions to the share databases and access the data. Data ingested using batch mode into the source Azure Data Explorer cluster will show up on the target cluster within a few seconds to a few minutes.

Additional resources

Scale your data sharing needs with the power of Azure Data Share’s .NET SDK

Akshat_Dave — Thu, 12 Dec 2019 07:55:59 GMT

Azure Data Share, now a generally available service, makes it very simple to share your organization’s data securely with your partners. You may have already seen and tried it on the Azure Portal to share data swiftly, without writing any code. But what if you want to scale your sharing needs to thousands of customers spread across the world? Data Share offers a rich API/SDK for you to leverage and scale your sharing relationships seamlessly. Let’s jump right in and walk through a sample use case using the .NET SDK.

Why use the .NET SDK?

Imagine the following situation: Your organization provides data to some partners and consumes data from others, where these partners can be departments within your company or external organizations. Using Data Shares's portal experience for creating and managing the first few sharing relationships is quick and intuitive. However, as the number of sharing relationships grows larger, the process would soon grow tedious. Imagine managing potentially hundreds or even thousands of sharing relationships. This would become manually unscalable. We’ve designed the Data Share SDK for ease-of-use, to facilitate the scaling of your organization’s sharing needs.

Scenario

Since we would like to demonstrate both the data provider’s and consumer’s perspectives, let’s try a common customer scenario of sharing between departments of the same organization. Specifically, suppose the provider department (say Marketing) has its data in a blob store and wants to share that with a different department (say Sales). To make this sharing more interesting, we’ll try to share the data to a different tenant. Let's see how the Data Share SDK can be used for this.

Setting up the Console Application

Getting a copy of the sample code

Start by cloning the sample Git repository: by typing on the command prompt:

git clone https://github.com/Azure-Samples/azure-data-share-dotnet-api-sample.git

Creating a Service Principal for the Console Application

We'll use active directory application Id and secret for authenticating the console application. For this, an Azure Active Directory (AAD) application must be created each for the provider and the consumer. Follow this tutorial to set up the AAD application.

Creating Storage Accounts

The console application will share data from the provider data share account to the consumer data share account, each of which will point to an underlying data store. For this demo you will need to create a storage account each for the provider and consumer. Please follow this tutorial to create the storage accounts. Also ensure that the Service Principal created in the previous section has "Owner" role on the storage accounts. To learn how to add role assignment to resources, please follow this tutorial.

Configuring the run-time settings

Once the repository has been cloned, navigate to the file DataShareSample.sln and open it. By default, it should open in Visual studio 2017. Try to build the solution and make sure everything compiles correctly.

Now we will go ahead and configure the run-time settings in the appSettings.json file (shown in Snippet 1):

Snippet 1: appSettings.json

{ "configs": { "provider": { "tenantId": "", "clientId": "", "objectId": "", "secret": "", "subscriptionId": "", "dataShareResourceGroup": "", "dataShareAccountName": "", "dataShareShareName": "", "dataShareInvitation": "", "dataShareDataSetName": "", "dataShareDataSetMappingName": "", "storageResourceGroup": "", "storageAccountName": "", "storageContainerName": "", "storageBlobName": "" }, "consumer": { "tenantId": "", "clientId": "", "objectId": "", "secret": "", "subscriptionId": "", "dataShareResourceGroup": "", "dataShareAccountName": "", "dataShareShareSubscriptionName": "", "dataShareInvitation": "", "dataShareDataSetName": "", "dataShareDataSetMappingName": "", "storageResourceGroup": "", "storageAccountName": "", "storageContainerName": "", "storageBlobName": "" } } }

That's it! Now that you have everything configured, let’s run the code by debugging through the important lines.

Code walk-through and execution

Program.cs looks similar to the code given below in Snippet 2. Let’s have a look within the Main method. First the configurations are read from the appSettings.json that you have just filled. Following this, a Resource Group is created (for a logical grouping of the data share resources that we are about to create). Once the resource group is in order, the Data Share Account creation code is invoked, followed immediately by the Share creation. An important step to enable the data sharing is to assign the Data Share Account the Blob Reader role on the underlying provider storage account. Finally on the provider side, Data Sets are created and an invitation is sent to the consumer.

Note: the AAD application should have permission to create resources in the subscription configured and the Microsoft.DataShare resource provider should be registered in the subscriptions configured in appSettings.json.

On the consumer side, a similar flow is followed. The consumer Data Share Account is created, and subsequently the invitation is accepted by creating a Share Subscription. On the consumer side, the account Id is assigned a Blob writer role on the underlying consumer data store. A Data Set Mapping is created to link the Data Set received on the consumer side to the consumer data store. Finally, a data Synchronization is initiated and the result reported.

Go ahead and execute the code or debug through it line by line to gain a better understanding. You should be able to track the resource creation on Azure Portal while the code executes. Further, the blob from the provider blob store would appear on the consumer blob store at the end of a successful synchronize call.

Snippet 2: Program.cs

// ----------------------------------------------------------------------- // <copyright file="Program.cs" company="Microsoft Corporation"> // Copyright (C) Microsoft Corporation. All rights reserved. // </copyright> // ----------------------------------------------------------------------- namespace DataShareSample { using System; using System.IO; using System.Threading.Tasks; using Microsoft.Azure.Management.DataShare.Models; using Microsoft.Azure.Management.ResourceManager.Fluent; using Microsoft.Extensions.Configuration; public class Program { public static async Task Main(string[] args) { Console.WriteLine("\r\n\r\nReading the configurations..."); IConfigurationRoot configurationRoot = new ConfigurationBuilder() .SetBasePath(Directory.GetCurrentDirectory()).AddJsonFile("AppSettings.json").Build(); var configuration = configurationRoot.GetSection("configs").Get<Configuration>(); Console.WriteLine("\r\n\r\nIdempotent creates for provider resources..."); var providerContext = new UserContext(configuration.Provider); IResourceGroup providerResourceGroup = providerContext.IdempotentCreateResourceGroup(); Account providerAccount = providerContext.IdempotentCreateAccount(); Share share = providerContext.IdempotentCreateShare(); Console.WriteLine($"\r\n\r\nAssign MSI of {providerAccount.Id} as the Blob Reader on the Provider Storage..."); await providerContext.AssignRoleTaskAsync( configuration.Provider, providerAccount.Identity.PrincipalId, "2a2b9908-6ea1-4ae2-8e65-a410df84e7d1"); Console.WriteLine("\r\n\r\nCreate data set and send invitation"); DataSet dataSet = providerContext.CreateIfNotExistDataSet(configuration.Provider); Invitation invitation = providerContext.CreateIfNotExistInvitation(configuration.Consumer); Console.WriteLine("\r\n\r\nIdempotent creates for consumer"); var consumerContext = new UserContext(configuration.Consumer); IResourceGroup consumerResourceGroup = consumerContext.IdempotentCreateResourceGroup(); Account consumerAccount = consumerContext.IdempotentCreateAccount(); Console.WriteLine("\r\n\r\nTo accept the invitation create a share subscription/received share..."); ShareSubscription shareSubscription = consumerContext.CreateIfNotExistShareSubscription(invitation); Console.WriteLine($"\r\n\r\nAssign MSI of {consumerAccount.Id} as the Blob Contributor on the consumer Storage..."); await consumerContext.AssignRoleTaskAsync( configuration.Consumer, consumerAccount.Identity.PrincipalId, "ba92f5b4-2d11-453d-a403-e96b0029c9fe"); Console.WriteLine("\r\n\r\nCreate data set mapping to setup storage for the consumer"); ConsumerSourceDataSet consumerSourceDataSet = consumerContext.GetConsumerSourceDataSet(); DataSetMapping dataSetMapping = consumerContext.CreateDataSetMapping( configuration.Consumer, consumerSourceDataSet); Console.WriteLine("\r\n\r\nInitiate a snapshot copy (duration depends on how large the data is)..."); ShareSubscriptionSynchronization response = consumerContext.Synchronize(); Console.WriteLine( $"Synchronization Status: {response.Status}. Check resource {consumerAccount.Id} on https://portal.azure.com for further details. \r\n\r\n Hit Enter to continue..."); Console.ReadLine(); } } }

The overall program flow can be summarized by Figure 1.

Figure 1: Sharing Model

Additional Capabilities: Scheduled Snapshots

In addition to the above process of triggering an on-demand synchronization, Data Share also provides native automation. You may choose to write a wrapper around scheduling on-demand runs or use the scheduled synchronization feature. To enable this cool feature, the provider, at the time of creating the share, specifies a snapshot schedule with a daily or hourly frequency along with a schedule start time; the consumer simply needs to accept the schedule by creating a Trigger on their side to receive automated snapshots as per the schedule. The consumer, of course always has the option to disable or re-enable the schedule. This is specifically useful for daily/hourly reports or non-real-time incremental updates. The snapshots taken after the first snapshot will be incremental in case the schedule is enabled. You can find API documentation for this at Synchronization Settings and Triggers .

Conclusion

With the Azure Data Share’s easy-to-use .NET SDK, you can now take control of sharing big data across organizations and geographies and through a single pane-of-glass create and manage all your sharing relationships at scale. For further reading please refer to our public documentation.

Azure Data Share is now in GA!

joannapod — Mon, 04 Nov 2019 13:00:00 GMT

Todays most successful organizations have one thing in common: they are tirelessly data driven at their core. The ability to easily and securely share and consume data from customers and partners is critical for organizations looking to unlock transformational insights and make critical business decisions.

The Azure Data Share product team is pleased to announce the general availability of snapshot-based sharing for Azure Data Lake Store and Azure Blob Storage. In just a few clicks, organizations can share data stored in their data lakes with third party organizations outside their Azure tenancy. Data providers wanting to share data with their customers/partners can easily create a new share, populate it with data residing in a variety of stores and add recipients.

Data providers always stay in control of their data sharing relationships. They are able to track who they've shared with and whether the data consumers have started consuming their data. If they no longer want their data consumers to receive new data, they can revoke their access to future updates. Azure Data Share offers a hassle-free way to manage data – organizations pay only for what they share, with no code to write and no infrastructure to manage.

Azure Data Share uses Managed Identities for Azure Services to authenticate to the underlying data store, ensuring that no exchange of credentials occurs between data providers and data consumers.

We're also excited to announce that snapshot-based sharing from Azure SQL-based sources including Azure SQL Database and Azure SQL Data Warehouse is now in public preview. Data providers that want to share data from SQL-based sources no longer need to be concerned about how their data consumers will receive the data. The ability to share from one source, receive in another is a capability which many customers have been eagerly awaiting and one that offers customers flexibility of choice. Providers can share tables and views from an Azure SQL Data Warehouse, and their data consumers can receive this in parquet or csv directly to their data lake. Alternatively, data consumers can choose to receive SQL-based data into a SQL-based source, another option which provides customers with data fluidity previously unseen in the market.

Customers that have been seeking out the ability to perform in-place sharing - sharing data without data movement - will be delighted to learn that we are now in limited public preview with in-place sharing from Azure Data Explorer. In-place sharing works by establishing a symbolic link to where the data resides, enabling customers to run their compute workloads against data in real-time. This feature is not to be missed - click here to join!

Secure, code free, pay for what you share, no infra to manage - it's as simple as that. Start sharing today!