Recent Releases and Announcements

• Announcing SQL Server 2012 Service Pack 4
  • SQL Server 2012 SP4 will be released in September 2017 (note the date has changed from previous announcement)
  • A release announcement with detailed information on improvements will be shared at the time of the release.
  • https://blogs.msdn.microsoft.com/sqlreleaseservices/announcing-sql-server-2012-service-pack-4/
• Petya Ransomware Prevention & Detection in Azure Security Center
  • Azure Security Center customers who have opted into Standard-Tier can benefit from a new detection recently added to alert on specific indicators related to Petya ransomware running on an infected host.
  • https://azure.microsoft.com/en-us/blog/petya-ransomware-prevention-detection-in-azure-security-center/
• Identity Columns Now Available in SQL Data Warehouse
  • We’re excited to announce that Azure SQL Data Warehouse now supports an IDENTITY column property as well as SET IDENTITY_INSERT syntax and generating IDENTITY on load.
  • https://azure.microsoft.com/en-us/blog/identity-now-available-with-azure-sql-data-warehouse/
• Announcing Microsoft Azure Government services in the Cloud Solution Provider program
  • With the CSP program, partners can now create high-value service offerings that combine use of Azure Government with solution management, customer support, and billing to U.S. government customers.
  • https://azure.microsoft.com/en-us/blog/announcing-microsoft-azure-government-services-in-the-cloud-solution-provider-program/
• SQL Server Diagnostics (Preview) Extension
  • Microsoft is releasing the SQL Server Diagnostics (Preview) extension within SQL Server Management Studio and Developer APIs to empower SQL Server customers to achieve more through a variety of offerings to self-resolve SQL Server issues.
  • View Recommendations and Analyze Dumps – Customers using this extension will be able to debug and self-resolve memory dump issues from their SQL Server instances and receive recommended Knowledge Base (KB) article(s) from Microsoft, which may be applicable for the fix.
  • This extension requires Microsoft SQL Server Management Studio (SSMS) 2016 or a later version.
  • https://blogs.msdn.microsoft.com/sql_server_team/sql-server-diagnostics-preview/
• Interactive R Visuals in Power BI
  • In the latest update to Power BI, you can create R custom visuals that embed interactive R charts
  • http://blog.revolutionanalytics.com/2017/06/interactive-r-visuals-in-power-bi.html
  • https://powerbi.microsoft.com/en-us/blog/interactive-r-custom-visuals-support-is-here/
• Introducing Dashboard Email Subscriptions
  • We are excited to announce the public preview of email subscriptions for dashboards in the Power BI service.
  • Like report page subscriptions, dashboard subscriptions require a Pro license.
  • https://powerbi.microsoft.com/en-us/blog/introducing-dashboard-email-subscriptions-a-360-degree-view-of-your-business-in-your-inbox-every-day/
• Released: SQL Server Management Pack Updates
  • SQL Server, Replication, AS, RS, Dashboards (6.7.31.0): https://blogs.msdn.microsoft.com/sqlreleaseservices/released-system-center-management-pack-for-sql-server-replication-as-rs-dashboards-6-7-31-0/
  • SQL 2017 Preview: https://blogs.msdn.microsoft.com/sqlreleaseservices/released-public-preview-for-sql-server-2017-and-replication-management-pack-ctp4/
• DSC Resource Kit Release May 2017
  • The modules updated in this release are:  SharePointDsc, SecurityPolicyDsc, xCertificate, xComputerManagement, xCredSSP, xExchange, xHyper-V, xNetworking, xSqlServer, xStorage
  • https://blogs.msdn.microsoft.com/powershell/2017/05/31/dsc-resource-kit-release-may-2017/
• SQL Server Migration Assistant (SSMA) v7.4 is now available
  • https://blogs.msdn.microsoft.com/sql_server_team/sql-server-migration-assistant-ssma-v7-4-is-now-available/
• The Associated Press takes its data storytelling to the next level with Microsoft Power BI
  • AP and Microsoft are excited to announce that AP will use Microsoft Power BI, a powerful data analytics and visualization tool, to expand the way AP shares data with its member news organizations and customers
  • https://blogs.microsoft.com/blog/2017/06/14/associated-press-takes-data-storytelling-next-level-microsoft-power-bi/
• Microsoft AI – Now Serving Critical Care Patients, Water-Insecure Populations in Africa, Bank Customers in New Zealand & Many More
  • Addressing Water Security in Kenya: The team has developed sensors …[and put] them inside water pump handles on rural wells. These sensors are used to monitor groundwater level and to improve the pace of repairs that may be needed to fix broken pumps. Their accelerometer and gyroscope record the motion and vibration of pump handles and, by applying machine learning techniques on this data, predicts whether the water is coming from a deep or a shallow source, and how much remains underground.
  • Providing Best-in-Class Banking: Bank employees are now able to get answers to questions such as “What would happen to our deposits if we increased our six-month deposit rate by 20 basis points over the next three months?” Many more exciting opportunities are on the horizon, such as using cognitive services for facial recognition, for instance, to compare the image on photo IDs with loan applicants, as an added layer of security.
  • Boosting the ROI on Marketing Campaigns: When a user clicks on an ad, for instance, a set of data points are sent to the Cortana Intelligence Suite, and predictions based on those data points get sent back to Track Revenue. The Track Revenue service then serves the most appropriate landing page or offer to the potential buyer, with the entire process getting completed in milliseconds.
  • https://blogs.technet.microsoft.com/machinelearning/2017/06/05/microsoft-ai-now-serving-critical-care-patients-water-insecure-populations-in-africa-bank-customers-in-new-zealand-many-more/
• Power BI Service April and May Feature Summary
  • Announcing Power BI Premium: Builds on the existing Power BI portfolio with a capacity-based licensing model
  • Usage metrics for dashboards and reports: This feature gives you one-click access to key usage metrics for your dashboards and reports, allowing you to pinpoint how your end users are interacting with your content.
  • PostgreSQL support in the on-premises data gateway
  • Power BI SharePoint web part now available for all users
  • Data connector SDK: A developer preview of the Data Connector SDK, allowing for the creation of custom data connectors.
  • Multiple URL filter parameters: We now support multiple URL query string parameters. For example – following URL will navigate to the report with two filters one for the Territory attribute in the Store table, and one for the Month attribute in the Sales table: ?filter=Store/Territory eq ‘NC’ and Sales/Month eq ’May’
  • And more…
  • https://powerbi.microsoft.com/en-us/blog/power-bi-service-april-and-may-feature-summary/
  • https://powerbi.microsoft.com/en-us/blog/measure-and-magnify-your-impact-with-usage-metrics-for-dashboard-and-report-authors/
• Power BI Mobile app feature summary – May 2017
  • New “Shared with Me” view, designed to reduce clutter when searching for dashboards that are shared with you by others.
  • We’re also introducing Apps! These collections of purpose-built dashboards and reports are grouped together so you can share BI information with your colleagues simply by pasting the URL address from your browser.
  • Additionally, you can now connect to a Power BI Report Server, and view Power BI reports directly in the app.
  • https://powerbi.microsoft.com/en-us/blog/power-bi-mobile-apps-feature-summary-may-2017-3/
• Announcing Storage Service Encryption with Customer Managed Keys limited preview
  • This is one of the most requested features by enterprise customers looking to protect sensitive data as part of their regulatory or compliance needs, HIPAA and BAA compliant.
  • Customers can rotate their key in Azure Key Vault as per their compliance policies. When they rotate their key, Azure Storage detects the new key version and re-encrypts the Account Encryption Key for that storage account. This does not result in re-encryption of all data and there is no other action required from user.
  • https://azure.microsoft.com/en-us/blog/announcing-storage-service-encryption-with-customer-managed-keys-limited-preview-for-azure-blob-storage/
• Announcing large disk sizes of up to 4 TB for Azure IaaS VMs
  • At Build Conference, we announced the addition of new Azure Disks sizes – which provide up to 4TB of disk space.
  • https://azure.microsoft.com/en-us/blog/azure-introduces-new-disks-sizes-up-to-4tb/
• Power BI Report Server now generally available
  • Publish reports to an on-premises Power BI Report Server
  • Power BI Report Server is part of Power BI Premium
  • https://blogs.msdn.microsoft.com/sqlrsteamblog/2017/06/12/power-bi-report-server-now-generally-available/
• Power BI Premium generally available
  • Power BI Premium enables the same number of virtual cores an organization provisions in the cloud to also be deployed on-premises without the need to split the capacity.
  • SQL Server Enterprise Edition customers with active Software Assurance (SA) have the rights to deploy Power BI Report Server through SA entitlement.
  • The Visio custom visual for Power BI will be available in the custom visuals store in July
  • https://powerbi.microsoft.com/en-us/blog/power-bi-premium-generally-available/
• Introducing Modern Backup Storage with Azure Backup Server on Windows Server 2016
  • Modern Backup Storage…leverages Windows Server 2016 native capabilities such as ReFS block cloning, deduplication and workload aware storage to optimize backup storage and time, and delivers nearly 50% disk
  • Provides storage savings and 3x faster backups.
  • https://azure.microsoft.com/en-us/blog/introducing-modern-backup-storage-with-azure-backup-server-on-windows-server-2016/
• Protect Windows Server System State to cloud with Azure Backup!
  • We are excited to preview the support for backing up Windows Server System State directly to Azure with Azure Backup.
  • https://azure.microsoft.com/en-us/blog/windows-server-system-state-backup-azure/
• Azure SQL Data Sync Refresh
  • With Azure SQL Data Sync users can easily synchronize data bi-directionally between multiple Azure SQL databases and/or on-premises SQL Databases. This release includes several major improvements to the service including new Azure portal support, PowerShell and REST API support, and enhancements to security and privacy.
  • https://azure.microsoft.com/en-us/blog/azure-sql-data-sync-refresh/
• Announcing Public Preview of Disaster Recovery for Azure IaaS Virtual Machines
  • You can now easily replicate and protect IaaS based applications running on Azure to a different Azure region of your choice within a geographical cluster without deploying any additional infrastructure components or software appliances in your subscription.
  • This new capability, along with Azure Backup for IaaS virtual machines, allows you to create a comprehensive business continuity and disaster recovery strategy for all your IaaS based applications running on Azure.
  • https://azure.microsoft.com/en-us/blog/announcing-disaster-recovery-for-azure-iaas-vms-using-asr/
• Higher database eDTU limits for Standard elastic pools in Azure SQL Database
  • Until now the maximum DTUs per database in a Standard elastic pool was limited to 100 eDTUs in Azure SQL Database. We are pleased to announce the public preview of an increase in this limit to as much as 3000 eDTUs with new click stop choices starting at 200 eDTUs
  • https://azure.microsoft.com/en-us/blog/higher-database-edtu-limits-for-standard-elastic-pools-in-azure-sql-database/
• Spark Connector for CosmosDB Provides Seamless Interaction with Globally-Distributed, Multi-Model data
  • We’re excited to announce that the Spark connector for Azure Cosmos DB is now truly multi-model
  • Azure Cosmos DB is Microsoft’s globally distributed, multi-model database service for mission-critical applications.
  • Azure Cosmos DB’s database engine natively supports SQL (DocumentDB) API, MongoDB API, Gremlin (graph) API, and Azure Table storage API.
  • With the updated Spark connector for Azure Cosmos DB, Apache Spark can now interact with all Azure Cosmos DB data models: Documents, Tables, and Graphs.
  • https://azure.microsoft.com/en-us/blog/spark-connector-for-cosmosdb-seamless-interaction-with-globally-distributed-multi-model-data/
• New Sample Model for Azure Analysis Services
  • Now you can try Azure Analysis Services without the need to build anything with our new sample model based on the Adventure Works Internet Sales database. It is designed to show a range of Analysis Services modeling features.
  • https://azure.microsoft.com/en-us/blog/new-sample-model-for-azure-analysis-services/
• GA of Cognitive Toolkit 2.0
  • We’re excited to announce the general availability of Cognitive Toolkit 2.0, Microsoft’s open source, enterprise-ready, production-grade AI offering.
  • Cognitive Toolkit allows users to create, train, and evaluate their own neural networks that can then scale efficiently across multiple GPUs and machines on massive data sets.
  • Cognitive Toolkit 2.0 is available on GitHub.
  • https://blogs.technet.microsoft.com/machinelearning/2017/06/01/announcing-ga-of-cognitive-toolkit-2-0-microsofts-open-source-enterprise-ready-tensorflow-outperforming-ai-offering/
• New Azure VPN Gateways now 6x faster
  • We are introducing a new generation of VPN gateways with better performance, a better SLA, and at the same price as our older gateways.
  • https://azure.microsoft.com/en-us/blog/new-azure-vpn-gateways-now-6x-faster/
• Power BI Desktop June Feature Summary
  • Data bars for new table & matrix (preview), Accessible reports, Horizontal Image Slicer, Bing maps improvements, Custom Data Connectors support, Power BI service Live Connect to On Premises and Push streaming datasets, “Add Column from Examples” enhancements, and more…
  • Data bars are a quick way to format your data to make it easier to compare ranges of values. They’re visually similar to turning a column of your table or matrix into a bar chart.
  • For line charts, area charts, and the lines of combo charts, we are adding a new formatting option that allows you to show markers on each line. This feature is not only great for adding some personalization to your charts, but is also very useful if you have any color-blind users. By adding markers to your lines, you are no longer relying solely on color to differentiate the lines from each other.
  • This month is the first of several updates with accessibility in mind. This first release allows users to consume Power BI reports using keyboard navigation and screen readers. We are also giving report authors new formatting options that make reports more accessible to everyone.
  • https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-june-feature-summary/
• You can now invite guest users to your Azure Analysis Services by using B2B
  • We are excited to announce extended support for Azure Active Directory to include Azure AD Business to Business (B2B) collaboration.
  • Using B2B, you can invite users from outside your organization to be guest users in your tenant. After a guest user had been added to your tenant directory, you can add them to security groups or as members to a server or model database role.
  • https://azure.microsoft.com/en-us/blog/invite-guest-users-to-your-azure-analysis-services-by-using-b2b/
• ArcGIS Maps for Power BI is now Generally Available on PowerBI.com
  • What is GIS? It’s the combination of authoritative data layers on a map with spatial analysis
  • The ArcGIS Maps for Power BI visual is provided by Esri, so before you get started, you’ll need to consent to Esri’s terms of service
  • https://powerbi.microsoft.com/en-us/blog/arcgis-maps-for-powerbi-generally-available-in-powerbi-service/
• Introducing the new Data Science Virtual Machine on Windows Server 2016
  • We are excited to offer a Windows Server 2016 version of our very popular Microsoft Azure Data Science Virtual Machine (DSVM).
  • With this release, we are pre-installing the GPU Nvidia drivers, CUDA toolkit 8.0, and cuDNN library in the image. Along with it, we have also installed the latest GPU of the following popular deep learning frameworks: Microsoft Cognitive toolkit (CNTK), TensorFlow, Mxnet.
  • DSVM allows users to get a data science desktop in the cloud without worrying about installation, configuration and maintenance. With the DSVM, all the users of an organization get the exact same standardized setup.
  • http://blog.revolutionanalytics.com/2017/06/dsvm-update.html
  • https://blogs.technet.microsoft.com/machinelearning/2017/06/06/introducing-the-new-data-science-virtual-machine-on-windows-server-2016/
• Announcing Microsoft Machine Learning Library for Apache Spark
  • We’re excited to announce the Microsoft Machine Learning library for Apache Spark.
  • Deep Neural Networks (DNNs) are a powerful technique and can yield near-human accuracy for tasks such as image classification, speech recognition and more. But building and training DNN models from scratch often requires special expertise, expensive compute resources and access to very large datasets.
  • With MMLSpark, we provide easy-to-use Python APIs …By using these APIs, you can rapidly build image analysis and computer vision pipelines that use the cutting-edge DNN algorithms.
  • https://blogs.technet.microsoft.com/machinelearning/2017/06/07/announcing-microsoft-machine-learning-library-for-apache-spark/
• Event Hubs Capture (formally Archive) is now Generally Available
  • Event Hubs Capture makes it easy to send data streams to persistent storage without using code or configuring other compute services.
  • You can currently use it to push data directly from Event Hubs to Azure Storage as blobs. Soon, we will also support Azure Data Lake Store.
  • https://azure.microsoft.com/blog/event-hubs-capture-formally-archive-is-now-generally-available/
• Announcing Data Refresh APIs in the Power BI Service
  • These new APIs will allow you to programmatically trigger data refreshes and retrieve refresh history for any dataset that you own.
  • These APIs open the door to integrating data refresh into your existing ETL or deployment processes. You could, for example, trigger Power BI data refresh as the last step in your Azure Data Factory ETL pipeline.
  • https://powerbi.microsoft.com/en-us/blog/announcing-data-refresh-apis-in-the-power-bi-service/
• Azure Management Libraries for .NET and Java 1.1
  • We released v1.1 of the Azure Management Libraries. This release adds support for Cosmos DB
  • https://azure.microsoft.com/en-us/blog/java-manage-azure-container-service-cosmos-db-active-directory-graph-and-more/
  • https://azure.microsoft.com/blog/net-manage-azure-container-service-cosmos-db-active-directory-graph-and-more/
• Announcing the Campaign & Brand Management for Facebook Pages
  • We are excited to announce a new Power BI solution template – the Campaign/Brand Management template for Facebook
  • You can quickly navigate posts and comments, identify key trends, understand user sentiment, and uncover how well users are engaged.
  • https://powerbi.microsoft.com/en-us/blog/announcing-the-campaign-brand-management-for-facebook-pages/

Issue Alert

• Critical: Do NOT delete files from the Windows Installer folder. C:windowsInstaller 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.
  • http://blogs.msdn.com/b/sqlserverfaq/archive/2013/04/30/do-not-delete-files-from-the-windows-installer-folder.aspx
• 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%system32msvcr120.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%system32msvcr120.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
  • http://support.microsoft.com/kb/239530
• 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)
• Performance impact of memory grants on data loads into Columnstore tables
  • Problem: We found that only at the beginning of the run, there was contention on memory grants (RESOURCE_SEMAPHORE waits), for a short period of time. After that and later into the process, we could see some latch contention on regular data pages, which we didn’t expect as each thread was supposed to insert into its own row group.
  • Cause: For every bulk insert we first determine whether it can go into a compressed row group directly based on batch size. If it can, we request a memory grant with a timeout of 25 seconds. If we cannot acquire the memory grant in 25 seconds, that bulk insert reverts to the delta store instead of compressed row group.
  • Solution: We created and used a resource governor workload group that reduced the grant percent parameter to allow greater concurrency during data load
  • https://blogs.msdn.microsoft.com/sqlcat/2017/06/02/performance-impact-of-memory-grants-on-data-loads-into-columnstore-tables/
• You may see “out of user memory quota” message in errorlog when you use In-Memory OLTP feature
  • SQL Server 2016 SP1 allows In-Memory OLTP to be used in all editions but enforces memory quotas for editions other than Enterprise edition.
  • If you are using memory optimized tables in the context of table valued parameters repeatedly, you may encounter the “out of user memory quota” error even if you use the variables in a separate batch (and memory gets released). We are looking to address this issue in future cumulative updates.
  • For now, you either need to use enterprise edition or periodically restart SQL or offline/online the database.
  • https://blogs.msdn.microsoft.com/psssql/2017/06/07/you-may-see-out-of-user-memory-quota-message-in-errorlog-when-you-use-in-memory-oltp-feature/

Recent Blog Posts and Articles

• Azure Marketplace Test Drive
  • One feature in Azure Marketplace that is especially useful for learning about products is “Test Drive.”
  • Test Drives are ready to go environments that allow you to experience a product for free without needing an Azure subscription. An additional benefit with a Test Drive is that it is pre-provisioned – you don’t have to download, set up or configure the product and can instead spend your time on evaluating the user experience, key features, and benefits of the product.
  • https://azure.microsoft.com/en-us/blog/azure-marketplace-test-drive/
•  Building an Azure Analysis Services Model on Top of Azure Blob Storage—Part 2 + 3
  • https://blogs.msdn.microsoft.com/analysisservices/2017/05/30/building-an-azure-analysis-services-model-on-top-of-azure-blob-storage-part-2/
  • https://blogs.msdn.microsoft.com/analysisservices/2017/06/22/building-an-azure-analysis-services-model-on-top-of-azure-blob-storage-part-3/
•  Use WITH clause in OPENJSON to improve parsing performance
  • OPENJSON function has a WITH clause where you can specify what fields should be extracted from input JSON. This may be faster than using OPENJSON without schema and later extracting information from the parsed JSON using JSON_VALUE function.
  • Example: SELECT count(*) FROM OPENJSON(@json) WITH(model nvarchar(20) ) WHERE model = ‘Golf’
  • https://blogs.msdn.microsoft.com/sqlserverstorageengine/2017/05/29/use-with-clause-in-openjson-to-improve-parsing-performance/
• Smart Transaction log backup, monitoring and diagnostics with SQL Server 2017
  • New DMF sys.dm_db_log_stats which we released in SQL Server 2017 CTP 2.1 will enable DBAs and SQL Server community to build scripts and solutions that performs smart backups, monitoring and diagnostics of transaction log.
  • Column log_since_last_log_backup_mb can be used in your backup script to trigger a transaction log backup when log generated since last backup exceeds [an activity] threshold value.
  • We have exposed columns for VLF monitoring total_vlf_count and active_vlfs allowing you to monitor and alert if the total number of VLFs of the transaction log file exceeds a threshold value.
  • New log_truncation_holdup_reason column to understand the cause of the log truncation holdup
  • If a log truncation doesn’t happen and active_vlfs approaches total_vlfs, it would lead to autogrow causing total_vlfs to increase.
  • The log_backup_time column in sys.dm_db_log_stats can be used to determine the last transaction log backup and can be used to alert a DBA and trigger a backup in response to the alert. The last log backup time can also be derived from msdb database but one of the advantage of using log_backup_time column in sys.dm_db_log_stats is it also accounts for the transaction log backup completed on secondary replica if the database is configured in Availability groups.
  • For long running transaction in killedrollback state scenario, a DBA can look at recovery_vlf_count and log_recovery_size_mb to understand the number of vlfs to recover and log size to recover if the database is restarted.
  • https://blogs.msdn.microsoft.com/sql_server_team/smart-transaction-log-backup-monitoring-and-diagnostics-with-sql-server-2017/
• What is plan regression in SQL Server?
  • Plan regression happens when SQL Server starts using sub-optimal plan, which increases CPU time and duration.
  • One way to mitigate this is to recompile query with OPTION(RECOMPILE) if you find this problem. Do not clear procedure cache on production system because it will affect all queries!
  • Another option would be to use automatic plan choice correction in SQL Server 2017 that will look at the history of plans and force SQL Server to use last known good plan if plan regression is detected.
  • https://blogs.msdn.microsoft.com/sqlserverstorageengine/2017/06/09/what-is-plan-regression-in-sql-server/
• Columnstore Index: How do I find tables that can benefit from Clustered Columnstore Index
  • Use DMV sys.dm_db_index_operational_stats to identify the access pattern on the base rowstore table to identify tables that meet the criteria: (1) At least one partition has > 1 million rows and does not have unsupported types for CCI; (2) Range queries account for > 50% of all operations; (3) DML Update/Delete operations
  • https://blogs.msdn.microsoft.com/sql_server_team/columnstore-index-how-do-i-find-tables-that-can-benefit-from-clustered-columnstore-index/
• Azure SQL databases in logical servers, elastic pools, and managed instances
  • Logical servers enable you to perform administrative tasks across multiple databases – including specifying regions, login information, firewall rules, auditing, threat detection, and failover groups. Databases cannot share resources, and each database has guaranteed, and predicable performance defined by its own service tier. Some server-level specific features such as cross-database querying, linked servers, SQL Agent, Service Broker, or CLR are not supported in Azure SQL databases placed in logical server.
  • Elastic Pools: Databases that need to share resources (CPU, IO, memory) can be stored in elastic pools instead of a logical server. Databases within the elastic pools cannot have different service tiers because they share resources that are assigned to entire pool.
  • Managed instances (In private preview): In May 2017, the concept of a managed instance was announced. With a managed instance, features like SQL CLR, SQL Server Agent, and cross-database querying will be fully supported. Furthermore, a managed instance will have the current capabilities of managed databases, including automatic backups, built-in high-availability, and continuous improvement and release of features in the Microsoft cloud-first development model.
  • You may sign-up for the limited preview here: https://sqldatabase-migrationpreview.azurewebsites.net/
  • https://azure.microsoft.com/en-us/blog/new-options-to-modernize-your-application-with-azure-sql-database/
  • https://blogs.msdn.microsoft.com/sqlserverstorageengine/2017/06/13/azure-sql-databases-in-logical-servers-elastic-pools-and-managed-instances/
• Indirect Checkpoint and tempdb – the good, the bad and the non-yielding scheduler
  • In SQL Server 2016, indirect checkpoint is ON by default with target_recovery_time set to 60 seconds for model database.
  • With indirect checkpoint, the engine maintains partitioned dirty page lists per database to track the number of dirty pages in the buffer pool for that database from each transaction.
  • DIRTY_PAGE_POLL is a new system waittype introduced to support timed wait of recovery writer thread. If you see it as one of the high wait events in sys.dm_os_wait_stats, you can safely ignore it.
  • One of the scenarios where skewed distribution of dirty pages in the DPList is common is tempdb.
  • If recovery writer starts falling behind resulting into long DPLists, the individual worker threads running on the scheduler starts playing the role of recovery writer.
  • In scenarios when the DPList has grown very long, the recovery writer may produce a non-yielding scheduler dump…If you are running SQL Server 2016, we recommend monitoring the output of sys.dm_os_spinlock stats DMV for DP_LIST spinlock to establish a baseline and detect spinlock contention on DP_List.
  • For tempdb, …. indirect checkpoint is still important to smoothen the IO burst activity from automatic checkpoint and to ensure the dirty pages in tempdb do not continue to take away buffer pool pages from user database workload. In this scenario, we recommend tuning the target_recovery_interval to higher value (2-5 mins) for recovery writer to be less aggressive and strike a balance between large IO burst and DPList spinlock contention.
  • https://blogs.msdn.microsoft.com/sql_server_team/indirect-checkpoint-and-tempdb-the-good-the-bad-and-the-non-yielding-scheduler/
• SQL Server: Large RAM and DB Checkpointing
  • The legacy implementation of database checkpointing, which we’ll call FlushCache, needs to scan the entirety of SQL Server’s Buffer Pool for the checkpoint of any given Database.
  • The new Indirect Checkpoint option relies […] on Dirty Page Managers (DPM), and […] doesn’t rely on the Buffer Pool scan anymore.
  • SQL Server 2016 will by default create new databases with Indirect Checkpoint enabled, and therefore uses the DPM approach [for checkpoints]. [However] there are other places in SQL Server 2016, such as Restore Log which still rely on the FlushCache implementation and will still hit the scan delay on large RAM servers.
  • To activate DPM logic in SQL 2014:  Ensure SQL is at the required SQL build level, activate Indirect Checkpoint for the relevant databases, and enable trace flag 3449.
  • https://blogs.msdn.microsoft.com/psssql/2017/06/29/sql-server-large-ram-and-db-checkpointing/

Recent Training and Technical Guides

• Predicting Hospital Length of Stay using SQL Server with R Services
  • https://channel9.msdn.com/blogs/Cloud-and-Enterprise-Premium/Predicting-Hospital-Length-of-Stay-using-SQL-Server-with-R-Services
• Data Visualization Design Discussion Lab
  • https://powerbi.microsoft.com/en-us/blog/webinar-wednesday-data-visualization-design-discussion-lab/
• SQL Server Database Unit Testing in your DevOps pipeline
  • https://channel9.msdn.com/Shows/Visual-Studio-Toolbox/SQL-Server-Database-Unit-Testing-in-your-DevOps-pipeline
• Using a Recommendation System in an Application
  • https://blogs.technet.microsoft.com/machinelearning/2017/06/29/using-a-recommendation-system-in-an-application/
• Machine Learning for Developers – How to Build Intelligent Apps & Services
  • https://blogs.technet.microsoft.com/machinelearning/2017/06/22/machine-learning-for-developers/
• Predictive Maintenance Using PySpark
  • Detailed step-by-step tutorial of using Spark Python API PySpark to demonstrate how to approach predictive maintenance for big data scenarios. The tutorial covers typical data science steps such as data ingestion, cleansing, feature engineering and model development.
  • We formatted this tutorial as Jupyter notebooks because it is easy to show the step-by-step process this way. You can also easily compile the executable PySpark script(s) using your favorite IDE.
  • https://azure.microsoft.com/en-us/blog/predictive-maintenance-using-pyspark/
• Deep learning for predictive maintenance with Long Short-Term Memory Networks
  • In the notebook Deep Learning Basics for Predictive Maintenance, we build an LSTM network for the data set and scenario described at Predictive Maintenance Template to predict remaining useful life of aircraft engines using the Turbofan Engine Degradation Simulation Data Set.
  • This notebook serves as a tutorial for beginners looking to apply deep learning in predictive maintenance domain and uses simulated aircraft sensor values to predict when an aircraft engine will fail in the future so that maintenance can be planned in advance.
  • https://azure.microsoft.com/en-us/blog/deep-learning-for-predictive-maintenance/
• [Whitepaper] Update to Auto-Partitioning Code Sample & Whitepaper
  • Enhancements include support for 1400 models with M partitions and M expressions, Auto retry n times on error, Integrated authentication for Azure AS and more.
  • https://blogs.msdn.microsoft.com/analysisservices/2017/05/30/update-to-auto-partitioning-code-sample-whitepaper/
  • https://github.com/Microsoft/Analysis-Services/blob/master/AsPartitionProcessing/Automated%20Partition%20Management%20for%20Analysis%20Services%20Tabular%20Models.pdf
• [Quick Walkthrough] First look at Power BI Report Server
  • https://community.powerbi.com/t5/Community-Blog/First-look-at-Power-BI-Report-Server/ba-p/189150
• SQL SERVER Transactional and Merge Replication internal and work flow explained
  • https://channel9.msdn.com/Blogs/Developer-Support-Series-PGI-Sessions/Developer-Support-Series-SQL-Server-Replication
• Watch presentations from R/Finance 2017
  • http://blog.revolutionanalytics.com/2017/05/watch-rfinance-2017.html
• Reflections on ROpenSci Unconference 2017
  • http://blog.revolutionanalytics.com/2017/05/runconf17.html
• Webinar Wednesday: Data Storytelling
  • Come hear how data visualization and industries expert Dallas Salazar, collaborating with Power BI MVP Greg Deckler, recently used data and Power BI to draw readers into a complicated story.
  • Mr. Salazar developed a fascinating thesis and supporting model around enterprise risk for Oil & Gas exploration and production corporations.
  • Commodity Correlation Double Helix visual: https://community.powerbi.com/t5/Data-Stories-Gallery/Commodity-Correlation-quot-Double-Helix-quot/m-p/135256
  • https://powerbi.microsoft.com/en-us/blog/webinar-wednesday-data-storytelling/
• Teach kids about R with Minecraft
  • R package, miner, is now available to install from Github.  It is the result of a project to interface the R language with Minecraft
  • http://blog.revolutionanalytics.com/2017/06/teach-kids-about-r-with-minecraft.html
• Data Insights Summit 2017 On-Demand Sessions
  • https://community.powerbi.com/t5/Data-Insights-Summit-2017-On/bd-p/DataInsightsSummit2017OnDemand
• Powe[R] BI: Free e-book on using R with Power BI
  • http://blog.revolutionanalytics.com/2017/06/power-bi-free-e-book.html
• [Video] Demo: Real-Time Predictions with Microsoft R Server
  • With Microsoft R Server 9.1, there are now two ways to operationalize models as a Web service or as a SQL Server stored procedure.
  • http://blog.revolutionanalytics.com/2017/06/real-time-predictions.html
• Data Science Walkthrough with SQL Server 2017 and Microsoft Machine Learning Services
  • https://blogs.technet.microsoft.com/machinelearning/2017/06/15/data-science-walkthrough-with-sql-server-2017-and-microsoft-machine-learning-services/
• Creating enterprise grade BI models with Azure Analysis Services
  • https://azure.microsoft.com/en-us/blog/creating-enterprise-grade-bi-models-with-azure-analysis-services/
• Creating Slopegraphs in Power BI
  • https://community.powerbi.com/t5/Community-Blog/Creating-Slopegraphs-in-Power-BI/ba-p/180034
• Machine Learning for Developers – How to Build Intelligent Apps & Services
  • https://blogs.technet.microsoft.com/machinelearning/2017/06/22/machine-learning-for-developers/
• Power BI – Overview Infographic
  • Infographics with a nice one page overview of the entire Power BI Service and features
  • https://community.powerbi.com/t5/Community-Blog/Power-BI-Overview-Infographic/ba-p/201669
• Using Azure Data Lake and R for Fraud Detection
  • https://blogs.technet.microsoft.com/machinelearning/2017/06/28/using-azure-data-lake-and-r-for-fraud-detection/

Monthly Script and Tool Tips

• AzFileDiagnostics automates detection of many symptoms and helps set up your environment for optimal performance.
  • https://azure.microsoft.com/en-us/blog/new-troubleshooting-diagnostics-for-azure-files-mounting-errors-on-windows/
• [Script Of May. 31] How to list all databases in Azure Cosmos DB by PowerShell
  • https://blogs.technet.microsoft.com/onescript/2017/05/31/script-of-may-31-how-to-list-all-databases-in-azure-cosmos-db-by-powershell/
• [Script Of Jun. 6] How to list all collections of database in Azure Cosmos DB by PowerShell
  • https://blogs.technet.microsoft.com/onescript/2017/06/06/script-of-jun-6-how-to-list-all-collections-of-database-in-azure-cosmos-db-by-powershell/
• [Script Of Jun. 7] How to list all documents in a collection in Azure Cosmos DB by PowerShell
  • https://blogs.technet.microsoft.com/onescript/2017/06/07/script-of-jun-7-how-to-list-all-documents-in-a-collection-in-azure-cosmos-db-by-powershell/
• [DAX Script for Power BI] Power BI Desktop/DAX – Using Unicode (Arrows) as Measures in a Table
  • The user had sales figures and based on the Previous month, they wanted to have an arrow showing if it was up or down. As well as if there was no data to say that there was no data.
  • Solution uses Unicode Characters for arrows in a measure, DAX Switch statement, and DAX UNICHAR function
  • http://community.powerbi.com/t5/Community-Blog/Power-BI-Desktop-DAX-Using-Unicode-Arrows-as-Measures-in-a-Table/ba-p/190725
• Getting Started with PowerShell Core on Windows, Mac, and Linux
  • PowerShell Core designed to run cross-platform on Windows, Mac, and Linux.
  • It runs on top of .NET Core, a cross-platform, open-source version of the code base powering most of the Windows world.
  • This version of PowerShell will be different than the Windows PowerShell edition you see built into Windows today.
  • The beta release of PowerShell Core 6 supports a significant list of Linux distributions
  • $PSVersionTable.PSVersion = Core
  • We now have built-in variables indicating the operating system version. These variables enable you to check the current OS and perform different behaviors from within a script. IsLinux, IsWindows, IsOSX, IsCoreCLR
  • With the release of the PowerShell v1.x extension for Visual Studio Code it is now possible to have a cross-platform PowerShell editing experience very similar to the ISE on Windows boxes
  • https://blogs.msdn.microsoft.com/powershell/2017/06/09/getting-started-with-powershell-core-on-windows-mac-and-linux/
• Run massive parallel R Jobs in Azure, now at a fraction of the price
  • doAzureParallel built on top of Azure Batch allows you to easily use Azure’s flexible compute resource right from your R session.
  • doAzureParallel is extremely easy to use. With just a few lines of code, you can register Azure as your parallel backend which can be used by foreach, caret, plyr and many other popular open source packages.
  • https://azure.microsoft.com/en-us/blog/run-massive-r-jobs-in-azure-directly-from-r-studio-at-a-fraction-of-the-price/
  • http://blog.revolutionanalytics.com/2017/06/doazureparallel-updated.html
• Syberia: A development framework for R code in production
  • An open-source framework used to build a production data modeling system. It encourages you to break up your process into a series of distinct steps, each of which can be run (and tested) independently.
  • http://blog.revolutionanalytics.com/2017/06/syberia.html

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