Friday, November 18, 2016

Interesting SQL related links for the week of Nov 18, 2016

Here are some interesting articles I read and tweeted about this past week, I think you will like these as well. If you are bored this weekend, some of these might be good for you to read

Massive Disruption Is Coming With Quantum Computing
Next year, we may see the launch of the first true quantum computers.

The implications will be staggering.

This post aims to answer three questions:
  1. What are quantum computers?
  2. What are their implications?
  3. Who’s working on them?

New Showplan XML properties in SSMS October Release

Back in March we announced the availability of per-operator level performance stats for Query Processing (exposed in Showplan XML) with SQL Server 2014 SP2 and SQL Server 2016. However, SQL Server Management Studio (SSMS) did not expose this information, and so users had to look in the Showplan XML, in the context of the appropriate node and operator.

This caused some friction, and so we are happy to announce that in the latest (October) release of SSMS, these attributes are now readily available in the Properties window of an Actual Execution Plan, in the scope of each operator.

Microsoft signs deal to power data center entirely by wind power

You need a lot of energy to run a data center. Especially when your name is Microsoft, and you’re the biggest technology company on the planet. But this doesn’t necessarily have to be environmentally harmful.

The company just announced that it has inked deals with two wind farms, with the aim of entirely powering its Cheyenne, Wyoming data center from renewable sources.

Microsoft has contracted Bloom Wind farm in Kansas to provide 178 megawatts, and the Silver Sage and Happy Jack farms in Wyoming to provide an additional 59 megawatts.

Unable to drop a user in a database

A user called in for help because he wasn’t able to drop a user from a database. The error message is below

Msg 15136, Level 16, State 1, Line 2
The database principal is set as the execution context of one or more procedures, functions, or event notifications and cannot be dropped.

From the error, the logical starting point is sys.sql_modules because functions, stored procedures have entries in this catalog view. Additionally, it has a column called execute_as_principal_id

So I looked there but found no entries whose execute_as_principal_id is the same user id of the user this customer tried to drop. Next I started to look at source code and found out this error is raised in a couple of other places. It gave me the clue that I didn’t have to do source code research, TSQL is all I needed to solve the problem.

Connect(); // 2016

Videos from the Connect(); // 2016 event can be found here
Screenshot of some of these

SQL Server 2016 Service Pack 1 (SP1) released !!!

With cloud first strategy, the SQL Product Team has observed great success and adoption of SQL Server 2016 compared to any previous releases. Today, we are even more excited and pleased to announce the availability of SQL Server 2016 Service Pack 1 (SP1). With SQL Server 2016 SP1, we are making key improvements allowing a consistent programmability surface area for developers and organizations across SQL Server editions. This will enable you to build advanced applications that scale across editions and cloud as you grow. Developers and application partners can now build to a single programming surface when creating or upgrading intelligent applications, and use the edition which scales to the application’s needs.

In addition to a consistent programmability experience across all editions, SQL Server 2016 SP1 also introduces all the supportability and diagnostics improvements first introduced in SQL 2014 SP2, as well as new improvements and fixes centered around performance, supportability, programmability and diagnostics based on the learnings and feedback from customers and SQL community.

SQL Server 2016 SP1 also includes all the fixes up to SQL Server 2016 RTM CU3 including Security Update MS16–136.

SQL Server on Linux Documentation
SQL Server vNext CTP1 now runs on Linux! Learn about the core relational database capabilities in this release of SQL Server, and how to install, manage, secure, and develop for this new release.

What's New in SQL Server vNext

SQL Server vNext represents a major step towards making SQL Server a platform that enables choices of development languages, data types, on-premises and in the cloud, and across operating systems by bringing the power of SQL Server to Linux, Linux-based Docker containers, and Windows.

You can find all the new stuff at that page

Now pay attention to the next two links... did you noticed it was getting colder since Wednesday? That is because Hell just froze over!!!!
A funny comment on slashdot
Microsoft joins the Linux foundation, Google joins the .NET foundation. What's next? Hillary joins the Trump Foundation?

Google Cloud to join .NET Foundation Technical Steering Group

Google is pleased to be joining the Technical Steering Group of the .NET Foundation. .NET is a key component in the modern enterprise, and the Google Cloud Platform (GCP) team has worked hard to ensure that .NET has first-class support on Google’s infrastructure, including excellent infrastructure for Windows. For years, Google has offered .NET libraries for more than 200 of its cloud services. More recently, we’ve built native GCP support for Visual Studio and PowerShell.

Come again?
Microsoft Fortifies Commitment to Open Source, Becomes Linux Foundation Platinum 

The Linux Foundation, the nonprofit advancing professional open source management for mass collaboration, today announced that Microsoft has joined the organization at a Platinum member during Microsoft’s Connect(); developer event in New York.

From cloud computing and networking to gaming, Microsoft has steadily increased its engagement in open source projects and communities. The company is currently a leading open source contributor on GitHub and earlier this year announced several milestones that indicate the scope of its commitment to open source development. The company released the open source .NET Core 1.0; partnered with Canonical to bring Ubuntu to Windows 10; worked with FreeBSD to release an image for Azure; and after acquiring Xamarin, Microsoft open sourced its software development kit. In addition, Microsoft works with companies like Red Hat, SUSE and others to support their solutions in its products.

Disable CREATE OR ALTER for DDL triggers (or fix it)
I filed this bug while working on the post What's new in SQL Server 2016: CREATE OR ALTER

On SQL Server 2016 SP1 and SQL Server vNext (neither available from drop down, so I picked 2016 RTM) When executing CREATE OR ALTER with a DDL trigger, it will run fine the first time, when you execute the script again, you will get an error like the following 

Msg 2714, Level 16, State 2, Procedure safety, Line 1 [Batch Start Line 77] There is already an object named 'safety' in the database. 

So either don't allow CREATE OR ALTER when the trigger is a DDL trigger or fix it so it doesn't throw the error if you run it more than once

And to finish it of, here are some of my blog posts about SQL Server vNext and SQL Server 2016 Service Pack 1 from this week

SQL Server 2016 SP1 released, SQL Server vNext available for download

No comments: