SQL Server in Red Hat Linux
This post explains you in detail (step by step) about how to install SQL Server and SQL CMD tools in Red Hat Enterprise Linux 7.2.
Azure Virtual Machine Template “SQL Server vNext on Red Hat Enterprise Linux 7.2” has been used for this demo.
Go to Azure Virtual Machine and click “Add”, it will open a window where you can choose the SQL Server vNext on Linux template.
Check the information on the below page as it has the command to configure and start SQL Server.
Configure the VM as usual, check the “Authentication type”. It has two options. I am going to choose Password for this demo.
Fill the other details and create a virtual machine.
It will take some time to configure the Linux machine. Once the deployment is done, then you can access the machine.
Since this is Linux machine, Azure gave us a connection IP address. I am using “PuTTY” (command line tool) to access this machine.
Download and install the PuTTY Software from here.
Once installed PuTTY, open and pass the IP address and click Open. You can save the host as new session and can load and open for next time.
Login with the username and password that you defined while creating the virtual machine.
Enter the below command to install and configure SQL Server.
sudo /opt/mssql/bin/sqlservr-setup
It will ask password to proceed further and also need to confirm the license.
Next step is to set up the SA admin password for SQL Server. Setup will be completed after this step.
Check the status of the service.
systemctl status mssql-server
As we need to run the SQL scripts in command line, we need to install the SQL CMD tools. To install, you need to go to the root directory.
#Install SQL Cmd tools
sudo su
Run the below commands to get the file and exit from root.
curl https://packages.microsoft.com/config/rhel/7/prod.repo > /etc/yum.repos.d/msprod.repo
exit
Run the below command to install
sudo yum install mssql-tools
Once the tool is successfully installed. You can see the success message.
Open command to start type the SQL commands.
Pass the SA credentials. We are going to access with SQL Server authentication mode.
sqlcmd -S localhost -U SA –P ‘SA Password’
Type the SQL Script and end with GO.
Happy Learning.
Access Azure Virtual Machine Applications Outside of Network
This article explains that how you can access virtual machine applications outside of network with the help of DNS.
For example,
Consider you have installed SQL Server on Azure Virtual machine and created few databases on the server. If you want to access those databases on your local machine, then you need to follow the below steps.
- DNS Configuration in Azure Portal
- Enable the TCP port with 1433 in Azure portal
- Enable the TCP 1433 in virtual Machine
- Create a SQL Server login and enable the mixed mode authentication
Once you have configured all the above steps, you can access your databases on another machine in SQL Server Management Studio.
Refer the below link to know in detail about the above steps.
PowerBI Reports in SQL Server Reporting Services
Recently, Microsoft released a Technical Preview of PowerBI in SQL Server Reporting services. It has opened a door to create and deploy PowerBI reports within the Reporting services.
This will be a great feature that who are all searching for great report representations in SQL Server Reporting services itself.
This article illustrates, the new look and feel of SQL Server Reporting Services portal and diverse approaches to publish the PowerBI reports.
As we have PowerBI Desktop tool from PowerBI Team, we need to use this tool to develop the PowerBI Reports. It means, we need to use two different tools for developing and publishing reports, i.e. regular SSRS reports from Visual Studio data tools and PowerBI reports from PowerBI Desktop.
Look and Feel
As you know, the look and feel of SQL Server Reporting Services Portal has changed from SQL Server 2016.
In this technical preview, there is a new section added at the bottom to hold all the PowerBI Reports but still we can manage inside our regular report folders.
Approaches to publish PowerBI Reports to the portal,
Approach 1:
Develop reports in PowerBI Desktop and perform “Save As” to directly publish (deploy) to portal.
Approach 2:
Save the report in local folder and upload manually to the portal.
Custom Report for SSMS
It is very easy to create a custom report and use it inside SQL Server Management Studio.
As we know, we have standard reports from Microsoft to monitor the Server and Database information.
These reports are available for instance and database level, If you are not aware of this standard reports, please check it below to access those reports.
Steps to use custom report in SQL Server Management Studio.
Just created a Report as like below,
We need to adjust the data source properties to use inside SSMS. Modify the data source like below. Once modified the data source, save the report. We will take this rdl file and attach in SSMS.
Once added the report, open and see the information.
Now, you can refresh the report and see the real time information and even you can build a report to specific database for monitoring purpose.
Happy Learning.
Statistical Functions in R
R language has many built in functions, in which statistical functions are mostly used on numeric vectors.
sum(x) – The sum of vector x
min(x) – The Minimum value of vector x
max(x) – The maximum value of vector x
mean(x) – The arithmetic mean of vector x
median(x) – The median of numeric vector x. 50% of data should be less than median and balance 50% data should be greater than median.
sd(x) – The standard deviation of vector x
var(x) – The Variance of numeric vector x
quantile(x,p) – The Pth sample quantile of numeric vector x. for example, quantile(x,.3) will tell us the value at which 30% of cases are less than value x.
summary(x) – It shows several statistics of vector x, including the above.
Practical Space,
X<-c(2,4,6,8,9,10,14,56,70)
sum(X)
min(X)
max(X)
mean(X)
median(X)
sd(X)
var(X)
quantile(X,0.5)
summary(X)
Update on Azure Stretch Database
Previously, when we configure the stretch database in SQL Server 2016, the database created in Azure and it was using one of the Azure SQL Database pricing tier (Basic, Standard & Premium). Microsoft has done recent changes in pricing tier of stretch database. Now, we have new pricing tier for compute and storage. This might resemble the same as Azure SQL Data Warehouse.
We can control the compute power based on required performance. The default compute level is 100 DSU. Compute power is charged per hour basis.
Storage of data will be charged at standard disk rates. Data storage includes the size of the stretch database and backup snapshots. All stretch database have 7 days of incremental backup snapshots.
The look and feel of the stretch database in Azure Portal.
Stretch Database – Filter
We can easily filter the rows to migrate the data to azure through T-SQL statement. Need to associate that filter function with the script to enable the data migration to azure.
This article explains the same filter concept through wizard. we can easily apply the filter with few clicks.
1. Right click your database->Tasks->Stretch->Enable
2. Choose the table and look for a column “Migrate” and click the hyperlink “Entire Table”
3. Filter the rows in the below window
Always Encrypted–PowerShell Scripts Part-2
Applying the encryption to the table column is easy with the PowerShell. Please make sure to follow the below steps before run the below scripts.
1. Connect to SQL Server from PowerShell
2. Create column master key & column encryption key – You can create by using wizard or PowerShell Scripts
3. Choose the column which you want to apply the encryption.
4. Defined the encryption type
Run the below script just continue with this script
$ces = @()
$ces += New-SqlColumnEncryptionSettings -ColumnName “dbo.Patients.SSN” -EncryptionType “Deterministic” -EncryptionKey “CEK1”
Set-SqlColumnEncryption -InputObject $database -ColumnEncryptionSettings $ces
You can append multiple encryption by adding + symbol, like below
$ces = @()
$ces += New-SqlColumnEncryptionSettings -ColumnName “dbo.Patients.SSN” -EncryptionType “Deterministic” -EncryptionKey “CEK1”
$ces += New-SqlColumnEncryptionSettings -ColumnName “dbo.Patients.BirthDate” -EncryptionType “Randomized” -EncryptionKey “CEK1”
Set-SqlColumnEncryption -InputObject $database -ColumnEncryptionSettings $ces