sexta-feira, 28 de abril de 2017

[From Technet] SQL Server community-driven enhancements in SQL Server 2017

This post was authored by the SQL Server Tiger Team

Although SQL Server 2016 runs faster, SQL Server 2017 promises to run even faster and empower customers to run smarter with intelligent database features like the ability to run advanced analytics using Python in a parallelized and highly scalable way, the ability to store and analyze graph data, adaptive query processing, and resumable online indexing allowing customers to deploy it on the platform of their choice (Windows or Linux). SQL Server is one of the most popular DBMS among the SQL community and is a preferred choice of RDBMS among customers and ISVs owing to its strong community support. In SQL Server 2017 CTP 2.0, we have released several customer delighters and community-driven enhancements based on the learnings and feedback from customers and the community from in-market releases of SQL Server.

Smart differential backup – A new column modified_extent_page_count is introduced in sys.dm_db_file_space_usage to track differential changes in each database file of the database. The new column modified_extent_page_count will allow DBAs, the SQL community and backup ISVs to build smart backup solutions, which perform differential backup only if percentage changed pages in the database are below a threshold (say 70 to 80 percent), otherwise full database backup is performed. With a large number of changes in the database, cost and time to complete differential backup is similar to that of full database backup, so there is no real benefit to differential backup in this case; instead, it can increase database restore time. By adding this intelligence to the backup solutions, customers can now save on restore and recovery time while using differential backups.

Consider a scenario where you previously had a backup plan to take full database backup on weekends and differential backup daily. In this case, if the database is down on Friday, you will need to restore full database backup from Sunday, differential backups from Thursday and then T-log backups of Friday. By leveraging modified_extent_page_count in your backup solution, you can now take full database backup on Sunday and let’s say by Wednesday, 90 percent of pages have changed, the backup solution takes full database backup rather than differential backup since the time and resources consumed remain the same. If database is down on Friday, you will restore the full database backup from Wednesday, small differential backup from Thursday and T-log backups from Friday to restore and recover the database much quicker compared with the previous scenario. This feature was requested by customers and community in connect item 511305.

USE <database-name>
select CAST(ROUND((modified_extent_page_count*100.0)/allocated_extent_page_count,2) as decimal(2,2)) from sys.dm_db_file_space_usage
CAST(ROUND((SUM(modified_extent_page_count)*100.0)/SUM(allocated_extent_page_count),2) as decimal(2,2)) as ‘% Differential Changes since last backup from sys.dm_db_file_space_usage

Smart transaction log backup – In the upcoming release of SQL Server 2017 CTP, a new DMF sys.dm_db_log_stats(database_id) will be released, which exposes a new column log _since_last_log_backup_mb. The column log _since_last_log_backup_mb will empower DBAs, the SQL community and backup ISVs to build intelligent T-log backup solutions, which take backup based on the transactional activity on the database. This intelligence in the T-log backup solution will ensure the transaction log size doesn’t grow due to a high burst of transactional activity in short time if the T-log backup frequency is too low. It will also help avoid situations where default periodic interval for transaction log backup creates too many T-log backup files even when there is no transactional activity on the server adding to the storage, file management and restore overhead.

SELECT INTO … ON Filegroup – One of the highly voted connect items and highly requested feature requests from the SQL community to support loading tables into specified filegroups while using SELECT INTO is now made available in SQL Server 2017 CTP 2.0. SELECT INTO is commonly used in data warehouse (DW) scenarios for creating intermediate staging tables, and inability to specify filegroup was one of the major pain points to leverage and load tables in filegroups different from the default filegroup of the user loading the table. Starting SQL Server 2017 CTP 2.0, SELECT INTO T-SQL syntax supports loading a table into a filegroup other than a default filegroup of the user using the ON <Filegroup name> keyword in TSQL syntax shown below:

select * from sys.database_files
ALTER DATABASE [AdventureWorksDW2016]
    NAME= ‘FG2_Data’,
    FILENAME = ‘/var/opt/mssql/data/AdventureWorksDW2016_Data1.mdf’
SELECT * INTO [dbo].[FactResellerSalesXL] ON FG2 from [dbo].[FactResellerSales];

Tempdb setup improvements – One of the constant pieces of feedback from customers, the SQL community and the field after doing the SQL Server 2016 setup improvements is to uplift the maximum initial file size restriction of 1 GB for tempdb in setup. For SQL Server 2017, the setup will allow initial tempdb file size up to 256 GB (262,144 MB) per file with a warning to customers if the file size is set to a value greater than 1 GB and if IFI is not enabled. It is important to understand the implication of not enabling instant file initialization (IFI) where setup time can increase exponentially depending on the initial size of tempdb data file specified. IFI is not applicable to transaction log size, so specifying a larger value of transaction log can increase the setup time while starting up tempdb during setup irrespective of the IFI setting for the SQL Server service account.



Tempdb monitoring and planning – A few months back, the SQL Server Tiger team surveyed the SQL community to identify common challenges experienced by customers with tempdb. Tempdb space planning and monitoring were found to be top challenges experienced by customers with tempdb. As a first step to facilitate tempdb space planning and monitoring, a new performant DMV sys.dm_tran_version_store_space_usage is introduced in SQL Server 2017 to track version store usage per database. This new DMV will be useful in monitoring tempdb for version store usage for DBAs who can proactively plan tempdb sizing based on the version store usage requirement per database without any performance toll or overheads of running it on production servers.

Transaction log monitoring and diagnostics – One of the highly voted connect items and highly requested requests from the community is to expose transaction log VLF information in DMV. T-log space issues, high VLFs and log shrink issues are some of the common challenges experienced by DBAs. Some of our monitoring ISVs have asked for DMVs to expose VLF information and T-log space usage for monitoring and alerting. A new DMV sys.dm_db_log_info is introduced in SQL Server 2017 CTP 2.0 to expose the VLF information similar to DBCC LOGINFO to monitor, alert and avert potential T-log issues.

In addition to sys.dm_db_log_info, a new DMF sys.dm_db_log_stats(dbid) will released in the upcoming CTP release of SQL Server 2017, which will expose aggregated transaction log information per database. We will share more details on this DMF once it is released.

Improved backup performance for small databases on high-end servers – After migrating an existing in-market release of SQL Server to high-end servers, customers may experience a slowdown in backup performance when taking backups of small to medium-size databases. This happens as we need to iterate the buffer pool to drain the ongoing I/Os. The backup time is not just the function of database size but also a function of active buffer pool size. In SQL Server 2017, we have optimized the way we drain the ongoing I/Os during backup, resulting in dramatic gains for small to medium-size databases. We have seen more than 100x improvement when taking system database backups on a 2TB machine. More extensive performance testing results on various database sizes is shared below. The performance gain reduces as the database size increases as the pages to backup and backup IO take more time compared with iterating buffer pool. This improvement will help improve the backup performance for customers hosting multiple small databases on a large high-end server with large memory.



DB Size Older SQL Server releases SQL Server 2017 Improvement
8MB 107 0.4 642x
256MB 108 1 108x
1GB 110 4 27.5
8GB 139 24 5.79x
16GB 168 59 2.85x
32GB 216 108 2.12x
64GB 332 200 66%
128GB 569 469 21.32%
256GB 1055 953 10.70%

Processor information in sys.dm_os_sys_info – Another highly requested feature among customers, ISVs and the SQL community to expose processor information in sys.dm_os_sys_info is released in SQL Server 2017 CTP 2.0. The new columns will allow you to programmatically query processor information for the servers hosting SQL Server instance, which is useful in managing large deployments of SQL Server. New columns exposed in sys.dm_os_sys_info DMV are socket_count, core_count, and cores_per_socket.

Capturing query store runtime statistics in DBCC CLONEDATABASE – DBCC CLONEDATABASE has proved to be extremely useful in exporting the query store metadata for regression testing and tuning. DBCC CLONEDATABASE didn’t capture the runtime statistics, which is flushed every 15 minutes and which required customers to manually execute sp_query_store_flush_db before running DBCC CLONEDATABASE to flush and capture query store runtime statistics in database clone. Starting with SQL 2016 SP1 CU2 and SQL Server 2017 CTP 2.0, DBCC CLONEDATABASE will flush runtime statistics while cloning to avoid missing query store runtime statistics in database clone. In addition, DBCC CLONEDATABASE is further enhanced to support and clone Full-Text indexes. We also fixed several bugs caused when cloning databases using some of latest features (AlwaysEncrypted, RLS, Dynamic data masking, temporal) and released the fixes in SQL Server 2016 SP1 CU2 and SQL Server 2017 CTP 2.0.

Thanks to all SQL community members for sharing your feedback and making SQL Server a preferred choice of RDBMS for customers.

Parikshit Savjani
Senior PM, SQL Server Tiger Team
Twitter | LinkedIn
Follow us on Twitter: @mssqltiger | Team blog:

from SQL Server Blog

terça-feira, 25 de abril de 2017

[From Technet] Get to cloud faster with a new database migration service private preview

A new migration service

Last week at the Microsoft Data Amp online event, Microsoft announced a private preview for a new database migration service that will streamline the process for migrating on-premises databases to Azure. Using this new database migration service will simplify the migration of existing on-premises SQL Server, Oracle, and MySQL databases to Azure, whether your target database is Azure SQL Database or Microsoft SQL Server in an Azure virtual machine.

The automated workflow with assessment reporting guides you through the necessary changes before performing the migration. When you are ready, the service will migrate the database to SQL Server in Azure or Azure SQL Database. For an opportunity to access this service, please sign up for the limited preview.

At the same time, if you’re interested in a managed database service but need instance-level features enabling greater security, extensibility, and compatibility with SQL Server, consider signing up for the Azure SQL Database private preview as well. This new private preview of our SQL Server-based database-as-a-service can help you move hundreds of apps to the cloud with minimal changes.

You can sign up for one or both previews at

For more information about all the announcements we made yesterday, get the full scoop in this Data Amp blog. You can also watch videos from the event and other on-demand content at the Data Amp website.

from SQL Server Blog

quinta-feira, 20 de abril de 2017

[From Technet] Graph Data Processing with SQL Server 2017

SQL Server is trusted by many customers for enterprise-grade, mission-critical workloads that store and process large volumes of data. Technologies like in-memory OLTP and columnstore have also helped our customers to improve application performance many times over. But when it comes to hierarchical data with complex relationships or data that share multiple relationships, users might find themselves struggling with a good schema design to represent all the entities and relationships, and writing optimal queries to analyze complex data and relationships between the tables. SQL Server uses foreign keys and joins to handle relationships between entities or tables. Foreign keys only represent one-to-many relationships and hence, to model many-to-many relationships, a common approach is to introduce a table that holds such relationships. For example, Student and Course in a school share a many-to-many relationship; a Student takes multiple Courses and a Course is taken by multiple Students. To represent this kind of relationship one can create an “Attends” table to hold information about all the Courses a Student is taking. The “Attends” table can then store some extra information like the dates when a given Student took this Course, etc.

Over time applications tend to evolve and get more complex. For example, a Student can start “Volunteering” in a Course or start mentoring “Mentoring” others. This will add new types of relationships to the database. With this type of approach, it is not always easy to modify existing tables to accommodate evolving relationships. To analyze data connected by means of foreign keys or multiple junction tables involves writing complex queries with joins across multiple tables, and this is no trivial task. The queries can quickly get complex, resulting in complex execution plans and degraded query performance over time.

We live in an era of big data and connected information; people, machines, devices, businesses across the continents are connected to each other more than ever before. Analyzing connected information is becoming critical for businesses to achieve operational agility. Users are finding it easier to model data and complex relationships with the help of graph databases. Native graph databases have risen in popularity, being used for social networks, transportation networks, logistics, and much more. Graph database scenarios can easily be found across several business disciplines, including supply chain management, computer or telecommunication networks, detecting fraud attacks, and recommendation engines.

At Microsoft, we believe that there should be no need for our customers to turn to a new system just to meet their new or evolving graph database requirements. SQL Server is already trusted by millions of customers for mission-critical workloads, and with graph extensions in SQL Server 2017, customers get the best of both relational and graph databases in a single product, including the ability to query across all data using a single platform. Users can also benefit from other cutting-edge technologies already available in SQL Server, such as columnstore indexes, advanced analytics using SQL Server R Services, high availability, and more.

Graph extensions available in SQL Server 2017

A graph schema or database in SQL Server is a collection of node and edge tables. A node represents an entity—for example, a person or an organization—and an edge represents a relationship between the two nodes it connects. Figure 1 shows the architecture of a graph database in SQL Server.

Figure 1

Figure 1: SQL graph database architecture

Create graph objects

With the help of T-SQL extensions to DDL, users can create node or edge tables. Both nodes and edges can have properties associated to them. Users can model many-to-many relationships using edge tables. A single edge type can connect multiple type of nodes with each other, in contrast to foreign keys in relational tables. Figure 2 shows how a node and edge table are stored internally in the database. Since nodes and edges are stored as tables, most of the operations supported on tables are available on node or edge tables, too.

Figure 2

Figure 2: Person Node and Friends Edge table.

The CREATE TABLE syntax guide shows the supported syntax for creation of node and edge tables.

Query language extensions

To help search a pattern or traverse through the graph, a new MATCH clause is introduced that uses ASCII-art syntax for pattern matching and navigation. For example, consider the Person and Friends node tables shown in Figure 2; the following query will return friends of “John”:

SELECT Person2.Name
FROM Person Person1, Friends, Person Person2
WHERE MATCH(Person1-(Friends)->Person2)
AND Person1.Name = ‘John’;

The MATCH clause is taking a search pattern as input. This pattern traverses the graph from one node to another via an edge. Edges appear inside parentheses and nodes appear at the ends of the arrow. Please refer to MATCH syntax guide to find out more ways in which MATCH can be used.

Fully integrated in SQL Server engine

Graph extensions are fully integrated in the SQL Server engine. Node and edge tables are just new types of tables in the database. The same storage engine, metadata, query processor, etc., is used to store and query graph data. All security and compliance features are also supported. Other cutting-edge technologies like columnstore, ML using R Services, HA, and more can also be combined with graph capabilities to achieve more. Since graphs are fully integrated in the engine, users can query across their relational and graph data in a single system.

Tooling and ecosystem

Users benefit from the existing tools and ecosystem that SQL Server offers. Tools like backup and restore, import and export, BCP, and SSMS “just work” out of the box.


How can I ingest unstructured data?

Since we are storing data in tables, users must know the schema at the time of creation. Users can always add new types of nodes or edges to their schema. But if they want to modify an existing node or edge table, they can use ALTER TABLE to add or delete attributes. If you expect any unknown attributes in your schema, you could either use sparse columns or create a column to hold JSON strings and use that as a placeholder for unknown attributes.

Do you maintain an adjacency list for faster lookups?

No. We are not maintaining an adjacency list on every node; instead we are storing edge data in tables. Because it is a relational database, storing data in the form of tables was a more natural choice for us. In native-directed graph databases with an adjacency list, you can only traverse in one direction. If you need to traverse in the reverse direction, you need to maintain an adjacency list at the remote node too. Also, with adjacency lists, in a big graph for a large query that spawns across your graph, you are essentially always doing a nested loop lookup: for every node, find all the edges, from there find all the connected nodes and edges, and so on.

Storing edge data in a separate table allows us to benefit from the query optimizer, which can pick the optimal join strategy for large queries. Depending on the complexity of query and data statistics, the optimizer can pick a nested loop join, hash join, or other join strategies — as opposed to always using nested loop join, as in the case of an adjacency list. Each edge table has two implicit columns, $from_id and $to_id, which store information about the nodes that it connects. For OLTP scenarios, we recommend that users create indexes on these columns ($from_id, $to_id) for faster lookups in the direction of the edge. If your application needs to perform traversals in reverse direction of an edge, you can create an index on ($to_id, $from_id).

Is the new MATCH syntax supported on relational tables?

No. MATCH clause works only on graph node and edge tables.

Can I alter an existing table into a node or edge table?

No. In the first release, ALTER TABLE to convert an existing relational table into a node or edge table is not supported. Users can create a node table and use INSERT INTO … SELECT FROM to populate data into the node table. To populate an edge table from an existing table, proper $from_id and $to_id values must be obtained from the node tables.

What are some table operations that are not supported on node or edge tables?

In the first release, node or edge tables cannot be created as memory-optimized, system-versioned, or temporary tables. Stretching or creating a node or edge table as external table (PolyBase) is also not supported in this release.

How do I find a node connected to me, arbitrary number of hops away, in my graph?

The ability to recurse through a combination of nodes and edges, an arbitrary number of times, is called transitive closure. For example, find all the people connected to me through three levels of indirections or find the employee chain for a given employee in an organization. Transitive closure is not supported in the first release. A recursive CTE or a T-SQL loop may be used to work around these types of queries.

How do I find ANY Node connected to me in my graph?

The ability to find any type of node connected to a given node in a graph is called polymorphism. SQL graph does not support polymorphism in the first release. A possible workaround is to write queries with UNION clause over a known set of node and edge types. However, this workaround is good for a small set of node and edge types.

Are there special graph analytics functions introduced?

Some graph databases provide dedicated graph analytical functions like “shortest path” or “page rank.” SQL Graph does not provide any such functions in this release. Again, T-SQL loops and temp tables may be used to write a workaround for these scenarios.

Thank you for reading this post! We are excited to announce the first version of graph extensions to SQL Server. To learn more, see this article on Graph processing with SQL Server 2017. Stay tuned for more blog posts and updates on SQL graph database!

Try SQL Server 2017

Get started with the preview of SQL Server 2017 on macOS, Docker, Windows, and Linux using these links:

from SQL Server Blog

[From Technet] Resumable Online Index Rebuild is in public preview for SQL Server 2017 CTP 2.0

We are delighted to announce that Resumable Online Index Rebuild is now available for public preview in the SQL Server vNext 2017 CTP 2.0 release. With this feature, you can resume a paused index rebuild operation from where the rebuild operation was paused rather than having to restart the operation at the beginning. In addition, this feature rebuilds indexes using only a small amount of log space. You can use the new feature in the following scenarios:

  • Resume an index rebuild operation after an index rebuild failure, such as after a database failover or after running out of disk space. There is no need to restart the operation from the beginning. This can save a significant amount of time when rebuilding indexes for large tables.
  • Pause an ongoing index rebuild operation and resume it later. For example, you may need to temporarily free up system resources to execute a high priority task or you may have a single maintenance window that is too short to complete the operation for a large index. Instead of aborting the index rebuild process, you can pause the index rebuild operation and resume it later without losing prior progress.
  • Rebuild large indexes without using a lot of log space and have a long-running transaction that blocks other maintenance activities. This helps log truncation and avoid out-of-log errors that are possible for long-running index rebuild operations.

Read the articles: The following articles provide detailed and updated information about this feature:

Public preview information: For public preview communication on this topic, please contact the alias.

To try SQL Server 2017: Get started with the preview of SQL Server 2017 on macOS, Docker, Windows, and Linux.

from SQL Server Blog

quarta-feira, 19 de abril de 2017

[From Technet] Python in SQL Server 2017: enhanced in-database machine learning

We are excited to share the preview release of in-database analytics and machine learning with Python in SQL Server. Python is one of the most popular languages for data science and has a rich ecosystem of powerful libraries.

Starting with the CTP 2.0 release of SQL Server 2017, you can now bring Python-based intelligence to your data in SQL Server.

The addition of Python builds on the foundation laid for R Services in SQL Server 2016 and extends that mechanism to include Python support for in-database analytics and machine learning. We are renaming R Services to Machine Learning Services, and R and Python are two options under this feature.

The Python integration in SQL Server provides several advantages:

  • Elimination of data movement: You no longer need to move data from the database to your Python application or model. Instead, you can build Python applications in the database. This eliminates barriers of security, compliance, governance, integrity, and a host of similar issues related to moving vast amounts of data around. This new capability brings Python to the data and runs code inside secure SQL Server using the proven extensibility mechanism built in SQL Server 2016.
  • Easy deployment: Once you have the Python model ready, deploying it in production is now as easy as embedding it in a T-SQL script, and then any SQL client application can take advantage of Python-based models and intelligence by a simple stored procedure call.
  • Enterprise-grade performance and scale: You can use SQL Server’s advanced capabilities like in-memory table and column store indexes with the high-performance scalable APIs in RevoScalePy package. RevoScalePy is modeled after RevoScaleR package in SQL Server R Services. Using these with the latest innovations in the open source Python world allows you to bring unparalleled selection, performance, and scale to your SQL Python applications.
  • Rich extensibility: You can install and run any of the latest open source Python packages in SQL Server to build deep learning and AI applications on huge amounts of data in SQL Server. Installing a Python package in SQL Server is as simple as installing a Python package on your local machine.
  • Wide availability at no additional costs: Python integration is available in all editions of SQL Server 2017, including the Express edition.

Data scientists, application developers, and database administrators can all benefit from this new capability.

  • Data scientists can build models using the full datasets on the SQL Server instead of moving data to your IDE or being forced to work with samples of data. Working from your Python IDE, you can execute Python code that runs in SQL Server on the data in SQL Server and get the results in your IDE. You are no longer dependent on application developers to deploy your models for production use, which often involves translating models and scripts to a different application language. These models can be deployed to production easily by embedding them in T-SQL stored procedures. You can use any open source Python package for machine learning in SQL Server. The usage pattern is identical to the now popular SQL Server R Services.
  • Application developers can take advantage of Python-based models by simply making a stored procedure call that has Python script embedded in it. You don’t need a deep understanding of the inner workings of the Python models, or have to translate it to a line of business language in close coordination with data scientists to consume it. You can even leverage both R and Python models in the same application—they are both stored procedure calls.
  • Database administrators can enable Python-based applications and set up policies to govern how Python runtime behaves on SQL Server. You can manage, secure, and govern the Python runtime to control how the critical system resources on the database machine are used. Security is ensured by mechanisms like process isolation, limited system privileges for Python jobs, and firewall rules for network access.

The standard open source CPython interpreter (version 3.5) and some Python packages commonly used for data science are downloaded and installed during SQL Server setup if you choose the Python option in the feature tree.

Currently, a subset of packages from the popular Anaconda distribution is included along with Microsoft’s RevoScalePy package. The set of packages available for download will evolve as we move toward general availability of this feature. Users can easily install any additional open source Python package, including the modern deep learning packages like Cognitive Toolkit and TensorFlow to run in SQL Server. Taking advantage of these packages, you can build and deploy GPU-powered deep learning database applications.

Currently, Python support is in “preview” state for SQL Server 2017 on Windows only.

We are very excited about the possibilities this integration opens up for building intelligent database applications. Please watch the Python based machine learning in SQL Server presentation and Joseph Sirosh Keynote at Microsoft Data Amp 2017 event for demos and additional information. We encourage you to install SQL Server 2017. Please share your feedback with us as we work toward general availability of this technology.

Thank you!

Sumit Kumar, Senior Program Manager, SQL Server Machine Learning Services

Nagesh Pabbisetty, Director of Program Management, Microsoft R Server and Machine Learning

from SQL Server Blog

[From Technet] Delivering AI with data: the next generation of Microsoft’s data platform

This post was authored by Joseph Sirosh, Corporate Vice President, Microsoft Data Group

Leveraging intelligence out of the ever-increasing amounts of data can make the difference between being the next market disruptor or being relegated to the pages of history. Today at the Microsoft Data Amp online event, we will make several product announcements that can help empower every organization on the planet with data-driven intelligence. We are delivering a comprehensive data platform for developers and businesses to create the next generation of intelligent applications that drive new efficiencies, help create better products, and improve customer experiences.

I encourage you to attend the live broadcast of the Data Amp event, starting at 8 AM Pacific, where Scott Guthrie, executive VP of Cloud and Enterprise, and I will describe product innovations that integrate data and artificial intelligence (AI) to transform your applications and your business. You can stream the keynotes and access additional on-demand technical content to learn more about the announcements of the day.

Today, you’ll see three key innovation themes in our product announcements. The first is the close integration of AI functions into databases, data lakes, and the cloud to simplify the deployment of intelligent applications. The second is the use of AI within our services to enhance performance and data security. The third is flexibility—the flexibility for developers to compose multiple cloud services into various design patterns for AI, and the flexibility to leverage Windows, Linux, Python, R, Spark, Hadoop, and other open source tools in building such systems.

Hosting AI where the data lives

A novel thread of innovation you’ll see in our products is the deep integration of AI with data. In the past, a common application pattern was to create statistical and analytical models outside the database in the application layer or in specialty statistical tools, and deploy these models in custom-built production systems. That results in a lot of developer heavy lifting, and the development and deployment lifecycle can take months. Our approach dramatically simplifies the deployment of AI by bringing intelligence into existing well-engineered data platforms through a new computing model: GPU deep learning. We have taken that approach with the upcoming release of SQL Server, and deeply integrated deep learning and machine learning capabilities to support the next generation of enterprise-grade AI applications.

So today it’s my pleasure to announce the first RDBMS with built-in AIa production-quality Community Technology Preview (CTP 2.0) of SQL Server 2017. In this preview release, we are introducing in-database support for a rich library of machine learning functions, and now for the first time Python support (in addition to R). SQL Server can also leverage NVIDIA GPU-accelerated computing through the Python/R interface to power even the most intensive deep-learning jobs on images, text, and other unstructured data. Developers can implement NVIDIA GPU-accelerated analytics and very sophisticated AI directly in the database server as stored procedures and gain orders of magnitude higher throughput. In addition, developers can use all the rich features of the database management system for concurrency, high-availability, encryption, security, and compliance to build and deploy robust enterprise-grade AI applications.

SQL Server 2017

We have also released Microsoft R Server 9.1, which takes the concept of bringing intelligence to where your data lives to Hadoop and Spark, as well as SQL Server. In addition to several advanced machine learning algorithms from Microsoft, R Server 9.1 introduces pretrained neural network models for sentiment analysis and image featurization, supports SparklyR, SparkETL, and SparkSQL, and GPU for deep neural networks. We are also making model management easier with many enhancements to production deployment and operationalization. R Tools for Visual Studio provides a state-of-the-art IDE for developers to work with Microsoft R Server. An Azure Microsoft R Server VM image is also available, enabling developers to rapidly provision the server on the cloud.


In the cloud, Microsoft Cognitive Services enable you to infuse your apps with cognitive intelligence. Today I am excited to announce that the Face API, Computer Vision API, and Content Moderator are now generally available in the Azure Portal. Here are some of the different types of intelligence that cognitive services can bring to your application:

  • Face API helps detect and compare human faces, organize faces into groups according to visual similarity, and identify previously tagged people in images.
  • Computer Vision API gives you the tools to understand the contents of any image: It creates tags that identify objects, beings like celebrities or actions in an image, and crafts coherent sentences to describe it. You can now detect landmarks and handwriting in images. Handwriting detection remains in preview.
  • Content Moderator provides machine-assisted moderation of text and images, augmented with human review tools.

Azure Data Lake Analytics (ADLA) is a breakthrough serverless analytics job service where you can easily develop and run massively parallel petabyte-scale data transformation programs that compose U-SQL, R, Python, and .NET. With no infrastructure to manage, you can process data on demand, scale instantly, and pay per job only. Furthermore, we’ve incorporated the technology that sits behind the Cognitive Services inside U-SQL directly as functions. Now you can process massive unstructured data, such as text/images, extract sentiment, age, and other cognitive features using Azure Data Lake, and query/analyze these by content. This enables what I call “Big Cognition—it’s not just extracting one piece of cognitive information at a time, and not just about understanding an emotion or whether there’s an object in an individual image, but rather it’s about integrating all the extracted cognitive data with other types of data, so you can perform powerful joins, analytics, and integrated AI.

Azure Data Lake Store (ADLS) is a no-limit cloud HDFS storage system that works with ADLA and other big data services for petabyte-scale data. We are announcing the general availability of Azure Data Lake Analytics and Azure Data Lake Store in the Azure North Europe region.

Yet another powerful integration of data and AI is the seamless integration of DocumentDB with Spark to enable machine learning and advanced analytics on top of globally distributed data. To recap, DocumentDB is a unique, globally distributed, limitless NoSQL database service in Azure designed for mission-critical applications. Designed as such from the ground up, it allows customers to distribute their data across any number of Azure regions worldwide, guarantees low read and write latencies, and offers comprehensive SLAs for data-loss, latency, availability, consistency, and throughput. You can use it as either your primary operational database or as an automatically indexed, virtually infinite data lake. The Spark connector understands the physical structure of DocumentDB store (indexing and partitioning) and enables computation pushdown for efficient processing. This service can significantly simplify the process of building distributed and intelligent applications at global scale.


I’m also excited to announce the general availability of Azure Analysis Services. Built on the proven business intelligence (BI) engine in Microsoft SQL Server Analysis Services, it delivers enterprise-grade BI semantic modeling capabilities with the scale, flexibility, and management benefits of the cloud. Azure Analysis Services helps you integrate data from a variety of sources—for example, Azure Data Lake, Azure SQL DW, and a variety of databases on-premises and in the cloud—and transform them into actionable insights. It speeds time to delivery of your BI projects by removing the barrier of procuring and managing infrastructure. And by leveraging the BI skills, tools, and data your team has today, you can get more from the investments you’ve already made.

Stepping up performance and security

Performance and security are central to databases. SQL Server continues to lead in database performance benchmarks, and in every release we make significant improvements. SQL Server 2016 on Windows Server 2016 holds a number of records on the Transaction Processing Performance Council (TPC) benchmarks for operational and analytical workload performance, and SQL Server 2017 does even better. I’m also proud to announce that the upcoming version of SQL Server will run just as fast on Linux as on Windows, as you’ll see in the newly published 1TB TPC-H benchmark world record nonclustered data warehouse performance achieved with SQL Server 2017 on Red Hat Enterprise Linux and HPE ProLiant hardware.

SQL Server 2017 will also bring breakthrough performance, scale, and security features to data warehousing. With up to 100x faster analytical queries using in-memory Columnstores, PolyBase for single T-SQL querying across relational and Hadoop systems, capability to scale to hundreds of terabytes of data, modern reporting, plus mobile BI and more, it provides a powerful integrated data platform for all your enterprise analytics needs.

In the cloud, Azure SQL Database is bringing intelligence to securing your data and increasing database performance. Threat Detection in Azure SQL Database works around the clock, using machine learning to detect anomalous database activities indicating unusual and potentially harmful attempts to access or exploit databases. Simply turning on Threat Detection helps customers make databases resilient to the possibility of intrusion. Other features of Azure SQL Database such as auto-performance tuning automatically implement, tune, and validate performance to guarantee the most optimal query performance. Together, our intelligent database management features help make your database more secure and faster automatically, freeing up scarce DBA capacity for more strategic work.

Simple, flexible multiservice AI solutions in the cloud

We are very committed to simplifying the development of AI systems. Cortana Intelligence is a collection of fully managed big data and analytics services that can be composed together to build sophisticated enterprise-grade AI and analytics applications on Azure. Today we are announcing Cortana Intelligence solution templates that make it easy to compose services and implement common design patterns. These solutions templates have been built on best practice designs motivated by real-world customer implementations done by our engineering team, and include Personalized Offers (for example, for retail applications), Quality Assurance (for example, for manufacturing applications), and Demand Forecasting. These templates accelerate your time to value for an intelligent solution, allowing you to deploy a complex architecture within minutes, instead of days. The templates are flexible and scalable by design. You can customize them for your specific needs, and they’re backed by a rich partner ecosystem trained on the architecture and data models. Get started today by going to the Azure gallery for Cortana Intelligence solutions.


Also, AppSource is a single destination to discover and seamlessly try business apps built by partners and verified by Microsoft. Partners like KenSci have already begun to showcase their intelligent solutions targeting business decision-makers in AppSource. Now partners can submit Cortana Intelligence apps at AppSource “List an app” page.

Cross-platform and open source flexibility

Whether on-premises or in the cloud, cross-platform compatibility is increasingly important in our customers’ diverse and rapidly changing data estates. SQL Server 2017 will be the first version of SQL Server compatible with Windows, Linux, and Linux-based container images for Docker. In addition to running on Windows Server, the new version will also run on Red Hat Enterprise Linux, SUSE Enterprise Linux Server, and Ubuntu. It can also run inside Docker containers on Linux or Mac, which can help your developers spend more time developing and less on DevOps.

Getting started

It has never been easier to get started with the latest advances in the intelligent data platform. We invite you to join us to learn more about SQL Server 2017 on Windows, Linux, and in Linux-based container images for Docker; Cognitive Services for smart, flexible APIs for AI; scalable data transformation and intelligence from Azure Data Lake Store and Azure Data Lake Analytics; the Azure SQL Database approach to proactive threat detection and intelligent database tuning; new solution templates from Cortana Intelligence; and precalibrated models for Linux, Hadoop, Spark, and Teradata in R Server 9.1.

Join our Data Amp event to learn more! You can go now to the Microsoft Data Amp online event for live coverage starting at 8 AM Pacific on April 19. You’ll also be able to stream the keynotes and watch additional on-demand technical content after the event ends. I look forward to your participation in this exciting journey of infusing intelligence and AI into every software application.

from SQL Server Blog

[From Technet] Introducing Microsoft R Server 9.1 release

This post is authored by Nagesh Pabbisetty, Partner Director of Program Management at Microsoft.

Expert data scientists are adopting Advanced Analytics and Machine Learning at a rising rate. The adoption of these technologies is increasingly possible, thanks to enterprise-grade capabilities that can operate in the environments where data lives, making it easier to infuse intelligence into mission-critical business applications. Enterprises can turn to Microsoft to integrate Advanced Analytics and Machine Learning in into enterprise infrastructure and support the SLAs they have come to expect.  At Microsoft, our mission has been to make this vision of ambient intelligence a reality for our customers. We took the first step with Microsoft R Server 9.0,  and we are continuing that innovation with this Microsoft R Server 9.1 release.

You can immediately download Microsoft R Server 9.1 from MSDN and Visual Studio Dev Essentials. It comes packed with tons of value built on top of the latest open source R engine that makes R enterprise-class. Also check out RClient for Windows and R Client for Linux.

State of the Art Machine Learning

Bring Machine Learning to where your data is

With Microsoft R Server 9.0 release, we provided Machine Learning (ML) algorithms battle-tested by Microsoft as MicrosoftML package, available as a part of SQL Server R Services and Microsoft R Server  9.0 on Windows. We have now made these MicrosoftML algorithms portable and distributed to run on Linux, Windows, and the most popular distributions of Hadoop — Cloudera, Hortonworks, MapR, in addition to SQL Server 2016: Fast linear with L1 and L2 regularization, Fast boosted decision tree, Fast random forest, Logistic regression, with support for L1 and L2 regularization, GPU-accelerated Deep Neural Networks (DNNs) with convolutions, Binary classification using a One-Class Support Vector Machine. This blog demonstrates the use of Microsoft ML algorithms on Hadoop and Spark.

Pre-trained Cognitive Models

We make it easy for enterprises to infuse intelligence into their Line of Business applications. Conventional methods require significant investments of time and effort to hand-craft Machine Learning models from scratch. Harnessing decades of work on cognitive computing in the context of Bing, Office 365 and Xbox, we are delivering the first installment of pre-trained cognitive models that accelerate time to value. Further, these models can be re-trained and optimized for your business.

We now offer a Sentiment Analysis pre-trained cognitive model, using which you can assess the sentiment of an English sentence/paragraph with just a few lines of code. With the Image Featurizer pre-trained cognitive model, you can derive up to 5,000 features on a given image, and use that to compare similarity between two images. This blog shows you how to benefit from the power of image featurizers and more details of Sentiment Analysis are covered in this blog.

Combining the best of Microsoft Innovation and Open Source

We are delivering on the promise of embracing the best of open source, and pairing it with the best of Microsoft innovation. With this release, within the same R script, you can mix and match functions from RevoScaleR and Microsoft ML packages with popular open source packages like SparklyR and through it, H2O. Refer to this blog for examples on how to get the best of both worlds!

Optimized Algorithm for Pleasingly Parallel

One of the most popular advanced analytics use cases is Pleasingly Parallel where you run massively parallel computations on partitions that are grouped by one or more attributes. These embarrassingly parallel use cases are common across industries:

  • Life sciences simulations to identify the best drug for a given situation
  • Portfolio analysis to identify the right investment for each portfolio
  • Utilities to forecast energy consumption for each cohort
  • Shipping to forecast demand for various container types

We have generalized the pattern and provided a highly performant, simple, and flexible RxExecBy() function within RevoScaleR, to address these use cases. Furthermore, this function is portable across all platforms that support Microsoft R Server — Windows, Linux, Hadoop, SQL Server. More details on how to choose the best algorithm for Pleasingly Parallel use-cases are available here.

This release also includes support for Optimized Row Columnar (ORC) file format which provides a highly efficient way to store Hive data, and distributed merge for Spark compute context, RxMerge().

Enterprise-Grade Operationalization

We recognize that easy, secure, and high-performance operationalization is essential for Tier-1 enterprises, at scale, to derive maximum value from their analytics investments. Microsoft R Server 9.1 release continues strengthening the power of operationalization. See blog for more details.

  • Real time web services: realize 10X to 100X boost in scoring performance, scoring speeds at <10ms. Currently on Windows platform; other platforms will be supported soon.
  • Role Based Access Control: enables admins to control who can publish, update, delete or consume web services
  • Asynchronous batch processing: speed up the scoring performance for the web services with large input data sets and long-running jobs
  • Asynchronous remote execution: run scripts in background mode on a remote server, without having to wait for the job to complete
  • Dynamic scaling of operationalization grid with Azure VMs: easily spin up a set of R Server VMs in Azure, configure them as a grid for operationalization, and scale it up and down based on CPU / Memory usage

SQL Server R Services

The innovations in Microsoft R Server 9.1 are available to SQL Server 2016 customers; an easy upgrade of R services in SQL Server 2016 as described in this doc and in this blog post, is all you need. The machine learning and pleasingly parallel enhancements listed in the previous section are fully supported on SQL server as well. SQL Server is the first database in the world that has in-database Machine Learning!

Real-time scoring

With R Services in SQL Server 2016, we set the industry benchmark for high throughput scoring at 1 Million predictions per second. Now, we have improved single row scoring performance significantly, up to two orders of magnitude better than earlier versions. Real-time scoring is supported on models trained using both RevoScaleR and MicrosoftML algorithms & transforms. With this release, SQL Server understands these models natively and scores inputs without the need of R interpreter and associated overhead, delivering significantly better performance.

Flexible R package management

In 9.0.1 release of Microsoft R Server we added functionality in RevoScaleR package that enables users to install, uninstall and manage packages on SQL Server without requiring administrative access to the SQL Server machine. Data scientists and other non-admin users can install packages in specific databases, user or group scope. In this release, we have added the rxSyncPackages API to ensure that the user-installed packages are not lost if the SQL Server node goes down or if the database is migrated. The list of packages and the permissions are maintained in a server table and this API ensures that the required packages are installed on the file system.

SQL Server Machine Learning Services – Python Preview

SQL Server 2016 brought you in-database analytics with SQL Server R Services. With CTP 1 of SQL Server 2017, MicrosoftML provided in-database Machine Learning.  CTP 2.0 of SQL Server 2017 brings you SQL Server Machine Learning Services that embraces both R and Python. Data Scientists can now choose from a huge collection of analytics and machine learning algorithms across R and Python communities to execute in-database and get their job done much more effectively. CTP 2.0 enables collaboration between traditional data scientists with strong R backgrounds and computer scientists with strong Python backgrounds, to deliver the best business ROI.

Additionally, the real-time scoring and flexible package management functionality listed above for SQL Server R Services is also available in the CTP2 release as part of Machine Learning services.

Customer & Partner Engagement

Engaging with Customers

“Working with Microsoft R Server for our data science needs at eToro has been a key factor in our success. The tools are appropriate for all levels of data scientist skills from beginners to seasoned professionals. Using Microsoft R Server, we were able to quickly run large scale statistical simulations in a distributed manner that ensured the robustness of our machine learning models. This partnership was instrumental in meeting our business goals and we look forward to using the continuing innovation coming out on Microsoft R Server!” — Moti Goldklang, Director of Trading Systems, eToro.

We are committed to finding more ways for our customers to connect with us, to understand how to get the most out of their investments and provide feedback to influence product direction.  We offer a variety of ways customers can engage closely with Microsoft and provide product feedback.

R Advisors: We launched R Advisors where select customers can get access to preview releases and also have the opportunity to provide feedback before we release. Azure Advisors is an opt-in program that you can sign up as a member of your organization.
User Voice: As a customer focus team, we are interested in listening to your feedback and to help us steer our product capability we are launching User Voice for Microsoft R today. You can partake in discussion and cast your vote on features that you’d like to see us enable. We are listening!

Authoring Tools from Microsoft and Partners

I am happy to announce that we have a number options to help you develop Microsoft R based applications, both from Microsoft and from our partners.  R Tools for Visual Studio (RVTS) is now Generally Available, and brings support for Microsoft R into Visual Studio. In addition, we also have Python Tools for Visual Studio (PVTS) for your Python development. In addition, we have worked with MicroStrategy, Alteryx and KNIME, and, augmented open source Rattle, to give you more choices.

Microsoft has been contributing to the R Community to ensure that there is an open source WYSIWYG tool to do big data analytics in the community. We have enhanced the popular Rattle package to support Microsoft R Server capabilities. You can download the latest, and stay abreast with the developments.

With Alteryx Designer 11.0, a self-service analytics workflow tool from Alteryx, business analysts and data scientists can use the drag-and-drop interface for many of the big-data statistical modeling tools included with Microsoft R Server and SQL Server R Services. More details are in this blog post.

Microsoft and KNIME have partnered to bring Microsoft R capabilities to the KNIME platform. “KNIME has added the option to reach out to Microsoft R from KNIME Analytics Platform to make a scalable and enterprise ready R integration part of any KNIME workflow” says Michael Berthold, CEO of KNIME. Here is an example of how this works and you can see it in action here.

MicroStrategy has made Microsoft R runtime accessible from MicroStrategy Desktop. “MicroStrategy is embracing Microsoft R in our analytics platform tools to bring the power of advanced analytics and machine learning to our customers. We just announced this at MicroStrategy World and you can read more about this here”, says Sandipto Banerjee, VP Data Group & Advanced Technologies, MicroStrategy.

Getting Started

The best place to get started is our comprehensive documentation site, which introduces concepts, platforms, features, code samples, and how-tos. Our vibrant blogs  R Server Blog that we launched earlier this year on all thing R,  R Tiger Team covering deep technical insights on Microsoft R Server, and Revolutions R Blog provide a plethora of articles, tips and tricks for novices and experts alike. I welcome you to check these out and leave us your comments.

Check out the eBook on SQL Server 2016 that covers what’s new in SQL Server, installing & configuring R Services, and how to develop full applications through walkthroughs.

Want to get certified and show your mastery in data science? We have your covered via several courses at Microsoft LearnAnalytics and Microsoft Academy! We have several or training partners that can help you train your teams on advanced analytics and machine learning!

Solution Templates

Check out the R Solution Templates that will walk you through how to develop a solution using Microsoft R Server, from beginning to end. In addition, with the click of a button, you can deploy these templates to an AzureVM and see the entire application in action. You can follow the links to GitHub and use the code as a starting point for your own solution, and accelerate time to value!

In our last release, we provided Solutions Template for Campaign Optimization using SQL Server R Services, to make it easy for Data Engineers and Data Scientists to create full solutions based on SQL Server 2016.  Now, we have added a solution template for Azure HDInsight platform on Spark compute context.  In the words of Anindya Palit, EVP Affine Analytics, “Partnering with Microsoft allowed Affine’s extensive analytics experience in marketing to be transformed into a solution for optimizing lead generation through Campaign Optimization. We were able to quickly ramp up and build the solution utilizing the power of R Services within SQL Server.”

Hospital Length Of Stay (LOS) is the latest solution template built on SQL Server R Services. Dr. Greg Mckelvey, Head of Clinical Insights, KenSci, says “The Hospital ‘Length of Stay Prediction’ solution shows how you can build a potentially life-saving machine learning solution by leveraging the power of R within SQL Server. By predicting how long an admitted patient is likely to stay at the hospital based on clinical history, labs and vital, the solution enables doctors and nurses to better manage patient flow and coordinate post-discharge patient care.”

Azure VMs

Microsoft R Server 9.1 will be released as Azure VMs in Azure Marketplace, Data Science VMs, and on Azure HDInsight. VMs were available on CentOS 7.2 and Ubuntu 16.04. Now, we have added support for RHEL 7.2, and made all VMs available in China.

Microsoft R Client

With our current release, we are delivering Microsoft R Client on Linux platform for the first time, in addition to Windows. R Client is available on all four popular flavors of Linux – RHEL, CentOS, Ubuntu, and SuSE. Please check out RClient for Windows and R Client for Linux.

In Summary

I am proud of how we are making R enterprise-grade through Microsoft R portfolio of products and services, building on top of open source R in fully compatible ways. Adopting advanced analytics and machine learning requires a holistic approach that transcends technology, people and processes; we continue to deliver more handholding to ensure that enterprise users are set up for success! With the 9.1 release, you have in-database analytics and machine learning in a variety of platforms, develop powerful analytics models leveraging both open source and Microsoft innovation, deploy them at scale, and easily integrate into line-of-business systems to maximize ROI.  We invite you to get started with Microsoft R Server 9.1.

Nagesh Pabbisetty

from SQL Server Blog

[From Technet] SQL Server 2017 Community Technology Preview 2.0 now available

Microsoft is excited to announce a new preview for the next version of SQL Server!  We disclosed a name for this next release, SQL Server 2017, today at the Microsoft Data Amp event. Community Technology Preview (CTP) 2.0 is the first production-quality preview of SQL Server 2017, and it is available on both Windows and Linux.  In this preview, we added a number of new capabilities, including the ability to run advanced analytics using Python in a parallelized and highly scalable way, the ability to store and analyze graph data, and other capabilities that help you manage SQL Server for high performance and uptime, including the Adaptive Query Processing family of intelligent database features and resumable online indexing.

In addition to all these great new features we are excited to announce a world record in the TPC-H 1TB data warehousing workload (non-clustered). The benchmark was achieved with SQL Server 2017 on Red Hat Enterprise Linux and HPE Prolliant server hardware, beating SQL Server 2016 on the same hardware handily.  This is just the first of many anticipated performance benchmarks for SQL Server 2017 on Linux and Windows, demonstrating SQL Server’s industry leading performance.  When taken in conjunction with the fact that SQL Server has had the least vulnerabilities of any major database over the last 7 years in the National Institute of Standards and Technology (NIST) vulnerability database, SQL Server 2017 on Windows and Linux is the best database for your Mission Critical application and data warehouse workloads.

You can try the preview in your choice of development and test environments now.

Power your entire data estate

Key CTP 2.0 enhancements on Windows and Linux

We also added support for storing and analyzing graph data relationships. This includes full CRUD support to create nodes and edges and T-SQL query language extensions to provide multi-hop navigation using join-free pattern matching. In addition, SQL Server engine integration enables querying across SQL tables and graph data. And, you can use all of your existing SQL Server tools to work with graph data.

With resumable online index rebuild, you can resume a paused index rebuild operation from where the rebuild operation was paused rather than having to restart the operation at the beginning. Additionally, this feature rebuilds indexes using only a small amount of log space. This feature will help pick up right where you left off when an index maintenance job encounters issues, or allow you to split index rebuilds across maintenance windows.

New in SQL Server 2017, we’re adding the Adaptive Query Processing family of intelligent database features. These features automatically keep database queries running as efficiently as possible without requiring additional tuning from database administrators.  In addition to the previous capability to adjust batch mode memory grants, in CTP 2.0 Adaptive Query Processing adds the batch mode adaptive joins and interleaved execution capabilities. Interleaved execution will improve the performance of queries that reference multi-statement table valued functions by surfacing runtime row counts to the query optimizer.  Batch mode adaptive joins enables the choice of a query’s physical join algorithm to be deferred until actual query execution, improving performance based on runtime conditions.

In addition, some functionality that was previously available in SQL Server on Windows is now available on Linux for the first time. This includes:

  • Additional SQL Server Agent capabilities – Use SQL Server Agent to keep replicas in synch with log shipping.
  • Listener for Always On availability groups – The listener enables clients to connect to the primary replica in an availability group, monitoring availability and directing connections to the replicas.

Key CTP 2.0 enhancement to SQL Server on Windows – Python for analytics

Another new, key feature enhancement in CTP 2.0 of SQL Server 2017 is the ability to run the Python language in-database to scale and accelerate machine learning, predictive analytics and data science scripts. The new capability, called Microsoft Machine Learning Services, enables Python scripts to be run directly within the database server, or to be embedded into T-SQL scripts, where they can be easily deployed to the database as stored procedures and easily called from SQL client applications by stored procedure call. SQL Server 2017 will also extend Python’s performance and scale by providing a selection of parallelized algorithms that accelerate data transforms, statistical tests and analytics algorithms. This functionality and the ability to run R in-database and at scale are only available on Windows Server operating system at this time.

For additional detail on CTP 2.0, please visit What’s New in SQL Server 2017, Release Notes and Linux documentation.

Get SQL Server 2017 CTP 2.0 today!

Get started with the preview of SQL Server with our developer tutorials that show you how to install and use SQL Server 2017 on macOS, Docker, Windows, and Linux and quickly build an app in a programming language of your choice.

Have questions? Join the discussion of SQL Server 2017 at MSDN. If you run into an issue or would like to make a suggestion, you can let us know through Connect. We look forward to hearing from you!

from SQL Server Blog

terça-feira, 18 de abril de 2017

[From Technet] Top three capabilities to get excited about in the next version of SQL Server


We announced the first public preview of SQL Server v.Next in November 2016, and since then we’ve had lots of customer interest, but a few key scenarios are generating the most discussion.

If you’d like to learn more about SQL Server v.Next on Linux and Windows, please join us for the upcoming Microsoft Data Amp online event on April 19 at 8 AM Pacific. It will showcase how data is the nexus between application innovation and intelligence—how data and analytics powered by the most trusted and intelligent cloud can help companies differentiate and out-innovate their competition.

In this blog, we discuss three top things that customers are excited to do with the next version of SQL Server.

1. Scenario 1: Give applications the power of SQL Server on the platform of your choice

With the upcoming availability of SQL Server v.Next on Linux, Windows, and Docker, customers will have the added flexibility to build and deploy more of their applications on SQL Server. In addition to Windows Server and Windows 10, SQL Server v.Next supports Red Hat Enterprise Linux (RHEL), Ubuntu, and SUSE Linux Enterprise Server (SLES). SQL Server v.Next also runs on Linux and Windows Docker containers opening up even more possibilities to run on public and private cloud application platforms like Kubernetes, OpenShift, Docker Swarm, Mesosphere DC/OS, Azure Stack, and Open Stack. Customers will be able to continue to leverage existing tools, talents, and resources for more of their applications.

SQL Server v.Next Linux Docker

Some of the things customers are planning for SQL Server v.Next on Windows, Linux, and Docker include migrating existing applications from other databases on Linux to SQL Server; implementing new DevOps processes using Docker containers; developing locally on the dev machine of choice, including Windows, Linux, and macOS; and building new applications on SQL Server that can run anywhere—on Windows, Linux, or Docker containers, on-premises, and in the cloud.

2. Scenario 2: Faster performance with minimal effort

SQL Server v.Next further expands the use cases supported by SQL Server’s in-memory capabilities, In-Memory OLTP and In-Memory ColumnStore. These capabilities can be combined on a single table delivering the best Hybrid Transactional and Analytical Processing (HTAP) performance available in any database system. Both in-memory capabilities can yield performance improvements of more than 30x, enabling the possibility to perform analytics in real time on operational data.

In v.Next natively compiled stored procedures (In-memory OLTP) now support JSON data as well as new query capabilities. For the column store both building and rebuilding a nonclustered column store can now be done online. Another critical addition to the column store is support for LOBs (Large Objects).

With these additions, the parts of an application that can benefit from the extreme performance of SQL Server’s in-memory capabilities have been greatly expanded! We also introduced a new set of features that learn and adapt from an application’s query patterns over time without requiring actions from your DBA.

3. Scenario 3: Scale out your analytics

In preparation for the release of SQL Server v.Next, we are enabling the same High Availability (HA) and Disaster Recovery (DR) solutions on all platforms supported by SQL Server, including Windows and Linux. Always On Availability Groups is SQL Server’s flagship solution for HA and DR. Microsoft has released a preview of Always On Availability Groups for Linux in SQL Server v.Next Community Technology Preview (CTP) 1.3.

SQL Server Always On availability groups can have up to eight readable secondary replicas. Each of these secondary replicas can have their own replicas as well. When daisy chained together, these readable replicas can create massive scale-out for analytics workloads. This scale-out scenario enables you to replicate around the globe, keeping read replicas close to your Business Analytics users. It’s of particularly big interest to users with large data warehouse implementations. And, it’s also easy to set up.

In fact, you can now create availability groups that span Windows and Linux nodes, and scale out your analytics workloads across multiple operating systems.

SQL Server v.Next HA

In addition, a cross-platform availability group can be used to migrate a database from SQL Server on Windows to Linux or vice versa with minimal downtime. You can learn more about SQL Server HA and DR on Linux by reading the blog SQL Server on Linux: Mission-critical HADR with Always On Availability Groups.

To find out more, you can watch our SQL Server on Linux webcast. Find instructions for acquiring and installing SQL Server v.Next on the operating system of your choice at To get your SQL Server app on Linux faster, you can nominate your app for the SQL Server on Linux Early Adopter Program, or EAP. Sign up now to see if your application qualifies for technical support, workload validation, and help moving your application to production on Linux before general availability.

To find out more about SQL Server v.Next and get all the latest announcements, register now to attend Microsoft Data Amp—where data gets to work.

from SQL Server Blog

quinta-feira, 13 de abril de 2017

[From Technet] Crossplatform Tools for SQL Server opened for community localization

This post was authored by Mona Nasr and Andy Gonzalez, Program Manager in C+E APEX Global Services

In February 2017, we announced that the localization of Crossplatform Tools for SQL Server (mssql for Visual Studio Code and SQL Tools Service) is open for community contributions on GitHub. We got great response from our community and would now like to thank the community members who collaborated in the international versions of SQL Tools API Service and Visual Studio Code SQL Server Extension.

This was a good first step to connect with and involve our communities in our goal to be more open and collaborative.

Community has completed the translations for VScode SQL Server extension for six languages: Brazilian, French, Japanese, Italian, Russian, and Spanish.

We still need help with other languages. If you know anyone with language expertise, refer them to the Team Page.

Your contributions are valuable and will help us improve the product in your languages. We hope to continue working with the community in future projects.

Project Translators:

Language Community members
Brazilian-Portuguese Bruno Sonnino, Juan Pablo Belli, Jhonathan de Souza Soares, Marcondes Alexandre, Rodrigo Romano, Rodrigo Crespi, Marcelo Fernandes, Roberto Fonseca
Chinese (Traditional, Simplified) Alan Tsai, Geng Liu, Lynne Dong, Ji Zhao健 邹,
French Antoine Griffard
German Jens Suessmeyer, Simon B, Markus Weber, Thomas Hütter, Goran Stevanovic
Italian Piero Azi
Japanese Rio Fujita, Takahito Yamatoya, Miho Yamamoto, Masashi Onodera, Yasuhiko Tokunaga
Korean Evelyn Kim
Russian Anatoli Dubko, Aleksey Nemiro
Spanish Josué Martínez Buenrrostro, Juan Pablo Belli, Diego Melgarejo San Martin, David Triana, Mariano Rodriguez, Christian Palomares Peralta

Project Contributors:

Language Community members
Brazilian-Portuguese Bruno Sonnino, Marcondes Alexandre, Rodrigo Romano, Rodrigo Crespi, Luan Moreno Medeiros Maciel, Jhonathan Soares, Marcelo Fernandes, Roberto Fonseca, Dennis Hernández, Caio Proiete, Alexandro Prado
Chinese (Traditional, Simplified) Alan Tsai, Geng Liu, Ji Zhao, 健 邹, Huang Junwei, Wei-Ting Shih, Lynne Dong, Ji Zhao, Y-Chi Lu, Weng Titan, KuoChen Lien, Kevin L. Tan
French Antoine Griffard, Lucas Girard
German Jens Suessmeyer, Simon B, Thomas Hütter, Martin Pöckl, Wolfgang Strasser, Markus Weber, Erich Gamma, Nico Erfurth, Ralf Krzyzaniak
Italian Piero Azi, Sergio Govoni
Japanese Rio Fujita, Takahito Yamatoya, Miho Yamamoto, Takayoshi Tanaka, Kamegawa Kazushi, Masashi Onodera, Yasuhiko Tokunaga, Kentaro Aoki, Igarashi Yuki, 徳永 康彦
Korean Evelyn Kim, Rachel Yerin Kim, Taeyoung Im, SUNG IL CHO, Hongsuk Kim
Russian Anatoli Dubko, Aleksey Nemiro, Anton Afonin, Anton Maznev, Илья Зверев
Spanish Josué Martínez Buenrrostro, Juan Pablo Belli, Diego Melgarejo San Martin, David Triana, Mariano Rodriguez, Christian Palomares Peralta, Jhonathan de Souza Soares

For more information about communities working with the Microsoft product teams, see

from SQL Server Blog