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.
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.
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.
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.
Issue 2: Stretch option is not enabled for table even though I enabled the stretch on database with selected tables.
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
Issue 4: Almost all the time, all the data got migrated to Azure database, in local only 0 record.
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,
make sure your working directory is correct without any extra space and also check the permission for “ExtensibilityData” directory.
Provide similar permission level as above.
Now try to run the same script in SSMS,
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.
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>
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
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.
5. Click “Create”, Azure will create and deploy DW for you.
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.
2. Provide the Azure storage account details and select the container
3. Check the Azure storage for the .bacpac file.
Once exported the database, your next step is to import into Azure SQL Server
1. Choose Your SQL Server in Azure and Import database
2. Map your .bacpac file from your storage container.
Once above steps are completed then your database will be deployed on Azure
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
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;
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;
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;
3. Granting Permission to Unmask
GRANT UNMASK TO User1;
EXECUTE AS USER = ‘User1’;
SELECT * FROM Users;
REVERT;
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;
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
Azure SQL database,
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
Script 2: Using sp_spaceused stored procedure.
Exec sp_spaceused [Users] –Table Name
