Friday, December 23, 2016
Tuesday, December 20, 2016
If you like slide shows that outline the latest with SQL Server, take a look at the SlideShare on LinkedIn. You can find it here:
What's new in SQL Server 2016 - James Serra
Monday, December 19, 2016
- Language and performance enhancements to natively compiled T-SQL modules, including support for OPENJSON, FOR JSON, JSON built ins as well as memory-optimized tables support for computed columns.
- Improved the performance of updates to non-clustered columnstore indexes in the case when the row is in the delta store.
- Batch mode queries now support "memory grant feedback loops,” which learn from memory used during query execution and adjusts on subsequent query executions; this can allow more queries to run on systems that are otherwise blocking on memory.
- New T-SQL language features:
- Introducing three new string functions: TRIM, CONCAT_WS, and TRANSLATE
- BULK IMPORT supports CSV format and Azure Blob Storage as file source
- STRING_AGG supports WITHIN GROUP (ORDER BY)s
- Most of the improvements in SQL Server 2016 Service Pack 1 are now available in SQL Server on Linux, with the exception of a few features such as Polybase
How to Update - See: Update SQL Server on RHEL - CentOS
Sourced from the Microsoft support documents:
1) No restart/reboot after running the update command
2) Microsoft's posted command uses yum update, not yum upgrade.
Monday, December 12, 2016
After installing SQL Server Management Studio for vNext, the Configuration Manager no longer opens, with a message similar to the following:
Steps to correct:
- Search for the file: sqlmgmproviderxpsp2up.mof
You will probably find it in the folder:
C:\Program Files (x86)\Microsoft SQL Server\140\Shared
- Run the following command, adjusted using the file location from the search above.
C:\WINDOWS\system32>mofcomp "%programfiles(x86)%\Microsoft SQL Server\140\Shared\sqlmgmproviderxpsp2up.mof"
Based on Microsoft support - kb966013:
Thursday, December 8, 2016
You can read the full posting at: Availability Groups: More Planned Downtime for Less Unplanned Downtime
Tuesday, November 29, 2016
Monday, November 28, 2016
Once connected, our Locate Backup File pop-up takes us to the C:\var\opt\mssql\data folder. Interestingly, on our Linux machine, the folder is simply addressed as: /var/opt/mssql/data. No leading C:\, and the slashes are reversed. The wizard pop-up looks like this:
For this preview release, the location wizard limits our access to various files and folders. It's not good form, but we'll move the file to the /data/ folder.
Copy / Move
Here we move our backup file to the folder where SSMS is expecting it to be on our Linux machine. For production, you really will not do this, but just so we can see our backup file display in our wizard, we'll place a copy of the backup file in the default data folder used by SSMS.
But my Backup file is still not visible in the wizard!
- Permissions. Once we changed access permissions on our backup file, our wizard could access the backup file.
- Don't use the folder: /var/opt/mssql/data. This folder holds the live database files (.mdf, .ldf, etc) so it's best to create a new folder to hold your backup files. The location wizard will let you type in a different folder directory.
Change Permissions on Test.bak
- sudo su ( and enter root password)
This brings up the file viewer as root. Navigate to /var/opt/mssql/data, and then adjust the file permissions:
Tuesday, November 22, 2016
Operating system error 2(The system cannot find the file
- Home folder - Allow Others to Access files
- Downloads folder - Allow Others to Access files
- backup file - Allow Others to Access files
$ sudo chown mssql /home/user/Downloads/AdventureWorks2012.bak
$ sudo chgrp mssql /home/user/Downloads/AdventureWorks2012.bak
Also see how to RESTORE to SQL Server on Linux: Restore a SQL Server database to Linux.
You can find the CentOS images here: https://www.osboxes.org/centos/
You can also find many at Virtualbox: https://virtualboxes.org/images/centos/
The gcc utility was not found. If the following module compilation fails then this could be the reason and you should try installing it.The headers for the current running kernel were not found. If the following module compilation fails then this could be the reason. The missing package can be probably installed with yum install kernel-devel-3.10.0-327.el7.x86_64Building the main Guest Additions module[FAILED] (Look at /var/log/vboxadd-install.log to find out what went wrong)Doing non-kernel setup of the Guest Additions[ OK ]Press Return to close this window...^CSignal caught, cleaning upPress Return to close this window...
Fortunately, the answer has already been posted.
You can find it here: How To Install VirtualBox 5 Additions on CentOS 7.
In summary, the steps were:
- $ sudo yum -y update
- $ sudo yum install epel-release
- $ sudo yum install dkms kernel-devel
- install GuestAdditions from CD
- via auto-installer, or
- $ cd /run/media/<yourid>/VBOXADDITIONS_x_x_x_xxxxx
- $ sudo ./VBoxLinuxAdditions.run
Monday, November 21, 2016
( see Use SSMS to connect to Linux SQL Server v.Next
and Use SQuirrel SQL to Connect to Linux SQL Server v.Next )
Overall, the process was straight forward, especially since we were using SSMS. File and folder permissions was the one area that caused us the most problems.
First, back up the database
We used an older version of AdventureWorks for this task. We explicitly used the NO_COMPRESSION option for this demo - to keep it simple.
BACKUP DATABASE [AdventureWorks2012] TO DISK = N'H:\Data\AdventureWorks2012.bak' WITH NOFORMAT, NOINIT, NAME = N'AdventureWorks2012-Full Database Backup' , SKIP, NOREWIND, NOUNLOAD, NO_COMPRESSION, STATS = 10, CHECKSUM GO
Next, change Permissions to Read-only
File and folder permissions can fail the restore, so if you use another folder, just make sure all folder and file permissions allow access.
Test the file HEADER and FILELIST
USE [master] RESTORE HEADERONLY FROM DISK = N'C:\home\user\Downloads\AdventureWorks2012.bak' USE [master] RESTORE FILELISTONLY FROM DISK = N'C:\home\user\Downloads\AdventureWorks2012.bak'
If you are not sure of the actual Ubuntu file location, use properties:
Swap backslash/forward slash
Restore Database to Linux
USE [master] RESTORE DATABASE AdventureWorks2012 FROM DISK = N'C:\home\user\Downloads\AdventureWorks2012.bak' WITH FILE = 1 , MOVE N'AdventureWorks2012_Data' TO N'C:\media\user\Data\SQLServer\AdventureWorks2012_Data.mdf' , MOVE N'AdventureWorks2012_Log' TO N'C:\media\user\Data\SQLServer\AdventureWorks2012_Log.ldf' , NOUNLOAD, STATS = 5 GO
-- We also used this same driver to use Oracle's SQL Developer to connect to SQL Server vNext for Linux as well.
Friday, November 18, 2016
- see SQL Server on Linux is now in public preview)
Command lines are great for production, but for investigation, a GUI interface is better. So, can we connect using SQL Server Management Studio? The answer is yes, but with some qualifiers.
- Use a current production release of SSMS?
We used a slightly outdated SSMS, version/release 13.0.15700.28. The current release is build number : 13.0.16000.28 (lised as release 16.5). With our older version, we were able to make a connection using a standard connection using the IP for the server name along with SQL Server Authentication. While we could make a connection, we opted to switch to the new v.Next preview edition.
- Use a preview edition of SSMS, updated for SQL Server v.Next, version 17.0 RC1
You can down a preview edition on the main SSMS download page: Download SQL Server Management Studio (SSMS)
Wednesday, November 16, 2016
- stored procedures
- user defined functions
For more details see: SQL Server 2016 Service Pack 1 (SP1) released !!!
What was not upgraded? Scale and high availability.You can read all the details
at: SQL Server 2016 Service Pack 1 (SP1) released !!!
plus: Features Supported by the Editions of SQL Server 2016
- Red Hat Enterprise Linux 7.2 (we tested, it works with CentOS )
- Ubuntu Linux 16.04
- SUSE Linux Enterprise Server v12 SP2 (listed as in-process )
We've started to take a look.
 CentOS release 7 (1511) is listed as based on RHEL 7.2.
Reference: https://wiki.centos.org/Download. No guarantees, but we tested it, and for us it works.
Thursday, November 10, 2016
See also: Setup SSRS 2016 with support for Power BI Desktop in VirtualBox for how to
get your own copy as a virtual machine on Azure.
With our local virtual machine up and running, we wanted to be able to create a new Power BI Desktop report on our own workstation, and then upload the report to the preview SSRS server. To do this we had to take a few extra steps to get our VM ready for external connections.
On the SSRS Server:
- Enable TCP/IP: on our preview VM, use SQL Server Configuration Manager and then enable TCP/IP. You'll need to stop/restart services.
- Open ports to allow external access to SQL Server. See: Script to open ports for SQL Server
In SSRS (this is optional, depending on the user to log in)
- SSRS Preview: On the desktop, open the link that takes you to the SSRS portal. Naviage to the security section, and add the user you will use to connect to SSRS, and enable the appropriate roles. (or add to the server Administrators group)
Optional: or, you can just connect remotely using the admin/pw for the server when you set it up - your choice.
Connect via BrowserGet the reporting server URL and connect using a browser on your remote workstation. You can do this on the VM, but we wanted to test our ability to upload a .pbix remotely. And while we typically use Chrome, for this, we used Edge. It is actually very fast, faster than Chrome, but does not yet have a Norton plug-in for web security.
You will get a challenge/response:
Connect using the admin/pw credentials for the server, or others you setup earlier. You will get a page that looks like this:
Notice the Power BI Desktop Reports section at the bottom.
Upload a new Power BI Desktop ReportWe created several new reports using Power BI Desktop on our workstation that used the databases on our SSRS 2016/Power BI VM. Now, it was time to upload them to SSRS.
1. On the menu bar, locate the Upload button and select
2. Select your .pbix file:
This brings up a pop-up file selection window. Locate and select your .pbix file, and then select/click the Open button. Your .pbix file will go directly into the "Power BI Desktop Reports" section.
That is it. Your Power BI Desktop Report will be available for review.
[the (PBID July 2016) just tells us that we built this report with the Power BI Desktop July 2016 release - Version: 2.37.4464.461 64-bit (July 2016).
Notes, warnings and other considerations
- Version of Power BI Desktop
- What's missing
Version of Power BI Desktop.For this preview, you will need to use either the September, 2016 version, or an earlier version. The preview uses version: 2.39.4526.801 64-bit (September, 2016). The October version did not work for us. We used a July version of Power BI Desktop for our tests. If you use a new version, you will get this error:
|An error has occurred. Something went wrong. Please Try again later.|
|Unable to Open Document|
.pbix is incompatible with your current version of Power BI Desktop
Custom FoldersIf you want your .pbix file to reside in a custom folder, no problem. First navigate to your folder, and then start the upload process. If the folder does not yet have a section labeled "Power BI Desktop Reports" the upload process will first create one. Below is a new folder where the upload process automagically created the "Power BI Desktop Reports" section.
What's Missing?It's a preview, and a great start at that. Still, there are some items that are not in the SSRS preview. Let's take a look at the one's we have identified so far in our review:
- Dashboards - at least for now, SSRS only supports reports
- Natural Language Queries
- Quick Insights
Also see: Upload Power BI Desktop .pbix to SSRS 2016
Overview of Steps
- In Azure, create a new SSRS-PowerBI preview virtual machine
- Download the Azure VHD
- Create a new VirtualBox VM - without storage/virtual harddisk
- Update the VM settings and add the downloaded VHD
- Start the VM, log in, and add VirtualBox Guest Additions
- Delete your Azure virtual image and both storage items
- or pay about $5.00 a day in storage costs.
Create a new Azure SSRS 2016 with Power BI Desktop support.
Download the VHD for you newly created Azure image
Make a VHD backup. Personal choice, especially if you plan to delete the one on Azure.
Create a new VirtualBox machine
Here, create a new VirtualBox machine without a virtual harddisk. The operating system is Windows Server 2012 R2.
And then update the configuration settings for your new VM
- General - use defaults
- memory: 8+ GB,
- floppy: disable
- others: go with defaults
- Processor: 4+
- All others: use defaults
- Display use defaults
- move your downloaded VHD to your newly created VM folder. Not required, but it helps to keep the folders well organized
- attach VBoxGuestAdditons.iso to the CD/DVD
- Audio - defaults (disabled)
- Network - we use the bridged Adapter
- Serial Ports - defaults (disabled)
- USB - use defaults (disabled)
- Shared folders - we usually hold off on this until we install Guest Additions
- User Interface - defaults
Start up the VM in VirtualBox
Install VirtualBox Guest Additions
Delete your Azure VM and both storage files
More about SSRS and Power BI Desktop reports
Upload Power BI Desktop .pbix to SSRS 2016
Friday, November 4, 2016
If you'd like to read a well written, fairly comprehensive comparison between R, python and SAS, check out this posting: R, Python or SAS: Which one should you learn first?
Monday, October 31, 2016
Look no further: Hadoop Tutorial at Yahoo!.
Why Yahoo! - they were effectively the first major company to embrace and extend Hadoop, helping to make it into what it is today.
- Should I use:
- pure open source community supported CRAN version,
- enhanced Microsoft R Open, or
- R Server
- which R option, if any, supports multi-threading
- In-memory constraint
- which R option, if any, supports big data, and breaks out of the in-memory road block
Thursday, October 27, 2016
Announced on October 25, 2016 on the Power BI blog, they are releasing it into technical preview. For this first review, you'll need an Azure account. No mention of when a local, on-premises preview will be available, but still this is great news.
See: Technical Preview of Power BI reports in SQL Server Reporting Services
Video - here's a video showing Power BI against SSRS 2016
Create a modern enterprise reporting and mobile BI solution with SQL Server 2016
Can I try it out on-premises?
Ok, what you really want is to run a localized VM version. Christopher Finlan has added a posting that outlines how you can download the VM from Azure and get it up and running as a localized VM. Find that post here: How to run the Technical Preview of Power BI Reports in SQL Server Reporting Services on-prem using Hyper-V
- Readability! the secondaries are readable.
- Multiple passive secondaries
- A possible lower cost that the primary