Recent Updates to SqlPackage

In recent updates for SqlPackage, 18.7 (March 2021) and 18.7.1 (June 2021), functionality was added specific to big data in Azure Synapse Analytics, options were added to improve import performance in some scenarios, and changes were made to make the troubleshooting and general use of SqlPackage easier.

With version 18.7 and above of SqlPackage, Azure Synapse Analytics gains improved portability through the Parquet flat file format. Through additional command line properties on the Extract operation (see documentation) data is written to flat files in Azure Blob Storage while the schema is stored in a Dacpac file. Flat files storing data can be published to a database along with a Dacpac through the Publish operation with similar command line properties. Both Extract and Publish operations have large performance improvements when writing to Azure Blob Storage for large database sizes.

In some SqlPackage import scenarios indexes have a large impact on the performance, whether it is an index assisting with data insertion speed or rebuilding indexes hampering other operations. To allow further tuning SqlPackage’s behavior in these cases, the command line properties “RebuildIndexesOfflineForDataPhase” and “DisableIndexesForDataPhase” were added to the Import action in SqlPackage 18.7.

If you are looking to better understand the SqlPackage operations for troubleshooting or performance improvements, there are some essential steps to capture more information. Enabling a diagnostics file with the parameter “/DiagnosticsFile:<filename>” is fundamental and in the latest SqlPackage release the version and architecture were standardized at the top of the file to assist in troubleshooting version differences. Trace data including timestamps for performance can be included by setting an environment variable “DACFX_PERF_TRACE” to true. To support environments where object names may contain Personally-identifiable information (PII) or otherwise confidential information, SqlPackage 18.7 added a property for all operations that hashes the object names in logs (“HashObjectNamesInLogs”).

DacFx v160 Preview

The DacFx v160 preview release is now available as a NuGet package for use in testing or updating existing applications to Microsoft.Data.SqlClient. External DacFx APIs are not altered by this update, however the dependency on System.Data.SqlClient is removed. An example application leveraging the v160 release of DacFx is available. Existing applications will continue to use the generally available v150 NuGet package unless a major version is not specified and preview releases are included in the package dependencies.

As a component of several SQL tools, including Azure Data Studio, SQL Server Management Studio, and SQL Server Data Tools in Visual Studio, DacFx v160 will be incorporated in those tools to bring functionality forward over the coming months.

DacFx GitHub Repository

Since the DacFx library is surfaced through multiple products, it can be difficult to locate information and provide feedback specific to DacFx and the SqlPackage CLI. To reduce barriers to collaboration, a repository is being opened with a focus on transparently triaging and addressing feedback. You are welcome to open a discussion around a particular use case and we have begun to gather previously reported items from other feedback channels. The code in the repository will comprise of current samples and will be updated based on questions and feedback. The DacFx team looks forward to an environment with a focus on the community’s use of DacFx and building more resources.

The post Recent updates for Data-Tier Application Framework and SqlPackage appeared first on Microsoft SQL Server Blog.

Azure Data Studio v1.25

Azure Data Studio follows a four by two development model, where the development cycle includes a two month effort for fundamentals between a four month period focused on improvements and feature creation. In looking at this month’s release notes, you will see that limited features were added but over 30 bugs were fixed. Taking a period to focus engineering work on fundamentals helps keep the codebase healthy and ensures we dedicate attention to addressing imperceivable functionality and improving engineering processes.

SQL Database Projects extension in Azure Data Studio

The 1.25 release of Azure Data Studio is accompanied by an update to the SQL Database Projects extension, which introduces workspaces and an improved viewlet focused on projects in the sidebar. A workspace is a logical collection of projects and can contain a single project or multiple projects. The new projects viewlet displays any projects in the current workspace and the associated objects and actions, including pre or post deployment scripts and database references. Containing projects in workspaces facilitates quickly reopening the same set of projects

Azure Data Studio included with SQL Server Management Studio

As highlighted in a previous blog post, starting with SQL Server Management Studio 18.7, Azure Data Studio is now included in the SSMS installer. This change brings new features to SSMS, now and increasingly in the future. Since this release, we have had the opportunity to discuss some of the unique challenges that users face in deploying SQL tools. Today we’d like to share more information about the roadmap and current installation processes for SSMS and Azure Data Studio. Before we discuss these details, we’d like to thank those who stepped forward to engage in discussions about the specifics of their environments.

The roadmap for graphical SQL tools can be scoped in two phases, continued version 18 releases and a future major version of SSMS. For the remainder of v18, skipping the installation of Azure Data Studio can be accomplished via a command line flag. A future version of SSMS will require dependencies provided by Azure Data Studio but this change will be communicated ahead of the release.  At this time, installation without Azure Data Studio can be accomplished through the command line flag “DoNotInstallAzureDataStudio=1”.  An example use of this would be:

SSMS-Setup-ENU.exe /Passive DoNotInstallAzureDataStudio=1

A common set of questions about the combined installation has led to additional information in our installation documentation, but the most frequent question was “Can Azure Data Studio be updated separately?” The SQL Server Management Studio installation will not overwrite an equivalent or newer version of Azure Data Studio, allowing independent Azure Data Studio updates if desired.

SQL Server Management Studio (SSMS) v18.8

Rounding out the year with a fourth release of SSMS, this release includes a handful of fixes and new functionality added for auditing in Azure SQL Managed Instance (MI). For more about the EXTERNAL_MONITOR keyword, review the documentation.

The introduction of filtering of Extended Events by the wait_type name introduced a defect and has been temporarily removed while the defect is corrected. Details on the other changes can be found in the release notes, including fixes for a long-standing issue with XML column reordering and the SQL Replication Monitor auto-connect capability.

mssql extension v1.10.0 for Visual Studio Code

The v1.10.0 release of the mssql extension for Visual Studio Code brings Azure Active Directory (AAD) authentication support, improvements to syntax colorization, and some bug fixes.

2020 recap

The Azure data platform user base is growing across their choice of deployment options, platforms, and current skillsets. During the past year, we have seen a progression of SQL Server tools to support new workflows and experiences, including Azure Arc, Azure SQL Edge, and SQL Server on Linux. Of the flagship graphical tools, this included four version releases of SSMS and monthly releases of Azure Data Studio. We look forward to continuing to bring new capabilities to you in 2021.

The post SQL Tools December release recap 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.

The 18.7 release is the third major release of SSMS in 2020 and expands on our commitment to providing quality tooling for database administration. Key changes include:

• Fix for Database diagrams add table dialog.
• Installation of Azure Data Studio.
• Extended Events script with wait type name.

Installation with Azure Data Studio

SQL Server Management Studio is a foundational tool for many working with Microsoft data solutions. First released in 2018, Azure Data Studio is a cross-platform and open source desktop environment for data professionals using the Azure Data family of on-premises and cloud data platform solutions. Architecturally, SQL Server Management Studio has long been combined with additional tools, including Profiler, Database Engine Tuning Advisor (DTA), and Database Mail. As Azure Data Studio continues to mature, the Microsoft data tools experience on Windows has become a combination of SSMS and Azure Data Studio. Beginning in the 18.7 release of SQL Server Management Studio, Azure Data Studio is installed alongside SSMS automatically.  Users of SQL Server Management Studio will be able to benefit from the innovations in Azure Data Studio, whether their environments are cloud, on-premises, or hybrid.

To learn more about Azure Data Studio, please check out the Azure Data Studio documentation or the FAQ.

Extended Events

Extended Events is a lightweight performance monitoring system that enables users to collect data needed to monitor and troubleshoot problems in SQL Server.  One of the ways to configure an extended events session is through the “New Session Wizard” in SQL Server Management Studio.

One of the events that can be monitored by Extended Events is server waits. With each new SQL engine version, additional wait types are introduced. This occasionally changes the wait_type_id for a named wait type. The latest version of SQL Server Management Studio adds the capability to script out Extended Events where wait types are filtered by the wait type name instead of the wait_type_id.  Extended events sessions scripted by the wait type name are robust against future changes to the wait_type_id.

Look for this new option in the “New Session” dialog under “Extended Events” in object explorer. For more information on Extended Events in SQL Server Management Studio, please check out the Extended Events documentation.

Policy-based management

Policy-based management is a feature for managing one or more instances of SQL Server through policies. These policies are user-created conditions applied to database objects. For example, you may want to ensure the AUTO_SHRINK database option is off on selected databases, so you create a condition that checks the AUTO_SHRINK option. Policies are evaluated on demand, on change, or on schedule, providing flexibility in the application from manual to automated.

Policy-based management is accessed in SQL Server Management Studio under “Management” in the object explorer as “Policy Management”. Getting started with policy-based management can be accelerated by importing the sample policies available for SQL Server. In September, these policies were added to the open source collection of SQL Server samples to facilitate their use and improvement. You can access these sample policies on the GitHub repository and your contributions to these best practices are welcome. For more information on Policy-Based Management, please check out the documentation.

Looking forward

To users who have submitted items, commented, or voted at User Voice for SQL Server—thank you.  Work items for SSMS 18.7 were heavily influenced by user feedback. Regular updates to SQL Server Management Studio will continue to be released on an approximately quarterly cadence and your feedback is an integral part of our planning. Submit feedback anytime on User Voice.

The post SQL Server Management Studio 18.7 now generally available appeared first on Microsoft SQL Server Blog.