Step by step instruction on installation of SQL Server 2016 RC3
Following the download of my ISO file from my earlier article, I am going to show you the step by step instructions on installation of SQL Server RC3.
Step 1 is to run the exe file and you get to the Planning tab. Below screenshot, shows the options available in next Installation tab. You can then click on first option which is “New SQL Server stand-alone installation or add features to an existing installation”.
Step 2: Here you have option to evaluate the free edition or enter the product key that you purchased already. Here I chose the Evaluation which is the default screen and then I clicked on next.
Step 3: In the License Terms screen, you have full terms that you can read through to accept the license from Microsoft before start installing.
Step 4: I got error message in the product updates section, as I did not had internet connection in my virtual machine as I had problems in bridging the connection during this setup process. You can click on “Check again” button if you have got the same error screen and still have the internet connection. Then you can click on Next or you can do the product updates later and you can directly click on Next button.
Step 5: Then the system starts installing the necessary setup files and rules. In my below screen you can see the warning messages that I got. As long as it is not failing, we are good to go. So click on Next.
Step 6: Here you will get the options to choose the features. As I am doing main testing on R, SSRS, Polybase, Analysis services, I have ensured to check them all here. You can check/uncheck based on what you need to install.
Step 7: Here I got one task failed for not having Oracle JRE 7 Update 51 to support the polybase installation. After installing this seperately, I was able to continue my installation. Note: till this setup is not installed, the Next button won’t be enabled. Once I have this installation done, I made sure to click on “Re-run” button to test the latest and then this enables me to click on Next button. Here, no need to cancel and then no need to go through all above 6 steps.
Step 8: Then I get the option to configure the name of the instance and I chose it to reflect the RC3 here.
Step 9: As this is the setup in my standalone laptop, I chose the SQL Server as standalone polybase enabled instance.
Step 10: In Server configuration section, it shows all the relevant account names that will be created and the type of startup services (Manual or Automatic). When it is set to automatic, this will then start the services whenever you system is booted. If it is Manual, then you have to start it manually via the services or configuration manager.
Step 11: In the database engine configuration, I have chosen to opt for Mixed Mode to keyin my preferable password, and this also helps me to recover the instance if my current user gone cranky. Also I have added the current user YSMUser1 from the “Add Current User” button. I also have option to choose the data directories, tempdb and filestream.
In here, you also have the new feature of trying to have more than one tempdb and you can get it configured in this place.
Step 12: In Analysis Services Configuration tab, I have chosen to use the Multidimensional and Data Mining server mode and also added the current user to get admin rights.
Step 13: In reporting services configuration, I then have option to choose whether to install and configure or just install only. You still can configure at the later stage, however in regards to Reporting services, I always choose to get it configured as well as you can see below.
Step 14: For the distributed replay controller, I again choose my current user to give the admin permissions to manage it.
Step 15: Then you get to the place where you have option to choose where your controller name, working and result directories should be placed. I have not made any changes here and left it to reflect the default installation folder.
Step 16: This is very new specifically for SQL Server 2016 and you have to accept to download Microsoft R Open and install and also to agree to accept patches and updates for the software. This feature enables us to work on R side from Microsoft itself.
Step 17: I then get this screen to choose the installation path for the Microsoft R Open and Microsoft R Server.
Step 18: I have provided below install path for Microsoft R Open and Microsoft R Server to be installed.
Step 19: This is the last part where you can see the summary of installation on what necessary information you have provided to verify before installation. You still have option to go back to any configuration and make changes and then click on Next.
Step 20: This is the final screen where it says the installation was successful and advise to restart to complete the installation process.
Have good findings in your SQL Server 2016 journey.
R Plots in SQL Server 2016
As we know, Microsoft related the latest version of SQL Server 2016 RC3, download it here. From RC2, we no need to install R components separately. Actually, R database and standalone server both are installed as part of SQL Server 2016 installation itself.
It makes the R integration with SQL Server 2016 is easy.
As part of the integration, we just need to enable the external scripts and start using the R scripts inside SQL Server 2016.
Creating a plot in R console is easy, just write a code for plot and the plot will be displayed in the new window. In SQL Server 2016, we need to store the R script result in VARBINARY and then need to convert to an image file in reporting or using BCP command line tool.
I have provided the steps to get the image (plot) from R scripts in SQL Server 2016.
Solution 1:
Step 1: Create a sample database and table. Insert some values into the table.
Step 2: Do the modification on the table name and column name and run it.
EXEC sp_execute_external_script
@language = N’R’
,@script = N’ df <- inputDataSet;
image_file = tempfile()
jpeg(filename = image_file, width=500, height=500); #create a JPEG graphic device
hist(df$Values);
dev.off();
OutputDataset <- data.frame(data=readBin(file(image_file,”rb”),what=raw(),n=1e6));
‘
,@input_data_1 = N’SELECT * FROM MyValues;’ –Provide your table name
,@input_data_1_name = N’inputDataSet’
,@output_data_1_name = N’OutputDataset’
WITH RESULT SETS ((plot varbinary(max)));
Step 3: Make sure that you are getting an varbinary result as a output.
Step 4: Wrap the above code within stored procedure, like below and check the result again.
CREATE PROCEDURE Sp_Test1
As
EXEC sp_execute_external_script
@language = N’R’
,@script = N’ df <- inputDataSet;
image_file = tempfile()
jpeg(filename = image_file, width=500, height=500); #create a JPEG graphic device
hist(df$Values); #column name
dev.off();
OutputDataset <- data.frame(data=readBin(file(image_file,”rb”),what=raw(),n=1e6));
‘
,@input_data_1 = N’SELECT * FROM MyValues;’ –Provide your table name
,@input_data_1_name = N’inputDataSet’
,@output_data_1_name = N’OutputDataset’
WITH RESULT SETS ((plot varbinary(max)));
Step 5: Use below bcp command and convert the Varbinay into jpeg format.
bcp “exec Sp_Test1” queryout “Test.jpg” -S <server name> -d <database name> -U <user name> -P <password>
Step 6: Verify the plot in a above image.
Solution 2:
Step 1: You can create a table and insert this varbinary result and can use in SSRS report with image option.
Step 2: Verify the plot.
Solution 3:
We can export the result as an image file without using bcp tool itself.
Step 1: Do the following changes in the script. Replace the tempfile() with the exact path where you want to store the plot image.
Step 2: Run the script and check the above specified location. Image file will be available with plot.
Azure–Billing Alert Service
The most expected feature in Azure, available for preview.
Yes, we have been expected an alert for azure credit usage. It is available now. It is in the preview so it will not be available by default, you have to activate it by going through “Preview Feature”
Follow the below steps to activate it.
Step 1: Login with your azure subscription email, and visit the following page.
https://account.windowsazure.com/Subscriptions
Step 2: Look for “preview features” option on the top and select it.
Step 3: Search for “Billing Alert Service”, out of all the listed preview features. Click “try it now” link and this will make you in the queue. The Azure team will look for your request and will activate it for you. It may take at least one week to get activated.
Once this feature is active, you can able to set up an alert for your usage as like below.
The option will look like below,
We can create up to 5 alerts and at present only two alert types are available, “Billing Total” & “Monetary Credits”
Billing Total – An alert is sent when subscription spending exceeds the threshold.
Monetary Credit – An alert is sent when monetary credits drop below the limit.
Download and install SQL Server 2016 RC3
You can now download the SQL Server 2016 Release Candidate 3 from TechNet Evaluation Center. In the below screenshot, I have chosen to download it as ISO copy. This will enable me to attach it as disk drive in my Hyper-V Virtual Machine.
Based on few browser plugin issues and local settings, the download may not start immediately. Hence you can click on Manual download again in case if the above chosen ISO file does not download.
I am looking to find if all the previous issues that I faced with previous releases have been fixed in this new Release Candidate 3 or not.
Have a good journey in this SQL Server 2016 as we have now come to the last RC3 before we get the full SQL Server 2016.
Manage Azure Storage Account using Azure CLI
We can manage storage account through Azure Portal or PowerShell Scripts.
This article will explain you the commands that you need to use inside Azure CLI to manage storage account.
Download the Azure CLI in following link and choose respective installer package.
Install it on your machine. if you are working with Azure and if you had installed different tools to interact with Azure, you may have different command line tools in your machine. To find the recently installed one, just search for “Microsoft Azure Command Prompt”.
You will see a tool like below,
#First login into Azure using below command
azure login
You need to open the following link in your browser, https://aka.ms/devicelogin and provide the given code to authenticate.
If you have multiple subscription, then you can able to choose the default one.
#List out all the subscriptions
azure account list
#To set the default subscription
azure account set “name of your subscription”
#Create a storage account on the selected subscription
azure storage account create <storage account name>
Above script will ask you to provide two values, “Account Type” & “Location”. Just provide the numbers of the respective information.
To create a containers or all other storage services, we need to fix the created storage account details in environment variable so that when we run a further commands, it will consider the correct storage account. If you missed to set up environment variable then you will end up with following error.
Azure has command called “export” which is used to hold your storage connection string, just like a variable but it is not working as expected. If you try like below,
export AZURE_STORAGE_ACCOUNT=<account_name>
You will get below error. So we can follow different approach to have environment variable.
‘export’ is not recognized as an internal or external command,
operable program or batch file.
#Get the connection string of storage account
azure storage account connectionstring show <storage account name>
#Set Azure Connection string as environment Variable
SET AZURE_STORAGE_CONNECTION_STRING=<connection string>
#Create a container
azure storage container create <container name>
#Upload the blobs
azure storage blob upload <Path> <container name> <blob name>
Example:
azure storage blob upload E:\Encryption.png logs myblob
#Download the blobs
azure storage blob download <container name> <blob name> <local path>
Example:
azure storage blob download logs myblob E:\log.png
#Delete blobs
azure storage blob delete <container name> <blob name>
Example:
azure storage blob delete logs myblog
SQL Server 2016 RC2–Enhancements in R Integration
Microsoft released the latest version of SQL Server 2016 RC2 which has many enhancements. This article exploring the enhancements related R integration in SQL Server.
Before RC2, we need to install SQL Server 2016 prior versions and then R related components separately. This scenario has been changed, We can install R services or standalone R server through SQL Server 2016 RC2 installation itself.
Look at the below changes in the installation center.
New feature to create a standalone R Server.
Confirmation to install Microsoft R Open.
FREE Technical Data Seminar in London
Hi Everyone. I am very excited to announce the DAGEOP’s first FREE DATA SEMINAR programme now available for you to attend and enjoy the sessions from world renowned speakers. Please visit this link to get yourself registered and I have 3 sessions frozen already and awaiting for the final one. I will get back to you with final agenda once that is confirmed.
You can find more information on this from http://www.dageop.com/Events/DaGeopsDataDay.aspx
Also provided you the agenda that is available as of now. Don’t forget to register as this seating is limited.
Stretch in SQL Server RC0
SQL Server Release Candidate 0 version has been released. We have been discussed about stretch database in CTP 3.3. There are many improvements in RC0. This article is about, how to enable the stretch in RC0.
Go through Stretch in SQL Server 2016 CTP 3.3.
Steps to enable the Stretch in RC0 Version,
Step 1: Right click on your database and go to Tasks->Stretch->Enable
Step 2: Choose the tables you want to stretch.
Step 3: Provide your Azure subscription to configure SQL Server and database for stretch.
Step 4: Set up master key for your database.
Step 5: Provide your IP, in case if you have configured.
Step 6: Configuration process started to enable stretch for database and table.
As you can see, there are some difference in steps to configure when compare with CTP 3.3 version. There is no changes on Monitoring the stretch.
Enable Stretch for Tables
Previously, we need to select Reconfigure option to add new tables to stretch but that “Reconfigure” option has been removed. Now, we have “Enable”, “Disable” & “Monitor”.
To add new tables to stretch, follow the below steps.
Choose the new table to add into stretch. Right click –> Stretch –>Enable
Disable Stretch
To Disable the stretch for the database, we need to disable the stretch for tables first. We don’t have option to disable in one short if multiple tables are in stretch. Need to manually disable all the tables from stretch.
If we try to disable stretch for database without disabling tables then below message will be appeared.
There are two options in disable on table,
1. Bring data back from Azure
2. Leave data in Azure.
Once stretch is enabled for a table, data migration will be initiated from local to azure through batch process. If we decided to disable the stretch then make sure whether we want to bring data back to local or keep them on azure it self. Based on that choose any of the above option.
Note: The table in Azure database will not be deleted by process, you need to delete it by going to azure management portal.
If you are selected “Bring data back from Azure” then below message will be prompted.
Once disable the stretch for all your tables, then disable the stretch on your database.
