SQL Updates Newsletter – March 2017
Recent Releases and Announcements
- SQL Server next version CTP 1.4 now available
- The primary enhancement to SQL Server v.Next on Linux in this release is the ability to schedule jobs using SQL Server Agent.
- The mssql-server-linux container image on Docker Hub now includes the sqlcmd and bcp command line utilities to make it easier to create and attach databases and automate other actions when working with containers.
- https://blogs.technet.microsoft.com/dataplatforminsider/2017/03/17/sql-server-next-version-ctp-1-4-now-available/
- Cumulative Update #2 for SQL Server 2016 SP1
- Cumulative Update #5 for SQL Server 2016 RTM
- Cumulative Update #8 for SQL Server 2012 SP3
- Released: Data Migration Assistant (DMA) v3.1
- Data Migration Assistant (DMA) enables you to upgrade to a modern data platform by detecting compatibility issues that can impact database functionality on your new version of SQL Server. It recommends performance and reliability improvements for your target environment. It also allows you to not only move your schema, data, but also uncontained objects from your source server to your target server.
- DMA replaces all previous versions of SQL Server Upgrade Advisor and should be used for upgrades for most SQL Server versions (see below for supported versions).
- https://blogs.msdn.microsoft.com/sql_server_team/released-data-migration-assistant-dma-v3-1/
- Released: System Center Management Pack for SQL Server and Dashboards (6.7.20.0)
- Announcing R Tools 1.0 for Visual Studio 2015
- This release will be shortly followed by R Tools 1.0 for Visual Studio 2017 in early May.
- RTVS is a free and open source plug-in that turns Visual Studio into a powerful and productive R development environment.
- https://blogs.technet.microsoft.com/machinelearning/2017/03/23/announcing-r-tools-1-0-for-visual-studio-2015/
- SQL Server Replication enhancements in SQL Server 2016
- In SQL Server 2016, transaction replication re-publisher is supported in an AlwaysOn availability groups configuration.
- You can now configure transaction replication to replicate data to Azure SQL Database from on-premises SQL Server.
- In SQL Server 2016, tables acting as snapshot and transaction replication subscribers can be configured as memory-optimized tables. Peer-to-peer transaction replication and other replication types are not supported yet.
- Starting SQL Server 2016 SP1, a table included as an article of transaction replication publication can be dropped from the database and the publications.
- https://blogs.msdn.microsoft.com/sqlserverstorageengine/2017/03/22/sql-server-replication-enhancement-in-sql-server-2016/
- Early technical preview of JDBC 6.1.5 for SQL Server released!
- Now available: SQL Server Premium Assurance provides six more years of product support
- When you purchase Premium Assurance, you receive “critical” and “important” security updates and bulletins during the six years after the End of Extended Support. This means you can get up to 16 years of total support beginning with SQL Server 2008 and 2008 R2 versions.
- https://blogs.technet.microsoft.com/dataplatforminsider/2017/03/01/now-available-sql-server-premium-assurance-provides-six-more-years-of-product-support/
- SSMS DAX Query Editor in SSMS RC3 for vNext
- We are excited to announce the SQL Server Management Studio DAX Query Editor! Have you ever authored a DAX query in SSMS using the MDX editor? With the new DAX Query Editor, you no longer need to.
- IntelliSense works for DAX functions and model objects.
- This is the first release of the SSMS DAX Query Editor; it is not yet in GA status. We are still adding enhancements.
- https://blogs.msdn.microsoft.com/analysisservices/2017/03/14/ssms-dax-query-editor/
- SQL Server Data Tools 17.0 RC3, and SSDT in VS2017
- Introducing SQL Server Data Tools packages for Analysis and Reporting Services for Visual Studio 2017
- The full SQL Server Data Tools (SSDT) for Visual Studio 2017 stand-alone download is not yet available. This is still a work in progress and should be available in the near future. The good news is that the installation packages for the preview versions of SQL Server Analysis Services and SQL Server Reporting Services project types are already available as Visual Studio Deployment (VSIX) packages.
- https://blogs.msdn.microsoft.com/analysisservices/2017/03/09/introducing-sql-server-data-tools-for-analysis-and-reporting-services-for-visual-studio-2017/
- https://marketplace.visualstudio.com/items?itemName=ProBITools.MicrosoftReportProjectsforVisualStudio
- https://marketplace.visualstudio.com/items?itemName=ProBITools.MicrosoftAnalysisServicesModelingProjects
- Query designer support for DAX now available in Report Builder and SQL Server Data Tools
- With the latest releases of Report Builder and SQL Server Data Tools – Release Candidate, you now have the ability to create native DAX queries against supported SQL Server Analysis Services tabular data models.
- https://blogs.msdn.microsoft.com/sqlrsteamblog/2017/03/09/query-designer-support-for-dax-now-available-in-report-builder-and-sql-server-data-tools/
- Gartner names Microsoft a leader in the Magic Quadrant for Data Management Solutions for Analytics
- Announcing the new Power BI Video Gallery and Ask a Partner Anything live event
- DSC Resource Kit Release March 2017
- The modules updated in this release are: OfficeOnlineServerDsc, PSDscResources, SecurityPolicyDsc, SharePointDsc, xCertificate, xExchange, xPSDesiredStateConfiguration, xRemoteDesktopSessionHost, xSQLServer, xWindowsUpdate
- https://blogs.msdn.microsoft.com/powershell/2017/03/08/dsc-resource-kit-release-march-2017/
- Power BI mobile apps feature summary – February 2017
- Power BI Service February Feature Summary
- With this release, we have added a quick access area to the left navigation pane that gives you single click access all dashboards, reports, workbooks and datasets in the current workspace.
- The quick access area also allows you to perform manual refresh or setup scheduled refresh on your dataset.
- We’re happy to announce that you can now embed Powerbi.com dashboards into your custom applications. You’ve long been able to embed Powerbi.com reports and tiles into your custom apps - this functionality is simply the exciting next step. Dashboard embedding means that you can bring many powerful dashboard features into your applications.
- https://powerbi.microsoft.com/en-us/blog/power-bi-service-february-feature-summary/
- Power BI Desktop March Feature Summary
- Report View: Percent of total on pie and donut chart data labels
- Analytics: Clustering is now generally available
- New Connector: Azure Analysis Services; Azure Data Lake Store connector is now generally available
- Query editing
- Split column by delimiter: automatic detection of delimiter character
- And more…
- https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-march-feature-summary/
- Increasing PolyBase Row width limitation in Azure SQL Data Warehouse
- In the latest release of PolyBase in SQL DW, we have increased the row width limit to 1MB from 32KB.
- https://azure.microsoft.com/blog/increasing-polybase-row-width-limitation-in-azure-sql-data-warehouse/
- Announcing Azure SQL Database Premium RS, 4TB storage options, and enhanced portal experience
- Today we are happy to announce the preview of the latest edition to our service tiers, Premium RS, a 4TB increase of storage limits for Premium P11 and P15
- Premium RS is designed for your IO-intensive workloads that need Premium performance but do not require the highest availability guarantees.
- You can now use up to 4TB of included storage with P11 and P15 Premium databases at no additional charge.
- https://azure.microsoft.com/blog/sql-database-4tb-premium-and-premium-rs-preview/
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)
- Troubleshooting Internal Load Balancer Listener Connectivity in Azure
- Why am I getting so many checkpoint files when I have In-Memory OLTP enabled?
- …in sys.dm_db_xtp_checkpoint_files, … the state_desc was “WAITING FOR LOG TRUNCATION” [for] most of the checkpoint files…Checkpoint files go through various stages before they can be deleted and removed. If it is “WAITING FOR LOG TRUNCATION”, they can’t be removed.
- https://blogs.msdn.microsoft.com/psssql/2017/03/06/why-am-i-getting-so-many-checkpoint-files-when-i-have-in-memory-oltp-enabled/
- A few new things in XML plan to help you troubleshoot query performance
- Scenario: In this case, customer stated a merge query ran very slowly and wanted us to help improve the performance.
- By examining ActualElapsedms which is displayed as “Actual Elapsed Time(ms)”, we quickly found out which operator was taking most of the time.
- We also added top wait stats for the query. if your query runs slowly but consumes very little CPU, this will be a great help.
- For both properties to show up in SSMS, you must be [on the] latest SSMS
- https://blogs.msdn.microsoft.com/psssql/2017/02/28/a-few-new-things-in-xml-plan-to-help-you-troubleshoot-query-performance/
- Replication Monitor could not insert the tracer token
- We’re seeing an uptick in customers using combination of AlwaysOn and Transactional Replication.
- Scenario: If a failover has occurred and Published database is now running on a node which was not Primary when Replication was setup, i.e. now on Secondary as Primary, inserting a Tracer Token via Replication Monitor will generate the following error: Replication Monitor could not insert the tracer token.
- Solution: You can still insert Tracer Token via sys.sp_posttracertoken on the current Primary then show latency in Replication Monitor, you just can’t insert new Token via Replication Monitor after failover to secondary replica.
- https://blogs.msdn.microsoft.com/repltalk/2017/03/29/replication-monitor-could-not-insert-the-tracer-token/
- Transaction Log won’t truncate when using Snapshot Replication to Azure SQLDB
- When enabled, table schema changes are tracked in the Transaction Log and not cleared unless either 1) publication is dropped, or 2) sp_repldone is manually executed.
- https://blogs.msdn.microsoft.com/repltalk/2017/03/29/transaction-log-wont-truncate-when-using-snapshot-replication-to-azure-sqldb/
- Backing up a VLDB to Azure Blob Storage
- If using Backup to URL to create striped backups of large databases (over 48 GB per stripe), specify MAXTRANSFERSIZE = 4194304 and BLOCKSIZE = 65536 in the BACKUP statement.
- Scenario: The following error is raised during backup to blob storage: Write to backup block blob device <url> failed. Device has reached its limit of allowed blocks.
- Solution: Make SQL Server use larger block sizes via the MAXTRANSFERSIZE parameter in the BACKUP DATABASE statement. For 4 MB blocks: BACKUP DATABASE … TO URL = '<>', … URL = '<>', WITH COMPRESSION, MAXTRANSFERSIZE = 4194304, BLOCKSIZE = 65536, CHECKSUM, FORMAT, STATS = 5;
- https://blogs.msdn.microsoft.com/sqlcat/2017/03/10/backing-up-a-vldb-to-azure-blob-storage/
Recent Blog Posts and Articles
- Columnstore Index – How to Estimate Compression Savings
- Customers can invoke sp_estimate_data_compression_savings stored procedure to estimate the storage savings for ROW and PAGE compression
- This stored procedure has not been extended to estimate storage savings from columnstore index. This is something we could consider for the future.
- To estimate compression savings for columnstore index, we recommend the following steps: (1)Create a staging table with identical schema (2)Load 2 million rows into the staging table. [We] have chosen 2 million arbitrarily but it needs to be at least 1 million. (3) Use sp_spaceused to find the size of the table (4) Now create columnstore index on the table (5) Measure the storage using sp_spaceused. Compare the numbers in (3) and (5)
- https://blogs.msdn.microsoft.com/sql_server_team/columnstore-index-how-to-estimate-compression-savings/
- Moving databases to new storage within an Availability Group
- Data Simulator For Machine Learning
- Getting more statistics information programmatically
- sys.dm_db_stats_properties, … has a similar output to running DBCC SHOW_STATISTICS … WITH STATS_HEADER.
- https://blogs.msdn.microsoft.com/sql_server_team/getting-more-statistics-information-programatically/
- Five reasons to run SQL Server 2016 on Windows Server 2016 — No. 1: Security
- The security functionality in Windows Server 2016 includes the following:
- Device Guard helps lock down what runs on the server so that you are better protected from unauthorized software running on the same server as your SQL Server application.
- Credential Guard to protect SQL Server admin credentials from being stolen by Pass-the-Hash and Pass-the-Ticket attacks. Using an entirely new isolated Local Security Authority (LSA) process, which is not accessible to the rest of the operating system, Credential Guard’s virtualization-based security isolates credential information to prevent interception of password hashes or Kerberos tickets.
- Control Flow Guard and Windows Defender protect against known and unknown vulnerabilities that malware can otherwise exploit. Control Flow tightly restricts what application code can be executed — especially indirect call instructions. Lightweight security checks identify the set of functions in the application that are valid targets for indirect calls. When an application runs, it verifies that these indirect call targets are valid. Windows Defender works hand-in-hand with Device Guard and Control Flow Guard to prevent malicious code of any kind from being installed on your servers.
- https://blogs.technet.microsoft.com/dataplatforminsider/2017/03/23/five-reasons-to-run-sql-server-2016-on-windows-server-2016-no-1-security/
- Five reasons to run SQL Server 2016 on Windows Server 2016 – No. 2: Performance and cost
- SQL Server professionals know that database transactions can be gated by log write speed. If the log is faster, more database updates are possible. Windows Server 2016 helps solve this with Persistent Memory (aka Storage Class Memory).
- Storage Spaces Direct in Windows Server 2016 allows use of industry-standard servers with local storage as a highly available, scalable alternative to expensive storage area networks (SANs) — with read speeds that can exceed 25 GB/second.
- Windows Server 2016 has built-in capability to provide in-memory with 24 terabytes of available server memory. Plus, new CPU maximums have been increased by three times so that you can run up to 640 CPU cores.
- https://blogs.technet.microsoft.com/dataplatforminsider/2017/03/30/five-reasons-to-run-sql-server-2016-on-windows-server-2016-part-2/
Recent Training and Technical Guides
- [Video] Improve Deployment Speed and Reduce Overhead with Containers in Windows Server 2016
- [Video] SQL Server vNext Management Pack Demo
- Starting with R-Scripting - Power BI
- Learn about Azure Analysis Services at the Microsoft Data Insights Summit 2017
- Webinars 3/30-5/31: R Script, Marketing Insights, Modeling Deep Dive, New Flow Features, Power BI Embedded, and Visualization Best Practices from Marco Russo
- We have an exciting selection of webinars coming up over the next few months!
- https://powerbi.microsoft.com/en-us/blog/webinars-r-script-marketing-insights-modeling-deep-dive-new-flow-features-power-bi-embedded-and-visualization-best-practices-from-marco-russo/
- Online Analysis Services Course: Developing a Tabular Model
- Lean how to develop tabular data models with SQL Server 2016 Analysis Services. The complete course is available on edX at no cost to audit.
- https://blogs.msdn.microsoft.com/analysisservices/2017/03/22/online-analysis-services-course-developing-a-tabular-model/
Monthly Script and Tool Tips
- [Script Of Mar. 24] Determining which version and edition of SQL Server Database Engine is running
- Announcing Microsoft Azure Storage Explorer 0.8.9
- Get your own Power Query Editor using Notepad++
- Getting more statistics information programmatically
- sys.dm_db_stats_properties DMV
- https://blogs.msdn.microsoft.com/sql_server_team/getting-more-statistics-information-programatically/
- [Script Of Mar. 2] How to export SQL Server bacpac backup file by PowerShell
- [Script Of Mar. 23] How to calculate Azure SQL databases price by PowerShell
- [Script Of Mar. 10] How to backup outlook emails by PowerShell
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: November 2016 (KB3197874) security monthly quality rollup for 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