SQL Updates Newsletter – May 2017
Recent Releases and Announcements
- SQL Server 2017 CTP 2.1 now available
-
- The primary enhancement to SQL Server 2017 in this release is the ability to configure SQL Server configuration settings through environment variables passed in as parameters to Docker run. This enables many of the SQL Server configuration scenarios in Docker containers such as setting the collation.
- SQL Server Integration Services now supports Linux for the first time!
- In CTP 2.1, we moved Reporting Services installation from the SQL Server installer to a separate installer.
- /en-us/sql/reporting-services/what-s-new-in-sql-server-reporting-services-ssrs
- https://blogs.msdn.microsoft.com/ssis/2017/05/17/ssis-helsinki-is-available-in-sql-server-vnext-ctp2-1/
- https://blogs.technet.microsoft.com/dataplatforminsider/2017/05/17/sql-server-2017-ctp-2-1-now-available/
Cumulative Update #6 for SQL Server 2016 RTM
Cumulative Update #3 for SQL Server 2016 SP1
Coming Soon: SQL Server 2012 Service Pack 4
- The SQL Server team is excited to bring you the last service pack release for SQL Server 2012
- SQL Server 2012 SP4 will be released next month and a release announcement with detailed information on improvements will be shared at the time of the release
- https://blogs.msdn.microsoft.com/sqlreleaseservices/announcing-sql-server-2012-service-pack-4/
Cumulative Update #9 for SQL Server 2012 SP3
Announcing PowerShell for Visual Studio Code 1.0!
- PowerShell ISE-like interactive development experience with the PowerShell Integrated Console
- Rich debugging experience including variables view, call stack, watch window, and various breakpoint types
- Code navigations that allow you to find definitions and references of functions across your script files
- And more…
- The PowerShell ISE has been the official editor for PowerShell throughout most of the history of Windows PowerShell. Now with the advent of the cross-platform PowerShell Core, we need a new official editor that’s available across all supported OS platforms and versions. Visual Studio Code is now that editor and the majority of our effort will be focused there. However, the PowerShell ISE will remain in Windows supporting Windows PowerShell with no plans to remove it. We will consider investing effort there in the future if there is a high demand for it, but for now we think that we will be able to provide the best possible experience to the PowerShell community through Visual Studio Code.
- https://blogs.msdn.microsoft.com/powershell/2017/05/10/announcing-powershell-for-visual-studio-code-1-0/
SQL Server 2017 on Linux surpasses 1 million Docker pulls as the next preview version rolls out
R and Python support now built in to Visual Studio 2017
Microsoft R Open 3.4.0 now available
SSMS v17.0 now available
Microsoft Certifications in Data Science & Machine Learning
- Those who are planning to build and deploy machine learning solutions should consider taking the Perform Cloud Data Science with Azure Machine Learning exam.
- https://blogs.technet.microsoft.com/machinelearning/2017/05/16/democratizing-ai-through-microsoft-certifications-in-data-science-machine-learning/
Microsoft ODBC Driver 13.1 for macOS released
- Enables access to SQL Server, Azure SQL Database and Azure SQL DW from any C/C++ application on macOS
- https://blogs.technet.microsoft.com/dataplatforminsider/2017/05/15/odbc-driver-13-1-for-macos-released/
SQL Server Command Line Tools for macOS released
Power BI Report Server preview now available
- Ability to manage Power BI reports on-premises with the included Power BI Report Server
- https://powerbi.microsoft.com/en-us/blog/power-bi-report-server-preview-now-available/
- https://blogs.msdn.microsoft.com/sqlrsteamblog/2017/05/17/a-closer-look-at-power-bi-report-server/
Microsoft accelerates modern BI adoption with Power BI Premium
- Power BI Premium introduces the ability to maintain BI assets on-premises with Power BI Report Server. With Power BI Premium, the same number of virtual cores an organization provisions in the cloud can also be deployed on-premises through Power BI Report Server, without the need to split the capacity.
- As part of the new offering we are converging Power BI Embedded with the Power BI service to deliver one API surface, a consistent set of capabilities and access to the latest features. Moving forward we encourage those interested in embedding Power BI in their apps to start with Power BI Desktop and move to deployment with Power BI Premium. Existing apps built on Power BI Embedded will continue to be supported.
- We’re also simplifying the distinction between Power BI Pro and the free service. Going forward, we will improve the free service to have the same functionality as Power BI Pro, but will limit sharing and collaboration features to only Power BI Pro users.
- Power BI Premium will be generally available late in the second quarter of 2017.
- https://powerbi.microsoft.com/en-us/blog/microsoft-accelerates-modern-bi-adoption-with-power-bi-premium/
Power BI Desktop May Feature Summary
WannaCrypt attacks: guidance for Azure customers
- WannaCrypt malware exploits a Service Message Block (SMB) vulnerability
- Review all Azure subscriptions that have SMB endpoints exposed to the internet
- Disable SMBv1
- Utilize Windows Update to keep your machines up-to-date with the latest security updates
- Use the Azure Security Center to continuously monitor your environment for threats
- Use Network Security Groups (NSGs) to restrict network access
- Confirm that anti-malware is deployed and updated
- Configure backups with multifactor authentication…we also recommend enabling Azure Multi-Factor Authentication to provide an additional layer of security to your backups in Azure.
- https://azure.microsoft.com/en-us/blog/wannacrypt-attacks-guidance-for-azure-customers/
Threat Detection in Azure SQL Database is now Generally Available
Early technical preview of JDBC 6.1.7 for SQL Server released!
Newly created Azure SQL databases will be encrypted at rest by default
- All new Azure SQL databases will be encrypted with Transparent Data Encryption (TDE) by default, to make it easier for everyone to benefit from encryption at rest
- https://blogs.msdn.microsoft.com/sqlsecurity/2017/05/02/newly-created-azure-sql-databases-will-be-encrypted-at-rest-by-default/
Data Connector SDK Developer Preview
- You can begin to create your own custom PowerBI data connectors.
- https://powerbi.microsoft.com/en-us/blog/data-connectors-developer-preview/
Announcing the public preview of SQL Server command line tools to generate T-SQL scripts and monitor Dynamic Management Views
- The mssql-scripter tool scripts database objects in SQL Server, Azure SQL DB, and Azure SQL DW from the command line
- The DBFS tool exposes live data from SQL Server Dynamic Management Views (DMVs) as virtual files in a virtual directory on Linux operating systems
- https://blogs.technet.microsoft.com/dataplatforminsider/2017/05/17/try-new-sql-server-command-line-tools-to-generate-t-sql-scripts-and-monitor-dynamic-management-views/
In-Memory OLTP Updates Available Today in Azure SQL Database and Coming to SQL Server vNext
- The limitation of 8 indexes for memory-optimized tables has been eliminated. You can now create as many indexes on memory-optimized tables as you can create on disk-based tables.
- Added support for the system stored procedure sp_rename with memory-optimized tables and natively compiled T-SQL modules. With this, Azure SQL Database now supports all the main schema management operations for In-Memory OLTP.
- The limit on the maximum number of transactions a given transaction depends on or the number of transactions that depend on a given transaction is eliminated
- We have added T-SQL support for CASE, computed columns, CROSS APPLY
- sp_spaceused can now be used to understand the storage footprint associated with In-Memory OLTP
- https://azure.microsoft.com/en-us/blog/in-memory-oltp-blog-update-q1-2017/
Announcing 1400 Compatibility Level in Azure Analysis Services
- We are excited to announce the public preview of the 1400 compatibility level for tabular models in Azure Analysis Services
- The 1400 compatibility level will also be available in SQL Server 2017 Analysis Services
- New infrastructure for data connectivity and ingestion into tabular models with support for Tabular Object Model (TOM) APIs and TMSL scripting
- Object-level security to secure table and column names in addition to the data within them
- Enhanced support for ragged hierarchies such as organizational charts and chart of accounts.
- And more
- https://blogs.msdn.microsoft.com/analysisservices/2017/05/04/1400-compatibility-level-in-azure-analysis-services/
Announcing Public Preview of HDInsight HBase on Azure Data Lake Store
Announcing support for additional Blockchain Protocols on Azure
Announcing Azure Cosmos DB
- Azure Cosmos DB, announced at the Microsoft Build 2017 conference, is the first globally distributed, multi-model database service for building planet scale apps. You can easily build globally-distributed applications without the hassle of complex, multiple-datacenter configurations.
- https://azure.microsoft.com/en-us/blog/azure-cosmos-db-microsofts-globally-distributed-multi-model-database-service/
- https://azure.microsoft.com/en-us/blog/dear-documentdb-customers-welcome-to-azure-cosmos-db/
Azure SQL Database now supports transparent geographic failover of database groups
Batch computing at a fraction of the price
- We are delighted to announce the public preview of a new way to obtain and consume Azure compute at a much lower price using Azure Batch – low-priority VMs
- Batch processing jobs are one of the main types of workload that can leverage low-priority VMs
- https://azure.microsoft.com/en-us/blog/announcing-public-preview-of-azure-batch-low-priority-vms/
Microsoft extends Azure managed database services with introduction of MySQL and PostgreSQL
- We are excited to announce the preview of managed database services with Azure Database for MySQL and Azure Database for PostgreSQL.
- https://azure.microsoft.com/en-us/blog/microsoft-extends-azure-managed-database-services-with-introduction-of-mysql-and-postgresql/
New in SSMS: Query Performance Troubleshooting made easier!
- We have been working on a functionality that implements some degree of automation in the task of query plan analysis, especially for those large and complex plans. The purpose is to make it easier to find common scenarios where plan choice may be inefficient, and get some recommendations on next steps to take.
- In this first release, we added an “Inaccurate Cardinality Estimation” scenario.
- https://blogs.msdn.microsoft.com/sql_server_team/new-in-ssms-query-performance-troubleshooting-made-easier/
SQL Server 2017 Showplan enhancements
- We are happy to announce that identifying which statistics were in fact used by the Query Optimizer for a given compilation has become much easier…they are now part of Showplan.
- https://blogs.msdn.microsoft.com/sql_server_team/sql-server-2017-showplan-enhancements/
Released: Microsoft Azure SQL Database Management Pack (6.7.28.0)
- Added support of Azure AD authentication, performance improvements, and more
- https://blogs.msdn.microsoft.com/sqlreleaseservices/released-microsoft-azure-sql-database-management-pack-6-7-28-0/
Released: Public Preview for SQL Server 2017 Management Pack (CTP3)
New Power BI content pack for Azure Enterprise users
Issue Alert
- Critical: Do NOT delete files from the Windows Installer folder. C:\windows\Installer is not a temporary folder and files in it should not be deleted. If you do it on machines on which you have SQL Server installed, you may have to rebuild the operating system and reinstall SQL Server.
- Critical: Please be aware of a critical Microsoft Visual C++ 2013 runtime pre-requisite update that may be required on machines where SQL Server 2016 will be, or has been, installed.
- https://blogs.msdn.microsoft.com/sqlcat/2016/07/28/installing-sql-server-2016-rtm-you-must-do-this/
- If KB3164398 or KB3138367 are installed, then no further action is necessary. To check, run the following from a command prompt:
- powershell get-hotfix KB3164398
- powershell get-hotfix KB3138367
- If the version of %SystemRoot%\system32\msvcr120.dll is 12.0.40649.5 or later, then no further action is necessary. To check, run the following from a command prompt:
- powershell "get-item %systemroot%\system32\msvcr120.dll | select versioninfo | fl"
- Important: If the Update Cache folder or some patches are removed from this folder, you can no longer uninstall an update to your SQL Server instance and then revert to an earlier update build.
- In that situation, Add/Remove Programs entries point to non-existing binaries, and therefore the uninstall process does not work. Therefore, Microsoft strongly encourages you to keep the folder and its contents intact.
- https://support.microsoft.com/en-us/kb/3196535
- Important: You must precede all Unicode strings with a prefix N when you deal with Unicode string constants in SQL Server
- Important: Default auto statistics update threshold change for SQL Server 2016
- https://blogs.msdn.microsoft.com/psssql/2016/10/04/default-auto-statistics-update-threshold-change-for-sql-server-2016/
- SQL Server 2016: Default is new threshold if database compatibility level is 130. If database compatibility is below 130, old threshold is used (unless you use trace flag 2371)
- Distribution Agent fails with ‘Cannot update identity column ‘OrderID’.’
- Memory optimized table variable and cardinality estimate
- By default memory optimized table variable behaves the same way as disk based table variable. It will have 1 row as an estimate.
- Inside a natively compiled stored procedure…it will always estimate 1 row. You can’t change it because natively compiled procedure doesn’t allow a statement-level recompile.
- https://blogs.msdn.microsoft.com/psssql/2017/05/10/memory-optimized-table-variable-and-cardinality-estimate/
Recent Blog Posts and Articles
- Serving AI with Data: A Summary of Build 2017 Data Innovations
- Infusing AI within our data platform: GPU accelerated analytics through Python/R, Graph support
- Azure Cosmos DB delivers a schema-agnostic database service with turnkey global distribution, support for multiple models across popular NoSQL technologies, elastic scale of throughput and storage, five well-defined consistency models, and financially-backed SLAs across uptime, throughput, consistency, and millisecond latency.
- Azure Database for MySQL and Azure Database for PostgreSQL
- Azure Data Lake Tools for Visual Studio Code (VSCode) which gives developers a light but powerful code editor for big data analytics
- https://blogs.technet.microsoft.com/dataplatforminsider/2017/05/10/serving-ai-with-data-a-summary-of-build-2017-data-innovations/
- What’s new in SQL Server 2017 CTP 2.1 for Analysis Services
- Shared M expressions are shown in the SSDT Tabular Model Explorer, and can be maintained using the Query Editor
- Data Management View (DMV) improvements
- Opening an file with the .MSDAX extension in SSDT enables DAX non-model related IntelliSense
- Encoding hints can be set in the SSDT properties window
- https://blogs.msdn.microsoft.com/analysisservices/2017/05/18/whats-new-in-sql-server-2017-ctp-2-1-for-analysis-services/
- Migrating to Azure Data Sync 2.0
- We’ve made improvements to SQL Data Sync including PowerShell programmability, better security and resilience, enhanced monitoring and troubleshooting, and availability in more regions.
- This article covers the steps current active users will use to migrate to the new service
- https://azure.microsoft.com/en-us/blog/migrating-to-azure-data-sync-2-0/
- Updates to Microsoft Cognitive Services
- Custom Vision Service, Video Indexer, Custom Decision Service, Bing Custom Search, Microsoft’s Cognitive Services Labs, Next version of Bing APIs, Presentation Translator, and Language Understanding Intelligent Service (LUIS) improvements.
- https://azure.microsoft.com/en-us/blog/at-build-microsoft-expands-its-cognitive-services-collection-of-intelligent-apis/
- Azure SQL Data Warehouse loading patterns and strategies
- Automatic plan correction in SQL Server 2017
- SQL Server 2017 provides a new system view called sys.dm_db_tuning_recommendations that shows all identified plan regressions.
- To enable auto plan correction: ALTER DATABASE current SET AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = ON)
- https://blogs.msdn.microsoft.com/sqlserverstorageengine/2017/05/17/automatic-plan-correction-in-sql-server-2017/
- Automatic index management in Azure SQL database
- Azure SQL Database has a built-in intelligence mechanism that can automatically tune and improve performance of your queries by dynamically adapting the database schema to your workload.
- https://blogs.msdn.microsoft.com/sqlserverstorageengine/2017/05/16/automatic-index-management-in-azure-sql-db/
- Visualize DSC Reporting with PowerBI
Recent Training and Technical Guides
- [Course] Technical Foundations of Informatics: A modern introduction to R
- [Tutorial] Azure IoT Suite adds physical IoT device/gateway tutorials
- End-to-End Scenarios Enabled by the Data Science Virtual Machine: Webinar Video
- Join Alberto Cairo at the Microsoft Data Insights Summit to Explore the Different Dimensions of Data Visualization
- May and June Webinars: Power BI Security, Best Practices and more
- Ready-made examples of Root-Cause Analysis with In-Query Machine Learning in Application Insights Analytics
- Distribute to large audiences with Power BI apps
- Whitepaper: Guide for enhancing privacy and addressing GDPR requirements
Monthly Script and Tool Tips
- [Script Of May. 25] How to query Azure Cosmos DB resources using the REST API by PowerShell
- How to Use an Encrypted Password Within a SQL Agent job to Access a Fileshare in an Untrusted Domain
- Script to decrypt the @schema_option for a Transactional Replication Article
- Webinar: Seven Tips to Better Visualization Design
- [Script Of May. 24] How to write SQL results to HTML document using PowerShell
Fany Carolina Vargas | SQL Dedicated Premier Field Engineer | Microsoft Services