Monitor SQL Server enabled by Azure Arc 

Simplifying deployment and cost management in multi-cloud environments.

Learn more

The traditional method of paying for SQL Server is buying license and software assurance. Now, you can connect your SQL Server to Azure Arc and pay only for the hours when your VM with SQL Server is online and your SQL instance is active. For the pricing information, see SQL Server 2022—Pricing | Microsoft.

While SQL Server 2022 integrates a pay-as-you-go billing option in the setup, the older versions require a product key to install any production edition. This blog provides instructions on how to install a pay-as-you-go image of any SQL Server version starting from SQL Server 2012 without purchasing a license and providing a product key.

NOTE: if you have access to a SQL Server image that does not require a product key, for example from a hosting provider, you should install it, follow the documented Azure Arc onboarding process for SQL Server and then set the License type to pay-as-you-go in SQL Server configuration.

The process in a nutshell

The deployment of a pay-as-you-go image on a virtual or physical machine running in any cloud, edge, or on-premises datacenters consists of three steps:

1. Installing the prerequisites on the target machines
2. Creating a support ticket with Microsoft Azure to request and download a pay-as-you-go image
3. Running the installation script

Prerequisites

Make sure your target machine meets the following requirements.

On target VMs:

• Windows Server instance is running, and you have a local administrator account.
• Azure PowerShell is installed and updated.
• For SQL Server version 2014, you will need to install .NET. For more information on this prerequisite, visit here.
• You have a remote desktop connection to connect to Windows Server as a local administrator.
• You copied the script from here to a local folder. Follow the download instructions in the readme file.
• Important: If your target VM runs Windows Server 2016, you must make sure that your Transport Layer Security (TLS) configuration is compatible with Azure. Follow the instructions in the readme file on how to mitigate the TLS version issue on Windows Server 2016.

On Microsoft Azure:

• You have an Azure account, subscription, and a target resource group.

Opening a support ticket

You can open the support ticket using a Support + Troubleshooting entry from the subscription which the newly installed SQL Server instance will be connected to. The following screenshots illustrate the flow. Make sure to use the answers as highlighted in the screenshots.

After you complete the support ticket creation process, Azure support will email you a private link to a workspace with the zip file you need to download.

Download the file to the VM where you want to install SQL Server, and unzip it to the same folder that contains the installation script.

Note: For SQL Server 2012 and SQL Server 2022 images, the keys are not required, and the .zip file will contain only the ISO file. For other SQL Server versions, the .zip file includes a product key and the ISO file. The script will recognize these differences.

Running the script

Follow the instructions in the readme file on how to launch the script. An Azure sign-in screen will prompt you to enter your Azure credentials or identify the account you’re already signed into. After this, the script proceeds unattended to carry out the following steps:

• Installing Azure PowerShell modules if they’re not already installed
• Logging into Azure with your assistance
• Onboarding the VM to Azure Arc
• Installing SQL Server on the Windows Server from the file you identified in the previous step
• Mounting the ISO file as a volume
• Installing SQL Arc Extension with a pay-as-you-go license type
• Displaying the status of the Azure resource for the connected SQL Server Instance

Disclaimer: The script has been tested on several combinations of VM products available on AWS and Google Cloud Platform with different versions of Windows Server and SQL Server, but it is not specific to these platforms, and you are welcome to try it on other clouds.

Supercharge SQL Server with Azure Arc

You can use this process to streamline the installation of pay-as-you-go images of SQL Server versions and editions of your choice to machines across different clouds, on-premises datacenters, and edge. By doing so, you can maintain control over the SQL Server compliance and optimize the cost based on the resource’s utilization.

The post Save money on Microsoft SQL Server licensing with Microsoft Azure Arc appeared first on Microsoft SQL Server Blog.

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.

In response to the ever-growing demand for various deployment models, you can run SQL Server on-premises with RHEL 8, in the cloud with Azure Virtual Machines, and in containers with Red Hat’s OpenShift Container Platform. Additionally, this week at Red Hat Summit we made exciting 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 multiple clouds.

SQL Server on RHEL 8, including UBI

You can be assured that the latest SQL Server and Red Hat innovations work well together. With SQL Server 2019, you can leverage features such as Intelligent Query Processing, data virtualization, improved developer experiences, and much more deployed on the Red Hat 8 environment of your choice. You can deploy SQL Server 2019 on Azure, or in your data center with virtual machines. You can leverage orchestrators like OpenShift to run SQL Server in a fully supported stack by Microsoft and Red Hat by using UBI-based images for SQL Server containers.

Learn more about how you can get started with SQL Server 2019 on Red Hat Enterprise Linux 8.

SQL Server on RHEL 8 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 Red Hat Enterprise Linux 8 Azure Virtual Machines. The latest technology from both Red Hat and Microsoft in the image allows for quick yet highly scalable and performant deployments. These images have high availability Red Hat repository enabled, which allows one to configure Availability Groups on SQL Server with RHEL VM on Azure. To maximize cost savings, customers can bring their SQL Server licenses with Software Assurance to the cloud with Azure Hybrid Benefit and may consider Azure Reserved VM Instances.

SQL Server 2019 Big Data Clusters (upcoming)

SQL Server 2019 became generally available on Nov 1, 2019. Among the capabilities introduced with this release is Big Data Clusters, a new deployment pattern for SQL Server that adds Apache HDFS and Spark for big data storage and analytics, which will be supported on Red Hat OpenShift in an upcoming cumulative update release of SQL Server 2019. This deployment pattern runs entirely as Linux container images on Kubernetes enabling customers to run their analytical workloads at any scale, on an integrated platform designed to derive new intelligent insights out of data.

Red Hat’s OpenShift container platform (OCP) is a market leader in commercially supported Kubernetes distributions and is one of few supported reference platforms for running SQL Server in containers. Red Hat and SQL Server engineering teams are closely partnering to enable support for BDC on this platform. It is key for our enterprise customers to unblock BDC deployments on a fully supported stack, that follows best practices and guidance unique to OCP. We are working to enhance our deployment model and enable customers to be able to follow this guidance, so privileged containers are not required. In addition to non-privileged, containers should be running as a non-root user by default. Where elevation of privileges will be necessary to enable better process isolation within a container, we will clearly describe what and why we require certain security policies.

Learn more and get started today

We have a number of exciting free virtual sessions at this week’s Red Hat Summit Virtual Experience. Watch live or on-demand:

• Managing Red Hat Enterprise Linux 8 for SQL Server database administrators.
• Extend Azure management to Red Hat Enterprise Linux anywhere with Azure Arc.

Learn more about the SQL Server partnership with Red Hat, 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 Red Hat Linux on-premises or provisioning a pre-configured Azure Virtual Machine image.

¹ All claims as of April 24, 2020: 1TB TPC-H, 30TB TPC-H.

The post Enabling customer choice, agility, and performance with SQL Server on Red Hat appeared first on Microsoft SQL Server Blog.

For our purposes, I’m going to walk through how to use the Azure portal to create a Linux virtual machine with SQL Server 2017 or 2019 installed. If you’re following along, you’ll need an Azure subscription. If you don’t already have one, sign up for an Azure free account. Alternatively, if you know which options you’re going to select, you can create a Linux virtual machine with the Azure command-line interface. Or, if you want to learn more about the benefits of SQL Server in Azure, download Future-Proof Your Data Infrastructure with Azure: A Business Case for Database Administrators.

First, we’ll create a Linux virtual machine with SQL Server installed:

1. Sign in to the Azure portal.
2. Select Create a resource, and then Compute.
3. Next to the Featured heading, select See all.
4. In the search box, type SQL Server 2019, and select Enter.
5. You can limit the search results by operating system. To do so, select Operating system > Red Hat.
6. Select a SQL Server 2019 image from the search results.
7. Select Create.

The next step is to set up your Linux virtual machine:

1. In the Basics tab, select your Subscription and Resource Group.
2. In virtual machine name, enter a name for your new Linux virtual machine.
3. Type or select the following, based on your requirements such as region, availability options, and change size.
4. For authentication type, select SSH public key. (SSH is more secure than a password.)
5. For username, enter the administrator’s name.
6. For SSH public key, enter your Rivest–Shamir–Adleman (RSA) public key.
7. For public inbound ports, choose allow selected ports and choose the SSH (22) port in the select public inbound ports.
8. You can change or keep the default settings for disks, networking, management, guest configuration, and tags.
9. Select Review and Create, and then select Create.

Next, connect to the Linux virtual machine. If you use a BASH shell, connect to the Azure virtual machine using the SSH command. In the following command, replace the virtual machine username and IP address to connect to your Linux virtual machine, and you can find the IP address of your virtual machine in the Azure portal:

ssh azureadmin@40.55.55.555

If you don’t have a BASH shell, install an SSH client like PuTTY.

The new virtual machine installs SQL Server with a random system administrator (SA) password, so you’ll need to reset the password before connecting to SQL Server with the system administrator login:

1. After connecting to the Linux virtual machine, open a new command terminal.
2. Change the system administrator password with the following commands:

sudo systemctl stop mssql-server

sudo /opt/mssql/bin/mssql-conf set-sa-password

When prompted, enter a new system administrator password and password confirmation.

3. Finally, restart the SQL Server service:

sudo systemctl start mssql-server

You’ve just provisioned a SQL Server 2017 virtual machine in Azure! You can connect to it locally with sqlcmd to run Transact-SQL queries or you can configure the Azure virtual machine for remote SQL Server connections. The best part is, you now have a full version of SQL Server in the cloud. There’s no need to manage on-premises hardware or secure the budget to procure and manage that hardware. Plus, the images are pay-as-you-go, so you only pay for what you use.

To learn more about moving your databases to the cloud, download Future-Proof Your Data Infrastructure with Azure: A Business Case for Database Administrators or check out the SQL Server on RHEL Azure VMs Operations Guide.

The post How to provision a Linux SQL Server Virtual Machine in Azure appeared first on Microsoft SQL Server Blog.

The mssql extension for Visual Studio Code is the official SQL Server extension that supports connections to SQL Server and rich editing experience for T-SQL in your favorite developer tool, Visual Studio Code.

You can download the mssql extension and try out a tutorial to get started.

This is one of the largest mssql extension updates to date. This update includes the number one and two most requested features over the last two years: SQL Server Management Studio Object Explorer and SQL Server command line (SQLCMD) support. In addition, we’re innovating IntelliSense through AI-assisted Visual Studio IntelliCode support.

This release includes:

• Announcing IntelliCode support
• Added support for Object Explorer
• Added support for SQLCMD mode
• New SqlClient driver
• Bug fixes

Read on to learn more about this new update.

Announcing IntelliCode support

Today we’re sharing a wonderful collaborative effort with the Visual Studio Code IntelliCode team. If you haven’t heard of IntelliCode, we highly encourage you to read the general availability announcement from Build 2019. IntelliCode is a set of AI-assisted capabilities that aims to improve developer productivity with features like contextual IntelliSense, code formatting, and style rule inference. With this release of the mssql extension, we now support IntelliCode in the mssql extension.

For this initial launch, SQL Server developers can now get smarter IntelliSense for T-SQL reserved keywords like SELECT, FROM, WHERE, and many more. The IntelliCode suggestions are starred items that appear at the top of the IntelliSense list. This improves productivity as you write your T-SQL queries as reserved keywords you are looking for appear at the top for quick completion. You can see the suggestions by the light bulb icon that appears in the IntelliSense menu as you are typing. This improves productivity as you write your T-SQL queries as reserved keywords that you are looking for appear at the top.

In order to try out IntelliCode, you will first need to download the IntelliCode extension. After downloading the extension, you can start seeing IntelliCode working instantly in your SQL Server query files.

We will continue to work with the IntelliCode team to unlock even more IntelliCode features for our SQL Server users. If this feature triggers a light bulb for you and you have ideas for how this feature can help you, please submit a feature request on our GitHub issues page.

To learn more about IntelliCode, check out the IntelliCode product page.

Announcing Object Explorer

The #1 most requested feature is to bring Object Explorer support into the mssql extension, and we ‘re excited to announce we now support an Object Explorer in Visual Studio Code.

This well-known feature is an essential component for our dedicated SQL Server Management Studio and Azure Data Studio users. An object explorer allows developers to easily make connections to their SQL servers and databases and explore the hierarchy of the SQL Server instance. Users can also easily launch new query windows for specific databases or even run a basic SELECT TOP 1000 on a table. This makes it much easier for developers to explore the structure of a database.

After downloading the new update, you can access the Object Explorer through the server icon on the left activity bar. You can then add a new connection or explore an existing connection. We hope this feature will improve your productivity.

If you have additional feature requests you would like to see in the Object Explorer, let us know on GitHub.

Announcing SQLCMD mode

The second most highly asked for feature that was also announced in Azure Data Studio was adding SQLCMD mode support. SQLCMD mode allows users to write and edit queries as SQLCMD scripts, and also execute the SQLCMD scripts.

From the command prompt, users can toggle SQLCMD mode on or off, and Visual Studio Code will recognize the syntax.

To learn more about SQLCMD mode, check out our documentation.

Microsoft.Data.SqlClient support

With this release, this extension is moving from System.Data.SqlClient to the new Microsoft.Data.SqlClient client access library. This new library was announced at Build in May and released to general availability in August. This update will keep the mssql extension in step with SQL Server as new driver-impacting features are released.

Bug Fixes

With each release, we look at the top requested customer reported issues and strongly consider those issues first. The theme for this release was to focus on improving security and accessibility issues.

Fixes include:

• Users can now adjust the size of the SQL results window
• Users can navigate with the keyboard away from the SQL results screen
• Fixed copy and paste with keyboard shortcut
• Added Copy Header option to the results grid
• The full list of bug fixes can be found on our GitHub

Contact us

If you have any feature requests or issues to report, you can reach out to us through GitHub issues. 

The post Mssql extension for Visual Studio Code now has Object Explorer and IntelliCode appeared first on Microsoft SQL Server Blog.

As many of you may know, we have already enabled certain Azure Data Studio capabilities such as launching Notebooks, ADS, etc. from SSMS.

In our June release of Azure Data Studio, we also enabled launching SSMS functionality from Azure Data Studio. You can check Alan’s blog post describing these features to learn more.

You can download SQL Server Management Studio 18.1 today and review the SSMS release notes for further details. Some of the recent updates include:

SSMS

• Yes, we’ve heard the feedback; Database Diagrams is back.
• And, so is ssbdiagnose utility.
• Is SSMS ignoring Intellisense settings? Not anymore!

SSIS

• Support for scheduling the SQL Server Integration Services (SSIS) package is now available, located in the SSIS catalog in Azure or the file system in Azure. There are three ways to launch the New Schedule dialog; the first is from the New Schedule menu item shown when you right click the SSIS package in the SSIS catalog in Azure, the second is from the Schedule SSIS Package in Azure menu item under the Migrate to Azure menu in Tools, and the third is under Schedule SSIS in Azure that displays when you right click on the Jobs folder under SQL Server agent of Azure SQL Database Managed Instance.

SSMS setup

• We heard the feedback here too, and have fixed the issue where SSMS was exiting immediately after showing the splash screen.
• We also fixed an issue that was causing SSMS setup to fail when the setup log path contained spaces.

We have also resolved a number of other issues in areas such as Object Explorer, SQL Agent, accessibility, Intellisense, SMO scripting, data classification, etc.

Please see the release notes for a comprehensive list of bug fixes.

As always, we are always listening and would love to hear your feedback with any questions, comments, or feature suggestions.

The post SQL Server Management Studio (SSMS) 18.1 is now generally available appeared first on Microsoft SQL Server Blog.

Together with our partners, SQL Server continues to innovate with high-performing, enterprise-ready solutions that deliver unparalleled price performance. We’re pleased to share the results for the TPC-H (the industry standard for benchmarking data warehouse) performance at 3TB with HPE that currently holds the record as the industry’s fastest 2-socket non-clustered solution, and 10TB with Cisco, as well as the TPC-E (the industry standard for benchmarking online transaction processing) performance at 10TB with Lenovo. Check out the results below.

The table above shows the TPC-H data warehousing results published on SQL Server 2017 with configuration (size). The table below shows the TPC-E OLTP results published on SQL Server 2017 with throughput and price/performance ratio.

These new results for TPC-H at 3TB scale prove the superior combination of SQL Server 2017 Enterprise Edition, SUSE Linux Enterprise Server, and HPE ProLiant DL380 Gen10. The benchmark showcases the secure, resilient, and versatile 2P/2U HPE ProLiant DL380 Gen10 as an industry-leading server for multi-workload compute, ideal for structured data analysis such as decision support for large databases.

In addition, a new record for the 10TB non-clustered TPC-H benchmark has been accomplished with a winning combination of SQL Server 2017, Red Hat Linux 7.6, and Cisco UCS C480 M5 Server. Cisco’s 4-socket Cascade Lake system surpassed the previous #1 10TB non-clustered result by 11.6 percent in performance along with a cost reduction.

The TPC-E results showcase SQL Server 2017 blazing the trail together with our strategic partner Lenovo. Lenovo’s ThinkSystem SR650 on a 2-socket Cascade Lake system was the fastest 2-socket system when combined with SQL Server 2017 and Windows Server 2016. It surpassed the previous 2-socket Sky Lake publication by 3 percent, and was also the best overall  price performance leader.

Learn more about SQL Server 2017.

All claims in this post were made as of 5/15/2019.

Learn more

• SQL Server 2019 is now in public preview.
• Learn more about big data clusters in the SQL Server 2019 big data clusters white paper and sign up to try out the new features for big data, analytics, and AI in SQL Server 2019 Big Data Clusters by signing up for the SQL Server 2019 Early Adoption Program.

The post SQL Server 2017 achieves top TPC benchmarks for OLTP and DW on Linux and Windows appeared first on Microsoft SQL Server Blog.

The CTP 2.5 preview brings the following new features and capabilities to SQL Server 2019:

Big data clusters

• For more control and flexibility over the big data cluster layout and configuration settings, we’re introducing a new deployment mechanism that uses configuration files to deploy your cluster. You can start from the built-in configurations that come with the mssqlctl utility and customize them to accommodate the platform you want to run the big data cluster on.
• To streamline the deployment process, mssqlctl utility enables an interactive deployment experience that guides you through the steps to initiate the deployment with prompts for required inputs. You can also automate the entire process using mssqlctl configuration commands available to list, customize, or deploy using configuration files.
• To avoid incompatibilities between client utility and big data cluster server versions, you can now verify you installed the right version of the utility by using mssqlctl –version command.
• The new MSSQL Spark connector that uses SQL Server bulk write APIs to provide high performance Spark to SQL Server writes for high volume data.

Database engine

• PolyBase enables you to run a T-SQL query inside SQL Server to pull data from Cloudera or Hortonworks and return it in a structured format—all without moving or copying the data.  As part of CTP 2.5, we are now introducing the ability to install PolyBase on Linux for the first time. This includes the new connectors in SQL Server 2019 for additional data sources such as Oracle, Teradata, other SQL Servers, and MongoDB.
• The new Java language SDK for SQL Server can be used to simplify the development of Java programs that can be run inside of SQL Server.

Getting started

Ready to learn more?

• To get started with SQL Server 2019 CTP 2.5, you can find the download instructions on the SQL Server 2019 web page or use a SQL Server 2019 CTP virtual machine in Microsoft Azure
• Join the SQL Server 2019 Early Adopter Program for access to SQL Server big data clusters
• Check out what features were introduced in SQL CTP 2.4
• Register for our upcoming webinar on May 16th on Run SQL Server on Linux and Kubernetes
• Watch this free on-demand webinar to discover how SQL Server 2019 big data clusters can help you gain business insight.

The post SQL Server 2019 community technology preview 2.5 is now available appeared first on Microsoft SQL Server Blog.

Download Azure Data Studio and review the Release notes to get started.

Please note: After downloading Azure Data Studio, say Yes to enabling preview features so that you can use extensions.

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

Check out this video for a general overview of Azure Data Studio.

The key highlights to cover this month include:

• March release recap
• Azure Explorer improvements
• Visual Studio code merge process
• Insiders build process
• Viewlet revamp
• Notebook improvements
• Announcing SandDance extension
• Bug fixes

For a complete list of updates, please refer to the Release notes.

March release recap

Last month, we released many highly-requested features by the community including SQL Notebooks, PostgreSQL support, and the PowerShell extension for Azure Data Studio. To learn more about these features, check out the full blog post.

With the excitement for PostgreSQL support, this makes Azure Data Studio officially a multi-database tool. As a result, we’re curious to hear what other databases the community is looking for. Currently, we have the following tracking requests. If one of these databases listed interests you, please leave a thumbs up on the issue.

• MySQL
• SQLite
• Azure Cosmos DB
• VistaDB
• MariaDB
• Redis

If there are other databases you would like to see that are not on this list, please open an issue following the format of these issues.

Meanwhile, SQL Notebooks continues to be used in demos, conferences, videos, and blogs all over the world. You can keep track of all of the exciting ways the community is using Notebooks in Azure Data Studio across SQL Server, Azure SQL, and PostgreSQL by following us on Twitter. If you have ideas of how to showcase Notebooks, please don’t hesitate to tweet us.

We have also made it even easier to use the PowerShell extension for Azure Data Studio with new updated documentation. Give it a try and let us know your ideas to make even better use of the PowerShell extension for your database management needs.

Azure Explorer improvements

If you used the Azure Resource Explorer, you may be wondering where the Azure icon went. In this release, we wanted to make it even easier for you to manage your Azure resources by having an integrated object viewer experience with our Servers View. Thus, we made a few changes:

• Servers view has been renamed to Connections
• Azure view now allows users to browse objects in Azure SQL Databases directly without having to copy to Servers view
• Servers view has the exact same functionality as the previous Servers experience
  • Lists saved connections for SQL Server, Azure SQL, and PostgreSQL connections
  • Server groups to help organize connections
  • Right-click actions
• Azure view has the exact same functionality as Azure Resource Explorer
  • Sign in to Azure account
  • View Azure subscriptions and resources
  • Filter subscriptions

The Connections viewlet is fully extensible providing the opportunity to bring in future extensions such as Central Management Servers.  Check out the latest insiders build for an early preview of the CMS experience.

Visual Studio Code merge process

As requested by the community, we’ve decided to get the newest Visual Studio Code features even faster. Previously, we would be 2-3 months behind before doing a VS Code merge.  We’ve recently made engineering changes so that we can stay more closely in sync with VS Code.

As a result, the April release is built against the latest stable VS Code (1.33), which is their March release, and upgraded from 1.30.2. This includes features from:

• March release
• February release
• January release

Users should expect to see VS Code features light up much more quickly in Azure Data Studio going forward.

Insiders build process

As part of moving faster to align with VS Code releases, some new features will be shipped in an Insiders build which is a preview build that doesn’t have the same quality as our monthly stable builds. In production environments, you should keep using the monthly stable builds, but if you want to get the latest features, you can try out our Insiders build.

Insiders builds allow us to get feedback on new experiences like Central Management Servers, new welcome pages, schema compare, and more. We also update the Insiders build daily and keep the same download link for convenience.

To try out an Insiders build, go to our GitHub, and search below our stable releases to find the following:

Choose the installation method depending on your operating system, and then you are good to go. Keep in mind that Insiders builds will tend to have more bugs, so feel free to report any that you see.

If you prefer to stay on our monthly stable builds, then that is not a problem. Read on to learn more about changes in this release.

Notebook improvements

Notebooks in Azure Data Studio continues to grow in popularity with the release of SQL Notebooks for both SQL Server and PostgreSQL. Thus, we are committed to improving the Notebook experience for not only the SQL kernel, but for other kernels like Python. Here is a summary of Notebook changes this month:

• Added a button on the toolbar to clear output for all cells
• Added a button on the toolbar to run all cells
• Fixed connection name instead of server name (if set) in the Attach to drop down
• Fix for images in markdown not rendering when you’re using relative image paths
• Improved functionality in notebook grids by adding double-click auto-resize column size and improved mouse wheel support
• Improvements to error handling and Python install resiliency when installing Python through notebooks
• Improvements to select all functionality when selecting notebook cells
• Improvements to notebook connections to prevent closing a notebook and impacting an object explorer connection
• Improved notebook experience to display a message to the user when the notebook disconnected and needs a connection to run cells
• Improved support for unsaved notebooks to rehydrate in ADS when ADS is started again

As you use Notebooks in your day-to-day work or in demos, you will inevitably get ideas for feature requests. You can open issues on our GitHub directly and we will consider implementing these ideas.

Announcing SandDance extension

Azure Data Studio now offers quick visualizations for the files you are working on whether its local files or files you are working on HDFS in your SQL Server 2019 Big Data Cluster. SandDance, the well-loved data visualization tool from Microsoft Research, has been re-released as a Preview extension in Azure Data Studio and as an open source project on GitHub. In addition, SandDance is also available as a Preview extension to Visual Studio Code.

You can use SandDance to explore and tell stories with your data, easily discover patterns and insights by visualizing datasets across different attributes using natural user interactions. By using easy-to-understand views, SandDance helps you find insights about your data, which in turn helps you tell stories supported by data, build cases based on evidence, test hypotheses, dig deeper into surface explanations, support decisions for purchases, or relate data into a wider, real world context.

This is especially helpful when you are trying to have a quick look at the data and understand what’s going on.

SandDance was created by Microsoft Research’s Visualization and Interactive Data Analysis (VIDA) group.

Visit the new SandDance GitHub project or try it out now with the companion website.

Bug fixes

If you would like to help make Azure Data Studio a great product, please 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 will know what we are working on it. Your votes help us prioritize issues.

Check out the full list of bug fixes for the April 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.

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

Recently, we blogged about performing automated machine learning on SQL Server 2019 big data clusters. In today’s post, we will present a complementary automated machine learning approach leveraging Azure Machine Learning service (Azure ML) invoked from SQL Server. While the previous post dealt with a Spark-based implementation tuned for big data, this post presents an approach that runs directly in SQL Server running on a single server. This is well suited for use with data residing in SQL Server tables and provides an ideal solution for any version of SQL Server that supports SQL Server Machine Learning Services.

Azure Machine Learning service

Azure Machine Learning service is a cloud service. We call the service from SQL Server to manage and direct the automated training of machine learning models in SQL Server. Automated machine learning tries a variety of machine learning pipelines.  It chooses the pipelines using its own machine learning model based on the scores from previous pipelines. Automated machine learning can be used from SQL Server Machine Learning Services, python environments such as Jupyter notebooks and Azure notebooks, Azure Databricks, and Power BI.

Starting in SQL Server 2017, SQL Server includes the ability to run Python code using the sp_execute_external_script stored procedure. This allows SQL Server to call Azure ML automated machine learning.  Instructions and code for running the following example are available on GitHub.

Example: Energy demand forecasting

Automated machine learning can be used for regression (the prediction of continuous values), classification, or forecasting. This example focuses on energy demand forecasting, where the goal of a power grid operator is to predict future energy demand given forecasted weather data. Although the context of our example is energy demand forecasting, the methods used can be applied to many other contexts and use cases.

This example uses an energy demand data set consisting of a table with four columns: timeStamp, demand, precip, and temp. We use three Transact-SQL stored procedures including AutoMLTrain, AutoMLGetMetrics, and AutoMLPredict. The AutoMLTrain procedure returns a model that predicts the label column – in this case, demand – based on the remaining columns that include timeStamp, precip and temp. Here, we run the AutoMLTrain procedure using data in rows with a time stamp before February 1, 2017:

The resulting model is stored in a SQL Server table so that it can be used later for prediction. The training can be viewed in the Azure Portal under Machine Learning service workspaces:

Optional parameters on the AutoMLTrain allow for explicit train/validate split, cross validation, sample weights, number of iterations, exit score, blacklist and whitelist models, forecasting, and time limits.

The AutoMLGetMetrics stored procedure returns several metrics for each pipeline.  These can be stored and queried in SQL Server.

Finally, the AutoMLPredict stored procedure can predict new values based on the model returned by AutoMLTrain. Having trained our model using data before February 1, 2017, we will forecast demand for February 1, 2017 and later:

Conclusion

In this blog post, we have seen how Azure ML automated machine learning can be used from within SQL Server to train models and then predict new values. SQL Server developers can now train and use machine learning models without needing to learn Python and without needing detailed knowledge of machine learning.

Get started

• You can set up the configuration described in this blog post using the instructions on GitHub.
• Learn more about SQL Server Machine Learning Services.
• Learn more about Azure ML automated machine learning.

The post Automated machine learning from SQL Server with Azure Machine Learning appeared first on Microsoft SQL Server Blog.