BLOGS

ByDr. SubraMANI Paramasivam

Download and install SQL Server 2016 RC3

You can now download the SQL Server 2016 Release Candidate 3 from TechNet Evaluation Center. In the below screenshot, I have chosen to download it as ISO copy. This will enable me to attach it as disk drive in my Hyper-V Virtual Machine.

SQL2016RC3_Download_01

Based on few browser plugin issues and local settings, the download may not start immediately. Hence you can click on Manual download again in case if the above chosen ISO file does not download.

SQL2016RC3_Download_02

I am looking to find if all the previous issues that I faced with previous releases have been fixed in this new Release Candidate 3 or not.

Have a good journey in this SQL Server 2016 as we have now come to the last RC3 before we get the full SQL Server 2016.

ByHariharan Rajendran

Manage Azure Storage Account using Azure CLI

We can manage storage account through Azure Portal or PowerShell Scripts.

This article will explain you the commands that you need to use inside Azure CLI to manage storage account.

Download the Azure CLI in following link and choose respective installer package.

image

Install it on your machine. if you are working with Azure and if you had installed different tools to interact with Azure, you may have different command line tools in your machine. To find the recently installed one, just search for “Microsoft Azure Command Prompt”.

You will see a tool like below,

image

#First login into Azure using below command

azure login

You need to open the following link in your browser, https://aka.ms/devicelogin and provide the given code to authenticate.

If you have multiple subscription, then you can able to choose the default one.

#List out all the subscriptions

azure account list

#To set the default subscription

azure account set “name of your subscription”

#Create a storage account on the selected subscription

azure storage account create <storage account name>

Above script will ask you to provide two values, “Account Type” & “Location”. Just provide the numbers of the respective information.

image

To create a containers or all other storage services, we need to fix the created storage account details in environment variable so that when we run a further commands, it will consider the correct storage account. If you missed to set up environment variable then you will end up with following error.

image

Azure has command called “export” which is used to hold your storage connection string, just like a variable but it is not working as expected. If you try like below,

    export AZURE_STORAGE_ACCOUNT=<account_name>

You will get below error. So we can follow different approach to have environment variable.

‘export’ is not recognized as an internal or external command,
operable program or batch file.

 

#Get the connection string of storage account

azure storage account connectionstring show <storage account name>

#Set Azure Connection string as environment Variable

SET AZURE_STORAGE_CONNECTION_STRING=<connection string>

#Create a container

azure storage container create <container name>

image

#Upload the blobs
azure storage blob upload <Path>  <container name>  <blob name>

Example:
azure storage blob upload E:\Encryption.png logs myblob

#Download the blobs
azure storage blob download <container name>  <blob name>  <local path>

Example:

azure storage blob download logs myblob E:\log.png

#Delete blobs
azure storage blob delete <container name> <blob name>

Example:
azure storage blob delete logs myblog

image

ByHariharan Rajendran

SQL Server 2016 RC2–Enhancements in R Integration

Microsoft released the latest version of SQL Server 2016 RC2 which has many enhancements. This article exploring the enhancements related R integration in SQL Server.

Before RC2, we need to install SQL Server 2016 prior versions and then R related components separately. This scenario has been changed, We can install R services or standalone R server through SQL Server 2016 RC2 installation itself.

Look at the below changes in the installation center.

New feature to create a standalone R Server.

image

 

image

Confirmation to install Microsoft R Open.

image

ByDr. SubraMANI Paramasivam

FREE Technical Data Seminar in London

Hi Everyone. I am very excited to announce the DAGEOP’s first FREE DATA SEMINAR programme now available for you to attend and enjoy the sessions from world renowned speakers. Please visit this link to get yourself registered and I have 3 sessions frozen already and awaiting for the final one. I will get back to you with final agenda once that is confirmed.

You can find more information on this from http://www.dageop.com/Events/DaGeopsDataDay.aspx

Also provided you the agenda that is available as of now. Don’t forget to register as this seating is limited.

DaDataDay_May142016_Schedule

 

ByHariharan Rajendran

Stretch in SQL Server RC0

SQL Server Release Candidate 0 version has been released. We have been discussed about stretch database in CTP 3.3. There are many improvements in RC0. This article is about, how to enable the stretch in RC0.

image

Go through Stretch in SQL Server 2016 CTP 3.3.

Steps to enable the Stretch in RC0 Version,

Step 1:  Right click on your database and go to Tasks->Stretch->Enable

image

image

Step 2: Choose the tables you want to stretch.

image

Step 3: Provide your Azure subscription to configure SQL Server and database for stretch.

image

Step 4: Set up master key for your database.

image

Step 5: Provide your IP, in case if you have configured.

image

image

Step 6: Configuration process started to enable stretch for database and table.

image

image

As you can see, there are some difference in steps to configure when compare with CTP 3.3 version. There is no changes on Monitoring the stretch.

Enable Stretch for Tables

Previously, we need to select Reconfigure option to add new tables to stretch but that “Reconfigure” option has been removed. Now, we have “Enable”, “Disable” & “Monitor”.

To add new tables to stretch, follow the below steps.

Choose the new table to add into stretch. Right click –> Stretch –>Enable

image

image

image

image

image

Disable Stretch

To Disable the stretch for the database, we need to disable the stretch for tables first. We don’t have option to disable in one short if multiple tables are in stretch. Need to manually disable all the tables from stretch.

If we try to disable stretch for database without disabling tables then below message will be appeared.

image

There are two options in disable on table,

1. Bring data back from Azure

2. Leave data in Azure.

Once stretch is enabled for a table, data migration will be initiated from local to azure through batch process. If we decided to disable the stretch then make sure whether we want to bring data back to local or keep them on azure it self. Based on  that choose any of the above option.

Note: The table in Azure database will not be deleted by process, you need to delete it by going to azure management portal.

If you are selected “Bring data back from Azure” then below message will be prompted.

image

Once disable the stretch for all your tables, then disable the stretch on your database.

image

ByHariharan Rajendran

Issues in R Package in SQL Server 2016

I have got a below error when I try to use one of the R package “ggplot2”.

R

It says that there is no package called ggplot2 even though I installed the ggplot2 package in R.

Message from R when I install ggplot2 package,

R1

The above message clearly explaining that the package is not installed on defined path, it has been downloaded on temporary directory. It means there was no proper installation happened in R. This was the reason for error in SQL Server.

Follow the below steps to sort out the issue,

Use “.libPaths” to define the library path before install a package in R.

Command is,

> .libPaths (.libPaths()[2:3])

> install.packages(“ggplot2”)

Package will be installed like below,

R2

Now run the same SQL Script with R, this time it will be executed without an issue.

ByDr. SubraMANI Paramasivam

Data Driven & SQL Server 2016

Following today’s Data Driven live streaming from New York, Satya Nadella have opened the speech by providing insights on what CEOs and organization owners should see with Data being the key factor driving the business.

DataDriven_02

DataDriven_01

 

Also SQL Server 2016 RC (Release Candidate) is now available here and no more CTPs from Microsoft download site. Be the first to download the latest Release Candidate and try the brand new features of SQL Server.

DataDriven_04

DataDriven_05

ByHariharan Rajendran

Monitor Stretch in SQL Server 2016

Go through my previous article to enable the stretch for your database and table. This article is about how to monitor the stretch. You can monitor the stretch through GUI or DMVs.

We have below DMVs available to monitor the status of the stretch.

select * from sys.remote_data_archive_databases

 

The above script will provide the details about what is the database name in Azure used for stretch.

image

 

select * from sys.remote_data_archive_tables

 

This will tell you, what is the table name in azure used for stretch.

image

The above result has column called “filter_predicate”. It means, if apply any predicate function while configure the stretch then you can see that predicate function here. unfortunately, we don’t have option to apply the predicate function in GUI method. It is available when we enable stretch through T-SQL Script.

select * from sys.dm_db_rda_migration_status

This script will tell you about data migration. Data will start migrate from the table once stretch is enabled. Migration will start  from on-premises SQL Server to Azure. It is using batch process so it will migrate maximum of  4999 records in a batch. The batch id will be available in Azure table.

image

To see the live migration, we can use “SP_SPACEUSED”. By seeing the rows and size we can identify the data migration.

image

 

You can monitor the stretch using GUI,

image

image