Voice of the DBA

Author: way0utwest

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

  • Testing

    It seems that software always contains bugs. No matter how much time and effort is spent building an application, there will be issues. Sometimes this is because of a lack of testing, and sometimes this is because of poor testing, but in any case, the expectation that we will test our code is becoming more prevalent as we depend more and more on computer software. Users expect our software to work.

    It seems there is never enough time to properly test software after it is complete. Perhaps your deadlines are too tight, perhaps there aren’t enough resources to devote to comprehensive testing processes, but it really doesn’t matter. We will never have enough resources in our QA and testing teams to do as much testing as we would like. We need to expect this and find ways to raise the quality of our work, given those constraints.

    One solution to increasing code coverage and ensuring more testing takes place is to move some of the testing burden into the development process. While this sounds like a bad idea, overburdening developers that already struggle to meet deadlines, I’d note that part of the burden of development is fixing the mistakes they make. Perhaps a bit more testing in the development process will help us release fewer mistakes.

    The idea of regular, repeatable, automated unit testing has become quite commonplace in many software development tools and environments, but it hasn’t caught on in database development. There are some frameworks for testing T-SQL, and I’d encourage you to look at our Stairway for TDD as a way to get started or download tSQLt and give it a try.

    We can fix mistakes after our software is released, or we can fix them before the software is completed, but we’ll be fixing them either way.

    Steve Jones

    The Voice of the DBA Podcast

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