It's a new year, 2016, and been for a while and SQL Nexus in Copenhagen

Last year, and currently, there are so much work and so little time. Am right now in Copenhagen for SQL nexus and current charging for my session Hi(gh) Availability. Slightly different format on the session than usual, and I'm going to promise to publish the demos as blog posts. That will force me to add stuff more frequently, which is good. And it will force me to plan my hours even more rigorous, which might give me my life back.

Anyways, it's a new year. And a few days ago I was told that the next version of SQL Server will be released on the 1st of June, and over the months after all goodies will be in place.

I can't wait for getting the golden RTM bits, it's going to be a super year.

Cheers,
Mattias

PS: Please check back and comment and request!

New features in Microsoft SQL Server 2016

Sitting on my train home from Microsoft Ignite 2015 in Chicago, IL, USA after 14 hours of travelling and still three hours to go. I’d like to summarize a few of all new features that we probably will get in vNext, i.e. Microsoft SQL Server 2016. At the time being there isn’t a public CTP, but to the summer we’ll probably get our hands on CTP2 which will be the first public preview and sneak peek.

For the BI folks out there in the world I’m going to say: There are investments done in Analysis Services, Master Data Services and Data Quality Services. Power BI is getting closer to On-Prem, Power Query as a Data Source for Integration Services and this is not all. Over the time forward I will elaborate and share my expressions of all the new goodies.

For the DBAs out there, and the DB Devs too, we’re getting tons of new stuff. Think about having a tool that actually persists queries and execution plans, and help us identify parameters, and easily pinpoint execution plans to explicit queries. And expose this together with graphs and statistics. This coolness is called Query Store.

And think about having a Slowly Changing Dimension type 3 alike feature built in into the OLTP engine, and having this ability transparently added to tables we need to keep track of all versions of rows, and as a time machine jump back and forward in time over the data. This is called Temporal Tables.

Is your table growing out of size and you still need to keep everything forever? Think about letting the table stretch cold data to Azure, still keeping it online, transparently and infinitely. Just having the need to have the hot and warm data in the backups, still being able to restore to any point in time have timely snapshots for your restore process. In 2016 the hybrid experience take another step and gives us the ability to stretch an On-Premise table to Azure offering you potential infinite storage.

This is just some, and more will come… I’ll keep you posted!

Over and out,

Mattias

SQL Server 2016 is not that far away... maybe...

Today is an awesome day, during the keynote here in Chicago at the Microsoft Ignite Sataya announced the upcoming and new SQL Server 2016 and that we will get a preview shortly. Read more at this blog, http://blogs.technet.com/b/dataplatforminsider/archive/2015/05/04/sql-server-2016-public-preview-coming-this-summer.aspx

And if you're in Chicago try to find me...

My sessions at tech|connect 2015 in Cyprus can be found here...

http://1drv.ms/1CsoExz

Get inspired and if you have any questions tweet me at @SoQooL

Installing SQL Server Data Tools - Business Intelligence for VS2013?

I'm just publishing some links to good to have if you are...

.Net Framework 4.5.1 (Offline Installer)
SQL Server Data Tools - Business Intelligence for Visual Studio 2013

And the tools again plus some more

Are you setting up a SharePoint-farm? You better read this!

When we're talking about SQL Server there are two settings that are commonly debated. "Maximum Degree of Parallellism" and "Cost Threshold for Parallellism". Before we get to the reason of the subject and the main message I'd like to use the moment to debunk and explain these two settings, how to locate them, and how to configure them in normal setups. With that said, anything related to SQL together with SharePoint is or isn't normal, it's all up to you... ;)

"Maximum Degree of Parallellism" or MAXDOP is the amount of parallell threads a single statement can be split up into. You usually see recommendations for OLTP-Environments to change the default value of "0", which means dynamic, to recomended value of "1". This means a single thread, and my opinion on it is actually depends. I would recommend to set it to the half amount of available cores and then look at contention, and I look at physical cores, not the logical hyper-threaded ones. Some queries might suffer from parallellism, but not all. And for that you can hint the query instead, just add "OPTION (MAXDOP 1)" at the end of the statement.

"Cost Threshold for Parallellism" is the result of the Query-cost, and if the estimated cost exceeds the cost threshold the Query Optimizer tries to parallellize the execution plan. The defaulr value "5" was originally 5 seconds, today this is an calculation of cpu time, IO and Formulas depending of which optimizer method used. This means the default value is not valid anymore, and we need a new value. I usually recommend to start out with 50 and then adjust if it is to low or high for average queries.

And now over to the main subject. Did you know that when you run SharePoint there is a requirement to set "Maximum Degree of Parallellism" to "1". This is true for other systems as well, for example System Center and Dynamics. The SharePoint actually set this automatically for you. See this capture when creating a SharePoint Web Service Application:

IF (SELECT value FROM sys.configurations WHERE name = 'max degree of parallelism') <> 1
BEGIN
    exec sp_configure 'show advanced options', 1;
    RECONFIGURE WITH OVERRIDE;
    exec sp_configure 'max degree of parallelism', 1;
    RECONFIGURE WITH OVERRIDE;
END

Being a DBA this would freak me out. But this is done by design, and we have to live with it. Remember this if you are using your data warehouse server as a database host for SharePoint, or if you share databases for other uses with SharePoint. This is an obvious choice for another instance, have a specific, dedicated instance for SharePoint. And Let SharePoint do whatever SharePoint want to with that instance. The DBA can maintain the instance as any other SQL Server instance.

Remember that Resource Governor is a perfect choice for controlling resource usage on a scenario type of level, and in SQL Server 2014 it can not only manage the cpu and memory, it also can manage IOPS.

Have Fun,
Mattias

Sources

• http://technet.microsoft.com/en-us/library/hh292622(v=office.15).aspx
• http://technet.microsoft.com/en-us/library/ms181007(v=sql.105).aspx
• http://technet.microsoft.com/en-us/library/ms188603(v=sql.105).aspx

Ok, I want Power BI but I do not know which option?

That was the question I got, and there are actually two start scenarios. I base this on the current Power BI offering from Microsoft. Please see the official site for details, http://www.microsoft.com/en-us/powerBI/pricing.aspx.
* 40% off promotion for Office 365 E3 or E4 customers. Offered through December 31, 2014.

 

1) I have an Office 365 Enterprise E3 or E4 subscription, and want to add Power BI functionality.

• You can add Power BI functionality for the discounted* rate of $20USD per user and month, standard price is $33USD per user and month. On the Buy Now site you just choose "Add-on for E3/E4 Subscribers".

2) I do not have an Office365 E3 or E4 Enterprise subscription, or do not want to use the one I have.
You can choose between two options, "Standalone" or "Standalone + Office 365 ProPlus". It all comes down to if you have the "Office 365 ProPlus" or the "Office 2013 Suite" or not, this is what I call the client tools.

• With "Standalone" you get a SharePoint Online but you need your own Office-suite. Current offer is $40USD per user and month. 
• And if you need the client tools you chose the other, "Standalone + Office 365 ProPlus". Current offer under the discount is $52USD per user and month.

After this response I got a follow-up question.

?) Do I need a Power BI license for every user?
It all comes down to what the user needs to do. Once again there are two categories.

• "Publishers" are the users that create and share Power BI content, these users need a Power BI license.
• "Consumers" are the users that consume the shared reports, and they just need a license for the SharePoint Online portal.

Is this a full Picture? Nope, this is basically the view right now and it might change over time. If you are going to Power BI I strongly recommend a session with an Expert that can evaluate your current need and help you decide on what to get or not.