BLOGS

ByDr. SubraMANI Paramasivam

Download and install latest Microsoft SQL Server 2016 CTP 3.2

You can download the latest Microsoft SQL Server 2016 CTP 3.2 from this link. Ensure to choose the ISO option in this link.

Once downloaded you can start executing the exe file and I have provided enough screenshots to support you with your own installation. My installation is not on my laptop but in the virtual machine. So once you start your Hyper-V Manager and shoot your virtual machine and start the windows. Once booted, you need to go to main menu Media -> DVD Drive -> Insert Disk… option. Once you choose this you will get the below screen and choose your already downloaded SQL Server 2016 CTP 3.2.

InstallMSSS_2016_CTP32_01

Once you have chosen the ISO image, click on Open and you will see the new DVD Drive D: like below screenshot. This is equivalent to inserting your DVD drive into your computer. So now you have the ISO mounted on to the virtual machine and then you are good to go with the installation by double clicking on the setup file, which can be seen below.

InstallMSSS_2016_CTP32_02

Then you will get the SQL Server Installation Center which will be the source for you to plan, install, maintain, advanced resources, etc., In here click on Installation and then choose the first option to create a brand new installation on your system.

InstallMSSS_2016_CTP32_03

Then you will see the below screen, which will then start the setup.

InstallMSSS_2016_CTP32_04

As we have downloaded the CTP 3.2 which is valid for 180 days, you can leave the option button checked with the Free Evaluation edition, from the below screenshot.

InstallMSSS_2016_CTP32_05

Once, you click on next then you will get the screen to accept the License Terms, like shown below. Read the license terms for Pre-Release software license terms and then check the box saying you accept the license terms, this will then enable you to click on Next and proceed further.

InstallMSSS_2016_CTP32_06

The system will then start checking the global rules, product updates and try to install setup files. If it already exists, it will be skipped and if not it will be installing them here automatically. In the below screen, “Download setup files” and “extract setup files” are skipped and “Scan for product updates” completed and “Install setup files” yet to start.

InstallMSSS_2016_CTP32_07

Then you will get the results on the installation rules and gives you the status of each rule. If it failed any one, you cannot proceed your installation and needs to be fixed. But you still can proceed further installation with warnings.

InstallMSSS_2016_CTP32_08

You can click on the Warning link and see the issues in detail. Below screenshot advises to enable appropriate ports to be opened for remote access.

InstallMSSS_2016_CTP32_09

I don’t want to choose all feature with default option as this will install everything and by choosing SQL Server Feature Installation, you have options to choose what do be installed in simple check/uncheck boxes.

InstallMSSS_2016_CTP32_10

Below image provides all the available features that you can choose to install.

InstallMSSS_2016_CTP32_11

I have simply copy pasted (below) the feature description from each of the features that you have from this installation box to give you one point of understanding on each individual features.

Instance Features:

The configuration and operation of each instance feature of a SQL Server instance is isolated from other SQL Server instances. SQL Server instances can operate side-by-side on the same computer.

Database Engine Services

Includes the Database Engine, the core service for storing, processing and securing data. The Database Engine provides controlled access and rapid transaction processing and also provides rich support for sustaining high availability. The Database Engine also provides support for the utility control point in the SQL Server Utility. Only Database Engine Services and Analysis Services can be clustered.

SQL Server Replication

Includes a set of technologies for copying and distributing data and database objects from one database to another and synchronizing  between the databases for consistency. You can use replication to distribute data to different locations, and to remote and mobile users over local and wide area networks, dial-up connections, wireless connections and the Internet.

Advanced Analytics Extensions

Includes Advanced Analytics Extensions that enables integration with R language using standard T-SQL statements.

Full-Text and Semantic Extractions for Search

Includes the Search engine that supports Full-Text Extraction for fast text search as well as Semantic Extraction for key phrases (likely tags) and similarity search on content stored in SQL Server.

Data Quality Services

Includes Data quality database objects.

PolyBase Query Service for External Data

Includes PolyBase technology that enables truly integrated querying across Hadoop non-relational data and SQL Server relational data using standard T-SQL statements.

Analysis Services

Includes Analysis Services and tools used to support online analytical processing (OLAP) and data mining. Only Database Engine Services and Analysis Services can be clustered.

Reporting Services – Native

Includes Reporting Services, a server-based application for creating, managing, and delivering reports to email, multiple file formats, and interactive Web-based formats. The Native mode server provides all processing and management functionality through Reporting Services components. Reporting Services cannot be clustered.

Shared Features

Each shared feature is installed once within a defined scope and operates within that scope. The defined scope can span all SQL Server versions on a computer (e.g., SQL Server Browser), can be isolated to one major version of SQL Server (e.g., SQL Server Management Tools), or can be isolated to one or more minor versions.

Reporting Services – SharePoint

Includes Reporting Services, a server-based application for creating, managing, and delivering reports to email, multiple file formats, and interactive Web-based formats. SharePoint integrated mode integrates the report server with SharePoint products.  The report viewing and report management experience are integrated with SharePoint sites and libraries. Reporting Services cannot be clustered.

Reporting Services Add-in for SharePoint Products

Includes management and user interface components to integrate a SharePoint product with an SSRS report server in SharePoint integrated mode. The add-in only needs to be installed on server running a SharePoint product.

Data Quality Client

Includes Data quality client objects.

Client Tools Connectivity

Includes components for communication between clients and servers.

Integration Services

Includes the designer, runtime, and utilities that enable Integration Services to move, integrate, and transform data between data stores.

Client Tools Backwards Compatibility

Contains Client Tools Backwards Compatibility

Client Tools SDK

Includes the software development kit containing resources for programmers.

Documentation Components

Installs only the components that you use to view and manage the documentation for SQL Server 2016 CTP3.2. By default, the Help Viewer component uses the online library. After installing SQL Server, you can use the Help Library Manager component to download documentation to your local computer.

Management Tools – Basic

Includes Management Studio support for the Database Engine and SQL Server Express, SQL Server command-line utility (SQLCMD), SQL Server PowerShell provider, and Distributed Replay Administration Tool.

Management Tools – Complete

Adds the following components to the basic management tools installation: Management Studio support for Reporting Services, Analysis Services, and Integration Services technologies, SQL Server Profiler, Database Tuning Advisor, and SQL Server Utility management.

Distributed Replay Controller

Includes the Distributed Replay Controller which orchestrates the actions of the distributed replay clients.

Distributed Replay Client

Includes the Distributed Replay Client. Multiple Distributed Replay Clients work together to simulate a workload against an instance of SQL Server.

SQL Client Connectivity SDK

Includes SQL Server Native Client (ODBC / OLE DB) SDK for database application development.

Master Data Services

Includes Master Data Services, the platform for integrating data from disparate systems across an organization into a single source of master data for accuracy and auditing purposes. Installs the Master Data Services Configuration Manager, assemblies, PowerShell snap-in, and folders and files for Web applications and services.

Redistributable Features

SQL Server redistributable and shared features are installed when needed: Error and Usage Reporting, SQL Server Native Client, MSXML version 6.0, Sync Services for ADO.NET, and SQL Server Browser.

After reviewing every single feature, I have chosen what is required for me and you can see from below screenshot. I can visit anytime later to install the missing features, when required.

InstallMSSS_2016_CTP32_12

Once I have done the selection for the required features, the next step it will check for the rules for the selected features, like below and gives me the status of Passes/Failed/Warning. Again it is only only warning with post-installation steps are required for my Advanced Analytics Extensions, which I cover in separate posts.

InstallMSSS_2016_CTP32_13

In the next step of Instance configuration, I have options to choose the default or named instance. I have chosen the named instance and provided the Named Instance name. The instance ID will be picked up automatically from the Named Instance. This below screenshot also shows the already existing instances on the same system and I have SQL 2014 installed with the instance name of DAGEOPINS01.

InstallMSSS_2016_CTP32_14

Next in the server configuration screenshot (below), you will have options to choose whether the services should be started Manually everytime the system starts or automatically. Also you have options to choose the account names. Note: In the below screenshot, I have no option to change the startup type for “SQL Server Launchpad” & “SQL Full-text FIlter Daemon Launcher” as they are considered system services and the SQL Server directly controls them.

InstallMSSS_2016_CTP32_15

In the database engine configuration section, it is always good to choose the Mixed mode, so that you get the option to recover the instance in case if you loose your master AD account. By selecting mixed more I have given a password for the System administrator account. Also I have clicked on “Add Current User”, which will automatically select my current logged in account as part of the SQL Server administrator. You can choose more users to be part of admin, by clicking “Add” button.

InstallMSSS_2016_CTP32_16

In the Analysis services configuration, leave the default Multidimensional and data mining mode and choose Add current User and click next.

InstallMSSS_2016_CTP32_17

For the distributed replay controller, I have chosen the Add current user to grant permissions for this service.

InstallMSSS_2016_CTP32_18

For the distributed replay client I have not made any change and you can continue to click next if any changes required.

InstallMSSS_2016_CTP32_19

For the PolyBase Configuration, I have selected the PolyBase scale-out group which enables me to have the SQL Server instance either as a Head or Compute node. Also I have not made any changes to the port range.

InstallMSSS_2016_CTP32_20

After all the steps, finally you get to see the summary of what you have selected and what SQL Server will be installing from the below screenshot.

InstallMSSS_2016_CTP32_21

You can now sit back and relax for about 15 to 20 minutes and the installation will be done by then.

InstallMSSS_2016_CTP32_22

You will now have fully installed version as shown below with status of Succeeded and also you have option to see all the installation steps and log file from the link provided in the below screenshot.

InstallMSSS_2016_CTP32_23

You are now good to go and start using the SQL Server.

ByHariharan Rajendran

Steps to Create HDInsight Cluster in Azure

Step 1: Log into azure portal

Step 2: Click “Browse” and look for HDInsight Clusters which will have Hadoop icon.

HDCluster1

Step 3: Click the HDInsight Clusters and “Add” a new cluster.

HDCluster2

Step 4: Provide the cluster name and other details as like below and cluster type should be “Hadoop”.

HDCluster3

Step 5: Cluster credentials are mandatory so define the credentials. In this section, you can enable remote desktop by selecting “YES”, need to provide remote desktop credentials.

HDCluster4

Step 6: Datasource must be configured for cluster.

HDCluster5

Step 7: Node pricing tiers is required to configure as like below.

HDCluster6

Step 8: Click the create button when all the steps are filled and configured. HDInsight cluster will be created and deployed for your use.

 

 

ByDr. SubraMANI Paramasivam

SQL Server 2016 CTP 3 in Azure VM

Though SQL Server 2016 CTP 3.2 was released in mid Dec 2015, you can download the latest version from this link or you can also choose CTP3 to try by creating Azure Virtual machine with this link.
After clicking on this link, you need to login to Microsoft Azure portal with your subscription and then you can see below image, which talks about the legal terms, documentation, etc.,
SQL2016CTP3_AzureVM_01
Once you have validated, you can click on the “Create” button, which will then take you to below screenshot which has simple 5 steps to create the VM with SQL Server 2016 CTP3.2.
SQL2016CTP3_AzureVM_02
Then, I have provided below details in step 1 and clicked okay.
Server name: DAGEOP2016CTP32
user name: MSSQLCTP32
password: Password1!
Resource Group: MSSQLGroup
Then, in step 2, I have moved on to creating the D1 standard size that looks like below screenshot. You can choose the right size according to your requirements. If you don’t see all sizes, then you need to click on Show all instead of Recommended options.
SQL2016CTP3_AzureVM_03
Then in Step 3, it gives all the settings option for Storage account, virtual network, subnet, public IP address, Network security group. You can continue to choose the defaults or choose from already existing accounts if you have any.
SQL2016CTP3_AzureVM_04
In step 4, you will have further options to configure SQL Server settings like SQL authentications, port, connectivity, etc.,
SQL2016CTP3_AzureVM_05
In step 5, summary you can see all the configured features that will enable you to review at once and then click on OK button.
SQL2016CTP3_AzureVM_06
This will directly take you to the dashboard and you can see below screenshot which deploys the SQL Server 2016 CTP3 Evaluation. This will take minutes to hours to get it deployed and make the VM available for you to use.
SQL2016CTP3_AzureVM_07
ByHariharan Rajendran

Dataset for Power BI from SharePoint Team Site

We discussed about preparing datasets using files from different environment (Local drive & OneDrive) in my previous articles.  Go through here.

We have another option under files section which is “SharePoint – Team Sites”.

It is very easy to get a file (dataset) from SharePoint as we are using business email to connect Power BI so no need to provide the credentials again.

Select the file from SharePoint and connect to Power BI. File will be added in to Datasets.

PowerBI9

 

ByHariharan Rajendran

Dataset for Power BI from Onedrive

In my last article, we discussed about how to prepare a dataset from local file.

This article speaks about how we can create a dataset which is stored in OneDrive. It can be personal or business OneDrive.

There is no changes up to 4th step from my previous article, after that follow the below steps

  1. Choose either OneDrive – Business or OneDrive – Personal where the file stored. PowerBI6
  2. Here am using OneDrive – Personal, click it.
  3. It will ask for Microsoft account credentials to access OneDrive.PowerBI7
  4. Choose the file from OneDrive and select Connect which is on top right.PowerBI8
  5. Selected file will be available under Datasets in Power BI