Microsoft, please open source sqlcmd, Sqlps, SMO, and LogParser

Mar 28 2015No Comments SQL, Open Source, Microsoft SQL Server by justin

Microsoft has open sourced part of the .NET framework. This is exciting news, although honestly not all that unprecedented. There was a time when AT&T defended the C language as its intellectual property. These days, the language is effectively in the public domain. (Note IANAL, the preceding was not legal advice. UNIX is a trademark of the Open Group). This lead to a few people asking if SQL Server would ever be open sourced. That answer is probably no for the time being. However, I do think certain components of if can be open sourced.

Command Line tools.

sqlcmd is a great command line shell for SQL Server. However, it could be better. The same goes for bcp and the other tools. Microsoft isn't going to spend the time making sqlcmd read the contents of the EDITOR environment variable if SQLCMDEDITOR is not set to match the behavior of unix command line tools. Its not going to add a simple switch to generate query results as a CSV, or sendout output to the clipboard. Its not going to allow you to colorize RAISERROR messages based on severity. However, members of the community most certainly would do that. Also, I think if sqlcmd got enough pull requests, Microsoft might realize that PowerShell is not the be all and end all of the command line.

SQLPS

By SQLPS, I am referring to all the powershell modules, providers, etc for SQL Server. There are some great cmdlets in that collection. However, can we get Invoke-SqlCmd to do parameter substitution? Yes, I know its unlikely that a PowerShell script will be run by an untrusted user, and if it was, that user probably could already run arbitrary SQL. However, as a matter of best practice and acknowledgement that there are third party solutions to serve web pages by PowerShell, one should be allowed to parametrize their queries. Invoke-SqlCmd2, written by Chad Miller and contributed to by others including myself, allows you to do this. Wouldn't it be nice if a future version of the builtin cmdlet had the same feature? If it was open source, I swear on my honor as a developer, I'd submit the patch. 

SMO

Sql Server Management Objects is a bit of a legacy solution. Hwoever, it still has valid use cases. It provides amongst other things a COM API for backing up and restoring databases. I don't actually use SMO, but many DBAs do. I might some day. I'm sure developers that USE SMO has patches they'd like to

LogParser

LogParser is actually not part of the SQL Server code base, but its used by SQL Server DBAs. LogParser claims to be intended for dealing with Log files, and it indeed lets you sql SQL queries on IIS log files and other text formats. However, it also lets you read and write from SQL Server using the bulk copy mechanisms. Its a great ETL tool. It exposes a COM object, and can be automated with Powershell. I've asked Scott Hanselman about this, and he says the main thing Microsoft needs is an internal sponsor to make this happen.

If LogParser was open source, a proper .NET layer could be added to it, support for more data sources, and the SQL syntax could be improved. 

Conclusion

Will Microsoft Open source any of these tools? I think they could safely do so without hurting their current revenue streams. Having these tools open sourced would benefit people who use SQL Server, but not in a manner that would allow people to, for example, use Express instead of Standard Edition or Standard instead of Express. It would not hurt the revenue streams. It would require them to dedicate some resources to the Sql Server client tools. I think these tools need some love anyway, and those resources should be dedicated.

0 Responses to 'Microsoft, please open source sqlcmd, Sqlps, SMO, and LogParser'

You must be authenticated in order to add a comment.