Voice of the DBA

Category: Editorial

  • The Ever Expanding Data Platform

    This past week was the 2018 Ignite conference from Microsoft, where we had a number of announcements about the data platform. You can rewatch some of the sessions from the event, and I might recommend the keynotes to see some of the demos and positioning of the data platform. That’s the direction that Microsoft is moving their database products, as a complete platform that not only includes SQL Server, but CosmosDB, Managed Instances, Data Lakes, and  more.

    If you’re a SQL Server DBA, it’s time to stop thinking yourself as a SQL Server DBA or developer. Instead, you need to be a data professional, especially on the Microsoft stack. While you might concentrate on SQL Server and live in SSMS, you ought to be aware of the growing options for working on the Microsoft stack. Azure Data Studio, which Grant wrote about this week, ought to be a tool you investigate. You also ought to be looking at the latest version of SSMS, which had it’s v18 move into a public preview this week. With these tools being free, companies ought to be moving away from the older versions that shipped with SQL Server 2014 and earlier. Instead you should at least be on a v17 version of SSMS. Talk to your IT group and give it a try today. It works fine with all your SQL Server versions, from 2005 through 2017.

    Microsoft is certainly hoping you’ll run more workloads in Azure, and that’s where the data platform is growing. CosmosDB, which I think has a lot of promise for various types problem domains, or even as a companion to SQL Server for certain types of data. There is an increase in their SLA to 5 9s, which is both impressive and ambitious. I know very few on-premises instances that get by with 5 9s across multiple years, leaving aside the ability to get 10ms write performance in the SLA. The is also multi master replication and support for the Cassandra API. While I haven’t done much with CosmosDB, it is on my radar to experiment with as a data store option.

    Managed Instances will be generally available on Oct 1, just a couple days away. While I wasn’t sure that this product would catch on, I’m not surprised that some companies would like to get away from managing most of the stuff around the database and stick with the data. To me, this, more than anything else, can mean that DBAs at larger companies need to be managing data, security, and more, without worrying too much about the basics of backups and HA. Even threat detection, something few of us are good at, is handled by Azure. The restore demo in the keynote is truly impressive. I’m not sure many of us would want to, or be able to, architect those speeds. At least not as easy as provisioning an Azure Managed Instance.

    There are lots of other announcements, which you can read. The one really interesting thing for me was the Data Box announcement. I’ve had more than a few people be concerned about the initial loads of data into an Azure database or data lake. I’ve had that concern, and actually been part of a company that FedEx shipped a rack of disks as part of a SAN to a DR site because of bandwidth constraints. The Data Box is a device that you can order and fill, shipping this back to Azure for loading. It comes in 40TB, 100TB, abd 1PB sizes. That is truly stunning to me. Drop ship 1PB if you have the need. You can even see a picture of it from Argenis Fernandez for some idea of size.

    It’s an exciting time to be a data professional, and Microsoft’s data platform continues to grow. I don’t know that any of us will know more than a tiny bit about most of the platform, but I certainly plan on increasing my knowledge in a few areas to become better aware of how they work and what they are capable of. I might not be able to use them well, but I can at least have enough knowledge to have a conversation about the technology and have an idea of whether it might solve a problem that I run into at work.

    Steve Jones

  • An Open Thank You to the Microsoft SQL Server Team

    Thank you, Microsoft SQL Server Development team. I really am happy with your work.

    I was going through emails and noticed the someone had posted a surprise in the SQL Server 2019 CTP. The post was slightly cryptic, but since I saw the dreaded string-or-binary-data-truncated message, I was intrigued. Digging further, with some help from fellow MVPs, I found that one of the most voted on feature requests was actually implemented. I blogged about this briefly, but in case you wonder, this is the message in SQL Server 2017 and below:

    Msg 8152, Level 16, State 14, Line 8
    String or binary data would be truncated.

    In SQL Server 2019, I get this from the same repro:

    Msg 2628, Level 16, State 1, Line 8
    String or binary data would be truncated in table 'Sandbox.dbo.Customer', column 'CustomerName'. Truncated value: 'Is this th'.

    You do need to enable trace flag 460, but this works and should help you find that problematic data. I’m really glad that Microsoft has implemented the first stages of a fix, and I do appreciate their efforts here. This is a nice step forward.

    I do hope that future versions, or even continued development on SQL Server 2019 will enhance this. Right now I just get the first occurrence, which is good, but I’d love it if we could get a result set back, or maybe an indication of more problems. I know I’m asking for more here, but there is room for improvement.

    If this is all we get for SQL Server 2019, that’s fine. I think this is going to be very helpful for those problematic imports, and at least help us find bad data more quickly in those poorly structured input strings.

    Steve Jones

    The Voice of the DBA Podcast

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

  • The Uninteresting But Necessary Work

    There has been a rising tide of data legislation in the last few years that asks for organizations, especially private companies, to better protect their data. The GDPR is one of the most well known, taking effect with regards to enforcement earlier this year, and I’ve been doing quite a bit of work in relation to this law. There are plenty of other laws, such as California’s CCPA, Australia’s NBD, Japan’s APPI, and more that we ought to be aware of as data professionals. These laws affect personal data about people in a variety of ways, and they can affect how we process and use portions of the data we store.

    It’s not as simple as it might sound to change our data handling practices. In fact, it might not be that easy for many of us to do this now unless we’ve actually done something in advance: we need to have classified our data. We need to understand the impact of the various columns in our tables, the exports of flat files or reports, and even the development processes that make copies of our production databases.

    I’ll be honest, classification work is mind-numbingly boring and uninteresting. This almost feels like busy work to me, especially once we get past the obvious tax IDs and birthdays of people. When we start examining other data, the task feels like it ought to be delegated to junior staff, but many of them lack the experience to make the decisions. What can be more frustrating is that most of them lack the status to get others in the organization to respond to questions, which means the task ultimately falls on more senior people. This also means they often do it once and then forget it, leading to out of date information.

    We don’t like doing classification, but we need to do it. Without having some mechanism that allows us to determine if data can be moved or used in another system/database/report/etc., we end up just ping-ponging around. We assume all data is sensitive and try to lock it all down. That leads to complaints, as well as staff working to circumvent the rules until they appear meaningless. At this point we might give up on controlling data and just trust people. That leads to audit problems, potential data loss from security incidents, and plenty of embarrassment about why we didn’t implement some simple controls.

    Then the cycle starts again.

    Classifying data is simple in some ways, but not easy to ensure the data is available, up to date, and easy to find for any size team. I’ve seen simple solutions that rely on spreadsheets. I’ve seen complex software packages that are expensive and cumbersome to implement with other applications. Microsoft has started to help with a few changes in SSMS, but this doesn’t seem like a long term solution, though SQL Server 2019 might help. Redgate has spent some time on this as well, thinking about the issue and we have an early access program now.  All of these are partial solutions that might work for some organizations, but not all.

    Ultimately, this is something like security, that we ought to be building into our systems from day one. Every proof-of-concept or prototype ought to be classifying data from the beginning. We won’t be perfect, and won’t get every label correct, but if we’re always thinking about the data, we can always correct our label and more tightly or loosely decide to handle data. I’d also like to think that if we conservatively label the data early, we’re unlikely to get into positions where we are mishandling data in a way that makes it more likely that we accidentally lose data.

    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.

  • 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.