Why Is It Complicated?

  • Comments posted to this topic are about the item Why Is It Complicated?

  • My best guess, it's not been prioritized to the team or that they have run into some issues they have not been able to solve. It is a good low costing product but it has it's issues, for instance RESEED is still bugged and that is a very basic simple feature, one would think.

  • the fact that it is complicated, keeps us working. SQL Server is too simplistic and gui driven already. I see enough of people with next to nothing of experiences, posting basic questions.

    there is room for improvement, but if we always got what we wanted, would we be happy?

    [highlight]Recommended Articles on How to help us help you and[/highlight]
    [highlight]solve commonly asked questions[/highlight]

    Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
    Managing Transaction Logs by Gail Shaw[/url]
    How to post Performance problems by Gail Shaw[/url]
    Help, my database is corrupt. Now what? by Gail Shaw[/url]

  • Silverfox (11/2/2009)

    there is room for improvement, but if we always got what we wanted, would we be happy?

    I would.

  • Backups and restores are too complicated. It's as if it has been created that way to make sure DBAs keep control of such areas, but I imagine it is just because there hasn't been an obvious need or demand (in the eyes of Microsoft) to make it simpler.

    Many moons ago I worked for a company that had an inordinate number of small customers scattered across the country, all with SS 2000 MSDE installed. I wrote a VB/SQL DMO package that controlled the backups and controlled the restores if required - prompting the users for the correct disks. They had to get a password from us if they thought they needed to restore as it's surprising just how often they'd panic and want to do such a thing for no good reason, but it was as automated as it was sensibly possible to make it.

  • I don't think it is complicated. SSMS will build up a restore job for you starting from the latest full backup to the latest tran log backup. all the information needed is in the msdb database. you can learn all you need about backup and recovery in a couple of hours if you just take the time to read up about it.

    Now, Oracle, that is ridiculously complicated, you need a week long course to learn how to restore an oracle database.


  • I agree with you 100%, and more, this is the most talked about topic amongst our technical staff, associates of mine, and customers as well - That is of course, Microsoft's addiction to taking the most simple of tasks and making them as needlessly complex as possible. If you think SQL Server is bad, try working with Team Foundation Server - its supposed to be a simple system for managing code, and yet Microsoft has once again loaded it down with tons of features no one needs, and endlessly complex interfaces for the most simple of activities.

    The reason for this? Simple. Its the key difference between intelligence and common sense. Microsoft has thousands of highly intelligent people working for them, but I doubt there are even 10 MS employees with basic common sense. You can see this in just about all their products. Using MS products is like trying to clean your fingernails with a backhoe - a great powerful tool, completely inappropriate for the job at hand.

    There is one glimmer of hope though - after the Vista debacle, MS has shown a small inkling of "getting it" with Windows 7. I hope this small inkling becomes a tidal wave trend and that SQL Server, Visual Studio and all of MS product line starts to reflect more common sense design than "intelligence" design where they load down products simply because they can.

    There's no such thing as dumb questions, only poorly thought-out answers...
  • Backups, if you are only using them for disaster recovery, have been made fairly easy to manage in SQL Server. The problem, as I see it, is that I don't use them for disaster recovery as much as for development. We have a couple dev boxes that are restored at will by anyone in the department. If we need to check data from a certain time in the past or if we want to test something on recent data we run a restore to a dev box using backups from the production environment.

    Here's the problem. The simple GUI interface to perform these restores is based on tables - tables that are only current in the production environment. They aren't available to the dev boxes. That means I need my backup files to be named reliably so I can write a script that knows what files to restore based on whatever criteria I require.

    Yes. I could set up log shipping if I were so inclined. I'm not. I've instead taken to writing backup and restore scripts that manage things. I'm using extended properties on my databases to specify which ones get differential and T-Log backups. The scripts I have written are fairly easy to use for the structure I have in place. It would be a different story if I wanted to use partitioned tables (which I'm beginning to consider).

    I don't see this as something that can be easily managed by simple GUI tools. There's so many different ways you can store data, backup data, and restore data that it starts to look like something that a DBA should be intimately involved in. It's not needlessly complicated - unlike many MS products (BizTalk /shudder). It is complicated to the point it actually needs to be to provide us with the level of security we want or need.

    Take a day or two, or maybe a week, and design your backup/restore strategy to meet the needs of your organization. You'll look like a genius (and rightly so) when things go bad and you have things back up and running quickly by running a single script.

  • I don't see this as something that can be easily managed by simple GUI tools. There's so many different ways you can store data, backup data, and restore data that it starts to look like something that a DBA should be intimately involved in. It's not needlessly complicated - unlike many MS products (BizTalk /shudder). It is complicated to the point it actually needs to be to provide us with the level of security we want or need.

    I mostly agree with this. There are a lot of situations, most in fact, where DR is not the primary use case for backup/restore. It's really hard to script/gui/wizard all the various restore situations while allowing for outages and disk space shortages and poorly trained users. ("Put that Sept 22 copy of INT customer test data on the DEV box again." "Dude, that should have said Not In." "I don't care about the Test backups. We need the Prod backups to fit on the stupid SAN.")

    That said, there are *lots* of smaller, simple, vanilla installations out there that would benefit tremendously from a backup/restore wizard with options for DR and simple point in time restoration. Smaller places can be the most vulnerable when bad things happen, and they're the ones most likely to be doing without a trained DBA.

    [font="Arial"]Are you lost daddy? I asked tenderly.
    Shut up he explained.
    - Ring Lardner

  • Are BACKUP and RESTORE really that complicated? Compared to, for example, limitations on recursive common table expressions or all the tricks you can perform with MERGE and OUTPUT? Backup and disaster recovery are arguably the most important part of a DBA's job, so we should expect it to take some effort to master. It may be difficult because the commands are boring, haven't changed much in years, and kind of scary because if you get them wrong it could cost you your job. These commands have so many options because there are so many backup & recovery scenarios, but they separate real DBAs from the .Net developers and recent Access graduates.

    The simpler you make it, the larger the population of morons who think they're qualified to do it. If you have a week-old full backup, a day-old diff backup, and a hundred transaction log backups since then, all you have to do is open the Restore task and give it a time and it will figure out all the necessary commands. Unfortunately, that makes some people think they're experts. If they know how to get the GUI to script all the commands, and take credit for writing them, they're wizards.

    I've been monitoring a project in development where a "Senior DBA" has done over a dozen backups over the course of a week without realizing they were overwriting the same backup file every time (not even appending it!). They know how to click the GUI buttons, but without any real understanding. Giving this person more pushbutton capability to screw things up scares me.

  • Have you suggested it to MS? You said yourself that you didn't think about all the things that needed to be done until you tried to tell someone how to do them. Maybe they just haven't thought about providing a way to simplify the backup process.

  • The simpler, "vanilla", uses are easy to do through a provided GUI. You can set up maintenance plans and do point-and-click restores for that type of installation quite easily.

    And Scott makes the same argument (with a good example) I did about wanting a competent DBA in charge of the critical Database activities. I'm not sure I'd go as far as saying it shouldn't be made easy if it could be easy.

    I just don't see a way to do it unless, as the parent article mentioned, there was some amount of data in the backup files themselves to direct a server in how to process them. This has its own pitfalls though due to the nature of the backup files. A full backup is going to be ignorant of any subsequent differentials and T-Log backups. A differential is going to be ignorant of any subsequent T-Log backups. I just don't see a way to do it easily.

    The same can be said for file system backups and restores. They aren't as easy as people want to believe. A good system administrator can get a file back for a user rather quickly but we don't expect the users or even developers to be able to do that for themselves.

  • Really glad you raised this one Steve. Let's face it we spend most of our time trying to prevent the disaster scenario in the first place. At the very least, the error message could be more informative. E.g...

    Restore failed for Server 'blah'. (Microsoft.SqlServer.Smo)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server(snip)...


    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

    The file or filegroup "Blah_log" cannot be selected for this operation. RESTORE DATABASE is terminating abnormally. (Microsoft SQL Server, Error: 3219)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=3219&LinkId=20476



    No... It is not "OK". How about some content at the end of those URLs?

  • I still don't see what's so complicated about the BACKUP and RESTORE commands. If you need some obscure option, you need to understand how to use it.

    The maintenance plan backup task, on the other hand, is so crippled I've never been able to use it. Same goes for the backup file cleanup task.

  • I never looked at the maintenance plan backup task until 2008 because I needed compression. With 2008 I tried it out. It was easy to use but wouldn't have worked for the things I need to do. I did manage to get it to create the backups I wanted and was able to restore from those backups. Like I said, I never even looked at it prior to 2008 but I could see it being handy for a shop that didn't have a dedicated DBA and didn't need to do anything special with their backups other than having them available for disaster recovery.

    And, yeah, the BACKUP and RESTORE commands aren't all that complicated. It's developing an overall backup/restore strategy that works for your implementation that takes a little more thought.

    I guess I understand the frustration with backup/restore. So many other features of SQL Server are so easy to work with from a GUI that you do get people who aren't really competent in a position of managing it. I was that guy not so long ago but I grew into my title with on-the-job experience and a little help from people here.

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply