Backup and restore to URL is now available in Azure Data Studio for Managed Instances

Support for backup to URL in Azure Data Studio with Azure Blob Storage as a backup destination was added for Managed Instance in this release. When connected to a Managed Instance, you now have the option backup to URL:

Table Designer updates

We are excited to announce the SQL Projects support for the Table Designer in Azure Data Studio. With SQL Projects, changes or updates to database schema can be deployed without the need to be connected to a server instance. Tables, views, stored procedures, and scripts can all be added and edited in offline mode to allow for easier serverless database development and administration. Now with support for Table Designer in the SQL Projects extension, you can create tables for SQL Projects with the same graphical editor available from object explorer connections to live databases. To learn more about what can be done with this extension in Azure Data Studio, check out the SQL Database Projects extension documentation and Getting Started with SQL Database Projects. Please note that SQL Projects functionality requires that the extension be installed.

Table Designer now supports computed columns and column reordering

Based on user requests, our team worked to add APIs to support computed columns in Table Designer. With computed columns, data can be used from other columns to calculate a value for the column to which it belongs. These columns are not physically stored on the disk unless the column is marked PERSISTED. Computed column expressions can be specified in the Column Properties pane and these expressions are reflected in the Graphical User Interface (GUI) as well as in the Scripts Pane.  

Table Designer now provides the option to add columns in between existing columns, rather than at the end of the table by default. See below for this in action.

In addition, columns can now be re-arranged by dragging to user specification. See below for column dragging in action.

Query Plan Viewer

In this release of Azure Data Studio, we’ve introduced plan comparison, which allows you to view two plans side by side to help identify differences. Within a plan, right-click and select Compare Execution Plan from the pop-up menu, or select the menu button. 

The plan will open in a new window where you can add another plan to compare against the original. Beyond visual distinction, you can open the properties window via the top menu to drill into different operators and further understand differences.

This release also includes minor changes related to the display of object names in plans, the display information in the properties window, the addition of decimal places to operator costs for large plans, and the option to display the estimated plan, or include the actual plan, via the toolbar:

SQL Bindings

Support for Azure SQL Bindings for Azure Functions is now available in preview for C#, JavaScript, and Python. In a recent update to the mssql extension for VS Code we introduced the ability to quickly create Azure Functions with integration to your database tables from the object explorer view. Both input and output bindings are available for you to use in connecting your database to Azure Functions for both querying and inserting data. Get started with C# bindings through the mssql extension integration today and learn more about SQL bindings for Azure Functions in the documentation.

Extension updates

We are excited to introduce an update to the PowerShell extension, bringing an improved user interface and performance. Read more about this extension update on the PowerShell team blog.

In the June release, you will see several extensions receiving updates. Many of these updates come from quality improvements based on your feedback at our GitHub repository. Extensions receiving fixes include Schema Compare and SQL Projects.

SSMS 18.12 release

Last week we released SSMS 18.12, which contains updates to the Link feature for Azure SQL Managed Instance and improvements to the Data Classification interface, as well as multiple fixes. SSMS 18.12 also includes support for compatibility mode 160, which is available in Azure SQL DB and SQL Server 2022. Note that SSMS 19 is the recommended SSMS release for SQL Server 2022. Download SSMS preview 2.

Summary

We are excited to continue to improve on capabilities on Azure Data Studio. We are aware of current limitations and are working on iterative improvements. If you would like to help improve Azure Data Studio, please share any feedback or report issues through our Azure Data Studio Issues page. Our engineering team regularly reviews issues and assigns them to different monthly milestones. Your votes on issues help us prioritize what to work on next.

The post What’s new with SQL Tools appeared first on Microsoft SQL Server Blog.

SQL Server Management Studio

First, in alignment with the announcement of preview for SQL Server 2022, we released SQL Server Management Studio (SSMS) 19 Preview 2. 

SSMS 19 is the recommended version of SSMS for SQL Server 2022. The release notes for SSMS document current limitations and we will have additional previews in upcoming months with expanded capabilities.

Developer Tools

Second, we continue to support developers working with databases via the latest version of the SQL Database Projects extension in Azure Data Studio. We now have preview support for SDK-style SQL projects which benefit developers who work in cross-platform environments or ship applications through CI/CD platforms. In addition, the SQL Database Projects extension provides a local development environment for Azure SQL Database through an emulator. The emulator provides a containerized database to allow for testing and development in an offline and independent environment, thereby reducing resource requirements and increasing developer autonomy. We are working to expand the capabilities of the Azure SQL Database emulator in future releases.

Further enhancements in the development space include the integration of SQL bindings with the mssql extension in VS Code. This enables users to develop Azure Functions with Azure SQL bindings and while the SQL bindings extension currently only integrates with C# Azure Functions, support for JavaScript, TypeScript, and Python have been added to the preview for SQL bindings as well.

We also have a set of driver, library, and command-line interface (CLI) updates to note:

• SqlServer PowerShell module released 22.0 preview.
• SqlPackage/DacFx update to 19.1.
• SQL Management Objects is now open source.
• Microsoft fork of go-mssqldb driver for golang.
• Lightweight CLI go-sqlcmd in preview.

Internally we continue to explore additional open-source opportunities and remain committed to expanding scripting and library support. Across new and established landscapes we continue our commitment to empowering developers with open source projects. In the go ecosystem, we look forward to expanding the capabilities of the go-mssqldb driver and enabling new CLI scenarios with go-sqlcmd.

Azure Data Studio

Lastly, we released Azure Data Studio 1.36.2 immediately prior to Microsoft Build, and it contains key improvements driven by our partners. Azure Data Studio now supports connectivity to Power BI Datamarts, for which the preview was just announced. Power BI Datamarts are a self-service analytics solution that allows individuals with little to no code experience to quickly and easily bring their data into a fully managed database. Azure Data Studio (as well as SSMS) provides users the ability to explore the objects and the data to get started with initial analysis and query writing.

We introduced .NET Interactive Notebooks in Azure Data Studio through the .NET Interactive Notebooks extension. With multi-language support for Jupyter Notebooks you can now code in T-SQL, PowerShell, C#, JavaScript, and more. 

This .NET Notebooks in Visual Studio Code blog provides an example to get started. And finally, you can now access Mongo resources in Azure Cosmos DB using the MongoDB Extension for Azure Cosmos DB (currently in preview).

What’s next for SQL Server client tools

Though we are almost halfway through the calendar year, we expect to deliver even more improvements and new functionality in the latter half of 2022. We have several exciting tools in preview (SSMS 19 and SqlServer PowerShell module 22.0) that we look forward to advancing towards general availability, along with new versions of Azure Data Studio and VS Code SQL extensions. We appreciate your support and feedback as we continue to ship releases across a breadth of new and established experiences for SQL Server. Please continue to submit feedback for Azure Data Studio and SQL Server Management Studio to support the evolution of client tools.

The post Recapping recent updates to client tools in SQL Server appeared first on Microsoft SQL Server Blog.

SQL Server Management Studio 18.11

SSMS 18.11 adds support for creating ledger tables, viewing the history table, and scripting support for ledger objects in Azure SQL Database, as well as the capability to review trace files from Analysis Services (version 16.0) in Profiler. This version also includes the fix to use CTRL+mousewheel to zoom in query plans, and the ability to edit and create SQL Server Agent jobs in Managed Instance. Additional improvements related to accessibility and underlying code were implemented, as well as expanded support for special characters in connection names, and minor UI fixes.  

Azure Data Studio 1.35

We are excited to announce the release of Azure Data Studio 1.35. This release is focused on bringing new innovations to the core SQL Server data toolset with a focus on data scheming using a modern table designer and query performance tuning.

Table Designer (preview) in Azure Data Studio

Inspired by SQL Server Data Tools (SSDT), the Table Designer provides the ability to manage table metadata, view detailed properties, and preview generated SQL DDL. Supported table metadata includes columns, foreign keys, indexes, and check constraints definitions. The new Table Designer preview feature supports creating new tables and editing existing tables on a connected SQL Server instance. This is a highly requested product enhancement and enables more productive schema management with a modern, streamlined UX. This feature is built on the DacFx framework, which is the same mature library that is used in the SSDT Table Designer.

Query Plan Viewer (preview) in Azure Data Studio

We are excited to introduce the Query Plan Viewer for Azure Data Studio. 

Both estimated and actual query plans can be displayed, and no extension is required. The initial release of the plan viewer includes components familiar to users who analyze plans, as well as new functionality including Top Operations and the ability to open the query in a new window, directly from the plan.

The plan viewer allows users to understand object access and join type for a query, view operator properties to identify what consumes the most time or CPU, as well as save plans for additional analysis.  Future improvements will be added; look for opportunities to provide feedback about what you would like to see next!

Notebooks in Azure Data Studio

Azure Data Studio 1.35 now supports easier keyboard navigation in notebooks without mouse clicking. This is done by hitting the Esc key and navigating between cell rows using the Up and Down arrow keys. To enter edit mode, hit the Enter key on the keyboard.

Azure SQL Migration extension update

We made some major updates in the Azure SQL Migration extension. These include provision of recommendations for right-sized Azure SQL Managed Instance SKUs or SQL Server on Azure Virtual Machines SKUs based on performance characteristics of your on-premises SQL Server as well as an ability to provide performance database assessments and recommendations without the need for an Azure Account. To learn more, read the Azure SQL Migration extension article on Tech Community.

DacFx 160 and SqlPackage 19 general availability

As mentioned in a prior post on the Data-Tier Application Framework (DacFx), DacFx 160 is the latest iteration of the .NET library for database development and CI/CD operations for Microsoft SQL Server and Azure SQL. DacFx supports declarative development with the capability to apply database changes based on the current state of a database and the contents of a dacpac or SQL project. In addition to being available as a .NET package on NuGet as well as in the SqlPackage CLI, DacFx is available in both Azure DevOps pipelines and GitHub actions. Using DacFx on these automation platforms can be achieved through published tasks or actions and by directly invoking SqlPackage in the workflow. With each DacFx release, the provided virtual environments are automatically updated ensuring that your SQL deployment workflows receive the latest version.

In January 2022, DacFx 160 became generally available. With this version of DacFx, the dependency on System.Data.SqlClient is replaced with Microsoft.Data.SqlClient. Additionally, support is added for .NET 6 and column-level symmetric encryption in Azure Synapse Analytics. Information on these and other changes is available in the SqlPackage release notes, and users can download the latest SqlPackage and join the conversations on DacFx at the GitHub repository.

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 Introducing new and improved updates to SQL tools on Azure 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.

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.

The 18.6 release is the second major release of SSMS in 2020 and packs several high impact changes, including a fix for crashes in database diagrams. Key fixes include:  

• Save to XEL file error fix. 
• Bacpac file import error fix. 
• Database diagrams crash fix. 
• Addressed sources of three common application hangs. 

Database diagrams 

Database Designer is a visual tool in SQL Server Management Studio that allows you to design and visualize a connected database. This tool is helpful in both creating tables as well as describing existing structures. For creating tables, database diagrams facilitate specifying column names and data types as well as visually linking tables with foreign keys and other relationships. When using database diagrams for describing existing databases, there are additional tools to assist. In addition to being able to label relationships and display differing levels of details about each table, general text annotations can be leveraged for additional commentary. 

If you created or saved a diagram using SSMS 18.0 through 18.5.1, and that diagram includes a Text Annotation, you won’t be able to open that diagram in any version of SSMS. With this fix, SSMS 18.6 can open and save a diagram created by SSMS 17.9.1 and prior as well as by other SSMS 18.6 instances. SSMS 17.9.1 and prior versions will also be able to open the diagram after being saved by SSMS 18.6. 

For more information on Database Diagrams in SQL Server Management Studio, please check out the documentation. 

Integration with Azure Data Studio 

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. SQL Server Management Studio and Azure Data Studio are complementary tools on a Windows desktop, especially for a database administrator’s workload. In recent releases, several points of integration between Azure Data Studio and SSMS have been introduced. 

• SQL Server Management Studio experiences. 
• New query and new notebook in Azure Data Studio from database and server nodes in object explorer. 
• Generate Scripts wizard output option for a notebook. 
• Azure Data Studio experiences. 
• SSMS properties dialog. 
• SSMS generate scripts wizard. 

To enable the Azure Data Studio experiences listed above, install the “Database Administration Tool Extensions for Windows” from the extension marketplace in Azure Data Studio. Note: this functionality only works on Windows. 

Import Flat File Wizard 

The flat file import wizard, powered by PROSE (Program Synthesis using Examples), is a powerful tool included in SSMS that streamlines importing data by inferring column names and data types. In this update, import from fixed-width format files and automatic detection of file format is now supported as well as improved handling for escaped quotes. You can find the Import Flat File wizard in object explorer at the database node level under tasks. For more information on the import flat file wizard, please check out the documentation. 

Looking forward 

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

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

You can download SQL Server Management Studio 18.5  today and review SSMS Release Notes for full details.

A glimpse into some of the interesting changes for this release include, but are not limited to:

New features in SQL Server Management Studio

General SSMS

• SMO / Scripting
  • Added “Notebook” as a destination for Generate Scripts wizard.
  • SQL Assessment API – Platform, Name, and Engine Edition fields can now contain usual comma-separated lists (“platform”: [“Windows”, “Linux”]), not only regular expressions (“platform”: “/Windows|Linux/”).

• Data Classification
  • Added support for sensitivity rank in Data Classification.
• Hyperscale
  • Added support for “Import Data-Tier Application” (.bacpac) to SQL Azure HyperScale.
  • This change adds missing Azure Editions to our Dac wizards, particularly support for Hyperscale.

IS

• Support executing SSIS Package from file system in MI Agent Job.
• Made user-friendly improvements in configuring Azure-enabled DTExec to invoke SSIS package executions on Azure-SSIS Integration Runtime.
• Support connecting Azure-SSIS integration runtime and managing or executing SSIS packages in package stores.
• Support migrating on-premise SSIS agent jobs to ADF pipelines and triggers.
• Made an improvement for the user experience of exporting SSIS projects from SSISDB. Compared with the old Export, which loaded and upgraded packages in the SSIS project, the new version independent Export won’t load and upgrade packages in the SSIS project. Instead, it keeps packages in the projects as they are in SSISDB except changing protection level to EncryptSensitiveWithUserKey.

AS

• Added support for PowerBI endpoint in AS,  matching functionality of asazure.
• Profiler: added support for AS Trace Definition 15.1.

Bug fixes

We also have several bug fixes in various areas of general shell improvements, DPI and scaling, accessibility, and more.

General SSMS

• Always On
  • Fixed an issue where the first database added to Always On AG does not join correctly.
  • Fixed an issue where an error was displayed when trying to display the dashboard when connected to a Big Data Cluster endpoint.

• Object Explorer:
  • Fixed an issue where OE was very slow in expanding tables for databases with several thousands of tables (over 20,000).

• SQL Agent
  • Fixed an issue where trying to edit a SQL Agent job step could have resulted in the SSMS UI freezing. See more here.

Note that SSMS is now allowing viewing (View button) an output_file whose name is tokenized (at least for the simple macros/tokens supported by SQL Agent that are not determined at runtime). Additionally, SSMS is not disabling the View button when the user does not have access to the file (as far as SQL permissions go).

• Fixed the tab ordering on the Job Step page.
• Reversed the position of the Next and Previous buttons on the Job Step page to put them in a logical order.
• Adjusted the Job Schedule window to not clip the UI.
• General SSMS:
  • Addressed at least two common sources hangs and crashes in SSMS.
  • Addressed one more case where “Restore dialog” hangs when clicking Browse.
  • Fixed “New Database GUI” for SQL On Demand.
  • Fixed “New External Table…” and “New External Data Source…” templates for SQL On Demand.
  • Fixed database properties, connection properties, hiding reports and rename for SQL On Demand.
  • Cleaned up the “Database Property Options” grid which was showing two “Miscellaneous Categories.”
  • Fixed an issue where the scroll bar started from the middle in “Database Properties Options” grid.
  • Fixed an issue that was causing SSMS to crash when opening .sql file while connected to Analysis Services server.
  • Connection Dialog: fixed an issue where unchecking the “Remember Password” does not work. Also, fixed an issue where credentials associated with Server or Users are always remembered.

More improvements

• Mitigated an issue where occasionally Editor windows were not properly refreshed. This is achieved by disabling the hardware acceleration in Tools | Options | Environement. See more here.
• Fixed an issue where Azure Active Directory authentication was not working through a proxy
• ShowPlan:
  • Improved how SSMS displays estimated row counts for operators with multiple executions: (1) Modified “Estimated Number of Rows” in SSMS to “Estimated Number of Rows Per Execution”; (2) Added a new property “Estimated Number of Rows for All Executions”; (3) Modify the property “Actual Number of Rows” to “Actual Number of Rows for All Executions”

• Xevent UI:
  • Fixed an issue where items in the grid were being selected on hovering. See more here.

• Replication tools:
  • Fixed an issue where Replication Wizard would throw an exception dialog (heterogeneous replication).

• Data classification:
  • Fixed an issue where the “Data Classification” wizard won’t open for databases with a large number of tables.

• Auditing:
  • Fixed an issue where the Audit logs merge window crashes when there’s a folder with an empty name in the root folder of the storage account.
  • Fixed an issue where the Audit logs merge window doesn’t show all servers when there are too many items in the root of the container.

To view the complete set of release notes, visit the Documentation page.

As always, we’re listening and would love to hear from you with any questions, comments, or feature suggestions. You can submit feedback anytime.

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

Since SQL Server 2016, the tools for SQL Server have been released independently “out of box” from the server product. This allows us to be more agile to the needs of our users, get both features and bug fixes shipped more quickly, stay aligned with the more continuous release cycle of Azure SQL, and in general allows the tools team to innovate in exciting ways. However, one side effect is that it can be difficult to understand what’s happening across the tools landscape, as things change quickly in multiple products that are releasing as frequently as every month.

The SQL Server client tools cover the full breadth and depth of the product, and can be broadly categorized as follows:

• SQL Server drivers and connectivity APIs, such as Microsoft.Data.SqlClient, ODBC, JDBC, etc.
• SQL Server tooling APIs and scripting support, including SQL Server Management Objects (SMO), DacFX, and PowerShell
• Command line tools for SQL Server like sqlcmd, bcp, mssql-cli, and sqlpackage
• Jupyter Notebooks, a new entrant in this space that bridges the CLI-to-GUI divide
• Graphical tools for SQL Server, including SQL Server Management Studio, Visual Studio, Azure Data Studio, and Visual Studio Code
• The Azure portal experiences supporting the Azure SQL family of products

Each of these areas has had important updates since the last major release of SQL Server.

SQL Server drivers

Microsoft maintains or contributes to a number of drivers for SQL Server supporting a wide variety of languages. As new features are added to Azure and to SQL Server, some require updates at the protocol and driver level, including features like UTF-8 support, Always Encrypted, and data classification. The most significant strategic update in this area has been the change from System.Data.SqlClient to Microsoft.Data.SqlClient as the premier driver for the .NET ecosystem. You can read more about this in the .NET announcement blog. The Microsoft.Data.SqlClient driver and its associated NuGet package are now generally available, and while System.Data.SqlClient will continue to be supported, the new out-of-the-box Microsoft.Data.SqlClient is the recommended assembly for .NET developers targeting SQL Server in the future.

SQL Server APIs and scripting

There is continuous work in the important SQL Server Management Objects (SMO) and DacFX libraries, which are the application interfaces used by most of the rest of the tooling stack to interact with SQL Server. With SQL Server 2017 introducing a need for cross-platform tooling, a lot of effort has been spent in the past few years in updating these assemblies so that they can be used on MacOS, Linux, and in containers. The SQL Assessment API is a new offering that allows for best practice checks to be performed with SQL Server Management Objects and with PowerShell. And the SQL Server PowerShell module continues with very regular updates, having had 17 releases in the past two years. These capabilities are widely used, and the SQL PowerShell module has been downloaded over 2.3 million times since 2017.

Command line tools

The SQL Server command line tools were some of the first and most important tools to be taken cross-platform for users of containers, Linux, and MacOS, owing to their important role in non-graphical and automation use cases. The open source mssql-cli is a new command line tool with a more user-friendly interactive querying experience compared to sqlcmd, and important work has been done in sqlpackage.exe to allow cross platform extract and build for dacpacs. This work is not only foundational to bringing more cross-platform developer experiences into the graphical tools, it has proven to bring significant performance improvements as well.

Jupyter Notebooks

The flexible and portable format of Jupyter Notebooks has been popular in the data science and big data communities for a number of years. However, with the introduction of full fidelity T-SQL support to notebooks in Azure Data Studio, this is a technology that has become relevant to a wider community of SQL Server users including developers, IT professionals, database administrators, analysts, and more. The nature of a Jupyter notebook creates a “documented CLI” experience which can be used for development, data exploration, troubleshooting, documentation, and more. Through integration with Microsoft tools like Azure Data Studio, PowerShell, and Azure Data, in combination with open source platforms like Papermill, it’s possible for notebooks to be used as both interactive and non-interactive, operationalized experiences. The SQL Server team is investing heavily into notebooks and the related Jupyter Book concept for product documentation, deployments, and more. Azure Data Studio offers a full Jupyter Notebook editor with support for SQL, Python, Apache Spark™, Scala, R, and PowerShell.

Graphical tools

The graphical tools for SQL Server are the most visible and recognizable tools in the portfolio. The flagship graphical interface for SQL Server is SQL Server Management Studio (SSMS), a tool that has been used by millions for over 15 years. Since SQL Server 2016, SQL Server Management Studio has been available as a separate download from SQL Server. In the past year, the major version 18.0 shipped, followed by a number of minor releases on an approximately bimonthly cadence. Regular updates and improvements to SQL Server Management Studio will continue, but most of the net new innovations in the graphical tooling space, such as notebook support, can be expected to ship in Azure Data Studio. As a cross-platform tool designed to support multiple database systems, Azure Data Studio operates in a slightly different space from SQL Server Management Studio. Azure Data Studio is open source and ships monthly, with major features in the last year including Jupyter Notebooks, Dacpac import and export, Schema Comparison, external table creation, data visualization in SandDance, and extensions provided by the community and commercial partners. Azure Data Studio is also investing in deployment experiences for SQL Server, which is an inversion from the days when a SQL Server CD was used to install SQL tools.

For our SQL Server developers, the SQL Project functionality in Visual Studio has been supported and maintained, but without a great deal of visible change in the last couple of years, as work has been going into the API and CLI levels. This lower level work is coming toward completion, and new experiences around development for Azure SQL Database Edge are imminent. Additionally, we have a vibrant userbase of the SQL Server (mssql) extension for Visual Studio Code, that’s very actively maintained and has recently shipped a major update.

Azure portal

In the Azure portal, a great deal of work is being done to consolidate, standardize, and simplify the process of working with the Azure SQL family of products including SQL Server on Azure Virtual Machines, Azure SQL Database, and Azure SQL Managed Instance. The past few months have brought a new unified deployment and management experience and moving forward, we intend to bring more features pertaining to management at scale and beyond.

Looking forward

The SQL Server tooling stack has never been deeper or stronger. The agile nature of tooling development combined with the active involvement of the SQL Server community creates an environment where it’s possible to iterate more quickly than ever. As SQL Server spreads across an ever widening combination of deployment options and platforms, from ground to cloud, expect to see changes in the SQL Server tools as well to support the new workflows and experiences that users need to do their best work.

The post State of the SQL Server tools appeared first on Microsoft SQL Server Blog.

You can download SQL Server Management Studio 18.4 today and review SQL Server Management Studio release notes for details.

In addition, we’re also announcing sqlpackage v18.4 is now generally available. This means cross-platform support for sqlpackage is officially generally available. Download the latest release today.

In this blog, you will learn about the new features in these two exciting releases.

SQL Server Management Studio 18.4 features

General SQL Server Management Studio

SQL Management Objects and scripting

• Supports the script of a materialized view in SQL Data Warehouse

Data classification

• Added support for a custom information protection policy for data classification

Query store

• Added the “Max plan per query” value in the dialog properties
• Added support for the new custom capture policies

Key bug fixes include:

General SQL Server Management Studio

• Object Explorer
  • Fixed an issue where dropping an Azure SQL Database in Object Explorer was showing an inaccurate error message.

• SQL Agent
  • Fixed an issue where the agent operator UI would not update the operator name when it was changed in the UI and it wouldn’t be scripted. See the feedback forum to learn more.

• General SQL Server Management Studio
  • Fixed a long outstanding issue where SQL Server Management Studio wasn’t honoring the connection information passed at the command line when SQL files were also specified.
  • Mitigated the removal of the -P command line option by having SQL Server Management Studio look at its cache of credentials. If the required credential was found, the connection would be established using it.
  • Fixed a crash in SQL Server Management Studio while trying to display securables on “Replication Filter” objects.

• Query result
  • Fixed an issue introduced in SQL Server Management Studio 18.3.1, where grids would be drawn slightly too narrow and show “…” at the end of the longest string in every column.

• SQL Management Objects and scripting
  • Fixed an issue when “Script table as…” for SQL Data Warehouse whose collation was Japanese_BIN2 wasn’t working.
  • Fixed an issue where ScriptAlter() ended up executing the statements on the server.

• Import flat file
  • Fixed an issue where “Import flat file” functionality wasn’t handling text qualifiers correctly.

• Replication tools
  • Fixed an issue that was causing the application to throw an error (“Could not load file or assembly…”) when trying to edit SQL Agent jobs

Please see the SQL Server Management Studio release notes for a comprehensive list of bug fixes.

Sqlpackage v18.4 features

New features include:

• Added support to deploy to Azure SQL Data Warehouse
• Added support for new Azure database editions including GeneralPurpose, BusinessCritical, and Hyperscale
• Added Azure SQL Database Managed Instance support for Azure Active Directory users and groups
• Supports the /AccessToken parameter for sqlpackage on .NET Core
• Removed SHA1code signing

View the complete set of release notes to learn more.

To learn more about sqlpackage, read our documentation.

As always, we’re here to listen and would love to hear from you with any questions, comments, or feature suggestions.

The post SQL Server Management Studio 18.4 and sqlpackage v18.4 generally available appeared first on Microsoft SQL Server Blog.

You can download SQL Server Management Studio 18.3 today and review the SQL Server Management Studio release notes for details.

Some of the new features in SQL Server Management Studio include:

Intellisense/Editor: Updated support for features recently added to SQL Server 2019 such as ALTER SERVER CONFIGURATION.

SQL Server Management Objects  and scripting

• Added support for support scripting of Azure SQL Data Warehouse unique constraint
• Added support for ordered clustered columnstore index in Azure SQL Data Warehouse
• Data classification:
  • • Added support for SQL Server version 10 (SQL Server 2008) and higher
    • Added new sensitivity attribute ‘rank’ for SQL Server version 15 (SQL Server 2019) and higher and Azure SQL Database

Key bug fixes include:

Please see the SQL Server Management Studio release notes for a comprehensive list of bug fixes.

As always, we’re always listening and would love to hear from you with any questions, comments, or feature suggestions via the SQL Server feedback forum.

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