Azure Data Studio for SQL Server

Azure Data Studio is a new cross-platform desktop environment for data professionals using the family of on-premises and cloud data platforms on Windows, MacOS, and Linux. Previously released under the preview name SQL Operations Studio, Azure Data Studio offers a modern editor experience with lightning fast IntelliSense, code snippets, source control integration, and an integrated terminal. It is engineered with the data platform user in mind, with built-in charting of query resultsets and customizable dashboards.

Research has shown that users spend an order of magnitude more time working on query editing than on any other task with SQL Server Management Studio. For that reason, Azure Data Studio has been designed to focus deeply on the functionality that is used the most, with additional experiences made available as optional extensions into the product. This allows every user to customize their environment to the workflows that they use most often. Today we are pleased to announce the GA of the product, which will continue to be released on a monthly basis.

The vision of the product is to create a unified experience across heterogenous data sources regardless of their form or location: structured or unstructured, on-premises or cloud. Azure Data Studio currently offers built-in support for SQL Server on-premises and on the cloud and Azure SQL Database, along with preview support for Azure SQL Managed Instance, Azure SQL Data Warehouse and SQL Server 2019 Big Data Clusters. Other preview experiences include Azure Data Studio Notebooks, Azure Resource Explorer, SQL Server Profiler, SQL Server Agent, SQL Server Import Wizard, and SQL Server PolyBase Create External Table Wizard. Due to the extensible nature of the product, Azure Data Studio also offers third party partners and community members to contribute their own experiences to the tool, including Redgates SQL Search extension.

We are proud to offer a preview of the first ever notebook experience for SQL Server in the Azure Data Studio SQL Server 2019 Preview Extension. Notebooks are one of the most common code development environments for data and serve multiple purposes in a modern data development workflow. Notebooks combine human readable documentation with executable code and resultsets, greatly improving the process of collaborating on data analysis. The Azure Data Studio notebook viewer uses the open source Jupyter server and file format, but adds in the modern, keyboard-focused coding environment and rich editor experience of Azure Data Studio, allowing users to write code in the language of their choice. Having a notebook embedded with Azure Data Studio allows seamless in-context operations such as launching notebook analysis on an HDFS file from the Object Explorer and connection to remote SQL Server big data clusters. In the CTP 2.0 Preview, notebooks may be run locally or against SQL Server big data clusters using Python and Scala, with additional language and endpoint support coming in a future preview, including a planned pure T-SQL notebook experience for the SQL Server user.

Azure Data Studio shares a heritage and a roadmap with SQL Server Management Studio, which has been a phenomenally successful and well-liked tool in its own right. Over the course of time, all of the management features of SQL Server Management Studio will be made available in Azure Data Studio and the two products will integrate smoothly with each other. At present, Azure Data Studio is tightly focused on the experiences around query editing and data development. Additional high-value administrative features such as backup, restore, agent job management, and server profiling are also available as extensions in Azure Data Studio. Azure Data Studio is also cross-platform, allowing users to work on their platform of choice. However, SQL Server Management Studio still offers the broadest range of administrative functions and remains the flagship tool for platform management tasks.

Azure Data Studio may be downloaded from here. You can participate in the future of the tool by entering or voting on feature suggestions, reporting bugs, or by contributing your own pull requests or extensions into the product. The team welcomes your feedback and will be adding capabilities on a monthly basis based on community requests.

When Should I Use Azure Data Studio vs SQL Server Management Studio?

Use Azure Data Studio if you:

  • Need to run on macOS or Linux
  • Are connecting to a SQL Server 2019 big data cluster
  • Spend most of your time editing or executing queries
  • Need the ability to quickly chart and visualize result sets
  • Can execute most administrative tasks via the integrated terminal using sqlcmd or Powershell
  • Have minimal need for wizard experiences
  • Do not need to do deep administrative configuration

Use SQL Server Management Studio if you:

  • Spend most of your time on database administration tasks
  • Are doing deep administrative configuration
  • Are doing security management, including user management, vulnerability assessment, and configuration of security features
  • Make use of the Reports for SQL Server Query Store
  • Need to make use of performance tuning advisors and dashboards
  • Are doing import/export of DACPACs
  • Need access to Registered Servers and want to control SQL Server services on Windows

Feature comparison

Shell Features

Feature Azure Data Studio SSMS
Azure Sign-In Yes Yes
Dashboard Yes
Extensions Yes
Integrated Terminal Yes
Object Explorer Yes Yes
Object Scripting Yes Yes
Project System Yes
Select from Table Yes Yes
Source Code Control Yes
Task Pane Yes
Theming Yes
Dark Mode Yes
Azure Resource Explorer Preview
Generate Scripts Wizard Yes
ImportExport DACPAC Yes
Object Properties Yes
Table Designer Yes

Query Editor

Feature Azure Data Studio SSMS
Chart Viewer Yes
Export Results to CSV, JSON, XLSX Yes
IntelliSense Yes Yes
Snippets Yes Yes
Show Plan Preview Yes
Client Statistics Yes
Live Query Stats Yes
Query Options Yes
Results to File Yes
Results to Text Yes
Spatial Viewer Yes
SQLCMD Yes

Operating System Support

Feature Azure Data Studio SSMS
Linux Yes
macOS Yes
Windows Yes Yes

Data Engineering

Feature Azure Data Studio SSMS
Create External Table Wizard Preview
HDFS Integration Preview
Notebooks Preview

Database Adminstration

Feature Azure Data Studio SSMS
Backup / Restore Yes Yes
Flat File Import Preview Yes
SQL Agent Preview Yes
SQL Profiler Preview Yes
Always On Yes
Always Encrypted Yes
Copy Data Wizard Yes
Data Tuning Advisor Yes
Error Log Viewer Yes
Maintenance Plans Yes
Multi-Server Query Yes
Policy Based Management Yes
PolyBase Yes
Query Store Yes
Registered Servers Yes
Replication Yes
Security Management Yes
Service Broker Yes
SQL Mail Yes
Template Explorer Yes
Vulnerability Assessment Yes
XEvent Management Yes

 

The post Azure Data Studio for SQL Server appeared first on SQL Server Blog.

SQL Server 2019 preview combines SQL Server and Apache Spark to create a unified data platform

Today at Ignite, Microsoft announced the preview of SQL Server 2019. For 25 years, SQL Server has helped enterprises manage all facets of their relational data. In recent releases, SQL Server has gone beyond querying relational data by unifying graph and relational data and bringing machine learning to where the data is with R and Python model training and scoring. As the volume and variety of data increases, customers need to easily integrate and analyze data across all types of data.

Now, for the first time ever, SQL Server 2019 creates a unified data platform with Apache SparkTM and Hadoop Distributed File System (HDFS) packaged together with SQL Server as a single, integrated solution. Through the ability to create big data clusters, SQL Server 2019 delivers an incredible expansion of database management capabilities, further redefining SQL Server beyond a traditional relational database. And as with every release, SQL Server 2019 continues to push the boundaries of security, availability, and performance for every workload with Intelligent Query Processing, data compliance tools and support for persistent memory. With SQL Server 2019, you can take on any data project, from traditional SQL Server workloads like OLTP, Data Warehousing and BI, to AI and advanced analytics over big data.

SQL Server provides a true hybrid platform, with a consistent SQL Server surface area from your data center to public cloudmaking it easy to run in the location of your choice. Because SQL Server 2019 big data clusters are deployed as containers on Kubernetes with a built-in management service, customers can get a consistent management and deployment experience on a variety of supported platforms on-premises and in the cloud: OpenShift or Kubernetes on premises, Azure Kubernetes Service (AKS), Azure Stack (on AKS) and OpenShift on Azure. With Azure Hybrid Benefit license portability, you can choose to run SQL Server workloads on-premises or in Azure, at a fraction of the cost of any other cloud provider.

SQL Server Insights over all your data

SQL Server continues to embrace open source, from SQL Server 2017 support for Linux and containers to SQL Server 2019 now embracing Spark and HDFS to bring you a unified data platform. With SQL Server 2019, all the components needed to perform analytics over your data are built into a managed cluster, which is easy to deploy and it can scale as per your business needs. HDFS, Spark, Knox, Ranger, Livy, all come packaged together with SQL Server and are quickly and easily deployed as Linux containers on Kubernetes. SQL Server simplifies the management of all your enterprise data by removing any barriers that currently exist between structured and unstructured data.

Heres how we make it easy for you to break down barriers to realized insights across all your data, providing one view of your data across the organization:

  • Simplify big data analytics for SQL Server users. SQL Server 2019 makes it easier to manage big data environments. It comes with everything you need to create a data lake, including HDFS and Spark provided by Microsoft and analytics tools, all deeply integrated with SQL Server and fully supported by Microsoft. Now, you can run apps, analytics, and AI over structured and unstructured data using familiar T-SQL queries or people familiar with Spark can use Python, R, Scala, or Java to run Spark jobs for data preparation or analytics all in the same, integrated cluster.
  • Give developers, data analysts, and data engineers a single source for all your data structured and unstructured using their favorite tools. With SQL Server 2019, data scientists can easily analyze data in SQL Server and HDFS through Spark jobs. Analysts can run advanced analytics over big data using SQL Server Machine Learning Services: train over large datasets in Hadoop and operationalize in SQL Server. Data scientists can use a brand new notebook experience running on the Jupyter notebooks engine in a new extension of Azure Data Studio to interactively perform advanced analysis of data and easily share the analysis with their colleagues.
  • Break down data silos and deliver one view across all of your data using data virtualization. Starting in SQL Server 2016, PolyBase has enabled you to run a T-SQL query inside SQL Server to pull data from your data lake and return it in a structured formatall without moving or copying the data. Now in SQL Server 2019, we’re expanding that concept of data virtualization to additional data sources, including Oracle, Teradata, MongoDB, PostgreSQL, and others. Using the new PolyBase, you can break down data silos and easily combine data from many sources using virtualization to avoid the time, effort, security risks and duplicate data created by data movement and replication. New elastically scalable data pools and compute pools make querying virtualized data lighting fast by caching data and distributing query execution across many instances of SQL Server.

“From its inception, the Sloan Digital Sky Survey database has run on SQL Server, and SQL Server also stores object catalogs from large cosmological simulations. We are delighted with the promise of SQL Server 2019 big data clusters, which will allow us to enhance our databases to include all our big data sets. The distributed nature of SQL Server 2019 allows us to expand our efforts to new types of simulations and to the next generation of astronomical surveys with datasets up to 10PB or more, well beyond the limits of our current database solutions.”- Dr. Gerard Lemson, Institute for Data Intensive Engineering and Science, Johns Hopkins University.

Enhanced performance, security, and availability

The SQL Server 2019 relational engine will deliver new and enhanced features in the areas of mission-critical performance, security and compliance, and database availability, as well as additional features for developers, SQL Server on Linux and containers, and general engine enhancements.

Industry-leading performance The Intelligent Database

  • The Intelligent Query Processing family of features builds on hands-free performance tuning features of Adaptive Query Processing in SQL Server 2017 including Row mode memory grant feedback, approximate COUNT DISTINCT, Batch mode on rowstore, and table variable deferred compilation.
  • Persistent memory support is improved in this release with a new, optimized I/O path available for interacting with persistent memory storage.
  • The Lightweight query profiling infrastructure is now enabled by default to provide per query operator statistics anytime and anywhere you need it.

Advanced security Confidential Computing

  • Always Encrypted with secure enclaves extends the client-side encryption technology introduced in SQL Server 2016. Secure enclaves protect sensitive data in a hardware or software-created enclave inside the database, securing it from malware and privileged users while enabling advanced operations on encrypted data.
  • SQL Data Discovery and Classification is now built into the SQL Server engine with new metadata and auditing support to help with GDPR and other compliance needs.
  • Certification Management is now easier using SQL Server Configuration Manager.

Mission-critical availability High uptime

  • Always On Availability Groups have been enhanced to include automatic redirection of connections to the primary based on read/write intent.
  • High availability configurations for SQL Server running in containers can be enabled with Always On Availability Groups using Kubernetes.
  • Resumable online indexes now support create operations and include database scoped defaults.

Developer experience

  • Enhancements to SQL Graph include match support with T-SQL MERGE and edge constraints.
  • New UTF-8 support gives customers the ability to reduce SQL Servers storage footprint for character data.
  • The new Java language extension will allow you to call a pre-compiled Java program and securely execute Java code on the same server with SQL Server. This reduces the need to move data and improves application performance by bringing your workloads closer to your data.
  • Machine Learning Services has several enhancements including Windows Failover cluster support, partitioned models, and support for SQL Server on Linux.

Platform of choice

  • Additional capabilities for SQL Server on Linux include distributed transactions, replication, Polybase, Machine Learning Services, memory notifications, and OpenLDAP support.
  • Containers have new enhancements including use of the new Microsoft Container Registry with support for RedHat Enterprise Linux images and Always On Availability Groups for Kubernetes.
    You can read more about whats new in SQL Server 2019 in our documentation.

SQL Server 2019 support in Azure Data Studio

Expanded support for more data workloads in SQL Server requires expanded tooling. As Microsoft has worked with users of its data platform we have seen the coming together of previously disparate personas: database administrators, data scientists, data developers, data analysts, and new roles still being defined. These users increasingly want to use the same tools to work together, seamlessly, across on-premises and cloud, using relational and unstructured data, working with OLTP, ETL, analytics, and streaming workloads.

Azure Data Studio offers a modern editor experience with lightning fast IntelliSense, code snippets, source control integration, and an integrated terminal. It is engineered with the data platform user in mind, with built-in charting of query result sets, an integrated notebook, and customizable dashboards. Azure Data Studio currently offers built-in support for SQL Server on-premises and Azure SQL Database, along with preview support for Azure SQL Managed Instance and Azure SQL Data Warehouse.

Azure Data Studio is today shipping a new SQL Server 2019 Preview Extension to add support for select SQL Server 2019 features. The extension offers connectivity and tooling for SQL Server big data clusters, including a preview of the first ever notebook experience in the SQL Server toolset, and a new PolyBase Create External Table wizard that makes accessing data from remote SQL Server and Oracle instances easy and fast.

Getting started

Find additional resources and get started today by visiting the links below:

The post SQL Server 2019 preview combines SQL Server and Apache Spark to create a unified data platform appeared first on SQL Server Blog.

Cloud Data and AI Services training roundup September 2018

To help you stay up to date on online training opportunities, were releasing a monthly list of the latest free Data and Artificial Intelligence (AI) sessions in one convenient post.

SQL Server

With SQL Server virtual machines, you can use full versions of SQL Server in the cloud without having to manage any on-premises hardware. SQL Server virtual machines also simplify licensing costs when you pay as you go. They run many different geographic regions worldwide and offer a variety of machine sizes.

As data continues its exponential growth, its increasingly important to trim costs and manage risks while ensuring that your users have uninterrupted access. Register for this upcoming session to learn how to get started with SQL Server on Azure virtual machines, migrate your on-premises database to the cloud and use built-in features such as automated backup and patching.

Intelligence (AI)

Infuse your apps, websites, and bots with intelligent algorithms to see, hear, speak, understand, and interpret your user needs through natural methods of communication. The Microsoft AI platform offers a comprehensive set of flexible AI services for any scenario and enterprise-grade AI infrastructure that runs AI workloads anywhere at scale.

Artificial intelligence is accelerating digital transformation across every industry. Join this session with AI experts to learn how to use AI to augment human ingenuity and create the next generation of intelligent applications. We will dive into the tools, infrastructure, and services available as part of the Microsoft Azure AI platform and show you how to teach your bot to use prebuilt AI capabilities in computer vision, speech, and translation.

Big Data and Analytics

Deliver better experiences and make better decisions by analyzing massive amounts of data in real time. Get the insight you need to deliver intelligent actions that improve customer engagement, increase revenue, and lower costs.

R is an increasingly popular programming language for running predictive analytics workloads. For analytics practitioners looking to scale out R-based advanced analytics to big data, Azure Databricks starts in seconds, integrates with RStudio, and automatically executes R workloads at unprecedented scale across single or multiple nodes. View this session to see how to get the ideal dataset for your needs.

The insights gathered from AI provide a competitive advantage in the digital marketplace. Watch this session from GigaOm Research and Microsoft to explore AIs impending impact on the world, learn what organizations need to do to prepare for building AI solutions, and experience how you can build a data platform to bring together all kinds of data.

The post Cloud Data and AI Services training roundup September 2018 appeared first on SQL Server Blog.

SSMS 17.9 is now available

We are excited to announce the release of SQL Server Management Studio (SSMS) 17.9!

Download SSMS 17.9 and review the Release Notes to get started.

SSMS 17.9 provides support for almost all feature areas on SQL Server 2008 through the latest SQL Server 2017, which is now generally available.

In addition to enhancements and bug fixes, SSMS 17.9 comes with several new features:

  • ShowPlan improvements
  • Azure SQL support for vCore SKUs
  • Bug Fixes

View the Release Notes for more information.

ShowPlan improvements

Graphical Showplan now shows the new row mode memory grant feedback attributes when the feature is activated for a specific plan: IsMemoryGrantFeedbackAdjusted and LastRequestedMemory added to the MemoryGrantInfo query plan XML element. For more on row mode memory grant feedback, see Books Online.

For more on row mode memory grant feedback, view the Adaptive Query Processing documentation.

Image from Adaptive Query Processing

Azure SQL support for vCore SKUs

Added support for vCore SKUs in Azure DB creation. For more information on vCore, check out the full blog.

Image from Azure SQL DB blog

Bug fixes

In SSMS 17.9, there were many bug fixes.

  • Replication Monitor
    • Fixed an issue that was causing Replication Monitor (SqlMonitor.exe) not to start (User Voice item)
  • Import Flat File Wizard
    • Fixed the link to the help page for “Flat File Wizard” dialog
    • Fixed issue where the wizard did not allow changing the destination table when the table already existed: this allows users to retry without having to exit the wizard, delete the failed table, and then re-enter the information into the wizard (User Voice item)
  • Import/Export Data-Tier Application
    • Fixed an issue (in DacFx) which was causing the import of a .bacpac could fail with a message like “Error SQL72014: .Net SqlClient Data Provider: Msg 9108, Level 16, State 10, Line 1 This type of statistics is not supported to be incremental. ” when dealing with tables with partitions defined and no indexes on the table
  • Intellisense
    • Fixed an issue where Intellisense completion was not working when using AAD with MFA.
  • Object Explorer
    • Fixed an issue where the “Filter Dialog” was displayed on random monitors instead of the monitor where SSMS was running (multi-monitor systems)
  • Azure SQL
    • Fixed an issue related to enumeration of databases in the “Available Databases” where “master” was not displayed in the dropdown when connected to a specific database.
    • Fixed an issue where trying to generate a script (“Data” or “Schema and Data”) was failing then connected to the SQL Azure DB using AAD with MFA.
    • Fixed an issue in the View Designer (Views) where it was not possible to select “Add Tables” from the UI when connected to a SQL Azure DB.
    • Fixed an issue where SSMS Query Editor was silently closing and reopening connections during MFA token renewal. This will prevent side effects unbeknownst to the user (like closing a transaction and never reopening again) from happening. The change adds the token expiration time to the properties window.
    • Fixed an issue where SSMS was not enforcing password prompts for imported MSA accounts for AAD with MFA login
  • Activity Monitor
    • Fixed an issue that was causing “Live Query Statistics” to hang when launched from Activity Monitor and SQL Authentication was used.
  • Microsoft Azure integration
    • Fixed an issue where SSMS only shows the first 50 subscriptions (Always Encrypted dialogs, Backup/Restore from URL dialogs, etc)
    • Fixed an issue where SSMS was throwing an exception (“Index out of range”) while trying to log on to a Microsoft Azure account which did not have any storage account (in Restore Backup from URL dialog)
  • Object Scripting
    • When scripting “Drop and Create”, SSMS now avoids generating dynamic T-SQL
    • When scripting a database object, SSMS now does not generate script to set database scoped configurations, if they are set to default values
  • Help
    • Fixed a long outstanding issue where “Help on Help” was not honoring the online/offline mode
    • When clicking on “Help | Community Projects and Samples” SSMS now opens the default browser that points to a Git page and shows no errors/warnings due to the old browser being used

To learn more about other bug fixes covered in this release, check the Release Notes.

Call to action

Try it out and let us know what you think! You can message us on our twitter @SQLDataTools or reach out to Ken Van Hynings twitter @SQLToolsGuy.

 

The August release of SQL Operations Studio is now available

We are excited to announce the August 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 August release is the ninth major update since the announcement. If you missed it, the July release announcement is available here.

Highlights for this release include the following.

  • Announcing the SQL Server Import extension
  • SQL Server Profiler Session management
  • New community extension: First responder kit
  • Quality of Life improvements: Connection strings
  • Bug bash galore

For complete updates, refer to the Release Notes.

Announcing the SQL Server Import extension

It all started from a simple idea: Take the #1 most used wizard in SSMS in the past year and bring this wizard to SQL Operations Studio. When we first released our Wizard and Dialog extensibility APIs in June, this was the perfect candidate to test our wizards and highlight to the community that these UI components are ready to incorporate in community extensions.

To provide some background, the Import Flat File Wizard was first released and announced on October 2017 in SSMS 17.3 (shameless plug alert: coincidentally my first SQL Server blog post and first project at Microsoft.) Outside of featuring in a Channel9 video, the wizard did not receive any additional marketing. Fast forward a few months, and it was suddenly the #1 most used wizard in SSMS. How did this happen?

A very common scenario for SQL Server users is that they simply want to take a .txt or .csv file and import it to their SQL database as a table. As much as we love the ever reliable Import and Export Wizard, for users unfamiliar with the wizard, there were many configuration options that can make first experience difficult. If a user simply wants to import a text file, how can we make a simple scenario easier? By creating a whole new wizard of course!

The Import Flat File Wizard utilizes a Microsoft Research framework known as Program Synthesis using Examples (PROSE) to import .txt and .csv files into a SQL table. It is a powerful framework for data wrangling, and it is the same technology that powers Flash Fill in Microsoft Excel and featured in many publications and demos led by Sumit Gulwani. This technology turns the Import Flat File experience into a 6 click experience to go from selecting a file and importing into your database. Clearly, incorporating PROSE into everyday database tasks is a delighter for our users, and we will continue to put investment into creating experiences with PROSE.

Logically, it made perfect sense to have an AI-powered feature be our first wizard experience in SQL Operations Studio, but our engineers were at full capacity, so we had to be a little creative to make this possible.

Every year since Satya Nadella became CEO, Microsoft holds a global, company-wide hackathon where employees spend 3 days working on any project ranging from Hack for Good projects, VP-sponsored challenges, or from a random idea on the drive back home. This was the perfect opportunity to pitch bringing the Import Flat File Wizard to SQL Operations Studio, while also promoting cross-platform and open source development to our fellow co-workers.

By the time of the Hackathon, we had 4 interns and 7 full-time employees signed up for the project. More importantly, we asked why external team members chose our project, and we were blown away by the passion the interns and external team members had for SQL Server, and how they wanted to work on projects to improve SQL Server user experience.

Starting from mockups featured on PowerPoint slides, we shared the vision of the project while quickly onboarding new team members to our tech stack. It was not the most productive first day, but we did end up finding our rhythm. Using this momentum, we iterated quickly and were nailing our checkpoint sync-ups throughout the day, but there was still a lot to get done. However, with one hour to go before the Hackathon tents closed, we completed the first SQL Server Import experience in SQL Operations Studio end-to-end. Very proud of this team for getting a shippable deliverable within the allocated Hackathon time.

Our intern, Amir Omidi, worked on the fit and finish for the wizard for the remainder of his internship, and we are grateful for his hard work.

Now, we are ready to share this extension with the community. You can get this extension from the Extensions Manager. This feature not only brings the same simplicity as the SSMS wizard, but also brings this experience cross-platform to our macOS and Linux users. You can start the wizard with the same right click experience or press Ctrl + I.

Overall, this project taught us several things:

  • Our SQL Server users love AI-assisted features, and this is the first of many AI experiences in SQL Operations Studio.
  • Interns are very talented. Invest in their growth.
  • Keep things simple. Bring our users with us on our journey.

If you have ideas of what you would like to see in this extension, let us know through our community feedback. We look forward to bringing more PROSE experiences into SQL Operations Studio in the future.

SQL Server Profiler Session management

Since the June release, we have been making improvements with SQL Server Profiler. We are excited to announce the Profiler extension now supports session management. With session management, you can now configure your most popular sessions as you can in SSMS.

To try out this feature, you will need to make an active connection to a SQL Server instance. You can then launch Profiler by clicking on a server or database in the Object Explorer and pressing Alt + P.

This will pop up a New Session dialog as shown in the gif. Here you can give an easy to remember name like Profiler and hit create. If you dont want to create a new profiler session, simply press Cancel.

To select the session you just created, simply click on the dropdown next to Start/Stop and select Profiler.

You can now start profiling your SQL Server events. Note that there is also a new Create button where you can pop up the Create Session dialog.

With template support released last month and session support in this release, we hope to continue to improve the Profiler extension for all the avid Profiler users in the SQL world. A big thank you to Madeline MacDonald for her hard work in shipping Profiler over the course of her internship.

New community extensions: First Responder Kit

Continuing our extensibility story, our marketplace now includes Brent Ozars First Responder Kit. For those unfamiliar with the First Responder Kit, this toolkit helps users understand why their SQL Server is down or slow. Specifically, there are five main scripts featured:

  • sp_blitz: Overall health check
  • sp_blitzcache: Most resource-intensive queries
  • sp_blitzfirst: Why is server slow
  • sp_blitzindex: Indexes missing or slow
  • sp_blitzwho: Queries currently running

To leverage these features, you fill first need internet connection. Then, open the command palette with Ctrl+Shift+P and type > first responder kit: import to see a list of scripts to import. Then select the script with arrow keys and press enter.

Once the scripts are loaded to the database, you can run the scripts by again opening command palette and type > first responder kit: run to view the list of available scripts to run. Then select the script with arrow keys and press enter.

A big thank you to Drew Skwiers-Koballa for using our extensibility APIs to create a SQL Operations Studio extension. Also thank you to Brent Ozar Unlimited team for having these awesome scripts easily available for the community.

In addition to having a great new extension, Drew shared his story for creating a SQL Operations Studio extension through a detailed blog post. If you are interested in leveraging extensions APIs or have a great idea for an extension, we would highly recommend checking out his blog.

Quality of Life improvements: Connection Strings

As requested by the community, we have also made it easier for you to handle connection strings in SQL Operations Studio.

Generate Connection String

If you need to quickly generate a connection string, you can follow these three steps:

  1. Open a query editor with an active connection.
  2. Open Command Palette (Ctrl+Shift+P), and type Get Current Connection String and then press Enter.
  3. Copy connection string from notification pop-up.

Note: Password will be removed from the returned string.

You can now use or share the connection string.

Populate info from Connection String

If you have a valid connection string such as from the Azure Portal, you can now copy your connection string and paste the string into the connection dialog and it will auto-populate the fields based on the connection string.

Bug bash galore

In addition to the new features, we dedicated time to fix many of the top user reported bugs.

To highlight high impact ones:

  • Cursor position no longer loses context when switching between tabs #1744
  • Script As now auto-connects to the server connection. #825
  • .sql files now are associated with SQL Operations Studio #1836

All fixed customer reported issues:

  • Parse SQL in a Query Editor window by using the Parse Syntax command
  • Save edit data scroll position when switching tabs #2129
  • View as Chart options are cut off at the bottom #1497
  • Cancel change connection disconnects current connection #1474
  • Bug: Error message when saving Excel file second (and subsequent) time #1748
  • Update document icon for Dashboard and Profiler documents #2107
  • SQL Tab DB Icon is red #387
  • Added more saveAsCsv options #2099
  • Feature Suggestion: Get Connection String for existing connection #1620
  • Agent: Enabled button to import queries from sql files #2042
  • Copy from query results grid is off by 1 column #1985
  • Add VS Code version to About dialog #1998
  • double-click not selecting @ in variable name #143
  • Typing N” autocompletes to N”’ #1850
  • Results Grid Row Indicator Zero Based #2152
  • Fix the decimal separator #1317
  • SelectBox doesn’t change color when disabled #1624
  • Save as JSON/EXCEL/CSV not work #1728
  • Shell/Dashboard: Main viewlet icons are draggable and can crash the app #1524
  • Can’t use Ctrl+C shortcut to copy from result pane #2091
  • Updating causes application icon to be removed/replaced in Windows #1285
  • Not able to expand/collapse remote file browser folder by clicking name #1578
  • sqlops.desktop [Desktop Entry] – redundant value for Name & Comment #1278
  • Edit data: cell doesn’t revert to original value on hitting Escape key #1782

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, message us on Gitter, or tweet us @SQLOpsStudio.

Cloud data and AI services training roundup August 2018

To help you stay up to date on online training opportunities, were releasing a monthly list of the latest free Data and Artificial Intelligence (AI) sessions in one convenient post.

SQL Server

Build modern applications using the language of your choice, on-premises and in the cloud, now on Windows, Linux, and Docker containers.

  • Prepare for Windows Server 2008 and SQL Server 2008 End of Support
    Support for SQL Server 2008/ 2008 R2 and Windows Server 2008 will end in July 2019 and January 2020, respectively, which means youll no longer receive security patches for these versions. When you join this session, youll learn how to migrate your applications and data, avoid business disruptions, and adopt the most current security technologies. You will also receive guidance for your migration and find resources to help you move quickly.

Azure Database services for PostgreSQL and MySQL

Azure Database Services for PostgreSQL and MySQL provide fully managed, enterprise-ready community PostgreSQL/MySQL database as a service. These community editions help you easily lift and shift to the cloud, using languages and frameworks of your choice. On top of that, you get built-in high availability and capability to scale in seconds, helping you easily adjust to changes in customer demands.

  • How Open Source Database engines help you migrate to Azure
    Learn how to take advantage of fully managed, enterprise-ready PostgreSQL and MySQL community database engines. Join us as we cover how to use Azure Database Migration Service and what incentives are in place to help you in your migration journey.

Azure Cosmos DB

Azure Cosmos DB offers the first globally distributed, multi-model database service for building planet-scale apps.

  • Controlling your application experience with Azure Cosmos DBs consistency models
    The ability to control your application experience by changing your consistency model has been lackinguntil now. Azure Cosmos DB offers five well-defined and preconfigured consistency models, helping you navigate the tradeoffs between data consistency and app availability. In this session, learn the key differences between the five consistency models, which applications are best suited for each model and how to configure the models to ensure high performance.

Big Data and analytics

Deliver better experiences and make better decisions by analyzing massive amounts of data in real time. Get the insight you need to deliver intelligent actions that improve customer engagement, increase revenue, and lower costs.

  • Making R-based analytics easier and more scalable
    R is an increasingly popular programming language for running predictive analytics workloads. If you are looking to scale out R-based advanced analytics to big data, Azure Databricks starts in seconds, integrates with RStudio, and automatically executes R workloads at unprecedented scale across single or multiple nodes. Join us to see how to get the ideal dataset for your needs and a detailed demonstration of the entire solution.

The July release of SQL Operations Studio is now available

We are excited to announce the July 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 June release is the eighth major update since the announcement. If you missed it, the June release announcement is available here.

Highlights for this release include the following.

  • SQL Server Agent preview extension Job configuration support
  • SQL Server Profiler preview extension Improvements
  • Combine Scripts Extension
  • Wizard and Dialog Extensibility
  • Social content
  • Fix GitHub Issues

For complete updates, refer to the Release Notes.

SQL Server Agent configuration

As part of our story of bringing over SSMS features and improving user experience, we are excited to introduce SQL Agent configuration support.

Summary of changes include:

  • Added view of Alerts, Operators, and Proxies and icons on left pane
  • Added dialogs for New Job, New Job Step, New Alert, and New Operator
  • Added Delete Job, Delete Alert, and Delete Operator (right-click)
  • Added Previous Runs visualization
  • Added Filters for each column name

In addition to jobs, users can now view Alerts, Operators, and Proxies through the icons on the left pane as demonstrated in the gif above.

We also made several improvements for the Job View. Previous Runs visualization can now be seen so that a user can quickly see a jobs history of past runs if they passed or failed.

This release also made it easier to find specific jobs in a large list of jobs. Imagine you had a list of 100+ jobs and you only wanted to see the failed jobs? Now you can by checking out the gif below using the filter column option.

With all the improvements in Views, we have added new dialogs so that users can now add Jobs, Alerts, and Operators without having to go to SSMS. To open each dialog, click New Job above each respective view.

For all the SQL Agent enthusiasts out there, we would love for you to try out the new SQL Server Agent experience and let us know what you like and what is still missing for you to use Agent day to day. As part of doing our engineering out in the open, we need your feedback so that we can create experiences that empower you to do your job (pun intended).

To learn more about SQL Server Agent, check out the documentation.

SQL Server Profiler improvements

With the release of SQL Server Profiler extension last month, our team has been working hard on improvements, especially making launching Profiler quickly.

Summary of changes include:

  • Added Hotkeys to quickly launch and start/stop Profiler
  • Added 5 Default Templates to view Extended Events
  • Added Server/Database connection name
  • Added support for Azure SQL Database instances
  • Added suggestion to exit Profiler when the tab is closed and Profiler is still running

As seen in this gif, you can quickly get Profiler open after making a server/database connection. With this release, we added Keyboard Shortcuts to Launch Profiler (Windows: Alt + P Mac: Ctrl+ALT+P) and Start/Stop Profiler (Windows: Alt + S Mac: Ctrl+ALT+S). From our user survey, the highest priority for users is to be able to start Profiling as quickly as possible. Now with two keyboard strokes, you can start Profiler.

In addition, Profiler now has added Default templates for five different views: Standard, TSQL, Tuning, TSQL_Locks, and TSQL_Duration. When you click on each one, a different list of columns will generate in your Profiler view so that you can focus on the areas that you are investigating. At the moment, it will reset the view each time.

In addition, each Profiler tab will show the server/database the Profiler instance is connected to. You can see the name in the top right of the above screenshot, which is localhost/Adventureworks2014.

Please let us know what you think and what you would like to see in Profiler.

Combine Scripts Extension

We have a new community extension published in our Extensions Manager. Created by Cobus Kruger, the Combine Scripts Extension for SQL Operations Studio is now available.

From the extension description: Ever needed to execute several scripts spread over several folders? Now you can select several files and folders, right click and click Combine Scripts, and generate a single combined file to execute or use any way you choose.

For those new to extensions, here are the instructions to access the Extensions Manager and download the Combine Scripts extension. For this extension, in particular, the install button will take you to a download link for the VSIX package. Download the VSIX, and then click File -> Install Extension from VSIX Package.

Dialog and Wizard extensibility

With this release, we are continuing to provide more opportunities for extension authors, which we highly encourage you to participate. The highlight for this release is we have now provided options for extension authors to incorporate Dialogs and Wizards in their extensions.

The differences between using dialogs and wizards are very similar to SSMS. Generally, use Wizards for step-by-step scenarios, and use dialogs for most other cases.

Extension authors can see the full list of Dialog and Wizard APIs.

To see this in action, check out our sample extension that includes this code.

We are excited to see what our extension authors can come up with these new extensibility points. If you arent an extension author but have ideas in mind, please feel free to share on Twitter or GitHub Issues.

Social content

Over the past month, we have seen a lot of great content about SQL Operations Studio as we monitored social media. We highly encourage the community that if you love this tool, consider using this tool in demos and blog posts. We will also make sure to share any of your content with the community through our Twitter handle (@sqlopsstudio).

If you would like to use SQL Operations Studio at sessions like SQL Saturdays or PASS Summit, feel free to reach out to our team and we can work with you. If there are any demo blockers, please submit an issue on our GitHub Issues. Our engineers will help unblock your scenarios.

With the launch of the Data Double-Click channel, our Principal PM Lead, Vicky Harp, discussed SQL Operations Studio with Scott Klein. Check out the conversation below.

In addition, Vicky was also interviewed by Joey DAntoni for Redmond Mag, covering the current state of SQL Server Tools development.

SQL Ops Studio also had a presence at OSCON in Portland this year, where Shayne Boyer shared SQL Operations Studio and mssql-cli.

Fixed GitHub Issues

Here is a summary of issues addressed:

  • #728No response to Add Connection on macOS
  • #1718Unable to connect to any data source
  • #1713Number of rows affected
  • #1843Better Table organization
  • #1847MFA Login to Azure SQL Databases
  • #1845Bug Scroll change tab query
  • #1612Results grid text display is messed up by international characters
  • #1749BUG: HTML data in a column gets interpreted
  • #1830Setting iconPath in ButtonComponent after component() is called does not change icon
  • #1789Extensibility: if you add a connection provider uninstall will never remove it from the list
  • #1799Top 10 DB Size chart does not work on ccase-sensitive instances
  • #1724Extension dialogs have stopped working
  • #1719TypeError when Connecting to Server
  • #1693Backup dialog: File browser UI is broken
  • #1817Error de Ortografia
  • #1791Sqlops Extensions: queryeditor.connect() connects to the target database, but UI does not show the editor is connected
  • #1814d.ts typo causing implicit ‘any’ type definition

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, message us on Gitter, or tweet us.

 

SQL Server 2008 end of support is the first step to tomorrow’s database

Today, Takeshi Numoto blogged about the upcoming SQL Server 2008 and 2008 R2 end of support. If youve been thinking about what to do with your SQL Server 2008 and 2008 R2 databases, youre not alone. These databases reach end of support on July 9, 2019, and many organizations have started planning in earnest for this milestone.

Which brings us to Will, a database admin at that development shop downtown. Bucking the job-hopping trends of today, Wills been a stalwart for his company, wrangling data for them since the mid-2000s. He remembers leading the installation of SQL Server 2008. Back then, connections were speedy, requests processed swiftly. Security, efficiency, and good clean design existed in abundancethere was a place for every byte and every byte was in its place. App developers loved Will because he made sure their data was right where they needed it, when they needed it. What an idyllic, peaceful time!

A lot has happened in the decade since. Over the years, Wills information transfer network has grown and evolvedforking and pooling and spidering as it has had to make new, less-efficient references. The schema, once so clear, is now muddied and confusing, obscuring paths for formerly responsive queries. Latency abounds. And lurking in every shadow? The threat of unidentified, potentially insecure, rogue requests for information. Those developers arent nearly so happy anymore. Ugh.

Fortunately, hope is not lost for Will. He can restore the organizational marvel that was yesterdays landscapeand layer on even more improvements. Yes, modernization to SQL Server 2017 or Azure SQL Database must be undertaken carefully, with planning and analysis and meticulous list-making. But its a journey worth taking. And luckily for Willand youtheres a free databasemigration guide that provides step-by-step instructions for getting from here to there.

Plus, Wills options arent limited to a single choice. The new SQL Server runs on numerous hosting environments and operating systemson-premises or in the cloud, virtual machine or Azure data services, Windows or Linux. As soon as its time to reroute those data streams, clear away the puddles, and brighten up the place, were here to help.

End of support is coming soon

As mentioned, SQL Server 2008 and 2008 R2 reach the end of support on July 9, 2019. We know it can be difficult to upgrade everything before end-of-support deadlines like this. Thus, were offering extended security updates for SQL Server 2008 when you rehost your database in Azure Virtual Machineswith no application code changes needed. Youll gain the critical patches you need to help keep your data safe for three more years after the end-of-support deadline, giving you time to plan and implement your next move. Find out all the details you need in the end-of-support blog post.

All this is to say, the future of data is bright for Will, his data-loving development team, and you.

Get started today

SQL Server 2008 and 2008 R2 are reaching end of support. What’s next?

SQL Server 2008 and 2008 R2 have had a tremendous run. But all good things come to an end, right? On July 9, 2019, Microsoft will end Extended Support, which means no more updates or support of any kind, potentially leaving you vulnerable to security and compliance issues.

The good news is, you still have plenty of time and options to avoid any heartburn caused by the technology circle of life. And well lay out all of those options for youin a webinar on July 12.

In this webinar, well show you:

  • how to migrate your applications and data to make the transition more than just an upgrade
  • how to avoid business disruptions and adopt the most current security technologies
  • the advantages of moving your legacy platforms to SQL Server 2017 and Azure
  • the range of guidance and resources available to help, no matter which path you choose

We want this transition to be as seamless and pain-free as possible for you. Register now and find out all the ways weve come up with to make that happen.

Re-platforming and modernizing your data workloads with SQL Server on Linux

This post is authored by Marko Hotti, Senior Product Marketing Manager, SQL Server.

Today, were excited to introduce a free e-book, SQL Server on Linux: A guide to re-platforming and modernizing your data workloads. This is the thirdin a series oftechnical e-books helping you get the most out of your database. With SQL Server 2017, you can harness the latest capabilities of SQL Server on the platform of your choicewhether thats Windows, Linux, or even containers.

In this guide, you getthe technical details for preparing your Linux system for SQL Server installation, managing a mixed environment, and migrating your existing data and databases. The e-book addresses flexibility, performance, and security with your data platform, whether youre a database architect, administrator, or developer working with data.

Here’s whats covered:

  • An overview of SQL Server on Linuxunderstand platform options, features, pricing, and more.
  • Getting the most out of SQL Server on Linuxreview our planning considerations to prepare your Linux system to best support your business goalssuch as performance optimization, high availability, or enterprise-grade security.
  • Tools and managementget a rundown of the many tools available from Microsoft that make managing your mixed environment easier.
  • Migrations and upgradesdiscover the tools and services available for managing your transition to SQL Server on Linux.

Microsoft is committed to enabling you to choose the best platform for your data and applications. And as deployment options expand on Windows Server, on Linux, and in virtual machines and containers,SQL Server 2017 continues the evolution of SQL Server on your platform of choiceimproving support while enhancing data management and data-driven applications.

Get your copy of the free guide today. Plus, dont miss the other e-books in the series.