By migrating their databases to Azure, customers like Ernst and Young are modernizing their data estate and leveraging cutting-edge cloud innovations. However, the migration process can be complex, whether moving within the same database management system (homogeneous) or between different systems (heterogeneous). Microsoft offers a suite of tools for migration to simplify the migration process. To further enhance the user experience, we are streamlining the Azure database migration tools ecosystem. This involves retiring certain overlapping tools to simplify finding the right tool and provide unified migration experiences across all phases of migration. As part of this effort, effective 12/15/2024 we are replacing some tools with unified experiences that offer capabilities across various migration stages in the drive to modernize their data estate and take advantage of innovation in the cloud.

Azure Database Migration Guides

Step-by-step guidance for modernizing your data assets

Learn more

With a refined set of tools, you can confidently plan, assess, and execute your database migration with minimal downtime, ensuring a smooth transition to Azure SQL. Post the 12/15/24, retirement date, Microsoft will stop supporting these tools for any issues that arise and will not issue any bug fixes or further updates. Here is the list of tools that are planned for retirement and Microsoft recommended replacement tools.

Microsoft recommended tools for migration to Azure SQL

With the retirement of Database Migration Assistant for Oracle (DMAO), Database Schema Conversion Toolkit (DSCT), Data Access Migration Toolkit (DAMT), Database Experimentation Assistant (DEA), the Azure database migration tooling ecosystem is greatly simplified. Here is Microsoft’s recommendation for database migration tools for customers moving to Azure SQL. 

Homogenous migrations (SQL Server to Azure SQL) 

If the SQL Server that will be migrated is already enabled by Azure Arc, you can use Arc capabilities to perform a migration assessment and get optimal Azure SQL Target recommendations. Additionally, SQL Server enabled by Azure Arc provides multiple Azure benefits to SQL Servers outside Azure like automated backups and patching, Microsoft Defender for SQL, inventory of instances and databases, and Entra ID support. By enabling these Arc features, you can leverage cloud automation and security for Azure SQL Server even before you migrate. 

If the SQL Server outside Azure is not inventoried yet, you can use Azure Migrate for discovery, assessment and business case to know the right Azure SQL targets for your on-premises SQL Workloads and to get the projected cost savings of migrating to Azure SQL.

To migrate SQL Server into an Azure Virtual Machine with the same configuration as the source, users can use Azure Migrate to perform lift and shift migrations. SQL Server on Azure Virtual Machines allows you to easily migrate your SQL Server workloads to the cloud, offering SQL Server’s performance and security along with Azure’s flexibility and hybrid connectivity to address urgent business needs. Later you can evaluate one of the Azure SQL PaaS targets (Azure SQL Managed Instance, Azure SQL Database) and modernize to a PaaS service for better cost and workload performance optimizations. 

If you have completed an assessment and are ready to move to Azure SQL Managed Instance or Azure SQL Database, you can start your migration journey with Azure Migrate, you can use Azure Database Migration service or Azure SQL Migration extension for Azure Data Studio can be used. 

If the SQL Server estate is already inventoried, users can use Azure SQL Migration extension for Azure Data Studio to complete the entire migration journey i.e., perform assessment, get Azure SQL Target recommendations and perform migrations.

Heterogenous migrations (non-SQL Server databases to Azure SQL) 

With the availability of Target Assessment and SKU recommendation capabilities in SQL Server Migration Assistant (SSMA) along with existing code conversion and migration capabilities, SSMA becomes a single tool that you need to use to migrate from other source database platforms like Oracle, DB2, SAP ASE, MySQL, Access to Azure SQL or SQL Server. 

Learn more about modernizing your databases with Azure

• Get step-by-step guidance for modernizing your Oracle and SQL Server databases to Azure in the Azure Database Migration Guides.
• Read about the Azure Migrate Assessment for Azure SQL to get an overview of SQL Server assessment process using Azure Migrate.
• Know how to complete end to end SQL Server to Azure SQL migration journey with Azure SQL Migration extension for Azure Data Studio. 
• Find documentation for SQL Server Migration Assistant (SSMA), a tool for assessing and automating migration from Microsoft Access, DB2, MySQL, Oracle, and SAP ASE.

The post Modernize your database with the consolidation and retirement of Azure Database Migration tools appeared first on Microsoft SQL Server Blog.

Azure Data Studio

A modern open-source, cross-platform hybrid data analytics tool designed to simplify the data landscape.

Learn more

Connectivity

The migration from the Active Directory Authentication Library (ADAL) to Microsoft Authentication Library (MSAL) was a significant undertaking by the team. This was necessary as ADAL support ends in June of this year, and it provides multiple benefits for those environments using Azure Activity Directory (AAD). AAD users should notice an improved and more reliable experience, particularly around token refresh and connection stability. This also helped us fix an issue in the MySQL extension for AAD. 

Additional changes include improved loading of Azure resources and new Dedicated SQL Pools and Azure Synapse Analytics nodes in the Azure tree. Azure Data Studio 1.41 also provides the ability to customize the name of firewall rules for Azure SQL and adds support for connecting to a server alias (versus a server name).

If you have applications that use ADAL, please see the Migrate applications to the Microsoft Authentication Library (MSAL) page for more information.

Object explorer

A new area of focus in this release is Object Explorer (OE), and this will continue to be an area we improve upon in the next few releases. Those with serverless Azure SQL previously reported issues with folders not expanding correctly, and with databases being brought online (thus incurring costs) when it was not expected. Other users noted that expanding OE timed out after 45 seconds. We have addressed all these issues in this release, in addition to adding support for Ledger Views.

Query results

The query results window got a fair bit of attention this release as we work through the backlog of open issues. First, we introduced a new configuration option to show or hide the action bar in the query results view. The Query Editor > Results: Show Action Bar option can be found in the command palette (CTRL + , ) if you type Show Action Bar. By default, the action bar is shown in the query results pane, as seen in the screenshot below:

There are also improvements around opening JSON files and the visibility of the horizontal scroll bar in the query results pane. Azure Data Studio 1.41 now correctly handles line breaks in cells when copying from the results grid and pasting to an editor, and the auto-resizing of columns in the output pane has been updated to better display column contents. Finally, cell selection and navigation in the results grid have been enhanced, and we introduced additional summary details when selecting multiple cells in the results window:

Extensions

Multiple teams have been working on updates to various extensions available from Azure Data Studio.  For SQL Projects, we have improved the experience of finding projects by providing a dropdown that lists saved projects, rather than requiring users to browse to their location. We had reports that differences in schema compare were not highlighted correctly, and that problem has been fixed.

Users of the SQL Migration extension will see an improvement in the migration process as we better support migrations to specific subscriptions (such as government), and the extension now includes the Premium Series Memory Optimized SQL MI SKU as a recommendation where appropriate.

MongoDB and Microsoft Azure continue to build on their partnership by introducing an extension for MongoDB Atlas and Azure Data Studio on the Azure Marketplace. This Extension is available in Public Preview as of today, Wednesday, January 25, 2023.  You already know that Azure Data Studio is a modern open-source, cross-platform hybrid data analytics tool designed to simplify your data landscape, and customers can use Azure Data Studio to work with their data sitting in one or more Azure data services. MongoDB Atlas on Azure provides a fully managed solution for MongoDB in the cloud, and you can now seamlessly connect to and query data on MongoDB Atlas right from Azure Data Studio. This allows you to interact with data on MongoDB Atlas alongside other data services and provides a unified view of your data estate.  If you are an Azure customer that is curious about building applications with MongoDB Atlas and want to amplify your integrated experience inside Azure Data Studio, try Pay-As-You-Go Atlas on the Azure Marketplace today!

With this 1.41 release, the Polyglot Notebooks extension will be removed from the Azure Data Studio Extension Marketplace. For a polyglot notebooks experience, we recommend folks use the Polyglot Notebooks in Visual Studio Code.

Odds and ends

Continuing on our path of adding support for arm64, we now include support for arm64 on Windows.  Whether you run iOS or Windows, Azure Data Studio 1.41 now provides the capability to leverage arm64, resulting in improved performance.

We are pleased to see users embracing Table Designer and Query Plan Viewer, two features that became generally available (GA) in the November release. In 1.41 we fixed an issue related to opening Table Designer for Ledger tables, and one related to creating a table when another table with the same name already exists.

There were also two requests specific to Query Plan Viewer that got attention in this release. When saving query plan files from Azure Data Studio, we now incrementally append a number to the end of the file for unique naming, and we’ve altered the default folder location when saving plans for a more consistent experience.

Lastly, we had previously announced that we were removing Big Data Cluster functionality from Azure Data Studio. This removal has been delayed until a later release.

Looking forward

We are already at work on the next release of Azure Data Studio and are making plans for what we want to accomplish in 2023. You can expect that we will continue to review backlog issues and address them as they relate to an existing area of focus. We have more changes coming related to the connection dialog and object explorer, and you will also see improvements in user management. Finally, if you see a comment on an issue you opened–whether recent or ages ago–please feel free to respond and provide more information if you are able. 

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

In addition to these two features now being generally available, we are pleased to announce enhancements in the assessment tooling for Oracle database migration to Azure Database for PostgreSQL and Azure SQL, both in preview. The MySQL extension for Azure Data Studio is also available in preview, as is Azure SQL Database Offline Migration support in the Azure SQL Migration extension. We would also like to introduce arm64 macOS support, for which many of you have been patiently waiting for (more details below).

As you may already know, SQL Server 2022 is generally available today, and we have introduced support for this latest version of SQL Server via our deployment wizard. We have made improvements to the connection experience, including a change to the Encrypt property, which now defaults to True. Finally, we completed another set of Visual Studio (VS) Code merges that included numerous bug fixes and UI improvements, some of which are highlighted below.

arm64 macOS support in Azure Data Studio

Over one year ago we had a request to add support to Azure Data Studio for arm64 on macOS. We are pleased to announce that in this release, said functionality is now available. Folks using arm64 macOS will notice that the Azure Data Studio builds for Apple Silicon or Universal will load and run significantly faster as emulation is no longer needed. There are a few extensions that still need to be modified to have the same support and we are working with the appropriate teams to get those changes in place. Interested in seeing support for arm64 on Windows? Share your comments and upvote here.

Change in default value for Encrypt Property

Version 1.40 of Azure Data Studio includes an important change to the Encrypt property for the MSSQL provider connections, which is now enabled by default (set to True). 

In Azure Data Studio 1.39 and below, the Encrypt connection property was on the Advanced page and defaulted to False. As shown below, both the Encrypt and Trust server certificates have been moved to the main Connection Details for Microsoft SQL Server connections, with information icons to provide more detail on hover.

The Encrypt property continues to support two options:

1. True (now the default value)
2. False

Upon upgrading to Azure Data Studio 1.40, users should review the options selected for Encrypt and Trust server certificate before connecting. More information about this change can be found here.

Introducing assessment tooling for Oracle database migrations to Azure Database for PostgreSQL and Azure SQL (preview)

Enabling the migration of Oracle workloads to Azure PostgreSQL and Azure SQL through a unified assessment tool has been a key ask from customers and we are excited to announce the preview release of this experience in Azure Data Studio via the Database Migration Assessment for Oracle extension (check out this demo to see this in action). With these changes, migration planning is simplified for customers looking to modernize their data estate to Azure managed databases. This new assessment scenario helps customers speed up migrations while reducing risk, making it easier than ever to move Oracle databases to Azure. Read more in this blog announcement.

MySQL extension is now available in Azure Data Studio (preview)

As you may have heard at Microsoft Ignite, the MySQL extension for Azure Data Studio is now available in preview, bringing more flexibility to database management. With the MySQL extension for Azure Data Studio, you can now connect to and modify MySQL databases, taking advantage of the modern editor experience and capabilities in Azure Data Studio. You can learn more here.

Announcing Azure SQL Database Offline Migrations in the Azure SQL Migration Extension (preview)

This new migration capability in the Azure SQL Migration extension provides an end-to-end experience to modernize from SQL Server to Azure SQL Database. This extension allows you to perform a migration readiness check with actions to remediate possible migration blockers, export the assessment results, and get right-sized Azure recommendations. These recommendations include an all-new elastic recommendation model to meet your database performance needs. Thanks to the Azure SQL Migration extension, you can perform offline migrations of your SQL Server databases running on-premises, SQL Server on Azure Virtual Machines, or any virtual machine running in the cloud (private, public) to Azure SQL Database. Learn more from this blog announcement. For a hands-on experience using this extension, please refer to the Migrate SQL Server to an Azure SQL Database offline using Azure Data Studio tutorial.

Query History Extension is now generally available

In the August release blog, we noted the benefits of the Query History extension which includes the ability to view previous queries executed and double-click on any query in the history to open it in a new window for viewing or execution. The team has addressed a few accessibility issues, added the ability to limit the number of entries stored, and it is now generally available through the extensions pane in Azure Data Studio. For those of you who write queries and tune code, the history provided from this extension can be a time-saver.

Visual Studio Code Merge—from 1.62 to 1.67

The UI framework of Azure Data Studio is forked from VS Code, and the codebase needs to be kept up to date with updates to its parent framework via merges, completed periodically by our engineers. This Azure Data Studio release includes updates that bring ADS to version 1.67, from its previous version, 1.62. While there are many updates in this release that users will appreciate, we have highlighted a few of our favorites below.

New Side Panel and Configure Layout settings 

Azure Data Studio now offers more flexibility to customize the layout in the user interface via the addition of Side Panels. With Side Panels, you can now house views from the Side Bar or the bottom Panel. Unlike moving the bottom Panel to the left or the right of the editor, the new Side Panel works in addition to the bottom Panel so you can see more sets of views at once. In the illustration below, the terminal is being dragged to the right and dropped to create the Side Panel. The user can then switch views in the Side Bar (by opening the global Search view), while keeping the terminal view visible.

To better configure layouts, we added a Customize Layout button in the title bar. This button provides a new interactive quick pick experience, allowing you to control all aspects of layout configuration in one place.

To learn more about these layout changes, check out the Visual Studio Code January 2022 release notes.

Local history

The local history of files is now available in the Timeline view. Every time you save a file in query editor, a new entry is added to the list. Each local history entry contains the full contents of the file at the time the entry was created and in certain cases, can provide more semantic information—for example, indicate a refactoring. In the illustration below, a CREATE TABLE T-SQL script is edited by inserting a statement to insert an additional column. Upon saving, this new entry is saved to the Local History tab and then compared to its previous version. You can also restore the content to previous versions, as well as delete or rename the entry. To learn more about this update, please check out this Visual Studio Code March 2022 release note.

Modified menu settings

The Settings editor search control now contains a funnel button on the right side. Clicking on the button shows a list of filters that you can apply to the search query to filter down the results.

Learn more

If you haven’t already installed this release of Azure Data Studio, please see how to on our download page.

The team is focused on improving Azure Data Studio from both a feature and stability perspective, and we hope these improvements make your daily use of ADS even better. 

We would love to hear your feedback on this release—you can find us on Twitter or log an issue on GitHub.

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

The end of summer in the Northern Hemisphere can be a quiet time for some, but the Azure Data Studio team has been busy. We’ve got another release to share and would like to start with a big “Thank You” to all who have interacted with the team and provided feedback through the various platforms and at recent events. (Hello VS Live attendees!) In this release, we have addressed issues across core features, and extensions, and fixed accessibility and usability bugs—many based on customer feedback. Please continue to share suggestions with us!

The Deployment Wizard now supports SQL Server 2022

SQL Server 2022, the most Azure-enabled release of SQL Server yet was announced in May and is publicly available in preview. SQL Server 2022 (preview) is now also available as an option in the Azure Data Studio Deployment Wizard. On the homepage, click on Deploy a Server and then choose the appropriate deployment option—SQL Server on Windows or SQL Server container image. On the version dropdown menu in Step 1: Deployment pre-requisites, select SQL Server 2022 Preview. This will then direct you to the free trial setup page. Please note that this trial is valid for 180 days or about 6 months.

Ledger Objects

Azure SQL Database and SQL Server 2022 includes support for Ledger, a new technology that brings the security and trust benefits of blockchain to relational databases. In this release of Azure Data Studio, we have introduced the appropriate icons for Ledger tables and views in Object Explorer, as well as scripting for CREATE and DROP. We will continue to expand support for Ledger in future releases.

Query Plan Viewer and Table Designer Updates

Both Query Plan Viewer and Table Designer are still in preview, with minor improvements in this release. Table Designer now supports the creation of filtered indexes, and you can specify included columns for nonclustered indexes.

Query Plan Viewer now includes the ability to copy text from the Properties pane, and new Find Node buttons exist in plan comparison to quickly find operators in either the original or added plan.

SQL Database Projects Updates

The SQL Database Projects Publish dialog has been enhanced with a breadth of options including excluding object types, data definition language (DDL) trigger behavior, index rebuild behavior, and more. These options are available after clicking the Advanced button and a description for each option appears at the bottom of the panel. With the availability of these options, you can now publish a SQL project or generate the publish script with more precision for the requirements unique to your environment.

Extension Updates

Anyone who has spent time in the Query Editor window knows that some days you write a lot of queries, regardless of whether you’re coding, tuning, or troubleshooting. There are times when you continually edit the same query, trying to get it just right, and then suddenly you realize that the version several iterations ago was the “best” one. But what was the exact syntax? With the Query History extension, you can view all the previous queries you’ve executed. The most recent update of the extension includes an enhancement that permits a double-click on a history query to open in a new query window and optionally, immediately execute it. For anyone who has lost minutes or hours trying to remember what they wrote, this extension can be a lifesaver.

In addition, an updated PowerShell extension was released in July from the VS Code team and is now available in the extension gallery for ADS. The release provides a more reliable PowerShell editor that includes an improved interface, and built-in snippets.

Summary

Let us know what you think of these updates by engaging with us on Twitter or by creating issues on GitHub. We would love to hear your feedback as we continue to iterate on Azure Data Studio.

Did You Know?

You can watch cute dogs while learning about Databases. Be sure to follow Anna Hoffman as she shares short but valuable information about the Azure Data platform as she walks her dog, Moose in this series.

The post Azure Data Studio August Release appeared first on Microsoft SQL Server Blog.

Updates to Query Plan Viewer

The Query Plan Viewer feature continues to add functionality with this release of Azure Data Studio. There are several UX improvements users may notice: the icon to enable the capture of an actual plan has been updated, operator selection is now noted with a solid green line, and the plan labels are updated in the Properties window when plans are compared and the orientation is toggled from horizontal to vertical, and back.  We have updated the Command Palette to make it easier to find the commands for execution plans, and while the CTRL + M command still enables actual plan capture for a query window, it no longer executes the selected query (or queries) in the window. 

The Top Operations pane, which appears secondary to the plan, now includes the Actual Rows column for plans with runtime statistics to expediate the evaluation of estimated and actual rows when analyzing a plan.  In each row of the Top Operations pane, the Operation (Clustered Index Seek, Nested Loop) is now a clickable link, and when selected will take you directly to that operator in the plan.

We’re excited to introduce collapse and expand functionality in plans, which is particularly helpful for unwieldy plans with numerous operators. If you want to hide one or more sections of the plan, simply click on the icon located to the right of the operator. This can reduce clutter and permit more focused analysis when you have many operators in a plan.

Visual Studio Code 1.62 merge  

Locked editor groups  

As mentioned earlier, Azure Data Studio was updated to merge with the Visual Studio Code version VS Code 1.62, from its previous version, 1.59. While there were substantial updates in this version, key improvements include improvements to the editor and the integrated terminal to allow for better management and customization.

Locked editor groups are now available both in the terminal as well as in the editor. With locking enabled, new editors will not open in a locked group unless explicitly moved there by the user. If an editor skips a locked group for opening, it will either open in the most recently used unlocked group or create a new group to the side of the locked one. Lastly, the locked state of an editor group is persisted and restored across restarts. A locked group is indicated by a lock icon in the action toolbar (top right).

Terminal improvements 

You can now statically set terminal dimensions via the Terminal: Set Fixed Dimensions command. This is useful for cases when line wrapping can be jarring or disruptive. The command will display a prompt where you can enter the preferred width and/or height. Additionally, updates have been made to allow for easier distinguishment between terminals. You can now configure both the title and description of your terminals using variables in the terminal.integrated.tabs.title and terminal.integrated.tabs.description settings. Lastly, the 1.62 VS Code release resolved over 4K upstream issues that were merged into this Azure Data Studio 1.38 release. For more details, check out this VS Code 1.62 version release note.

Table Designer updates

Updates were made to the Table Designer to improve overall accessibility. Previously, the context menu could be opened using the context menu key. However, this action was difficult to perform with the cell in edit mode; the user would have to escape this mode by clicking out of it to access the context menu. To solve this problem, a new column was added to allow for these actions to be easily discoverable. Users can access this menu simply by clicking on the ellipses in the More Actions column.

Summary

In the coming months, we will continue to make improvements to Azure Data Studio. You will see incremental, but vital enhancements to the Table Designer and Query Plan Viewer features, and we are continuing to keep pace with VS Code updates. We expect new extensions from our partners, and we are also working on our roadmap for the rest of this year and into 2023.

Did you know?

We are on Twitter! We want to hear from you as we continue to grow Azure Data Studio and fill existing gaps. You can always log an issue on GitHub, but feel free to engage with us on Twitter also. You can share what you like, or what you think we could do better. Thanks for reading!

The post Azure Data Studio July Release with VS Code Updates  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.

Announcing general availability for the Azure SQL migration extension in Azure Data Studio

We are excited to announce the general availability of the Azure SQL Migration extension for Azure Data Studio. Among many other capabilities, this extension can be used for migrating SQL Server databases to Azure for an enhanced user experience. With this extension, users can get right-sized Azure recommendations based on performance data collected from your source SQL Server databases to optimize for cost and scale. The migration experience is powered by the Azure Database Migration Service which provides a scalable, resilient, and secure way to meet the needs of your organization. See below for a snapshot UI of this extension.

With this extension, you can view and monitor the details of your database migrations. This is as simple as migrating your database using the Azure SQL extention and tracking progress and health of these migrations directly on the Azure portal.

To learn more, take a look at our Tech Community blog.

Support for .NET Interactive Notebooks extension

The .NET Interactive Notebooks extension in Azure Data Studio now provides support for additional languages such as C#, F#, and PowerShell. This multi-language experience enables users to use the best language for the task at hand. Even more exciting is that there is now the ability to use multiple languages in the same Interactive notebook and share variables between those languages using SQL Integration.

New Table Designer features

Based on user feedback, we have made available more table attributes with advanced features. Azure Data Studio now offers system versioning, graph tables, and Memory-Optimized Tables.

System versioned tables

System versioned tables, also known as Temporal Tables, can now be created in Azure Data Studio, and these tables provide historical information about column data at any given point in time. All data in Temporal Tables exists in direct relation to the time the data was created, updated, or deleted.

So why Temporal Tables?

You guessed it, time travel! System versioning makes it easy to perform “time travel” to see the history of all modifications made to data, which makes tracking changes and data auditing more feasible. Time travel also makes data recovery possible as all changes made to data are tracked in the system-versioned tables, making it easier to revert to the values which existed at the exact time the data was lost. When system versioning is enabled during table creation, the engine automatically creates a separate version of that table. System versioned tables follow the same schema as the original tables from which they were generated.

In the table above, we have a system version enabled table named “Department”. A separate historical table, “dbo.MSS…” is automatically created to track all changes made to this table. In the original Department table, a series of changes were made. The table below shows all these changes relative to the time frame they existed. For more information, take a look at our documentation on system versioned tables.

Graph tables

Graph tables have been available since SQL Server 2017 and can now be designed in Azure Data Studio. Graph tables provide an easier way to create relationships between different datasets using nodes and edge tables. Node or edge tables can be created under any schema in the database, but they all belong to one logical graph. Azure Data Studio brings a newly improved visualization to differentiate node and edge tables. In the table below, node tables are represented by a single dot, while edge tables are depicted by two dots with a line in between connecting them.

The image below shows a graph node table in the Table Designer. When a table is indicated as a node, the engine automatically assigns an ID to that table (as seen in the UI as “$node_id”) as this becomes the unique identifier to this table. The script is also updated to indicate that this table is a node table.

Edge tables, on the other hand, are used to show the relationships between node tables. When an edge table is created, multiple internal columns are created. The ID specific to the edge table, and then the IDs representing the nodes that will be related to each other as seen below. Also, notice the change in the script.

The Table Designer fully supports working with edge constraints for SQL graph edge tables. In the image below, our edge table (named “Likes”) is constrained by “Person to Product” and “Person to Restaurant” clauses. This means that an edge can either link between “Person to Product” or “Person to Restaurant” based on the “likes”. Azure Data Studio brings a one-of-a-kind, easy-to-grasp UI to depict edge and node relationships.

For more information take a look at our documentation on graph tables.

Memory-Optimized Tables

Memory-Optimized Tables can now be created via Table Designer in Azure Data Studio. With Memory-Optimized Tables, data is persisted in memory, rather than on disk, with the option to include durability to schema and/or data. This is particularly useful for applications that have databases that undergo multiple concurrent transactions. See image below for memory-optimized configuration and accompanying script changes. For more information take a look at our memory-optimized tables documentation.

Query Plan Viewer updates

There have been a small number of visible changes to Query Plan Viewer in this release of Azure Data Studio. We have added icons to note warnings and parallelism when they occur for an operator, and we’ve introduced additional Find options when searching within a plan. There is a new option on the plan toolbar (added to the bottom) which provides the option to disable tooltips, this is particularly helpful when navigating plans with numerous operators.

Finally, we’ve added support for opening saved .sqlplan files in Azure Data Studio, and we addressed some display issues in the Properties window.  Expect additional enhancements in the next release.

SQL Projects and Schema Compare

In the latest release of the SQL Database Projects extension for Azure Data Studio and Microsoft Visual Studio Code, we are introducing a new SQL Projects format based on an SDK-style project file. The SQL project SDK is named, Microsoft.Build.Sql and it is now available in preview via NuGet and the 0.16.1 version of SQL Database Projects for Azure Data Studio and Visual Studio Code. With Microsoft.Build.Sql, cross-platform pipeline builds are simplified, and the complexity of the project file is significantly reduced. For more information on SDK-style SQL Projects and updates to cross-platform SQL development take a look at this blog.

Other improvements for developers in this release focus on increasing the flexibility of local development. Updating a SQL project from the current state of a database is now enabled in the integration between Schema Compare and SQL Projects and can be accessed directly from the dashboard of a database. For developers focused on working with Azure SQL Database, a new project template has been introduced that enables local validation and testing of SQL Projects for Azure SQL Database.

Learn more

We are excited to continue this path to modernizing database experiences for developers and database administrators alike. Watch this space for future updates. To learn more about the Azure Data Studio release and journey, check out the release notes for Azure Data Studio.

The post Amplify your database development experience with Azure Data Studio 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.

Administrators can enable platform logging and metrics to one of their Azure services such as Azure SQL and set the destination to Log Analytics workspace. By installing native Azure Monitor Logs extension in Azure Data Studio, users can connect, browse, and query against Log Analytics workspace. Data professionals who are using Azure SQL, Azure PostgreSQL, or Azure Data Explorer in Azure Data Studio can access the data in the Log Analytics workspace for diagnosis or auditing in that same development environment. This native Azure Monitor Logs extension allows Azure service customers to also author notebooks with Log Analytics kernel, all equipped with Intellisense.

Examples for Azure SQL users

While the examples in this section are specific to Azure SQL scenarios, Azure Monitor Logs can also capture events from other Azure resources, such as Azure Functions or Azure Web Apps. Now, let’s dive into these examples where users can leverage Log Analytics workspace data to better understand an Azure SQL database.

Example One: Who dropped a table in my database?

Connect to your Log Analytics workspace that captures the Azure SQL audit events. Right-click and choose New Query from the menu. Copy the following query and paste it to Query editor in Azure Data Studio.

AzureDiagnostics
| where action_name_s == "BATCH COMPLETED"
| where statement_s contains "DROP TABLE"
| project TimeGenerated, Category, OperationName, server_principal_name_s, statement_s
| sort by TimeGenerated desc 
| take 10

In this example, a good question to ask might be, why are these users executing a lot of drops during this time?

Example Two: What type of errors happen?

Connect to your Log Analytics workspace that captures the Azure SQL error events. Right-click and choose New Notebook from the menu. Copy the following query and paste it to Query editor in Azure Data Studio.

AzureDiagnostics
| where OperationName == "ErrorEvent"
| extend ErrorNumber =  toint(error_number_d) 
| summarize event_count=count() by EventTime = bin(TimeGenerated, 2d),  ErrorNumber
| evaluate pivot(ErrorNumber, sum(event_count))
| sort by EventTime asc

In this example, we see a timeline with two-day intervals that maps error count by error number. A good question would be to understand why error 208 happens a lot.

Overall benefits

Here are four key benefits of using Azure Monitor Logs extension in Azure Data Studio.

1. Efficiency in data exploration and data analysis. Users now have access to their SQL data sources in the same place as their Log Analytics workspaces. Users can also use Sand Dance extension to further enhance their data exploration and analysis.
2. Reproducible analysis and diagnosis with notebooks.
3. Improved DevOps troubleshooting experience with Azure Monitor Logs notebooks. This is illustrated in the previous examples.
4. Version control the queries and notebooks directly with git in Azure Data Studio. Users can also add these files as part of their CI/CD pipelines in GitHub or Azure DevOps.

Other related extensions: Kusto (KQL) extension which works for Azure Data Explorer

How to get started

• Azure Monitor Logs Extension for Azure Data Studio (preview)
• Create diagnostic settings to send platform logs and metrics to different destinations
• SQL to Kusto cheat sheet
• What is Azure Monitor Logs?
• Using SandDance visualizations

This preview release is the beginning of a strategic journey to richer end-to-end DevOps with Data in Azure Data Studio. Please feel free to submit your suggestions and bugs on GitHub.

The post Enhance your Log Analytics data exploration in Azure Data Studio appeared first on Microsoft SQL Server Blog.

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

The key highlights to cover this month include:

• Added new notebook improvements
• Added improvements to Azure Arc extension.
• New extension updates.
• Bug Fixes.

For a list of complete updates, refer to the Azure Data Studio release notes.

Added new notebook improvements

Start-up time performance improvements

When first launching Azure Data Studio, it can take a long time for the Jupyter server to start running. We found this to be an issue on Windows, so with the February release of Azure Data Studio, you will experience up to a 50 percent improvement on Jupyter Server start-up time. This allows you to start creating and editing your Jupyter notebooks even faster.

Edit Jupyter Book through UI

As users are trying out Jupyter Books, which is a collection of Jupyter notebooks organized by a table of contents, users are looking for easy customization through UI-guided experiences. Users found it painful to update a .yml file in order to organize the Jupyter Book in the order that they wanted.

With the February release of Azure Data Studio, you no longer need to mess with a .yml file.

In the notebooks viewlet, you can now right-click a notebook or section in a Jupyter Book and move it to the correct location. The right-click option will take you to the quick picker that auto-detects all sections inside the Jupyter Book.

This quality of life feature was a highly requested feature, and we are happy to bring this to customers with the February release.

Preview of passing parameters through URI

With the February release, users can now pass parameters through notebook URI directly in Azure Data Studio.

New parameterization documentation

In a previous release, we announced Papermill parameterization support in Azure Data Studio. We now have updated documentation so you can start using parameterization yourself. Try it out now.

Added improvements to Azure Arc extension

Since the preview, the Azure Arc extension has continued to add new functionality. With the February release, users can now support multiple data controllers. This now gives you a flexible experience to view all the data controllers you want to connect and manage.

We have also added a new option in the connection dialog to ask for a Kube configuration file path to connect to a controller.

New extension updates

With this release, we have pushed out new versions of the following extensions. If you have not tried out these extensions before, check out each extension’s documentation and try it out.

• Profiler
• Kusto
• SQL Database projects
• Machine learning
• DacPac
• Schema compare

Bug fixes

As mentioned in the previous blog, we are focusing on fundamentals and driving the bug count down across the product. In February, we have fixed 51 bugs. A full list of bug fixes for the February release can be found here.

If you would like to help make Azure Data Studio a great product, please share any feedback or report issues through our Issues page. Our engineering team is regularly going through the untriaged issues and assigning issues into different monthly milestones so that you know we are working on it. Your votes on issues help us prioritize.

Contact us

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

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