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.
I can finally call it “SQL Server 2017” instead of “SQL Server vNext.” I don’t know why there was such a hubbub about the name 2017, but there you go. Anyhow, I’ve grabbed the CTP and am raring to go.
Microsoft R Open (MRO), Microsoft’s enhanced distribution of open source R, has been upgraded to version 3.3.3, and is now available for download for Windows, Mac, and Linux. This update upgrades the R language engine to R 3.3.3, upgrades the installer, and updates the bundled packages.
R 3.3.3 makes just a few minor fixes compared to R 3.3.2 (see the full list of changes here), so you shouldn’t encounter any compatibility issues when upgrading from MRO 3.3.2. For CRAN packages, MRO 3.3.3 points to CRAN snapshot taken on March 15, 2017 but as always, you can use the built-in checkpoint package to access packages from an earlier date (for compatibility) or a later date (to access new and updated packages).
Click through for more details. As a side note, CRAN R 3.4 is scheduled for release this month, so given their recent cadence, I’d guess MRO 3.4 to be out late this year.
The waitstats don’t appear at all in my older Surface which has a newer version of SQL. So what is 4202.2? It’s a refresh for Master Data Services and R. Could that really have broken my query plan waitstats?
I doubt it but maybe. I updated to make the two equal. Did the waitstats go away?
When reading the solution, it seems obvious, but this is a good reminder that there are a lot of moving parts here, and one of the early troubleshooting steps for “It works here, so why not over here?” types of issues is to make sure software is at the same version number.
The memory limit of 128GB RAM applies only to the buffer pool (the 8KB data pages that are read from disk into memory — in other words, the database itself).
For servers containing more than 128GB of physical RAM, and running SQL Server 2016 with Service Pack 1 or higher, we now have options.
Randolph has a couple good clarifications on memory limits outside the buffer pool, making this worth the read.
But all of that is in the past. Here’s what you need to know about SQL Slammer today.
First, this worm infects unpatched SQL 2000 and MSDE instances only. About a month ago, I would have thought that the number of such installs would be quite small. But the recent uptick in Slammer tells me that there are enough of these systems to make Slammer one of the top malware detected at the end of 2016. And a quick search at Shodan shows thousands of public-facing database servers available. And if you want to have some real fun at Shodan®, Ian Trump (blog | @phat_hobbit) has a suggestion for you.
Click through for ways to protect yourself. The best way to protect yourself is not to have SQL Server 2000 around anymore.
Key CTP 1.3 enhancement: Always On Availability Groups on Linux
In SQL Server v.Next, we continue to add new enhancements for greater availability and higher uptime. A key design principle has been to provide customers with the same HA and DR solutions on all platforms supported by SQL Server. On Windows, Always On depends on Windows Server Failover Clustering (WSFC). On Linux, you can now create Always On Availability Groups, which integrate with Linux-based cluster resource managers to enable automatic monitoring, failure detection and automatic failover during unplanned outages. We started with the popular clustering technology, Pacemaker.
In addition, Availability Groups can now work across Windows and Linux as part of the same Distributed Availability Group. This configuration can accomplish cross-platform migrations without downtime. To learn more, you can read our blog titled “SQL Server on Linux: Mission Critical HADR with Always On Availability Groups”.
That’s a big headline. In the Other Enhancements section, I like resumable online index rebuilds as well.
This release also adds support for Spark 2 including version Spark 2.1. Zeppelin now also links to Spark History Server UI from Zeppelin so users can more easily track Spark jobs. The Livy interpreter now supports specifying packages with the job.
The major security improvement in Zeppelin 0.7.0 is using Apache Knox’s LDAP Realm to connect to LDAP. Zeppelin home page now lists only the nodes to which the user is authorized to access. Zeppelin now also has the ability to support PAM based authentication.
The full list of improvements is available here
This visualization platform is growing up nicely.
Key CTP 1.2 enhancement: Support for SUSE Linux Enterprise
In SQL Server v.Next, a key design principle has been to provide customers with choice about how to develop and deploy SQL Server applications: using technologies they love like Java, .NET, PHP, Python, R and Node.js, all on the platform of their choosing. Now in CTP 1.2, Microsoft is bringing the power of SQL Server to SUSE Linux Enterprise Server, providing more deployment options and a streamlined acquisition process.
That makes three mainline distributions supported: Ubuntu, Red Hat, and now SuSE.
Introduced in Spark 2.0, Structured Streaming is a high-level API for building continuous applications. The main goal is to make it easier to build end-to-end streaming applications, which integrate with storage, serving systems, and batch jobs in a consistent and fault-tolerant way.
Event-time watermarks: This change lets applications hint to the system when events are considered “too late” and allows the system to bound internal state tracking late events.
Support for all file-based formats and all file-based features: With these improvements, Structured Streaming can read and write all file-based formats, e.g. JSON, text, Avro, CSV. In addition, all file-based features—e.g. partitioned files and bucketing—are supported on all formats.
Apache Kafka 0.10: This adds native support for Kafka 0.10, including manual assignment of starting offsets and rate limiting.
This is a pretty hefty release. Click through to read the whole thing.
If you are using Columnstore indexes, you get the following performance benefits automatically, when you use Enterprise Edition:
Aggregate Pushdown: This performance feature often gives a 2X-4X query performance gain by pushing qualifying aggregates to the SCAN node, which reduces the number of rows coming out of that iterator.
Index Build/Rebuild: Enterprise Edition can build/rebuild columnstore indexes with multiple processor cores, while Standard Edition only uses one processor core. This has a pretty significant effect on elapsed times for these operations, depending on your hardware.
Local Aggregates: Enterprise Edition can use local aggregations to filter the number of rows passing out of a SCAN node, reducing the amount of work that needs to be done by subsequent query nodes. You can confirm this by looking for the “ActualLocallyAggregatedRows” attribute in the XML of the execution plan for the query.
Glenn’s focus is around columnstore indexes and DBCC CHECKDB, but there are additional benefits as well, with the separator being improved performance rather than different feature surface areas.