November 25, 2016

TL;DL – .NET Rocks! 1379 (SQL Choices with Tony Petrossian)

Show link: .NET Rocks! 1379 (SQL Choices with Tony Petrossian)

Guest: Tony Petrossian (pronounced peh-TROH-see-uhn)

Show Notes

  • Discussion about the IoT comment from the listener (see below)
    • Many customers store data in SQL Server because of in-memory processing, analytics, and the ability to run R. Many also use Hadoop or data lakes; it depends on ingestion rate and what you’ll do with the data afterward
    • Some systems are real-time where the device sends data directly to SQL. A column store allows you to do data processing in real-time.
    • Hybrid Transactional/Analytical Processing (HTAP) sounds like what you’d want in a high data-rate transactional system (i.e., analysis in real-time).
  • SQL Server 2016 (CTP 1) runs on Windows and Linux; this is the first public preview for Linux.
  • Are the Windows and Linux versions running a common code base?
    • Yes. Microsoft demoed the ability to take a database from a Windows machine, make a backup, copy the file to Linux, and construct a SQL database using that file.
    • Across platforms there is feature parity out of the gate.
    • Internally they call themselves the “SQL Choice Team;” their mission is to provide choices to their customers.
  • What is the parity with SQL Azure? Is it running exactly the same code as SQL Server on-prem?
    • Yes, it’s the same engine and same source base.
    • There’s cloud-first development. Most of the new features get coded/tested by the developers, deployed into SQL Azure, and then it goes into the next version of SQL.
  • Row level security started in a private preview, some issues were worked out, and now it has been made generally available.
  • What’s the product time horizon?
    • In 2017, there is a scheduled Linux version of SQL (note the product is in CTP as of November 2016). Beyond that, no comment on other dates.
    • The goal is to get the product out more quickly (i.e., Agile), without disrupting existing customers. There is still support from Microsoft.
  • All programmable features (e.g., page compression) of SQL are now available for any level of SQL Server. This availability makes SQL Server more accessible because of lower cost. There is still SQL Server Enterprise if you want to support up to 24 TB of RAM, better scaling support, operational security, and increased availability. You don’t have to change any of your code for the Express, Standard, and Enterprise levels.
  • What are features that are in SQL Server Enterprise only?
    • Features pertaining to performance, scale, availability, and encryption. They are operational, and not coded against so you won’t have to change your code to work with Enterprise.
  • How do these features just work on both platforms?
    • There is a platform abstraction layer that acts as an interface to the operating system that SQL uses.
    • Other databases have implemented similar abstraction layers.
  • Are the other products — Analysis Services, Reporting Services, Integration Services — also cross-platform?
    • Not yet. The first step was the relational engine.
  • What about the new on-prem appliance?
    • There is a SQL fast-track program for data warehousing. There are different sizes, combinations, and vendors (e.g., Dell, HP).
    • A data sheet comes with each configuration, a set of tests have to run, and there’s a set of performance numbers that have to be reported.
    • Basically SQL Server comes configured like an appliance.
    • From an IT guy perspective, it’s good that I can use a vendor I already have a relationship with.
  • There’s R inside SQL, so you can use predictive models (e.g., for manufacturing, jet engine construction) to get in front of problems before they occur.
  • SQL Server Express was what you bundled with your app just so you had a reliable data store on the machine you were working from.
    • Express comes with in-memory and column stores as well.
    • There are no licensing issues — it’s free.
  • Microsoft is open-sourcing client libraries and SDKs (e.g., JDBC driver for SQL). Developers get the driver from their own channels (e.g., Maven instead of a Microsoft site). There’s Better engagement when work is out in GitHub. Microsoft is also helping maintain a PHP driver for SQL.
  • What about PostgreSQL?
    • Many people use it. It’s a different use-pattern than MySql, which is used for content management systems.
    • Maybe people using PostgreS on Linux will now consider SQL Server.
  • One reason Linux shops resist Windows is because their instrumentation and management software is so different. How do you shift your database health measurements over to a Linux way of doing things?
    • All of the SQL DMVs are mapped /proc style in Linux, so you can go look at the SQL stats like a Linux database administrator would look at processes and threads.
    • If you don’t want to use a Linux command shell, you can query process tables on Linux.
  • The installation process matches the platform. For example, use “apt-get” on Linux instead of a window-based installer on Windows.
  • Are there any other announcements?

Better Know a Framework

PolyScience The Smoking Gun Handheld Food Smoker with Wood Chips

Listener E-mail

From show #1359 (Hybrid Transactional Analytical Processing on SQL Server 2016 with Lindsey Allen); can and should we use a relational database to store IoT data? It would be easy for such devices to only report changes rather than current values.

Technology Giveaway Ideas

A Surface Studio and a few iPhones.