SQL Updates Newsletter – October 2017
Recent Releases and Announcements
- SQL Server 2012 Service Pack 4 (SP4) is now available
- Cumulative Update #8 for SQL Server 2014 SP2
- Cumulative Update #1 for SQL Server 2017 RTM
- Announcing new Azure Government capabilities for classified mission-critical workloads
- We’re expanding our support for highly-classified workloads, delivering advanced technologies like Blockchain and support for High Performance Computing, and increasing our available security capabilities with Azure Security Center. In addition, we’re launching Citrix VDI on Azure Government to help our government customers deliver user workloads from the cloud.
- https://azure.microsoft.com/en-us/blog/announcing-new-azure-government-capabilities-for-classified-mission-critical-workloads/
- Anaconda and Microsoft partner to offer Python and R for powerful machine learning
- Microsoft and Anaconda announced an exciting partnership to make Anaconda Python distribution into SQL Server, Machine Learning Server, Azure Machine Learning, and Visual Studio to deliver real-time insights. In addition, Anaconda will be distributing Microsoft R.
- https://blogs.technet.microsoft.com/machinelearning/2017/10/26/anaconda-and-microsoft-partner-to-offer-python-and-r-for-powerful-machine-learning/
- Microsoft R Open 3.4.2 now available
- MRO 3.4.2 is based on R 3.4.2
- https://blog.revolutionanalytics.com/2017/10/microsoft-r-open-342-now-available.html
- Modernize your applications across on-premise and cloud with the Microsoft Data Accelerator Program
- Leverage the Microsoft Data Accelerator Program, an efficient and reliable upgrade service, to modernize your on-premise or cloud applications.
- https://blogs.technet.microsoft.com/dataplatforminsider/2017/10/30/modernize-your-applications-across-on-premise-and-cloud-with-the-microsoft-data-accelerator-program/
- AI for Education: Individualized Code Feedback for Thousands of Students
- There are more than 9,000 students enrolled in the Microsoft Introduction to C# course on edX.org. Although course staff can’t offer the type of guidance available in an on-campus classroom setting, students can receive personalized help, thanks to a project from Microsoft Research.
- When a student’s assignment contains mistakes, that student—within seconds—receives a message specific to their code submission. Beyond just informing the student that their program doesn’t work, Microsoft has created a tool which automatically generates feedback that precisely identifies errors and even hints at how to correct them.
- Recently the team has been awarded a patent for this project (401696-US-NP)
- After using it with another set of students, Microsoft plans to make it more widely available.
- https://blogs.technet.microsoft.com/machinelearning/2017/10/25/ai-for-education-individualized-code-feedback-for-thousands-of-students/
- Highlighting 3 New Features in Azure Data Factory V2
- Control Flow. The Control activities in ADF will now allow you to loop, retrieve metadata and lookup from external sources.
- Parameterized Pipelines. We've added the ability to parameterize pipelines, which can be used in conjunction with expressions and triggers.
- Flexible Scheduling. We've changed the scheduling model for ADF so that when you build a pipeline in ADF V2, you will no longer build dataset-based time slices, data availability and pipeline time windows. Instead, you will attach separate Trigger resources that you can then use to reference pipelines that you've built and execute them on a wall-clock style schedule.
- https://azure.microsoft.com/en-us/blog/highlighting-3-new-features-in-azure-data-factory-v2/
- Transactional replication to Azure SQL Database is now generally available
- We are excited to announce that transactional replication to Azure SQL Database is now generally available (GA). This feature allows you to migrate your on-premises SQL Server databases to Azure SQL Databases with minimal downtime.
- https://azure.microsoft.com/en-us/blog/transactional-replication-to-azure-sql-database-is-now-generally-available/
- Inclusion in action: enabling transformative change with accessible technologies
- We’re unveiling a new video series celebrating six individuals who are using accessible technologies to help transform their lives.
- We’re excited to start today with a video about Andrew, an eight-year-old boy with dyslexia who read for the first time this summer with the help of Microsoft Learning Tools.
- https://blogs.microsoft.com/blog/2017/10/23/inclusion-action-enabling-transformative-change-accessible-technologies/
- Cray Supercomputers are coming to Azure
- We're thrilled to share our new, exclusive partnership with Cray that will provide our customers unprecedented access to supercomputing capabilities in Azure to solve their toughest challenges in climate modeling, precision medicine, energy, manufacturing, and other scientific research.
- https://azure.microsoft.com/en-us/blog/cray-supercomputers-are-coming-to-azure/
- Announcing the Data Science Virtual Machine (DSVM) in Batch AI Service
- DSVM is a family of popular VM images published on Azure with a broad choice of machine learning, AI and data science tools.
- Microsoft’s Batch AI Service is a new service that helps you train and test machine learning models, including deep learning models, on pools of GPU machines. It simplifies the process of creating a cluster of machines and training on it using many popular deep learning frameworks like TensorFlow, Microsoft Cognitive Toolkit, and others.
- https://blogs.technet.microsoft.com/machinelearning/2017/10/19/announcing-the-data-science-virtual-machine-in-batch-ai-service/
- On-premises data gateway October update is now available
- Cloud Service Map for AWS and Azure Available Now
- We are pleased to introduce a new cloud service map to help you quickly compare the cloud capabilities of Azure and AWS services in all categories.
- https://azure.microsoft.com/en-us/blog/cloud-service-map-for-aws-and-azure-available-now/
- Released: Public Preview for SQL Server 2017+ and Replication Management Pack (RC)
- We understand that with many SQL Server versions in market and with new server releases becoming more frequent, it is becoming harder to manage a separate MP for each server version. We are moving to version agnostic MPs to address this issue. This will be valid going forward. The new MP is named SQL Server 2017+. The ‘+’ in the name indicates that it will be used to monitor SQL Server 2017 and the releases that come after that. Current in-market MPs (2008 through 2016) will not be changed and the 2017+ MP cannot be used to monitor older releases.
- https://blogs.msdn.microsoft.com/sqlreleaseservices/released-public-preview-for-sql-server-2017-and-replication-management-pack-rc/
- Introducing the Deep Learning Virtual Machine on Azure
- SQL Server 2017 Reporting Services now generally available
- We’re pleased to make SQL Server 2017 Reporting Services generally available
- After much research into how customers install and update Reporting Services, we moved installation from the SQL Server installer to a separate installer.
- https://blogs.msdn.microsoft.com/sqlrsteamblog/2017/10/02/sql-server-2017-reporting-services-now-generally-available/
- Power BI Service and Mobile September Feature Summary
- Power BI Desktop October Feature Summary
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)
- Configure Azure Storage for SQL Database backups
- These are probably three things that can cost you a few hours of debugging if you are not creating Azure Storage keys every day. Make sure that you check type of storage, validity period, and SAS key when you configure Azure Storage, or your backups will fail.
- https://blogs.msdn.microsoft.com/sqlserverstorageengine/2017/10/17/configure-azure-storage-for-sql-database-backups/
- How to safeguard SQL Server on Linux from OOM-Killer
- SQL Server support team had the following customer scenario recently: When an index rebuild was kicked off on a large table (around 25GB), the reindex operation terminated, and the availability group had failed over to the other replica.
- To begin with, SQL Server was only using a portion of 9.5GB leaving plenty of memory available on the system. However, when the index rebuild was executed, SQL Server used up all the 9.5GB of memory it can use. This condition had caused very little memory to be left on the Linux server, causing the oom-killer to be invoked. SQL Server was chosen as a victim as it has the highest memory usage(oom-score). This behavior is expected.
- To make SQL Server less susceptible for termination by oom-killer, we recommend one or both of the following suggestions. 1) Adjust memory.memorylimitmb configuration option carefully to leave enough memory on the system even if SQL Server were to use all of the memory configured through this setting. 2) Ensure that swap file exists and sized properly.
- https://blogs.msdn.microsoft.com/psssql/2017/10/17/how-to-safeguard-sql-server-on-linux-from-oom-killer/
- Performance implications of using multi-Statement TVFs with optional parameters
Recent Blog Posts and Articles
- Create editable Microsoft Office charts from R
- Azure GDPR resources: Unmatched focus on customer compliance needs
- Recent updates to the Team Data Science Process
- It's been over a year since we first introduced introduced the Team Data Science Process (TDSP). The data, technology and practices behind Data Science continue to evolve, and the TDSP has evolved in parallel. Over the past year, several new facets have been added.
- https://blog.revolutionanalytics.com/2017/10/recent-updates-to-the-team-data-science-process.html
- Recent Updates to setting up SQL Server Availability Groups in Azure VM with AAD Domain Services
- Top Questions from New Users of Azure SQL Database
- An Updated History of R
- Data Integrity in Azure SQL Database
- The Azure SQL Database engineering team takes responsibility for managing data integrity. As such, it is not necessary for customers to run integrity checks in Azure SQL Database.
- With the existing monitoring and protection provided by the service, customers can still choose to execute user-initiated integrity checks in Azure SQL Database. For example, customers may optionally run DBCC CHECKDB.
- https://azure.microsoft.com/en-us/blog/data-integrity-in-azure-sql-database/
Recent Training and Technical Guides
- [Recorded Workshop] My Dashboard Best Practices Revealed - Recorded Workshop and Resource Download Available
- [Tutorial] Duplicate workspaces using the Power BI REST APIs: a step-by-step tutorial
- [On-line Book] Statistical Machine Learning with Microsoft ML
- A great way to see what MicrosoftML can do is to take a look at the on-line book Machine Learning with the MicrosoftML Package Package by Ali Zaidi.
- https://blog.revolutionanalytics.com/2017/10/statistical-machine-learning-with-microsoft-ml.html
- The R manuals in bookdown format
- While there are hundreds of excellent books and websites devoted to R, the canonical source of truth regarding the R system remains the R manuals.
- You can find the manuals at your local CRAN mirror and on your laptop as part of the R distribution (try Help > Manuals in RGui, or Help > R Help in RStudio to find them).
- https://blog.revolutionanalytics.com/2017/10/r-manuals-bookdown.html
- Monitoring SQL Server and Azure SQL DB Session at DBA Fundamentals PASS VC
- Tutorial: Azure Data Lake analytics with R
- Learn the latest on Azure Analysis Services
Monthly Script and Tool Tips
- Azure Analysis Services web designer adds new DAX query viewer
- Today we are announcing new functionality that will allow you to generate, view and edit your DAX queries.
- https://azure.microsoft.com/en-us/blog/azure-analysis-services-web-designer-adds-new-dax-query-viewer/
- Reverse Desired State Configuration: How it works
- ReverseDSC is a module that provides a set of functions that can be used to reverse engineer an existing environment into a DSC script.
- At its core, it is an extensible solution that you can use to extract your existing environment as a Desired State Configuration (DSC) script. As long as there is a DSC Module for the technology component, you can use ReverseDSC to extract its current configuration.
- We currently have the following official ReverseDSC Orchestrator Scripts available on GitHub: SQLServer, SharePoint, and more.
- https://blogs.technet.microsoft.com/heyscriptingguy/2017/10/27/reverse-desired-state-configuration-how-it-works/
- Polaris – simple Microservices using only PowerShell
- Polaris is a cross-platform, minimalist web framework for PowerShell Core 6. With 6 lines of code, you have your very own webserver running and ready to accept requests – all within PowerShell.
- https://blogs.msdn.microsoft.com/powershell/2017/10/16/polaris-simple-microservices-using-only-powershell/
- DSC Resource Kit Release October 2017
- Install SQL Server 2017 Using PowerShell Desired State Configuration
Fany Carolina Vargas | SQL Dedicated Premier Field Engineer | Microsoft Services