We are now fully deactivating the Microsoft/mssql-server-linux repository. A couple of years ago, we updated the old Docker Hub page with the above information and pointed to the current Docker Hub page, where all the old and latest SQL Server container Linux-based images can be downloaded from Microsoft Container Registry. As a result, we will disable the source “microsoft/mssql-server-linux” by May 31, 2021.

Learn more

Please upgrade to the Microsoft Container Registry if you are still using the old source. For more details on how you can download from the Microsoft Container registry, refer to the latest Docker Hub page.

The post Microsoft SQL Server Linux-based container images to be available only from the Microsoft Container Registry 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.

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 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.

Supported Linux platforms

Microsoft SQL Server 2019 is tested and supported to run on several Linux distribution platforms:

• Red Hat Enterprise Linux (RHEL)
• SUSE Linux Enterprise Server (SLES)
• Ubuntu

Along with the above versions of Linux distributions, SQL Server 2019 is supported in a container scenario using a Docker image.  Running a SQL Server database inside a Docker engine with Linux offers more flexibility, faster recovery, and quicker deployments, including deployments into the Azure cloud. For those becoming familiar with Linux, Docker for Windows or Mac gives you the option to run a Docker engine on your workstation with SQL Server 2019 on Linux.

Along with Docker technology, orchestration can be achieved, both managing and deploying SQL Server containers on Linux using Red Hat Open shift or Kubernetes. This includes SQL Server 2019 Big Data Clusters (BDC), fully scalable clusters with SQL Server, Spark, and Hadoop File System (HDFS). BDCs provide the ability to read, write, and analyze big data with T-SQL or Spark, and you can combine big data and relational data, too.

While this post has focused on RHEL, SLES, Ubuntu, and container options for Linux, you may have questions regarding other Linux distributions that may be able to run SQL Server 2019, but they’re not supported by Microsoft. Always consider the database server’s use before deciding to run SQL Server 2019 on an unsupported operating system and refer to Microsoft’s support policy on the topic.

Tools for Microsoft SQL Server 2019 running on Linux

With the release of SQL Server 2019, there are more tools that database professionals can use with SQL Server 2019 running on Linux:

• Windows-based tools—You can use any existing tools that run on Windows to access SQL Server on Linux. This includes SQL Server Management Studio (SSMS), SQL Server Data Tools (SSDT), operational support systems, and third-party tools.
• Activity Monitor—Use SSMS on Windows to connect remotely and use tools/features such as Activity Monitor commands on a Linux host.
• Azure Data Studio—Use this cross-platform database tool to manage the Microsoft family of on-premises and cloud data platforms on Windows, MacOS, and Linux. That includes SQL Server 2019 running on Linux. You can also create SQL Server Notebooks in Azure Data Studio, multiple team members to collaborate on projects.
• Dynamic Management Views (DMVs)—System DMVs collect different types of information about SQL Server, including Linux process information. Use server state information in conjunction with dynamic management functions (DMFs) to monitor the health of a server instance, diagnose problems, and tune performance.
• Query Store—Use Query Store for insights on query plan choice and performance. It simplifies performance troubleshooting by helping database professionals quickly find performance differences caused by query plan changes.
• Performance Dashboard—The Performance Dashboard helps you determine if there’s a performance bottleneck on the connected SQL Server instance and provides diagnostic data to help you resolve the problem.
• mssql-conf is a configuration script that installs with SQL Server 2019 for Linux.

Command Line Tools for Microsoft SQL Server 2019 on Linux

• sqlcmd and Bulk Copy Protocol (BCP)—These command-line tools are natively available on Linux. sqlcmd is a command-line query utility, and BCP is a bulk import-export utility.
• mssql-scripter—This command-line tool on Linux generates T-SQL scripts for a SQL database running anywhere.
• mssql-cli—This new, interactive command-line tool written in Python offers several enhancements over sqlcmd in the Terminal environment.

With these tools and command line tools, you’re now ready to run Microsoft SQL Server 2019 on Linux. To learn more about what you can do with Microsoft SQL Server 2019, check out the free Packt guide Introducing Microsoft SQL 19. If you’re ready to jump to a fully managed cloud solution, check out the Essential Guide to Data in the Cloud.

The post Tools and commands for running SQL Server 2019 on Linux appeared first on Microsoft SQL Server Blog.

As a long-time Linux database administrator (DBA), in this post I’ll share my top five focus areas for the Microsoft data professional to become knowledgeable of as they embark on the brave new world of Linux.

1. Embrace the command line

Yes, there is a graphical user interface, (GUI) for Linux, but the command line rules in Linux. We can’t stress enough how important it is to learn how to navigate directories (cd), change permissions (chmod), and list contents (ls). Your best friend will become the -h argument to any command to get the help menu for whatever you’re attempting.

It will be essential to know how to install and update your server and applications, (apt-get, yum, and zypper) as it may be your responsibility not only to just perform this task for the database tier, but also the server if a Linux administrator isn’t already on staff in your organization.

You will also need to learn how to update your existing PowerShell scripts to work on Linux or learn how to write BASH scripts. This is commonly done from a terminal editor and one of the best is VI/VIM. Knowing how to use VI will give you credibility when working with Linux and deter too many questions when in the middle of critical work or even day-to-day tasks.

2. Understand the operating system

There are many distributions (aka flavors) of Linux, which has grown from the original release. Each distribution, although very similar, may have their own unique requirements and quirks. Understanding what distributions are best for enterprise deployments can ensure what you have deployed into the environment can withstand the demands the business puts on it. It was only two decades ago that Unix DBAs were hesitant to run relational databases on any version of Linux. In that time, every distribution has come a long way, but many distributions have a purpose. If we take a very specific flavor of Debian, called Raspbian, it was specifically designed to run the operating system for Raspberry Pis, which are single Advanced RISC Machine (ARM) processor computers. Understanding the difference in a distribution like Raspbian vs. enterprise Linux distributions like Enterprise Red Hat, Enterprise SUSE, and Ubuntu is important. Just because you can get SQL Server to run on non-supported Linux distributions such as CentOS doesn’t mean you should run the business on them. Stick to the supported versions and the user experience will be more satisfying along with new features available in regular release intervals.

3. Use the tools you already have

Just because SQL Server is running on Linux doesn’t mean that your previous robust tools for management, monitoring, and querying won’t work any longer. As a DBA, you can still use SQL Server Management Studio (SSMS) and preferably, use Azure Data Studio to connect to your Linux SQL Server 2019 databases. You can’t install Server Management Studio onto Linux, but you can create a Windows “jump box” with Server Management Studio and configure it as the primary log in point for administration tasks. Developers can still access SQL Server 2019 on Linux with Visual Studio Code with the SQL Server extension and have full use of SQL Server Data Tools (SSDT).

As this is Linux, the command line is still king. Sqlcmd is available to log in from on the Linux server to execute queries, run scripts, and perform management. Sqlcmd doesn’t change in the Linux version and executing a script is as simple as the following example, which logs in as the admin user to the database and password, then runs a script and logs it all in a simple text file:

sqlcmd -U $username -S $servername  -P $spassword -d $database -i $script.sql > $logfile

4. Build on existing knowledge with new tools

PowerShell is taking center stage on Linux with new releases on a regular basis, so keep those skills sharp and with a few changes, many of the scripts you use today can be updated to work on SQL Server 2019 on Linux. Although sqlcmd is available for your querying command line needs, there is a new tool in town called mssql-cli.

This installation is a simple, on-line command:

sudo pip install mssql-cli

If there are any issues with installation, Linux is commonly quite good about telling you what library or tool dependencies that must be installed before the installation needs to be run again for success.

Always remember, if you get stuck, help is always available to offer assistance:

mssql-cli –h

To log into a database, the following is required:

mssql-cli -U $username -S $servername -P $password -d $database

If you notice the command is very similar to the example we used for sqlcmd rest easy, your world hasn’t changed as much as you were concerned about. Once you’re logged into mssql-cli, you’ll be able to use this to access SQL Server 2019 databases inside Linux. Features like auto-complete with IntelliSense, multi-line editing, and a metadata repository makes it easier to work with the command line for those just getting up to speed with SQL Server 2019 on Linux. There are a few limitations in mssql-cli, like the ability to execute scripts or send an output aren’t present, but the same queries that worked on-premises in sqlcmd will work here. Keep in mind that this new tool is in its infancy and still maturing on a regular release schedule.

The third new tool isn’t that new but is the next powerhouse to take on the DBA since SQL Server Management Studio and is called Azure Data Studio. If you haven’t embraced this cross-platform tool specifically designed for data professionals, you’re missing out. No more 32-bit interface, this is a modern interface with IntelliSense, code control integration, and customizable dashboards.

The biggest benefit is that it can be installed on Linux with no local installation or jump box required. All three primary Linux distributions are supported, as well as a Mac OS installation. Database administration tasks that are less likely to be required with cloud implementations, such as on-premises physical management, backups, etc. still will require SQL Server Management Studio, but if you’re part of the next generation of cloud administrators that have automated much of the mundane, then Azure Data Studio is a must.

5. Consider the power of containers

With Linux and SQL Server 2019, the addition of Big Data Clusters with Kubernetes support has been added. The reasons behind using containers are vast including simplified deployment, ease of management, and quicker recovery, but then add Big Data Clusters on top of this and it becomes quite the powerhouse.

Containers with Kubernetes offers DBAs the opportunity to manage the database without the added layer of separate OS for each node, or group of nodes, that is also called a pod. Less resources have to be allocated per database and without the additional layer, it can recover quicker.

Using Kubernetes, or similar sorted container orchestration, Big Data Clusters can be deployed, then manage a cluster of SQL Server, Apache Spark™, and the Hadoop Distributed File System (HDFS). Polybase is then used to virtualize data from various data sources with SQL Server, while an HDFS data pool can be used as a data lake for all big data using Apache Spark™. The SQL Server master instance simplifies the management and includes read, write, and processing of all data, no matter the source and is an exciting new frontier in the SQL Server 2019 release for Linux.

The list above includes just a few of the significant opportunities to learn and build your skills in Linux with SQL Server 2019. To help you on the way to mastering SQL Server 2019 on Linux, a number of resources have been made available through Microsoft, including this incredible new Packt e-book Introducing Microsoft SQL Server 2019. Don’t miss out on the next generation of SQL Server. Learn all the things and let Microsoft help you get there. If you’re ready to jump to a fully managed cloud solution, check out the Essential Guide to Data in the Cloud.

The post What’s new with SQL Server 2019 Linux features appeared first on Microsoft SQL Server Blog.

SQL Server 2016 introduced support for non-volatile DIMMs and an optimization called Tail of the Log Caching on NVDIMM. These leveraged Windows Server direct access to a persistent memory device in DAX mode to reduce the number of operations needed to harden a log buffer to persistent storage.

SQL Server 2019 extends the support for PMEM devices to Linux, providing full enlightenment of data and transaction logs placed on PMEM. Enlightenment is a way to access the storage device using efficient user-space memcpy() operations. Rather than going through the file system and storage stack, SQL Server leverages DAX support on Linux to place data directly into the device. This helps to reduce latency.

Enable enlightenment of database files

The first step to enabling enlightenment of database files in SQL Server on Linux is to configure the devices. In Linux, use the ndctl utility to configure PMEM device and create a namespace.

ndctl create-namespace -f -e namespace0.0 –mode=fsdax* –map=mem

You can verify the namespace using ndctl, as shown by this sample output:

ndctl list

[

  {

    “dev”:”namespace0.0″,

    “mode”:”memory”,

    “size”:1099511627776,

    “blockdev”:”pmem0″,

    “numa_node”:0

  }

]

Next, create and mount PMEM device.

With XFS:

mkfs.xfs -f /dev/pmem0

mount -o dax,noatime /dev/pmem0 /mnt/dax

xfs_io -c “extsize 2m” /mnt/dax

With EXT4:

mkfs.ext4 -b 4096 -E stride=512 -F /dev/pmem0

mount -o dax,noatime /dev/pmem0 /mnt/dax

Once your device has been configured, formatted, and mounted, you can place database files in it or create a new database.

Note that PMEM devices are O_DIRECT safe. Therefore, you must enable trace flag (TF) 3979 using mssql-conf utility to disable the forced flush mechanism. This is a server-wide configuration change, so don’t use this trace flag if you have any O_DIRECT non-compliant devices that require forced flush to ensure data integrity.

Now that you know how to use PMEM on SQL Server 2019 on Linux, you can reduce latency considerably. To learn more about what you can do with Microsoft SQL 19, check out the free Packt guide Introducing Microsoft SQL 19. If you’re ready to jump to a fully managed cloud solution, check out the Essential Guide to Data in the Cloud.

The post How to configure persistent memory for SQL Server on Linux 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.

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

Note: If you are currently using the preview version, SQL Operations Studio, and would like to retain your settings when upgrading to the latest version, please follow these instructions. After downloading Azure Data Studio, click “Yes” to enable preview features so that you can use extensions.

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

Azure Data Studio was announced Generally Available at Microsoft Ignite 2018. If you missed it, you can view that GA announcement here. You won’t want to miss the great orthogonality matrix that compares SQL Server Management Studio (SSMS) and Azure Data Studio, and it may provide answers to many of your questions.

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

The key highlights for the January release include:

• Azure Active Directory Authentication support
• Announcing Data-Tier Application Wizard support
• Announcing IDERA SQL DM Performance Insights (Preview)
• Updates to the SQL Server 2019 Preview extension
• SQL Server Profiler improvements
• Preview: results streaming for large queries
• User setup installation support
• Bug fixes

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

Azure Active Directory Authentication support

When Azure Data Studio was announced for Public Preview in November 2017, we received many requests and upvotes for features users wanted to see in Azure Data Studio. What turned out to be the third most upvoted feature request was Azure Active Directory (AAD) support, and we are excited to announce that AAD is now supported in Azure Data Studio.

Thanks to feedback from our user community from testing our insider builds, we have made it easier for you to connect with AAD. To see AAD in action, view the gif below.

For Azure SQL Database and SQL Data Warehouse users who require AAD, you can now get started with using Azure Data Studio. We challenge you to make a connection with AAD and try our Quickstarts for Azure SQL Database and Azure SQL Data Warehouse. If there are feature gaps, you can file an issue on our Issues page.

With the release of AAD, we made it a priority to release this feature as soon as dependencies in .NET Core were available. Thank you to our user community for helping us prioritize this feature through Twitter, GitHub, sqlcommunity Slack channel, and at conferences for this feature.

A more immediate way to let us know the importance of your feature request is to look for an existing issue on this list and encourage your coworkers, colleagues, and friends on social media to give a thumbs up on the issue, or blog about it. Even better—comment about what scenarios this feature would help you with. If a feature request doesn’t already exist, feel free to make it yourself. We want you to be part of the journey for this product.

Announcing Data-Tier Application Wizard support

Speaking of top customer feature requests, we have also added preview support for the Data-Tier Application Wizard used in SSMS, which is currently among the top 10 most requested features.

These wizards make it easier for users to manage .dacpac and .bacpac files, which simplifies the development, deployment, and management of data-tier elements that support an application. To learn more about using Data-tier applications, you can read this documentation.

The current experience combines the four separate wizards from SSMS into one wizard where users can choose which action they need to take. These actions include:

• Deploying .dacpac to SQL Server instance
• Extracting SQL Server instance to a .dacpac
• Creating a database from .bacpac
• Exporting schema and data to .bacpac

You can see Extract in action in the gif below.

In order to use this wizard, you will need to install the SQL Server Import extension. This is included with the Import Flat File Wizard, which is also a preview experience. This extension gives you the top two most used wizards from SSMS.

After downloading the extension, you can access the wizard in two ways:

• Right click a database in the Object Explorer and click Data-tier Application wizard
• If you are connected to a SQL Server instance, you can also start the wizard from the command palette (Ctrl+Shift+P) by searching for Data-tier Application wizard

As we did for SQL Server Agent and Import, we are actively seeking community feedback on improving this experience.

If you are interested in having a chat about this experience to help out your team, feel free to reach out on GitHub or email yu.alan@microsoft.com.

Introducing the IDERA extension SQL DM Performance Insights (Preview) extension

As part of the roadmap for Azure Data Studio, we’re finding ways to empower our partners to build extensions with our extensibility model. To show the progress we’re making on our extensibility, we are excited to announce the new IDERA extension.

IDERA SQL DM Performance Insights is an Azure Data Studio (ADS) extension that will display key performance metrics for SQL Server that have been collected and stored in a database repository by IDERA’s SQL Diagnostic Manager, a powerful performance monitoring, alerting, and diagnostics solution for SQL Server that proactively notifies administrators to health, performance, or availability problems via a desktop console, web console add-on, or mobile device.

This extension is a free add-in for Azure Data Studio that quickly allows you to see high level aggregated performance issues across multiple servers monitored by SQL Diagnostic Manager.

This preview provides the following features:

• Servers: Display metrics such as CPU, Disk I/O, network, and memory used
• Sessions: Display session summary averages such as client connections, logins, user processes, transactions, and oldest open transactions
• Databases: Display database summary-related statistics such as reads, writes, transactions, and log activity at the server level, as well as database statistical averages at the database level
• Queries: Display wait statistics and summaries of query statistics

This extension is free and will be supported through the IDERA Community Free Tools.

With partners joining our marketplace, if you are also interested in joining our extension marketplace or are curious about how to get started, feel free to contact me at yu.alan@microsoft.com to get started.

SQL Server 2019 Preview extension

Since announcing the Public Preview of SQL Server 2019 at Ignite, we are continuing to make updates to the SQL Server 2019 extension in Azure Data Studio, including improvements in the Azure Data Studio Notebooks and the Create External Data Wizard.

Azure Data Studio Notebooks

• The Notebook view UI has moved into Azure Data Studio core. This improves performance when loading and updating a notebook, including Jupyter notebook compatibility.
• The PROSE python package has been updated to 1.2.1 and includes a number of bug fixes. Use the Reinstall Notebook Dependencies command in the command palette to update this package.
• Cells have a simpler look with just one Run Cell button on the action bar, a real context menu with additional actions on the right of the cell. You can also double-click to edit support on Markdown cells.
• A Notebook API is in progress. This is included in the proposed API file for Azure Data Studio. As this feature is in active development, we expect some APIs to change, but these can be used to perform basic operations on a Notebook – Open New/Existing Notebook, query contents, add cells, etc.

Create External Data Wizard

• A new wizard to create external data sources from HDFS files/folders in SQL Server 2019 big data clusters has been added.
• Support for mapping SQL Server and Oracle Views has been added to the existing Create External Data wizard.
• Performance and reliability fixes for the existing wizard were also added.

Follow these instructions to download the extension and view the release notes.

SQL Server Profiler Improvements

We know the user community has been asking for a better Profiler experience. While working with Julie Lerman on her MSDN magazine article, we decided to make some investments to improve the Profiler experience.

To access SQL Server Profiler, you will need to download the extension through the Extensions marketplace. You can then right-click your target server and click Launch Profiler.

In this release, we fixed 11 bugs which include:

• Consistent Text/Details pane to pop up
• Fixed long time for profiler to refresh events
• Fixed Profiler for Azure SQL instances

We also added UI updates to the toolbar to make each action or dropdown clearer that the user can change. Please let us know any feedback you have.

In addition, we worked on improving the first-time experience to make it easier to launch Profiler. When you right-click a server and click Profiler, there is a default session name provided for each session template.

When you click start, Profiler will immediately start running. If you have any issues running Profiler, please let us know on GitHub.

Preview: Results streaming for large queries

In previous versions of Azure Data Studio, when a user ran large queries, no results would appear in the results grid until the query could show all of the results. This was not a great experience for our users, thus we did some investigating to improve this experience. In the latest build of Azure Data Studio, users can now see results streamed in the results grid. This makes it a better experience since users can see the results quicker and interact with their data instead of being in a waiting state.

However, this is still a preview feature so it is disabled by default. To enable this feature when you are running large queries, you will need to go to File > Preferences > Settings, and Search Settings for “sql.results.streaming” and set this value to true. Then you will need to restart Azure Data Studio.

Please let us know of any feedback you have for this experience, we will continue to be making investments in improving Query Editor and the results grid in the upcoming months.

User Setup installation support

Now featured on the Azure Data Studio download page is user setup installation for Windows only. This downloads the whole executable for Azure Data Studio so you won’t need admin privileges to install Azure Data Studio since the location will be under Local AppData folder. This also makes it easier to get monthly auto-updates.

We encourage users to download User Setup going forward. If you are transitioning from System Setup to User Setup, you will not lose any of your settings or extensions in the process.

Bug fixes and thank yous

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 monitoring the untriaged issues and assigning issues into different monthly milestones so that you will know we are working on it. Your votes on issues helps us prioritize.

To see the full list of bug fixes in the January release, view them here.

We also want to take this time to thank many of our users who have submitted PR’s or taken time to give us feedback in calls.

• Tarig0 for Add Routine_Type to CreateStoredProc fixes #3257 (#3286)
• oltruong for typo fix #3025′
• Thomas-S-B for Removed unnecessary IErrorDetectionStrategy #749
• Thomas-S-B for Simplified code #750
• Julie Lerman for feedback on Profiler and Import Wizard extensions, as well as writing MSDN magazine article on Azure Data Studio
• Dave Dustin for testing AAD, user setup, Data-Tier Application Wizard
• Peter Schott for feedback on AAD and Data-Tier Application Wizard
• Josh Booker for feedback on Data-Tier Application Wizard
• Constantine Kokkinos for feedback on Profiler
• Warwick Rudd for feedback on SQL Agent
• Mario Ezra for testing user setup

Contact us

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

The post The January release of Azure Data Studio appeared first on Microsoft SQL Server Blog.