Voice of the DBA

Tag: sql server

  • The End of Support

    With the accelerating pace of development and more frequent releases, we are seeing a bunching up of SQL Server support milestones. From 2008 through 2016, we had a new version every two years. Last year we saw another version, which means 5 versions in a ten year span. With 5 years of main support and 5 of security patches + extended support, Microsoft has quite a few active branches of code.

    We now see SQL Server 2008 and R2 coming to the end of support on July 8, 2019. There are plenty of options for customers, including doing nothing. After all, if you’ve been running SQL Server 2005 or even 2000, you’ve been without support for some time. That might not be a policy of your organization, particularly if you adhere to any standards or are bound by regulation. In those cases, you may need to have active support under contract.

    There is a good argument to be made that you should have active support for your core systems, such as the OS or your database platform. If a bug or security issue is discovered, would you want to upgrade on a very short timeline or apply a patch to your existing version? Think about running SQL Server 2008 today and a critical security problem is discovered in October. Do you want to upgrade to SQL Server 201x this year? Or would you wish that you would have planned and executed an upgrade in June and have to apply a GDR patch in October?

    I tend to view patches as much lower risk than an upgrade, mostly because the number of changes is smaller, and an upgrade reduces my options, not to mention often increases my costs. That might change over time, as I upgrade other instances and gain experience with a new version. SQL Server 2017 still feels new to me, but SQL Server 2014 and SQL Server 2016 feel older. If I had to perform an emergency upgrade to an instance, I’d be more comfortable moving from 2008 to 2016 than 2017 right now.

    I wonder how many of you view the upgrades as more risky? These days the migration tools analyze for keyword issues, and many of those can be alleviated with compatibility levels. Perhaps some of you aren’t worried and will run your SQL Server 2008 instances until they die, only upgrading if forced. If so, let us know.

    If you’ve got R2 instances, however, please get rid of them. The sooner we get rid of this horribly confusing version name, the better.

    One late note, after this was written, Microsoft offer to add additional extended support if you migrate to Azure.

    Steve Jones

    The Voice of the DBA Podcast

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

  • String or binary data would be truncated

    Microsoft, please fix this. This issue is still out there, and once again, it’s the top voted on item for SQL Server. Everytime I see this:

    Msg 8152, Level 16, State 13, Line 1
    String or binary data would be truncated.

    I’m annoyed, and I’m not alone. If you’ve had to load data into SQL Server, and you’ve every encountered this error, you’ve probably been frustrated. In fact, when you get this particular error, you might wonder what to do.

    Many of us find this error to be a problem because we don’t know where the error is located. In fact, we often end up jumping through hoops to somehow track down the offending row. We might scan through our input file. Or we might build a parallel process to load the data into a series of MAX columns and then use T-SQL to search out the data, change it, and then export it again for us in our ETL process. Or we might throw up our hands in frustration and go for a walk.

    SQL Server developers, if you feel any of these things, go vote for this item.

    When there is a mismatch between our input data and the schema, there is a lot of friction to getting work done. While DBAs might be willing to track down the invalid rows, developers hate it and business people often get confused. This one reason why developers dislike relational schemas and look for easier data stores, like NoSQL databases. Trying to figure out that there’s one name in a list of 1000 that’s got 31 characters and doesn’t fit in a 30 character field is a pain for anyone..

    I have no idea of the effort to fix this, but this isn’t a syntactical sugar item. Microsoft, this is a piece of work that can be extremely helpful. Pick a method to solve thisand get to work. Ignore the error rows and output them, let us redirect them to another table, or some up with some other solution. Make something simple that works, but please, just fix this.

    It should be embarassing to you that this is still a issue that is reported and voted on in SQL Server.

    Steve Jones

    The Voice of the DBA Podcast

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

  • How Far Back?

    At the Redgate SQL Privacy Summit recently, there was panel I hosted that I thought was really interesting. At one point someone asked a question and there was a bit of a discussion about the right to forget in the EU. One of the fascinating things wasn’t about the rights, but about backups. One of our panelists noted that we often keep too many backups, most of which are really useless.

    This portion of the discussion started by asking how many backups people keep. Many of those in the audience kept many backups, sometimes dozens. Many of them had some rotation scheme that determined how many files were kept over time. I know I’ve worked in places where we kept over a week or two worth of backup files, often along with a monthly or quarterly backup for a year and then multiple year end backups in perpetuity. I guess since we didn’t really know how long those backups were valid as we just marked some tape as the end-of-year one and left it in a vault somewhere.

    What was also interesting is the panelist asked if many people had restored a backup more than a month old. Almost no one had, but a few had restored a year old backup. I think that’s common, and I find I’ve only restored one backup more than a month old. That one was actually for a legal case, and it was six or seven years old. I actually had to find someone at Microsoft that could provide us with an old version of an SP to restore the database.

    I’m wondering today about the rest of you. Not if you have done it, but would you do it today? How far back would you go for a backup of an important database? Assume this isn’t any requirement to find the data at a point in time, but for recovery purposes in a disaster.

    For me, I’d expect that most of the systems I’ve managed really would have struggled to go back a week. After that point in time, so many transactions are missing that there are serious repercussions. This is why I’ve often ensured I had automated restores happening all the time, since I need to be sure that we can restore from yesterday or two days ago. If I went back more than a week, it would be because we had corrupt backups. While the business would probably survive, I’m not sure my job would. If I hadn’t been able to produce a workable backup for the last week, I think most organizations would be justified in terminating me.

    Whether they would or not is another debate.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Update Oddness for SQL Server

    I’ve been meaning to patch some of my development instances. With a little spare time while doing other work, I started the process the other day. My first step was to patch SQL Server 2016 with SP2.

    This went as many patches have gone for me, which is fairly boring and easy. Other than having to wait to accept the R terms and then clicking next, it was quick. Afterwards I rebooted.

    Then things got interesting. I started the SQL 2017 CU6 patch. This extracted, as other patches have, but then I got a message that I wasn’t running this under enough permissions. To be clear, after extraction, the SP2 patch did give me a UAC prompt.

    I stopped and then re-ran the patch, right clicking and running as administrator. After a few Nexts, I got this screen:

    2018-05-21 14_47_47-Install a SQL Server 2017 update

    I haven’t seen a patch ask me to stop SSMS. I’m still not clear if this is necessary, but I did stop SSMS. The patch worked fine, and strangely, re-running the patch later didn’t produce the same messages.

    Odd, but things seemed to work and my system was patched, though even without SSMS, I still had a pending reboot.

    Any thoughts or explantions appreciated.