Self Describing Databases

  • Comments posted to this topic are about the item Self Describing Databases

  • Efficiency?? Consistency?? What are you?? Some kind of rabble rouser???

    Good points.

    <><
    Livin' down on the cube farm. Left, left, then a right.

  • I don't know if I'd want backup information in the database or not. On the one hand I could see it, and you're right, having information about the backup in the database is a seductive idea. At least for the backup portion of the instructions...

    But...what happens when the database is corrupted? Where are the restore instructions? Same database? Then how do you restore?

    And if you put the restore instructions in the instance instead of the database, does that really buy you anything? Because then you have to split the backup and restore instructions and now you've introduced a huge confusion new DBAs are going to fall into.

    Not a good idea with backups.... 😀

  • I agree with Roger re what if the DB has a major crash - all your notes and instructions are gone too.

    What occurred to me was that there should be a "Maintenance and Instructions" container on instance but outside the database. Whenever you create a new DB, a new M&I is created in parallel with the same name as the database. You would use it to set up your maintenance plan and tools and documentation. Then you would have your database and your 'operators manual' with the same name. They would always exist in pairs and SQL Manager would alert you if one was missing.

  • I totally disagree with Roger and Donald however they have a very important point but I would argue that there is a strategy that we should all understand that we could apply: replication.

    I am not advocating SQL Server replication just the abstract technique. The master copy of the backup strategy etc should be within the database itself with a copy against the instance perhaps. A folder as previously suggested would work nicely too.

    Gaz

    -- Stop your grinnin' and drop your linen...they're everywhere!!!

  • roger.plowman (3/12/2013)


    I don't know if I'd want backup information in the database or not. On the one hand I could see it, and you're right, having information about the backup in the database is a seductive idea. At least for the backup portion of the instructions...

    But...what happens when the database is corrupted? Where are the restore instructions? Same database? Then how do you restore?

    And if you put the restore instructions in the instance instead of the database, does that really buy you anything? Because then you have to split the backup and restore instructions and now you've introduced a huge confusion new DBAs are going to fall into.

    Not a good idea with backups.... 😀

    Since you (presumably) know where your backup files are stored and you will need to bootstrap at least that far, the first backup will restore most of your detail work. That is a good thing. Basically this is no different really from ANY significant SQL server failure, regardless of where the backup details are stored.

    Because certain information needs to be outside the backup (you do have a DR manual, don't you? Keys and passwords saved someplace accesible?) does not really change the value of incorporating much of the pedestrian detail into the backup itself. There is lots of detail information that benefits from encapsulation, it certainly would be awful nice if, once you restored a database, all your backup jobs were ready to go.

    ...

    -- FORTRAN manual for Xerox Computers --

  • roger.plowman (3/12/2013)


    I don't know if I'd want backup information in the database or not. On the one hand I could see it, and you're right, having information about the backup in the database is a seductive idea. At least for the backup portion of the instructions...

    But...what happens when the database is corrupted? Where are the restore instructions? Same database? Then how do you restore?

    And if you put the restore instructions in the instance instead of the database, does that really buy you anything? Because then you have to split the backup and restore instructions and now you've introduced a huge confusion new DBAs are going to fall into.

    Not a good idea with backups.... 😀

    What restore instructions? A list of the backups? In corruption or crashes, you'll want other copies of this data. Plus, that can happen in MSDB. Then you can lose it for all databases.

    There are no restore instructions. That's the human's job. You assemble files, you write restore statements. You script this.

    Not sure how you think it's different than now.

  • I like your idea Steve.

    It seems feasible for there to be a config setting or switch to make this functionality optional (i.e. ContainedDatabaseAdminFlag ) as to mandatory for all databases on the instance.

    I have seen a home-grown attempt at this concept, and I think it wasn't implemented very well. First discovered in a review of existing servers, I noticed there was not a single SQL Agent Job. How could this be? I saw backup files so knew it was being done (at least sometimes without error). The developers included stored procedures to backup, reindex, archive etc. into the user database for the application. Unfortunately, they didn't include a user-interface in the application to configure them. It turns out that they had Windows Scheduler Tasks to execute these procedures.

    What followed was more than a bit of a mess. Hard-coded backup paths and schedules. Any change to the setup was one-off, and we had several hundred servers to manage on client sites. I fully understand their intention, but also understand some fundamental flaws in design and implementation.

    For example, new storage was installed to replace failed disks and was not designated the previous drive letter. Transaction log backups failed, transaction log filled, database became unavailable, no backups at all. No one to review ERRORLOGS, no DBA, no front-end interface to notify users. A database calling it's own backup? Just sounded like a terrible idea to me, for some of the same reasons stated by other responders.

    In the end, I went back to the source code and had them remove all of that "garbage"... I was truly offended that the developers wouldn't rely on a super-smart seasoned professional DBA like me to manage the instances! So then, the smart guy gets to run with his smart idea. Strip out theirs, and configure backups and maintenance tasks in SQL jobs that any DBA could figure out. Now I have some backups on some instances managed by their own database, some managed on the instance level, and two versions of the deployment package, at least until we get this mess straightened out.

    Fast forward two weeks. One of the instances I had "fixed" had full disks (oh, I forgot to mention that data .mdfs, log .ldfs, and backup .baks all resided on the same physical drive... again a hard-coded feature of the software, that basically required the client to buy a standalone instance of SQL) The disk was full because not only were my backups working, but the internal user database backups were working as well. Support Department saw the version of the database, ran a deploy/repair utility that reinstalled all of the components I had stripped.

    No matter features or the technology used, the whole organization, including the end client, need to understand how the pieces work together. Is it right to have a user database backup itself? Is it right to have developers manage with internal code? Should the DBA be in charge of maintenance and administration? As far as and end-user and client is concerned, it doesn't matter. What matters is availability, recoverability, stability, consistency, and maintainability. Our downfall came undoubtedly from lack of communication.

    Egos and pride got in the way of the end goal. I shot myself in the foot!

    Ouch.

    Todd Carrier
    MCITP - Database Administrator (SQL 2008)
    MCSE: Data Platform (SQL 2012)

  • Todd Carrier (3/12/2013)


    I like your idea Steve.

    ...

    A database calling it's own backup? Just sounded like a terrible idea to me, for some of the same reasons stated by other responders.

    I'm not sure I've stated my case well. This isn't a database determining it's backup schedule (or maintenance or anything). This isn't a developer doing it. This is still the DBA. This is still the SQL Agent and instance level work being performed. This is, however, the DBA or someone with knowledge, choosing the backup/maintenance/etc. schedule and STORING it in the database. If the database moves to another instance, the backup schedule gets populated into the instance level jobs.

    This is really about keeping metadata about backup schedules, maintenance, needs and requirements, in the database. The "who" decides it is no different than today.

  • I had to think on this a bit. I think the idea has some merit. Storing database "maintenance" details and schedules within the database would be useful. Especially when moving the database.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

Viewing 10 posts - 1 through 9 (of 9 total)

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