Voice of the DBA

Tag: sql server

  • A Joke Come True

    Yesterday was the opening of Microsoft Connect 2016. If you didn’t have the chance to watch the opening keynote, I’d recommend you do so. Mostly because one of my jokes, or perhaps a dream, has come true. SQL Server runs on Linux, and you now run it yourself. I know Microsoft has talked about this for most of 2016, and they have used it in demos, but there haven’t been public bits available.

    I’ve been lucky. In fact, I’ve been running SQL Server on Ubuntu since April of this year as part of a private program. I’ve been testing various Redgate Software tools as well as my demo code from presentations and so far everything has run. This includes the tSQLt framework and my tests that make use of SQLCLR. This includes my AlwaysEncrypted demos. I was very impressed that these features just worked, as though this instance were any other SQL Server that I had installed.

    What’s more, the installation and updating of SQL Server on Ubuntu, using apt-get, is far, far smoother than the installation on Windows. To be fair, this is a default installation, and I haven’t tried to set up all the various options and settings that are available on Windows. The various additional subsystems (SSIS, SSAS, SSRS, etc) aren’t available as well, but still, it’s a very smooth process. As I’ve updated various release candidates across the last few months, I run two commands: “apt-get update”, and “apt-get install mssql-server”.

    I don’t know if this is a good business decision for Microsoft. Time will tell, but I can’t help but think that the addition of another platform on which SQL Server can run is good for the product. More people will consider SQL Server as their database platform, with all of the powerful features and capabilities that brings to a database driven application. I suspect this will mean that many developers working in non-Microsoft environments with Linux, Java, PHP, and more will begin to consider SQL Server as an alternative to PostgreSQL and MySQL, in addition to Oracle and DB2. Certainly there is still a cost to using SQL Server, but it’s an incredibly powerful platform, one that now has a more consistent programming surface since almost all features are now available in Standard as well as Enterprise with SQL Server 2016 SP1. Getting RLS, Columnstore Indexes, In-Memory OLTP tables and more in all additions is a major win, and another of the pet peeves I’ve wanted changed for years.

    I am very interested to see how people view these changes, and if they will impact you? Do you want to run SQL Server on Linux? Since In-Memory OLTP and other features are now on Standard, are you interested in upgrading to SQL Server 2016 now? Perhaps you’re a little more excited about Microsoft and SQL Server with all of the new development changes announced yesterday? Let me know today.

    Steve Jones

     

  • A New Recovery Tool for Your Toolbelt

    Those of us that administer databases and are responsible for ensuring the integrity and availability of data know that being able to restore systems is a core skill. In the SQL Server world, this should mean more than just being able to run a RESTORE DATABASE Sales FROM DISK = ‘Sales.bak’. Many of us would easily recognize that we should be able to use the WITH MOVE option. We should also understand when to use (and not use) NORECOVERY and STANDBY. Perhaps more importantly, we need to know the default option is RECOVERY.

    If we delve further, perhaps we could debate and discuss when a tail log backup is needed. Many might mention how to programmatically use FILELISTONLY and HEADERONLY to generate scripts.  A few of you would note that restoring databases means restoring logs as well and an understanding of the STOPAT option is important. Perhaps learning the ins and outs of restoring a filegroup is important in your environment. There are many nuances and options with restoring a database that a system administrator could learn.

    Apart from performing a restore, a careful DBA might think that we should try to avoid restoring databases if possible. Perhaps a DR system using Log Shipping can be used to recover data quicker than a database restore. Maybe periodic snapshots are valuable in recovering from those “whoops” mistakes. As our data sizes grow larger, we may need to develop more creative ways of recovering from user mistakes.

    Recently I saw the SQLCAT team had written a post on using Temporal Tables to recover from an “oops” mistake. If you haven’t looked at these structures, they are new in SQL Server 2016. Temporal tables, or system versioned tables, allow a user to view a row as of a certain timestamp, which can be especially useful for DML mistakes. Have you ever updated every row of a table to the same value because of a poorly written query? I have. A temporal table would have been handy in this case. Perhaps even more useful are the cases where a table is receiving regular inserts and updates, which can be very difficult to recover from with backup files.

    Certainly the majority of us don’t yet have temporal tables, and we might not even set up the option on many tables even if we were running SQL Server 2016. Over time, however, some of us will use these system-versioned objects, and perhaps we will run into a situation where recovery using temporal queries is more efficient than restoring an entire database. If that were to happen, wouldn’t you want to have some skills in this area?

    As SQL Server continues to evolve, many of the techniques and skills we’ve built across time will continue to work. There will be new ways of accomplishing work, based on features and enhancements that we ought to consider, especially when they might be more efficient than our past techniques. As you work with SQL Server, I hope you keep learning about the “new” ways that you might accomplish the tasks for which you’ve developed solutions in the past. Sometimes the old way might prove to be a better choice, but you won’t know that unless you learn, practice, and measure the effects of a new method.

    Steve Jones

    The Voice of the DBA Podcast

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

  • What’s Your Backup Speed?

    I ran across a thread recently where someone was looking to estimate the time it too to perform a 600GB backup. I’ve seen other threads with similar questions. The general advice is that you need to run a backup and see how long it takes. After all, your hardware, your workload, the compression settings, and more will affect your backup.

    In researching speeds, I didn’t find much info out there, so I thought I’d start a poll. This might be fun for you, so I’m hoping quite a few of you will answer.

    How long does it take you to back up 1TB?

    I know some of you don’t have a 1TB database, and probably a few of you have much larger ones. However, look at your backup and extrapolate (or interpolate) the time it would take to complete a 1TB backup. Assume that your hardware would scale appropriately to allow you to run a 1TB backup if you actually had that much data.

    If you can share your setup, or your configuration, that would be very interesting. I know some of you use striped backups, some compress, some use third party tools to help, and maybe you even have another way to actually get data from a live system into a backup file.

    Over the years I’ve seen some amazing improvements in the backup and restore functionality that SQL Server provides. I think it’s one of the most solid subsystems in SQL Server, and I’m looking forward to seeing just how fast some of you can create backup files.

    Steve Jones

    The Voice of the DBA Podcast

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

  • The Forever Technology War

    The Forever War is a classic science fiction novel. I’d highly recommend it if you enjoy that genre, but even if you aren’t a science fiction fan, this is a story that can really make you think. The gist of the story is that as various parts of our military travel faster than light, we might encounter foes (or other units of our own military) that are substantially ahead of, or behind us, in technology. All because of the nature of time travel. Recently I ran across a few situations that reminded me of this story.

    The first item was at home, where my son’s computer acted up. He likes Windows 7 and has stuck by it. However an update caused boot corruption and he wasn’t able to repair the system. In the process of trying to get repair things, we dropped into the command line to run chkdsk and bootrec. He wasn’t sure how those worked, which makes sense. He’s 17 and most of his life has been spent in GUI OS’s. However, once we realized he needed to reinstall, I told him to copy things off his boot drive to another drive. I showed him a bit of moving around the command line, making directories, listing contents, copying files. As I was walking away, he asked, “how do I make a folder?”

    A folder. Not a directory. We’ve had not only a technology shift, but also a nomenclature change in computing. It had never occurred to me that my son wouldn’t know basic command line calls. As a side note, he greatly appreciated the /? parameter to learn more about commands.

    The second situation was at a conference, where I was talking about continuous integration and deployment. A person asked about SSIS packages, and how would you move these with a tool like TeamCity or VSTS. I asked how they’d do this manually, and said that we would just program that. This person asked how they’d program the steps they take in SSDT. My answer was to use the command line, but this person had no idea there was a command line. Their whole career had been spent in GUI systems and they assumed that was the only way. Hint, pretty much everything can be run from a command line.

    Those situations showed me that we have a technology shift in place. There are things that some of us have grown up with, used as a base to learn more, and we can assume that others have similar paths. After all, wouldn’t you need to know about x to learn about y?

    Not necessarily. The longer I am in this business, the more I see that the experiences and knowledge of different people can be vastly different. A person that knows how some technology, say T-SQL and SQL Server security might have no idea how replication works. Or they might know AlwaysOn well, but couldn’t implement an Extended Event session. There are gaps and holes in the knowledge that each of us has, and assuming that others have similar understanding of any particular item can cause us problems when we try to work together.

    Understanding, transparency, sharing knowledge, and appreciation of diverse experiences can help us all learn, and work together better. If a situation seems strange or a person is confused, perhaps a few questions on their frame of reference of the situation is a better response than assuming they are incompetent or telling them to RTM.

    Steve Jones

    The Voice of the DBA Podcast

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