We’ve been on the road the last couple of weeks at MVP Summit, SQLBits and Fabric Con, connecting with customers talking about the value of migration and modernization. We want to dig into specifically, how Azure can deliver real business value through cost optimization and streamlined productivity for their Windows Server and SQL Server deployments when they migrate to Azure. 

We’ve helped countless organizations migrate their SQL Server and Windows workloads to Azure a critical 1^st step in any transformation initiative. The move can help improve cybersecurity posture and business continuity, boost productivity, and lay the foundation for AI and other highly scalable data innovations, while automating updates, backups, and other time-consuming IT tasks. 

Modernize and lower total cost of ownership (TCO) 

Migration is a business strategy that pays off. In The Business Value of Microsoft Azure SQL Database and Azure SQL Managed Instance Workload,¹ organizations that migrated to Azure SQL Managed Instance and Microsoft Azure SQL Database can get up to 406 percent return on investment over 3 years and can expect a 30-percent reduction in TCO over 5 years, protecting an additional $6.85 million in annual revenue.

A separate study found that customers that migrated both Windows Server and SQL Server workloads to Azure generated more value. According to The Business Value of Microsoft Azure for SQL Server and Windows Server Workloads,² by optimizing costs, operations, and business opportunities, companies gained $15.85 million in total annual benefits while also increasing IT security efficiency by 43 percent with cloud tools and automation.

Azure SQL

Migrate, modernize, and innovate with the modern SQL family of cloud database services.

Start today

A smooth path to migration, a more powerful destination

Migrating to a cloud platform is an essential step on the journey to modernization, and there are many choices. 

What makes SQL unique is that it’s built on the same engine, no matter where you deploy, which means you can build on your existing SQL experience while gaining the layered security, intelligent threat detection, and data encryption that Azure provides. And as we shared with customers at SQLBits, there’s now an even more powerful option available for customers looking to leverage the full PaaS experience. Azure SQL Managed Instance Next-gen GP  brings significantly improved performance and scalability to power up your existing Azure SQL Managed Instance fleet, and help bring more mission-critical SQL workloads to Azure. With close to 100 percent feature compatibility with SQL Server, Azure SQL Managed Instance is the recommended choice to migrate and modernize SQL apps at scale and at your own pace.

Another option many of our customers start with is by running their Windows Server workloads on Azure Virtual Machines, benefiting from a simplified, managed experience and cloud-native support for SQL Server, .NET apps, and Remote Desktop Services. Or you can modernize your entire Windows Server estate, choosing from more than 200 Azure services and capabilities, including support for hybrid environments. 

Take the first step or the next: You have choices

When it comes to migration, Azure meets you where you are with options for moving on-premises workloads and for developing new cloud solutions. For example, many organizations start by moving Windows Server workloads to Azure Virtual Machines, enabling them to easily scale to support new developments and more efficiently manage peak loads. Hokkoku Bank took this step, migrating its Windows Server–based estate to Azure as part of a cloud-first initiative. Azure supports the bank’s modernization plans and helps provide a disaster recovery solution in an earthquake-prone region.  

Correios de Portugal, the country’s postal service, migrated its Windows Server workloads to Azure Virtual Machines backed by Azure SQL, which provides a smooth path to a cost-effective, highly scalable, fully managed PaaS database. It’s the best choice for modernizing your apps and getting the most out of your existing investments.

Many of our database customers move to SQL Server on Azure Virtual Machines for the cost benefits on top of the scalability and resilience of Azure. As an example, healthcare software manufacturer Allscripts migrated on-premises applications to Azure SQL Database Managed Instance when possible, but another 600 on-premises VMs needed a different migration approach. Allscripts moved them to SQL Server on Azure Virtual Machines, a quick, low-risk step for workloads it plans to optimize and modernize later. The lift-and-shift approach can be an easy first   step in your cloud journey.

Azure also offers hybrid solutions that bridge your on-premises and cloud resources. For example, you can move or extend on-premises VMware environments using Azure VMWare Solution. You can even use the free Windows Admin Center tool to manage across Windows Server environments—physical, virtual, on-premises, in Azure, or in a hosted environment—at no additional cost. To get started with a Windows Server migration, start discovering and assessing on-premises resources using the free Azure Migrate tool.

Watch the Migrate to Innovate digital event on demand and learn the business benefits of migrating to Azure.

Try it for free 

If you want to know how your workload will perform before migrating, try these Azure offers and get started building that proof-of-concept.  

• Try Azure SQL Managed Instance for free. For 12 months, you can get up to two instances per Azure subscription, 750 vCore hours of compute per month, and 32 GB data storage and 32 GB backup storage per month. 

• Try Azure SQL Database for free. Test and develop applications or run small production workloads for free. This offer provides the first 100,000 vCore seconds, 32 GB of data, and 32 GB of backup storage per month at no charge for the lifetime of your subscription. 

• Try Windows Server in the cloud with an Azure free account. Extend your existing Windows Server datacenter and try out popular services free for 12 months. 

Learn more about Azure SQL

Stay tuned for more migration announcements in the coming months. To get started now: 

• Get the full ROI and TCO reports from IDC: The Business Value of Microsoft Azure SQL Database and Azure SQL Managed Instance Workload and The Business Value of Microsoft Azure for SQL Server and Windows Server Workloads.

• Review the benefits of migration in Better on Azure: The benefits of using Azure for Windows Server and SQL Server workloads. 

• Discover why cloud economics make sense and get greater return on your investment. 

1. IDC report, The Business Value of Microsoft Azure SQL Database and Azure SQL Managed Instance Workloads, IDC #US51073123, August 2023. 
2. The Business Value of Microsoft Azure for SQL Server and Windows Server Workloads

The post Why migrate Windows Server and SQL Server to Azure: ROI, innovation, and free offers appeared first on Microsoft SQL Server Blog.

Overview

Microsoft Purview is a family of data governance, risk, and compliance solutions that help organizations:

• Gain visibility into data assets across your organization. Data assets can be across data centers, multicloud, and software as a service (SaaS) data.
• Enable secure access to your data.
• Safeguard and manage sensitive data across clouds, apps, and endpoints.
• Manage data risks and regulatory compliance end-to-end.
• Empower your organization to govern, protect, and manage data in new, comprehensive ways.

The focus of this article is on using Microsoft Purview to enable access to user data as well as specific system metadata in SQL Server 2022 running on Azure Arc–enabled servers.

With the SQL Server 2022 release, the goal is to enable three main scenarios:

• Browsing data in user-defined tables and views.
• Performance monitoring with system commands, functions, and views.
• Security auditing with security-related system functions and views.

How it works

To complete these scenarios the data owner first needs to author a policy. A purview policy is a set of purview statements, each containing a purview role, scope, and assigned Azure AD principal.

Depending on the scenario, a purview role would be one of the built-in roles: Read, SQL Performance Monitor, or SQL Security Auditor.

With scope, the policy author defines an applicable target for the statement. It could be either a specific SQL Server or a wider scope like a resource group or a subscription.

To apply the policy, the author then needs to publish it to the target resources. One thing to keep in mind here is that it takes a couple of minutes for a policy to become active as it takes that much time for the SQL engine to pull it in.

Once the policy is active, the assigned users will be able to connect and perform actions assigned by the policy. The Connect permission is implicit, so there is no need to create logins or database users using T-SQL.

Let’s see this in a more visual fashion using the scenario walkthrough.

Scenario walkthrough

For SQL Server to be able to use policies, it needs to be registered with a Microsoft Purview account and enabled for Data use management.

In this scenario, Ana, who is an IT manager, wants to provide Chris, who is a data analyst, read-only access to tables in a SQL Server database so that Chris can create reports.

To accomplish this, Ana authors a policy that will allow Chris access to the target server.

When finished, Ana publishes this policy and applies it to SQL Server by publishing it.

Now Chris can connect to the SQL Server and execute queries against the server to which the policies are applied.

As highlighted above, Chris could connect to SQL Server even though there is no explicit login.

Summary

The new access policies feature provides data owners the capability to author access policies through the Microsoft Purview data-governance service experience and then apply them to SQL Server 2022 data sources individually or at scale. With this approach users are empowered to assign permissions to Azure Active Directory users at a scale without using T-SQL or the need to explicitly create logins or users on a server. In this release, the following server roles are eligible for assignment: Read, SQL Performance Monitor, and SQL Security Auditor.

Next steps

Microsoft Purview access policies for SQL Server 2022 are just one of the many benefits of migrating to SQL Server 2022.

Download the latest release of SQL Server 2022 if you haven’t already done so and check out the SQL Server 2022 Overview and What’s New references. There are many new features and improved functionality being added to this release.

Learn More

For more information and to get started, check out the following references:

Read What’s New in SQL Server 2022

To learn more about Microsoft Purview check out What is Microsoft Purview? | Microsoft Docs.

For a detailed how-to on Microsoft Purview access policies for SQL Server 2022 visit Provision access by data owner for SQL Server on Azure Arc-enabled servers (preview) – Microsoft Purview | Microsoft Docs.

To see how Microsoft Purview access policies could be published at scale see Resource group and subscription access provisioning by data owner (preview) – Microsoft Purview | Microsoft Docs.

The post Microsoft Purview access policies for SQL Server 2022 appeared first on Microsoft SQL Server Blog.

As the lead sponsor, we will deliver content including the keynote, five full-day training days, and over fifty general sessions. With so many opportunities to educate, we are bringing the full Azure data team including folks from across the data platform, such as SQL Server, Azure SQL, Cosmos DB, Azure Purview, Azure Synapse Analytics, and Power BI.

Start the week with my team for two day-long training sessions where you will have a unique chance to work directly with Microsoft engineering:

The Hands-on Azure SQL Workshop on March 8 will help you translate your existing SQL Server skills to Azure SQL. Bring your laptop and get ready to learn hands-on. You will gain a foundational knowledge of what to use when, as well as how to configure, monitor, and troubleshoot the “meat and potatoes” of SQL Server in Azure: security, performance, and availability.

Migrate SQL Server to Azure on Wednesday, March 9 will help you migrate your SQL Server environments to Azure. In this session, the Microsoft engineering team will show you everything you need to know, including the tools and knowledge you need to make your migrations seamless, cost-efficient, and optimized for speed.

Other training sessions cover topics such as Azure SQL Database, Synapse Analytics, and Power BI.

All speaker proceeds from these sessions will be given back to a local charity.

The SQLBits event theme this year is Video Games—and in the “Level Up With Azure Data” keynote, Buck Woody has asked me to come talk about SQL Server 2022 and Azure Data. He assures me I will have help with some surprise guests so it should be interesting. It is always a fun keynote when Buck and I are on stage, and this year you really do not want to miss it!

You also have the opportunity to attend the Microsoft general sessions to learn about the entire Azure data platform.

Take a look at some of the learning available SQLBits 2022

See all the opportunities to engage with Microsoft engineering by heading over to our blog on Microsoft Tech Community, Ready for SQLBits 2022. And don’t forget to stop by our booth, where you can get your questions answered by members of the Microsoft team.

SQLBits is a marathon of top-quality training from global specialists, with two days of full-day training sessions and three days of general sessions. As always with SQLBits, Saturday, March 12 is free to attend. Meet with community leaders sharing their real-world experience and Microsoft product teams providing deep insights into innovations that meet your needs.

Register today for SQLBits 2022

Join Microsoft at this hybrid event for the latest on the data platform and a chance to see whether Buck Woody or I have the best arcade game skills!

Register to attend, and we’ll see you there, in-person, or virtually!

The post Meet us at SQLBits 2022 and level up as a data professional appeared first on Microsoft SQL Server Blog.

Deliver faster performance than ever before with SQL Server and Azure

Hear directly from Microsoft’s Rohan Kumar and senior Microsoft engineering leaders during the day one kick-off keynote as they take you on a journey to a new universe shaped by our past—and built to take us into a limitless future. The cloud has created a whole new universe and advancements in Microsoft data products and services are your bridge.

You’ll see how you can use your existing SQL Server and Azure skills, and learn about new tools and platforms available from Microsoft to deliver faster performance than ever before. You’ll see how to shape your data so you can harness its power to find a new galaxy of insights, answers, and predictions. And you will hear about new innovations that continue Microsoft’s rich heritage of data integrity and governance.

Additionally, in the special on-demand keynote, Microsoft Azure Data CTO Raghu Ramakrishnan and team will share a technical keynote and demos showing Azure Purview and SQL.

Register for the PASS Data Community Summit

Don’t miss this opportunity to see how Microsoft is uniquely positioned to provide you with an end-to-end data platform seamlessly integrating limitless database scale and performance, unmatched analytics and intelligence, and unified data governance.

After the keynotes, ground your learning with in-depth training in one of more than two dozen sessions Microsoft will be delivering. Hear the latest from the Engineering teams who develop the tools you use every day. After your sessions, don’t forget to visit the virtual exhibit hall where you can connect with our team across SQL Server 2022, Azure SQL, Azure Synapse Analytics, Microsoft Power BI, Azure Arc, and more.

Register for PASS Data Community Summit today.

The post PASS Data Community Summit keynote: a bridge to a new universe appeared first on Microsoft SQL Server Blog.

The rise of data represents a tremendous opportunity and also poses challenges. Companies are seeing their relational and nonrelational data proliferate exponentially on-premises, in the cloud, at the edge, and in hybrid environments. The most transformative companies drive predictive insights on current data, whereas others may struggle to drive even reactive insights to their historical data. Information may be siloed across geographies and divisions.

To empower customers amid this environment, Microsoft offers an end-to-end data platform of products and services that come together to meet these challenges. Operational databases cover all possible deployment locations, including SQL Server and Azure Arc-enabled data services, Azure SQL fully-managed cloud databases, and Azure SQL Edge for IoT devices. To enable real-time insights, Azure Synapse Analytics brings together data integration, enterprise data warehousing, and big data analytics, and customers can visualize their data with Power BI. Customers can discover, catalog, and govern their data wherever it resides with Azure Purview.

SQL Server 2022 integrates with Azure Synapse Link and Azure Purview to enable customers to drive deeper insights, predictions, and governance from their data at scale. Cloud integration is enhanced with disaster recovery (DR) to Azure SQL Managed Instance, along with no-ETL (extract, transform, and load) connections to cloud analytics, which allow database administrators to manage their data estates with greater flexibility and minimal impact to the end-user. Performance and scalability are automatically enhanced via built-in query intelligence. There is choice and flexibility across languages and platforms, including Linux, Windows, and Kubernetes.

Azure-enabled

Bi-directional HA/DR to Azure SQL

To ensure uptime, SQL Server 2022 is fully integrated with the new link feature in Azure SQL Managed Instance. With the new link feature for Azure SQL Managed Instance, you now get all the benefits of running a PaaS environment applied to disaster recovery—allowing you to spend less time on setup and management even when compared to an IaaS environment. This works by using a built-in Distributed Availability Group (DAG) to replicate data to a previously deployed Azure SQL Managed Instance as a DR replica site. The instance is ready and waiting for whenever you need it—no lengthy configuration or maintenance is required. You can also use this link feature in read scale-out scenarios to offload heavy requests that might otherwise affect database performance. And we are working on building out more capabilities to support bi-directional data movement.

Azure Synapse Link

Previously, moving data from on-premises databases, like SQL Server, to Synapse required you to use ETL. As we all know, setting up and running an ETL pipeline takes a lot of work, and insights lag behind what is happening at any moment. Azure Synapse Link for SQL Server 2022 provides automatic change feeds that capture the changes within SQL Server and feed those into Azure Synapse Analytics. It provides near real-time analysis and hybrid transactional and analytical processing with minimal impact on operational systems. Once the data hits Synapse, you can combine it with many different data sources regardless of their size, scale, or format and run powerful analytics over all of it using your choice of Azure Machine Learning, Spark, or Power BI. Since the automated change feeds only push what is new or different, data transfer happens much faster and now allows for near real-time insights, with minimal impact on the performance of the source database in SQL Server 2022.

Azure Purview integration

We recently announced the general availability of Azure Purview as a unified data governance and management service. We are excited to highlight that SQL Server is also integrated with Azure Purview for greater data discovery, allowing you to break down data silos. Through this integration you will be able to:

• Automatically scan your on-premises SQL Server for free to capture metadata.
• Classify data using built-in and custom classifiers and Microsoft Information Protection sensitivity labels.
• Set up and control specific access rights to SQL Server.

Enhancements to performance, security, and availability

Performance

SQL Server offers differentiated performance, with number one OLTP performance¹ and number one Non-Clustered DW performance on 1TB², 3TB³, 10TB⁴, and 30TB⁵ according to the independent Transaction Processing Performance Council. Built-in query intelligence in SQL Server 2022 innovation includes:

• For Query Store, we are adding support for read replicas and enabling query hints to improve performance and quickly mitigate issues without having to change the source T-SQL.
• For Intelligent Query Processing, we’re expanding more scenarios based on common customer problems. For example, the “parameter sensitive plan” problem refers to a scenario where a single cached plan for a parameterized query is not optimal for all possible incoming parameter values. With SQL Server 2022’s Parameter Sensitive Plan optimization feature, we automatically enable the generation of multiple active cached plans for a single parameterized statement. These cached execution plans will accommodate different data sizes based on the provided runtime parameter values.

Security

Over the past ten years, SQL Server has had fewer vulnerabilities than the competition.⁶ Building on this, the new ledger feature creates an immutable track record of data modifications over time. This protects data from tampering by malicious actors and is beneficial for scenarios such as internal and external audits.

Availability

With the move to a more global distribution of workers and customers, many organizations are moving to a multi-write environment that allows changes to be made to the local database and pushed out to other replicas in a two-way flow of updates. However, if multiple people change the same row in the database and the different write replicas have different information in them, previously this peer-to-peer replica conflict would stall the whole operation until it was addressed. With SQL Server 2022, we are automating the last-writer wins rule. Now, when a conflict is detected, the most recent modification time will be chosen to be persisted on all replicas. This helps keep your multi-write scenarios running smoothly.

Learn more and apply for the preview today

At this point in time, we are onboarding a limited number of customers and partners to our SQL Server 2022 preview, in advance of public preview and general availability in the coming year.

Learn more about the SQL Server 2022 release on our webpage and by viewing deep-dive sessions at Microsoft Ignite, with Microsoft Mechanics, and at the upcoming free virtual PASS Data Community Summit. Read more about Azure at Ignite on the Azure blog.

Register today to apply for the SQL Server 2022 preview and stay informed about SQL Server 2022 updates.

All TPC Claims as of October 6, 2021

¹ http://www.tpc.org/4087

² http://www.tpc.org/3374

³ http://www.tpc.org/3380

⁴ http://www.tpc.org/3362

⁵ http://www.tpc.org/3364

⁶ National Institute of Standards and Technology Comprehensive Vulnerability Database

The post Announcing SQL Server 2022 preview: Azure-enabled with continued performance and security innovation appeared first on Microsoft SQL Server Blog.

Azure offers unique benefits that no other cloud provider can match including dual-use rights, Azure Hybrid Benefit, and unlimited virtualization, which lets you license the physical cores you have on-prem and those licenses will cover all the vCPUs on the host until the host runs out of resources. All of these benefits help you migrate to the cloud as cost-effectively as possible. We’ll go into more detail about each Azure-only benefit in this blog.

This blog will take you on a deep dive into moving SQL Server licenses to Azure Dedicated Host, its infrastructure benefits, and why it is the easiest way to bring your existing on-prem licenses to the cloud.

What is Azure Dedicated Host?

Azure Dedicated Host is an Azure service that provides you with a physical server, which you can host one or more virtual machines (VMs) on. Through physical, host-level isolation, you are the only tenant on the host; the server is dedicated to your organization and your workloads. With Azure Dedicated Host, you gain direct visibility to and control over all the Azure resources on the host. Essentially, Azure Dedicated Host allows you to create your own private cloud in Azure.

What are the benefits of Azure Dedicated Host?

Infrastructure benefits

With hardware isolation at the physical server level, Azure Dedicated Host provides servers solely for your subscription. You are the only one using the capacity and resources on the host. Physical isolation may be required by your company or industry, or integral to ensuring your workloads are able to perform as quickly as they should.

In addition to the physical isolation of the host and awareness of all Azure resources on the host, Azure Dedicated Host allows you to choose the combination of the number of processors, VM series, VM sizes, and the type of processor you use. Azure has a wide offering of various combinations of processor type and VM series. A full list of the available Dedicated Host SKUs, the combination of a VM series and processor type in a given region can be found on the Dedicated Host pricing page.

Azure Dedicated Host also gives you direct control over the maintenance on the host. Instead of adhering to Azure’s maintenance schedule, Maintenance Control allows you to delay platform updates and apply them during a 35-day window.

Licensing benefits

In addition to the infrastructure benefits, Azure Dedicated Host provides substantial financial benefits for those who already have on-premises SQL Server enterprise edition licenses, whether the license was purchased with or without Software Assurance. Software Assurance allows you to maximize your discounts and can unlock more benefits like Azure Hybrid Benefit, unlimited virtualization, and dual-use rights, which are not available for the license only. Microsoft highly recommends purchasing Software Assurance for your migration to Azure.

*Azure Hybrid Benefit allows you to bring your on-premises SQL Server licenses with Software Assurance to Azure at no additional cost.

Software application licenses can be applied at one of two places: the host or the VM. By applying the licenses at the host level, rather than the VM level, your license will cover all the physical cores rather than the virtual cores. With more virtual cores than physical cores on a host, this allows you to stretch your existing licenses further. For example, instead of licensing all 80 virtual cores on the Esv4-Type1 Dedicated Host, you can instead save 28 core licenses by licensing the 52 physical cores on the host. In doing so, you can license up to 80 virtual cores on the host while only applying the licenses on 52 virtual cores. This is only available on Azure.

The practice of licensing the physical cores to minimize your overall licensing cost is called unlimited virtualization. With SQL Enterprise Edition, Software Assurance, and unlimited virtualization, customers can apply the license to the physical cores, at the host level, and create as many VMs as the host allows. All VMs created on that host are then covered by unlimited virtualization and are covered by the host-level licenses. Unlimited virtualization is unique to Azure.

How you go about achieving unlimited virtualization depends on the license type. For SQL Server 2008, you can achieve unlimited virtualization at no additional cost by applying the licenses at the host level. For those with SQL Server 2008 R2 and later, by purchasing Software Assurance, you can unlock unlimited virtualization on your Dedicated Host.

Azure allows for dual-use rights while migrating your on-prem workloads to the cloud through Azure Hybrid Benefit. You can migrate to the cloud and simultaneously use your existing on-prem SQL Server licenses in Azure for up to 180 days. This allows you to avoid buying more licenses to cover the migration period and furthers the use of your existing licenses. You must have Software Assurance in order to use dual-use rights while migrating.

To better understand how much you can save through Azure Dedicated Host, Software Assurance, and unlimited virtualization, please check out the Azure pricing calculator.

Extended Security Update benefits

Customers who migrate their SQL Server 2012 licenses to Azure will receive free Extended Security Updates, only in Azure. Extended Security Updates are critical security patches for legacy services that need to be run past the end of support date. After the July 12, 2022 end of SQL Server 2008 and 2008 R2 Extended Security Updates on-premises, you can migrate to Azure and take advantage of one year of additional SQL Server 2008 extended security updates, only in Azure, for free. Extended security updates for both SQL Server 2008 and SQL Server 2012 are free only in Azure, making Azure the prime choice for SQL workloads running on either version.

Moving to the cloud doesn’t have to be challenging. There are proven ways to get to Azure that drive business impact. Through Azure Dedicated Host, you can bring your existing licenses to Azure. By purchasing Software Assurance, you maximize your licensing discounts and unlock more benefits, such as Azure Hybrid Benefit, unlimited virtualization, and dual-use rights.

Learn more about our product and pricing offers for SQL Server migration:

• Azure Dedicated Host
• Azure Hybrid Benefit
• Extended Security Updates

Get started with your migration to Azure using Azure Migrate.

The post Move SQL Server licenses without Software Assurance to Azure appeared first on Microsoft SQL Server Blog.

Azure SQL Edge is a small-footprint container that enables localized IoT solutions for edge servers, gateways, and devices by offering data streaming, storage, and analytics in connected or disconnected environments.

Built on the same code base as Microsoft SQL Server and Azure SQL, Azure SQL Edge provides the same industry-leading security, the same familiar developer experience, and the same tooling that many teams already know and trust—now extended to IoT deployments for real-time intelligence.

And best of all, Azure SQL Edge has simplified pricing that is right-sized for IoT deployments, available as low as $60 per year per device for a 3-year commitment or at $10 per month, per device subscription.

Data and compute are pushing closer to the edge

The explosion of the internet of things (IoT) has changed the way we collect and analyze data. As compute becomes more powerful in smaller form factors, edge devices such as sensors and cameras are being adopted across industries to digitally transform their operations.

Gartner estimates that by 2025, 75 percent of enterprise-generated data will be created and processed at the edge, up from less than 20 percent today.¹ This technological shift towards edge gateways and devices puts data collection and compute together at the same location, reducing latency, and enabling real-time insight and impact whether on the manufacturing floor or on a remote wind farm.

A data engine built, optimized, and priced for IoT deployments

 Azure SQL Edge provides affordable solutions for even the most demanding edge architectures:

• Time series, data streaming, and AI built-in. Stream, store, and analyze data while it is in motion or at rest. Real-time analytics and simultaneous event-processing whether online or off.
• Your choice of platform. Run SQL on ARM 64 and x64 architectures. A small footprint under 500mb means you can deploy on IoT devices as small as a Raspberry Pi.
• Develop once, deploy anywhere from edge to cloud. Consistent app development, security, and management from Azure SQL to SQL Server to the IoT edge.
• Native integration with Azure products and services. Simplify and strengthen your cloud-to-edge architecture with native integration to Azure products and services such as Azure IoT Edge and Azure Stack Edge.
• Simplified pricing for IoT. No upfront cost and subscriptions offers as low as $60 per year per device for a 3-year commitment.² It’s as simple as that.

“This is a game changer.”

For more than 57 years, Fugro has delivered projects in some of the most remote and challenging environments around the world. It uses the latest technology to provide comprehensive information about the world’s environment and structures to contribute to a safe and livable world. Its ocean fleet largely relied on on-premises servers to collect and analyze data, slowing down their time to insights.

“Traditionally, it took two weeks to prep each client’s monthly data, to verify the report, and for the client to receive it. Now we deliver the first draft of that report in eight minutes. This is a game changer, it massively simplifies everything we do.” – Richard Corless, Lead Cloud Architect, Fugro

Fugro now uses Microsoft Azure IoT Edge and Azure SQL Edge to connect its vessels and onshore assets to boost efficiency and speed innovation. 

“The decision-maker, the worker, the line manager—everyone is able to make decisions earlier, quicker, and more accurately.”

A leading technology enterprise in the fields of optics and optoelectronics, ZEISS embarked on a digitization process with the goal of creating a connected smart factory that can execute intelligent, flexible manufacturing for optimal efficiency, precision, and accuracy.

“Collecting this data helps everyone at every level of the factory. The decision-maker, the worker, the line manager—everyone is able to make decisions earlier, quicker, and more accurately.”  – Jochen Scheuerer, Head of Connected Smart Factory, ZEISS

Achieving these smart production goals requires measurement and inspection technology, that can capture and analyze quality data at different sites with greater flexibility and speed. Azure SQL Edge was added to production lines for eyeglass lenses, mechanical parts, and spectroscopic solutions.

Azure SQL Edge is now available

Get started today, or view whitepapers, case studies, and more.

¹ “Edge Computing Solutions for Industrial IoT.” July 2018, Gartner, “Top Strategic IoT Trends and Technologies Through 2023.” September 2018, Gartner

² Restrictions apply. See Azure.com for more information.

The post Real-time data intelligence and security at the edge with Azure SQL Edge appeared first on Microsoft SQL Server Blog.

Our customers have asked for this and we’ve been listening – advanced data security is now available for SQL Server on Azure Virtual Machines! Using just a few simple steps, you can now protect your SQL Server installations on Azure VMs with Microsoft’s advanced data security capabilities.

Advanced data security for SQL Server on Azure VM currently includes functionality for surfacing and mitigating potential database vulnerabilities and detecting anomalous activities that could indicate a threat to your server. To get started today, read the Advanced data security for SQL Server on VM setup instructions.

Why you should enable advanced data security for SQL Server on Azure VM

While in public preview, advanced data security for SQL Server on Azure VM is free and includes: 

1. Vulnerability assessment – A database scanning service that can discover, track, and help you remediate potential database vulnerabilities. Detected vulnerabilities across all connected SQL Servers will appear in one unified dashboard!
2. Advanced threat protection – A detection service that continuously monitors your database for suspicious activities and provides action-oriented security alerts on anomalous database access patterns. All alerts will appear in your centralized go-to location for security management in the Azure portal – the Azure Security Center threats dashboard.

For full details regarding threat detectors in Public Preview, read the Advanced data security for SQL Server on VM documentation.

These advanced security features have evolved and benefited from continuous improvement over the past couple of years, and have already been running on more than 1 million databases in the corresponding Azure SQL Database service – Advanced data security for Azure SQL databases.

How does it work?

Using the Azure Log Analytics agent, you connect your SQL Server’s hosting machine to a Log Analytics workspace. The agent collects audit logs for login events (omitting any sensitive data like queries or user’s data) and uploads them from the machine to the workspace, where our security analytics capabilities go into action. In addition, the agent also collects results from the vulnerability assessment scans and sends those to the workspace as well.

Logs and assessment results will appear in the workspace and are entirely under your control and can be queried for more insights. You can also identify the logs that triggered Advanced Threat Protection alerts for further investigation. Finally, the workspace contains a built-in dashboard for intuitive analysis of the vulnerability assessment results.

For a complete set of instructions, review the documentation for Advanced data security for SQL Server on VM.

We want to hear from you!

We greatly appreciate your feedback and want to hear from you. Please contact us directly through SQL Security Feedback sqlsecurityfd@microsoft.com.

The post Advanced data security for SQL Server is coming to Azure Virtual Machines appeared first on Microsoft SQL Server Blog.

Always Encrypted debuted in SQL Server 2016 as a solution for protecting sensitive data used during the processing of Transact-SQL queries. With Always Encrypted, the data is encrypted and decrypted on the client-side, and is not exposed in plaintext in memory of the SQL Server process. As a result, even DBAs and administrators of machines hosting the database can’t see plaintext data. This makes Always Encrypted a great way to keep your data secure, but it restricts computations that SQL Server can perform on the data.

The only operation SQL Server 2016 and 2017 support on encrypted database columns is equality comparison, providing you use deterministic encryption. For anything else, your apps need to download the data to perform the computations outside of the database. Similarly, if you need to encrypt your data for the first time or re-encrypt it later (e.g. to rotate your keys), you need to use special tools that move the data and perform crypto operations on a different machine than your SQL Server computer. These restrictions are not an issue if equality comparison is all your applications need and if the tables containing your sensitive data are small. However, many types of sensitive information, e.g. a person’s name or phone number, often require richer operations, including pattern matching and sorting, and it’s not uncommon for sensitive data to be too large to move outside of the database for processing.

To address the above challenges, Always Encrypted in SQL Server 2019 is enhanced with secure enclaves. A secure enclave is a protected region of memory that appears as a black box to the containing process and to other processes running on the machine, including the operating system. There is no way to view the data or code inside the enclave from the outside, which makes enclaves ideal for processing sensitive data. There are several enclave technologies that differ in how enclave isolation is accomplished. SQL Server 2019 preview uses a Windows Server technology called Virtualization Based Security (VBS), which relies on Hypervisor to protect and isolate enclaves.

A SQL Server 2019 instance can be configured to contain a secure enclave that is used for computations on data protected with Always Encrypted, which is illustrated in the diagram below. This secure enclave logically extends client applications’ trust boundary to the server side. While it is contained by the SQL Server environment, the secure enclave is not accessible to SQL Server, the operating system, or the database or system administrators. This means the enclave can safely perform cryptographic operations on sensitive data or decrypt the data to perform rich computations on the plaintext, without exposing the data to potential adversaries in the database environment.

If you are wondering how it all works under the covers, please see the online documentation and other resources listed at the end of this post. In the remainder of this article, we will focus on the benefits of Always Encrypted with secure enclaves and discuss the new scenarios the enhanced feature enables.

Boost performance of encrypting columns in large tables or complex database schemas

Always Encrypted with secure enclaves allows you to encrypt your data and re-encrypt it (e.g. to rotate column encryption keys) in-place, inside a server-side secure enclave. In contrast, without secure enclaves, crypto operations on columns protected with Always Encrypted requires loading the data to a trusted machine, where the operations are performed and then the data is uploaded back to the database. This process is prone to network errors and can take a long time if your sensitive data resides in large tables. In-place encryption with secure enclaves improves the resiliency and dramatically reduces the duration of cryptographic operations. As a result, it makes Always Encrypted a practical solution for protecting sensitive data in large tables.

Always Encrypted with secure enclaves also gives you the option to perform cryptographic operations using ALTER TABLE ALTER COLUMN Transact-SQL statements, which is particularly useful if you have a very large database schema that contains many database objects. Until now, cryptographic operations have required using tools such as the Always Encrypted wizard in SQL Server Management Studio (SSMS) or the Set-SqlColumnEncryption PowerShell cmdlet. These tools automatically handle all dependencies between the columns and other database objects, such as foreign key constraints, indexes, stored procedures, views, etc. Both SSMS and PowerShell leverage DAC Framework to detect, remove, and (after completing cryptographic operations) re-create all dependencies. This convenience, however, can be costly: if your database contains thousands of objects, the tools need to issue many queries to retrieve the metadata for your database, which can take a long time and consume a lot of resources, both on the server side and on the machine running the tool. When using Transact-SQL for cryptographic operations, you need to manually create scripts for handling dependencies. While this requires manual work, it gives you more control over handling dependencies and is typically a better or in some cases the only practical method for running such operations on databases with large schemas.

Please note in the current preview of SQL Server 2019 (CTP 2.1), in-place encryption is only supported when using ALTER TABLE ALTER COLUMN. SSMS and PowerShell will be updated later to take advantage of in-place encryption if your database configuration supports secure enclaves.

Here is an example of an ALTER TABLE ALTER COLUMN statement triggering a crypto operation. The example assumes the LastName column is not encrypted initially. Once the statement is completed, the column will become encrypted using randomized encryption and the specified key.

ALTER TABLE [dbo].[Patients]
ALTER COLUMN [LastName] [nvarchar](50) 
ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK1], ENCRYPTION_TYPE = Randomized, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL
WITH (ONLINE = ON)
GO

Note the use of the ONLINE=ON switch, which causes the operation to perform in the online mode to minimize interruptions for applications that may be querying the table. Also note that the statement must be issued over a connection with Always Encrypted enabled and the user must have access to the column master key, protecting the column encryption key (CEK1) that is being used. This requirement is the same when running the wizard or the above-mentioned PowerShell cmdlet. Full details on this can be found in the Getting started with Always Encrypted with secure enclaves using SSMS documentation.

Protect PII and other sensitive data while running rich queries

Besides making encryption and key rotation easier, the main goal of secure enclaves is to enable SQL Server to support rich computations on encrypted database columns, while preserving the security benefits of Always Encrypted. Equipped with a secure enclave, a SQL Server instance can delegate computations to the enclave, which decrypts the data and performs the requested operations on plaintext. SQL Server 2019 preview supports pattern matching using the LIKE operator and comparison operators (<, >, =, etc.) on columns using randomized encryption. The example below shows a query searching patient records based on the last name prefix (assuming the LastName column is encrypted):

DECLARE @SearchPattern nvarchar(11) = N'Ab%'
SELECT * FROM [dbo].[Patients] WHERE [LastName] LIKE @SearchPattern
GO

Enabling rich queries unlocks Always Encrypted to a much broader set of scenarios, including applications that process PII such as people’s names or phone numbers, which typically require more complex computations than just equality comparison supported in SQL Server 2016/2017. This makes both preventing insider attacks and meeting regulatory compliance requirements, such as the EU General Data Protection Regulation (GDPR), much easier. With secure enclave, you do not need to re-engineer your apps to load the data and implement your query logic inside the applications. Instead, you can continue running your rich queries inside the database.

Note: Rich computations are pending several performance optimizations and include limited functionality (e.g., you cannot create indexes to support rich computations yet). Therefore, they remain disabled by default in SQL Server 2019 CTP 2.1. You can see the online documents on how to enable rich queries here.

Conclusion and next steps

Always Encrypted with secure enclaves in SQL Server 2019 preview helps you protect your sensitive data from malicious insiders and cloud operators or malware while supporting richer processing of your data inside the database. Here are the types of applications that can particularly benefit from using the enhanced Always Encrypted technology:

• Applications that store sensitive data in large tables. With in-place encryption, you can encrypt columns or change the encryption scheme of columns in large tables much faster, as you don’t need to move the data to a machine running an encryption tool, such as SSMS or PowerShell.
• Applications using databases with large schemas, containing thousands of database objects. Always Encrypted with secure enclaves gives you the flexibility of triggering cryptographic operations using Transact-SQL and create custom scripts that typically handle database dependencies more efficiently, unlike the client-side tools (SSMS, PowerShell). This makes encrypting columns in databases with large database schemas feasible or much faster.
• Applications that process PII or other types of sensitive information that require pattern matching or range queries to be performed inside the database. You can protect the confidentiality of your sensitive data without re-implementing your business logic inside your apps, which is often expensive or even impossible, depending on the size of your data.

Here are a few links to learn more information about Always Encrypted using enclaves:

• Online documentation
• The tutorial on how to get started evaluating the feature
• SQL Server Security blog posts
• The Ignite session recording
• GitHub sample

We would love to learn about your scenarios and requirements for protecting sensitive data, partner with you on your Proof of Concept projects, and help you become early adopters of Always Encrypted with secure enclaves. We also welcome your feedback while we continue our journey to the RTM of SQL Server 2019 and beyond. Please contact us directly at AEwithEnclaves@microsoft.com or by signing up for the SQL Server Early Adoption Program.

To get started with SQL Server 2019 preview, you can find download instructions on the SQL Server 2019 web page.

We look forward to working with and learning from you!

The post Confidential computing using Always Encrypted with secure enclaves in SQL Server 2019 preview appeared first on Microsoft SQL Server Blog.

SQL Server has provided enterprises the capability to manage all facets of their relational data. Over the years, we have increasingly seen a convergence for the need of combining heterogenous sets of relational and non-relational data to meet the needs of business scenarios. This requires setting up a unified data platform that transcends the boundaries of all types of data. Incidentally, we are also celebrating 25 years since SQL Server first shipped on Windows NT in 1993. The heart of SQL Server is mission critical performance, security, and availability and the use of our database platform in mission-critical environments is a testament to that fact. The SQL Server 2019 preview relational engine will deliver new and enhanced features in the areas of mission-critical performance, security and compliance, and database availability, as well as additional features for developers, SQL Server on Linux and containers, and general engine enhancements.

Earlier at Ignite, Microsoft announced the first public Community Technology Preview (CTP 2.0) of SQL Server 2019. For the first time, SQL Server 2019 comes with big data capabilities built-in, with Apache Spark and Hadoop Distributed File System (HDFS) in the box—extending SQL Server beyond a traditional relational database. This blog post covers the database engine features that are available in first public Community Technology Preview (CTP 2.0) of SQL Server 2019.

An Intelligent database providing Industry-leading performance

The Intelligent Query Processing suite builds on hands-free performance tuning features of Adaptive Query Processing in SQL Server 2017 like row mode memory grant feedback, batch mode on rowstore, table variable deferred compilation. We have identified common classes of query performance problems which could benefit from automatic corrective approaches during runtime based on changes in cardinality or through leveraging a feedback loop based on statistics from past executions. These are features that we have already started leveraging in Azure SQL Database and remain a top investment area for SQL Server 2019.

These are new changes to our query processor which are available with database compatibility level = 150 keeping in line with our database compatibility based upgrade promise. Database compatibility level provides an easy certification path for an existing application which helps with future upgrades to new releases where the database compatibility mode remains the same. This allows our customers to reduce the effort require to leverage capabilities in latest releases for availability, performance and security without having to worry about re-certifying the entire application on a newer release.

Persistent memory support is improved in this release with a new, optimized I/O path available for interacting with persistent memory storage. Any SQL Server file that is placed on a persistent memory device allows SQL Server to directly accesses the device, bypassing the storage stack of the operating system. This mode improves performance by significantly improving low latency input/output without any change to your application or database design. The ability for an existing database schema to leverage significant throughput gains allows existing applications with I/O bound bottlenecks.

The lightweight query profiling infrastructure is now enabled by default to provide per query operator statistics anytime and anywhere you need it. This provides the ability to look back in time and investigate query performance issues. We also decided to extend this capability to queries that are running on the server. This allows SQL Server administrators the ability to leverage Management Studio’s Live Query Statistics or the new DMF, sys.dm_exec_query_statistics_xml, to perform live troubleshooting of a current performance problem without needing to turn on any diagnostic data collection.

Enhanced security enabling Confidential Computing

Earlier this year, we announced Confidential Computing with Always Encrypted using Enclaves for Azure SQL Database. Now we have Always Encrypted with secure enclaves for SQL Server 2019 preview which extends the client-side encryption technology introduced in SQL Server 2016. Secure enclaves protect sensitive data in a hardware or software-created enclave inside the database, securing it from malware and privileged users during advanced operations on encrypted data.

SQL Data Discovery and Classification is now built into the SQL Server engine with new metadata and auditing support which allows you to create solutions for key compliance requirements. We now have the ability for SQL Server catalog metadata to persist information about user-defined data classification labels.

Certificate management is now integrated into the SQL Server Configuration Manager, simplifying common tasks like deploying certificates across machines participating in a failover cluster instance or availability group. This removes the overhead of managing certificates separately on each node of the SQL Server failover cluster or availability group instance.

Mission-critical availability to keep your SQL Server running

Always On Availability Groups have been enhanced to include automatic redirection of connections based on read/write intent. This capability allows applications to be redirected to the primary replica without requiring a listener for handling scenarios where creation of a listener is not possible. This gives an opportunity for legacy applications which depend on a hard-coded server/host name but can still leverage Availability Groups on upgrade by redirection to the original replica after a failover.

High availability configurations for SQL Server running in containers can be enabled with Always On Availability Groups using Kubernetes as an orchestration layer. A Kubernetes operator deploys a Stateful Set including a container with mssql-server container and a health monitor. This introduces a tighter integration between SQL Server availability groups and Kubernetes. The operator will be available in the Microsoft Container Registry for SQL Server 2019 preview.

SQL Server Always On availability groups will support up to 5 synchronous replicas (1 primary and 4 synchronous secondary) with automatic failover support. This increases your ability to sustain simultaneous failures within or across data centers using SQL Server’s high availability and disaster recovery features.

We are enhancing the capability of resumable online index DDL by allowing users to restart from the last point the rowstore index create was paused or failed. This allows you the ability to continue an online index build after an outage, database failover or even stopping the operation to free up resources on the SQL Server instance.

Clustered Columnstore indexes can now be created and rebuilt online to help improve uptime for hybrid transaction analytical processing (HTAP) environments.

SQL Server Machine Learning Services will now support clustering which allows you to have a highly available intelligent database for both OLTP and Machine Learning scenarios.

Enhancing the developer experience

We are introducing UTF-8 support, a widely used character encoding format, which can provide significant storage savings up to 50 percent for your character data. This allows you to compress your existing character data without the need to write additional routines and leverage external software to compress existing data. The ability to convert existing data to UTF-8 collations will allow existing databases to leverage this new capability for storage savings.

Enhancements to SQL Graph include match support with T-SQL MERGE and edge constraints.

We are extending the ability for SQL Server to leverage common programming languages by adding Java. We already have the ability for customers to leverage CLR, R and Python in earlier releases of SQL Server. The new Java language extension will allow you to call a pre-compiled Java program and securely execute Java code on the same server with SQL Server. This reduces the need to move data and improves application performance by bringing your workloads closer to your data. This extension is installed when you add the feature ‘Machine Learning Services (in-database)’ to your SQL Server instance. And since SQL Server on Linux uses the same database engine code, you can execute the same compiled Java classes on both SQL Server on Linux and Windows.

Machine Learning Services has several enhancements for partitioned models, and support for SQL Server on Linux. We now have the ability to process external scripts per partition which supports training many small models (one model per partition of data) instead of one large model and there by providing the ability to leverage SQL Server machine learning services across your partitions. This allows you to create a partitioned training strategy across archived data sets without having to incur the performance cost of training over all your data in a single monolithic operation.

Azure Data Studio, previously SQL Operations Studio, is now generally available. Azure Data Studio is a free tool that runs on Windows, macOS, and Linux, for managing SQL Server, Azure SQL Database, and Azure SQL Data Warehouse; wherever they’re running. SQL Server Management Studio 18.0 Preview will also be available for customers to continue managing SQL Servers with the support for SQL Server 2019 Public Preview.

Platform of choice

The preview container images of SQL Server will be available on the Microsoft Container Registry along with the new certified RHEL-based SQL Server container image available on the Red Hat Container Catalog. This allows users to leverage well known commands to setup a RHEL image with SQL Server running on it in a matter of seconds improving the ability to deploy and manage their environment where SQL Server running on Red Hat is a requirement.

We are introducing new connectors for PolyBase to external data for SQL Server, Oracle, Teradata, and MongoDB which allows you to create a unified data platform using the SQL Server database engine. We have redesigned PolyBase to allow you to connect to ODBC sources, other relational databases, NoSQL and Big Data environments which enables scenarios like building new application capabilities using SQL Server as a data hub without duplicating data and system of records.

Additional capabilities for SQL Server on Linux include distributed transactions, replication, Machine Learning Services, and OpenLDAP support. These features are driven by customer demand from customer running or evaluating SQL Server on Linux for production use.

We continue to listen to customer feedback and provide features, enhancements and innovation which help our customers run mission and business critical environments on SQL Server. Our new capabilities on SQL Server on Linux along with engine enhancements in SQL Server 2019 Preview features like columnstore statistics support for DBCC CLONEDATABASE, compression estimates for columnstore indexes, and new T-SQL built-in functions to discover details for page resource waits are examples of such customer driven engineering

We also wanted to point out that SQL Server 2008 and SQL Server 2008 R2 will be approaching end of support during July 2019. Microsoft is making options available for you to successfully modernize your data platform while staying secure on your existing environment. Please read about SQL Server 2008 and 2008 R2 End of Extended Support for more information.

Get started now

• Preview SQL Server 2019 for Windows, Linux, or Docker.
• Sign up for the Early Adoption Program to get advice and support for your project from SQL Server engineering, or to try SQL Server 2019 big data clusters.
• Download Azure Data Studio to get started with new SQL Server big data features like data virtualizations and notebooks.

The post SQL Server 2019: Celebrating 25 years of SQL Server Database Engine and the path forward appeared first on Microsoft SQL Server Blog.