Voice of the DBA

Tag: Database Weekly

  • The Need for DRE

    “Today’s database professionals must be engineers, not administrators”

    That’s a quote from an interview at O’Reilly on Database Reliability Engineering. I don’t want to quibble about whether those of us working in technology are engineers in name or not, but in function, I do think the world is changing to one where we need to do more than be caretakers of systems. We need to be actively involved in ensuring we have very reliable, well-designed systems that use the best practices and patterns of highly available and reliable database systems.

    There is advice to learn and work closer with developers. I like that, and I do think the idea of learning to script and automate tasks is important, perhaps more important all the time, for data professionals. Some of the advice to look towards the quick moving, limited lifetime of many software components today doesn’t make sense. A database, at least some parts of a database, don’t fit within that model. There are valid reasons why a datastore must provide some stability and persistence for an application and won’t fit within a model of multiple systems. Or at least not practically within a model.

    However, there is good advice in the piece to learn more about different types of database services and patterns, and consider other ways of implementing datastores other than a RDBMS. I wouldn’t necessarily abandon a RDBMS for some NoSQL store just because developers think it’s easier, but I would consider whether I actually need to very tight coupling between different parts of the database and complete consistency. I think there are a fair number of domains where a CQRS pattern or some distributed store would work well. The move to microservices might be an enabler for your business if you consider the advantages for OLTP type transactions.

    The flip side of all the power that many stores provide is that many also make reporting and aggregating information more difficult. I would venture that in many cases, a data warehouse (perhaps even a RDBMS-based or columnar store) is necessary, along with the ETL process necessary to keep it up to date. These aren’t simple processes, and take resources to build. For most of us, because we don’t work at extreme scales, I’m not sure it’s worth leaving a relational platform, but I do think that we can learn to evolve and enhance our relational databases faster with DevOps ideas and techniques.

    Steve Jones

     

  • Security and Patching Struggles

    A couple weeks ago we had a huge security issue with the WannaCry ransomware racing across the world. I was out of the office, and mostly offline, so I was a bit out of touch. However, many organizations were affected, and I’m sure many infrastructure people were scrambling to patch and protect vulnerable systems, possibly even restore affected systems. I certainly hope that most (or all) organizations didn’t pay any ransoms. For the future, my view is it’s better to lose a little data and restore systems than pay ransoms.
    I can remember when we got patches at random times, as vendors wanted to provide more functionality or finally had enough bugs fixed to release a patch. Sysadmins struggled to deploy these patches, being out of practice from the infrequency and struggling with non-standardized ways of applying updates. There was also the concern about the quality of patches, many of which introduced more bugs and issues. In the Windows world, I found many companies wanting to wait until an SP2 was released before applying, or even upgrading, many systems.
    That changed, with many companies moving to regular patches, and standard ways of applying, or even slipstreaming, patches easily onto machines. I welcomed the Microsoft Patch Tuesdays, as this provided a regular release, an expectation, and both admins and users became comfortable with the idea of regular patches. I haven’t loved the auto patches in Windows 10, but I find myself agreeing with Troy Hunt that we should just be patching. In an organization you may want someone to be responsible, but for home users, just patch.
    And, by the way, vendors, you need to do more work, and be more responsive to any issues that come up from patches. Your quality issues lead to greater security issues.
    If you want to ready about the WannaCry issue, there’s a good general post, as well as some guidance from the SQL Server perspective. If you haven’t patched, that’s something you should do ASAP, and while you’re at it, be sure you have the latest security patches for SQL Server applied. I’ve got a series of Build Lists at SQLServerCentral, one for each version. I’m still nervous about applying Cumulative Updates too quickly, but I certainly would download and have them ready, perhaps applying each a month late once once the early adopters have had a chance to report any major problems.
    Patching is a reality for the modern software world. We get regular patches for applications, but our core infrastructure (including servers, desktops, and various devices) also need patches to the OS and platforms. There’s a balance between ensuring stability with known softwre versions and keeping up with patches to prevent problems. We need to find a balance, which is probably different for each organization, and re-evaluate periodically if we are updating an an appropriate level. I lean towards fewer patches when I can, but I always want to keep up with security patches. We never know when someone will take advantage of those.
    Steve Jones

  • The Power BI Pivot

    This week we had quite a bit of Power BI content appear on various blogs and sites. Some of this was normal traffic, with Power BI gaining popularity and more data professionals writing about the topic. There are always quite a few blogs from The Guy in a Cube, including a look at how to better use colors, but I also see more and more posts from others. We had others about R in Power BI and custom visuals.

    The big news this week was the announcement of the Power BI Report Server from Microsoft. This is coming late in the second quarter of 2017, according to the SSRS blog, which I assume means in the next month. The second quarter is almost over, and with Microsoft ending their fiscal year in June, I’d expect this to release sooner than later. There is also a Power BI Premium, which apparently allows an enterprise to purchase capacity in the cloud and then concurrently deploy Power BI reports on-premises.

    Many people have been looking forward to this. I’ve been hearing from some reporting authors that we were going to get Power BI rolled into SSRS as an option for SQL reporting. Apparently we are, but there are some new costs here. This may change your calculation of whether this is a good move for your organization, but certainly I think that Power BI is one of the better reporting engines I’ve seen.

    The licensing is confusing, and I liked in a few other thoughts on the announcement from others, such as Dan English and James Serra. Both note that the free tier gets to connect to the same data sources as the Pro previously could (including on premises data), which is good. The storage quota increases to 10GB, up to 8 refreshes a day and a bit more. Pro is still $10/month, and apparently is still needed for those that spend the money on Premium if they author reports or export to Excel, even if you have Enterprise Edition. At least, that’s what Mr. English notes in the license terms. Alternatively you can spend a few thousand dollars a month for the cloud version and let all your employees access reports in the cloud or from a local report server.

    Power BI has been one of those amazing products that I’ve been very impressed with in the technical sense. I can build amazing reports, and get great interactivity, allowing me to analyze data in a way that hasn’t always been possible in a visual tool. Almost every time I see a new type of report or some demo, I’m impressed with what’s possible. I love the desktop tool for my own use, since it’s a nice, responsive and easy to understand interface. However the deployment method (originally just with PowerBI.com) and the licensing have always struck me as a bit strange, and perhaps somewhat crippled. It certainly felt that many features didn’t cost me money directly, but there was a cost. With this latest change, it feels like another pivot to try and increase revenue, but not in a way that’s attractive to me.

    Microsoft wants to make money, and they’ve certainly invested money in the Power BI platform, so they get to make the rules. I do like Power BI, and if the costs make sense to you, and there’s enough value, this is certainly a very powerful visualization and reporting tool that I can only imagine will become more and more useful over time.

    Steve Jones

  • More Power in Your BI

    I’ve seen lots of data visualization tools over the years. Cognos, Microstrategy, ProClarity, and more. While I haven’t been a BI person for most of my career, in many of my positions, someone has wanted to try some new tool and eventually I’ll get involved somewhat. I remember first seeing the OLAP cube browser in SQL Server 7, and showing my boss a quick demo. While it wasn’t something that you could let an end user have, it also got my boss excited, and they started a project as I was leaving to implement some OLAP for the sales department.

    When I first saw Tableau years ago (2008?) at TechEd, it was the first tool I’d seen that was really slick for the end user. I expected they’d grow quickly, and they have. Many people have looked at the Tableau tools as the standard for data visualization. For years nothing looked close, and as much as I appreciated Microsoft moving PowerPiviot and other tools to Excel, they weren’t as nice to use as the graphical tools.

    That changed with Power BI. When I first saw this tool, instantly I could start to see the power of the tool. Even altering and changing a tool had an ease and power that I hadn’t seen since the Tableau tools. The initial release had lots of limitations and was just on the web. It was slow, and refreshes from remote data weren’t great. There were security limitations, and I wasn’t sure that the vision for the product made sense from Microsoft, even though I could see tremendous potential.

    Things have improved, and I am somewhat amazed with the power of Power BI. A simple sales report is interactive. I can click on something intuitively and the values I see will adjust to focus on that item. What’s more, other graphs on the screen adjust. However, I don’t have to stick with simple graphs, I can add better imagry, such as this wine report, or this airline maintenance report that drives work. I was especially impressed with the analysis of Stephan Curry (which won the contest). Some of those reports I couldn’t imagine trying to build with other tools, especially SSRS.

    Power BI is a great tool, and if you need to provide visualizations for end users, I’d urge you to take a look. The Power BI Desktop tool is great for modeling and working with data. I find myself using that for quick looks at data, building a graph in a way that’s easier than in Excel. I went through Microsoft’s EdX course, and was somewhat amazed to see the vast capabilities available in the product. Guy in a Cube, Adam Saxton, has a YouTube channel where he and Patrick LeBlanc (@PatrickDBA) bring you constant training and tips on how to get the most out of the tool.

    What’s more, I keep finding more and more Power BI blogs and posts that I can add to Database Weekly every week. So many people are experimenting and finding ways to better analyze data with Power BI. This week we have a continent slicer, data privacy, collaboration, and more. With montly releases, I find that people are constantly digging into the possibilities and helping you learn with them. There are even developers building custom visuals that you can download. There’s even one for acquarium lovers. If you want to learn about these, Devin Knight blogs regularly about custom visuals and we include many of those links here and on SQLServerCentral

    Power Bi is a great way to empower your users, reduce your reporting load, and make everyone happier. Power BI is a great way to let users experiment with data and actually decide what reports are worth investing in more with IT resources to perhaps optimize the performance of certain visuals. Give it a try to day, especially the desktop version, and see how you can easily start to examine data that might be important to you. Even the SQLDBAWithaBeard finds Power BI helpful.

    Steve Jones