The October release of Azure Data Studio is now available

We are excited to announce the October release of Azure Data Studio (formerly known as SQL Operations Studio) is now available.

Download Azure Data Studio and review the Release Notes to get started.

Note: If you are currently using the preview version, SQL Operations Studio, and would like to retain your settings when you upgrade to the latest version, follow these instructions. When you download Azure Data Studio, remember to enable preview features by default on first launch, and then you can disable in settings if you dont need it otherwise you will be missing preview experiences like Query Plans, certain extension support, and more.

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. To learn more, visit our GitHub.

Azure Data Studio was announced Generally Available last month at Microsoft Ignite. If you missed the GA announcement, you can see “Azure Data Studio for SQL Server” on the SQL Server blog. You wont want to miss the great orthogonality matrix included comparing SSMS and Azure Data Studio and answers to many of your questions.

Check out this video for a general overview of Azure Data Studio.

In the Octobers version of the monthly release blog, we will be covering features shipped in the September GA release as well as what is new in the October release.

This includes:

For complete updates, refer to the Release Notes.

SQL Server 2019 Preview extension

As announced at Microsoft Ignite, one of the most exciting extensions to share in our September GA release was the release of the SQL Server 2019 Preview extension. If you were following the blog announcements, starting with SQL Server 2019 preview, SQL Server big data clusters allow you to deploy scalable clusters of SQL Server, Spark, and HDFS Docker containers running on Kubernetes.

These components are running side by side to enable you to read, write, and process big data from Transact-SQL or Spark. SQL Server big data clusters allow you to easily combine and analyze your high-value relational data with high-volume big data. To learn about all the excitement of SQL Server Big Data Clusters, follow the documentation here.

These experiences are built as an extension to Azure Data Studio. We can go into full depth about all the great capabilities this extension includes, but deep-diving into any one of these features can be a full blog post itself. Here is a high-level summary of these features, and then you can see a full demo of the features below.

  • Support for SQL Server 2019 features including big data cluster support
    • Connect to the HDFS/Spark Gateway shipped with SQL Server 2019
    • Browse HDFS, upload files, save files, and launch useful actions such as Analyze in Notebook for CSV files
    • Submit Spark jobs from the dashboard or right-click on a HDFS/Spark connection in Object Explorer
  • Azure Data Studio Notebooks
    • Create or open Notebooks using an integrated Notebook viewer. In this release the Notebook viewer supports connecting to local kernels and the SQL Server 2019 big data cluster only.
    • Use the PROSE Code Accelerator libraries in your Notebook to learn file format and data types for fast data preparation.
  • SQL Server Polybase Create External Table Wizard
    • Create an external table and its supporting metadata structures with an easy to use wizard. In this release, remote SQL Server and Oracle servers are supported.

Demo of SQL Server 2019 preview extension capabilities:

To download the extension, follow the instructions here.

Introducing the Azure Resource Explorer

As part of our goal to unify data management experiences, we have made it easier to manage your Azure subscriptions through the Azure Resource Explorer. Originally shipped as an extension, this feature is now built into the core product of Azure Data Studio.

After downloading the latest version, you will now see an Azure icon on the left bar, which you can click on to navigate to the Azure Resource Explorer.

With this feature, you can now manage your Azure SQL Server, Azure SQL Database, and the recently GAed Azure SQL Managed Instance resources easily. By clicking the filter icon to the right of the explorer, you can select which subscriptions you want to have displayed.

After drilling down to your target SQL instance through the explorer, you can then click on the plug icon next to each SQL instance to open up the connection dialog to directly connect to that resource and instantly start querying.

To learn more about the Azure Resource Explorer, check out our documentation.

SQL Server Agent extension improvements

One of our engineering focuses is to improve our first party extensions, which include SQL Server Agent, SQL Server Profiler, and SQL Server Import. As one of the first steps, we have brought a lot of UI and functionality fixes in SQL Server Agent, particularly in the Edit Job experience.

Now you can edit your Job steps, schedules, alerts, and notifications within the dialog.

If you are an avid user of SQL Server Agent, this is your chance to have a say in the new Agent experience in Azure Data Studio. You can report an issue directly on GitHub or go to Help->Report an issue to report directly from the product. Let us know your daily scenarios and how we can help empower you to use SQL Agent on Azure Data Studio daily.

To learn more about SQL Agent or how to acquire the extension, check out our documentation.

Improve Object Explorer and Query Editor connectivity robustness

As part of addressing customer reported issues, we put an emphasis on improving connectivity robustness across Object Explorer and Query Editor. In particular, queries that lose a connection will automatically attempt to reconnect.

To see a full list of the connection investments, see below:

Custom connection name option to provide alternative name

As requested on our GitHub issues page, you can now provide friendly connection names for your connections. This is particularly useful if your connection instance is an ip address, very long, or want to hide the name of the server in a public facing demo or docs.

This shows up as the last input box in the connection dialog as you can see in the screenshot below:

This will then appear in your Servers pane.

VS Code refresh from 1.23 to 1.26.1

Since Azure Data Studio forks from Visual Studio Code, our team continues to periodically refresh Azure Data Studio with stable and mature VS Code releases. This directly benefits users especially in editor and configuration experience.

The latest refresh picks up the latest changes from the July release of Visual Studio Code. This was implemented in the September release, but is still good to highlight for those coming from SQL Operations Studio.

A summary of changes:

To see the full list of changes, you can view the updates at the Visual Studio Code updates page. Be sure to view the changes in also the May and June changes.

Thank you to contributors

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

In addition to submitting issues, users can also contribute by submitting pull requests for potential quick fixes, and we welcome those submissions. Here is a shout-out to some of the customers who have submitted PRs that have been included in the product:

  • AlexFsmnFeature: Ability to add connection name (#2332)
  • AlexFsmnDisabled connection name input when connecting to a server. (#2566)
  • philoushka forcenter the icon(#2760)
  • anthonypants forTypo(#2775)
  • kstolte forFix Invalid Configuration in Launch.json(#2789)
  • kstolte forFixing a reference to SQL Ops Studio(#2788)

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

 

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

DBA essentials—SQL Server 2017 security, performance tuning, and more—inside out

Whether youre an experienced DBA with multiple certifications or just starting on your SQL Server DBA journey, you face challenges constantly for putting your organizations data to work. To turn the next-to-impossible into the practical, you need a comprehensive resource at your fingertips: SQL Server 2017 Administration Inside Out, by William Assaf, Randolph West, Sven Aelterman, and Mindy Curnutt. With this guide, you can get to know new and expanded SQL Server features like Columnstore, memory-optimized indexes, and Query Store, plus understand how to administer Microsoft Azure SQL Database or SQL Server 2017 virtual machines running on Azure.

Heres just some of what youll find in the ultimate guide to SQL Server 2017 (spoiler alertget free access to these chapters below):

Database server components

Even if you have a firm foundation in SQL Server configuration and administration, you want to keep the basics at-hand so you can easily refresh your memory. SQL Server 2017 Administration Inside Out provides a solid foundation in the infrastructure that makes up a database and how it works. The guide also offers helpful tips and best practices every DBA should know, like:

  • Keep in mind that In-Memory OLTP requires RAM overhead thats two times the size of a memory-optimized object.
  • Disable power saving everywhere. Power-saving settings in your operating system (OS) can result in poor query performance. Turn on High Performance at the OS and BIOS levels.
  • Realize that Redundant Array of Independent Disks (RAID) shouldnt take the place of performing backups. It doesnt provide 100 percent protection from data loss.
  • Ensure all TCP/IP traffic to and from SQL Server is encrypted. If youre using the Shared Memory Protocol with applications that are all on the same server, this isnt required.

Securing the server and data

Recognizing that more frequent and complex cyberattacks are targeting your environment, SQL Server 2017 Administration Inside Out covers the security capabilities of SQL Server 2017 comprehensively. The guide helps you play defense to protect your data from attack and minimize the damage should you suffer a data breach. For example, youll learn (or be reminded) that you can:

  • Prevent common SQL injection attacks by making sure that all data is escaped, sanitized, and validated before input and that all SQL Server queries are parameterized.
  • Use newer capabilities like Row-Level Security to restrict access through security policies based on group membership or execution context.
  • Start SQL Server in single-user mode or with minimal configuration and remove the offending audit if that audit shuts down your SQL Server instance.

Performance tuning SQL Server

Ensuring your database performs at top speed is one of your highest priorities. You want to have an accurate and complete understanding of performance tuning concepts, the objects typically associated with tuning SQL Server database performance, and best practices that help your databases run at top speed. In SQL Server 2017 Administration Inside Out, youll gain this critical knowledge, including:

  • The NOLOCK hint (or READ UNCOMMITTED isolation level) can return invalid data. Not only can uncommitted data be read, but committed data can be read twice or skipped. You also run the risk of returning corrupt data or finding the query has failed.
  • You can use the Query Store feature to help you analyze execution plan performance by looking at live Query Store data as it happens as well as at the history of statement execution. Query Store is one of the features that first appeared in Azure SQL Database and then moved to SQL Server on-premises.

All this infoall in one place

This is only a small sample of the in-depth information you can find in SQL Server 2017 Administration Inside Out. Take the book for a test drive by downloading a free custom excerpt, including the three full chapters previewed above.

With this excerpt, youll also get an exclusive 50-percent discount code for the full 14-chapter e-book.

Download the free excerpt from SQL Server 2017 Administration Inside Out today.

The post DBA essentials—SQL Server 2017 security, performance tuning, and more—inside out appeared first on SQL Server Blog.

Modernize your SQL Server at the PASS Summit 2018

Each year thousands of data professionals make the annual visit to Seattle, Washington for the Professional Association for SQL Server Summit. This is a community driven organization and event but Microsoft has been a major contributor, sponsor, and part of this summit since its inception.This is the second post in a series leading up to the PASS Summit 2018, to learn more about the keynotes you can reference the most recent post Join us at PASS Summit 2018 Nov 6-9, 2018.

This year myself and other members of the Microsoft SQL Server Engineering and support teams will be at the PASS Summit again in a major way with keynotes, a pre-conference session, conference sessions, theater sessions, a great booth, and the always popular Data Clinic.

We have already received great interested in our pre-conference session called Modernize Your SQL Server with Bob Ward, the Tiger Team, and CSS Escalation Engineers on Tuesday, November 6th. You can read more about this session at the PASS Summit Session webpage.

Whether you have registered or not, it is not too late to register for this pre-conference seminar. It has been awhile since I have participated in a pre-conference seminar at the PASS Summit. Im excited what this one is about. We know that many of our customers are still using legacy releases of SQL Server like 2005, 2008, and 2008R2. If you are in this situation and wondering should I make the move to something new, something more modern, then this session is for you.

This session is not just an upgrade session! Sure we will show you how to migrate but our intention is to provide you with everything you need on how to modernize. Now you can learn the capabilities of SQL Server 2016, 2017, 2019, SQL Server on Linux, containers, Azure Virtual Machine, and Azure SQL Database in a way to know what features make a migration compelling. Our day also includes pre and post migration advice to maximize your investments and architectural guidance for key areas like performance, security, and HADR (the meat and potatoes of SQL Server).

The team of folks I have lined up to train you this day is nothing short of amazing. Every person that will be there to train you all have extensive customer experience and practical knowledge. This includes members of the Tiger Team such as Amit Banerjee, Pam Lahoud, Pedro Lopes, Argenis Fernandez, Sourabh Agarwal, and Vin Yu. In addition, we want you to be able to diagnose and troubleshoot problems with new technologies in a modern way so Im bringing in some of the top talent of CSS including Suresh Kandoth and Sean Gallardy. It will be a packed day but also an opportunity when you can talk to us 1:1 about your SQL Server installation base and what and how modernization will help you.

You will walk away from this session with the resources you need including all of our content and demo scripts and examples. We look forward to everyone coming to this session and I would love to meet each and every one of you personally to talk about your modernization plans for SQL Server.

Stay tuned for future blog posts where Ill show you the other great session and content the Microsoft SQL Server team has available at the PASS 2018 Summit.

The post Modernize your SQL Server at the PASS Summit 2018 appeared first on SQL Server Blog.

Join us at PASS Summit 2018 – Nov 6-9, 2018!

Get inspired by our industry-leading keynote speakers on the Microsoft data platform at PASS Summit 2018! There has never been a more exciting time for data professionals and developers as more organizations turn to data-driven insights to stay ahead and prepare for the future. But staying up to speed in this rapidly changing data landscape is a challenge. At PASS Summit you have the unique combination of community leaders sharing their real-world experience and Microsoft product teams providing deep insights into Microsoft data solutions.

For those attending this year, weve put together a quick guide below to all the relevant Data + AI sessions and clinics that you can look forward to. Cant wait until PASS Summit? Check out the Conference Sessions to get a taste of what you wont want to miss at PASS Summit 2018.

Register today and receive a $200 off discount with code: MSLOVESPASS

Pre-Conference activities – Tuesday, November 6

Modernize your SQL Server with Bob Ward, the Tiger Team, and CSS Escalation Engineers
Speaker: Bob Ward
This session will walk you through practical options for modernizing older releases of SQL Server. There are lots of choices and the engineers at Microsoft are here to guide you and find the right solutions to help you modernize your systems. In this full day session, Bob and the Microsoft engineering team will go over the benefits of newest releases, such as SQL Server 2016 and 2017, that provide built-in features for performance, intelligent diagnostics, security, HADR, and Machine Learning now accessible on Linux and Docker containers.

Wednesday, November 7

KEYNOTE: SQL Server and Azure Data Services: Harness the future with the ultimate hybrid platform for data and AI
Speaker: Rohan Kumar, Corporate Vice President of Azure Data, Microsoft
In todays world, the forces of Cloud, Data and AI are driving innovation and ushering in the era of the intelligent cloud and intelligent edge. Microsofts goal is to bring you the best products and tools to tackle these new opportunitieshelping you build a data infrastructure that supports your organization now and into the future. Join Rohan Kumar, Corporate Vice President of Azure Data, as he demonstrates the latest advances from Microsoft across SQL Server, Azure Data Services, Business Analytics and AI. Preview new products and features and see the innovations that make Microsoft the best data partner of any provider on-premises or at cloud scale.

Highlighted Session:The Roadmap for SQL Server
Speakers: Amit Banerjee, Bob Ward, Asad Khan
SQL Server 2017 has brought to market a new modern data platform including support for Linux, Docker Containers and rich features in intelligent performance, HADR, machine learning, and graph database. Come learn about the roadmap and new functionality planned for SQL Server including intelligent query processing, data virtualization, new features for mission critical security and HADR, and new scenarios for Linux and Docker Containers.

Thursday, November 8

KEYNOTE: Two Decades of Data Innovation: Celebrate the Evolution of the Data Platform and See into the Future with Extreme Cloud-Based Data Solutions
Speaker: Raghu Ramakrishnan, CTO for Data, Microsoft
Twenty years of PASS Summit and twenty-five years of SQL Server; together weve come a very long way. Join SQL Server team past and present as they take a journey through the evolution of the Microsoft data platform into the broad ecosystem you see today. You will hear from many of your familiar friends: Connor Cunningham, Bob Ward, Lara Rubbelke, Mark Souza and a few other surprises. Then buckle-up for a deep dive with Microsoft Data Platform CTO Raghu Ramakrishnan on the internals of our next evolution in engine architecture which will form the foundation for the next 25 years of the Microsoft data platform. However you interact with data be the first to look under the hood and see the future of data straight from the Azure Data engineering team.

Over 30 sessions are from the Microsoft data platform speakers; we invite you to come hear from Microsoft and community experts.

The post Join us at PASS Summit 2018 – Nov 6-9, 2018! appeared first on SQL Server Blog.

SSMS 18.0 public preview released

We are very excited to announce the public preview of SQL Server Management Studio 18.0. SSMS has been ported to the VS 2017 Isolated Shell, which brings with it many improvements to look and feel and accessibility. This release expands platform support to keep up with the announcements at Microsoft Ignite last week and includes bug fixes and feature updates across the board.

Download your copy of SSMS 18 public preview today.

New platform and feature support

  • Support for SQL 2019
  • Support for Azure SQL Managed Instance
  • Azure SQL Database support
    • SLO/Edition
    • Support for updated Azure SQL SKUs
  • Support for Always Encrypted with secure enclaves
  • UTF-8 support on collation dialog

Shell improvements

  • SSMS is based on the new VS 2017 Isolated Shell. This means a modern shell that unlocks all the accessibility features from both SSMS and VS 2017.
  • Smaller download size (~400 MB). This is less than half of what SSMS 17.x is.
  • SSMS can be installed in a custom folder. Currently, this is only available on the command line setup. Pass the extra argument to SSMS-Setup-ENU.exe, SSMSInstallRoot = C:MyFolder
  • High DPI enabled by default.
  • Better support for multiple monitors to ensure dialogs and windows pop up on the expected monitor.
  • Isolation from SQL Engine. SSMS does not share components with SQL engine anymore. More isolation from SQL engine allows for more frequent updates.
  • Package Ids no longer needed to develop SSMS Extensions.

Existing feature improvements

  • Changed authentication mode from Storage Account Key to Azure AD.
  • AUTOGROW_ALL_FILES config option for Filegroups.
  • Removed lightweight pooling and priority boost options from GUI as these have been in the not recommended list for a long time.
  • Switched to Windows Credential Manager for connection dialog MRU passwords.
  • Exposed the backup checksum default property in the Default Settings Page under Server Properties dialog.
  • Added actual time elapsed, actual vs estimated rows under Show Plan operator node, if they are available. This will allow actual plan to be consistent with Live Query Stats plan.
  • Modified tooltip and added comment for Show Plan. Edit Query Button to indicate query text may be truncated if its over 4000 characters.
  • Added logic to display the Materializer Operator (External Select).
  • Added new show plan attribute BatchModeOnRowStoreUSed to easily identify queries that are using the batch-mode scan on rowstores feature.
  • Rehash RTO (Estimated Recovery Time) and RPO (Estimated data loss) in SSMS AlwaysOn dashboard. Additional details here, Monitor performance for Always On Availability Groups.
  • SMO
    • Scripting performance improvements
    • Extend SMO support for Resumable Index Creation
    • Added new event on SMO objects (Property Missing) to help application authors to detect SMO performance issues sooner
    • Exposed new property DefaultBackupCheckSum on the Configuration object which maps to backup checksum default in server configuration
  • Several bug fixes in the following areas in addition to crashes/hangs:
    • XEvents
    • SSMS Options
    • SSMS Editor
    • Object Explorer
    • Backup/Restore/Attach/Detach database
    • General Azure SQL DB Support

For a full list of changes, see Release Notes.

  • We have also deprecated some features, including:
  • Database Diagram
  • TSQL Debugger
  • OSQL.exe
  • Dreplay Admin UI
  • Configuration Manager tools, SQL Server Configuration and Reporting Server Configuration Manager

The product may be installed side-by-side with SSMS 17.x for testing purposes. As a reminder, the use of pre-GA software in production environments is not supported.

SSMS will continue to be the flagship tool for managing and administering SQL Server on Windows environments. We will continue to make investments to fix bugs, optimize and modernize SSMS as new features get lighted up in SQL Server. Wed love to hear from you with any questions, comments or feature suggestions.

The post SSMS 18.0 public preview released appeared first on SQL Server Blog.

SQL Server 2019: Celebrating 25 years of SQL Server Database Engine and the path forward

This post is authored by Amit Banerjee, Principal PM Manager, SQL Server andBob Ward,Principal Architect, Microsoft SQL Server Data Services.

SQL Server has provided enterprises the capability to manage all facets of their relational data. Over the years, we have increasingly seen a convergence for the need of combining heterogenous sets of relational and non-relational data to meet the needs of business scenarios. This requires setting up a unified data platform that transcends the boundaries of all types of data. Incidentally, we are also celebrating 25 years since SQL Server first shipped on Windows NT in 1993. The heart of SQL Server is mission critical performance, security, and availability and the use of our database platform in mission-critical environments is a testament to that fact. The SQL Server 2019 preview 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.

Earlier at Ignite, Microsoft announced the first public Community Technology Preview (CTP 2.0) of SQL Server 2019. For the first time, SQL Server 2019 comes with big data capabilities built-in, with Apache Spark and Hadoop Distributed File System (HDFS) in the boxextending SQL Server beyond a traditional relational database. This blog post covers the database engine features that are available in first public Community Technology Preview (CTP 2.0) of SQL Server 2019.

An Intelligent database providing Industry-leading performance

The Intelligent Query Processing suite builds on hands-free performance tuning features of Adaptive Query Processing in SQL Server 2017 like row mode memory grant feedback, batch mode on rowstore, table variable deferred compilation. We have identified common classes of query performance problems which could benefit from automatic corrective approaches during runtime based on changes in cardinality or through leveraging a feedback loop based on statistics from past executions. These are features that we have already started leveraging in Azure SQL Database and remain a top investment area for SQL Server 2019.

These are new changes to our query processor which are available with database compatibility level = 150 keeping in line with our database compatibility based upgrade promise. Database compatibility level provides an easy certification path for an existing application which helps with future upgrades to new releases where the database compatibility mode remains the same. This allows our customers to reduce the effort require to leverage capabilities in latest releases for availability, performance and security without having to worry about re-certifying the entire application on a newer release.

Persistent memory support is improved in this release with a new, optimized I/O path available for interacting with persistent memory storage. Any SQL Server file that is placed on a persistent memory device allows SQL Server to directly accesses the device, bypassing the storage stack of the operating system. This mode improves performance by significantly improving low latency input/output without any change to your application or database design. The ability for an existing database schema to leverage significant throughput gains allows existing applications with I/O bound bottlenecks.

The lightweight query profiling infrastructure is now enabled by default to provide per query operator statistics anytime and anywhere you need it. This provides the ability to look back in time and investigate query performance issues. We also decided to extend this capability to queries that are running on the server. This allows SQL Server administrators the ability to leverage Management Studios Live Query Statistics or the new DMF, sys.dm_exec_query_statistics_xml, to perform live troubleshooting of a current performance problem without needing to turn on any diagnostic data collection.

Enhanced security enabling Confidential Computing

Earlier this year, we announced Confidential Computing with Always Encrypted using Enclaves for Azure SQL Database. Now we have Always Encrypted with secure enclaves for SQL Server 2019 preview which 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 during advanced operations on encrypted data.

SQL Data Discovery and Classification is now built into the SQL Server engine with new metadata and auditing support which allows you to create solutions for key compliance requirements. We now have the ability for SQL Server catalog metadata to persist information about user-defined data classification labels.

Certificate management is now integrated into the SQL Server Configuration Manager, simplifying common tasks like deploying certificates across machines participating in a failover cluster instance or availability group. This removes the overhead of managing certificates separately on each node of the SQL Server failover cluster or availability group instance.

Mission-critical availability to keep your SQL Server running

Always On Availability Groups have been enhanced to include automatic redirection of connections based on read/write intent. This capability allows applications to be redirected to the primary replica without requiring a listener for handling scenarios where creation of a listener is not possible. This gives an opportunity for legacy applications which depend on a hard-coded server/host name but can still leverage Availability Groups on upgrade by redirection to the original replica after a failover.

High availability configurations for SQL Server running in containers can be enabled with Always On Availability Groups using Kubernetes as an orchestration layer. A Kubernetes operator deploys a Stateful Set including a container with mssql-server container and a health monitor. This introduces a tighter integration between SQL Server availability groups and Kubernetes. The operator will be available in the Microsoft Container Registry for SQL Server 2019 preview.

SQL Server Always On availability groups will support up to 5 synchronous replicas (1 primary and 4 synchronous secondary) with automatic failover support. This increases your ability to sustain simultaneous failures within or across data centers using SQL Servers high availability and disaster recovery features.

We are enhancing the capability of resumable online index DDL by allowing users to restart from the last point the rowstore index create was paused or failed. This allows you the ability to continue an online index build after an outage, database failover or even stopping the operation to free up resources on the SQL Server instance.

Clustered Columnstore indexes can now be created and rebuilt online to help improve uptime for hybrid transaction analytical processing (HTAP) environments.

SQL Server Machine Learning Services will now support clustering which allows you to have a highly available intelligent database for both OLTP and Machine Learning scenarios.

Enhancing the developer experience

We are introducing UTF-8 support, a widely used character encoding format, which can provide significant storage savings up to 50 percent for your character data. This allows you to compress your existing character data without the need to write additional routines and leverage external software to compress existing data. The ability to convert existing data to UTF-8 collations will allow existing databases to leverage this new capability for storage savings.

Enhancements to SQL Graph include match support with T-SQL MERGE and edge constraints.

We are extending the ability for SQL Server to leverage common programming languages by adding Java. We already have the ability for customers to leverage CLR, R and Python in earlier releases of SQL Server. 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. This extension is installed when you add the feature ‘Machine Learning Services (in-database)’ to your SQL Server instance. And since SQL Server on Linux uses the same database engine code, you can execute the same compiled Java classes on both SQL Server on Linux and Windows.

Machine Learning Services has several enhancements for partitioned models, and support for SQL Server on Linux. We now have the ability to process external scripts per partition which supports training many small models (one model per partition of data) instead of one large model and there by providing the ability to leverage SQL Server machine learning services across your partitions. This allows you to create a partitioned training strategy across archived data sets without having to incur the performance cost of training over all your data in a single monolithic operation.

Azure Data Studio, previously SQL Operations Studio, is now generally available. Azure Data Studio is a free tool that runs on Windows, macOS, and Linux, for managing SQL Server, Azure SQL Database, and Azure SQL Data Warehouse; wherever they’re running. SQL Server Management Studio 18.0 Preview will also be available for customers to continue managing SQL Servers with the support for SQL Server 2019 Public Preview.

Platform of choice

The preview container images of SQL Server will be available on the Microsoft Container Registry along with the new certified RHEL-based SQL Server container image available on the Red Hat Container Catalog. This allows users to leverage well known commands to setup a RHEL image with SQL Server running on it in a matter of seconds improving the ability to deploy and manage their environment where SQL Server running on Red Hat is a requirement.

We are introducing new connectors for PolyBase to external data for SQL Server, Oracle, Teradata, and MongoDB which allows you to create a unified data platform using the SQL Server database engine. We have redesigned PolyBase to allow you to connect to ODBC sources, other relational databases, NoSQL and Big Data environments which enables scenarios like building new application capabilities using SQL Server as a data hub without duplicating data and system of records.

Additional capabilities for SQL Server on Linux include distributed transactions, replication, Machine Learning Services, and OpenLDAP support. These features are driven by customer demand from customer running or evaluating SQL Server on Linux for production use.

We continue to listen to customer feedback and provide features, enhancements and innovation which help our customers run mission and business critical environments on SQL Server. Our new capabilities on SQL Server on Linux along with engine enhancements in SQL Server 2019 Preview features like columnstore statistics support for DBCC CLONEDATABASE, compression estimates for columnstore indexes, and new T-SQL built-in functions to discover details for page resource waits are examples of such customer driven engineering

We also wanted to point out that SQL Server 2008 and SQL Server 2008 R2 will be approaching end of support during July 2019. Microsoft is making options available for you to successfully modernize your data platform while staying secure on your existing environment. Please read about SQL Server 2008 and 2008 R2 End of Extended Support for more information.

Get started now

The post SQL Server 2019: Celebrating 25 years of SQL Server Database Engine and the path forward appeared first on SQL Server Blog.

SQL Server 2019 preview containers now available

Starting in SQL Server 2017 with support for Linux and containers, Microsoft has been on a journey of platform and operating system choice. With SQL Server 2019 preview, we are making it easier to adopt SQL Server in containers by enabling new HA scenarios and adding supported Red Hat Enterprise Linux container images. Today we are happy to announce the availability of SQL Server 2019 preview Linux-based container images on Microsoft Container Registry, Red Hat-Certified Container Images, and the SQL Server operator for Kubernetes, which makes it easy to deploy an Availability Group.

Customers are adopting SQL Server containers for many different purposes from local development to testing in DevOps pipelines to deployment with container orchestrators such as Kubernetes. SQL Server in containers are great because of their consistent, isolated and reliable behavior across environments, ease of use, and ease of starting and stopping. Customized content can be built on top of SQL Server containers, and run without being affected by the rest of the environment. This isolation makes SQL Server in containers ideal for test deployment scenarios as well as DevOps processes.

SQL Server 2019 on Linux and container images

SQL Server 2019 is now available on Red Hat Enterprise Linux as a Red Hat Certified Container Images and Ubuntu-based container images enabling you to take advantage of the latest SQL Server engine innovations such as new SQL Graph features, and Data Discovery and Classification. We are also making it possible to adopt SQL Server in containers with existing scenarios such as Replication and Distributed Transaction which are now part of SQL Server 2019 on Linux.

Red Hat Certified Container images

With SQL Server 2019 we continue to provide more platform choices with the addition of official SQL Server on Red Hat Enterprise Linux as a Red Hat Certified Container Image. Previously, it was possible to build and run SQL Server in these containers, and now we are making it easier to get started by making it available on Microsoft Container Registry. You can run SQL Server container on Red Hat Enterprise Linux with the all the existing SQL Server on Linux container functionality, or extend it by using this image as a base image. Either way, you can now use this image for a variety of use cases from development on local environment to deployment on OpenShift with support from Red Hat and Microsoft when 2019 is generally available.

Said Ashesh Badani, vice president and general manager, Cloud Platforms, Red Hat, Were pleased to extend our collaboration with Microsoft to bring SQL Server to the worlds leading enterprise Linux platform as a production-ready certified container. With this addition to the Red Hat Container Catalog, our customers now have ready access to a mission-critical database on a trusted, reliable, and more secure operating system thats optimized for container workloads.

For more details on how to deploy SQL Server 2019 Red Hat Certified Container Images are available here. For access to the full list of Red Hat Certified Container images, visit the Red Hat Container Catalog.

SQL Server High Availability operator

SQL Server 2019 introduces the ability to deploy SQL Server containers with Always On Availability Groups on a Kubernetes cluster. The key functionalities for creation, management and health detection of the Availability Groups is encapsulated in the SQL Server HA container image (only Ubuntu images for CTP2.0). Red Hat Enterprise Linux-based images for Kubernetes or Red Hat OpenShift clusters will be made available in a subsequent CTP release of SQL Server 2019.

For more details on deployment, management and connecting to Availability Groups on an AKS cluster in Azure refer to the documentation.

Getting started with SQL Server in containers

The post SQL Server 2019 preview containers now available appeared first on SQL Server Blog.

Introducing Microsoft SQL Server 2019 Big Data Clusters

Yesterday at the Microsoft Ignite conference, we announced that SQL Server 2019 is now in preview and that SQL Server 2019 will include Apache Spark and Hadoop Distributed File System (HDFS) for scalable compute and storage. This new architecture that combines together the SQL Server database engine, Spark, and HDFS into a unified data platform is called a big data cluster.

For 25 years, Microsoft SQL Server has been powering data-driven organizations. As the variety of types of data and the volume of that data has risen, the number of types of databases has risen dramatically. Over the years, SQL Server has kept pace by adding support for XML, JSON, in-memory, and graph data in the database. It has become a flexible database engine that enterprises can count on for industry-leading performance, high availability, and security. However, a single instance of SQL Server was never designed or built to be a database engine for analytics on the scale of petabytes or exabytes. It also was not designed for scale-out compute for data processing or machine learning, nor for storing and analyzing data in unstructured formats, such as media files.

SQL Server 2019 preview extends its unified data platform to embrace big and unstructured data by deploying multiple instances of SQL Server together with Spark and HDFS as a big data cluster.

When Microsoft added support for Linux in SQL Server 2017, it opened the possibility of deeply integrating SQL Server with Spark, the HDFS, and other big data components that are primarily Linux-based. SQL Server 2019 big data clusters take that to the next step by fully embracing the modern architecture of deploying applications even stateful ones like a database as containers on Kubernetes. Deploying SQL Server 2019 big data clusters on Kubernetes ensures a predictable, fast, and elastically scalable deployment, regardless of where it is deployed. Big data clusters can be deployed in any cloud where there is a managed Kubernetes service, such as Azure Kubernetes Service (AKS), or in on-premises Kubernetes clusters, such as AKS on Azure Stack. Built-in management services in a big data cluster provide log analytics, monitoring, backup, and high availability through an administrator portal, ensuring a consistent management experience wherever a big data cluster is deployed.

The SQL Server 2019 relational database engine in a big data cluster leverages an elastically scalable storage layer that integrates SQL Server and HDFS to scale to petabytes of data storage. The Spark engine that is now part of SQL Server enables data engineers and data scientists to harness the power of open source data preparation and query programming libraries to process and analyze high-volume data in a scalable, distributed, in-memory compute layer.

Figure 1: SQL Server and Spark are deployed together with HDFS creating a shared data lake

Data integration through data virtualization

While extract, transform, load (ETL) has its use cases, an alternative to ETL is data virtualization, which integrates data from disparate sources, locations, and formats, without replicating or moving the data, to create a single “virtual” data layer. Data virtualization enables unified data services to support multiple applications and users. The virtual data layersometimes referred to as a data huballows users to query data from many sources through a single, unified interface. Access to sensitive data sets can be controlled from a single location. The delays inherent to ETL need not apply; data can always be up to date. Storage costs and data governance complexity are minimized.

SQL Server 2019 big data clusters with enhancements to PolyBase act as a data hub to integrate structured and unstructured data from across the entire data estateSQL Server, Azure SQL Database, Azure SQL Data Warehouse, Azure Cosmos DB, MySQL, PostgreSQL, MongoDB, Oracle, Teradata, , HDFS, and more using familiar programming frameworks and data analysis tools.

Figure 2: Data sources that can be integrated by PolyBase in SQL Server 2019

In SQL Server 2019 big data clusters, the SQL Server engine has gained the ability to natively read HDFS files, such as CSV and parquet files, by using SQL Server instances collocated on each of the HDFS data nodes to filter and aggregate data locally in parallel across all of the HDFS data nodes.

Performance of PolyBase queries in SQL Server 2019 big data clusters can be boosted further by distributing the cross-partition aggregation and shuffling of the filtered query results to compute pools comprised of multiple SQL Server instances that work together.

When you combine the enhanced PolyBase connectors with SQL Server 2019 big data clusters data pools, data from external data sources can be partitioned and cached across all the SQL Server instances in a data pool, creating a scale-out data mart. There can be more than one scale-out data mart in a given data pool, and a data mart can combine data from multiple external data sources and tables, making it easy to integrate and cache combined data sets from multiple external sources.

Figure 3: Using a scale-out data pool to cache data from external data sources for better performance

A complete AI platform built on a shared data lake with SQL Server, Spark, and HDFS

SQL Server 2019 big data clusters make it easier for big data sets to be joined to the dimensional data typically stored in the enterprise relational database, enabling people and apps that use SQL Server to query big data more easily. The value of the big data greatly increases when it is not just in the hands of the data scientists and big data engineers but is also included in reports, dashboards, and applications. At the same time, the data scientists can continue to use big data ecosystem tools while also utilizing easy, real-time access to the high-value data in SQL Server because it is all part of one integrated, complete system.

Figure 4: A scalable compute and storage architecture in SQL Server 2019 big data cluster

SQL Server 2019 big data clusters provide a complete AI platform. Data can be easily ingested via Spark Streaming or traditional SQL inserts and stored in HDFS, relational tables, graph, or JSON/XML. Data can be prepared by using either Spark jobs or Transact-SQL (T-SQL) queries and fed into machine learning model training routines in either Spark or the SQL Server master instance using a variety of programming languages, including Java, Python, R, and Scala. The resulting models can then be operationalized in batch scoring jobs in Spark, in T-SQL stored procedures for real-time scoring, or encapsulated in REST API containers hosted in the big data cluster.

SQL Server big data clusters provide all the tools and systems to ingest, store, and prepare data for analysis as well as to train the machine learning models, store the models, and operationalize them.
Data can be ingested using Spark Streaming, by inserting data directly to HDFS through the HDFS API, or by inserting data into SQL Server through standard T-SQL insert queries. The data can be stored in files in HDFS, or partitioned and stored in data pools, or stored in the SQL Server master instance in tables, graph, or JSON/XML. Either T-SQL or Spark can be used to prepare data by running batch jobs to transform the data, aggregate it, or perform other data wrangling tasks.

Data scientists can choose either to use SQL Server Machine Learning Services in the master instance to run R, Python, or Java model training scripts or to use Spark. In either case, the full library of open-source machine learning libraries, such as TensorFlow or Caffe, can be used to train models.

Lastly, once the models are trained, they can be operationalized in the SQL Server master instance using real-time, native scoring via the PREDICT function in a stored procedure in the SQL Server master instance; or you can use batch scoring over the data in HDFS with Spark. Alternatively, using tools provided with the big data cluster, data engineers can easily wrap the model in a REST API and provision the API + model as a container on the big data cluster as a scoring microservice for easy integration into any application.

Importantly, this entire pipeline all happens in the context of a SQL Server big data cluster. The data never leaves the security and compliance boundary to go to an external machine learning server or a data scientists laptop. The full power of the hardware underlying the big data cluster is available to process the data, and the compute resources can be elastically scaled up and down as needed.

Figure 5: A complete AI platform: SQL Server 2019 big data cluster

Azure Data Studio is an open-source, multi-purpose data management and analytics tool for DBAs, data scientists, and data engineers. New extensions for Azure Data Studio integrate the user experience for working with relational data in SQL Server with big data. The new HDFS browser lets analysts, data scientists, and data engineers easily view the HDFS files and directories in the big data cluster, upload/download files, open them, and delete them if needed. The new built-in notebooks in Azure Data Studio are built on Jupyter, enabling data scientists and engineers to write Python, R, or Scala code with Intellisense and syntax highlighting before submitting the code as Spark jobs and viewing the results inline. Notebooks facilitate collaboration between teammates working on a data analysis project together. Lastly, the External Table Wizard simplifies the process of creating external data sources and tables, including column mappings.

Conclusion

SQL Server 2019 big data clusters are a compelling new way to utilize SQL Server to bring high-value relational data and high-volume big data together on a unified, scalable data platform. Enterprises can leverage the power of PolyBase to virtualize their data stores, create data lakes, and create scalable data marts in a unified, secure environment without needing to implement slow, costly ETL pipelines. This makes data-driven applications and analysis more responsive and productive. SQL Server 2019 big data clusters provide a complete AI platform to deliver the intelligent applications that help make any organization more successful.

Figure 6: SQL Server 2019 big data cluster summary

Getting started

The post Introducing Microsoft SQL Server 2019 Big Data Clusters appeared first on SQL Server Blog.

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.