Author Archives: Dr. SubraMANI Paramasivam

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.


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.


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.


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


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.


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.


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.


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.


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.


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.


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


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.


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.


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.


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.


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.


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


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


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


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.


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.


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


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.


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

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.,
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.
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.
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.
In step 4, you will have further options to configure SQL Server settings like SQL authentications, port, connectivity, etc.,
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.
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.
ByDr. SubraMANI Paramasivam

7 key habits, practice & experience, which made Bill Gates succeed

I just flipped through my phone and found this amazing article which might be a great energy re-charge for many youngsters today. This clearly talked about the key habits, practise and experience from Bill Gates that helped him succeed. This is just a simple formulae for all of us to do, what we do should have hard core dedication & hard work to succeed. The main 7 key points highlighted are

  1. Get in early – and learn
  2. Seek forgiveness, not permission
  3. Value your work
  4. Learning matters more than school
  5. When you’re in charge, take charge
  6. Be the guy who predicts the future
  7. Take on a big enough mission.

What a great re-invention that we should really apply in our day to day life. I sincerely advise and suggest to all of you who don’t do this or have done this in the past but don’t do it anymore, to restart in doing the same.

All the very best.


ByDr. SubraMANI Paramasivam

Hyperscale technology lets a DBA sleep, +30 minutes a day!

Ease of access and easy life is now possible with On-premises and hybrid cloud technology with SQL Server 2016.

SQL Server 2016’s Hyperscale technology gives us a huge relief in Backups, Disaster Recovery solutions, access resources no matter whether they are in on-premises, private or public cloud. New tools within SQL Server 2016 and Microsoft Azure has made every single DBA/Developers life much easier to scale to the cloud.
ByDr. SubraMANI Paramasivam

Tired of connecting on-prem & cloud data? Take a DB stretch

Save your time and cost on storages and get extra time to relax with this brand new stretch database concept within SQL Server 2016.

You can stretch your legs and relax and get a quick session on delivering your analytics against hot, warm & cold data. Stretch Database is the new concept, which allows you to stretch from on-prem to cloud easily. You can easily enable / disable data stretch, accessing data using stretch database, setup remote data archiving, basic concepts on enabling database/table and backup & restore for the stretch enabled databases.

Stretch DB also covers the concepts of Shallow & Deep backups. However, Deep backups are not supported with SQL Server 2016 CTP2.

You can download the latest CTP 3.2 from this link.

ByDr. SubraMANI Paramasivam

How to verify and then create a specific SQL users?

It is always a best practise to validate whether the logins & user exists before creating a new one in SQL Server. You can verify this by using the system tables (syslogins & sysusers). I have given below T-SQL queries to validate first and then create. To access any specific database, a login creation is a mandatory. If the login already exists, you can still verify it with syslogins and then create the user for any new databases.

–Check the logins first for the SQL Server
USE [master]
SELECT name FROM sys.syslogins;
SELECT name FROM sys.sysusers;

–Check the users for any specific databases.
USE [AdventureWorks2012]
SELECT name FROM sys.sysusers

After reviewing the available logins & users for the SQL Server and specific databases, and according to your company policies in naming convention, you then can create users accordingly. But before that need to drop the user if exists with the below queries.

–IF EXISTS DROP USER & LOGINS for a SQL user named ServiceAct_App1
USE [AdventureWorks2012]
IF EXISTS (SELECT name FROM sys.sysusers WHERE name = N’ServiceAct_App1′)
DROP USER ServiceAct_App1
USE [master]
IF EXISTS (SELECT name FROM sys.sysusers WHERE name = N’ServiceAct_App1′)
DROP USER ServiceAct_App1
IF EXISTS (SELECT name FROM sys.syslogins WHERE name = N’ServiceAct_App1′)
DROP LOGIN ServiceAct_App1

In here, I am creating login & user after dropping it with if exists statement.

–CREATE LOGIN, CREATE USER, ASSIGN ROLES for Applications – Service Account
USE [master]
USE [AdventureWorks2012]
CREATE USER [ServiceAct_App1] FOR LOGIN [ServiceAct_App1]
ALTER ROLE [db_datareader] ADD MEMBER [ServiceAct_App1]
USE [master]
CREATE USER [ServiceAct_App1] FOR LOGIN [ServiceAct_App1]
ALTER ROLE [db_datareader] ADD MEMBER [ServiceAct_App1]


ByDr. SubraMANI Paramasivam

Resource Governor – Quick tips

Quick and useful tips on using Resource Governor in your SQL Server environment. I have selected randomly on some of the Resource Governor related T-SQL queries that might be useful in your work environment.
–to view the resource governor configuration
SELECT * FROM sys.dm_resource_governor_configuration;


— to view the resource governor workload groups
SELECT * FROM sys.dm_resource_governor_workload_groups


— to view the resource governor resource pools

SELECT * FROM sys.dm_resource_governor_resource_pools


–to reset the accumulated statistics about the resource governor usage


–if you have more than one resource governor classifier function, you can use below statement to apply another function to take effect

WITH (CLASSIFIER_FUNCTION = dbo.AnotherClassifierFunction);

–Code to Remove All Classifier Functions from the Resource Governor

ByDr. SubraMANI Paramasivam

SQL Server 2016 CTP 3.1 is out now

Microsoft keeps improving the SQL Server 2016 in every CTP version and the latest CTP 3.1 has been released and can be downloaded in this link.

SQL Server 2016 Technical documentation is available here.

As usual, the downloads are available both in ISO & CAB formats.