Voice of the DBA

Category: Editorial

  • SQL Server on Linux is Just SQL Server

    I’ve been working with the SQL Server on Linux (SSoL) version for quite some time, almost a year. In all of that time, I’ve for the most part found that SQL Server is SQL Server. When I connect, run demos, check code, almost everything just works. If I didn’t bother to check @@version, this would appear to be just another SQL Server to me. That’s what a new video on Channel 9 shows as well. SQL Server is pretty much the same on both platforms. I’ve tested the Redgate tools and to all of them, SSoL is just SQL Server.

    There are some differences, which is to be expected. Any operations that access the file system and require paths work a bit differently, and for those people that end up administering the product, there will be some changes to get used to. The advanced HA features are similar, but again, some work is required. However, this isn’t all bad. I’m impressed with the apt-get process (I’m testing on Ubuntu), which is way, way easier than any patching or updating process I’ve gone through on Windows. In fact, setting up an Ubuntu VM last year was easy, and installing SQL Server was about as easy as it could be.

    The tooling on Linux isn’t as mature, and I don’t know when we will see a GUI client, but as I move more and more to PoSh or scripting to make changes in SQL Server, I expect more and more people to manage both Windows and Linux versions in the same way. Certainly using SSMS to write queries is a much nicer experience, and I would guess that many developers that might run SQL Server on OSX or Linux will want a Windows VM for SSMS. Of course, since Visual Studio is now on OSX, maybe we’ll see SSMS running natively on other platforms.

    I don’t know how many enterprises will run SQL Server on Linux, but I’m sure there are some that will. I don’t think a lot of organizations will move from Windows to Linux, unless they have loved SQL Server enough to install a single Windows host for the database and want to get rid of it now. I do think lots of developers will run SQL Server on Linux/OSX, especially in containers, where it is really easy to get a container running on their platforms.

    If you’ve experimented with SQL Server on Linux, or you are excited, let us know. If you think your organization might use this platform, let us know as well. I suspect a few of you will just because you can, which is as good a reason as any.

    Steve Jones

     

  • Becoming a Data Scientist

    Data Science is hot. There are lots of companies excited by using machine learning and AI to enhance their applications. There are new jobs, some of them well paying, and certainly not enough people to fill them. In many ways this reminds me of previous “hot” areas, such as Novell Networking in the late 80s/early 90s. Companies wanted new CNEs and paid dearly for them. The same thing happened in the mid 90s with MCSE’s for Microsoft networks. Many of the people hired weren’t remotely qualified, having just completed some multi-week boot camp.

    You could go to school. If you have completed college, there are a list of data science graduate programs that you could choose from and pursue a masters degree. There’s even a blog where someone is documenting their masters degree path to becoming a data scientist. This isn’t a quick or east path, but it is one way to gain data science skills.

    If you don’t want to spend the time or expense of a formal college program, Microsoft has a data science curriculum on the EdX platform that you can complete. These are low cost programs that you can complete to get a certificate. The value of that certificate is debatable, but the same could be said for any program. A few people that are working through this program have found it to be a good set of resources that is teaching them valuable skills.

    There are other options, no shortage of books, blogs, and other resources on data science and data analysis techniques. It’s up to you, however, to learn what you need to know and become competent at a level that is useful for some organization to pay you. I dislike people choosing to study a topic for a job, so I would say that if you wish to go down this path, do so because you enjoy the work and find it interesting. Build some skills, build a portfolio of data science projects, and best of luck.

    Our industry has thrived for a long time on simple analysis, and I think there will be jobs in this area for some time to come. I do expect that better looking reports and dashboards are going to be expected rather than simple tables, so I’d suggest everyone work on their visualization and report polishing skills. I also think that more complex data science techniques will be in demand, though I wouldn’t expect job growth here that overwhelms current jobs. Tackle data science if you like, but be aware this isn’t a simple or easy chore. There are lots of math and statistics involved and it looks like this is more science than just data.

    Steve Jones

  • Python in SQL Server

    The Microsoft Data Amp event was this week, with a number of SQL Server announcements. We now know this is going to be called SQL Server 2017, which is no surprise. CTP 2.0 was also released, which includes a few improvements, but one in particular caught my eye. Python code can now run inside SQL Server, meaning we are not limited to the R language for data analysis. That’s exciting to me and I think that’s a great change. It’s one that’s been rumored for some time. I like Python, and think it’s a nice, elegant language. My son has been learning it in college and I’ve spent some time playing with it after the SDTIG got me interested with their beginning Python and Data Science modules. Since then I’ve spent a little time learning R, but find it to be a cumbersome language to work within. Having code embedded in a string in a SQL Server stored procedure made things harder, and I kept hoping that Python would be added.

    It’s there now and you can play with it. While you might debate whether this is a good use of SQL Server resources, I think for quick, short, specific analysis calls, it’s a good choice. Certainly complex calculations and data manipulations are not always efficient in T-SQL and having another option is great. You’ll want to be careful when you run Python or R code, but these are good options to have. There is also GPU processing support is available to take some load off your main CPU.

    CTP 2 is supposedly feature complete and there are more changes than just Python. Tempdb has a better setup experience, we get the first version of graph data and query support, resumable online index rebuilds, adaptive query processing, and more. You can read the announcement, which isn’t heavy on technical details, but gives you an idea of what’s changing. I’m not much of an SSAS person, but there are some enhancements in this platform as well.

    There are other improvements and announcements this week. Azure Analysis Services is generally available (no longer beta), as as Azure Data Lake Analytics and Data Store. DocumentDB is improved and some Cognitive Services are GA, and in general, Microsoft is all in on the analytics and deep learning.

    The world of data in the Microsoft world is growing and changing rapidly. We’ll have a new version this year, just a little over a year after SQL Server 2016. I expect that we will continue to see more, and new changes, occurring at a rapid pace in the future. None of us will completely keep up and become experts in all areas, but we have lots of options for new learning and experimenting with advanced features. I’d encourage you to take a little time here and there and read about and play with the new features. If you have Docker, you can get a version of SQL Server 2017 up and running without installing anything on your machine to see if there are any changes that might help your organization take better advantage of  the data you work with every day.

    Steve Jones

     

  • SQL Server Telemetry

    One of the things that I’ve seen blogged about and noted in the news over the last year is the amount of data being collected by various software systems. In particular, I’ve seen numerous complaints and concerns over what data Microsoft collects with its platforms. Perhaps this is because Microsoft is the largest vendor, and plenty of other software collects usage information, primarily to determine if features are being used or working correctly. I think much of this is overblown, but I can understand having sensitivity about our computer usage, especially for home operating systems.

    Microsoft conducted an extensive review and published data about what is being collected and why. Windows 10 and other products have undergone review and must comply with the published policies. There’s even a Microsoft Privacy site to address concerns and explain things. It’s an easy to read policy that explains what  Microsoft is collectin, if you’re connected to the Internet (if you’re not, don’t worry, no bloating files). That’s a huge step forward in an area that is evolving and something I wouldn’t have expected to see in the past. I am glad Microsoft is making strides here, even if I may not agree with specific items in their policies. I do think that most of the companies collecting this data are doing so to improve the products, not spy on customers. I’m sure some do, but likely smaller organizations with some sort of criminal intent.

    As data becomes more important, telemetry for software is potentially a data leakage vector where private, personal, or customer information might be leaked. Certainly as more speech and other customized services are used in businesses, I worry about what data could be accidentally disclosed. After all, it’s not that super powerful smart phone that is actually converting audio to text in many cases; it’s a computer somewhere in the vendor’s cloud.

    With databases, this has also been a concern from some people. I’ve seen the Customer Experience Improvement Program for years and usually opted in. I’m rarely doing something sensitive and I hope that with more data, Microsoft improves the platform. That’s the stated goal, and I’d seen them talk about this a few times. The SQL Server has moved forward and published an explicit policy that spells out what and when data is collected. It was actually just updated recently and all new versions of the platform must provide this information (if anything is different) and adhere to what they disclose. There is a chance that user data could leak into a crash dump, though users have the opportunity to review data before it is sent to Microsoft. I’m not sure how many will, but they have the chance.

    I would like to be sure that anything sent is secured, and perhaps have an easy way to audit the data sent in a session, but I know this entire process is evolving. One important item to note is that customers can opt-out of data collection for any paid for versions of SQL Server. That’s entirely fair, but if you have regulatory concerns, you should be sure that you don’t restore production data to development machines. You shouldn’t anyway, but just an FYI.

    Usage data is going to be a part of the future of software, especially as more “services” integrate into what we think of as software. Those services aren’t always going to be under our control and certainly part of the reason many of these are inexpensive is that additional data is captured about the people using the software. I hope all companies publish and adhere to some sort of privacy statement, and maybe that’s a good start. Rather than any regulation on specific privacy that must exist, start forcing companies to publish and stick to whatever policy they choose.

    Steve Jones