Voice of the DBA

Category: Editorial

  • SQL Server v.Next is Coming in 2019

    Yesterday the 2018 Ignite conference kicked off with a number of announcements from Microsoft. You can re-watch some talks and the keynote, catching up on Windows, CosmosDB, and more. The big announcement concerning most of us is about the next version of SQL Server. This has been called v.Next in NDA briefings, as we’ve learned of different pieces of work, but now there’s a name: SQL Server 2019.

    The preview version, CTP 2.0, was released yesterday for public download. This is the first public release of the next version, and actually the first version I’ll be installing. To date I haven’t had time to even try to work with any previews. The keynote covers a bit of the product, but to see What’s New, check out Books Online. There are some database engine changes, such as UTF-8 support, better index rebuilds, improvements in Always Encrypted, Java programmability extensions and more. Big Data Clusters come as well, with Spark and better HDFS support. I don’t know much about it, but lot of friends that work in analytics are excited about Spark support.

    There are also some enhancements for SQL Server on Linux, which start to bring the two platforms closer together. Replication has been added, as well as DTC support, which have both been blockers for some users. AG support in containers is really interesting, though I’m not positive that this is that helpful. Machine learning services and OpenLDAP support are worthwhile additions as well.

    I am most excited about the secure enclaves for Always Encrypted. These will finally allow AE to be a more useful technology, and I’ll be updating my security session with this information. Maybe we’ll actually start to see AE deployed in more situations where high security is required as most of the operations we’ve needed, such as LIKE and range evaluations, haven’t been possible. I’m excited about the possibility of better security, though as most of us know, the weakest links are still the human and the client computer.

    There are plenty more enhancements, including more database scoped configuration items, better query processing for some opertions, more synchronous AG replicas, and maybe better, auto redirection of AG clients without a listener. There aren’t any new enhancements to the data classification options, though I’m hoping that will change before RTM. One last note, SQL Operations Studio has been renamed to Azure Data Studio. I’m not a big fan of naming changes, and I don’t like either of these, but I am curious if any you think this is the way forward for our toolset.

    All in all, this looks like a nice evolution for SQL Server, but not a major release with lots of new features. Perhaps my BI colleagues that use Spark or Java programers will disagree, but I don’t see anything that would be worth upgrading for the SQLServerCentral servers. Even in most of my jobs, other than getting the Always Encrypted enhancements, I don’t know many of these features would provide enough of an ROI. If you feel differently, let me know. There is some other coverage at Brent Ozar, MSSQLTips, and SQL Performance.

    One last item, if you’re looking to get started with any of the new Microsoft technologies, there are a few options. Certainly we will cover some items here, but we tend to focus on the data platform. Microsoft has announced Microsoft Learn, with content that covers Azure, PowerApps, and more. This might be a good resource for your learning plan this year. Pick a lunch or two a week, a weekend morning, or some other time and try to slowly improve your skills.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 6.0MB) podcast or subscribe to the feed at iTunes and Libsyn.

  • Azure DevOps

    I’ve been a fan of Visualstudio.com and VSTS for some time. I moved most of my demos to this platform a couple years ago and I’ve been pretty happy with it since then. There is tremendous flexibility in how you can use automation and dashboards to build software and coordinate the activities of your team.

    The VSTS system underwent a rebranding and reorgnaization renently. The various pieces of the system were renamed a part of Azure DevOps, with the different parts being given new monikers such as Azure Pipelines, Azure Boards, and more. This was combined with an initiative from Microsoft to better support open source projects by giving them unlimited build minutes for public Github repositories on a variety of platforms such as Windows, OSX, and Linux.

    I’m not a bit fan of name changes, as I think that if the software performs well and provides value, it will succeed. However, marketing people need work, too, and management inside a company often rearranges things to put their own mark on a project. I’m actually glad the marketing effort was ramped up as I think the Microsoft platform based on TFS for tracking work, version control, builds, and releases has become a fantastic platform for anyone building software. That’s not to take away from some other products like Bamboo and Octopus Deploy, which might work better for you. If they do, they plug into the Azure DevOps platform easily.

    If you haven’t tried Azure DevOps, I’d urge you to give it a try. There’s an all day recording of various parts of the system being used to produce software that will show you how to get started and use the system. I’m sure there will be more information and talks this week from Ignite. There aren’t a ton of database tools, but there are some add-ons from various companies to help you build and deploy databases alongside your application software.

    While there are challenges with databases, I’d argue that incorporating a known process will increase reliability and lower risk for making changes. This won’t help you build better code. That’s something you still need to ensure your developers are doing. This system just helps ensure simple, silly mistakes aren’t made and everyone knows exactly how your changes will be deployed to your production environment.

    Give Azure DevOps a try today and see how you can build a smooth, repeatable, reliable process for your software.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 4.0MB) podcast or subscribe to the feed at iTunes and Libsyn.

  • Fitting Into RAM

    RAM has always been a fairly limited resource in most of the computer systems I’ve worked with in my career. Often there is never enough RAM, and I’d always like more, often to speed up the systems. That has somewhat changed with laptops, as 16 GB really works well for me most of the time. Not that I wouldn’t take a 32GB machine, but I’m waiting for them to become more common and smaller.

    This has especially been true for database servers. It seems that I’ve rarely had a database server that could fit my entire database in RAM. Even now, I have an over-provisioned server for SQLServerCentral which has plenty of spare capacity, but I’m still slightly short on RAM. The target level for SQL Server is about one GB more than I have set. Not really worth complaining about, but still I don’t have the RAM I’d like.

    Last week I wrote about someone that attacked the RDBMS as old and troublesome technology. As a part of this, a method of storing all data in memory was presented. I’m not sure I think this is actually a good or practical idea for most systems, but I did wonder about the idea of data space and size. Certainly I have seen plenty of index space in databases, and certainly there is more index data than other data at times, but I suspect that’s not the case for many databases.

    Regardless, I was curious if anyone has large databases that couldn’t fit into RAM these days. If you think about the largest database you have, how big is it, in terms of data size. Not allocated size, but the total data space used. Would this fit into RAM if you could get 1TB or 2TB of memory? If you can, what about index sizes, are they large? There are a few scripts in this thread if you need one.

    I suspect there are certainly databases that don’t fit into RAM, and likely plenty of instances with more than 1 database that don’t have enough RAM. I still see plenty of people with less than 64GB on their servers, so that’s a battle still being fought. I certainly wouldn’t advocate an in-memory only database, likely because there are going to be other issues, but it’s still an interesting thought. Certainly my server has only 60GB allocated and the databases are well over that in aggregate.

    Maybe asking for a bit more RAM on those critical servers is the way to go, especially if you think you can get the entire database into memory.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 3.5MB) podcast or subscribe to the feed at iTunes and Libsyn.

  • The Upgrade Storm

    The Perfect Storm is a movie about the confluence of three weather events that combined to create a situation the resulted in the loss of all life on a fishing boat. It’s a good movie, and certainly one that might make anyone think twice about sailing off into the North Atlantic in bad weather. I certainly wouldn’t want to be traveling on the water when any type of hurricane might present.

    Glen Berry writes about a perfect storm of a different sort. The perfect storm to upgrade, with the confluence of three events he sees taking place soon. We have quite a few performance related changes coming in Windows 2019 Server, in CPU hardware, and storage technology. Those three changes could dramatically improve the performance you might see from your SQL Server licenses.

    If you were planning on updating a legacy system with a new version of SQL Server, you might do well to consider some of these hardware and software improvements. I don’t know what the costs might be, but that’s certainly something you will have to consider for your environment. The argument likely would be that if you’re updating the database server, why skimp? This is a limited resource and one that can affect your entire system. Spending some money here might make a significant difference to the way in which your system performs.

    This might be a perfect storm of a different sort for your budget and your accounting department as upgrades for SQL Server are likely to be pricey. The improvements could dramatically improve workloads by lowering the time users spend waiting or increasing the throughput of queries. Those might negate the cost of any hardware and software costs involved since time is a valuable resource and saving time is worth something.

    The problem for many people is that if you aren’t writing good code, then you might not get the benefits you want from the upgrade. Certainly the performance improvements might be lost under poor query performance. Upgrade to take advantage of improvements if they are something you need, but please, learn to write better code, and ensure your developers are regularly looking for better ways to use your expensive SQL Server licenses by writing more efficient code.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 3.2MB) podcast or subscribe to the feed at iTunes and Libsyn.