03/27/2018
by Marlon Ribunal
Comments Off on Getting Things Done With SSMS

Getting Things Done With SSMS

As a SQL Server pro, I spend most of my productive time in the SQL Server Management Studio (SSMS). I am currently using the latest version as of this writing, which is v17.6.

I set it up in different configurations according to my taste at any given time. After so many iterations of my preferences which slowly adapted to my working style, I think I found the “perfect” setup that suits me and the way I work.

I’d like to say that my setup is minimal and practical. It only contains settings and windows that I normally need in a typical day.

SSMS Environment Setting

For the general setting, I preferred the Light color theme. It looks clean and, well, light in a sense. It’s easy to focus on the working space where my eyeballs need to zoom in. There are so many interruptions around the working space either in the office or at home (I work from home 2 days a week). I want tools that are minimal and simple.

For the theme of the workspace, I prefer Aaron Bertrand’s (b | t) Dark Theme with Normal Fonts. I am wearing prescription glasses so I want a theme that’s easy on the eyes.

Registered Servers

Do you find it annoying logging into different servers and forgetting passwords most of the time? I do. I jump from one server to another all throughout the day. And remembering passwords is hard. That’s why I find the Registered Servers windows to be useful.

If I need to run a quick query against any of the servers, I’d just do a right-click on the server list and select Query which opens up a new Query Editor Window that is already connected to the server. No need to enter username and password to connect. That is because the credential is saved when I registered the server.

The added benefits of the Registered Servers Window is that you can backup the setting or share the configuration. I recently upgraded from SSMS v16 to v17x. There is no direct upgrade to v17.x so I have to install the newer version separately. I didn’t have to rebuild all the server one by one, I simply exported the setting from the older version to the newer version.

Object Explorer

The perfect pair for Registered Servers window is the Object Explorer. From the Registered Servers, I just do a right-click on the server and select Object Explorer. Again, there is no need to enter the username and password. I guess this is a common window. I placed it just below the Registered Server window just so I can quickly navigate between servers conveniently. These two windows are placed on the left-hand side of the editor.

Solution Explorer and Template Browser

On the right-hand side are the Solution Explorer and Template Browser windows which are docked on top of each other. The Solution Explorer contains scripts that I either wrote or harvested from the interwebs – scripts that I use day in, day out. The Template Browser is useful for when you forget the syntax of an object.

What are your setting preferences to maximize your productivity in SSMS? Share them in the comment below!

03/20/2018
by Marlon Ribunal
1 Comment

Identify R Package Dependencies Using miniCRAN

Installing R packages in SQL Server 2017 is a breeze with the stored procedure sp_execute_external_script. But so far, it is limited to installing one package at a time. If you have an R Package with dependencies, you have to install those in a separately.

Here’s an easy way to check for package dependencies using the package miniCRAN.

Using RGUI.exe

Find RGUI.exe in C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\R_SERVICES\bin\x64. Run the executable as an administrator (Right-Click & Select “Run as administrator”).

If you want to add the new package to the default library, you have to tell the installer exactly that by using the lib argument. I previously installed R and its environment in my laptop. Without specifying a library, R would have installed the package in the old default library. See the list of my library paths below (.libPaths())

install.packages("miniCRAN", lib="C:\\Program Files\\Microsoft SQL Server\\MSSQL14.MSSQLSERVER\\R_SERVICES\\library")

 

Then you should get a message like this one:

trying URL 'https://mran.microsoft.com/snapshot/2017-05-01/bin/windows/contrib/3.3/miniCRAN_0.2.7.zip'
Content type 'application/zip' length 1025529 bytes (1001 KB)
downloaded 1001 KB

package ‘miniCRAN’ successfully unpacked and MD5 sums checked

The downloaded binary packages are in
C:\Users\Marlon.Ribunal\AppData\Local\Temp\Rtmp4UPMso\downloaded_packages

You can find all your library paths with the .libPaths() function.

> .libPaths() [1] "C:/Users/Marlon.Ribunal/Documents/R/win-library/3.3" [2] "C:/Program Files/Microsoft SQL Server/MSSQL14.MSSQLSERVER/R_SERVICES/library"

Then we can double check real quick if the package actually made it:

sp_execute_external_script @language = N'R',
 @script = N'OutputDataSet <- data.frame(installed.packages())';

 

Then

> library("miniCRAN")
> pkg <- "ggplot2"
> pkgDep(pkg)

Results:

 

You may not need all the listed packages. Check the web page for the package you’re installing for the info.

03/13/2018
by Marlon Ribunal
2 Comments

Getting Started With R In SQL Server 2017

Prior to SQL Server 2016, running R on Microsoft platform required an installation of the R Language and its environment and an external IDE to write R codes. Then came the Microsoft R Server and its own flavor of R, the Microsoft R Open. With SQL Server 2016, and now 2017, Microsoft has brought R (and Python) closer to the database engine – SQL Server in this case.

I have a SQL Server 2016 instance that I upgraded to SQL Server 2017 with In-Database Machine Learning. I was not going to reinvent the wheel here, hence the upgrade.

Note: If you’re upgrading SQL Server 2016 Reporting Services to the 2017 version, the SQL Server 2017 RTM installer does include SSRS. It actually uninstalls the service. There is now a separate installer for SSRS 2017. So, you have to plan your SSRS migration prior to the upgrade.

Here are the general steps to install or upgrade an older version of SQL Server, 2016 in this case, to the 2017 version.

Let’s start with the SQL Server Installation Center. Select Upgrade from a previous version of SQL Server because that is what we’re doing here – upgrade from 2016 to 2017.

After  entering the product key, accepting the license terms, and  selecting the instance to upgrade, the installer asks you to confirm the separate migration of the SQL Server Reporting Services

Also, please take note that the SQL Server 2017 RTM installer does not allow changing features. So, it installs both the In-Database Machine Learning Services and the Standalone Machine Learning Server which you can access via Microsoft R Client.

The installer includes Microsoft R Open which is a good thing because you do not have to install and configure it separately.

Configure the instance, Service Accounts, and Full-Text. Check the Summary, then hit Upgrade

 

Before you can run R in SQL Server 2017, you need to enable external script to allow the sp_execute_external_script system stored procedure to run. This is not on by default.

EXEC sp_configure 'external scripts enabled', 1;
RECONFIGURE WITH OVERRIDE;

Once that is set, let’s execute the obligatory Hello, World! in R + TSQL on SQL Operations Studio.

EXEC sp_execute_external_script

@language =N'R',

@script=N'OutputDataSet<-InputDataSet',

@input_data_1 =N'SELECT ''Hello R From TSQL'''

WITH RESULT SETS (([HelloR] VARCHAR(100) notnull));

GO

Enjoy! I hope to write more about R and Machine Learning in the future posts.