To generate a production-ready schema-only copy of a database, it’s highly recommended to utilize tools such as Microsoft SQL Server Data Tools (SSDT); the Generate and Publish scripts Wizard; or data-tier application extraction, which includes either the Extract Data-tier Application (DAC) Wizard or a PowerShell script. These tools provide a reliable way to create a copy of a database for use in production environments.

Learn more about

• DBCC CLONEDATABASE (Transact-SQL) – SQL Server
• Clone an Existing Database – SQL Server Data Tools (SSDT)
• Generate and Publish Scripts Wizard – SQL Server Management Studio (SSMS)
• Create a statistics-only database using a generated statistics script – SQL Server
• Extract a DAC from a Database – SQL Server

Frequently asked questions

What is SSDT?

SQL Server Data Tools (SSDT) is a modern development tool that integrates with Microsoft Visual Studio and provides design, debugging, and deployment capabilities for building SQL Server relational databases, databases in Azure SQL, Analysis Services (AS) data models, Integration Services (IS) packages, and Reporting Services (RS) reports. With SSDT, developers can perform necessary tasks without needing the admin-focused SQL Server Management tool on their developer computer. Essentially, Visual Studio removes unnecessary features like admin tools, and focuses on tools that are useful for developers, like database and schema comparison tools.

What is the Generate and Publish Scripts Wizard?

The Generate and Publish Scripts Wizard is a feature in SQL Server Management Studio (SSMS) that allows you to create scripts for transferring a database between instances of the SQL Server Database Engine or Azure SQL Database. You can generate scripts for a database on an instance of the Database Engine in your local network, or from SQL Database. The generated scripts can be run on another instance of the Database Engine or SQL Database. You can also use the wizard to publish the contents of a database directly to a Web service created by using the Database Publishing Services. You can create scripts for an entire database or limit it to specific objects.

What is a data-tier application (DAC)?

A data-tier application (DAC) is a logical database entity that defines all of the SQL Server objects—such as tables, views, and instance objects, including logins—associated with a user’s database. A DAC is a self-contained unit of the entire database model and is portable in an artifact known as a DAC package, or .dacpac. Tooling support for data-tier applications enables developers and database administrators to deploy dacpacs to new or existing databases. Deployments to an existing database update the database model from the existing state to match the contents of the dacpac. Developers build DACs from SQL database projects, a declarative development concept for building SQL objects that enables source control on the database schema.

A .bacpac is a related artifact that, by default, encapsulates the database schema and the data stored in the database. The primary use case for a BACPAC is to move a database from one server to another—or to migrate a database from a local server to the cloud—and archiving an existing database in an open format.

What is DBCC CLONEDATABASE command?

DBCC CLONEDATABASE creates a new database that contains the schema of all the objects and statistics from the specified source database. Cloned databases copy all schema and metadata of the source database without copying any data.

How do I use Schema Compare to compare different database definitions?

SSDT includes a Schema Compare utility that you can use to compare two database definitions. The source and target of the comparison can be any combination of connected database, SQL Server database project or snapshot, or .dacpac file. The results of the comparison appear as a set of actions that must be taken with the target to make it the same as the source. Once the comparison is complete, you can update the target directly (if the target is a project or a database) or generate an update script that has the same effect.

The differences between source and target appear in a grid for easy review. You can drill into and review each difference in the results grid or in script form. You can then selectively exclude specific differences.

You can save comparisons either as part of an SQL Server Database project or as a standalone file. You can also set options that control the scope of the comparison and aspects of the update. Then you can save the comparison so that you can easily repeat the same comparison later or use it as the starting point for a new comparison.

Why do I need to generate a schema-only clone of a database with statistics in SQL Server?

You will need to generate a schema-only clone of a database with statistics to investigate performance issues.

The query optimizer in SQL Server uses the following types of information to determine an optimal query plan:

• Database metadata
• Hardware environment
• Database session state

Typically, you must simulate all these same types of information to reproduce the behavior of the query optimizer on a test system.

Microsoft Customer Support Services might ask you to generate a schema script of the database along with statistics to investigate a query optimizer issue.

The post Update on the support of DBCC CLONEDATABASE for production use appeared first on Microsoft SQL Server Blog.

SQL Server Developer Edition

SQL Server comes in several editions and Developer Edition has for many years been the choice of many developers to build applications and test SQL Server with no license costs and the full functionality of SQL Server. As the documentation describes SQL Developer Edition, it “includes all the functionality of Enterprise edition but is licensed for use as a development and test system, not as a production server.” Microsoft has had some questions about exactly what the term means “as a development and test system.” Let’s turn to the SQL Server Licensing Guide for the details.

The licensing guide states that SQL Server Developer Edition is “…licensed for development, test, and demonstration purposes only. SQL Server Developer Edition may not be used in a production environment”. Notice the addition of the word “demonstration purposes.” This means, for example, that you can use SQL Server Developer Edition to do demos at your favorite conference as a presenter.

There are some other points in the licensing guide worth noting, including:

• You can run Developer Edition for development, testing, and demonstration purposes on an unlimited number of devices.
• You cannot use Developer Edition to build test data and move that same data into production. But you can restore a production set of data backup for testing purposes. The intention here is to prevent users from using Developer Edition as a production server but calling the data test data.
• Production environments include any system that is accessed by end-users for anything more than acceptance testing, environments that connects to production systems (such as Linked servers), disaster recovery or backups of production systems, and environments that are “rotated” into production at any point in time.

One of the questions Microsoft often gets is how to find previous versions of the SQL Server Developer Edition. Therefore, you can use the following shortcuts to help you find versions back to SQL Server 2016:

• SQL Server 2022 Developer Edition
• SQL Server 2019 Developer Edition
• SQL Server 2017 Developer Edition
• SQL Server 2016 Developer Edition

SQL Server containers

One of the most amazing stories about SQL Server for developers is containers. SQL Server containers are pre-installed versions of SQL Server on Linux that can be used for developers on macOS, Linux, or Windows for a consistent experience. Furthermore, if you just run a SQL Server container by default it is configured for SQL Server Developer Edition. Now your organization can use a distributed development approach using a consistent SQL Server deployment instead of relying on development servers. SQL Server containers provide a convenient method to test your application against multiple versions of SQL Server.

You can find out more about how to use SQL Server containers. This includes details about how to find all container images for SQL Server 2017, 2019, and 2022 from the Microsoft Artifact Registry.

Visual Studio Subscriptions

Visual Studio Subscriptions provides developers options to use licenses for SQL Server Standard and Enterprise editions to develop and test applications for non-production scenarios. One scenario that can help developers is the ability to develop and test applications against SQL Server Standard Edition.

One of the benefits of a Visual Studio Subscription is Azure credits. Azure credits allows developers to receive free credits towards use of Azure services such as SQL Server (any edition) in Azure Virtual Machine or Azure SQL Database for development and testing purposes. No credit card is required to use these credits and it provides an option to develop or test SQL in Azure. While the number of monthly credits may feel limited, developers may find this benefit of their Visual Studio subscription as a method to get started in the cloud.

Azure Dev/Test pricing

If Azure credits are not enough for your development or testing needs, Visual Studio subscribers have a benefit available to them called Azure Dev/Test pricing. Azure Dev/Test pricing, offered exclusively to Visual Studio subscribers, allows developers to save money to develop and test applications with Azure services.

Azure Dev/Test pricing offers three plans:

• Enterprise Dev/Test—offered exclusively for customers with a Microsoft Enterprise Agreement (EA).
• Azure Plan for DevTest—offered exclusively to customers with a Microsoft Customer Agreement.
• Pay-As-You-Go Dev/Test—an option for customers without an agreement with Microsoft.

Each plan has different requirements and offers different benefits per your needs. Azure credits are not designed to be used in combination with Azure Dev/Test pricing.

Azure Dev/Test pricing not only provides significant cost savings for dev/test scenarios for Azure Services such as Azure SQL Database or Azure SQL Managed Instance, but also allows customers to use software that is included in their Visual Studio subscription for free in an Azure Virtual Machine.

For example, if your Visual Studio subscription includes licensing for SQL Server, you can deploy SQL Server Standard or Enterprise Edition in an Azure Virtual Machine and not incur licensing charges from Azure. This could provide a method for you to test your application against SQL Server Standard Edition in a cost effective manner.

You can also optimize your cost savings with both Azure Dev/Test pricing and Azure Hybrid Benefit licensing. You can use Azure Dev/Test pricing for your dev/test workloads and Azure Hybrid Benefit licensing for production workloads. Azure Hybrid Benefit is a licensing offer that helps you migrate and save to Azure by using your existing SQL Server licenses.

It is important to note that Azure Dev/Test labs is not related to Azure Dev/Test pricing. Azure Dev/Test labs is a free Azure service used to help you optimize provision of development and test environments. You use whatever Azure subscription pricing model is available for Azure services within your Dev/Test lab deployments.

Try Azure SQL Database for free (preview)

Microsoft has recently announced a new free offer for Azure SQL Database now in preview. This offer is a major milestone for developers or any user of Azure SQL. This free offer provides a monthly set of cores and storage for free for a single Azure SQL Database per subscription for the lifetime of the Azure subscription.

The most significant aspect of this offer is that it is not limited for development and testing purposes. While the number of free core and storage usage may not make it viable for medium or large production workloads, it can be an excellent option for developers to have a “developer edition” of Azure SQL Database or for small workloads.

The free Azure SQL Database offer is based on the General Purpose service tier using Serverless compute so it carries all of the capabilities and limits that go with this deployment choice.

Provisioning an Azure SQL Database for the free offer is easy.

1. Go to the Azure portal provisioning page for Azure SQL Database.
2. On the Basics tab, look for the banner that says, “Want to try Azure SQL Database for free?“, select the Apply offer button. If you do not see this banner, you may already have a free database provisioned for your subscription.

Try Azure SQL Managed Instance for free (preview)

In some cases, developers need the full managed instance capabilities of SQL Server in the cloud so they choose Azure SQL Managed Instance. Developers can now save costs to develop and test applications with Azure SQL Managed Instance with a new free offer.

This offer, now in preview, includes the ability to try Azure SQL Managed Instance free of charge for the first 12 months to get:

• A General Purpose service tier instance with up to 100 databases
• 750 vCore hours of compute every month
• 64 GB of storage

This is a major leap forward for developers who are looking to test a migration strategy with Azure SQL Managed Instance.

It is easy to get started with this free offer. Use the Azure portal to create the new free Azure SQL Managed Instance. To create your free instance, follow these steps:

• Go to the provisioning page for Azure SQL Managed Instance in the Azure portal.
• On the Basics tab, look for the “Want to try Azure SQL Managed Instance for free?” banner and select the Apply offer button. Check the Estimated costs per month option to validate the free offer has been applied to your instance.

When you are ready to migrate, our Managed Instance Link feature provides a true online migration path to modernize your SQL Server investments.

Other options for developers to save costs

As you can see SQL developers have several options to develop and test applications with SQL for free or substantial savings both on-premises and in the cloud. Microsoft wants all developers using SQL to have the most cost-effective options to build applications that work with SQL, edge to cloud, and the latest innovations.

Here are a few other tips for you to save costs with Azure SQL:

• Shutdown any virtual machine when you are not using it to save costs. You can use Azure Dev/Test labs to help automate this process. Be sure to use the right shutdown methods to save costs.
• Use Azure SQL Database Serverless with the autopause feature to save costs when the database is idle.
• Take advantage of a new pricing tier for Azure SQL Database Hyperscale.
• Use the Start/Stop feature for Azure SQL Managed Instance to pause compute and only pay for storage costs.

Learn more about SQL Server developer experiences

To learn more about some of the exciting new innovations for developers for SQL check out these resources:

• Discover more about the SQL Developer Experience: Beyond RDBMS.
• Increase your knowledge about intelligent applications with Azure SQL Database.
• Learn more about SQL Server for Developers.

The post How SQL developers can maximize savings appeared first on Microsoft SQL Server Blog.

SQL Server 2019 supports the R, Python, and Java Language Extensions. These language extensions provide many benefits to developers. They provide data security, rapid speed for deployment, and ease of integration.

Previously, we announced the release of Java, R, and Python extensions. Today we are thrilled to share that we are open sourcing the .NET 5 C# Language Extension for SQL Server on GitHub.

This extension is yet another example of using the developed programming language extensibility architecture which provides integration with a new type of language extension. This latest architecture allows customers to have the freedom to use existing SQL Server tables which they can pass to a C# application as a DataFrame. Then, they can perform operations in C#, use the rich libraries, and obtain back a result set. One of the reasons to use C# is to reuse existing customer C# code, calculations, logic, or extensive libraries that provide functionality which you cannot get in T-SQL. We encourage developers to use the .NET 5 C# Language Extension and build on it.

Let us look at what use cases C# can enable inside SQL Server. Bringing C# workloads closer to the data opens a variety of possibilities:

• Data cleaning.
• Fast data querying.
• Any processing in C# can now occur through a DataFrame.
• Customers are not limited to the T-SQL language surface area.
• C# application development teams that leverage SQL Server as backend storage can now even embed C# code in stored procedures which enables pushing business logic down into the database for better performance.
• Furthermore, this will help avoid unnecessary data movement and latency when data must be retrieved from SQL Server and moved into the app tier to do the business logic processing.

Why open source?

Like Java, Python, and R have open-source language extensions, it is a natural next step to open source the .NET 5 C# Language Extension. This language extension leverages the Extensibility Framework API for SQL Server and this API is publicly documented. The API along with the open-source code of the .NET Core C# language extension shows an example of how a programming language extension can be built. Now, more developers in the SQL Server community can continue to develop additional programming language extensions.

Get started

If you want to start using the .NET 5 C# language extension for SQL Server, follow this tutorial. If you are interested in creating your own language extension, explore more information on SQL Server Language Extensions.

The post Open sourcing the .NET 5 C# Language Extension for SQL Server appeared first on Microsoft SQL Server Blog.

Data science, machine learning, and analytics have re-defined how we look at the world. The R community plays a vital role in that transformation and the R language continues to be the de-facto choice for statistical computing, data analysis, and many machine learning scenarios.

The importance of R was first recognized by the SQL Server team back in 2016 with the launch of SQL ML Services and R Server. Over the years we have added Python to SQL ML Services in 2017 and Java support through our language extensions in 2019. Earlier this year we also announced the general availability of SQL ML Services into Azure SQL Managed Instance. We remain committed to R.

With that said, much has changed in the world of data science and analytics since 2016. Microsoft’s approach to open-source software has undergone a similar transformation in the same period. It is therefore time for us to share how we, in Azure SQL and SQL Server, are changing to meet the needs of our users and the R community moving forward.

SQL Server 2022

Learn more about the latest version of SQL Server.

Learn more

Today we are making the following announcements to clearly state our direction and intent for R within Azure SQL and SQL Server.

Microsoft R-Open (MRO) will be phased out in favor of the official CRAN distribution

Microsoft R-Open (MRO) is Microsoft’s distribution of R. Azure SQL and SQL Server products and services will begin to phase out the use of MRO in favor of the CRAN distribution of R. The next release of SQL Server will use the CRAN distribution of R for SQL ML Services. Similarly, Azure SQL Managed Instance will also be upgraded to the CRAN distribution of R in a future update.

Version 4.0.2 will be the last release of the MRO runtime. Customers using MRO as their R runtime should transition to the CRAN distribution of R. It is important to note that the CRAN Time Machine will be unaffected by this change and will continue to be supported.

Microsoft R and Python packages will be released as open-source and supported on the CRAN distribution of R

As part of the acquisition of Revolution Analytics, Microsoft acquired proprietary technology for running models with unprecedented performance and scalability. We will open source the RevoScaleR and revoscalepy packages, making them freely available under the MIT license. The Python packages will be made available in PyPi while the R packages will be ported from MRO to the CRAN distribution of R. Packages will be maintained by Microsoft as new versions of Python and the CRAN R runtime are released. To simplify the installation and creation of development environments, these R packages will be built and published into the Azure R universe.

Machine Learning Server is being retired; SQL investment directed to SQL ML Services

Version 9.4.7 will be the last release of the Microsoft Machine Learning Server and the product will be retired, effective July 1, 2022. Microsoft R Client will also be retired at the same time. The next version of SQL Server will not include the Machine Learning Server (Standalone) role as part of the setup experience. However, SQL Machine Learning Services will not be impacted by this change.

Existing customers of Machine Learning Server will be able to access the software for the next 12 months via the volume licensing download site. Support for Machine Learning Server has been extended by a period of 12 months from the date of this announcement, providing existing customers with the opportunity to migrate while still having a supported platform by Microsoft. Customers using Machine Learning Server as a development environment for R programming will be able to create their own environments with the Revo packages once they have been open sourced.

Moving forward, Azure SQL and SQL Server machine learning product investments will focus on SQL Machine Learning Services and the T-SQL language surface area. We encourage you to explore programming in R using Azure SQL Managed Instance or SQL Server 2019 with SQL Machine Learning Services. Enterprise R scoring scenarios such as linear regression, logistic regression, or boosted decision trees using the T-SQL PREDICT function provide market-leading performance and scalability that is unmatched in the industry. For native scoring, this is the recommended path forward.

Customers using R for machine learning training scenarios should also explore sp_execute_external_script as it provides greater flexibility, letting you run any valid R code without having to take the data out of your database.

“I am delighted that next version of SQL Server and Azure SQL will introduce new machine learning and advanced analytics capabilities based on the CRAN distribution of R, its original home. My customers rely on the amazing performance and the ease of use of the T-SQL PREDICT statement. Being able to do nanosecond-scale predictions, using billions of rows per second, is a superb feature of SQL Server! However, we also like the performance and efficiency of running more traditional R and Python code within the database thanks to the rest of RevoScale technology, like the very convenient RxSqlServerData. We have always wanted to have that functionality available in the open-source R, without having to rely on some infrequent Microsoft distribution of it. It is great that this will be possible in the upcoming version. I think it will make the combination of R and both Azure SQL and SQL Server even more powerful, easier to use, and more popular.”—Rafal Lukawiecki, Data Scientist at Project Botticelli

Finally, Microsoft Azure Machine Learning, Microsoft’s flagship machine learning PaaS platform, is now also available on Azure Arc to power your hybrid and multi-cloud machine learning topologies. You can learn about the public preview of Azure Arc-enabled machine learning from the recent product announcement. Plus, check out the product documentation for Azure Arc-enabled machine learning.

Looking to the future with the R community

Microsoft provides several great machine learning platforms. We also understand that the future and strength of R is in its open-source community. The Azure SQL and SQL Server teams are embracing that spirit of openness by sharing our direction and intent with you. We are at the beginning of this journey, and we look forward to serving the needs of the entire R community moving forward.

The post Looking to the future for R in Azure SQL and SQL Server appeared first on Microsoft SQL Server Blog.

Create a Jupyter Book

Use the command “Jupyter Books: Create Book (Preview)” to launch a preview experience for creating a Jupyter book through an Azure Data Studio notebook. The notebook handles the installation of any Python dependencies and prompts for the location of notebooks and markdown files to be compiled into a Jupyter book.

Jupyter books can be shared widely with low-end user friction through two methods, remote books, and Jupyter book extensions. The former requires specifically formatted GitHub releases and the latter requires packaging an extension containing the Jupyter book. We will further explore remote Jupyter books, including leveraging a GitHub action to facilitate creating remote Jupyter books.

Accessing Remote Jupyter Books

Remote Jupyter books load a Jupyter book from a public repository into Azure Data Studio, either in the currently open folder or a temporary location. Adding a remote Jupyter book to Azure Data Studio starts through the action menu in the notebooks pane.

The resulting dialog has a text input for the repository URL where a remote Jupyter book is hosted. Once the repository URL is input, you are presented with the Jupyter book releases hosted in that repository. A single repository can host multiple releases and multiple Jupyter books, whether they are variations for different SQL engines, use cases, or documentation language.

Creating a Remote Jupyter Book

The remote Jupyter books feature in Azure Data Studio is an integration with GitHub releases, and it follows that creating a remote Jupyter book is a variation of creating a GitHub release. The remote book release on GitHub requires the Jupyter book to be attached as both a .zip archive and a .tar.gz archive for full cross-platform compatibility. The Azure Data Studio fields for book name, version number, and language are populated from the GitHub release title and the name of the uploaded compressed Jupyter book.

This release can be created through the GitHub release interface and after manually creating the Jupyter book .zip and .tar.gz archives. The naming scheme for the archive files is a crucial step, where hyphens separate the book, version, and language parameters. Once the release is published in GitHub, it is available in Azure Data Studio as a remote book.

Sharing this Jupyter book with users is now as straightforward as giving them the repository URL, such as “repos/Microsoft/tigertoolbox”. While the process of creating a remote Jupyter book might seem daunting, it is possible to streamline this with the use of GitHub actions.

Automating a Remote Jupyter Book Release

GitHub actions are hosted workflow runners that are capable of automating software development processes right from a repository on GitHub. Prepackaged actions are available on the GitHub marketplace and can be combined to create custom workflows. To expedite the creation of a GitHub release for a remote Jupyter book, a GitHub action is available that takes inputs similar to those in the Azure Data Studio interface and creates the corresponding GitHub release to publish a remote Jupyter book.

The “Remote Jupyter Book Publish” action pairs nicely with a manual trigger, also known as workflow_dispatch, which creates a form in GitHub with inputs for each of the remote book variables. GitHub actions are managed with YAML file definitions that are stored within the /.github/workflows directory in the repository. By leveraging the workflow_dispatch trigger, inputs can be defined and made available to repository maintainers through a familiar-looking form in GitHub. By entering the required inputs and clicking “run workflow”, a release for a remote Jupyter book will be created on the repository.

This complete sample is available in the documentation on the GitHub marketplace where the action “Remote Jupyter Book Publish” is now available in preview. Check out the GitHub action in use on this sample repository. To learn more about GitHub actions, check out the quickstart documentation.

​

The post Building and sharing Jupyter Books in Azure Data Studio appeared first on Microsoft SQL Server Blog.

Azure Data Studio is a multi-database, cross-platform desktop environment for data professionals using the Microsoft family of on-premises and cloud data platforms on Windows, MacOS, and Linux. To learn more, visit our GitHub.

The key highlights to cover this month include:

• Added deployments of Azure SQL Database and virtual machines (VMs).
• Added PowerShell kernel results streaming support.
• New extension updates.
• Bug fixes.

For a list of complete updates, refer to the Azure Data Studio release notes.

Added deployments of Azure SQL Database and VMs

You can now deploy Azure SQL resources from the deployment wizard in Azure Data Studio. These new options sit alongside local options like SQL Server on-premises and on Big Data Clusters and hybrid options, like SQL Managed Instance on Azure Arc. The deployment wizard includes UI-assisted Notebook experiences to deploy Azure SQL virtual machines and links to the Azure portal to create SQL databases, database servers, and elastic pools (SQL managed instances are not yet included).

This added experience comes with the overall commitment to add more Azure resource management into Azure Data Studio. The announcements above mention the inclusion of Azure Arc and Azure SQL Edge. Recently in June, we added a link to Open in Azure Portal for any existing Azure SQL server in Azure Data Studio. Expect more improvements like this to come in future releases.

Added PowerShell kernel results streaming support

We have received several requests from the community that PowerShell notebooks need to support results streaming. As it turns out, this change required changes in the Open Source PowerShell kernel, which we are also active maintainers. If you upgrade to the latest PowerShell kernel, you will now be able to have results streaming supported as shown below.

New extension updates

With this release, we have pushed out new versions of the following extensions. If you have not tried out these extensions before, check out each extension’s documentation and try it out.

• Azdata
• Azure Arc
• Import
• Kusto
• Machine Learning
• Schema Compare
• SQL Assessment
• SQL Database Projects

Bug fixes

If you would like to help make Azure Data Studio an even better product, share any feedback or report issues through our Issues page. Our engineering team is regularly going through the untriaged issues and assigning issues into different monthly milestones so that you know we are working on it. Your votes on issues help us prioritize.

Take a look at the full list of bug fixes for the October release.

Contact us

If you have any feature requests or issues, please submit to our GitHub issues page. For any questions, feel free to comment below or tweet us @AzureDataStudio.

The post The October 2020 release of Azure Data Studio is now available appeared first on Microsoft SQL Server Blog.

In SQL Server 2016 through 2019, we added R and Python language support, which enable secure execution of R and Python programs in the context of a SQL Server query. This enables a wide range of scenarios such as performing advanced text and data preparation tasks, and reaching out to external APIs to get data and also training machine learning models and model scoring.

Previously, we announced a Java extension. Today, we are sharing that we are open sourcing the R and Python language extensions for SQL Server for both Windows and Linux on GitHub.

These extensions are the latest examples using an evolved programming language extensibility architecture which allows integration with a new type of language extension. This new architecture gives customers the freedom to bring their own runtime and execute programs using that runtime in SQL Server, while leveraging the existing security and governance that the SQL Server programming language extensibility architecture provides.

Choosing which runtime to use does provide the flexibility to choose different distributions of R and Python, and as newer versions of the R and Python runtimes get released, this architecture will make it easier to upgrade the R and Python runtime. Enterprises need to have a support contract in place for their R and Python runtime.

Now that support is not an issue, let’s look at what use cases R and Python can enable inside SQL Server. Bringing R and Python workloads closer to the data opens a variety of possibilities:

• Run R and Python scripts to do data preparation and general purpose data processing.
• Train machine learning models in database.
• Deploy your models and scripts into production in stored procedures.
• Furthermore, this will help avoid unnecessary data movement and latency when data must be retrieved from SQL Server and moved into the app tier to do the business logic processing.
• Data Security Model of database logins and roles extend to external scripts.
• Avoid impersonation attempts.
• Prevent the installation of malware.

Why Open Source?

The R and Python language extension leverages the Extensibility Framework API for SQL Server to communicate and exchange data with SQL Server. This API has been publicly documented. The API in combination with the open source code of the R and Python language extension provides an end to end example implementation of how a programming language extension can be built. This makes it easier for additional programming language extensions to be built for SQL Server by the community. What language extensions would you like to see?

Get started

Whether you are interested in creating your own language extension or just using the R and Python language extension for SQL Server, here is some more information to get you started.

The post Open sourcing the R and Python language extensions for SQL Server 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.

To broaden deployment options for our customers, you can run SQL Server on-premises with SUSE Linux Enterprise Server SLES 12, in the cloud with SLES-based Azure Virtual Machines, and in SQL Server SLES containers running on the SUSE Container as a Service (CaaS) Platform, and expect a consistent experience from on-premises to cloud. Customers will not need a separate repository or package requirements in case of SLES 12 SP5 and the guidelines documentation for quickstart installation remains current. Support levels are also updated in the release notes. Additionally, last week at SUSECON Digital we made announcements related to Azure Arc hybrid capabilities. Azure Arc enables deployment of Azure services anywhere and extends Azure management to any infrastructure across on-premises, edge, and multi-cloud environments. 

SQL Server 2019 on SLES 12 on-premises

Announcing SQL Server 2019 on SLES 12 SP5 is now fully supported for production use. With SQL Server 2019, you can take advantage of features such as intelligent query processing, data virtualization, accelerated database recovery, improved developer experiences, and much more deployed on the SUSE SLES 12 environment of your choice. See how SQL Server 2019 customers like Itaú Unibanco, a banking leader in Latin America, use the intelligent query processing capabilities in SQL Server 2019 with virtually no code changes to achieve incredible performance for several business-critical processes.  

One major performance improvement in SUSE SLES 12 SP5 is the support for Forced Unit Access (FUA) for user-mode IO calls for XFS file system. Users of SQL Server on Linux may have been introduced to certain storage and IO flush related configurations due to the unavailability of FUA in user mode. With the introduction of FUA capability support in user mode for XFS filesystem by the SUSE engineering team, users can realize high-performance gains for IO-bound workloads on SQL Server. 

Learn more about how you can get the best performance with SQL Server 2019 on SUSE SLES 12. 

SQL Server 2019 on SLES 12 Azure Virtual Machines

For customers interested in running SQL Server as Infrastructure as a Service (IaaS), the Azure Marketplace offers pre-configured SQL Server 2019 on SUSE SLES 12 Azure Virtual Machines. The SUSE Linux Enterprise Server with High Availability Extension provides mission-critical uptime, fast failover, improved manageability, and easy configuration for Always-on Availability Groups (AG) for SQL Server High Availability setup on Azure. While users will be able to set up a pacemaker cluster on SUSE SLES VM on Azure, high availability in the SUSE repository will be enabled in a future SQL Server community update. 

Currently, to have a highly available environment with SUSE on Azure, you can bring your own subscriptions to Azure SLES 12 SP5 and SUSE HA extension.

Customers can bring their SQL Server licenses with Software Assurance to the cloud with Azure Hybrid Benefit to maximize cost savings, and may also consider Azure Reserved VM Instances. 

Learn how to get started running SQL Server on Azure Virtual Machines on SUSE Linux in this on-demand webinar. 

SQL Server on SUSE CaaS Platform

Deploying SQL Server in containers simplifies and speeds up deployments making it easier for application development, database DevOps, and deploying in production. Customers can run SQL Server on the Kubernetes-based SUSE CaaS Platform at-scale in your on-premises environment. 

Learn more and get started today

There are a number of exciting free virtual sessions from SUSECON Digital for you to watch either live or on-demand. Here’s a list of all content from Microsoft at SUSECON Digital. Highlighted sessions include: 

• SQL Server in SUSE ecosystem – SQL server 2019 improvements 
• Extend Azure management to SLES anywhere with Azure Arc  
• Microsoft & SUSE co-present: The seamless collaborative support model for SUSE workloads on Azure 
• SQL Server on SUSE CaaS Platform 

Learn more about SQL Server on Azure Virtual Machines, and SQL Server 2019 Big Data Clusters. For a technical deep-dive on Big Data Clusters, read the documentation and visit our GitHub repository. 

Get started today by downloading SQL Server on SUSE Linux on-premises or provisioning a pre-configured Azure Virtual Machine image. 

The post Open innovation, customer choice, and reliability with SQL Server on SUSE appeared first on Microsoft SQL Server Blog.

In SQL Server 2019, we added a Java language extension, which enables secure execution of Java programs in the context of a SQL Server query. This enables a wide range of scenarios such as performing advanced text and data preparation tasks, reaching out to external APIs to get data and also training machine learning models and model scoring.

Today, we’re thrilled to announce that we are open sourcing the Java language extension for SQL Server on GitHub.

This extension is the first example of using an evolved programming language extensibility architecture which allows integration with a new type of language extensions. This new architecture gives customers the freedom to bring their own runtime and execute programs using that runtime in SQL Server while leveraging the existing security and governance that the SQL Server programming language extensibility architecture provides.

Choosing which runtime to use does provide the flexibility to choose different distributions of Java, and as newer versions of the Java runtime get released, this architecture will make it easier to upgrade the Java runtime. However, this freedom may raise some questions around support. Enterprises need to have a support contract in place for their Java runtime. The answer, in this case, is that we’ve got you covered. Thanks to a partnership between Microsoft and Azul, all Azure and SQL Server customers can use Azul’s Zulu for Azure – Enterprise distribution of Java for free with support jointly provided by Microsoft and Azul. This supported distribution of Java is included in SQL Server out of the box!

Now that support is not an issue, let’s look at what use cases Java can enable inside SQL Server. Bringing Java workloads closer to the data opens a variety of possibilities:

• This extends the TSQL surface area to better handle use cases involving regular expressions, string handling, and NLP support.
• This functionality also helps in migration scenarios from Oracle, where applications rely on Oracle Java procs in the database. With the ability to execute Java inside stored procedures in SQL Server, there is now a path for enabling Java application migrations to SQL Server.
• Java application development teams that leverage SQL Server as backend storage can now even embed Java code in stored procedures which enables pushing business logic down into the database for better performance.
• Furthermore, this will help avoid unnecessary data movement and latency when data must be retrieved from SQL Server and moved into the app tier to do the business logic processing.

Why Open Source?

The Java language extension leverages the Extensibility Framework API for SQL Server to communicate and exchange data with SQL Server. This API has been publicly documented. The API in combination with the open source code of the Java language extension provides an end to end example implementation of how a programming language extension can be built. This makes it easier for additional programming language extensions to be built for SQL Server by the community. What language extensions would you like to see?

Get started

Whether you are interested in creating your own language extension or just using the Java language extension for SQL Server, here is a tutorial to get you started.

The post Open sourcing the Java language extension for SQL Server appeared first on Microsoft SQL Server Blog.