Voice of the DBA

Author: way0utwest

  • Data Lakes

    I heard a new phrase this week: the data lake. It comes from a Radar Podcast episode where Edd Dumbill talks about data as an asset that should be exposed everywhere in an organization. There’s a blog post on the subject as well.

    The idea is somewhat centered on Hadoop, but it could apply elsewhere. Data comes into an organization and then tends to seek and get stored with other data in a large lake. Applications are just a way of accessing the data in the lake, but all the data really lives in a large Hadoop lake of information. In some sense, this isn’t far away from the “single view of the truth” that I’ve seen plenty of organizations attempt. In a relational world this means all data moves from OLTP systems to a large data warehouse, and is then moved to smaller data marts (really warehouse subsets) and is accessed from there.

    It’s a good idea in theory, but in the practice of trying to move data around with any velocity between users, with all the copying, cleaning, transforming, and more going on doesn’t work. OLTP systems are needed because there are transactional actions that must be completed quickly and accurately. Moving this data to other systems becomes harder as data volumes and the sheer number of clients (whether users or other systems) increase. The idea that we can keep all of our processes working quickly enough that users won’t get frustrated is likely a dream. The more a value exists in a set of data, the more users will access it. The more accesses, the slower it often becomes, which starts a cycle of smaller subsets of data and applications that subsist on those small data puddles.

    Excel is probably the most common example of a data puddle that exists in your organization. A set of data, perhaps out of date, but useful enough to make decisions based on. Infinitely flexible and convenient enough that updates, changes, and more often spawn more and more puddles where the information never gets transferred back to the large lake of a database, whether that’s an RDBMS, Hadoop clusters, or something else.

    I think the idea of a large data lake is great, but in a practical sense, much of an organization’s data will never live in the lake. If it does, it will most likely be data that’s been superceeded by information in a puddle somewhere on an employee’s laptop, tablet, or personal cloud.

    Steve Jones

  • Hardware Upgrades 2015

    I wrote awhile back that I ordered a new laptop, and I’m excited to have that coming. However, I had a few more upgrades to add to the machine, and I decided not use to use Toshiba to add them.

    These are a few items I’ll use for speaking in 2015, and I’m excited to get a better demo setup for showcasing SQL Server and Red Gate tools.

    Memory

    I can’t demo on less than 16GB of RAM. It’s disappointing as there are soooooooooooo many more choices for hardware if I could use 8GB, but I can’t. One of our demo VMs needs 6GB, and really wants 8GB. I used Grant as a testbed in a few places, and even when he built his own VM, stripping things down, it didn’t run well.

    I ordered the laptop with the base 4GB from Toshiba, and went to Crucial for a memory upgrade. For $100 less, I got a 16GB kit for the machine. There might be better choices than Crucial, but they’ve always worked well for me and I like them.

    Hard Drive

    I need disk space to keep a few demo VMs handy, and the 128GB that comes with the laptop isn’t enough. A 512GB from Toshiba was $400. I found an end of the year deal from Crucial for an mSata drive at $270. Hard to beat that, so I’ll put that in the machine once it arrives.

    External Storage

    I didn’t want to throw away the 128GB drive away, so I also grabbed a small enclosure from Amazon that will allow me to use that 128GB SSD as a backup for my main demo VM. I can connect it to any other laptop (likely Grant’s in an emergency) and work from there.

    Travel Wi-Fi

    I’ve been trying to be more economical, and looking to potentially carry two machines for demoing things. Ideally I want a small enclosure PC that I can run demos on, and connect to it over RDP. The first step here is to get a small router.

    I found a small travel router, with two LAN ports that I’ll be testing in that capacity. If that works well, I may end up trying to get a tablet of some sort as my backup machine for classes and training, some of which we are looking to start in 2015.

  • Multiple Backup Files

    I’ve been writing a little PowerShell lately that will back up databases, move files, and restore them. I’m often testing things, and having scripts to quickly and easily move files is very handy. After one of my posts recently, a reader asked if I’d considered multiple files and how to handle them in scripts. I confessed I hadn’t, mostly because I haven’t had to deal with them.

    In my career, I’ve tended to work with small to medium sized databases. I’ve had young children for a large portion of my SQL Server career and had no desire to babysit multi-hour (or multi-day) restores when things break. I know some people have been through those situations, and good for them. I just know I’ve had enough issues with the low-GB sized database, and haven’t been interested in supporting TB sized systems.

    However that likely wouldn’t be the case in the future. More and more companies are collecting and storing data that reaches into the TB, even in small companies. The rapid advances in sensors, development tools, and cheap storage means that many people are dealing with hundreds of GB in at least one of their databases. That means for a reasonable RTO, making quick backups, and maintaining good performance, multiple backup files are becoming a necessity.

    Is that really the case? Data volumes are exploding, but you many of you using this feature? I wanted to see how many people have implemented, or at least thought of striped backups. The poll this week is:

    Do you have any databases that benefit from backup to multiple files?

    I’ve consulted with clients that accidentally produced striped backups and then lost one of the files. That’s never a good situation, and it’s bad news to have to give as a consultant. However, I’m sure many of you have consciously implemented striped backups because they can perform better than single file backups and make for quicker restores. Others of you may suspect (or have tested) striped backups will help your systems but haven’t gotten around to setting things up.

    Let us know if this is a common feature you use, or is it still something esoteric that you have no need for. And if you have put striped backups in place, have you tested a striped restore? I certainly hope so. Let us know either way.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Building Connection Strings

    I ran across a great article on Connection Strings from my good friend, Allen White (b, t, c), recently. Quite a few of the items he mentioned in there, I knew, but it was a good review to stop and think about how to actually connect to systems. Especially as many of us will be moving to more complex, distributed environments that might include cloud (public or private) resources.

    The Address

    The address part of the connection is simple for many people, but often misunderstood if you’ve only worked in single domain with default instances. Many people are used to just putting in the name of the server and connecting from SSMS or some other dialog based application.

    However I do think it’s important to understand that connections to SQL Server are just like browsing the web. For example, I can do this:

    tcp:xxyyee.database.windows.net, 55345;

    This is a connection of the format:

    protocol:name of server, port;

    We don’t often worry about protocols, but in case you might want to ensure it’s a TCP connection, specify it. This is like:

    http://www.amazon.com

    In this case, I’d specified http. However you should try ftp:, https:, or other protocols in your browser. You might be surprised how often these work.

    The server address is a fully qualified domain name (FQDN). In a single domain, local network, you often just put the name of the host, but you should be aware that any address works.

    The port is 1433 by default, but you should be aware that this can be changed by the SQL Server administrator in the Configuration Manager, in Services, or even with firewall rules. If you’re ever in doubt, specify it.

    Security

    I hadn’t thought about security being more than trusted or not, but Allen pointed out a couple of settings I need to use. He talks about using SSPI to ensure Kerberos is used and also to set a value to encrypt the connection string. Both are good ideas.

    Five Minutes of Learning

    Read Allen’s article and learn a bit more about connection strings, or review your knowledge. Little pieces like this can help keep your skills sharp and allow you to pick up tidbits of knowledge that might really help you some day.