SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Self Describing Databases


Self Describing Databases

Author
Message
Steve Jones
Steve Jones
SSC Guru
SSC Guru (282K reputation)SSC Guru (282K reputation)SSC Guru (282K reputation)SSC Guru (282K reputation)SSC Guru (282K reputation)SSC Guru (282K reputation)SSC Guru (282K reputation)SSC Guru (282K reputation)

Group: Administrators
Points: 282723 Visits: 19914
Comments posted to this topic are about the item Self Describing Databases

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Tobar
Tobar
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1849 Visits: 758
Efficiency?? Consistency?? What are you?? Some kind of rabble rouser???

Good points.

<><
Livin' down on the cube farm. Left, left, then a right.
roger.plowman
roger.plowman
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3991 Visits: 1501
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.... :-D
Donald Bustell
Donald Bustell
SSC-Enthusiastic
SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)

Group: General Forum Members
Points: 141 Visits: 41
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.
Gary Varga
Gary Varga
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41630 Visits: 6562
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!!!
jay-h
jay-h
SSCertifiable
SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)

Group: General Forum Members
Points: 6751 Visits: 2493
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.... :-D


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 --
Steve Jones
Steve Jones
SSC Guru
SSC Guru (282K reputation)SSC Guru (282K reputation)SSC Guru (282K reputation)SSC Guru (282K reputation)SSC Guru (282K reputation)SSC Guru (282K reputation)SSC Guru (282K reputation)SSC Guru (282K reputation)

Group: Administrators
Points: 282723 Visits: 19914
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.... :-D


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.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Todd Carrier
Todd Carrier
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2829 Visits: 964
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)
Steve Jones
Steve Jones
SSC Guru
SSC Guru (282K reputation)SSC Guru (282K reputation)SSC Guru (282K reputation)SSC Guru (282K reputation)SSC Guru (282K reputation)SSC Guru (282K reputation)SSC Guru (282K reputation)SSC Guru (282K reputation)

Group: Administrators
Points: 282723 Visits: 19914
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.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (124K reputation)SSC Guru (124K reputation)SSC Guru (124K reputation)SSC Guru (124K reputation)SSC Guru (124K reputation)SSC Guru (124K reputation)SSC Guru (124K reputation)SSC Guru (124K reputation)

Group: General Forum Members
Points: 124722 Visits: 18627
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

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search