The April release of SQL Operations Studio is now available

The April release of SQL Operations Studio is now available

April 26th, 2018

This post is authored by Alan Yu, Program Manager, SQL Server.

We are excited to announce the April 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 April release is the fifth major update since the announcement. If you missed the March release announcement can be viewed here.

The April Public Preview release is focused on improving our Extensibility experience with the release of new extensions as well as addressing top Github issues.

Highlights for this build include the following.

  • Public preview release of SQL Agent extension
  • Added new extensions and improved existing extensions
    • Improvements to Server Reports Extension
    • Release of SSMS Keymap extension
    • Release of AlwaysOn Insights extension
    • Release of MSSQL Instance Insights
    • Release of MSSQL Db Insights
  • Added Visual Studio Code 1.21 platform source code refresh
    • Improved large and protected file support for saving Admin protected and >256M files within SQL Ops Studio
    • Integrated Terminal splitting to work with multiple open terminals at once
    • Reduced installation on-disk file count footprint for faster installs and startup times
  • Continue to fix GitHub issues

For complete updates, refer to the Release Notes.

Preview release of SQL Agent extension

Since SQL Operations Studio was released for public preview, one of the most requested features was providing SQL Agent support. Bringing over the most popular SSMS features has always been on our roadmap, but we wanted to make sure we did this the right way. For years, customers have submitted issues that were difficult to change due to being built on old dialog and wizard frameworks. With SQL Operations Studio, we had an opportunity to bring a modern user experience to our features while maintaining the same functionality that our users are experts with.

To make this possible, the engineering team reached out to the SQL Server community to learn more about your top scenarios and get direct feedback about our initial mock-ups. This involved creating surveys, scheduling user interviews, and promoting community discussion through a demo on Youtube showing our initial prototype. With your help and the release of Extensions Manager in the March release, we have provided you an initial preview release of SQL Agent.

When you install SQL Agent from the Extension Manager, you can view the SQL Agents extension as a tab on your server dashboard. To learn how to install an extension, please view this how-to guide.

This initial release focuses on providing a great View Jobs and Job History experience. You can see a list of all jobs including color-coded successful and filed jobs, names of jobs, and error messages. To see the job history of a specific job, you simply click on that job. This view lets you see a history of past runs, and also provides the ability run or stop the job.

The next step will be to add Job Configuration functionalities, including providing support for creating a job, setting alerts, and scheduling jobs. We would love to hear your feedback about this initial release through our GitHub Issues page and also any suggestions you may have as we build out Job Configuration.

This is the first step as we continue to bring over popular features to SQL Operations Studio from SSMS. Please continue to let us know what are your must-have features and feel free to join the discussion.

Adding and improving extensions

In the March Public Preview release, we first introduced the Extension Manager to SQL Operations Studio. With this release, we introduce 5 new extensions that you can now try out in the Extensions Manager (to get started, read the how-to install extension guide).

  • SQL Agent is the extension to view and run SQL Agent jobs as described earlier.
  • SSMS Keymap ports the most popular SSMS keyboard shortcuts to SQL Operations Studio. Created by Kevin Cunnane.
  • AlwaysOn Insights is a collection of widgets designed to provide insights into AlwaysOn Availability Group components to assist DBA’s or similar in managing their environment. Created by Matt Lavery.
  • MSSQL Instance Insights is a collection of widgets designed to provide insights into MSSQL Instance to further extend the built-in default widgets. Created by Matt Lavery.
  • MSSQL Db Insights is a collection of widgets that are designed to provide insights into MSSQL Database to further extend the built-in default widgets. Created by Matt Lavery.

Over the past month, we have received a lot of emails and tweets from the community who were interested in creating their own extension. One of our engineers, Kevin Cunnane, wrote awesome blog posts that includes his process to create and publish the SSMS Keymap extension:

For additional resources to get started writing an extension, please refer to our GitHub Extensibility Wiki Guide. Feel free to also reach out to @sqlopsstudio on Twitter if you need help getting started after checking out these resources.

In addition to adding new extensions, we also brought updates to existing extensions, especially Server Reports. These changes include:

  • Fixed DB Space Usage where it threw an error when database names contain special characters
  • Changed DB Space Usage and DB Buffer Usage to show only top 10 data
  • Updated ReadMe to reference Paul Randal’s wait types library for more info about the Wait Counts widget

As we build out our extensibility story, we will continue to collaborate with the community to learn and improve the way we build and publish extensions. This is the way we envision bringing over SSMS features while also empowering the community to contribute and build their own extensions to share with the community.

Visual Studio Code Refresh

One of the most significant highlights of this release is the Visual Studio Code 1.21 platform source code refresh. Since we fork from VS Code, we do these periodic updates and also get feature improvements. The key highlights with this refresh specifically for SQL Operations Studio are:

  • Improved large and protected file support for saving Admin protected and >256M files within SQL Ops Studio
  • Integrated Terminal splitting to work with multiple open terminals at once
  • Reduced installation on-disk file count footprint for faster installs and startup times

For additional details, checkout the Visual Studio Code February Release Notes, and the Visual Studio Code January Release Notes.

Fix GitHub issues

Fixing user-reported issues may not always get as much recognition as new feature releases, but it is definitely worth calling out. If we truly want to be a community driven tool, we will continue to work on addressing your submitted issues. Here is a summary of issues addressed:

  • #37 When the chart viewer throws an error, unexpected behavior occurs.
  • #462 Feature Request: Option for Server Groups to be expanded by default
  • #1023 Add square brackets for ms_foreachdb call from flyfishingdba
  • #1050 Clear insights view before showing error
  • #1057 Restore and new query actions in explorer-widget are broken
  • #1068 Dashboard Output windows pops-up with error message for Azure DB
  • #1069 Connection Dialog shows Server Required error when initially displayed
  • #1070 Server Groups now require a double-click to expand
  • #1072 Select control background is semi-transparent
  • #1115 Fix all high contrast accessibility issues in sqlops
  • #1101 Extension fails to upgrade “Download Manually” link goes to wrong location
  • #1103 V Scroll not working on Home Tab
  • #1104 SQL extension tabs stopped working
  • #967 Expect query plan when select XML showplan in the result grid
  • #606 intellisense – Bad suggestion for ‘update’ command
  • #1048 Pre-login SSL/TLS handshake error
  • #1150 Various types of query charts throw exceptions and don’t render

Contributions and thank you

We would like to thank all our users who raised issues, and in particular the following users who helped contribute fixes:

  • flyfishingdba for Add square brackets for ms_foreachdb call (#1023)

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.

Source: Microsoft Blog – SQL Server News – The April release of SQL Operations Studio is now available

Azure SQL Database – the database engine that cannot die

April 26th, 2018

Azure SQL Database is highly available database Platform as a Service that guarantees that your database will be up and running 99.99% of time, without worrying about maintenance and downtimes. This is a fully managed SQL Server Database Engine process hosted in Azure cloud that ensures that your SQL Server database is always upgraded/patched without affecting workload. Azure SQL Database could quickly recover even in the most critical circumstances ensuring that your data is always available.

Azure platform fully manages every Azure SQL Database and guarantees no data loss and high percentage of data availability. Azure automatically handles patching, backups, replication, failure detection, underlying potential hardware, software or network failures, deploying bug fixes, failovers, database upgrades and other maintenance tasks. SQL Server engineers have implemented the best-known practices in SQL Server that is running in Azure cloud, ensuring that all the maintenance operations are completed in less than 0.01% time of your database life. This architecture is designed to ensure that committed data is never lost and that maintenance operations are performed without affecting workload. There are no maintenance windows or downtimes that should require you to stop the workload while the database is upgraded or maintained. Built-in High Availability in Azure SQL Database guarantees that database will never be single point of failure in your software architecture.

There are two high-availability models applied in Azure SQL:

  • Standard/general purpose model that provides 99.99% of availability but with some potential performance degradation during maintenance activities.
  • Premium/business critical model that provides also provides 99.99% availability with minimal performance impact on your workload even during maintenance activities.

Azure upgrades and patches underlying operating system, drivers, and SQL Server Database Engine transparently with the minimal down-time for end users. Azure SQL Database runs on the latest stable version of SQL Server Database Engine and Windows OS, and most of the users would not notice that the upgrades are performed continuously.

In the following sections will be explained differences between standard and premium availability models.

Standard availability

Standard availability refers to 99.99% SLA that is applied in Standard/Basic tiers in Azure SQL database and General Purpose tier Azure SQL Managed Instance. Availability is achieved by separation of compute and storage layers. In the standard availability model we have two layers:

  1. Stateless compute layer that is running sqlserver.exe process and contains only transient and cached data (for example – plan cache, buffer pool, column store pool). This stateless SQL Server node is operated by Azure Service Fabric that initializes process, controls health of the node, and performs failover to another place if necessary.
  2. Stateful data layer with database files (.mdf/.ldf) that are stored in Azure Premium Storage Disks. Azure Storage guarantees that there will be no data loss of any record that is placed in any database file. Azure Storage has built-in data availability/redundancy that ensures that every record in log file or page in data file will be preserved even if SQL Server process crashes.

Whenever database engine or operating system is upgraded, or if some critical issue is detected in Sql Server process, Azure Service Fabric will move the stateless SQL Server process to another stateless compute node. Data in Azure Storage layer is not affected, and data/log files are attached to newly initialized SQL Server process. Expected failover time can be measured in seconds. This process guarantees 99.99% availability, but it might have some performance impacts on heavy workload that are running due to transition time and the fact the new SQL Server node starts with cold cache.

Premium availability

Premium availability is enabled in Premium tier of Azure SQL Database and it is designed for intensive workloads that cannot tolerate any performance impact due to the ongoing maintenance operations.

In the premium model, Azure SQL database has integrated compute and storage on the single node. Both SQL Server Database Engine process and underlying mdf/ldf files are placed on the same node with locally attached SSD storage providing low latency to your workload.

High availability is implemented using standard Always ON Availability Groups. Every database is a cluster of database nodes with one primary database that is accessible for customer workload, and few secondary processes containing copies of data. Primary node constantly pushes the changes to secondary nodes in order to ensure that the data would be available on secondary replicas if primary node accidentally crashes. Failover is handled by SQL Server Database Engine – one secondary replica is becoming primary node and new secondary replica is created instead of the falling ones in order to ensure enough nodes in the cluster. Workload is redirected to the new primary node that is ready to accept requests. Failover time might be measured in milliseconds and new primary instance is immediately ready to continue serving requests.

Conclusion

Azure SQL Database is a fully managed, highly available Sql Server Database Engine process that guarantees almost no downtime for your workload even the during maintenance operation. With Azure SQL you are getting highly available database that is always patched and upgraded with minimal downtime for your workloads. If you need a database that cannot die even in the most critical situation and should be always available – Azure SQL Database might be the right solution for you.

You can find more information about the High availability in Azure SQL on official documentation.

Source: Microsoft Blog – SQL Server Storage Engine – Azure SQL Database – the database engine that cannot die

We provide excellence in Database Administration

Let's work together