SQL Updates Newsletter – September 2016

Recent Releases and Announcements

• SQL Server 2016 – CU2
  • https://support.microsoft.com/en-us/kb/3182270
• SQL Server 2012 SP3 – CU5
  • https://support.microsoft.com/en-us/kb/3180915
• SQL Server 2012 SP2 – CU14
  • https://support.microsoft.com/en-us/kb/3180914
• Azure SQL Database v12 has landed in Azure Government
  • https://blogs.msdn.microsoft.com/azuregov/2016/09/27/azure-sql-database-v12-generally-available-in-azure-government/
• Announcing the launch of Windows Server 2016
  • At our Ignite conference in Atlanta we launched the newest release of our server operating system – Windows Server 2016
  • Download Windows Server 2016 trial  https://www.microsoft.com/en-us/evalcenter/evaluate-windows-server-2016
  • https://blogs.technet.microsoft.com/hybridcloud/2016/09/26/announcing-the-launch-of-windows-server-2016/
• Microsoft expands artificial intelligence (AI) efforts with creation of new Microsoft AI and Research Group
  • https://news.microsoft.com/2016/09/29/microsoft-expands-artificial-intelligence-ai-efforts-with-creation-of-new-microsoft-ai-and-research-group
• SQL Server Data Tools 16.4 Release
  • The SSDT 16.4 release adds support for Schema Compare in SqlPackage.exe and as an API
  • Integrated Workspace Mode for SSDT Tabular
  • https://blogs.msdn.microsoft.com/ssdt/2016/09/20/sql-server-data-tools-16-4-release/
• Announcing SQL Server Management Studio -16.4.1 Release
  • This update features new PowerShell cmdlets to help perform SQL Server login management, read/write data, and many improvements/bug fixes addressing several Connect Items.
  • https://msdn.microsoft.com/en-us/library/mt238290.aspx
• Public Preview for System Center Management Packs for SQL Server and Dashboards
  • https://www.microsoft.com/en-us/download/details.aspx?id=51645
• The first update for SQL Server Report Builder 2016 is now available in the Download Center
  • We addressed a number of items in this release that were related to creating and editing shared datasets
  • https://blogs.msdn.microsoft.com/sqlrsteamblog/2016/09/20/sql-server-2016-report-builder-update-now-available/
• We’re pleased to announce a release candidate of an updated Report Viewer 2016 control
  • Enhancements include: Modern browser support, Cross-browser printing, Report parameter positioning, Modern look-and-feel
  • https://blogs.msdn.microsoft.com/sqlrsteamblog/2016/09/23/embed-paginated-reports-into-asp-net-web-apps-using-the-report-viewer-2016-control/
• JSON is now Generally available in Azure SQL Database
  • JSON is available in all service tiers (basic, standard, and premium) but only in new SQL Database V12
  • https://blogs.msdn.microsoft.com/sqlserverstorageengine/2016/09/05/json-generaly-available-in-azure-sql-database/
• SQL Server 2016 now supports Windows Server 2016 Storage Spaces Direct
  • With the upcoming general availability of Windows Server 2016, we are pleased to announce that Microsoft SQL Server 2016 will support deploying databases on the new Storage Spaces Direct feature of Windows Server 2016.
  • Storage Spaces Direct, new in Windows Server 2016, enables customers to create highly scalable and flexible storage solutions, using local storage
  • The ability to aggregate locally attached storage across the nodes in a failover cluster enables customers to create very large and highly available pools of storage from types of devices which could not be leveraged before, such as inexpensive SATA SSD, or cutting edge solutions like NVMe flash, which must plug directly into the PCIe bus inside the machine.
  • https://blogs.technet.microsoft.com/dataplatforminsider/2016/09/27/sql-server-2016-now-supports-windows-server-2016-storage-spaces-direct/
• SQL Server Connector support for private Azure clouds
  • If you’re using a private Azure environment, such as Azure Government, Azure China, or Azure Germany, you can now use the SQL Server Connector to manage your TDE encryption in SQL Server using your Azure Key Vault keys.
  • Note that there is a TSQL syntax change if you’re using an Azure Key Vault from a private Azure cloud with the SQL Server Connector (you’ll need to provide the full Key Vault URI instead of just the Key Vault name when creating credentials in SQL Server).
  • https://blogs.msdn.microsoft.com/sqlsecurity/2016/09/14/sql-server-connector-support-for-private-azure-clouds/
• We are pleased to announce that starting from SQL Server 2016, transactional replication re-publisher is supported in an Always On Availability Group configuration.
  • https://blogs.msdn.microsoft.com/sql_server_team/transactional-replication-and-always-on-availability-groups-support-update/

Recent Whitepapers/E-books/Training/Tutorials

• [Video] Available Now: Videos from the Microsoft Machine Learning & Data Science Summit in Atlanta
  • https://blogs.technet.microsoft.com/machinelearning/2016/09/29/available-now-videos-from-the-microsoft-data-science-summit-in-atlanta/
  • https://channel9.msdn.com/Events/Machine-Learning-and-Data-Sciences-Conference/Data-Science-Summit-2016
• [Video] Microsoft Ignite On-Demand Sessions
  • https://ignite.microsoft.com/#fbid=MLijBJ0lFoJ
• Watch our Windows Server 2016 webcast October 13th
  • https://www.microsoft.com/en-us/cloud-platform/introducing-windows-server

 

Monthly Script Tips

• Schema Compare in SqlPackage and the Data-Tier Application Framework (DACFx)
  • Sample of new schema compare API
    • Using this API you can programmatically:
    • Load or create a Schema Compare (.scmp) file
    • Compare schemas and view results
    • Exclude specific differences
    • Create a script or publish to a target database
  • https://blogs.msdn.microsoft.com/ssdt/2016/09/20/schema-compare-in-sqlpackage-and-the-data-tier-application-framework-dacfx/
• [Script Of Sept. 6] How to determine versions and service pack levels of .NET Framework by Powershell
  • https://blogs.technet.microsoft.com/onescript/2016/09/06/script-of-sept-6-how-to-determine-versions-and-service-pack-levels-of-net-framework/

 

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.
  • https://blogs.msdn.com/b/sqlserverfaq/archive/2013/04/30/do-not-delete-files-from-the-windows-installer-folder.aspx
• Important: You must precede all Unicode strings with a prefix N when you deal with Unicode string constants in SQL Server
  • https://support.microsoft.com/kb/239530
• Unable to connect to SQL Server on azure VM due to an extra NSG applied to subnet
  • https://blogs.msdn.microsoft.com/psssql/2016/09/18/unable-to-connect-to-sql-server-on-azure-vm-due-to-an-extra-nsg-applied-to-subnet/
• SQL Server 2012 SP2 – CU14 fixes
  • https://support.microsoft.com/en-us/kb/3180914
  • FIX: MDX LastChild function returns incorrect result after ProcessUpdate a dimension in SSAS 2012
• SQL Server 2012 SP3 – CU5 fixes
  • https://support.microsoft.com/en-us/kb/3180915
  • Includes the following fixes among others:
    • SQL Server 2012 crashes with an access violation when you use the TRY…CATCH construct for bulk copy
    • FIX: Assertion failures occur when you query the database-state information in SQL Server 2012
    • CPU usage increases significantly when you execute queries that contain CHANGETABLE functions in SQL Server 2012 Service Pack 3
    • FIX: High CPU usage on SQL queries after you install SQL Server
    • FIX: Queries that run against secondary databases always get recompiled in SQL Server
    • SQL Server crashes because of an access violation error that occurs while it reads data from an event file target
    • SQL Server 2014 or 2012 doesn't start after you configure the tempdb database to use a very small log file
    • "The log backup chain is broken" error when the log backup process fails in SQL Server
  • SQL Server 2016 – CU2 fixes
    • https://support.microsoft.com/en-us/kb/3182270
    • Includes the following fixes among others:
      • FIX: High CPU usage causes performance issues in SQL Server 2016
      • FIX: Deadlock occurs when you acquire a SCH-M lock and alter a partition in SQL Server 2014 or 2016
      • FIX: DML statements are unexpectedly replicated to the subscribers in SQL Server 2014 or 2016
      • FIX: RDL report that's generated programmatically fails to run in SSRS
      • An access violation occurs when you execute two parent packages that run the same child package in parallel in SQL Server 2014 or 2016
      • Query returns incorrect results from nonclustered columnstore index under snapshot isolation level in SQL Server 2016
      • FIX: SQL Server crashes when you run a remote query in a stored procedure by using an invalid user name
      • Operating system error 32 when you restore a database in SQL Server 2014 or 2016
      • Incorrect results are returned when you execute a query that contains a GROUP BY operation in SQL Server 2016
      • A non-optimal query plan choice results in poor performance when values outside the range represented in statistics are searched in SQL Server 2016
      • Updating while compression is in progress can lead to nonclustered columnstore index corruption in SQL Server 2016
      • FIX: An access violation occurs when you use the TDE and BPE features in SQL Server 2014 or 2016
      • A memory leak occurs when you use Azure Storage in SQL Server 2014 or 2016
      • FIX: Access violation occurs when execute a query that contains many COUNT DISTINCT operations in SQL Server 2016
      • An access violation occurs when you execute a query if trace flag 4139 is enabled in SQL Server 2016
      • FIX: Query runs slowly when SQL Server uses hash aggregate in the query plan

 

Recent Blog Posts and Articles

• Introducing DSC Data Driven Deployment
  • This is a Proof of Concept Project on how a database solution can be utilized to manage DSC configurations.
  • https://blogs.msdn.microsoft.com/troy_aults_blog/2016/09/09/introducing-dsc-data-driven-deployment/
• SQL Server 2016 – It Just Runs Faster: Always On Availability Groups Turbocharged
  • Prior to SQL Server 2016, it could take as many as 15 worker thread context switches across both the primary and secondary replicas to replicate a log block… now [in SQL 2016] the path can be as little as 8 worker thread context switches across both machines provided the hardware can keep pace.
  • In SQL 2016 the LogWriter thread on the primary [can] directly submit network I/O to the secondary.
  • Communication workers can stream log blocks in parallel to the secondary and execute on hidden schedulers to avoid any bottlenecks with other read workloads on the primary.
  • On the secondary, [SQL] can spin up multiple LogWriter threads on NUMA machines and apply redo operations from the log in parallel.
  • We also streamlined several areas of the code to avoid spinlock contention
  • We also streamlined and improved our encryption algorithms (including taking advantage of AES-NI hardware) so ensure it could keep us the pace as well.
  • Performance is not the only reason to consider an upgrade to SQL Server 2016 for Always On Availability Groups. Consider these enhancements that make it a compelling choice:
    • Domain Independent Availability Groups.
    • Round-robin load balancing in readable secondaries
    • Increased number of auto-failover targets
    • Support for group-managed service accounts
    • Support for Distributed Transactions (DTC)
    • Basic HA in Standard edition
    • Direct seeding of new database replicas
  • https://blogs.msdn.microsoft.com/sqlserverstorageengine/2016/09/26/sql-server-2016-it-just-runs-faster-always-on-availability-groups-turbocharged/
• SQLSweet16!, Episode 8: How SQL Server 2016 Cumulative Update 2 (CU2) can improve performance of highly concurrent workloads
  • If you download and install Cumulative Update 2 for SQL Server 2016 RTM, you will observe two new spinlocks in the sys.dm_os_spinlock_stats view: LOCK_RW_CMED_HASH_SET and LOCK_RW_SECURITY_CACHE.
  • These are improved reader/writer versions of the original spinlocks. For example, LOCK_RW_CMED_HASH_SET is basically the replacement for CMED_HASH_SET
  • https://blogs.msdn.microsoft.com/sqlcat/2016/09/29/sqlsweet16-episode-8-how-sql-server-2016-cumulative-update-2-cu2-can-improve-performance-of-highly-concurrent-workloads/
• [Detailed Walkthrough] Using SQL Always Encrypted with Azure Web App Service
  • https://blogs.msdn.microsoft.com/sqlcat/2016/09/15/using-sql-always-encrypted-with-azure-web-app-service/
• Understanding the requirements for SeSecurityPrivilege to SQL setup account on remote fileserver when default backup folder is set to UNC path
  • https://blogs.msdn.microsoft.com/sql_server_team/understanding-the-requirements-for-sesecurityprivilege-to-sql-setup-account-on-remote-fileserver-when-default-backup-folder-is-set-to-unc-path/
• [Recently Updated BOL] Trace Flags (Transact-SQL)
  • Many of the entries link to their respective KBs
  • https://msdn.microsoft.com/en-US/library/ms188396.aspx

 

Fany Carolina Vargas | SQL Dedicated Premier Field Engineer | Microsoft Services

Comments

• Anonymous
  October 07, 2016
  Great source of information!! Thanks for the nice write-up!