Enhancing your SQL workflow

From a tooling perspective, we are investing heavily in SQL Server Management Studio (SSMS) and the MSSQL extension for Visual Studio Code. SSMS is where we primarily aim to serve data professionals like you including database administrators, data analysts, database developers, data scientists, and data engineers, and we have been doing so for two decades now. The MSSQL extension for Visual Studio Code is where we primarily aim to serve application developers. Additionally, from a web interface perspective, we are investing in the Microsoft Azure portal and Microsoft Fabric web experiences to support Azure and Fabric cross-functional roles and tasks to be done.

In the past year, we’ve modernized SSMS—now based on the latest release of Visual Studio—and brought in numerous customer requests including dark mode, Arm64 support, Fabric support, GitHub Copilot, and more. We also brought rich, AI-assisted experiences to Visual Studio Code with GitHub Copilot Ask and Agent mode support, in addition to many new and improved capabilities in areas around designing schemas and tables, provisioning and getting connected (including Fabric and local containers), query results, and more. In the web, we launched a unified Azure SQL experience in the Azure portal, and shipped SQL database in Fabric, now generally available.

Building the future of SQL development

In addition to delivering quality releases and consistent functionality across these tools and experiences that enable you to efficiently manage and develop with Microsoft SQL Server, we are aiming higher for the future. Our vision is to equip every database with source control and CI/CD integration, streamline trusted and reliable deployments, provide consistent and tailored Copilot experiences, and deliver modern drivers, SDKs, and CLIs as well as a robust data API and MCP Server. We’re also investing in rich experiences that help developers take full advantage of AI capabilities in the SQL engine, making it easier to build and optimize AI-ready applications.

Delivering on that vision requires focus and critical prioritization, a responsibility that we’re taking with deep consideration and increased transparency to you. Full roadmap details across our tools and experiences can be found at the end of this article. If you were using Azure Data Studio or SDK-style SQL projects in Visual Studio 2022 and are impacted by the retirement of these tools, you can still use the original SQL projects in Visual Studio 2026 so that your established solutions can upgrade to the latest Visual Studio version without compatibility conflicts. SDK-style (Microsoft.Build.Sql) projects are generally available in Visual Studio Code with the SQL Database Projects extension and are directly integrated with SQL database in Fabric source control. In the first half of 2026 SDK-style SQL projects will be added to SQL Server Management Studio, empowering more database professionals with foundational tools for database DevOps.

See what’s next and join the conversation

We know that you will have feedback for us, now and as we go forward, and we want to thank you in advance for that, as it is critical that we understand what you need from your SQL tooling. This benefits you, us, and the entire community.

• SQL Server Management Studio roadmap
• SQL Server Management Studio feedback site
• MSSQL extension for Visual Studio Code roadmap
• MSSQL extension for Visual Studio Code feedback
• SQL projects and DacFx feedback
• Microsoft SQL feedback site
• SQL database in Fabric roadmap
• SQL database in Fabric Ideas site

The post How the Microsoft SQL team is investing in SQL tools and experiences appeared first on Microsoft SQL Server Blog.

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

Learn more about

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

Frequently asked questions

What is SSDT?

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

What is the Generate and Publish Scripts Wizard?

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

What is a data-tier application (DAC)?

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

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

What is DBCC CLONEDATABASE command?

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

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

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

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

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

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

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

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

• Database metadata
• Hardware environment
• Database session state

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

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

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

Microsoft SQL Server Machine Learning Service

What is SQL Server Machine Learning Services with Python and R?

Learn more

Using the AdventureWorks sample database in a practical example

The AdventureWorks (fictitious) company uses a database that stores data about sales and marketing, products, customers and manufacturing. It also contains a view that joins information about the products, such as the product name, category, price, and a brief description.

Currently, AdventureWorks sends the product name and brief description to a marketing firm to develop a campaign to boost sales. The cost for this marketing specialist is significant, and after looking into the capabilities of Generative Pre-trained Transformers (GPT), the company is curious to know if this AI could be used to create starter-text for brochures, automating the first step of the process. GPT-4 is a powerful language model that can generate coherent and creative text from complex input, or prompts.

Instead of hiring a professional writer, they can use SQL Server Machine Learning Services with Microsoft Azure OpenAI Service to automatically generate high-quality starter ad copy based on the product description and other relevant data. This can save time and money on the marketing budget. This approach also has the added benefit of security, since the database stays local, Stored Procedures have high security granularity, and Azure OpenAI Service does not use the information sent for further training.

This process works on SQL Server Machine Learning Services platforms on Windows, Linux, Containers, and Kubernetes clusters, including virtual machines and SQL Server Managed Instance, from version 2019.

During their research, AdventureWorks discovered that it is important to be cautious when generating text using a GPT model. It is possible that the generated text may not always be appropriate or accurate, so it is important to have a system in place for reviewing and approving any changes before they are published.

The proof-of-concept (PoC) for AdventureWorks involves the following requirements:

1. Create a Stored Procedure to accept the model of a given product.
2. Generate marketing brochure text for that product, highlighting its features and benefits, using Azure OpenAI Service’s GPT-4 model.
3. Ensure that the generated text is safe and factual, and that it can be edited before publishing.

Example code

The POC project for AdventureWorks involves the following steps:

1. Install SQL Server Machine Learning Services on a Microsoft Windows, Linux, or Container environment.
2. Create and deploy a model on Azure OpenAI Service, and obtain the model’s name, keys and endpoints.
3. Install the openai Python package using Azure Data Studio or the sqlmlutils package. This enables the code to use OpenAI API in Python scripts.
4. Write a Python script that uses the sp_execute_external_script stored procedure to query the product’s view, send the product name and description as a combined prompt to the Azure OpenAI API. The script should also specify the parameters for the model, such as the engine name (such as `davinci`), the temperature (a measure of randomness), the max tokens (the maximum length of the generated text), and the stop sequence (a token that indicates the end of the text). The script should also handle any errors or warnings from the API and return the generated text as a result set.
5. Execute the Python script from SQL Server Management Studio or Azure Data Studio and review the generated text for each product. Edit the text as needed or re-run the script with different parameters to get different results.
6. Once the result is satisfactory, wrap the code in a stored procedure to take the product’s model number from the user, and ensure the security chain is verified for the Stored Procedure.
7. In the end-user application, display the result for the ad text to the user for editing, and send the edited result to the database for storage.

With that outline, sample code in an Azure Data Studio Jupyter Notebook has been developed for AdventureWorks, allowing descriptive blocks, code, and persistent results display. After testing and editing, the code can be copied to a Transact-SQL script and deployed to testing and then on to production. The end result produces the following starter text to display to the end user for editing and confirmation before being set to the database:

[sic]

Dear Mountain Bike Enthusiast, Are you looking for a bike that can deliver the performance you need without breaking the bank? Look no further than the Mountain-400-W Silver, 38.  This bike is designed to give you the high-level performance you need to tackle any terrain, without the high price tag. With its responsive and maneuverable design, you’ll be able to navigate even the toughest trails with ease.  But it’s not just about performance – the Mountain-400-W Silver, 38 also offers peace-of-mind when you decide to go off-road. With its sturdy construction and reliable components, you can trust this bike to handle anything you throw at it.  So why wait? Order your Mountain-400-W Silver, 38 today and experience the ultimate in off-road performance at a price you can afford. Don’t settle for anything less than the best – choose the Mountain-400-W Silver, 38 and take your mountain biking to the next level.

Other applications of this approach

As you can see, using Microsoft SQL Server Machine Learning Services with Microsoft Azure OpenAI Service has practical applications. In this example we used it to create a marketing brochure text for various products. This is a powerful and creative way to leverage AI for business needs. The resulting text could be sent to an image-generating service (such as DALL-E) to generate images for the brochure. Other data sets in the AdventureWorks database could be used in other applications, such as investigating sales regions and much more.

However, it is important to be cautious and responsible when using AI models, as they may not always produce accurate or appropriate results. You should always review and edit the generated text before publishing it and follow the safety and ethical guidelines of Microsoft Azure OpenAI Service.

Learn more

• What is SQL Server Machine Learning Services with Python and R?
• What’s new in SQL Server Machine Learning Services?
• The Microsoft Azure OpenAI Service.
• Understanding GPTs.

The post Secure your AI using SQL Server Machine Learning with Microsoft Azure OpenAI Services appeared first on Microsoft SQL Server Blog.

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.