BLOGS

ByHariharan Rajendran

Integrate R in SQL Server CTP 3

It is very easy to integrate R services with SQL Server 2016.

First, make sure you have selected the “Advanced Analytics Extensions” while installing SQL Server.

image

Next, download and install below two R components

1. Revolution R Enterprise 7.5

2.  Revolution R Open 3.2.2 for Revolution R Enterprise 7.5

Once the above components are installed then enable the external scripts in SQL Server Management Studio.

exec sp_configure 'external scripts enabled', 1; 
reconfigure;

 

The next step is run the post installation script in command prompt, Locate the registerRext.exe file in your system.

Mostly the file will be reside in following location, if you have provided the default installation path, “C:\Program Files\RRO\RRO-3.2.2-for-RRE-7.5.0\R-3.2.2\library\RevoScaleR\rxLibs\x64”

Open a command prompt with admin privilege and go to above directory,

 

Step 1: Type below command in command prompt

cd  C:\Program Files\RRO\RRO-3.2.2-for-RRE-7.5.0\R-3.2.2\library\RevoScaleR\rxLibs\x64

 

Step 2:

registerRext.exe  /install     (If your SQL Server is default instance, if your SQL Server is named instance then use the below code)

 

Step 3 (Optional): 

registerRext.exe” /install /instance<SQLNamedInstance>

ByHariharan Rajendran

Create Data Warehouse in Azure

Creating a SQL Data Warehouse is very easy in Azure. It is the industry first cloud data warehouse with full fledged SQL capabilities and ability to grow, shrink pause in seconds.

Follow the below steps to create data warehouse

1. Open portal.azure.com

2. Choose New –> Date_Storage –> SQL Data Warehouse

image 

3. Define a name. You can create a new SQL Server or select existing one. Choose the DWU (Data Warehouse Unit) based on your requirement .

4. You can create DW with blank database or with Sample.

image

5. Click “Create”, Azure will create and deploy  DW for you.

image

ByHariharan Rajendran

Database Migration On-Premises to Azure

I have talked about how to import the database on premises SQL Server using .bacpac file which was taken from Azure export (find here).

This is article will speak about, how to migrate the database from on-premises SQL Server to Azure. We to need to perform two steps here.

1. Export the database and store it in Azure Storage Container

2. Import in Azure SQL Server

Exporting the database using data tier application since we need to get .bacpac file format.

Steps to Export and store in Azure Storage,

1. Choose your database which you want to migrate. Right Click on database and choose Tasks –> Export Data-tier Application.

image

image

2. Provide the Azure storage account details and select the container

image

image

image

3. Check the Azure storage for the .bacpac file.

image

 

Once exported the database, your next step is to import into Azure SQL Server

1. Choose Your SQL Server in Azure and Import database

image

2. Map your .bacpac file from your storage container.

image

Once above steps are completed then your database will be deployed on Azure

image

ByHariharan Rajendran

Dynamic Data Masking in SQL Server 2016

It is one of the security feature available in SQL Server 2016. This is applicable in SQL database in Azure as well.

It enables us to handle the sensitive data very securely. By implementing dynamic data masking, we can restrict the users not to see the sensitive data. It will add a mask to the data.

It is easy to implement in new and existing applications. Since it is implementing on database layer, there won’t be any changes required in application.

Let us discuss below topics,

1. Create a table with dynamic data masking

2. Alter or Modify the table to add data masking

3. Granting Permission to Unmask

4. Dropping Data mask

5. Limitations

6. Query Masked columns

 

1. Create a table with dynamic data masking

CREATE TABLE Users

  (UserID int IDENTITY(1,1) PRIMARY KEY,

   FirstName varchar(100) MASKED WITH (FUNCTION = ‘partial(1,”XXXXXXX”,0)’) NULL,

   LastName varchar(100) NOT NULL,

   Phone varchar(12) MASKED WITH (FUNCTION = ‘default()’) NULL,

   Email varchar(100) MASKED WITH (FUNCTION = ’email()’) NULL);

Insert data into the table

INSERT into Users (FirstName, LastName, Phone, Email) VALUES

(‘Hari’, ‘Haran’, ‘989436878’, ‘rhariharaneee@gmail.com’),

(‘Hariharan’, ‘Rajendran’, ‘9545679781’, ‘test@test.com’),

(‘Raj’, ‘Kumar’, ‘888978562’, ‘sales@test.com’);

SELECT * FROM USERS

image

Create a user and check the data,

CREATE USER User1 WITHOUT LOGIN;

GRANT SELECT ON Users TO User1;

 

EXECUTE AS USER = ‘User1’;

SELECT * FROM Users;

REVERT;

image

2. Alter or Modify the table to add data masking

create a table without masking function as like below,

CREATE TABLE Users_Info

  (UserID int IDENTITY(1,1) PRIMARY KEY,

   FirstName varchar(100)  NULL,

   LastName varchar(100) NOT NULL,

   Phone varchar(12)  NULL,

   Email varchar(100)  NULL);

 

INSERT into Users_info (FirstName, LastName, Phone, Email) VALUES

(‘Hari’, ‘Haran’, ‘989436878’, ‘rhariharaneee@gmail.com’),

(‘Hariharan’, ‘Rajendran’, ‘9545679781’, ‘test@test.com’),

(‘Raj’, ‘Kumar’, ‘888978562’, ‘sales@test.com’);

 

SELECT TOP 10 * FROM Users_Info;

image

Modify the table to add data mask function,

ALTER TABLE Users_Info

ALTER COLUMN FirstName ADD MASKED WITH (FUNCTION = ‘default()’);

ALTER TABLE Users_Info

ALTER COLUMN Phone ADD MASKED WITH (FUNCTION = ‘partial(1,”XXXXXXX”,0)’);

ALTER TABLE Users_Info

ALTER COLUMN Email ADD MASKED WITH (FUNCTION = ’email()’)

 

Check the result with different user

EXECUTE AS USER = ‘User2’;

SELECT * FROM Users_info;

image

3. Granting Permission to Unmask

GRANT UNMASK TO User1;

EXECUTE AS USER = ‘User1’;

SELECT * FROM Users;

REVERT;

image

4.Dropping Data Mask

ALTER TABLE Users

ALTER COLUMN FirstName DROP MASKED;

 

5.Limitations

Masking rule is not supported for below data type columns,

Encrypted Columns

FILESTREAM

COLUMN_SET

 

6.Query Masked Columns

It is using “sys.masked_columns” & “sys.tables” system tables.

SELECT c.name, tbl.name as table_name, c.is_masked, c.masking_function

FROM sys.masked_columns AS c

JOIN sys.tables AS tbl

    ON c.[object_id] = tbl.[object_id]

WHERE is_masked = 1;

image

ByHariharan Rajendran

SQL Azure Database Table Size

Identifying the table size of the azure SQL database is not possible through GUI since the properties of the table not providing the size information.

The different between the table properties in on-premises SQL database and Azure SQL database.

Right-Click the table and go to Storage,

On-premises  SQL Server

image

 

Azure SQL database,

image

As you can see, SQL Azure database table doesn’t have storage information of the table.

To identify the table size and number of records, we need to use SQL scripts.

Script 1 : It is using “sys.dm_db_partition_stats” & “sys.objects” system tables.

SELECT   

      o.name AS [table_name],

      sum(p.reserved_page_count) * 8.0 / 1024  AS [size_in_mb],

      p.row_count AS [records]

FROM  

      sys.dm_db_partition_stats AS p,

      sys.objects AS o

WHERE   

      p.object_id = o.object_id

      AND o.is_ms_shipped = 0 

GROUP BY o.name , p.row_count

ORDER BY o.name DESC

 

image

Script 2: Using sp_spaceused stored procedure.

Exec sp_spaceused [Users] –Table Name

image

ByHariharan Rajendran

Connect Azure Storage in SQL Server Management Studio

It is very simple to connect your azure storage in SSMS.

Make sure that you are not using full name of  your storage account.

For example: If you have storage account named “Test”. Usually, the full name of the storage account will be “https://Test.blob.core.windows.net”.

To connect your storage account in SSMS, use only the name of your account (here it is “Test”). Then provide the storage key.

To get the storage key, follow the below steps.

Visit Management portal and choose your storage account.

image

Select the Manage Access Keys and copy your key.

image

 

Use your storage account name and key in SSMS.

image

image

image

ByHariharan Rajendran

Limitations in Stretch DB in SQL Server 2016 CTP 3

As per Microsoft article, we have many limitations that we need to understand before start enabling the stretch database in your environment.

I have discussed few limitations here and also what will happen when we override those limitations.

Limitations

1. We cannot perform UPDATE and DELETE operations on stretch enabled tables

image

2. We cannot create a index for a view that includes the stretch tables. “New Index” option is disabled.

image

3. We cannot perform update or delete a view that includes stretch enabled tables however insert is possible.

image

ByHariharan Rajendran

[Step by Step] Create Database from .bacpac File

We discussed before about how to export the database from azure and stored the bacpac file in azure storage container.

This article about how to create a database in on-premises  SQL Server using bacpac file.

Step 1: Download the bacpac file from azure storage container

image

Step 2: Use data tier application to import the bacpac file in SQL Server

image

Step 3: Follow the wizard instructions. You can able to create database using your azure storage container as well.

image

image

image

image

Now, you have successfully created a database using bacpac file.