SQLServerCentral - www.sqlservercentral.com

A community of more than 1,600,000 database professionals and growing

Featured Contents

Featured Script

The Voice of the DBA

Why I am Excited about SQL Server on Linux

On January 11th, Steve Jones published an editorialNot Excited by Linux, detailing how a recent poll of this community indicated a general lack of enthusiasm for the ability to run SQL Server on Linux. While that response doesn't exactly surprise me, I can say that I am definitely excited about SQL Server on Linux. In fact, I think it is a very long time overdue; I think it is sad that it took this long for Microsoft to head in this direction, but better late than never (though 10 years ago would have been preferable).

The three advantages I see are (the first two of which were mentioned by Steve in his post):

  1. More options for people to choose as a data platform. Some companies, and especially small, private projects / startups, simply aren't going to host on Windows, for a variety of reasons. For example, Linux hosting is typically cheaper than Windows hosting (just look at the Linux vs Windows price difference for Amazon EC2 instances: https://aws.amazon.com/ec2/pricing/reserved-instances/pricing/ ).  And because of that, SQL Server was never even an option.

    I use Linux hosting, and PHP / MySQL are offered by most (maybe all?) hosting companies. While I do like PHP, if I had the choice, I would certainly choose SQL Server over MySQL. It has just never been an option. I would be shocked if there weren't plenty of others out there that feel the same way, and who might not be in the SQL Server camp right now (and hence not going to respond to such a survey if they never see the survey) but will try it out once available for Linux and might really like it. And, once it is being used on projects being hosted on Linux, it is likely that some of those might someday transition over to a Windows OS (or at least from Express Edition to Standard). PHP has been running on Windows for many years, so that should be easy enough ;-).
  2. More job opportunities for those of us who work with SQL Server. Yes, there will be some amount of learning curve when it comes to learning a new OS (for those who have never worked with Linux / UNIX), but it's really not a very steep curve given that the OS's aren't nearly as different today as they were 20 years ago, especially with the introduction of PowerShell. But more SQL Server installations means more opportunities for Database Administrators and Database Developers alike.
  3. It's a good move for Microsoft because the OS is becoming less and less important in this internet-driven world; the app server and database are more important ¹. If SQL Server and .NET can run on more than just Windows, that gives Microsoft a much larger playing field. And, given that the underlying MacOS is very similar to Linux, it probably wouldn't be much effort to port SQL Server over to MacOS. And, Linux runs on pretty much any microchip ever created. While hardware-specific differences will prevent x86 / x64 binaries from running on these other chips without being ported over by Microsoft, porting SQL Server to Linux on x64 in the first place does drastically reduce the cost of porting it over to Linux on any other architecture.

    In fact, Linux can run on an IBM Mainframe ( "Linux on z Systems" @ Wikipedia -- "Linux on z Systems" @ IBM ), and companies that can afford mainframes can generally afford a license or two of SQL Server. AND, SQL Server Express LocalDB runs purely in user-mode (no NT service, hence fewer OS dependencies), so maybe they can get that running on my Android phone and/or tablet :-).

That all being said, it does kinda make sense that a large portion of the folks in the SQL Server world aren't super excited about this direction. For those who either have only ever dealt with Windows, or whose job is with an organization that is happily on Windows and sees no need for Linux, then this is pretty much a non-issue. Still, I think once SQL Server on Linux becomes generally available and is being offered by hosting companies and being used by companies of various sizes, then the benefits will become more apparent.

Also, with regards to the idea that "most companies using Linux expect all / most software to be free because Linux is free": that is not entirely true. While it is true for some, there are plenty of companies that pay for support contracts for "free" software such as Linux, MySQL, PostgrSQL, etc as well as pay for "enterprise" level software to run on their otherwise "free" OS, such as Oracle, DB2, etc:

And, there are various reasons for companies to pay:

  • some might just prefer SQL Server to the other options (and it certainly is a good option, right)
  • some might be involved in an acquisition where a Linux shop "inherits" a SQL Server project and it would be cheaper / easier to just move it from Windows to Linux rather than do that in addition to porting it to one of the other RDBMS's
  • or, a company producing database tools that work with a variety of RDBMS's can more easily develop (and test!) against SQL Server without having to worry about buying / managing a Windows OS if they can get Developer Edition and/or Express Edition to work on Linux. Even if there are cheap (or even free) tiers for Amazon RDS or Microsoft Azure SQL Database, those offerings do not support all SQL Server features, and some of those features might be needed for testing.
  • etc, etc...

With all of the above in mind, I think there are plenty of reasons to view this as a positive direction for Microsoft and more importantly, for SQL Server. Even if you aren't personally excited about the prospect of running SQL Server on Linux, I think everyone in this community should view this as at least providing collateral benefit to those who are not directly benefiting from it. This new ability opens the door for SQL Server to gain market share, which not only increases demand for our skillset (as mentioned above), but also increases demand for related services and software: more people needing training, more people attending PASS Summit / SQL Saturdays / etc, more companies buying monitoring / backup / etc software. So, whether you think the Linux port will be a success or flop, or whether you are waiting to use it or never want to touch it, I would expect that the prospect of increased opportunity is something that we can all get excited about :-).

¹ The underlying mechanism allowing SQL Server to run on Linux – SQLPAL ( SQL Server on Linux: How? Introduction ) – abstracts the OS, providing a consistent environment for SQL Server to run on / in. This is not unlike how the CLR (Common Language Runtime) or the JVM (Java Virtual Machine) abstract the OS away from .NET and Java apps, respectively. In web development there have been various libraries / frameworks that abstract the browser away from client-side interaction for many years now because web pages need to work regardless of which browser (and version!) someone is using. And now there are libraries / frameworks, such as Xamarin, that allow mobile app developers to write apps that work regardless of running on iOS, Android, or Windows Phone.

Solomon Rutzky from SQLServerCentral.com

Join the debate, and respond to today's editorial on the forums

SQL Clone

NEW SQL Clone - version 1 now available!

Create copies of production databases and SQL backups in seconds and save up to 99% of disk space using SQL Clone. Redgate’s new tool removes much of the time and resource needed to create and manage database copies, allowing teams to work on local environments to develop, test and diagnose issues faster. Try it free.

Featured Contents


Configure SSRS with an SSL Certificate

Thomas Liddle from SQLServerCentral.com

A short demonstration on how to configure Reporting Services (SSRS) with an SSL certificate. More »


How to Programmatically Determine Which Edition of SQL Server is Installed

Additional Articles from Database Journal

Greg Larsen shows you how to determine if you are running the standard, enterprise, or developer edition of SQL Server. More »


From the SQLServerCentral Blogs - How many Backups is Too Many?

Andy Galbraith from SQLServerCentral Blogs

Does this title seem strange?  Of course we know this is the true answer: https://cdn.meme.am/cache/instances/folder633/58097633.jpg But here is a story of a... More »


From the SQLServerCentral Blogs - The Empirical Rule

I am resuming technical blogging after a gap of nearly a month. I will continue to blog my re learning... More »

Question of the Day

Today's Question (by Steve Jones):

How can I use OBJECTPROPERTY() to determine the number of triggers a table has?

Think you know the answer? Click here, and find out if you are right.

We keep track of your score to give you bragging rights against your peers.
This question is worth 1 point in this category: T-SQL.

We'd love to give you credit for your own question and answer.
To submit a QOTD, simply log in to the Contribution Center.


Design and configure SQL Server instances and databases in support of high-throughput applications that are mission-critical and provide consistent response times in the face of variations in user numbers and query volumes. Learn to configure SQL Server and design your databases to support a given instance and workload.

Pick up your copy of this great book today at Amazon today.

Yesterday's Question of the Day

Yesterday's Question (by Steve Jones):

I want to copy a set of data from my results with the headers.  For example, I have selected three columns for two rows. I want to copy just these 6 cells with the header  names.

What keystroke combination will allow me to do this?

Answer: CTRL+Shift+C


CTRL+Shift+C will copy the data and headers selected in the results pane.

Ref: SSMS Keyboard Shortcuts - click here

» Discuss this question and answer on the forums

Featured Script

Getting resource name from blocked process report

Darko Martinovic from SQLServerCentral.com

When you analyzing blocking problems, first choice is that you look what sys.dm_os_waiting_tasks will display.
It means blocking occurs right now, and you know blocker and blocking spid's.
In that case it is easy to determine resource which is subject of blocking.

 DTL.[resource_type] AS [resource type]
  WHEN DTL.[resource_type] IN ('DATABASE', 'FILE', 'METADATA') THEN DTL.[resource_type]
  WHEN DTL.[resource_type] = 'OBJECT' THEN OBJECT_NAME(DTL.resource_associated_entity_id)
  WHEN DTL.[resource_type] IN ('KEY', 'PAGE', 'RID') THEN (SELECT
     WHEN s.name IS NOT NULL THEN s.name + '.'
     ELSE ''
     END) + OBJECT_NAME(p.[object_id])
    FROM sys.partitions p
    INNER JOIN sys.objects o
     ON o.object_id = p.object_id
    INNER JOIN sys.schemas s
     ON o.schema_id = s.schema_id
    WHERE p.[hobt_id] = DTL.[resource_associated_entity_id])
  ELSE 'Unidentified'
 END AS [Parent Object]
 ,DTL.[request_mode] AS [Lock Type]
 ,DTL.[request_status] AS [Request Status]
FROM sys.dm_os_waiting_tasks wt
INNER JOIN sys.dm_tran_locks DTL
 ON DTL.lock_owner_address = WT.resource_address
WHERE wt.blocking_session_id IS NOT NULL;

But, if you analyzing blocking problems off-line, using trace or extended events, you have to count only on information found in blocked process report.
The resource name is most important information in the blocked process report. Unfortunately this information is only available as encoded.
The purpose of this script is to decode this information.

I PART using T-SQL

--@waitResource is written in blocked process report as a attribute of blocked-process/process element

DECLARE @waitResource as nvarchar(128) = 'KEY: 41:72057594544062464 (b14200e25741)' -- replace this string with your wait resource string
 DBO.[GetResourceName]( @waitResource, default);

-- Will return t-sql to evaluate

 sc.name + '.' + so.name
FROM MYDB_NAME.sys.partitions AS p
JOIN MYDB_NAME.sys.objects AS so
 ON p.object_id = so.object_id
JOIN MYDB_NAME.sys.indexes AS si
 ON p.index_id = si.index_id
 AND p.object_id = si.object_id
JOIN MYDB_NAME.sys.schemas AS sc
 ON so.schema_id = sc.schema_id
WHERE p.hobt_id = 72057594098286592

--2. Example. Evaluation

DECLARE @waitResource as nvarchar(128)
DECLARE @sql as nvarchar(max)
DECLARE @resCon as nvarchar(256)
SET @waitResource = 'KEY: 10:72057594098286592 (b14200e25741)'
--second parametar name should be the same as
--the name of first parametar in sp_executesql
SET @sql = DBO.GetResourceName(@waitResource, '@resourceName')
EXEC sp_executesql @sql
,                  N'@resourceName nvarchar(max) output'
,                  @resCon OUTPUT;
SELECT @resCon

-- Will return resource name

-- II . PART  using SQLCLR

-- Although it seems that CLR is better solution, there is a limitation when processing PAGE information.

-- Clr function does not allwed using dbcc or create temp table. Workaround is to use CLR stored procedure to

-- determine resource name

 [dbo].[GetResourceNameClr]('PAGE: 25:1:6077390')

--KEY: 25:72057600909443072 (8b56a42c5bc8)
 [dbo].[GetResourceNameClr]('KEY: 25:72057600909443072 (8b56a42c5bc8)')

--OBJECT: 25:1239779574:0
 [dbo].[GetResourceNameClr]('OBJECT: 25:1239779574:0')
-- 'PAGE: 25:1:6077390
EXEC [dbo].[GetResourceNameFromPageClr] 25

More »

Database Pros Who Need Your Help

Here's a few of the new posts today on the forums. To see more, visit the forums.

SQL Server 2016 : SQL Server 2016 - Administration

RDP into SQL server BAD or not - I am just qurious if I overlook somthing, keep asking why.....   I started doing some BI work recently and like to RPD...

SSIS 2016 unable to connect from SSMS 2016 - Hello, I have an issue where I'm unable to connect to SSIS 2016 SP1 with Integration Services version #13 Below is...

KB3095681 caused: "SSMS Has detected a problem that may significantly degrade product functionality" (solved) - Since this weekend my SSMS 2016 is extremely slow and gives a popup message that there is a 'problem' with...

SQL Server 2016 : SQL Server 2016 - Development and T-SQL

Function works when passing in parameters, does not work with CROSS APPLY - Hi, I have a function that looks to work fine when I pass in parameters, but when I pass in...

SQL Server 2014 : Administration - SQL Server 2014

SQL Server 2014 Patching (on Cumulatives updates) and Rolling Updates - Need advice - Hi all, As most of of you already know, MS recommends to patch CUs as the same as SPs. I have generally...

Windows Authenticaion vs. Mixed Mode - Greetings: I'm reading Peter Carter's book 'Securing SQL Server' and there is a brief discussion about Windows Authentication vs. Mixed Mode. Currently...

Performance improves after reboot - I am an ETL Developer who was asked to look at SQL Server performance problem. We have two DBA's but they're...

SQL Server 2014 : Development - SQL Server 2014

Msg 547, Level 16, State 0, Line 1 The INSERT statement conflicted with the FOREIGN KEY constraint "fkInventory_TagAlongs". The conflict occurred in database "sanroqued", table "dbo.Inventory". - Insert into Inventory_tagalongs (itemnum, store_id, tagalong_itemnum, quantity) select itemnum, store_id, tagalong_itemnum, quantity from tagalonglistsq I do not understand why i am getting...

Indexing Small Temp Tables used to JOIN based on a value being in a range... - Just wondering if there's much I can do to improve a query that has to join about 70K rows to...

SSIS project fail to build - CPackage::LoadFromXML fails - I have been trying to solve a SSIS project related problem for a week now. The SSIS solution/project has been working...

SQL Server 2012 : SQL 2012 - General

get date / only sql - Hi how do I use the only clause and date together as follows a customer can  many orders from departments in one...

SQL Server 2012 : SQL Server 2012 - T-SQL

SQL loop never stops even if I define loop number - Hi, I have a temporary table, filled from an csv file, then this table values will be inserted in other...

TRIGGERS ON VIEWS - I have created a view which as you know pulls information from base tables.  Now this view stores live information pertaining...

XML Shred skips around if node is missing expected elements - Hello,  I have bulk imported xml to a local table  called CAQH_Return_XML. as a blob so that I can then shred it.....

SQL Server 2008 : SQL Server 2008 - General

sqlcmd error when executed from bat file - Hi All, Any ideas why I am getting the following error below?  It is being executing in a batch file locally...

Cloud Computing : SQL Azure - Administration

SQL Azure database replication - We have a database on our premise. We also have SQL Azure subscription. Is there a way that we can...

Programming : Powershell

Powershell formatting help - convert-html commandlet - Hi Experts, I need some formatting help in powershell using convert-html commandlet. Basically, here is my requirement. I want to...

SQL Server 2005 : Administering

Linked Server - I am having error on my linked server. from x to y. OLE DB provider "SQLNCLI" for linked server "x" returned...

SQL Server 2005 : Data Corruption

Check Catalog Msg 3854 resulting from dbcc checkdb - I'm on SQL2005, and I've let too many days go by, so I can't restore from a backup before this...

SQL Server 2005 : SQL Server 2005 Integration Services

Rename File in SSIS - I need to rename a file in SSIS. Part of the file rename should include the current date. I am trying...

This email has been sent to {user_email}. To be removed from this list, please click here.
If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com.
This newsletter was sent to you because you signed up at SQLServerCentral.com.
Feel free to forward this to any colleagues that you think might be interested.
If you have received this email from a colleague, you can register to receive it here.
This transmission is ©2015 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.
Contact: webmaster@sqlservercentral.com