SQL Updates Newsletter – August 2016
Recent Releases and Announcements
- Critical Update for SQL Server 2016 Analysis Services (KB3179258)
- https://www.microsoft.com/en-us/download/details.aspx?id=53583
- A possibility of data-loss has been identified in the SQL Server 2016 Analysis Services after partial processing of tabular model. Processing a partition causes data loss on other partitions after the database is restored or attached.
- After you install this item, you may have to restart your computer.
- SQL Server 2014 SP2 – CU #1
- SQL Server 2014 SP1 – CU #8
- SQL Server Data Tools in VS 2015 – August 2016 release
- Microsoft Security Bulletin Summary for August 2016
- Always Encrypted feature is part of updated ODBC Driver 13.1 for SQL Server
- Announcing SQL Server Management Studio -16.3 (August 2016) Release
- Download: https://msdn.microsoft.com/en-us/library/mt238290.aspx
- This generally available release of SSMS is free and does not require a SQL Server license to install and use.
- This update of SSMS features a switch from monthly-branded SSMS releases (e.g. August 2016 release) to numerically-branded SSMS releases
- This update also features a new Active Directory authentication option, new PowerShell cmdlets to help perform SQL Server login management, initial beta support for high-resolution displays, support for filtering in the ‘Databases’ node of the Object explorer, New Extended Events templates matching the functionality of SQL Server Profiler templates, and many more improvements and bug fixes.
- Microsoft products reaching end of support in the second half of 2016
- Introducing Tabular Model Explorer for SQL Server Data Tools for Analysis Services Tabular Projects (SSDT Tabular)
- Azure Storage PowerShell v.1.7
- Register for the Data Science Summit in Atlanta, GA
- JSON support is generally available in Azure SQL Database
- Microsoft Azure Stack: Upcoming Technical Preview and other updates
- GA for Azure AD authentication in SQL Database and SQL Data Warehouse
- https://azure.microsoft.com/en-us/blog/ga-for-azure-ad-authentication-in-sql-database-and-sql-data-warehouse/
- Azure Active Directory (Azure AD) authentication is generally available in Azure SQL Database and Azure SQL Data Warehouse.
- Newest Azure ExpressRoute location now available in Dallas
- Custom visual developer tool now GA
- https://powerbi.microsoft.com/en-us/blog/power-bi-custom-visuals-developer-tools-hit-v1/
- The Power BI extensibility team has redesigned the developer experience and made a new developer tool generally available. The tool is command-line based and allows users to test visuals inside a report with real data, among other improvements.
- Visual Studio ‘15’ Preview 4 now available
- https://blogs.microsoft.com/firehose/2016/08/22/visual-studio-15-preview-4-now-available/
- The highlight of this release is that nearly all of Visual Studio is running on the new setup engine, resulting in a smaller, faster and less impactful installation
- The smallest install is less than 500 MB on disk, compared to 6GB in the previous release of Visual Studio
- Microsoft: a Gartner cloud computing leader across IaaS, PaaS, and SaaS
- https://azure.microsoft.com/en-us/blog/microsoft-a-gartner-cloud-computing-leader-across-iaas-paas-saas/
- Gartner has named Microsoft a leader in its Magic Quadrant for Cloud Infrastructure as a Service Worldwide for the third year in a row.
- SQL PowerShell: July 2016 update
Recent Whitepapers/E-books/Training/Tutorials
- [Video] A Rambling Discourse on the State of SQL Server as it Applies to Developers
- [Video] Accelerating SQL Server 2016 performance with Persistent Memory in Windows Server 2016
- [Video] Tips to ensure High Availability with SQL Server Failover Clustering and Cluster Shared Volume
- [Video] Using JSON in SQL Server 2016 and Azure SQL Database
- [Video] Using Temporal in SQL Server 2016 and Azure SQL Database
- [Video] Energy Demand Forecasting Template with SQL Server R Services
- [Video] Introduction to Azure SQL Data Warehouse
- [Video] SQL Server 2016: It's Just Faster
Monthly Script Tips
- [Script Of Aug. 17] Get Active Directory user account last logged on time (PowerShell)
- [Script Of Aug. 15] How to create Azure VM by PowerShell using classic ASM API
- [Script Of Aug. 16] How to check execution time and receive/send bytes from website 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.
- Important: You must precede all Unicode strings with a prefix N when you deal with Unicode string constants in SQL Server
- Challenges running continuous Merge agents
- SQL Server 2014 SP2 – CU #1 fixes
- https://support.microsoft.com/en-us/kb/3178925
- Includes the following fixes among others:
- FIX: SQL Server crashes when you run a remote query in a stored procedure by using an invalid user name
- FIX: SQL Server 2012 failover cluster diagnostic log doesn't accurately reflect the failure_condition_level setting
- FIX: Incorrect results when you use a LIKE operator and an "ss" wildcard in SQL Server 2014 or 2016
- FIX: A valid derived column expression may fail in SSIS 2012, 2014 and 2016
- SQL Server 2014 SP1 – CU #8 fixes
- https://support.microsoft.com/en-us/kb/3174038
- Includes the following fixes among others:
- An access violation occurs when you execute two parent packages that run the same child package in parallel in SQL Server 2014
- An error occurs when you use ODBC driver to retrieve sql_variant data in SQL Server 2014
- SQL Server 2014 crashes because of an access violation error that occurs while it reads data from an event file target
- Operating system error 32 when you restore a database in SQL Server 2014
- FIX: Deadlock occurs when you acquire a SCH-M lock and ALTER PARTITION in SQL Server 2014
- FIX: "Cannot alter column" error after a database is restored to SQL Server
- FIX: Query runs slowly when SQL Server uses hash aggregate in the query plan
- Decrease in performance and "non-yielding scheduler" errors caused by unnecessary spinlocks in SQL Server
- Merge publication snapshot fails with "Script failed" error in SQL Server
Recent Blog Posts and Articles
- SQL Threat Detection – Your built-in security expert
- https://blogs.msdn.microsoft.com/sqlsecurity/2016/08/08/sql-threat-detection-your-built-in-security-expert/
- SQL Threat Detection on Azure SQL Database detects potential vulnerabilities and SQL injection attacks, as well as anomalous database access patterns.
- Case #1: Attempted database access by former employee
- Case #2: Preventing SQL Injection attacks
- Case #3: Anomalous access from home to production database
- Each Azure SQL Database Threat Detection notification provides the details of the anomalous activity and recommends how to further investigate and mitigate the potential threat.
- Changes in SQL Server 2016 Checkpoint Behavior
- https://blogs.msdn.microsoft.com/sqlcat/2016/08/03/changes-in-sql-server-2016-checkpoint-behavior/
- The default checkpoint behavior in SQL Server prior to 2016 is to run automatic checkpoints when the log records reach the number of records the database engine estimates it can process within the “recovery interval”… Indirect checkpoint provides the ability to set a target recovery time for a database (in seconds).
- When enabled, indirect checkpoint results in constant background writes of modified data pages vs. periodic flushes of modified pages.
- In addition to configuring indirect checkpoint SQL Server also exposes the ability to utilize a startup parameter (-k) followed by a decimal value which will configure the checkpoint speed in MB per second.
- Indirect checkpoint is the default behavior for new databases created in SQL Server 2016.
- When performing a checkpoint SQL Server considers the response time of the I/O’s and adjusts the amount of outstanding I/O in response to response times exceeding a certain threshold. In versions prior to SQL Server 2016 this threshold was 20ms. In SQL Server 2016 the threshold is now 50ms.
- The SQL Server engine will consolidate modified pages into a single physical transfer if the data pages are contiguous at the physical level. In prior versions, the max size for a transfer was 256KB. Starting with SQL Server 2016 the max size of a physical transfer has been increased to 1MB.
- Automatic checkpoints are exposed as “Checkpoint Pages/sec” perfmon counter. Indirect checkpoints are exposed as “Background Writer pages/sec” perfmon counter.
- TRUNCATE Selected Partitions
- https://blogs.msdn.microsoft.com/sqlcat/2016/08/11/sqlsweet16-episode-5-truncate-selected-partitions/
- SQL Server 2016 introduces a WITH PARTITIONS clause for TRUNCATE TABLE statement that allows specifying a selected set of partitions (you can specify more than one partition at once)
- Example: TRUNCATE TABLE <tblName> WITH (PARTITIONS (7, 8))
- DBCC CHECKDB with MAXDOP
- https://blogs.msdn.microsoft.com/sqlcat/2016/08/17/sqlsweet16-episode-6-dbcc-checkdb-with-maxdop/
- [In SQL 2016] you can apply a MAXDOP option to the DBCC CHECKDB command
- Example: DBCC CHECKDB WITH MAXDOP = 4
- Install Option for Instant File Initialization
- https://blogs.msdn.microsoft.com/sqlcat/2016/08/23/sqlsweet16-episode-7-install-option-for-instant-file-initialization/
- If you want to enable instant file initialization, SQL Server 2016 makes life simpler for DBAs and System Administrators by providing a simple “Grant Perform Volume Maintenance” checkbox during the install of SQL Server
- Migrating data to Azure SQL Data Warehouse in practice
- Supported SQL Server versions in Replication Topology
- https://blogs.msdn.microsoft.com/repltalk/2016/08/12/suppported-sql-server-versions-in-replication-topology/
- Upgrade Replicated Databases: https://msdn.microsoft.com/en-us/library/ms143699.aspx
- Key point: Publisher is within 2 builds of the Subscriber and the Distributor is same or higher build then Publisher.
- Script to show Replication Topology
- Using Table Valued Parameters with Always Encrypted in SQL Server 2016 and Azure SQL Database
- Operator progress changes in Live Query Statistics (LQS)
- Improving Concurrency & Scalability of SQL Server workload by optimizing database containment check in SQL 2014\SQL 2016
- https://blogs.msdn.microsoft.com/sql_server_team/improving-concurrency-scalability-of-sql-server-workload-by-optimizing-database-containment-check-in-sql-2014sql-2016/
- Starting SQL 2014 SP1-CU8, SQL 2014 SP2-CU1 and SQL 2016 RTM-CU1, the spinlock to check the database containment property is replaced by the “load acquire and store release” lock semantics, which is a non-blocking lock-free synchronization mechanism between the concurrent threads.
Windows Server 2016 – Get Started
- [Video] What’s New in Windows Server
- Windows Server 2016 Data Sheet
- Windows Server channel
- 10 Reasons You'll Love Windows Server 2016
- Feature Deep Dives
- https://channel9.msdn.com/Blogs/windowsserver
- Containers channel
- Container Information
- Demos
- Quick Starts
- Feature Deep Dives
- https://channel9.msdn.com/Blogs/containers
- Windows Server Blog
- Virtual Hands On Labs- Tech Preview
- Windows Server 2016
- OMS Hybrid Management
- Installing and Managing Nano Server
- Managing Windows Server Containers with Docker
- Managing Windows Server Containers with PowerShell
- https://technet.microsoft.com/en-us/virtuallabs/bb467605.aspx
Fany Carolina Vargas | SQL Dedicated Premier Field Engineer | Microsoft Services