What’s new in Azure Data, AI, & Digital Applications: Modernize your data estate, build intelligent apps, and apply AI solutions

I write this blog each month to help navigate through the intense pace of news and innovation we’re releasing for customers. And what a year we’ve had! We witnessed incredible advances at breathtaking speed as AI reshaped what’s possible across industries, organizations, and our day-to-day lives.

Personally, I’m loving the features and capabilities of Copilot for Microsoft 365, especially meeting summaries after a Teams call so I can quickly access the most important points and any actions. We’re still experimenting with AI to craft marketing narratives across my team and it’s exciting to see how AI enhances our own creativity.

AI technology is not brand new. It’s been improving experiences across applications and business processes for some time but, the broad availability of generative AI models and tools this year was a watershed moment.  

Customers and partners made quick pivots to bring AI into their transformation roadmaps, and within months were deploying applications and services powered by AI. I have never seen a new technology generate such profound change at this pace. It shows how ready many organizations were for this moment; how investments made in the cloud, data, DevOps, and creating transformation cultures set the stage for AI adoption. This year Microsoft introduced hundreds of resources, models, services, and tools to help customers and partners maximize AI.

This month, I’m pleased to expand this blog’s scope and bring in what’s new for digital applications for a holistic look at everything we’re delivering to help customers modernize their data estate, build intelligent applications, and apply AI technologies to help achieve their business goals. Let’s go.

New models and multimodal capabilities available in Azure AI

Our focus is to deliver the most cutting-edge open and frontier models available so developers can build with confidence and unlock immediate value across their organization.  

Last month we announced a significant expansion of Azure OpenAI Service and introduced Models as a Service (MaaS). This is a way for model providers to offer their latest open and frontier LLMs on Azure for generative AI developers to integrate into their applications.

And just last week, we announced the availability of MaaS for Llama 2. With MaaS for Llama 2, developers can integrate with their favorite LLM tools like Prompt Flow, Semantic Kernel, and LangChain with a ready-to-use API and pay-as-you go billing based on tokens for LLMs. This allows generative AI developers to access Llama 2 via hosted fine-tuning without provisioning GPUs, greatly simplifying the model set up and deployment process. Then they can offer their custom applications utilizing Llama 2, purchased through and hosted on the Azure Marketplace.

Model catalog in Azure AI Studio Preview

We continue to advance our Azure OpenAI Service and recently launched several multimodal AI capabilities that empower businesses to build generative AI experiences with image, text and video, including:

• DALL·E 3, in preview: Generate images from text descriptions. DALL·E 3 is a remarkable AI model that does just that. Users describe an image, and DALL·E 3 will be able to create it.
• GPT-3.5 Turbo model with a 16k token prompt length, generally available, and GPT-4 Turbo, in preview: The latest models in Azure OpenAI Service enable customers to extend prompt length and bring more control and efficiency to their generative AI applications.
• GPT-4 Turbo with Vision (GPT-4V), in preview: When integrated with Azure AI Vision, GPT-4V enhances experiences by allowing the inclusion of images or videos along with text for generating text output, benefiting from Azure AI Vision enhancement like video analysis.
• Fine-tuning of Azure OpenAI Service models: Fine-tuning is now generally available for Azure OpenAI Service models including Babbage-002, Davinci-002, and GPT-35-Turbo. Developers and data scientists can customize these Azure OpenAI Service models for specific tasks.
• GPT-4 updates: Azure OpenAI Service has also rolled out updates to GPT-4, including the ability for fine-tuning. Fine-tuning will allow organizations to customize the AI model to better suit their specific needs. It’s akin to tailoring a suit to fit perfectly, but in the world of AI. Updates to GPT-4 are in preview.

Steering at the frontier: Extending the power of prompting

The power of prompting in GPT-4 continues to amaze! A recent Microsoft Research blog discusses promptbase, an approach to prompting GPT-4 that harnesses its powerful reasoning abilities. Across a wide variety of test sets (including the ones used to benchmark the recently announced Gemini Ultra), GPT-4 gets better results than other AI models (including Gemini Ultra). And it does this with zero-shot chain-of-thought prompting.

Infuse Responsible AI (RAI) tools and practices in your LLMOps

As AI adoption matures and companies put their AI apps into production, it’s important to consider the safety boundaries supporting the short- and long-term ROI of those applications. This month, we continued our series on LLMOps for business leaders with an article focused on how to infuse responsible AI into your AI development lifecycle. We highlight best practices and tools in Azure AI Studio to help development teams put their principles into practice.

Azure AI Advantage offer

Azure Cosmos DB is the cloud database for the Era of AI. It supports built-in AI, including natural language queries, AI vector search capabilities, and simple AI integration with Azure AI Search. We’re investing in helping customers discover these benefits with our new Azure AI Advantage offer, which helps new and existing Azure AI and GitHub Copilot customers save when using Azure Cosmos DB by providing 40,000 Request Units per second (RU/s) of Azure Cosmos DB for 90 days.

What’s new in the Azure data platform—because AI is only as good as your data

Every intelligent app starts with data so a modern data and analytics platform is essential for any AI transformation.

One example of this how multinational law firm Clifford Chance leverages new technologies to benefit their clients. The firm built a solid data platform on Azure to innovate with new technologies, including the new generation of large language models, Azure OpenAI and Microsoft 365 Copilot. Early innovations are already delivering value with cognitive translation proving to be one of the fastest growing products their IT team has ever released.

And Belfius, a Belgian insurance company, built on the Microsoft intelligent data platform using services like Azure Machine Learning and Azure Databricks to reduce development time, increase efficiency, and gain reliability. As a result, their data scientists can focus on creating and transforming features and the company can better detect fraud and money laundering. 

Azure and Databricks: Co-innovation for powerful AI experiences

Microsoft’s Scott Guthrie (left) and Arun Ulagaratchagan (right) with Databrick’s Co-Founder and CEO Ali Ghodsi (center) at Ignite

At Microsoft Ignite 2023 in November the benefits of maturing AI tools and services were in full view as customers and partners shared how Microsoft is empowering them to achieve more. 

Databricks is of one of our most strategic partners with some of the fastest growing data services on Azure. We recently showcased our co-innovation, including Azure Databricks interoperability with Microsoft Fabric, and how Azure Databricks is taking advantage of Azure OpenAI to deliver AI experiences for Azure Databricks’ customers. This means customers can take advantage of LLMs in Azure OpenAI as they build AI capabilities like retrieval-augmented generation (RAG) applications on Azure Databricks, and then use Power BI in Fabric to analyze the output.

If you missed Databricks’ CEO Ali Ghodsi on stage with Scott Guthrie at Ignite, I encourage you to check out the replay of Scott’s full segment Microsoft Cloud in the era of AI. If you only have a few minutes, his wrap up on LinkedIn is a great option, too. It’s a helpful overview of how the Microsoft Cloud is uniquely positioned to empower customers to transform by building AI solutions and unlocking data insights using the same platform and services that power all of Microsoft’s comprehensive solutions.

And for a closer look at our latest work with Databricks, check out the click-thru version of the Modern Analytics with Microsoft Fabric and Azure Databricks DREAM Lab from Ignite.

Now in preview: Azure AI extension for Azure Database for PostgreSQL 

The new Azure AI extension allows developers to leverage large language models (LLMs) in Azure OpenAI to generate vector embeddings and build rich, PostgreSQL generative AI applications. These powerful new capabilities combined with existing support for the pgvector extension, make Azure Database for PostgreSQL another great destination for building AI-powered apps. 

Azure Arc brings cloud innovation to SQL Server anywhere

This month, we’re introducing a new set of enhanced manageability and security capabilities from SQL Server enabled by Azure Arc. With Monitoring for SQL Server, customers can gain critical insights into their entire SQL Server estate and optimize for database performance. Customers can also view and manage Always On availability groups, failover cluster instances, and backups directly from the Azure portal, with better visibility and simplicity. Lastly, with Extended Security Updates as a service and automated patching, customers can always keep their apps secure, compliant, and up to date at all times. 

Lower pricing for Azure SQL Database Hyperscale compute

New pricing on Azure SQL Database Hyperscale offers cloud-native workloads the performance and security of Azure SQL at the price of commercial open-source databases. Hyperscale customers can save up to 35% on the compute resources they need to build scalable, AI-ready cloud applications of any size and I/O requirement. The new pricing is now available.

Digital applications deliver transformational operations and experiences

This era of AI is brought to life through digital applications developed and deployed by companies putting AI to work to enhance their operations and experiences—like a personalized app experience for employees or a customized chatbot for end customers. Here are some recent updates that help make all this innovation possible.

The seven pillars of modern AI development: Leaning into the era of custom copilots

Copilots are generating a lot of excitement and with Azure AI Studio in public preview, developers have a platform purpose-built for generative AI application development. As we lean into this new era, it’s important for businesses to carefully consider how to design a durable, adaptable, and effective approach. How can AI developers ensure their solutions enhance customer engagement? Here are seven pillars to think through when building your custom copilot.

AKS is a leading platform for modern cloud native and intelligent applications

The future of app development is at the intersection of AI and cloud-native technologies like Kubernetes. Cloud-native and AI are deeply rooted together in fueling innovation at scale and Azure Kubernetes Service (AKS) provides the scale customers need to run their compute intensive workloads like AI and machine learning. Check out Brendan Burn’s recent blog from KubeCon to learn how Microsoft is building and servicing open-source communities that benefit our customers.

There are no limits to your innovation with Azure

It has been amazing to see the tech community and customer response to our recent news, resources, and features across the portfolio, particularly digital applications. 

Resources like the Platform Engineering Guide launched in with Ignite have been incredibly popular, demonstrating the demand and appetite for this kind of training material.  

Seeing how organizations innovate with the technology is what it’s all about.   

Here are a couple of customer stories that caught my attention recently.

Modernizing interactive experiences across LEGO House with Azure Kubernetes Service

We’re collaborating with The LEGO House in Denmark—the ultimate LEGO experience center for children and adults—to migrate custom-built interactive digital experiences from an aging on-prem data center to Microsoft Azure Kubernetes Service (AKS) to improve stability, security, and the ability to iterate and collaborate on new guest experiences. This shift to the cloud enables LEGO House to more quickly update these experiences as they learn from guests. As the destination modernizes it hopes to share learnings and technologies with the broader LEGO Group ecosystem, like LEGOLAND and brand retail stores. 

Gluwa chose Azure for a reliable, scalable cloud solution to bring banking to emerging, underserved markets and close the financial gap

An estimated 1.4 billion people lack access to basic financial services because they live in a country with limited financial infrastructure, making it difficult to get credit or personal and business loans. Gluwa with Creditcoin uses blockchain technology to differentiate their business through borderless financial technology and chose Azure as the foundation to support it. Using a wide combination of our services and solutions—.NET framework, Azure Container Instances, AKS, Azure SQL, Azure Cosmos DB, and more—Gluwa has a strong platform to support their offerings. The business has also boosted operational efficiency with reliable uptime, stable services, and rich product offerings. 

CARIAD creates a service platform for Volkswagen Group vehicles with Azure and AKS

With the automotive industry shifting to software defined vehicles, CARIAD, the Volkswagen Group software subsidiary, collaborated with Microsoft using Azure and AKS to create the CARIAD Service Platform for providing automotive applications to brands like Audi, Porsche, Volkswagen, Seat and Skoda. This platform powers and accelerates the development and service of vehicle software by CARIAD’s developers, helping software become an advantage for the Volkswagen Group in the next generation of automotive mobility.

DICK’S Sporting Goods creates an omnichannel customer experience using Azure Arc and AKS

To create a more consistent, personalized experience for customers across its 850 stores and its online retail experience, DICK’S Sporting Goods envisioned a “one store” technology strategy with the ability to write, deploy, manage, and monitor its store software across all locations nationwide—and reflect those same experiences through its eCommerce site. DICK’S needed a new level of modularity, integration, and simplicity to seamlessly connect its public cloud environment with its computing systems at the edge. With the help of Microsoft, DICK’s Sporting Goods is migrating its on-premise infrastructure to Microsoft Azure and creating an adaptive cloud environment comprised of Azure Arc and Azure Kubernetes Service (AKS). Now the retailer can easily deploy new applications to every store to support a ubiquitous experience. 

Azure Cobalt delivers performance and efficiency for intelligent applications

Azure provides hundreds of services supporting the performance demands of cloud native and intelligent applications. Our work to maximize performance and efficiency now extends to the silicon powering Azure. We recently introduced Azure Maia, our first custom AI accelerator series to run cloud-based training and inferencing for AI workloads, and our custom in-house CPU series, Azure Cobalt, the first CPU designed by us, specifically for the Microsoft Cloud. 

Cobalt 100, the first generation in the series, is a 64-bit 128-core chip that delivers up to 40% performance improvement over current generations of Azure Arm chips and can power services such as Microsoft Teams and Azure SQL.

With Cobalt, we will deliver performance and economics to meet the demands of resource-intensive workloads like intelligent applications using generative AI for at-scale workloads. Our customized silicon and system features include dynamic power capabilities that can be tuned per-core based on the workload, leveraging the co-optimization of hardware with software to deliver best-in-class performance efficiency. 

Don’t just take it from me and Omar. Rani Borkar, CVP, Azure Hardware Systems shares how Cobalt delivers differentiated performance—and can even help drive toward achieving sustainability goals:

“We’re building sustainability into every part of our hardware for the cloud, from our silicon to the servers. This starts in the design phase, and on Cobalt we made those intentional design choices to be able to control performance and power consumption per core and on every single VM. We’re pleased with the performance we’ve seen testing Cobalt on internal workloads like Teams and Azure SQL, and looking forward to rolling this out more widely to customers as a VM offering next year.”

Grow your data, AI, and intelligent applications understanding and skillset

I often use December downtime to do a deep dive into a new technology or learn a new skill. I’m a data geek, and I have fond memories from a few years ago when I worked through more than a dozen Power BI trainings and loved every minute of it. Demystifying AI and helping customers build the right skillsets is just one way Microsoft is empowering AI transformation. Here are some new learning resources if you are like me and plan to end the year learning something new!

AI in a Minute: Looking for help ramping your teams on generative AI? Or maybe you want to go to end of year gatherings prepared to discuss AI and its possibilities? Our new “AI in a Minute” video series explains generative AI basics in short, snackable bites anyone can digest, regardless of job tile, level, or industry.

GenAI for beginners: This free 12-lesson course is designed to teach beginners everything they need to know to start building with generative AI. 

Microsoft Learn Cloud Skills Challenge—Microsoft Ignite Edition: Skill up for in-demand Data & AI tech scenarios and enter to win a VIP pass to the next Microsoft Ignite or Microsoft Build by completing a challenge by January 15, 2024. 

Cloud Workshop for the SQL Professional—If you missed this workshop at the PASS Data Summit, the labs are available to go through at your own pace. Our next workshop will be in March at the Microsoft Fabric Community Conference, so be sure to register and come see us in Las Vegas.

Accelerate your AI journey with key solution accelerators—Get started building intelligent apps on Azure with newly-published demos, GitHub repos, and Hackathon content to create AI-powered, intelligent apps. Customers and partners can “Build Your Own Copilot” using a click-through demo, sellers and partners can run a Hackathon with customers, and developers can leverage a code repo to quickly build solutions in their Azure account.

What will you build in 2024? Transform your business with a trusted partner

The ability of AI to accelerate transformation across industries, organizations, and daily life will certainly continue at an intense pace in 2024. Microsoft is proud to be your trusted partner in this era of AI and we’re committed to helping you achieve more for your business. I am excited to see how data, AI, and digital applications innovation unfolds for your business in the new year!

Source: microsoft.com

Continue reading

Securing Azure SQL Databases with managed identities just got easier

We are happy to share the second preview release of the Azure Services App Authentication library, version 1.2.0. This release enables simple and seamless authentication to Azure SQL Database for existing .NET applications with no code changes – only configuration changes! Up until this release, developers who wanted their existing SQL applications to use managed identities and AAD-based authentication were required to make code changes to retrieve and set the access token used for authentication. As many developers are familiar with the App Authentication library, they opted to use it to retrieve the access token, as below:

SqlConnection connection = new SqlConnection(connectionString);
connection.AccessToken = await (new AzureServiceTokenProvider()).GetAccessTokenAsync("https://database.windows.net/")
connection.Open();

Implementing and testing these code changes were often not straightforward. This was compounded further with EntityFramework where these code changes were more difficult and error-prone for developers. However, in .NET Framework 4.7.2, new functionality for SqlClient was added that enables developers to register a SQL authentication provider via an application's configuration. We've already done the work of implementing this SQL authentication provider for developers, so now the only actions they need to do are a few simple configuration changes to register our provider, as shown below.

With this release of the App Authentication library, we hope to enable many developers to try out our new functionality in existing SQL-backed solutions and gain the security benefits that the App Authentication library and managed identities afford.

Before, using a connection string containing credentials:

<connectionStrings>
   <!-- using connection string containing credentials -->
   <add name="MyDbConnection" connectionString="Data Source=tcp:[SQL Server Name].database.windows.net;Initial Catalog=[SQL DB name];UID=[UID];Password=[pwd]" providerName="System.Data.SqlClient" />
</connectionStrings>

After, registering App Authentication’s SqlAppAuthenticationProvider:

<configSections>
   ...
   <!-- Change #1: Register the new SqlAuthenticationProvider configuration section -->
   <section name="SqlAuthenticationProviders" type="System.Data.SqlClient.SqlAuthenticationProviderConfigurationSection, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
</configSections>
<connectionStrings>
   <!-- Change #2: Update the connection string to remove credentials and reference the registered SQL authentication provider from change #3 -->
   <add name="MyDbConnection" connectionString="Data Source=tcp:[SQL Server Name].database.windows.net;Initial Catalog=[SQL DB Name];UID=AnyString;Authentication=Active Directory Interactive" providerName="System.Data.SqlClient" />
</connectionStrings>
<!-- Change #3: Add the new SqlAuthenticationProvider configuration section, registering the built-in authentication provider in AppAuth library -->
<SqlAuthenticationProviders>
   <providers>
     <add name="Active Directory Interactive" type="Microsoft.Azure.Services.AppAuthentication.SqlAppAuthenticationProvider, Microsoft.Azure.Services.AppAuthentication" />
   </providers>
</SqlAuthenticationProviders>

Continue reading

Optimize price-performance with compute auto-scaling in Azure SQL Database serverless

Optimizing compute resource allocation to achieve performance goals while controlling costs can be a challenging balance to strike especially for database workloads with complex usage patterns. To help address these challenges, we are pleased to announce the preview of Azure SQL Database serverless. SQL Database serverless (preview) is a new compute tier that optimizes price-performance and simplifies performance management for databases with intermittent and unpredictable usage. Line-of-business applications, dev/test databases, content management and e-commerce systems are just some examples across a range of applications that often fit the usage pattern ideal for SQL Database serverless. SQL Database serverless is also well-suited for new applications with compute sizing uncertainty or workloads requiring frequent rescaling in order to reduce costs. The serverless compute tier enjoys all the fully managed, built-in intelligence benefits of SQL Database and helps accelerate application development, minimize operational complexity, and lower total costs.

Compute auto-scaling

SQL Database serverless automatically scales compute for single databases based on workload demand and bills for compute used per second. Serverless contrasts with the provisioned compute tier in SQL Database which allocates a fixed amount of compute resources for a fixed price and is billed per hour. Over short time scales, provisioned compute databases must either over-provision resources at a cost in order to accommodate peak usage or under-provision and risk poor performance. Over longer time scales, provisioned compute databases can be rescaled, but this solution may require predicting usage patterns or writing custom logic to trigger rescaling operations based on a schedule or performance metrics. This adds to development and operational complexity. In serverless, compute scaling within configurable limits is managed by the service to continuously right-size resources. Serverless also provides an option to automatically pause the database during inactive usage periods and automatically resume when activity returns.

Pay only for compute used

In SQL Database serverless, compute is only billed based on the amount of CPU and memory used per second.  While the database is paused only storage is billed, providing additional price optimization benefit.

For example, consider a line-of-business application or a dev/test database that is idle at night, but needs multi-core bursting headroom throughout the day. Suppose the application is using a serverless database configured to allow auto-pausing and auto-scaling up to 4 vcores and has the following usage pattern over a 24 hour period:

As can be seen, database usage corresponds to the amount of compute billed which is measured in units of vcore seconds and sums to around 46k vcore seconds over the 24 hour period. Suppose the compute unit price for the serverless database is around $0.000073/vcore/second. Then the compute bill for this one day period is just under $3.40. This is calculated by multiplying the compute unit price by the total number of vcore seconds accumulated. During this time period, the database was auto-paused while idle and enjoyed the benefit of bursting episodes up to 80 percent of 4 vcores without customer intervention. In this example, the price savings using serverless is significant compared to a provisioned compute database configured with the same 4 vcore limit.

Price-performance trade-offs

When using SQL Database serverless there are price-performance trade-offs to consider. These trade-offs are related to the compute unit price and the impact on application performance due to compute warm-up after periods of low or idle usage.

Compute unit price

The compute unit price is higher for a serverless database than for a provisioned compute database since serverless is optimized for workloads with intermittent usage patterns. If CPU or memory usage is high enough and sustained for long enough, then the provisioned compute tier may be less expensive.

Compute warm-up after low usage

While a serverless database is online, memory is gradually reclaimed if CPU or memory usage is low enough for long enough. When workload activity returns, disk IO may be required to rehydrate data pages into the SQL buffer pool or query plans may need to be recompiled. This memory management policy to reclaim cache based on low usage is unique to SQL Database serverless and done to control customer costs, but can impact performance. Memory reclamation based on low usage does not occur in the provisioned compute tier for single databases or elastic pools where this kind of impact can be avoided.

Compute warm-up after pausing

The latency to pause and resume a serverless database is usually around one minute or less during which time the database is offline. After the database is resumed, memory caches need to be rehydrated which adds additional latency before optimal performance conditions return. The idle period that must elapse before auto-pausing occurs can be configured to compensate for this performance impact. Alternatively, auto-pausing can be disabled for workloads sensitive to this impact and still benefit from auto-scaling. Compute minimums are billed while the database is online regardless of usage, and so disabling auto-pausing can increase costs.

Continue reading

Azure SQL Database Edge: Enabling intelligent data at the edge

The world of data changes at a rapid pace, with more and more data being projected to be stored and processed at the edge. Microsoft has enabled enterprises with the capability of adopting a common programming surface area in their data centers with Microsoft SQL Server and in the cloud with Azure SQL Database. We note that latency, data governance and network connectivity continue to gravitate data compute needs towards the edge. New sensors and chip innovation with analytical capabilities at a lower cost enable more edge compute scenarios to drive higher agility for business.

At Microsoft Build 2019, we announced Azure SQL Database Edge, available in preview, to help address the requirements of data and analytics at the edge using the performant, highly available and secure SQL engine. Developers will now be able to adopt a consistent programming surface area to develop on a SQL database and run the same code on-premises, in the cloud, or at the edge.

Azure SQL Database Edge offers:

◉ Small footprint allows the database engine to run on ARM and x64 devices via the use of containers on interactive devices, edge gateways, and edge servers.

◉ Develop once and deploy anywhere scenarios through a common programming surface area across Azure SQL Database, SQL Server, and Azure SQL Database Edge

◉ Combines data streaming and time-series, with in-database machine learning to enable low latency analytics

◉ Industry leading security capabilities of Azure SQL Database to protect data-at-rest and in- motion on edge devices and edge gateways, and allows management from a central management portal from Azure IoT.

◉ Cloud connected, and fully disconnected edge scenarios with local compute and storage.

◉ Supports existing business intelligence (BI) tools for creating powerful visualizations with Power BI and third-party BI tools.

◉ Bi-directional data movement between the edge to on-premises or the cloud.

◉ Compatible with popular T-SQL language, developers can implement complex analytics using R, Python, Java, and Spark, delivering instant analytics without data movement, and real-time faster insights

◈ Provides support for processing and storing graph, JSON, and time series data in the database, coupled with the ability to apply our analytics and in-database machine learning capabilities on non-relational datatypes.

For example, manufacturers that employ the use of robotics or automated work processes can achieve optimal efficiencies by using Azure SQL Database Edge for analytics and machine learning at the edge. These real-world environments can leverage in-database machine learning for immediate scoring, initiating corrective actions, and detecting anomalies.

Key benefits:

◈ A consistent programming surface area as Azure SQL Database and SQL Server, the SQL engine at the edge allows engineers to build once for on-premises, in the cloud, or at the edge.
◈ The streaming capability enables instant analysis of the incoming data for intelligent insights.
◈ In-Database AI capabilities enables scenarios like anomaly detection, predictive maintenance and other analytical scenarios without having to move data.

Train in the cloud and score at the edge

Supporting a consistent Programming Surface Area across on-premises, in the cloud, or at the edge, developers can use identical methods for securing data-in-motion and at-rest while enabling high availability and disaster recovery architectures equal to those used in Azure SQL Database and SQL Server. Giving seamless transition of the application from the various locations means a cloud data warehouse could train an algorithm and push the machine learning model to Azure SQL Database Edge and allow it to run scoring locally, giving real-time scoring using a single codebase.

Intelligent store and forward

The engine provides proficiencies to take streaming datasets and replicate them directly to the cloud, coupled with enabling an intelligent store-and-forward pattern. In duality, the edge can leverage its analytical capabilities while processing streaming data or applying machine learning using in-database machine learning. Fundamentally, the engine can process data locally and upload using native replication to a central datacenter or cloud for aggregated analysis across multiple different edge hubs.

Continue reading

Static Data Masking for Azure SQL Database and SQL Server

The SQL Security team is pleased to share the public preview release of Static Data Masking. Static Data Masking is a data protection feature that helps users sanitize sensitive data in a copy of their SQL databases.

Use cases

Static Data Masking is designed to help organizations create a sanitized copy of their databases where all sensitive information has been altered in a way that makes the copy sharable with non-production users. Static Data Masking can be used for:

◈ Development and testing

◈ Analytics and business reporting

◈ Troubleshooting

◈ Sharing the database with a consultant, a research team, or any third-party

Static Data Masking facilitates compliance with security requirements such as the separation between production and dev/test environments. For organizations subject to GDPR, the feature is a convenient tool to remove all personal information while preserving the structure of the database for further processing.

How Static Data Masking works

With Static Data Masking, the user configures how masking operates for each column selected inside the database. Static Data Masking will then replace data in the database copy with new, masked data generated according to that configuration. Original data cannot be unmasked from the masked copy. Static Data Masking performs an irreversible operation.

In the example below, all entries in the column FirstName have been nullified. The column LastName is made of randomly generated strings. In the EmailAddress column, names have been replaced with randomly generated strings, but the domain extension has been maintained. A similar narrative applies to the Phone column where the area code has been preserved, but not the last 7 digits.

Static Data Masking vs. Dynamic Data Masking

Data masking is the process of applying a mask on a database to hide sensitive information and replace it with new data or scrubbed data. Microsoft offers two masking options, Static Data Masking and Dynamic Data Masking

Static Data Masking	Dynamic Data Masking
Happens on a copy of the database Original data not retrievable Mask occurs at the storage level All users have access to the same masked data	Happens on the original database Original data intact Mask occurs on-the-fly at query time Mask varies based on user permission

How to download Static Data Masking

Static Data Masking ships with SQL Server Management Studio 18.0. The latest preview SQL Server Management Studio 18.0 is available today for download.

Compatibility

Static Data Masking is compatible with SQL Server (SQL Server 2012 and newer), Azure SQL Database (DTU and vCore-based hosting options, excluding Hyperscale), and SQL Server on Azure Virtual Machines.

Continue reading

Blazing fast data warehousing with Azure SQL Data Warehouse

We announced general availability of Azure SQL Data Warehouse (SQL DW) Compute Optimized Gen2 tier, the new generation of Azure SQL DW. Azure SQL DW is a fast, flexible, and secure cloud data warehouse tuned for running complex queries fast and across petabytes of data.

We see two key trends that drive data warehousing decisions, the amount of data continues to grow exponentially and the need to deliver insights from all this data is even more urgent. Azure SQL DW Compute Optimized Gen2 tier is designed to help customer accomplish just this by delivering dramatic query performance improvement. In addition, SQL DW now supports up to 128 concurrent queries while being able to provision five times more computing power compared to the previous product generation.

“After upgrading to the Gen2 of SQL Data Warehouse, our data warehouse workload has seen an average of 5.4 times performance improvement. This enhancement to the service is phenomenal and helps us deliver key customer insights for our business” said Brent Niezgocki, Senior Software Engineer for the Azure Active Directory analytics team at Microsoft.

Fast query performance through adaptive caching

As organizations look to accelerate time to insight, performance in the domain of interactive queries continues to be a top requirement. One of the biggest bottlenecks and pain points for delivering high performance is disk access. This holds especially true in cloud computing where the distance from the compute layer to the storage layer can potentially vary, leading to suboptimal query performance. Azure SQL DW Compute Optimized Gen2 tier delivers built-in adaptive caching that automatically caches data based on workload characteristics and query access patterns. Combining in-memory and disk-deployed caches, SQL Data Warehouse automatically and transparently moves frequently accessed data across various caching tiers. This caching strategy leads to faster data access and ultimately faster query performances. In addition to smart caching strategies, Azure SQL DW Compute Optimized Gen2 tier now leverages the latest hardware innovations that Azure offers. Together, all of this enables Azure SQL DW Compute Optimized Gen2 tier to deliver the next level of performance characteristics. On average, query workloads see five times the performance improvements compared to SQL DW Gen1.

Powering enterprise-wide dashboards with high concurrency

Many organizations rely on Business Intelligence (BI) tools to get insight into various business metrics and KPIs. However, existing data warehousing systems typically limit the number of concurrent queries that can be processed and executed, leading to suboptimal user experiences. Organizations are forced to place tight control over valuable data stored in their data warehouses, or to limit how many users can simultaneously query their data warehouses. These circumstances lead to analysis delays.

Not anymore, Azure SQL DW Compute Optimized Gen2 tier increases the number of concurrent queries that can be executed. With support for 128 concurrent queries, Azure SQL DW delivers four times more concurrency compared to the previous generation. This improvement allows organizations to satisfy the most demanding needs of their business and data analysts. To enable new concurrency levels, workload management functionality has been extended. Each SQL Data Warehouse SLO comes with predefined query capacity quotas. As the query concurrency grows, available capacity is consumed until the quota is reached. All this is happening completely transparently to the data warehouse end users, allowing your data warehouse to be used by hundreds of users who are using their BI tool of choice.

In the picture below, we see system throughput under the concurrency. Various queries were submitted to the system with 30s delay from each other. We see that number of active queries grows to 128, stays there for some time and ultimately drops down to zero as queries complete execution. While SQL DW was processing active queries, few queries were queued until resources became available for them to be processed.

Predictable performance through scaling

Organizations have been faced with an ever-growing demand to store and operate on larger and larger data sets. The ability to grow and shrink your data warehouse elastically, as your data needs to grow and shrink, is one of the key characteristics of SQL Data Warehouse. Azure SQL DW Compute Optimized Gen2 tier enables two additional capabilities in this area, the ability to store unlimited data in SQL’s columnar format, and the availability of new SLOs with an additional five times the compute capacity.

SQL Data Warehouse now delivers unlimited columnstore storage capacity. With this new capability, you might wonder if there is enough compute power to deliver acceptable query performances on larger and larger data. To allow for effective data processing and to deliver predictable performances for growing data, Azure SQL DW Compute Optimized Gen2 tier increases the compute power by introducing additional SLOs (DW7500c, DW10000c, DW15000c and DW30000c).

To demonstrate the effects of scaling to these new SLOs, the picture below shows execution times after running the same set of queries over the same data while varying the SLOs. These three queries were executed on DW6000c and DW15000c respectively. We see that execution times improved with using higher SLO level (lower bar indicates response time improvement). The queries used exercise common analytical query patterns such as aggregations, joins, predicates, in clauses, subselects, and data ordering.

Continue reading

A flexible new way to purchase Azure SQL Database

We’re excited to announce the preview of an additional purchasing model to the Azure SQL Database Elastic Pool and Single Database deployment options. SQL Database Managed Instance, the vCore-based model reflects our commitment to customer choice by providing flexibility, control, and transparency. As with Managed Instance, the vCore-based model makes the Elastic Pool and Single Database options eligible for up to 30 percent savings* with the Azure Hybrid Benefit for SQL Server.

Optimize flexibility and performance with two new service tiers

The new vCore-based model introduces two service tiers, General Purpose and Business Critical. These tiers let you independently define and control compute and storage configurations, and optimize them to exactly what your application requires.  If you’re considering a move to the cloud, the new model also provides a straightforward way to translate on-premises workload requirements to the cloud. General Purpose is designed for most business workloads and offers budget-oriented, balanced, and scalable compute and storage options. Business Critical is designed for business applications with high IO requirements and offers the highest resilience to failures.

Choosing between DTU and vCore-based performance levels

You want the freedom to choose what’s right for your workloads and we’re committed to supporting the DTU-based model alongside the new vCore-based option. Looking for a simple way to purchase and configure resources? The DTU-based model provides preconfigured bundles of resources across a range of performance options. If you are not concerned with customizing the underlying resources and prefer the simplicity of paying a fixed amount each month, you may find the DTU-based model more suitable for your needs. However, if you need more insights into the underlying resources or need to scale them independently to achieve optimal performance, the vCore-based model is the best choice. The vCore-based model is also a good choice if you own SQL Server licenses that you would like to move to the cloud. Migration between DTU-based and vCore-based performance levels is a simple online operation and is similar to the current process of upgrading from the Standard to Premium service tiers.

Save up to 30 percent* on vCore-based options with Azure Hybrid Benefit for SQL Server

Save more on vCore-based options when you use the Azure Hybrid Benefit for SQL Server. This benefit is exclusive to Azure and enables you to use your SQL Server Enterprise Edition or Standard Edition licenses with active Software Assurance to pay a reduced rate on a vCore-based Single Database, Elastic Pool or Managed Instance, with savings up to 30 percent.

Getting started!

The new vCore-based service tiers will be available in all Azure regions by April 6, 2018. If you already have an Azure SQL database, you can switch to the new service tiers in the portal and configure the database as illustrated by the following diagrams. Otherwise, you can create a new database in the General Purpose or Business Critical service tiers.

For more information about the vCore-based purchasing options visit our service tier documentation.

*Savings based on a 8 vCore Business Critical Managed Instance in East US Region, running 730 hours per month. Savings are calculated from full price (license included) against reduced price (applying Azure Hybrid Benefit for SQL Server), which includes the Software Assurance cost for SQL Server Enterprise edition. Actual savings may vary based on region, performance tier, and Software Assurance tier.

Continue reading

Sync SQL data in large scale using Azure SQL Data Sync

Azure SQL Data Sync allows users to synchronize data between Azure SQL Databases and SQL Server databases in one-direction or bi-direction. This feature was first introduced in 2012. By that time, people didn't host a lot of large databases in Azure. Some size limitations were applied when we built the data sync service, including up to 30 databases (five on-premises SQL Server databases) in a single sync group, and up to 500 tables in any database in a sync group.

Today, there are more than two million Azure SQL Databases and the maximum database size is 4TB. But those limitations of data sync are still there. It is mainly because that syncing data is a size of data operation. Without an architectural change, we can’t ensure the service can sustain the heavy load when syncing in a large scale. We are working on some improvements in this area. Some of these limitations will be raised or removed in the future. In this article, we are going to show you how to use data sync to sync data between large number of databases and tables, including some best practices and how to temporarily work around database and table limitations.

Sync data between many databases

Large companies and ISVs use data sync to distribute data from a central master database to many client databases. Some customers have hundreds or even thousands of client databases in the whole topology. Users may hit one of the following issues when trying to sync between many databases:

1. Hit the 30 databases per sync group limitation.

2. Hit the five on-premises SQL Server databases per sync group limitation.

3. Since all member databases will sync with the hub database, there’s significant performance impact to workload running in the hub database.

To work around the 30 databases or five on-premises databases per sync group limitation, we suggest you use a multi-level sync architecture. You can create a sync group to sync your master database with several member databases. And those member databases can become the hub databases of the sub sync groups and sync data to other client databases. According to your business and cost requirement, you can use the databases in the middle layers as client databases or dedicated forwarders.

There are benefits from this multi-level sync architecture even you don’t hit the 30 databases per sync group limitation:

◈ You can group clients based on certain attributes (location, brand…) and use different sync schema and sync frequency.

◈ You can easily add more clients when your business is growing.

◈ The forwarders (member databases in the middle layers) can share the sync overhead from the master database.

To make this multi-level sync topology work in your system, you will need a good balance between how many client databases in a single sync group and how many levels in the overall system. The more databases in a single sync group, the higher impact it will add to the overall performance in the hub database. The more levels you have in your system, the longer it takes to have data changes broadcasted to all clients.

When you are adding more member databases to the system, you need to closely monitor the resource usage in the hub databases. If you see consistent high resource usage, you may consider upgrading your database to a higher SLO. Since the hub database is an Azure SQL database, you can upgrade it easily without downtime.

Sync data between databases with many tables

Currently, data sync can only sync between databases with less than 500 tables. You can work around this limitation by creating multiple sync groups using different database users. For example, you want to sync two databases with 900 tables. First, you need to define two different users in the database where you load the sync schema from. Each user can only see 450 (or any number less than 500) tables in the database. Sync setup requires ALTER DATABASE permission which implies CONTROL permission over all tables so you will need to explicitly DENY the permissions on tables which you don’t want a specific user to see, instead of using GRANT. You can find the exact privilege needed for sync initialization in the best practice guidance. Then you can create two sync groups, one for each user. Each sync group will sync 450 tables between these two databases. Since each user can only see less than 500 tables, you will be able to load the schema and create sync groups! After the sync group is created and initialized, we recommend you follow the best practice guidance to update the user permission and make sure they have the minimum privilege for ongoing sync.

Optimize the sync initialization

After the sync group is created, the first time you trigger the sync, it will create all tracking tables and stored procedures and load all data from source to target database. The initial data loading is a size-of-data operation. Initializing sync between large databases could take hours or even days if it is not set up properly. Here are some tips to optimize the initialization performance:

1. Data sync will initialize the target tables using bulk insert if the target tables are empty. If you have data on both sides, even if data in source and target databases are identical (data sync won’t know that!), data sync will do a row-by-row comparison and insertion. It could be extremely slow for large tables. To gain the best initialization performance, we recommend you consolidate data in one of your databases and keep the others empty before setting up data sync.

2. Currently, the data sync local agent is a 32 bits application. It can only use up to 4GB RAM. When you are trying to initialize large databases, especially when trying to initialize multiple sync groups at the same time, it may run out of memory. If you encountered this issue, we recommend you add part of the tables into the sync group first, initialize with those tables, and then add more tables. Repeat this until all tables are added to the sync group.

3. During initialization, the local agent will load data from the database and store it as temp files in your system temp folder. If you are initializing sync group between large databases, you want to make sure your temp folder has enough space before you start the sync. You can change your temp folder to another drive by set the TEMP and TMP environment variables. You will need to restart the sync service after you update the environment variable. You can also add and initialize tables to the sync group in batch. Make sure the temp folder is cleaned up between each batch.

4. If you are initializing data from on-premises SQL Server to Azure DB, you can upgrade your Azure DB temporarily before the initialization. You can downgrade the database to the original SLO after the initialization is done. The extra cost will be minimum. If your target database is SQL Server running in a VM, add more resources to the VM will do the same.

Experiment of sync initialization performance

Following is the result of a simple experiment. I created a sync group to sync data from a SQL Server database in Azure VM to an Azure SQL database. The VM and SQL database are in the same Azure region so the impact of network latency could be ignored. It was syncing one table with 11 columns and about 2.1M rows. The total data size is 49.1GB. I did three runs with different source and target database configuration:

In the first run, the target database is S2 (50 DTU), and source database is running in D4S_V3 VM (4 vCPU, 16GB RAM). It takes 50 min to extract data to the temp folder and 471 min to load the data from the temp folder to the target database.

I upgraded the target database to S6 (400 DTU) and the Azure VM to D8S_V3 (8 vCPU, 32GB RAM) for the second run. It reduced the loading time to 98 min! The data extracting surprisingly took longer time in this run. I can’t explain the regression since I didn’t capture the local resource usage during the run. It might be some disk I/O issue. Even though, upgrading the target database to S6 reduced the total initialization time from 521 min to 267 min.

In the third run, I upgraded the target database to S12 (3000 DTU) and used the local SSD as temp folder. It reduced data extract time to 39 min, data loading time to 56 min and the total initialization time to 95 min. It was 5.5 time faster than the first configuration with extra cost of a cup of coffee!

Continue reading

Migrating to Azure SQL Database with zero downtime for read-only workloads

Microsoft uses an internally written service called MSAsset to manage all Microsoft data center hardware around the world. MSAsset is used for tracking Microsoft’s servers, switches, storage devices, and cables across the company and requires 24/7 availability to accommodate break-fix requirements.

Before migrating to Azure SQL Database last year, MSAsset’s data tier consisted of a 107 GB database with 245 tables on SQL Server. The database was part of a SQL Server Always On Availability Group topology used for high availability and the scaling out of read-activity.

The MSAsset engineering team faced the following issues:

◈ Aging hardware was not keeping up with stability and scale requirements.
◈ There was an increase in high severity data-tier incidents and no database administrator on staff to help with troubleshooting, mitigation, root cause analysis and ongoing maintenance.
◈ MSAsset’s database ran on SQL Server 2012. Developers and internal customers were increasingly requesting access to new SQL Server functionality.

After exploring various options and weighing several factors, the MSAsset engineering team decided that Azure SQL Database was the appropriate data tier for their future investment and would address all of their key pain points. With the move to Azure SQL Database, they gained increased scalability, built-in manageability and access to the latest features.

With 24/7 availability requirements, the engineering team needed to find a way to migrate from SQL Server to Azure SQL Database without incurring downtime for read-only activity. MSAsset is a read-heavy service, with a much smaller percent of transactions involving data modifications. Using a phased approach, they were able to move to Azure SQL Database with zero downtime for read-only traffic and less than two hours of down time for read-write activity. This case study will briefly describe how this was accomplished.

The original MSAsset architecture

The original MSAsset application architecture consisted of a web tier with read-write access to the primary database located on a SQL Server 2012 instance. The database was contained within an Always On Availability Group with one synchronous read-only secondary replica and three read-only asynchronous secondary replicas. The application used an availability group listener to direct incoming write traffic to the primary replica. To accommodate the substantial amount of read-only reporting traffic, a proprietary load balancer was used to direct requests across the read-only secondary replicas using a round-robin algorithm.

When planning for a move to Azure SQL Database as with the legacy SQL Server solution, the proposed new solution needed to accommodate one read-write database and depending on the final migrated workload volume and associated Azure SQL Database resource consumption one or more read-only replicas.

Using a phased migration approach

The MSAsset engineering team used a phased incremental approach for moving from SQL Server to Azure SQL Database.  This incremental approach helped reduce the risk of project failure and allowed the team to learn and adapt to the inevitable unexpected variables that arise with complex application migrations.

The migration phases were as follows:

1. Configure hybrid SQL Server and Azure SQL Database read-only activity, while keeping all read-write activity resident on the legacy SQL Server database.

◈ Set up transactional replication from SQL Server to Azure SQL Database, for use in accommodating read-only activity.

◈ Monitor the replication topology for stability, performance, and convergence issues. 

◈ As needed, create up to four active geo-replication readable secondary databases in the same region to accommodate read-only traffic scale requirements.

◈ Once it is confirmed the topology is stable for a sustained period of time, use load-balancing to direct read-only activity to Azure SQL Database, beginning with 25 percent of the read-only traffic. Over a period of weeks, increase to 50 percent, and then 75 percent. For load balancing, the MSAsset engineering team uses a proprietary application-layer library.

◈ Along the way, use Query Performance Insight to monitor overall resource consumption and top queries by CPU, duration, execution count. MSAsset also monitored application metrics, including API latencies and error rates.

◈ Adjust the Azure SQL Database service tiers and performance levels as necessary.

◈ Move or redirect any high-resource consuming unnecessary legacy traffic to bulk access endpoints.

2. After stabilizing in the prior phase of 75 percent read-only activity on Azure SQL Database, move 100 percent of the read-only traffic to Azure SQL Database.

◈ Again, use Query Performance Insight to monitor overall resource consumption and top queries by CPU, duration, execution count. Adjust the Azure SQL Database service tiers and performance levels as necessary and create up to four active geo-replication readable secondary databases in the same region to accommodate read-only traffic.

3. Prior to the final cut-over to Azure SQL Database, develop and fully test a complete rollback plan. The MSAsset team used SQL Server Data Tools (SSDT) data comparison functionality to collect the delta between Azure SQL Database and a  four day old backup and then applied the delta to the SQL Server database.

4. Lastly, move all read-write traffic to Azure SQL Database. In MSAsset’s case, in preparation for the final read-write cutover they reseeded, via transactional replication a new database in Azure SQL Database for read-write activity moving forward. Steps they followed:

5. After the full reseeding, wait for remaining transactions on SQL Server to drain before removing the transactional replication topology.

6. Change the web front-end configuration to use the Azure SQL Database primary database for all read-write activity. Use read-only replicas for read-only traffic.

7. After a full business cycle of monitoring, de-commission the SQL Server environment.

This phased approach allowed the MSAsset team to incur no downtime for read-only activity and also helped them minimize risk, allowing enough time to learn and adapt to any unexpected findings without having to revert to the original environment. 

The final MSAsset architecture uses one read-write Azure SQL Database replica and four active geo-replication readable secondary databases. 

The remaining sections will talk about key aspects and lessons learned from the migration effort.

Creating a read-only Azure SQL Database using Transactional Replication

The first phase involved setting up transactional replication from SQL Server to Azure SQL Database, ensuring a stable replication topology with no introduced performance or convergence issues. 

The MSAsset engineering team used the following process for setting up transactional replication:

◈ They first reviewed the existing SQL Server database against the requirements for replication to Azure SQL Database. These requirements are detailed in the Replication to SQL Database documentation. For example, a small number of the legacy tables for MSAsset did not have a primary key, and so a primary key had to be added in order to be supported for transactional replication. Some of the tables were no longer being used, and so it was an opportunity to clean up stale objects and associated code.

◈ Since the MSAsset publication was hosted on an Always On Availability Group, the MSAsset team followed the steps detailed here for configuration transactional replication: Configure Replication for Always On Availability Groups (SQL Server).

Once transactional replication was configured and fully synchronized, read-only traffic was first directed to both SQL Server and Azure SQL Database with read-write activity continuing to go just against the SQL Server-resident database.

The read-only traffic against Azure SQL Database was incrementally increased over time to 25 percent, 50 percent, and 75 percent, with careful monitoring along the way to ensure sufficient query performance and DTU availability. The MSAsset team used a proprietary load balancing application library to distribute load across the various read-only databases. Once stabilized at 75 percent, the MSAsset team moved 100 percent of read-only activity to Azure SQL Database and continued with the other phases described earlier.

Cleanup opportunities

The MSAsset team also used this as an opportunity to clean up rogue reporting processes. This included in-house Microsoft reporting tools and applications that, while being permitted to access the database had other data warehouse options that were more appropriate for ongoing use than MSAsset. When encountering rogue processes, the MSAsset team reached out to the owners and had them re-route to appropriate data stores. Disused code and objects, when encountered, were also removed.

Redesigning around compatibility issues

The MSAsset team discovered two areas that required re-engineering prior to migration to Azure SQL Database:

◈ Change Data Capture (CDC) was used for tracking data modifications on SQL Server. This process was replaced with a solution that leverages temporal tables instead.

◈ SQL Server Agent Jobs were used for executing custom T-SQL scheduled jobs on SQL Server. All SQL Server Agent Jobs were replaced with Azure worker roles that invoked equivalent stored procedures instead.

Understanding networking and connectivity with Azure SQL Database

With an array of services requiring access to MSAsset’s data tier, the engineering team had to familiarize themselves with Azure SQL Database networking and connectivity requirements as well as fundamentals. Having this background was a critical aspect of the overall effort and should be a core focus area of any migration plan to Azure SQL Database.

Modernizing the platform and unlocking cloud scalability

The original MSAsset SQL Server hardware was powerful, but old. Before moving to Azure SQL Database, the MSAsset engineering considered replacing the servers. But they were concerned about the projected cost and ability for the hardware to keep up with MSAsset’s projected workload growth over the next five years. The MSAsset engineering team was also concerned about keeping up with the latest SQL Server versions and having access to the latest features.

Moving to Azure SQL Database means that the MSAsset team can scale resources much more easily and no longer have to worry about outgrowing their existing hardware. They can also now access new features as they become available in Azure SQL Database without having to explicitly upgrade. They are also now able to leverage built-in capabilities unique to Azure SQL Database like Threat Detection and Query Performance Insight.

Reducing high severity issues and database management overhead

The MSAsset engineering team has no database administrator on staff, so coupled with the support of old hardware and standard DBA maintenance requirements, these factors were a major contributor to increasingly frequent high severity incidents.

Moving to Azure SQL Database, the MSAsset team no longer worries about ongoing database server patching, backups, or complex high availability and disaster recovery topology configuration. Since moving to Azure SQL Database, the MSAsset engineering team has seen an 80 percent reduction in high severity issues for their data tier.

Continue reading