Setup and Configuer R Services in SQL 2016

SQL Server R Services is designed to help you complete data science tasks.  scale analysis to billions of records without additional hardware, boost performance, and avoid unnecessary data movements.

Now you can put your R code into production without having to re-write it in another language. It also makes it easy to use R for statistical computations that might be difficult to implement using SQL. At the same time, you can leverage the power of SQL Server to achieve maximum performance, using features such as the in-memory database engine and column store indexes.

Here you will find detailed steps how you can install and configure R services and run your first code .

1. launch the Setup of SQL Server
2. Go to Installation clickable link in the left pane
3. Click at the first link the right pane as below screen shoot shows

 4. on the feature selection page select these options

•  Database Engine Services
•  R Services (In-Database

5. On the page, Consent to Install Microsoft R Open, click Accept.
6. if you are using a server doesn't connect to internet you will be asked to provide the location of R   packages you can download from here ( https://go.microsoft.com/fwlink/?LinkId=761266&lcid=1033  https://go.microsoft.com/fwlink/?LinkId=735051&lcid=1033 )
7. point to the location of the files downloaded in step 6 and then press next
8. installation progress started
9. you should now successfully installed R service


10. connect to the instance where the R service installed
11. Run the following T-SQL Code to enable
Exec sp_configure  'external scripts enabled', 1 
Reconfigure  with  override
12. Restart the SQL Server Service (DB Engine)
13. the instance is ready to run the first R code
exec sp_execute_external_script  @language =N'R',   
@script=N'OutputDataSet<-inputdataset br="" nbsp="">@input_data_1 =N'select 1 as hello'   
with result sets (([hello] int not null));   
go  

Enjoy :) this is the output of you first R' code






 

“Power Query” Get results in few clicks (with example-Mobile Banking Statistics)

only 2 clicks...

1. Load the query from the online search
2. visualize your data as you like .

Enjoy :)

 

Traditional RDBMS VS NoSQL VS MapReduce (Technology attributes)

Attribute	Traditional RDBMS	NoSQL	MapReduce
Data Size	Gigabytes	Gigabytes	Petabytes
Access	Interactive & Batch	Interactive & Batch	Batch
Updates	Read\Write oftentimes	Read\Write oftentimes	Write once read many
Structure	Static Schema	Object types	Dynamic schema
Integrity	High	High	Low
Scaling	Nonlinear	Nonlinear	Linear

SQL Server 2008 SP4 and 2008 R2 SP3 have been relased

SQL Server 2008 Service Pack 4
http://www.microsoft.com/en-us/download/details.aspx?id=44278

Microsoft® SQL Server® 2008 R2 Service Pack 3
http://www.microsoft.com/en-us/download/details.aspx?id=44271

Missed to regularly purge MSDB!! , what you have to consider prior to start ?

In the last couple of days I had to deal with these number for a MSDB db hasn't purged since 3 years !!!! yeah 3 years , so I had to do many tasks out of the normal tasks ,

let me give you brief about the figures

1. backupFile --- 10 Million rows
2. backupFilegroup --- 6 Million rows
3. backupmediafamily--- 4 Million rows
4. backupmediaset--- 4 Million rows
5. backupset--- 4 million

the total size of the MSDB is 11 GB ,

the fact is , if you start purging the historical backup data you will not be able to perform any type of backup operations for hours if it hasn't take days , in addition to growth of the log file of MSDB dbs , so I had a practical experience I would like to share to do so in a very smooth way as following :

1. extract the whole data in the list of tables above to another SQL instance plus these tables

• restorefile
• restorefilegroup
• restore history

2.apply missing indexes

 Create index IX_backupmediaset_media_set_id on backupmediaset(media_set_id)
go

 

Create index IX_backupmediafamily_media_set_id on backupmediafamily(media_set_id)
go

 

CREATE NONCLUSTERED INDEX [IX_pro_Del]

ON [dbo].[backupset] ([database_name])

INCLUDE ([backup_set_id])

GO

 

CREATE NONCLUSTERED INDEX []

ON [dbo].[backupset] ([database_name])

INCLUDE ([media_set_id])

GO

 

last step start delete per database
EXEC sp_delete_database_backuphistory 'you database Name'
GO
once you reach a reasonable number of transactions in above listed tables you can get back to normal operation and delete by date

what you have done on the test instance can be applied to your production without any interruption to your operation (backup and  size of log file)

let me know if anything goes wrong ... Enjoy :)
 

SQL Server perfromance Counters

What's new for PerformancePoint Services (SharePoint Server 2010)