SQL Server 2017 – Windows, Linux, and Docker

SQL Server 2017 - @SeniorDBA

SQL Server 2017 is available as a Release Candidate (RC2, August 2017) and Community Technical Preview (CTP) , which represents a step towards making SQL Server a platform that gives you more choices when it comes to development languages, data types, and operating systems by making it available the to Linux users, as a Linux-based Docker containers, and traditional Windows users.

New features as described by Microsoft:

SQL Server 2017 Database Engine

SQL Server 2017 includes many new Database Engine features, enhancements, and performance improvements.

    • CLR assemblies can now be added to a whitelist, as a workaround for the clr strict security feature described in CTP 2.0. sp_add_trusted_assembly, sp_drop_trusted_assembly, and sys.trusted_asssemblies are added to support the white list of trusted assemblies (RC1).
    • Resumable online index rebuild resumes an online index rebuild operation from where it stopped after a failure (such as a failover to a replica or insufficient disk space), or pauses and later resumes an online index rebuild operation. See ALTER INDEX and Guidelines for online index operations. (CTP 2.0)
    • The IDENTITY_CACHE option for ALTER DATABASE SCOPED CONFIGURATION allows you to avoid gaps in the values of identity columns if a server restarts unexpectedly or fails over to a secondary server. See ALTER DATABASE SCOPED CONFIGURATION. (CTP 2.0)
    • Automatic database tuning provides insight into potential query performance problems, recommends solutions, and can automatically fix identified problems. See Automatic tuning. (CTP 2.0)
    • New graph database capabilities for modeling many-to-many relationships include new CREATE TABLE syntax for creating node and edge tables, and the keyword MATCH for queries. See Graph Processing with SQL Server 2017. (CTP 2.0)
    • An sp_configure option called clr strict security is enabled by default to enhance the security of CLR assemblies. See CLR strict security. (CTP 2.0)
    • Setup now allows specifying initial tempdb file size up to 256 GB (262,144 MB) per file, with a warning if the file size is set greater than 1GB with IFI not enabled. (CTP 2.0)
    • The modified_extent_page_count column in sys.dm_db_file_space_usage tracks differential changes in each database file, enabling smart backup solutions that perform differential backup or full backup based on percentage of changed pages in the database. (CTP 2.0)
    • SELECT INTO T-SQL syntax now supports loading a table into a FileGroup other than the user’s default by using the ON keyword. (CTP 2.0)
    • Cross database transactions are now supported among all databases that are part of an Always On Availability Group, including databases that are part of same instance. See Transactions – Always On Availability Groups and Database Mirroring (CTP 2.0)
    • New Availability Groups functionality includes clusterless support, Minimum Replica Commit Availability Groups setting, and Windows-Linux cross-OS migrations and testing. (CTP 1.3)
    • New dynamic management views:
      • sys.dm_db_log_stats exposes summary level attributes and information on transaction log files, helpful for monitoring transaction log health. (CTP 2.1)
      • sys.dm_tran_version_store_space_usage tracks version store usage per database, useful for proactively planning tempdb sizing based on the version store usage per database. (CTP 2.0)
      • sys.dm_db_log_info exposes VLF information to monitor, alert, and avert potential transaction log issues. (CTP 2.0)
      • sys.dm_db_stats_histogram is a new dynamic management view for examining statistics. (CTP 1.3)
      • sys.dm_os_host_info provides operating system information for both Windows and Linux. (CTP 1.0)
    • The Database Tuning Advisor (DTA) has additional options and improved performance. (CTP 1.2)
    • In-memory enhancements include support for computed columns in memory-optimized tables, full support for JSON functions in natively compiled modules, and the CROSS APPLY operator in natively compiled modules. (CTP 1.1)
    • New string functions are CONCAT_WS, TRANSLATE, and TRIM, and WITHIN GROUP is now supported for the STRING_AGG function. (CTP 1.1)
    • There are new bulk access options (BULK INSERT and OPENROWSET(BULK…) ) for CSV and Azure Blob files. (CTP 1.1)
    • Memory-optimized object enhancements include sp_spaceused and elimination of the 8 index limitation for memory-optimized tables, sp_rename for memory-optimized tables and natively compiled T-SQL modules, and CASE and TOP (N) WITH TIES for natively compiled T-SQL modules. Memory-optimized filegroup files can now be stored, backed up and restored on Azure Storage. (CTP 1.0)
    • DATABASE SCOPED CREDENTIAL is a new class of securable, supporting CONTROL, ALTER, REFERENCES, TAKE OWNERSHIP, and VIEW DEFINITION permissions. ADMINISTER DATABASE BULK OPERATIONS is now visible in sys.fn_builtin_permissions. (CTP 1.0)
    • Database COMPATIBILITY_LEVEL 140 is added. (CTP 1.0).

SQL Server 2017 Integration Services (SSIS)

    • The new Scale Out feature in SSIS has the following new and changed features.
      • Scale Out Master now supports high availability.
      • The failover handling of the execution logs from Scale Out Workers is improved.
      • The parameter runincluster of the stored procedure [catalog].[create_execution] is renamed to runinscaleout for consistency and readability.
      • The SSIS Catalog has a new global property to specify the default mode for executing SSIS packages.
    • In the new Scale Out for SSIS feature, you can now use the Use32BitRuntime parameter when you trigger execution. (CTP 2.1)
    • SQL Server 2017 Integration Services (SSIS) now supports SQL Server on Linux, and a new package lets you run SSIS packages on Linux from the command line. For more information, see the blog post announcing SSIS support for Linux. (CTP 2.1)
    • The new Scale Out for SSIS feature makes it much easier to run SSIS on multiple machines. See Integration Services Scale Out. (CTP 1.0)
    • OData Source and OData Connection Manager now support connecting to the OData feeds of Microsoft Dynamics AX Online and Microsoft Dynamics CRM Online. (CTP 1.0)

SQL Server 2017 Master Data Services (MDS)

    • Experience and performance are improved when upgrading from SQL Server 2012, SQL Server 2014, and SQL Server 2016 to SQL Server 2017 Master Data Services.
    • You can now view the sorted lists of entities, collections and hierarchies in the Explorer page of the Web application.
    • Performance is improved for staging millions of records using the staging stored procedure.
    • Performance is improved when expanding the Entities folder on the Manage Groups page to assign model permissions. The Manage Groups page is located in the Security section of the Web application.

SQL Server 2017 Analysis Services (SSAS)

SQL Server Analysis Services 2017 introduces many enhancements for tabular models. These include:+

    • Tabular mode as the default installation option for Analysis Services. (CTP 2.0)
    • Object-level security to secure the metadata of tabular models. (CTP 2.0)
    • Date relationships to easily create relationships based on date fields. (CTP 2.0)
    • New Get Data (Power Query) data sources, and existing DirectQuery data sources support for M queries. (CTP 2.0)
    • DAX Editor for SSDT. (CTP 2.0)
    • Encoding hints, an advanced feature for optimizing data refresh of large in-memory tabular models. (CTP 1.3)
    • Support for the 1400 Compatibility level for tabular models. To create new or upgrade existing tabular model projects to the 1400 compatibility level, download and install SQL Server Data Tools (SSDT) 17.0 RC2. (CTP 1.1)
    • A modern Get Data experience for tabular models at the 1400 compatibility level. See the Analysis Services Team Blog. (CTP 1.1)
    • Hide Members property to hide blank members in ragged hierarchies. (CTP 1.1)
    • New Detail Rows end-user action to Show Details for aggregated information. SELECTCOLUMNS and DETAILROWS functions for creating Detail Rows expressions. (CTP 1.1)
    • DAX IN operator for specifying multiple values. (CTP 1.1)

SQL Server 2017 Reporting Services (SSRS)

As of CTP 2.1, SSRS is no longer available to install through SQL Server setup.

    • Comments are now available for reports, to add perspective and collaborate with others. You can also include attachments with comments. (CTP 2.1)
    • In the latest releases of Report Builder and SQL Server Data Tools, you can create native DAX queries against supported SQL Server Analysis Services tabular data models by dragging and dropping desired fields in the query designers.

SQL Server 2017 Machine Learning Services

SQL Server R Services has been renamed SQL Server Machine Learning Services, to reflect support for Python in addition to the R language. You can use Machine Learning Services (In-Database) to run R or Python scripts in SQL Server, or install Microsoft Machine Learning Server (Standalone) to deploy and consume R and Python models that don’t require SQL Server.

  • revoscalepy – This Pythonic version of RevoScaleR includes parallel algorithms for linear and logistic regressions, decision tree, boosted trees and random forests, as well as a rich set of APIs for data transformation and data movement, remote compute contexts, and data sources.
  • microsoftml – This state-of-the-art package of machine learning algorithms and transforms with Python bindings includes deep neural networks, fast decision trees and decision forests, and optimized algorithms for linear and logistic regressions. You also get pre-trained models based on ResNet models that you can use for image extraction or sentiment analysis.
  • Python operationalization with T-SQL – Deploy Python code easily by using the stored procedure sp_execute_external_script. Get great performance by streaming data from SQL to Python processes and using MPI ring parallelization.
  • Python in SQL Server compute contexts – Data scientists and developers can execute Python code remotely from their development environments to explore data and develop models without moving data around.

You can get more information and download the latest “Release Candidate” version here.

Advertisements

11 Hidden Windows 10 Tips and Tweaks

Windows 10 - SeniorDBA

All the features of Windows 10 aren’t as obvious as they should be, and that includes the tips and tweaks that make the powerful operating system easier to use. In this article by Howard Wen, we learn the details of 11 lesser-known ways to make Windows 10 better.

1. Delete your previous Windows version installation

2. Know how to sign out of Windows 10 

3. Pick whatever accent color you want

4. Use the new delay timer in the Snipping Tool 

5. Change Edge’s default search engine from Bing to another one 

6. Delay automatic updates over Wi-Fi 

7. Record video clips using the Xbox app

8. Remove the OneDrive folder from File Explorer 

9. Pin Windows apps to the desktop

10. Access all Windows 10 settings under one user interface

11. Uninstall default Windows apps

 

You can read the entire article to see all the details.

Microsoft researchers on what to expect in 2017 and 2027

Future Technology - SeniorDBA

Predicting the future can be difficult. You can see what is possible today, but something unexpected can happen tomorrow that can quickly change the future. When predicting the future of technology, it is critical to get the opinions of those people already working on future technology, like the folks at Microsoft Research.

This article provides the comments from various members of Microsoft Research.

What will be the key advance in algorithms for machine learning in 2017?
Deep learning is transforming many aspects of our technology, however deep learning algorithms today are still largely heuristic, based on the experience and intuition of leaders in the field. In 2017, we will develop a more principled understanding of deep learning and hence more robust algorithms. The insights here will come from many fields, including the intersection of statistical physics and computer science.

What will be the key advance in virtual reality in 2017?
In 2017 we will see the emergence of virtual reality devices that feature better body tracking. A positive outcome of this will be the ability to experience embodiment of virtual avatars from a first-person perspective.

What will be the key advance in artificial intelligence and machine learning in 2017?
In 2017 computer games will take center stage in the development of AI. Experimentation platforms based on games, such as Project Malmo — which my team and I have developed to enable AI experimentation in Minecraft – will allow for rapid testing of new ideas. I am especially excited about the potential for collaborative AI. We are now at the point where we can start to understand how AI can learn from us and collaborate with us to help us achieve our goals.

What will be the key advance in mathematics and cryptography in 2017?
New mathematical solutions allowing for computation on encrypted data will be deployed to protect the privacy of medical and genomic data for patients and hospitals.  The new homomorphic encryption schemes will secure the data while allowing the cloud to compute on it to make useful risk predictions and provide analysis and alerts. Homomorphic encryption will be deployed soon in the financial sector to protect sensitive banking data.

What will be the key advance or topic of discussion in programming languages and software engineering in 2027?
By 2027, the majority of software engineers will be facile in programming systems that reason about estimates and produce models with statistical methods.  This sea change will deliver applications that seamlessly integrate sensors, machine learning and approximation to interact with human beings in entirely new, meaningful and correct ways.

AzureAD PowerShell V2.0 is now GA

Azure - SeniorDBA

Microsoft has announced that PowerShell Azure AD v2.0 cmdlets are now generally available. They updated the names of all cmdlets to conform with the Azure PowerShell naming conventions. Since they’re publishing a new module for these cmdlets, the name of the module has changed as well: the existing module’s name was “MSOL”, the new module is now called “AzureAD”.

Azure Active Directory V2 PowerShell Cmdlets
  • Add-AzureADAdministrativeUnitMember – Add an administrativeUnit member
  • Add-AzureADApplicationOwner – Add an owner to an application
  • Add-AzureADDeviceRegisteredOwner – Add an owner to a device
  • Add-AzureADDeviceRegisteredUser – Add a user to a device.
  • Add-AzureADDirectoryRoleMember – Add a member to a directory role
  • Add-AzureADGroupMember – Add a member to a group
  • Add-AzureADGroupOwner – Add an owner to a group
  • Add-AzureADScopedRoleMembership – Add a scoped role
  • Add-AzureADServicePrincipalOwner – Add an owner to a service principal
  • Confirm-AzureADDomain – Validate the ownership of the domain.
  • Connect-AzureAD – Connect with an authenticated account to use Azure Active Directory cmdlet requests.
  • Disconnect-AzureAD – Disconnects the current session from an Azure AD tenant
  • Enable-AzureADDirectoryRole – Activates an existing directory role in Azure Active Directory
  • Get-AzureADAdministrativeUnit – Get an Administrative Unit by objectId
  • Get-AzureADAdministrativeUnitMember – Get administrativeUnit members.
  • Get-AzureADApplication – Get an application by objectId
  • Get-AzureADApplicationExtensionProperty – Get group extension properties
  • Get-AzureADApplicationKeyCredential – Get an application’s key credentials
  • Get-AzureADApplicationOwner – Get owners of an application.
  • Get-AzureADApplicationPasswordCredential – Get and application’s password credentials
  • Get-AzureADApplicationPolicy
  • Get-AzureADContact – Retrieves a specific contact from Azure Active Directory
  • Get-AzureADContactDirectReport – Get the contact’s direct reports.
  • Get-AzureADContactManager – Retrieves the manager of a contact from Azure Active Directory
  • Get-AzureADContactMembership – Get contact memberships.
  • Get-AzureADContract – Retrieves a specific contract from Azure Active Directory
  • Get-AzureADDevice – Retrieves a specific device from Azure Active Directory
  • Get-AzureADDeviceRegisteredOwner – Get users that are registered as owner on the device.
  • Get-AzureADDeviceRegisteredUser – Get users that are marked as users on the device.
  • Get-AzureADDirectoryRole – Retrieves a specific directory role from Azure Active Directory
  • Get-AzureADDirectoryRoleMember – Get the members of a directory role.
  • Get-AzureADDirectoryRoleTemplate – Retrieves a list of directory role templates in Azure Active Directory
  • Get-AzureADDirectorySetting – Retrieves a directory setting from Azure Active Directory.
  • Get-AzureADDirectorySettingTemplate – Retrieves directory setting template from Azure Active Directory.
  • Get-AzureADDomain – Get an domain by objectId
  • Get-AzureADExtensionProperty – A collection that contains the extension properties registered with the directory.
  • Get-AzureADGroup – Get a group by objectId
  • Get-AzureADExtensionProperty – Gets extension properties registered with Azure AD.
  • Get-AzureADGroupAppRoleAssignment – Get group application role assignments.
  • Get-AzureADGroupMember – Get members of a group.
  • Get-AzureADGroupOwner – Get owners of a group.
  • Get-AzureADMSGroup – Retrieves a group from the directory
  • Get-AzureADMSGroup – Gets information about groups in Azure AD.
  • Get-AzureADOAuth2PermissionGrant – Get a list of all oAuth2PermissionGrants granted by users within the directory.
  • Get-AzureADObjectSetting – Retrieves a object setting from Azure Active Directory.
  • Get-AzureADPolicy
  • Get-AzureADPolicyAppliedObject
  • Get-AzureADScopedRoleMembership
  • Get-AzureADServiceAppRoleAssignment – Get service principal application role assignments.
  • Get-AzureADServiceConfigurationRecord – Get serviceConfigurationRecords
  • Get-AzureADServicePrincipal – Get a service principal by objectId
  • Get-AzureADServicePrincipalCreatedObject – Get objects created by the service principal.
  • Get-AzureADServicePrincipalKeyCredential – Get a service principal’s key credentials
  • Get-AzureADServicePrincipalMembership – Get service principal memberships.
  • Get-AzureADServicePrincipalOAuth2PermissionGrant – Get the list of the oAuth2PermissionGrants that a user granted this service principal.
  • Get-AzureADServicePrincipalOwnedObject – Get objects owned by the service principal.
  • Get-AzureADServicePrincipalOwner – Get owners of a service principal.
  • Get-AzureADServicePrincipalPasswordCredential – Get a service principal’s password credentials
  • Get-AzureADServicePrincipalPolicy
  • Get-AzureADSubscribedSku – Retrieves a list of subscribed SKUs (subscriptions) to Microsoft services.
  • Get-AzureADTenantDetail – Retrieves the details of a tenant in Azure Active Directory
  • Get-AzureADTrustedCertificateAuthority
  • Get-AzureADUser – Retrieves a specific user from Azure Active Directory
  • Get-AzureADUserAppRoleAssignment – Get user application role assignments.
  • Get-AzureADUserCreatedObject – Get objects created by the user.
  • Get-AzureADUserDirectReport – Get the user’s direct reports.
  • Get-AzureADUserExtension
  • Get-AzureADUserManager – Retrieves the manager of a user from Azure Active Directory
  • Get-AzureADUserMembership – Get user memberships.
  • Get-AzureADUserOAuth2PermissionGrant – Get the list of the oAuth2PermissionGrants that the user granted applications.
  • Get-AzureADUserOwnedDevice – Get registered devices owned by the user.
  • Get-AzureADUserOwnedObject – Get objects owned by the user.
  • Get-AzureADUserRegisteredDevice – Get registered devices registered by the user.
  • Get-AzureADVerificationDnsRecord – Get verificationDnsRecords
  • New-AzureADAdministrativeUnit – Create a new administrativeUnit in Azure Active Directory
  • New-AzureADApplication – Create a new application in Azure Active Directory
  • New-AzureADApplicationExtensionProperty – Create application extension property
  • New-AzureADApplicationKeyCredential – Create a new key credential for an application
  • New-AzureADApplicationPasswordCredential – Create a new password credential for an application
  • New-AzureADDevice – Create a new device in Azure Active Directory
  • New-AzureADDirectorySetting – Creates a directory settings object in Azure Active Directory.
  • New-AzureADDomain – Create a new domain in Azure Active Directory
  • New-AzureADGroup – Create a new group in Azure Active Directory
  • New-AzureADGroupAppRoleAssignment – Assign a group of users to an application role.
  • New-AzureADMSGroup
  • New-AzureADMSInvitation
  • New-AzureADMSGroup – Creates an Azure AD group.
  • New-AzureADObjectSetting – Creates a settings object in Azure Active Directory.
  • New-AzureADPolicy
  • New-AzureADServiceAppRoleAssignment – Assign a service principal to an application role.
  • New-AzureADServicePrincipal – Create a new application in Azure Active Directory
  • New-AzureADServicePrincipalKeyCredential – Create a new key credential for a service principal
  • New-AzureADServicePrincipalPasswordCredential – Create a new password credential for a service principal
  • New-AzureADTrustedCertificateAuthority
  • New-AzureADUser – Create a new user in Azure Active Directory
  • New-AzureADUserAppRoleAssignment – Assign a user to an application role.
  • Remove-AzureADAdministrativeUnit – Delete an administrativeUnit by objectId.
  • Remove-AzureADAdministrativeUnitMember – Removes an administrativeUnit member.
  • Remove-AzureADApplication – Delete an application by objectId.
  • Remove-AzureADApplicationExtensionProperty – Delete an application extension property.
  • Remove-AzureADApplicationKeyCredential – Remove a key credential from an application
  • Remove-AzureADApplicationOwner – Removes an owner from an application.
  • Remove-AzureADApplicationPasswordCredential – Remove a password credential from an application
  • Remove-AzureADContact – Deletes a specific contact in Azure Active Directory
  • Remove-AzureADContactManager – Deletes the contact’s manager in Azure Active Directory
  • Remove-AzureADDevice – Deletes a specific device in Azure Active Directory
  • Remove-AzureADDeviceRegisteredOwner – Removes an owner from a device.
  • Remove-AzureADDeviceRegisteredUser – Removes a user from a device.
  • Remove-AzureADDirectoryRoleMember – Removes a specific member from a directory role.
  • Remove-AzureADDirectorySetting – Deletes a directory setting in Azure Active Directory.
  • Remove-AzureADDomain – Delete an domain by objectId.
  • Remove-AzureADGroup – Delete a group by objectId.
  • Remove-AzureADGroupAppRoleAssignment – Delete a group application role assignment.
  • Remove-AzureADGroupMember – Removes a member from a group.
  • Remove-AzureADGroupOwner – Removes an owner from a group.
  • Remove-AzureADMSGroup – This cmdlet removes a group from the directory
  • Remove-AzureADMSGroup – Removes an Azure AD group.
  • Remove-AzureADOAuth2PermissionGrant – Delete an oAuth2PermissionGrant.
  • Remove-AzureADObjectSetting – Deletes settings in Azure Active Directory.
  • Remove-AzureADPolicy
  • Remove-AzureADScopedRoleMembership
  • Remove-AzureADServiceAppRoleAssignment – Delete a service principal application role assignment.
  • Remove-AzureADServicePrincipal – Delete an application by objectId.
  • Remove-AzureADServicePrincipalKeyCredential – Remove a key credential from a service principal
  • Remove-AzureADServicePrincipalOwner – Removes an owner from a service principal.
  • Remove-AzureADServicePrincipalPasswordCredential – Remove a password from a service principal
  • Remove-AzureADTrustedCertificateAuthority
  • Remove-AzureADUser – Deletes a specific user in Azure Active Directory
  • Remove-AzureADUserAppRoleAssignment – Delete a user application role assignment.
  • Remove-AzureADUserExtension
  • Remove-AzureADUserManager – Deletes the user’s manager in Azure Active Directory
  • Revoke-AzureADSignedInUserAllRefreshToken – Invalidates all of the currently signed in user’s refresh tokens issued to applications (as well as session cookies in a user’s browser), by resetting the refreshTokensValidFromDateTime user property to the current date-time.
  • Revoke-AzureADUserAllRefreshToken – Invalidates all of the user’s refresh tokens issued to applications (as well as session cookies in a user’s browser), by resetting the refreshTokensValidFromDateTime user property to the current date-time.
  • Revoke-AzureADSignedInUserAllRefreshToken – Invalidates the refresh tokens issued to applications for the current user.
  • Revoke-AzureADUserAllRefreshToken – Invalidates the refresh tokens issued to applications for a user.
  • Select-AzureADGroupIdsContactIsMemberOf – From a list of groups Ids select those that the contact is a member of.
  • Select-AzureADGroupIdsGroupIsMemberOf – From a list of groups Ids select those that the group is a member of.
  • Select-AzureADGroupIdsServicePrincipalIsMemberOf – From a list of groups Ids select those that the service principal is a member of.
  • Select-AzureADGroupIdsUserIsMemberOf – From a list of groups Ids select those that the user is a member of.
  • Set-AzureADAdministrativeUnit – Updates a specific administrativeUnit in Azure Active Directory
  • Set-AzureADApplication – Updates a specific application in Azure Active Directory
  • Set-AzureADContact – Updates a specific contact in Azure Active Directory
  • Set-AzureADContactManager – Updates the contact’s manager in Azure Active Directory
  • Set-AzureADDevice – Updates a specific device in Azure Active Directory
  • Set-AzureADDirectorySetting – Updates a directory setting in Azure Active Directory.
  • Set-AzureADDomain – Updates a specific domain in Azure Active Directory
  • Set-AzureADGroup – Updates a specific group in Azure Active Directory
  • Set-AzureADMSGroup – Set a group’s attributes
  • Set-AzureADMSGroup – Changes attribute values on an Azure AD group.
  • Set-AzureADObjectSetting – Updates settings in Azure Active Directory.
  • Set-AzureADPolicy
  • Set-AzureADServicePrincipal – Updates a service principal in Azure Active Directory
  • Set-AzureADTrustedCertificateAuthority
  • Set-AzureADUser – Updates a specific user in Azure Active Directory
  • Set-AzureADUserExtension
  • Set-AzureADUserLicense – Add and remove one or more licenses for a Microsoft online service to the list of assigned licenses for the user.
  • Set-AzureADUserManager – Updates the user’s manager in Azure Active Directory
  • Set-AzureADUserPassword – Sets the password of a user in Azure AD
  • Update-AzureADSignedInUserPassword – Updates the password for the signed in user in Azure AD
Example

Update-AzureADSignedInUserPassword – Update a password

PS C:\>Update-AzureADSignedInUserPassword -CurrentPassword $CurrentPassword -NewPassword $NewPassword

This command updates the password for the signed-in user.

Energy Might Be Free Soon

Cheap Energy - SeniorDBA

Researchers at the Citi have calculated that cheaper storage and smart data analytics may soon make solar and wind energy available to consumers in some parts of the world – completely free.

As evidence of this prospect, analysts point to Microsoft, which has developed its own software that collects 500 million data transactions daily from sensors and other energy devices to understand its own energy demand level and patterns. You also need to understand that both Google and Apple have a license to trade energy.

“As new builds in the power sector involve more near zero-variable cost sources, such as wind and solar, along with greater demand and storage optimization, the goal of dramatically lowering energy costs for all, with the possibility of free energy in some corners, may finally come to fruition,” Citi analysts said.

Setting CE TraceFlags on a query-by-query (or session) basis

Cardinality - SeniorDBA

Using TraceFlags  on a query-by-query basis as described by Kimberly Tripp. This article does a great job of explaining this technique.

While the new cardinality estimator can offer benefits for some queries, it might not be perfect for ALL queries. Having said that, there is an OPTION clause that allows you to set the CE for that query. The setting to use depends on the CE that you’re running under currently. And, as of SQL Server 2016, even determining this can be difficult. At any given time, there are multiple settings that might affect your CE.

In SQL Server 2014, your CE was set by the database compatibility model. If you’re running with compatibility mode 120 or higher, then you’re using the new CE. If you’re running with compatibility mode 110 or lower, then you’re using the Legacy CE. In SQL Server 2016, the database compatibility mode is not the only setting that can affect the CE that you’re using. In SQL Server 2016, they added ‘database scoped configuations’ and introduced:

ALTER DATABASE SCOPED CONFIGURATION LEGACY_CARDINALITY_ESTIMATION = { ON | OFF | PRIMARY }

Having said that, an administrator can always override this setting by setting one of the CE trace flags globally [using DBCC TRACEON (TF, -1) – but I don’t recommend this!].

To use the LegacyCE when the database is set to the new CE, use Trace Flag 9481.
To use the New CE when the database is set to the LegacyCE, use Trace Flag 2312.

Generally, I recommend that most people STAY with the LegacyCE until they’ve thoroughly tested the new CE. Then, and only then, change the compatibility mode. But, even with extensive testing, you might still want some queries to run with the LegacyCE while most run with the new CE (or, potentially the opposite). What I like most about the addition of the new CE is that we have the ability to set EITHER!

Microsoft Replaces Command Prompt with PowerShell in Windows 10

The Command Prompt has been part of Windows for a very long time, but it is being replaced starting with Windows 10 build 14971. It looks like Microsoft is trying to make PowerShell the main command shell in their latest update to their premier operating system.

In this build, PowerShell will officially replace the traditional Command Prompt in most any way you used to run the utility. Even typing cmd in the run dialog will launch PowerShell.