Voice of the DBA

Author: way0utwest

  • Learn about SQL Source Control in Redgate University

    I love the SQL Source Control product from Redgate. It’s not perfect, and it can be slow to run at times, but the simplicity of what it does, of getting my code quickly and easily to a VCS is fantastic. I really appreciate it.

    This is one of the tools I enjoy demoing and showing off how to ensure you get all the code from you system stored away. I wish I had been able to purchase this product years ago when I was building database software as my day job.

    We now have a course to help you learn how to use SQL Source Control at Redgate University. This is a series of 10 sections (as of now) that cover a variety of ways in which you can capture development code with SQL Source Control and even deploy those changes to another database.

    Give the course a try, and see what you might learn about this product. We’ve got other courses as at Redgate University, with more coming all the time.

    If you’ve got ideas or suggestions for the courses, send us a note at https://www.red-gate.com/hub/university.

  • We Need Data Privacy Consistency

    For most of the last year, I’ve had quite a bit of my time devoted to the GDPR and related topics. My company is affected, as it’s based in the UK. Not only must we comply, but we know many other companies must as well. As a result, some of our product focus was aimed at helping companies solve their data privacy issues, especially with regards to data.

    That continues to be a good idea as the GDPR isn’t the only regulation out there affecting organizations’ data handling practices. There are other laws around the world, but the US is a big market, one of the biggest we have, and we are seeing increased need in the US for the same types of data privacy and protection solutions mandated by the GDPR.

    California recently passed their own data protection legislation, and it’s leading the way in the US. Tim Ford wrote a short piece on how this affects his company. He notes that as a consumer, he’s glad to see stricter data handling practices being required. However, as a business owner, he’s concerned and I think there is some basis to be worried.

    There are other laws that might pass soon in the US. New York has a bill, Colorado has signed a weaker, but still new, law. Other states are considering items, but the US Congress has yet to really move forward on any legislation, which might lead us to have multiple data handling practices that are required. That would be a nightmare, much more difficult than the hiring and tax practices of different states.

    I couldn’t imagine having to work with different processes, and certainly wouldn’t want to have more restrictive laws being passed in the future that might cause us to change practices multiple times. I can only hope that the US gets a common law for all our states, and that the practices are in line with what the GDPR requires. Other countries have used that as a basis for their laws, and I can only hope the US does the same.

    Steve Jones

  • How Do You Setup Your Instances?

    I’ve set up a lot of SQL Server instances in my career. I’ve gone from manual only setup in SQL Server 4.2 to more automated means in the latest versions. The easiest was actually in Azure where I only need to specify a few parameters for a PoSh cmdlet. However, unattended setup is pretty easy as well for local SQL Server instances. If you’ve never done it, you’re missing out. There are plenty of other ways to do this with tools like Chef and Puppet, AMIs in AWS, and more.

    Erik Darling wrote a post recently called Setting Up SQL Server: People Still Need Help. Erik’s point in the piece is not that installing SQL Server is hard, but that many people stick with the defaults once they’ve installed the instance, never changing anything. There are some basic things that you’ll want installed all the time, so having a repeatable process is important.

    This week I’m wondering how you install instances in your job. If you need to add a new SQL Server for production or development, what do you do? Let us know your process and procedure.

    I don’t set up too many instances, and in fact, I mostly add them as a lab instance on one of my machines. For the initial install at times, I’ll just run through the manual install, but I then have a quick config script that I use to change a few items, but very few. In most cases, I don’t do much more than add a few logins, limit memory, and enable the DAC. For the cases where I want to add a few different instances for testing, say for looking at patches or using mutli-server features, I’ll use an unattended install script.

    There are some amazing ways people have created for repeatable installs, such as the Finebuild project. If you’ve got a way that works for you, let us know. Just be sure that whatever repeatable process you use changes some of the defaults and ensures your SQL Server is better prepared for any workload to come.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Defending the RDBMS

    A few weeks ago I ran across an essay from Randolph West called, Relational Databases Aren’t the Problem. This was a response to another essay that made a case for relational databases being bad for many businesses. I thought that both pieces were interesting for different reasons. Certainly I don’t believe the the RDBMS is perfect, and it certainly can be hard for developers to build software that interfaces with a relational system.

    The original complaint about the RDBMS is somewhat rambling and deceitful, in my opinion. It is an excellent study of how to use a few concepts to confuse and create doubt in a casual reader. If I weren’t reading closely, I might fall for a number of the issues that exist with relational databases. However, in my mind, part of the issue is that quite a few of the issues that are discussed aren’t problems with relational databases, but often the issue with poorly developed software or design of the entities and relationships. I find myself even more disappointed that the author hasn’t really addressed any comments, but rather just pasted a link to his followup article.

    I do think that the defense from Mr. West does a good job, though it also misses some of the primary issues we struggle with relational databases. There are problems with the knowledge of how to build a well performing database, both from application developers that view this as a necessary evil as well as experienced database developers that don’t regularly improve their skills and try new design techniques.

    I also think that both of the pieces fail to address the issues of gathering and working with multiple rows of data. The second discussion of “doing without databases” really implements its own database management structure, which may work well, but is fraught with issues such as the concurrency issues of multiple users searching and scanning through data without having indexes. While indexes are overhead, they are necessary as hash buckets aren’t necessarily feasible for all the properties in a class. Also, if you end up building them for multiple properties, you’re building an index. There’s another good defense of some of the issues here.

    I do think that keeping more data in memory and synchronizing access to structures sounds great, but scaling that out to multiple systems, and ensuring consistency at high volumes, not to mention potential loss of data issues from crashes are a problem. Having a write ahead log in SQL Server does a wonderful job of ensuring we can handle redo/undo on system restart. The method presented doesn’t necessarily ensure this, though perhaps accepting some data loss from high concurrency changes is OK for many applications.

    I will say that the idea of all data in memory is interesting. I had to stop and think about how many databases really have more than 1TB of data. If we throw out indexes, does this cover most data stores? I bet this does, though that doesn’t mean that there aren’t issues with using in memory array structures, with widely varying data sizes.

    Would I use an in-memory data structure for software? It’s tempting, but honestly, I wouldn’t. The value of data is too high, with potential issues from poorly implemented ACID control structures. Plenty of issues have been found with different RDBMSs over their years, and even some in NoSQL systems. Thinking that I could avoid any issues and protect data is something I wouldn’t even try. After all, if there is some error, I’d prefer it from a system that many people use, rather than one I tried to emulate for no good reason.

    Steve Jones

    The Voice of the DBA Podcast

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