SQL Updates Newsletter – February 2017
Recent Releases and Announcements
- Cumulative Update #4 for SQL Server 2014 SP2
- Cumulative Update #11 for SQL Server 2014 SP1
- SQL Server vNext CTP 1.3 now available
- Always On Availability Groups on Linux
- Resumable online index rebuilds enables users to recover more easily from interruption of index builds, or split an index build across maintenance windows.
- Indirect checkpoint performance improvements. Indirect checkpoint is the recommended configuration for large databases and for SQL Server 2016, and now it will be even more performant in SQL Server vNext.
- Minimum Replica Commit Availability Groups setting enables users to set the minimum number of replicas that are required to commit a transaction before committing on the primary.
- Encoding hints in SQL Server Analysis Services is an advanced feature to help optimize refresh times
- https://blogs.technet.microsoft.com/dataplatforminsider/2017/02/17/sql-server-next-version-ctp-1-3-now-available/
- SQL Server 2016 Developer Edition in Windows Containers
- We are excited to announce the public availability of SQL Server 2016 SP1 Developer Edition in Windows Containers
- SQL Server 2016 in a Windows container would be ideal when you want to (1) Quickly create and start a set of SQL Server instances for development or testing. (2) Maximize density in test or production environments, especially in microservice architectures (3) Isolate and control applications in a multi-tenant infrastructure.
- https://blogs.msdn.microsoft.com/sqlserverstorageengine/2017/02/21/sql-server-2016-developer-edition-in-windows-containers/
- SQL Server Migration Assistant (SSMA) v7.3 is now available
- Gartner positions Microsoft as a leader in BI and Analytics Platforms for Ten Consecutive Years
- Released: SQL Server Data Tools 17.0 RC 2
- Most noteworthy is the addition of a menu bar to the Query Editor…[which] provide[s] quick and easy access to the same functions that Microsoft Excel and Power BI Desktop provide through the Query Editor ribbon.
- https://blogs.msdn.microsoft.com/analysisservices/2017/02/03/released-sql-server-data-tools-17-0-rc-2/
- Early technical preview of JDBC 6.1.4 for SQL Server released!
- Blob Auditing in Azure SQL Database is Generally Available
- We are excited to announce that SQL Blob Auditing is now Generally Available in Azure SQL Database.
- Blob Auditing tracks database events and writes audited events to an audit log in your Azure Storage account. Auditing can help maintain regulatory compliance, understand database activity, and gain insight into discrepancies and anomalies that could indicate business concerns or suspected security violations.
- Blob Auditing will replace Table Auditing
- Blob Auditing also supports Threat Detection, providing an additional layer of security that detects anomalous activities that could indicate a threat to the database
- https://blogs.msdn.microsoft.com/sqlsecurity/2017/02/23/blob-auditing-in-azure-sql-database-is-generally-available/
- ODBC Driver 13.1 for Linux Released
- Released: Microsoft Kerberos Configuration Manager for SQL Server v3.1
- The Kerberos Configuration Manager for SQL Server is a diagnostic tool that helps troubleshoot Kerberos related connectivity issues with SQL Server, SQL Server Reporting Services, and SQL Server Analysis Services. It can perform the following functions.
- Gather information on OS and Microsoft SQL Server instances installed on a server.
- Report SPN and delegation configurations on the server.
- Identify potential problems in SPNs and delegations.
- Fix potential SPN problems.
- This release (v 3.1) adds support for SQL Server 2016.
- https://blogs.msdn.microsoft.com/sqlreleaseservices/released-microsoft-kerberos-configuration-manager-for-sql-server-v3-1/
- With the latest SQL Server vNext CTP1.3 we introduced a new DMV sys.dm_db_stats_histogram.
- This DMV returns the statistics histogram for the specified object, equivalent to DBCC SHOW_STATISTICS WITH HISTOGRAM
- https://blogs.msdn.microsoft.com/sql_server_team/getting-more-statistics-information-programatically/
- [Video] Power BI Desktop February Feature Summary
- Word wrap on matrix row headers
- X- and Y-axis font size control,
- Line chart line thickness and join type controls
- PowerApps Common Data Service connector
- 2 new Quick Calcs: Percent of row total & percent of column total
- And more…
- https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-february-feature-summary/
- [Video} Announcing the Integration of Power BI reports in SharePoint Online
- The web part we are releasing enables SharePoint authors to embed Power BI reports directly in SharePoint Online pages with no code required! The feature is available today for Office 365 First Release customers
- Steps: Publish your Power BI report to your Power BI account, Get the URL to the report, Add the Power BI (preview) web part to your SharePoint Online page, Paste the URL of the report when prompted To finish, save and publish your page!
- The Power BI report web part requires all the viewers to have a Power BI Pro license.
- https://powerbi.microsoft.com/en-us/blog/integrate-power-bi-reports-in-sharepoint-online/
- Now in preview: Conversational BI with Q&A on Power BI mobile apps (iOS)
- We are happy to add a preview of Q&A capabilities to our Power BI iOS app
- https://powerbi.microsoft.com/en-us/blog/now-in-preview-conversational-bi-with-q-a-on-power-bi-mobile-apps-ios/
- Released: Public Preview for SQL Server Management Packs Update (6.7.16.0)
- Announcing general availability of Managed Disks and larger Scale Sets
- With this PaaS-like support, you no longer need to be concerned with the complexity of storage management nor worry about storage as you scale.
- Managed Disks are Azure Resource Manager (ARM) resources, can be fully templatized, and support both Standard and Premium Disk types.
- You only need to specify the size and type of the disk you want.
- You can create a blank disk, create one from a VHD in a storage account, or create one from an image as part of VM creation. You can even migrate an existing Azure Resource Manager VM to a VM with managed disks.
- https://azure.microsoft.com/blog/announcing-general-availability-of-managed-disks-and-larger-scale-sets/
- DSC Resource Kit Release January 2017
- The modules updated in this release are: AuditPolicyDsc, xDismFeature
- xExchange, xHyper-V, xNetworking, xPSDesiredStateConfiguration, xSQLServer, xWebAdministration
- https://blogs.msdn.microsoft.com/powershell/2017/01/25/dsc-resource-kit-release-january-2017/
Recent Whitepapers/E-books/Training/Tutorials
- [Video] R Services in SQL Server 2016
- [Video] Improve Speed and Reduce Overhead with Containers in Windows Server 2016
- [Video] SQL tools for Linux, Mac OS, and Windows
- [Video] Power BI, Flow, and PowerApps Webinars for February 23 - April 6
- The next two months we have an all-star set of webinars for your viewing and listening pleasure
- https://powerbi.microsoft.com/en-us/blog/community-webinars-feb-23-april-6/
- [Video] SQL Server in an Azure VM - What's New
Monthly Script/Tool Tips
- Getting more statistics information programmatically
- sys.dm_db_stats_properties DMV
- https://blogs.msdn.microsoft.com/sql_server_team/getting-more-statistics-information-programatically/
- [PowerShell] [Script Of Feb. 15] How to backup SQL database to Azure Blob storage by PowerShell
- [Script Of Feb. 13] How to automatically delete Windows event logs by date
- Debugging PowerShell a script in Visual Studio Code – Part 2
- Nearly everything has changed for SQL Server PowerShell
- As of this writing, we have received more than 30 new cmdlets since SQL Server 2016 became generally available.
- Sign ins: Add-SqlLogin, Get-SqlLogin, Remove-SqlLogin
- Data: Read-SqlTableData, Read-SqlViewData, Write-SqlTableData
- Error log: Get-SqlErrorLog, Set-SqlErrorLog, SQL Server Agent, Get-SqlAgent, Get-SqlAgentJob, Get-SqlAgentJobHistory, Get-SqlAgentJobSchedule, Get-SqlAgentJobStep, Get-SqlAgentSchedule
- Encryption: Add-SqlAzureAuthenticationContext, Add-SqlColumnEncryptionKeyValue, Complete-SqlColumnMasterKeyRotation, Get-SqlColumnEncryptionKey, Get-SqlColumnMasterKey, Invoke-SqlColumnMasterKeyRotation, New-SqlAzureKeyVaultColumnMasterKeySettings, New-SqlCngColumnMasterKeySettings, New-SqlColumnEncryptionKey, New-SqlColumnEncryptionKeyEncryptedValue, New-SqlColumnEncryptionSettings, New-SqlColumnMasterKey, New-SqlCspColumnMasterKeySettings, Remove-SqlColumnEncryptionKey. Remove-SqlColumnEncryptionKeyValue, Remove-SqlColumnMasterKey, Set-SqlColumnEncryption
- Improvements to existing cmdlets: Invoke-SqlCmd now allows a connection string or to output results as a DataTable.
- SSRS team, developed their own module (available on GitHub)
- https://blogs.technet.microsoft.com/heyscriptingguy/2017/01/25/nearly-everything-has-changed-for-sql-server-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: 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)
- SSAS extended events do not work in SQL Server vNext CTP 1.3. We plan to fix them for the next CTP.
- Importance of choosing correct bucket count of hash indexes on a memory optimized table
- Scenario: Memory optimized table query was very slow and never completed
- It’s critical you follow Determining the Correct Bucket Count for Hash Indexes https://msdn.microsoft.com/en-us/library/dn494956(v=sql.120).aspx to set an appropriate bucket count for hash indexes
- https://blogs.msdn.microsoft.com/psssql/2017/02/18/importance-of-choosing-correct-bucket-count-of-hash-indexes-on-a-memory-optimized-table/
- Be aware of 701 error if you use memory optimized table variable in a loop
- Scenario: Tried to insert 1 million rows into a memory optimized table variable and process them…deleted the rows from the memory optimized table variable and inserted another 1 million…run out of memory… [even though] existing rows [were deleted]
- This is actually by-design behavior documented in “Memory-Optimized Table Variables”)…“Unlike memory-optimized tables, the memory consumed (including deleted rows) by table variables is freed when the table variable goes out of scope)”. With a loop like above, all deleted rows will be kept and will consume memory until the end of the loop.
- https://blogs.msdn.microsoft.com/psssql/2017/02/22/be-aware-of-701-error-if-you-use-memory-optimized-table-variable-in-a-loop/
Recent Blog Posts and Articles
- Encoding Hints and SQL Server Analysis Services vNext CTP 1.3
- Value encoding provides better query performance for columns that are typically only used for aggregations. Hash encoding is preferred for group-by columns (often dimension-table values) and foreign keys.
- Encoding hints in CTP 1.3 allow the modeler to specify a preference for the encoding method given prior knowledge from data profiling and/or in response to re-encoding trace events.
- https://blogs.msdn.microsoft.com/analysisservices/2017/02/17/encoding-hints-and-sql-server-analysis-services-vnext-ctp-1-3/
- Parsing 4GB JSON with SQL Server
- Microsoft Enterprise Services Tips to using SQL Data Warehouse effectively
- Cloud-Scale Text Classification (NLP) with Convolutional Neural Networks on Microsoft Azure
- Upgrading a Replication Topology to SQL Server 2016
- Version support matrix for the major release versions for transactional and merge replication
- https://blogs.msdn.microsoft.com/sql_server_team/upgrading-a-replication-topology-to-sql-server-2016/
- Migration from SQL Server to Azure SQL Database Using Transactional Replication
- It is possible to create an Azure SQL Database subscriber
- BOL: Replication to SQL Database https://msdn.microsoft.com/en-us/library/mt589530.aspx
- https://blogs.msdn.microsoft.com/sqlcat/2017/02/03/migration-from-sql-server-to-azure-sql-database-using-transactional-replication/
- Leveraging Web Application Proxy in Windows Server 2016 to provide secure access to your SQL Server Reporting Services environment
- Exporting tables from SQL Server in JSON line-delimited format using BCP.exe
- How to Apply Transaction Logs to Secondary When it is Far Behind
- Scenario 2 The database is very large, making re-initialization prohibitive to the secondary replicas: 1. Determine the transaction logs that must be applied. Query the last_hardened_lsn for the database on the secondary. 2. Query the log backups in MSDB to find what log backup the secondary LSN falls in the range of. 3. Take database out of the availability group on the secondary. 4. Apply transaction logs to database on the secondary replica. 5. Add the database back into the availability group on the secondary and resume synchronization.
- https://blogs.msdn.microsoft.com/alwaysonpro/2017/02/22/how-to-apply-transaction-logs-to-secondary-when-it-is-far-behind-2/
- Live query troubleshooting unleashed
- In order to get insights into long running queries, the Live Query Statistics (LQS) feature allows you to tap into in-flight execution and do real-time analysis exploring runtime information. LQS requires enabling in-flight query execution statistics (costly, and not highly scalable for widespread use on busy servers). Data is then stored in the DMV sys.dm_exec_query_profiles, and available thru LQS UI or programmatically with direct querying.
- With the new lightweight query execution profiling we see dramatic reduction in the performance overhead of continuously collecting per-operator query execution statistics (such as actual number of rows). This new profiling infra can be enabled by using: Global TF 7412 and query_thread_profile extended event (debug channel)
- This enables a DBA to do live production query troubleshooting, by tapping to any in-flight execution, using for example SSMS Activity Monitor (below), or the new DMF sys.dm_exec_query_statistics_xml
- If your SQL Server is not already CPU bound and a 1.5% to 2% overhead is negligible for your workloads, we recommend you enable TF 7412 as a startup trace flag, and unleash the power of live query troubleshooting in production environments.
- https://blogs.msdn.microsoft.com/sql_server_team/live-query-troubleshooting-unleashed/
- Running SQL Server + ASP.Net Core in a container on Linux in Azure Container Service on Docker Swarm – Part 3
- Query Store: How it works? How to use it?
- SQL Server 2016 Row Level Security – A Practical Example
- SQL Server 2016 HA Series Part 2 – SSIS and Availability Groups
- From the primary replica, right click Integration Services Catalogs folder and select “Enable AlwaysOn Support”
- We now call an API which creates the required “additional” objects on each replica (login, tables, stored procedures) meaning upon failover the integration services catalog can continue to function as normal
- https://blogs.technet.microsoft.com/dataplatform/2017/02/15/sql-server-2016-ha-series-part-2-ssis-and-availability-groups/
- Getting Started with MS SQL Server on Red Hat Enterprise Linux
Fany Carolina Vargas | SQL Dedicated Premier Field Engineer | Microsoft Services