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

Transaction Log File Size and VLF in SQL Server

This article by Kimberly Tripp is very interesting. Simply put, she says you want the initial size of your transaction logs set to 8 GB, with auto growth set to 8 GB. This should help keep your Virtual Lof File (VLF) sizes below 512 MB, improve performance, and make maintenance during backups much faster.

VLF Image

The article, in part, reads:

First, here’s how the log is divided into VLFs. Each “chunk” that is added, is divided into VLFs at the time the log growth (regardless of whether this is a manual or auto-grow addition) and it’s all dependant on the size that is ADDED not the size of the log itself. So, take a 10MB log that is extended to 50MB, here a 40MB chunk is being added. This 40MB chunk will be divided into 4 VLFs. Here’s the breakdown for chunksize:

chunks less than 64MB and up to 64MB = 4 VLFs
chunks larger than 64MB and up to 1GB = 8 VLFs
chunks larger than 1GB = 16 VLFs

And, what this translates into is that a transaction log of 64GB would have 16 VLFs of 4GB each. As a result, the transaction log could only clear at more than 4GB of log information AND that only when it’s completely inactive. To have a more ideally sized VLF, consider creating the transaction log in 8GB chunks (8GB, then extend it to 16GB, then extend it to 24GB and so forth) so that the number (and size) of your VLFs is more reasonable (in this case 512MB).

You should visit Kimberly’s blog entry for more information. You can also get more information about Virtual Log Files here.

Microsoft Plans Office 365 Upgrades

Office 365 - @SeniorDBA

A few months ag0 Microsoft announced that Windows 10 would receive major updates just twice a year, scheduled for September and March. Based on feedback from enterprise customers wanting a more tolerable schedule, Microsoft moved to make their release schedule more predictable.

What some people missed is that they also announced an identical schedule for corporate subscribers to Office 365. They aligned the update schedule with Windows 10. Microsoft says they plan to deliver and support Office 365 ProPlus updates, starting in September.

Microsoft also extended support 50% from 12 months per update to 18 months. The additional six months means your IT team can choose to push updates just once or twice a year.

Office 365 update channels, showing the new update channel names and release cadence

The twice-a-year feature updates will be named Semi-annual Channel (Pilot) and Semi-annual Channel (Broad), each describing how Microsoft envisions them being deployed in the enterprise. Most people will probably just refer to them as simply “Pilot” and “Broad”.

You can get more information here.

Best Hacking Tools Of 2017: Nessus Vulnerability Scanner

Nessus Vulnerability Scanner

Developed by Tenable Network Security, this tool is one of the most popular vulnerability scanners on he market. Tenable provides different versions, depending on your needs: Nessus Home, Nessus Professional, Nessus Manager, and Nessus Cloud.

You can use Nessus to scan multiple types of vulnerabilities that include remote access flaw detection, misconfiguration alert, denial of services against TCP/IP stack, preparation of PCI DSS audits, malware detection, sensitive data searches, etc. Nessus can also call a popular external tools.

Nessus is supported by a variety of platforms including Windows, Mac OS, and popular Linux distributions like Debian, Ubuntu, Kali Linux, etc.

You can get more information and download the Nessus Home (free) tool here. The commercial version is available here.

Installing the Linux Bash Shell on Windows 10

Windows 10’s Anniversary Update offered a new feature for developers: A full Ubuntu-based Bash shell that can run Linux commands on a Windows 10 client. This is possible by using the new “Windows Subsystem for Linux” Microsoft added to Windows 10.

This isn’t a virtual machine or Linux software compiled for Windows. Microsoft worked with Canonical to offer a full Ubuntu-based Bash shell. This isn’t Linux, it is just the Bash shell and the exact same binaries you’d normally run on Ubuntu Linux.

It’s intended for developers who want to run Linux command-line utilities on Windows. They’ll get access to the Windows file system, but you can’t use Bash commands to automate normal Windows programs, or launch Bash commands from the standard Windows command-line.

How to Install Bash on Windows 10

To get started, make sure you have installed the Windows 10 Anniversary Update (build 14316 or higher). This also only works on 64-bit builds of Windows 10.

Once you’re sure you’re using the correct version of Windows 10, open the Settings app and go to Update & Security > For Developers. Activate the “Developer Mode” switch here to enable Developer Mode.

@SeniorDBA

Next, open the Control Panel, click “Programs,” and click “Turn Windows Features On or Off” under Programs and Features. Enable the “Windows Subsystem for Linux (Beta)” option in the list here and click “OK.”

After you do, you’ll be prompted to reboot your computer. Click “Restart Now” to reboot your computer and Windows 10 will install the new feature.

@SeniorDBA

After your computer restarts, click the Start button (or press the Windows key), type “bash”, and press “Enter.”

The first time you run the bash.exe file, you’ll be prompted to accept the terms of service. The command will then download the “Bash on Ubuntu on Windows” application from the Windows Store. You’ll be asked to create a user account and password for use in the Bash environment.

If you’d like to automate the installation of Bash instead, you can run the following command in a Command Prompt window. This will automatically agree to all prompts and set the default user to “root” with no password:

lxrun /install /y

Using Ubuntu’s Bash Shell

You now have a full command-line bash shell based on Ubuntu, which means you can use Ubuntu’s apt-get command to install software from Ubuntu’s repositories. You’ll have access to all the Linux command line software out there.

To open the Bash shell, just open your Start menu and search for “bash” or “Ubuntu.” You’ll see a “Bash on Ubuntu on Windows” application. You can pin this application shortcut to your Start menu, taskbar, or desktop for easier access.

If you’re experienced using a Bash shell on Linux, Mac OS X, or other platforms, you’ll be right at home. You don’t need to use sudo, as you’re given a root shell. The “root” user on UNIX platforms has  full system access, like the “Administrator” user on Windows. Your Windows file system is located at /mnt/c in the Bash shell environment.

Use the same Linux terminal commands you’d use to get around. If you’re used to the standard Windows Command Prompt with its DOS commands, here are a few basic commands on both Bash and Windows:

  • Change Directory: cd in Bash, cd or chdir in DOS
  • List Contents of Directory: ls in Bash, dir in DOS
  • Move or Rename a File: mv in Bash, move and rename in DOS
  • Copy a File: cp in Bash, copy in DOS
  • Delete a File: rm in Bash, del or erase in DOS
  • Create a Directory: mkdir in Bash, mkdir in DOS
  • Use a Text Editor: vi or nano in Bash, edit in DOS

It’s also important to remember that the Bash shell and its Linux-imitating environment are case-sensitive. Unlike Windows, “MyFileName.txt” is different from “myfilename.txt”, just because of the use of capital letters in the name.

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.