SQL Updates Newsletter – October 2016
Recent Releases and Announcements
- Cumulative Update #2 for SQL Server 2014 SP2
- https://blogs.msdn.microsoft.com/sqlreleaseservices/cumulative-update-2-for-sql-server-2014-sp2/
- We recommend ongoing, proactive installation of CUs as they become available.
- Cumulative Update #9 for SQL Server 2014 SP1
- Latest 2016 Gartner Magic quadrant recognitions
- Gartner has recognized Microsoft as a Leader — for the ninth year in a row — in the 2016 Magic Quadrant for Business Intelligence and Analytics Platforms
- Gartner recognized Microsoft as a Leader in the 2016 Magic Quadrant for Data Warehouse and Database Management Solutions for Analytics, based on its completeness of vision and ability to execute.
- In its latest Magic Quadrant report for Operational DBMS (ODBMS), Gartner ranks the Microsoft DBMS highest in completeness of vision and ability to execute.
- Azure Analysis Services Preview
- https://blogs.msdn.microsoft.com/analysisservices/2016/10/25/introducing-azure-analysis-services/
- We are pleased to announce the availability of Microsoft Azure Analysis Services preview.
- SQL Server Data Tools 16.5 Release
- Microsoft announced general availability of Data Migration Assistant v2.0 and the first public preview for Database Experimentation Assistant
- Database Migration Assistant replaces the previous SQL Server Upgrade Advisor tool.
- Database Migration Assistant is a free tool that reduces the effort required to upgrade SQL Server by detecting compatibility issues that can impact database functionality after an upgrade.
- Database Experimentation Assistant technical preview is a new free tool that enables customers to gather performance insights for upgrades by conducting experiments across two SQL Server versions using production workloads.
- https://blogs.technet.microsoft.com/dataplatforminsider/2016/10/27/upgrade-with-data-migration-assistant-2-0-and-database-experimentation-assistant-preview/
- ODBC Driver 13.0 for SQL Server – Linux is now released
- Announcing SQL Server Management Studio -16.5 Release
- Technical Preview of Power BI reports in SQL Server Reporting Services now available
- https://blogs.msdn.microsoft.com/sqlrsteamblog/2016/10/27/technical-preview-of-power-bi-reports-in-sql-server-reporting-services-now-available/
- https://blogs.msdn.microsoft.com/sqlrsteamblog/2016/10/25/announcing-a-technical-preview-of-power-bi-reports-in-sql-server-reporting-services/
- This Technical Preview represents our progress toward on-premises self-service BI via Power BI reports in Reporting Services, an important goal in our reporting roadmap. With this solution, you’ll be able to:
- Visually explore data and create an interactive report using Power BI Desktop
- Publish that [Power BI Desktop] report to an on-premises report server (SQL Server Reporting Services)
- Share the report with your coworkers so they can view and interact with it in their web browsers
- Released: System Center Management Pack for SQL Server and Dashboards (6.7.7.0)
- SQL Server 2016 Express Edition in Windows containers
- https://blogs.msdn.microsoft.com/sqlserverstorageengine/2016/10/13/sql-server-2016-express-edition-in-windows-containers/
- https://msdn.microsoft.com/en-us/virtualization/windowscontainers/quick_start/quick_start_windows_10
- The image is now available on Docker Hub and the build scripts are hosted on our SQL Server Samples GitHub repository. This image can be used in both Windows Server Containers as well as Hyper-V Containers.
- SQL Server 2016 in a Windows container would be ideal when you want to:
- Quickly create and start a set of SQL Server instances for development or testing.
- Maximize density in test or production environments, especially in microservice architectures.
- Isolate and control applications in a multi-tenant infrastructure.
- R Tools for Visual Studio 0.5 (Preview)
- https://blogs.technet.microsoft.com/machinelearning/2016/10/18/r-tools-for-visual-studio-0-5/
- R Tools for Visual Studio is a free extension for Visual Studio 2015 that brings the Visual Studio IDE experience to R users. This is a preview release, that is intended for early adopters who are eager to try out our new data science experience for R and Visual Studio users.
- Key updates in this release include:
- Integrated support for SQL and SQL Stored Procedures.
- Multiple plot windows, plot history with plot thumbnails for navigation.
- General improvements and bug fixes.
Recent Whitepapers/E-books/Training/Tutorials
- Free eBook: Using SQL Server 2016 for Data Science & Advanced Analytics
- ColumnStore Index in SQL Server 2016 and Azure SQL Database
- Hear customer success stories with columnstore index in SQL Server 2016
- In-Memory OLTP in SQL Server 2016 and Azure SQL Database
- Explore In-Memory OLTP architectures and customer case studies
- SQL Server Data Tools for Visual Studio
- First Look at the new SQL Server Data Migration Assistant
Monthly Script Tips
- [Script Of Oct. 24] Determining which version and edition of SQL Server Database Engine is running
- [Script Of Oct. 18] How to create linked server for SQL Database on Azure from local SQL Server
- [Script Of Oct. 10] How to set expired date of Azure Virtual Machine by PowerShell
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: You must precede all Unicode strings with a prefix N when you deal with Unicode string constants in SQL Server
- 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)
- SQL 2016 — Why can’t I STRETCH my database (I have the right user name and password)?
- Backup to remote Azure URL failed due to throttling at VM level
- Filestream RsFx Driver fails to load after installing SQL Server 2016 with CU2 on Windows Server 2016 with SecureBoot ON
- SQL Server 2014 SP1 – CU9 fixes
- https://support.microsoft.com/en-us/kb/3186964
- Includes the following fixes among others:
- 3194959 Statistics removed after rebuilding the index on a partitioned table in SQL Server
- 3189645 FIX: Access violation when you run a query that uses clustered columnstore index with trace flag 2389, 2390, or 4139
- SQL Server 2014 SP2 – CU2 fixes
- https://support.microsoft.com/en-us/kb/3188778
- Includes the following fixes among others:
- 3191487 Query performance improvement for some columnstore queries in SQL Server 2014
- 3184099 SQL Server 2012 crashes with an access violation when you use the TRY…CATCH construct for bulk copy
- 3181260 SQL Server Analysis Services crashes when you have multiple roles and run a CREATE SESSION CUBE statement in SQL Server 2014
- 164104 Unable to run DBCC CHECKDB when the database files reside in Azure Premium Storage in SQL Server 2014
- 3189033 FIX: Cannot uninstall service packs for SQL Server 2014 after a cumulative update is installed
- 3174370 A memory leak occurs when you use Azure Storage in SQL Server 2014 or 2016
Recent Blog Posts and Articles
- Hyper-V Large-Scale VM Support in Windows Server 2016
- https://blogs.technet.microsoft.com/windowsserver/2016/09/28/windows-server-2016-hyper-v-large-scale-vm-performance-for-in-memory-transaction-processing/
- Virtual Machine Memory Support = Up to 12 TB per VM
- Virtual Machine Virtual Processor Support = Up to 240 VPs per VM
- Feature Spotlight: Transparent Data Encryption (TDE)
- https://blogs.msdn.microsoft.com/sqlsecurity/2016/10/05/feature-spotlight-transparent-data-encryption-tde/
- With TDE, is the data stored in my data, log, and backup files protected from a disk theft? …If the OS image and data files are located on the same disk, an attacker can restore and compromise the data. A recommended best practice is to store the OS image and database files on separate disks for additional security.
- Can a backup encrypted by TDE be compressed? …Depends on the version of SQL Server. For SQL Server 2016, if you’d like a backup to have both compression and TDE, you must explicitly specify a MAXTRANSFERSIZE that is greater than 64K in the backup command (the next possible size is 128K). SQL Server will then decrypt the backup first, compress the backup, then encrypt the backup before writing to media. …For previous versions of SQL Server, the backup remains encrypted and then is compressed. Because the original encryption adds randomness to the data, little to no space is saved from the compression, wasting CPU cycles on compression/decompression.
- Columnstore Index- Should I partition my columnstore Index?
- https://blogs.msdn.microsoft.com/sqlserverstorageengine/2016/10/04/columnstore-index-should-i-partition-my-columnstore-index/
- The interesting thing with columnstore index is that it is implicitly partitioned by rowgroups, an internal physical organization…SQL Server can find those rows efficiently by looking at the metadata for each rowgroup and eliminating the ones that are out of range. This begs the question if you should even consider partitioning a table with CCI as it is already implicitly partitioned. The short answer is ‘Yes’.
- SQLSweet16!, Episode 9: Scripting Always Encrypted operations
- https://blogs.msdn.microsoft.com/sqlcat/2016/10/07/sqlsweet16-episode-9-scripting-always-encrypted-operations/
- The key value prop in Always Encrypted is that SQL Server itself cannot decrypt the data as it will not have access to the ‘Column Master Key’ (CMK). This also poses a challenge for application developers / administrators as the only way to encrypt existing data is to essentially ‘pump it out’ into an application which has access to the CMK. Typically, this application for DBAs and developers is SQL Server Management Studio (SSMS)… The July 2016 release of SSMS (and later versions) introduced a set of PowerShell cmdlets… Of most interest to the specific scenario described above is the Set-SqlColumnEncryption cmdlet.
- SQLSweet16!, Episode 10: “I can eat glass …”, but can I load it into a database?
- https://blogs.msdn.microsoft.com/sqlcat/2016/10/19/sqlsweet16-episode-10-i-can-eat-glass-but-can-i-load-it-into-a-database/
- You can now use CODEPAGE=’65001′ [UTF-8 data] with BULK INSERT, bcp and OPENROWSET utilities. Note that this improvement is only scoped to input processing by bulk load utilities. Internally, SQL Server still uses the UCS-2 encoding when storing Unicode strings.
- Do it right! Deploying SQL Server R Services on computers without Internet access
- https://blogs.msdn.microsoft.com/sqlcat/2016/10/20/do-it-right-deploying-sql-server-r-services-on-computers-without-internet-access/
- Scenario 1: Interactive setup of SQL Server 2016 RTM
- Scenario 2: Interactive patching with SQL Server 2016 Cumulative Update
- Scenario 3: Interactive, ‘slip-streamed’ setup of SQL Server 2016 RTM
- Scenario 4: Unattended setup of SQL Server 2016 RTM
- Scenario 5: Unattended patching of SQL Server 2016 Cumulative Update
- Scenario 6: Unattended, ‘slip-streamed’ setup of SQL Server 2016 RTM
- Repltalk – Replication Tuning Knobs – LogReader
- SQL Server as a Machine Learning Model Management System
Recommended KB’s To Review Regularly
- Recommended hotfixes and updates for Windows Server 2012 R2-based failover clusters
- https://support.microsoft.com/en-us/kb/2920151
- Note: September 2016 (KB 3185279) update rollup for Windows RT 8.1, Windows 8.1, and Windows Server 2012 R2
- Recommended updates and configuration options for SQL Server 2012 and later versions with high-performance workloads
- https://support.microsoft.com/en-gb/kb/2964518
- Note for SQL Server 2016: You no longer have to enable these trace flags in SQL Server 2016 because the auto-detection of the associated logic for trace flags is already incorporated into the product.
- Troubleshooting SQL Server backup and restore operations
Fany Carolina Vargas | SQL Dedicated Premier Field Engineer | Microsoft Services