Part of the SQL Server 2022 blog series.

Traditionally, data to serve analytical systems have been extracted from operational data stores using custom-built extract, transform, and load (ETL) processes. These processes are often long-running, exert pressure on the source systems, and only run periodically in batch mode. While this kind of latency and overhead may be acceptable for some workloads, more and more companies are finding themselves in a place where they need to do analytics over operational data closer to real-time—something that traditional ETL systems cannot support.

Enter Azure Synapse Link for SQL.

Azure Synapse Link for SQL provides an automated way to extract data from source operational systems without having to build custom ETL processes. Some of the benefits of Azure Synapse Link for SQL are:

• Low code/no code solution: With Azure Synapse Link for SQL, you don’t need to build custom processes to extract the data and load it into an analytical system. You choose the tables that you want to replicate, specify how you want them stored in the target Azure Synapse Analytics dedicated SQL pool, and Azure Synapse Link for SQL takes care of the rest.
• Minimal impact on the source systems: We have strived to minimize the impact of data extraction from the source system. Where a traditional ETL process will run queries against the source tables, which can get expensive, Azure Synapse Link for SQL uses the new change feed functionality built into SQL Server 2022 and Azure SQL Database to get the data without having to run custom queries.
• Near-real-time data movement: Data is continually moved from the source systems into the Azure Synapse Analytics environment. Optionally, you can switch to “scheduled mode” if you don’t need near-real-time data movement.

How does it work?

Azure Synapse Link for SQL is powered by the new change feed functionality that has been added to SQL Server 2022 and Azure SQL Database. This functionality allows us to monitor tables for changes as they happen without the additional overhead that is brought along by a change data capture (CDC)–based data movement solution.

When a transaction is committed on a table that is being replicated by Azure Synapse Link for SQL, that transaction is written into a “landing zone,” which is a Gen2 Azure Data Lake storage (ADLS) account. From there, an ingestion service picks up the data and loads it into an Azure Synapse Analytics dedicated SQL pool. Once the data lands there, you can query the data like any other dedicated SQL pool.

Who will benefit?

Here are some examples of scenarios that would benefit from Azure Synapse Link for SQL:

• Database consolidation: Azure Synapse Link for SQL allows you to bring data from multiple source databases together into a single dedicated SQL pool for analytics. Whether you have multiple tenant databases that you want to use for market-based analytics, or you have grown by acquisition and have multiple source systems to bring together for analytics, Azure Synapse Link for SQL can bring all of that data together into a unified analytical platform.
• Hybrid on-premises/cloud: Since Azure Synapse Link for SQL supports both Azure SQL Database and SQL Server 2022, you can bring data into a common analytical system from wherever it lives.
• Near-real-time extension: If you have an ETL system that meets most of your needs but have a few tables where you want data to arrive closer to real-time, you could use Azure Synapse Link for SQL to transfer those tables from the source systems into the Azure Synapse Analytics dedicated SQL pool alongside the data that is processed in your nightly ETL system, and perform reporting an analytics tasks over all of the data.

How to learn more

• Read our technical blog post from the public preview launch.
• Check out our “getting started” documentation.
• Dig in with our How-To guides:
  • SQL Server 2022
  • Azure SQL Database

The post Azure Synapse Link for SQL appeared first on Microsoft SQL Server Blog.

This year, DPS 2020 features five parallel tracks focusing on Azure Data:

• Advanced Analytics
• Artificial Intelligence
• Azure Data Administration
• Azure Data Development
• Power BI

The virtual platform offers live Q and A, a networking lounge, a community zone, and technical round tables. Additionally, attendees will receive 12 month on-demand access to session recordings.

DPS 2020 offers an incredible opportunity to learn directly from our engineering teams, who will share the latest advances and insights on the Azure Data platform.

• Rohan Kumar will deliver the keynote. Rohan will highlight the latest innovations across the Microsoft Azure Data platform and share customer case studies. The keynote will also feature demos from multiple Microsoft engineers, including Anitha Adusumilli, Anna Hoffman, Buck Woody, Travis Wright, and Vasiya Krishnan.
• Microsoft Azure Data engineering teams will deliver over 35 sessions at DPS 2020. Hear the latest from the people who develop the tools you use every day, and engage in live discussions.
• Visit the virtual expo hall where you can connect with our team across SQL Server, Azure SQL, Azure Synapse Analytics, Power BI, and more.

Register now for a week of training at Data Platform Virtual Summit and receive twelve months of on-demand access to the DPS 2020 sessions.

The post Microsoft Azure at Data Platform Virtual Summit 2020 appeared first on Microsoft SQL Server Blog.

Today we are announcing the general availability of the highest requested feature for SQL Analytics in Azure Synapse, SQL Server Data Tools (SSDT) database projects. This release includes support for SQL Server Data Tools with Visual Studio 2019 along with native platform integration with Azure DevOps providing built-in continuous integration and deployment (CI/CD) capabilities for enterprise level deployments. This announcement also comes with support for the Schema Compare extension in Azure Data Studio for SQL Analytics.  You can now expect a frictionless development and deployment experience on any platform for your analytics solution.

Since announcing preview support for SQL Server Data Tools (SSDT), customers have been able to use popular SQL Server Data Tools features such as Schema Compare, build, and publish for local development of their data warehouse. Although this has helped customers accelerate project development, an automated build, test, and deployment infrastructure is still critical for continuous integration and deployment (CI/CD) scenarios. Without the native integration with Azure DevOps, customers were still forced to manually write PowerShell and TSQL scripts integrated with Azure DevOps for an automated release process.

With SQL Server Data Tools generally available and native Azure DevOps support, you can now set up stable release pipelines without any custom code, and changes to your data warehouse model can be safely and automatically promoted across development, testing, and production environments. Preview customers such as T-Mobile will now be able to accelerate their feature development with Azure Synapse.

“In our current environment, we would have needed hundreds of custom scripts to validate and promote changes across our test and production environments. We’re excited to now simply use SSDT, MSBuild, and the Publish task in Azure DevOps to deploy and release features to production on a consistent and faster cadence.” – Anthony Sabol, Director, Reporting & Analytics at T-Mobile.

Integrate with Microsoft Azure Repos for continuous integration 

Data engineers and developers can easily integrate their SQL Server Data Tools database projects with Microsoft Azure Repos. 

Configure continuous deployment using Microsoft Azure Pipelines 

Changes committed to source control in Azure Repos can automatically be pre-validated using MSBuild and promoted to target environments using Microsoft Azure Pipelines and the built-in SQL Analytics deployment task extension.  

Cross platform support for Schema Compare with Azure Data Studio 

Azure Data Studio is a cross-platform database tool that now allows you to compare the schema between two data warehouse definitions.  

Next steps

• Install Visual Studio 2019 for SQL Analytics to get started
• Set up source control integration with SQL Analytics by using Azure Repos
• Integrate a simple Azure Pipeline for continuous integration and deployment for SQL analytics capabilities within Azure Synapse
• Learn more about Azure Data Studio SQL Compare
• For feature requests and providing us feedback, please vote on our UserVoice

The post New in Azure Synapse Analytics: CICD for SQL Analytics using SQL Server Data Tools appeared first on Microsoft SQL Server Blog.

1. It’s consistent

Microsoft is a truly consistent hybrid platform, with the same code base across on-premises, Azure, and Azure Stack. With Azure Stack, you can extend Azure services and capabilities to on-premises environments without any code changes. Interoperability between SQL Server, SQL Server in an Azure Virtual Machine, and Azure SQL Database ensures business continuity and you can easily set up disaster recovery through Always On availability groups.

Download the Azure Stack datasheet

Set up disaster recovery for SQL Server

2. It’s frictionless

The task of migrating your databases to the cloud can be daunting. Microsoft can help you manage the migration seamlessly with several tools and services that simplify the process with minimal downtime. SQL Database Managed Instance provides easy re-hosting with no code changes, enabling a way to migrate huge databases into Azure without changing any code and staying compliant with your applications, without sacrificing performance.

Get started with SQL Database Managed Instance

Learn more about migrating a SQL Server database to SQL in Azure Virtual Machines

3. It’s cost-effective

SQL Server and Azure SQL Database are two of the most cost-effective hybrid data platforms with flexible pricing tiers. Leverage your on-premises licenses and migrate your Windows Server and SQL Server workloads to Azure and save with the Azure Hybrid Benefit (AHB). You can choose from different purchasing options and service tiers to fit your performance and cost needs.

Calculate your savings here with the Total Cost of Ownership (TCO) calculator

Allscripts, a leading healthcare software manufacturer, started using Microsoft Azure. In just three weeks, the company lifted and shifted dozens of acquired applications running on 1,000 virtual machines to Azure – migrating their on-premises applications to the cloud by taking advantage of Azure SQL Database Managed Instance.

“Using Azure has dramatically accelerated both our acquisition time and our new product development time. We’ve been able to shut down older, inefficient datacenters and focus more resources on developing great software. This is one factor that moves us ahead in a very competitive market.” – Peter Tomlinson, Director of IS, Technology Operations, Allscripts

Learn more about Allscripts’ hybrid data story

Try SQL Server on Azure Virtual Machine or SQL Database Managed Instance today, and manage your data seamlessly across on-premises and cloud.

The post Three reasons to choose Microsoft for your hybrid data platform appeared first on Microsoft SQL Server Blog.

Customers will get significantly improved query performance and enhanced security features with this release. APS AU7 builds on appliance update 6 (APS 2016) release as a foundation. Upgrading to APS appliance update 6 is a prerequisite to upgrade to appliance update 7.

Faster performance

APS AU7 now provides the ability to automatically create statistics and update of existing outdated statistics for improved query optimization. APS AU7 also adds support for setting multiple variables from a single select statement reducing the number of redundant round trips to the server and improving overall query and ETL performance time. Other T-SQL features include HASH and ORDER GROUP query hints to provide more control over improving query execution plans.

Better security

APS AU7 also includes latest firmware and drivers along with the hardware and software patch to address the Spectre/Meltdown vulnerability from our hardware partners.

Management enhancements

Customers already on APS2016 will experience an enhanced upgrade process to APS AU7 allowing a shorter maintenance window with the ability to uninstall and rollback to a previous version.
AU7 also introduces a section called Feature Switch in configuration manager giving customers the ability to customize the behavior of new features.

Flexibility of choice with Microsoft’s data warehouse portfolio

The latest update is an addition to already existing data warehouse portfolio from Microsoft, covering a range of technology and deployment options that help customers get to insights faster. Customers exploring data warehouse products can also consider SQL Server with Fast Track for Data Warehouse or Azure SQL Data Warehouse, a cloud-based fully managed service.

Next Steps

For more details about these features, please visit our online documentation.

The post Microsoft releases the latest update of Analytics Platform System appeared first on Microsoft SQL Server Blog.

mssql-cli is a new and interactive command line query tool for SQL Server. This open source tool works cross-platform and is part of the dbcli community.

mssql-cli auto-completion in action

In this release (v0.13.0), highlights include:

• Apt-get and Linux packaging support
• GDPR Compliance
• New demo video

Apt-get and Linux packaging support

One of the key issues Linux users run into when setting up mssql-cli for the first time is not having the right version of Python or having to install Python for the first time. We want to make the first experience with mssql-cli painless, thus we added apt-get support in order to package Python with your installation to help improve the acquisition experience.

For full instructions to acquire mssql-cli for each Linux distribution, please check out the Linux installation guide.

Note: For those who already installed mssql-cli via pip install, please run

sudo pip uninstall mssql-cli

Then, follow the installation instructions.

GDPR compliance

As many of us are familiar with, GDPR is approaching and we made some updates. In the past, file history stored entire T-SQL queries. However, if the query contained any secrets or passwords, it wasn’t smart enough to scrub those out. This is no longer the case, and now file history has been updated to no longer store secrets or passwords.

In addition, we have added 24-hour rotation of UserID when we collect telemetry.

New Channel9 video

One of our engineers, Abhi Abhishek, presented a demo of mssql-cli for Channel9, the Microsoft Developer studio. If you are new to mssql-cli, please check out this video and please share with those you feel would benefit from using this tool.

Contact us

We are open to any questions, feedback, or any feature suggestions for future releases, which can be submitted on our GitHub Issues. You can also Tweet at us @sqldatatools.

The post A new update for mssql-cli, an interactive CLI, is now available appeared first on Microsoft SQL Server Blog.

We are excited to announce the February release of SQL Operations Studio is now available.

Download SQL Operations Studio and review the Release Notes to get started.

SQL Operations Studio is a data management tool that enables you to work with SQL Server, Azure SQL DB and SQL DW from Windows, macOS and Linux. To learn more, visit our GitHub.

SQL Operations Studio was announced for Public Preview on November 15th at Connect(), and this February release is the third major update since the announcement. If you missed it, the January release announcement is available here.

The February release includes several major repo updates and feature releases, including:

• Added Auto-Update Installation feature
• Added Connection Dialog ‘Database’ Drop-down
• Added functionality for new query tabs keeping active connection
• Fixed bugs in SQL Editor and auto-completion

For complete updates, refer to the Release Notes.

Auto-Update Installation

We want to use the February Insiders builds to test the auto-update feature. The 0.26.2 build will be released as an auto-update to 0.26.1 (assuming there are no issues that require publishing a new build to successfully support auto-update).

To discover updates faster, we have added auto-update functionality to ensure you have the latest version of SQL Operations Studio. A notification will pop up on the gear icon on the bottom left as shown in the image above. In addition, you can also check for updates by clicking Help on the toolbar.

Connection Dialog Database Drop-down

The Connection Dialog Database field is now a dynamically populated drop-down list that will contain a list of databases populated from the specified server.

New query tabs keeping active connection

A top voted feature by our users was for new query tabs to keep their active connection. In the February release, users can now click on a server name and press Ctrl + N, which will create a new query tab and retain the active connection instead of having to manually set the connection. This can be seen in the gif below.

Contact us

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

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

We are excited to announce the January release of SQL Operations Studio is now available.

Download SQL Operations Studio and review the release notes to get started. SQL Operations Studio is a data management tool that enables you to work with SQL Server, Azure SQL DB, and SQL DW from Windows, macOS, and Linux. To learn more, visit our Github.

SQL Operations Studio was announced for public preview on November 15, 2017 at Connect(), and this January release is the second major update since the announcement. If you missed the December release announcement, you can learn more on the SQL Server blog.

The January release includes several major repo updates and feature releases, including:

• Enable the HotExit feature to automatically reopen unsaved files.
• Add the ability to access saved connections from Connection Dialog.
• Set the SQL editor tab color to match the Server Group color.
• Fix the broken Run Current Query command.
• Fix the broken pinned Windows Start Menu icon.

For a complete list of updates, please refer to the release notes.

HotExit feature

A highly requested feature for SQL Operations Studio is to remember unsaved changes when the program is exited, similar to VSCode and other editors. We are excited to announce that HotExit has been enabled as demonstrated in the GIF above. SQLQuery2 represents an unsaved query, and the user can quit SQL Operations Studio and simply reload it again to retain the unsaved query.

This feature is not enabled by default. To enable HotExit by default, go to Settings with Ctrl + , and  copy “files.hotExit”: “onExit” into your settings.

Saved connections available in Connection Dialog

The Connection Dialog now contains a “Saved Connections” tab next to “Recent Connections”. This contains a list of saved server connections from the server’s “Object Explorer” viewlet, as well as the Server Group.

SQL editor tab color to match Server Group color

SQL query editor and dashboard windows will have the same color as the Server Group used to open the window. This feature is not enabled by default. To enable Tab Colors by default, go to Settings with Ctrl + , and  copy “sql.tabColorMode”: “fill” into your settings.

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

The post The January release of SQL Operations Studio is now available appeared first on Microsoft SQL Server Blog.

We are excited to announce the December release of SQL Operations Studio is now available.

Download SQL Operations Studio and review the Release Notes to get started.

SQL Operations Studio is a data management tool that enables you to work with SQL Server, Azure SQL DB and SQL DW from Windows, macOS and Linux. To learn more, visit our GitHub.

SQL Operations Studio was announced for Public Preview on November 15th at Connect(), and this December release is the first major update since the announcement.

The December release includes several major repo updates and feature releases, including:

• Migrating SQL Ops Studio Engineering to public GitHub repo
• Azure Integration with Create Firewall Rule
• Windows Setup and Linux DEB/RPM installation packages
• Manage Dashboard visual layout editor
• “Run Current Query with Actual Plan” command

For complete updates, refer to the Release Notes.

Migrating SQL Ops Studio Engineering to public GitHub repo

To provide better transparency with the SQL Operations Studio community, we have decided to migrate the Github internal branch to the public repo. This means any bug fixes, feature developments, or even test builds can be publicly viewed before an update is officially announced.

We made this move because we want to collaborate with the community to continually deliver features that our users want. This gives you the opportunity to see our changes in action to address your top voted issues. Visit our GitHub page and give us your feedback.

Azure Integration with Create Firewall Rule

Now let’s get into new features. A common issue when connecting to Azure SQL DB instances is that the connection can fail due to server firewall rules. This would require loading Azure Portal to configure firewall rules so that you can connect to your database, which can be inconvenient.

To speed up this process, we have enabled Azure Integration with Create Firewall Rule dialog. When your connection to an Azure SQL DB instance fails because of firewall settings, this dialog will appear, allowing the user to use their Azure subscription account to automatically configure the client IP address with the server. This retains the same experience as configuration on Azure Portal, except you can do it all through SQL Operations Studio.

Windows Setup installation and Linux DEB/RPM installation packages

We are always looking for new ways to improve the installation experience. With the December release, we have added Windows Setup installation to simplify installation on Windows. This wizard will allow the user to:

• Select installation location
• Select start menu folder
• Option to add to path

In addition to Windows Setup, we have also added Linux DEB/RPM installation packages. These will add new ways for Linux users to download SQL Operations Studio for their choice of installation.

Feel free to try out these new installation experiences on our download page.

Manage Dashboard visual layout editor

In the initial release, there were not many options to customize the visual layout of the dashboards. With the December release, you can now resize and move your widgets by enabling the visual layout editor mode by clicking the pencil on the top right of the Manage Dashboard screen. This gives users greater control of their dashboard in addition to building their own custom insight widgets.

Run Current Query with Actual Plan command

Another new feature we have enabled is Run Current Query with Actual Plan, which is a command that will execute the current query and return the actual execution plan with the query results. This feature area is still in-progress as we work through the best UX for integrating this command directly into the query editor. While that design work is in-progress the functionality is still available via the Command Palette and you can define a keyboard shortcut if using this feature frequently.

Contact us

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

The post The December release of SQL Operations Studio is now available appeared first on Microsoft SQL Server Blog.

We are excited to announce that SQL Operations Studio is now available in preview. SQL Operations Studio is a free, light-weight tool for modern database development and operations for SQL Server on Windows, Linux and Docker, Azure SQL Database and Azure SQL Data Warehouse on Windows, Mac or Linux machines.

Download SQL Operations Studio to get started.

It’s easy to connect to Microsoft SQL Server with SQL Operations Studio and perform routine database operations—overall lowering the learning curve for non-professional database administrators who have responsibility for maintaining their organization’s SQL-based data assets.

As more organizations adopt DevOps for application lifecycle management, developers and other non-professional database administrators find themselves taking responsibility for developing and operating databases. These individuals often do not have time to learn the intricacies of their database environment, making hard to perform even the most routine tasks. Microsoft SQL Operations Studio takes a prescriptive approach to performing routine tasks, allowing users to get tasks done fast while continuing to learn on the job.

Users can leverage their favorite command line tools (e.g. Bash, PowerShell, sqlcmd, bcp and ssh) in the integrated terminal window right within the SQL Operations Studio user interface. They can easily generate and execute CREATE and INSERT scripts for SQL database objects to create copies of their database for development or testing purposes. Database developers can increase their productivity with smart T-SQL code snippets and rich graphical experiences to create new databases and database objects (such as tables, views, stored procedures, users, logins, roles, etc.) or to update existing database objects. They also have the ability to create rich customizable dashboards to monitor and quickly detect performance bottlenecks in your SQL databases on-premises or in Azure.

SQL Operations Studio comes at an opportune time for users who use clients running macOS or Linux. Many users who use or plan to deploy SQL Server 2017, which became generally available in September 2017, also use Macs as their clients. They will now be able to use a free database development and operations tool from Microsoft that runs natively on their OS of choice.

SQL Operations Studio has been forked from Visual Studio Code with the objective to make it highly extensible. It’s built on an extensible microservices architecture and includes the SQL tools service built on .NET Core. Users will be able to download it from GitHub or Microsoft.

Eric Kang has done some great overview videos. Check out the latest one on Channel 9, below, or attend one of the Connect() pre-recorded sessions going on this week.

We hope you love this new tool.  It’s received great reviews from the community testing it in private beta and, with your feedback, we can make it even better. Join us in improving SQL Operations Studio by contributing directly at the GitHub repo.

If you have questions or would like to add comments, please use the comments section below. We would love to hear from you!

The post Announcing SQL Operations Studio for preview appeared first on Microsoft SQL Server Blog.