BLOGS

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

 

ByHariharan Rajendran

Prepare data for Power BI  – Local File

As we discussed the levels in Power BI, the first level is creating dataset.

To start prepare the dataset, first sign in to the power BI here,

This article will talk about, how to create a dataset from local file.

Steps to import the local file,

  1. Sign in to the Power BIPowerBI
  2. Check for Get Data and click as like below.
  3. Click on “Get” under FilesPowerBI1
  4. It will display the options to import the files from different places.PowerBI2
  5. Click on “Local File”
  6. Select the file from your local folder and press open. Make sure that file should have both dimension and measure values. Power BI only supports Excel and CSV files and Power BI Desktop files.PowerBI3PowerBI4
  7. Check for uploaded files under Datasets.PowerBI5
  8. Dataset is ready to create reports and dashboards.

 

ByHariharan Rajendran

PolyBase Services in SQL Server 2016 CTP

Microsoft SQL Server 2016 has one of the exciting feature called PolyBase. It is acts as a bridge between relational database and Hadoop.

We can access all structured, unstructured and semi-structured data using known language of SQL.

Polybase allows user to use T-SQL Statements in Microsoft SQL Server Management studio to access data stored in Hadoop or Microsoft Azure blob storage.

Below services should up and running to use this feature.

  1. SQL Server PolyBase Data Movement
  2. SQL Server PolyBase Engine

To check the installed services in system,

  1. Press Ctrl + R (Run command)
  2. Type services.msc and enter

polybase

ByHariharan Rajendran

Secrets behind Query processing Speed in Columnstore Index

Go through my previous article here, about Data storage format in columnstore Index.

There are lots of improvements in Query processing speed when we use columnstore  Index.

  1. The data are organized in a column. As a result, a much higher level compression is possible, compare than row store.
  2. It uses Vertipaq technology for data compression. The Compression rate can be achieved up to 15 to 1. Click here, to know about Vertipaq technology.
  3. Compressed data require only less IO because the data transferred between disk to memory are reduced.
  4. Reducing IO leads to improvement in query processing and response times.
  5. Amount of data transfer to memory is going to be less which leads less space is required in memory to hold the data which processed by the query.
  6. SQL Server fetches only the data which are required for the query. For example, data is maintained in a column from C1 to C10, if the required data residing only on C6 & C7 then it will fetch only those columns.
  7. It uses the advanced technology for processing the queries. The data in the columns are processed in batches using high-efficient vector technology. This processing mode is called “Batch-Mode Processing”.
ByHariharan Rajendran

Temporal Table in SQL Server 2016

Temporal is a database feature that was introduced in ANSI SQL 2011 and this is now supported in SQL Server 2016.

It is a new type of user defined table and designed to keep track of all historical changes on the table and make easy to do point in time analysis.

Main reasons to use temporal table,

  1. In real time, data is not going to be static. To track the changes (Auditing) of the data over a point of time.
  2. Recovering from accidental data changes.

Prerequisites to create temporal table,

  1. The table should have a primary key column
  2. Two columns should have datatime2 data type to track the data validity (from & to).

Syntax to create Temporal Table

CREATE TABLE <Table Name>

(

<column 1> int NOT NULL PRIMARY KEY CLUSTERED

,<column 2> <datatype>

,.

,.

, <column n>

, [ValidFrom] datetime2 (2) GENERATED ALWAYS AS ROW START

, [ValidTo] datetime2 (2) GENERATED ALWAYS AS ROW END

, PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)

)

WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = <schema>.<tablename> –Optional));

We can give any name instead of ValidFrom and ValidTo, both should be bind in PERIOD.

ByManjunath Jogalekar

Alert Feature in Microsoft SQL Server

Alert feature in Microsoft SQL server is one of the prominent feature available in SQL server. Before we understand what Alert feature is and how to make use of the feature, first let us understand about basic meaning of Alert.

Alert is a warning or notification highlighted or notified to individual, group or other subsequent action for the predefined scenarios. Having alert system will keep away any of the future hazardous or hefty loss.

In SQL server Alert work similar as the Alert system available in any of the other systems available in other systems or process. To feel the alert system how it works you can consider the smartphones or any other mobile devices which through warning when there Is a low battery or any communication received.

As simple example earlier, SQL server alert system behaves the same way where it notifies the end user about any performance issues, Starting or stopping the process, intimating the end user through mail or other any communication methods.

SQL server Alert system is managed through SQL Server agent and SQL server agent should be up and running to make use of SQL server Alert System feature.

SQL Server Alert system can notify the end users through following communication methods.

  • Email
  • Net share
  • Pager
  • In SQL server 2014 there are three type Alert system
  • SQL Server Event Alert
  • SQL server Performance Condition Alert
  • WMI Event alert
  • The following steps will instruct to set up Alert system in SQL server
  • Expand SQL server agent and make sure SQL server agent is up and running.

01

  • Right click on the alert system and click new Alert

02

  • Post to the previous step alert configuration window will be open. In the configuration window update the properties for the alert. In our example we have considered Alert system for SQL server performance condition alert type and the properties provided for Alert system will change according to the type of alert selected.

03

  • Name: Provide the alert Name which is going to be the identification for the Alert.
  • Type: Select the type of Alert (In this case we have selected SQL server performance condition alert.)
  • Counter: Configure the counter to choose the required counter ( In our Case it SQL server databases)
  • Instance : Configure the Instance for which Notification has to be processed ( Note: This is a counter instance and not server instance)
  • Alert: Here we will configure when the alert should be configured. In this case alert will be triggered if ‘Dageop’ log file exceed 75%
  • Move to the response tab provided in the left pane of configuration window. In the response window configure the Alert either to send the mail or to trigger some process or it might be both. In the below case we have considered both to send mail and also to trigger the ‘Flush_Dageop_Log’

04

  • The above steps will create Alert called ‘Log_Exceed_Alert’ which can be later redefined by double clicking the Alert from Alert folder in SQL server agent. This Alert will trigger Flush_Dageop_Log package to reduce the growth of Dageop database log file and also notification will be provided to the end user.

05