SQL 2016 CTP

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.

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

ByHariharan Rajendran

Insights on StretchDB SQL Server CTP 3.3

As we know that, the current version of stretch DB is migrating all the rows from local to Azure database table when there is no filter predicate is applied (in GUI, there is no option for filter predicate, it is possible via T-SQL script). At one point of time, there is no records in local table.

In this situation, we can not get the correct performance while executing the query. I had a chance to see the query execution plan and did some analysis.

I have executed the script when stretch db is not enabled. I took a simple table without any index on it.

image

 

Then I enabled the stretch db and included the above table and got the below query plan. At this time, almost all the records were migrated from local to azure.

image

This was not the execution plan when I see immediately once enabled the stretch. At that time, local table has more records compare than azure.

ByHariharan Rajendran

Issues in Stretch Database–SQL Server 2016 CTP 3.3

In latest version of SQL Server 2016 CTP 3.3, we have issues in Stretch option. I have listed out the issues, hope this will be sorted out before GA (General Available).

Issue 1:  No option to move only set of rows to Azure Database. The default option is “Migrate All Rows”. Using inline function, we can apply the condition to split the records but available only through T-SQL Scripts.

image

Issue 2: Stretch option is not enabled for table even though I enabled the stretch on database with selected tables.

image

Issue 3: I have enabled the stretch for the database, there is no option to disable in GUI. We have to disable through T-SQL Script

image

Issue 4: Almost all the time, all the data got migrated to Azure database, in local only 0 record.

image

ByHariharan Rajendran

First R Script in SQL Server 2016

Follow my previous article to integrate R in SQL Server 2016, Once done then check the R script in SQL Server to make sure integration is done successfully.

Run the below script in SQL Server

CREATE TABLE Sample ([Value] int not null) ON [PRIMARY]

 INSERT INTO Sample   Values (101);

 INSERT INTO Sample   Values (102);

 INSERT INTO Sample   Values (103) ;

GO

execute sp_execute_external_script

  @language = N’R’

, @script = N’ OutputDataSet <- InputDataSet;’

, @input_data_1 = N’ SELECT *  FROM Sample;’

WITH RESULT SETS (([Value_R] int NOT NULL));

GO

When I run the script, I got below error.

“Unable to communicate with the runtime for ‘R’ script. Please check the requirements of ‘R’ runtime. STDERR message(s) from external script:  Fatal error: cannot create ‘R_TempDir’”

To avoid this type of error, check the working directory on Rlauncher.config file

In my case, I have installed in E drive “E:\Local_Install_Applications\SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Binn”

The blue highlighted path will be different if you have installed SQL Server on a different path.

Open the Rlauncher.config file,

image

make sure your working directory is correct without any extra space and also check the permission for “ExtensibilityData” directory.

image

Provide similar permission level as above.

Now try to run the same script in SSMS,

image

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

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

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