Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Self Describing Databases Expand / Collapse
Author
Message
Posted Monday, March 11, 2013 9:14 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 11:24 AM
Points: 32,781, Visits: 14,942
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
Post #1429565
Posted Tuesday, March 12, 2013 3:25 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 9:51 AM
Points: 230, Visits: 692
Efficiency?? Consistency?? What are you?? Some kind of rabble rouser???

Good points.


<><
Livin' down on the cube farm. Left, left, then a right.
Post #1429633
Posted Tuesday, March 12, 2013 6:42 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, December 02, 2013 6:30 AM
Points: 346, Visits: 691
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....
Post #1429749
Posted Tuesday, March 12, 2013 7:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, July 26, 2013 12:06 PM
Points: 7, 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.

Post #1429775
Posted Tuesday, March 12, 2013 7:48 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 8:20 AM
Points: 4,862, Visits: 2,243
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!!!
Post #1429817
Posted Tuesday, March 12, 2013 7:54 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, April 10, 2014 11:06 AM
Points: 733, Visits: 1,869
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 --
Post #1429822
Posted Tuesday, March 12, 2013 8:51 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 11:24 AM
Points: 32,781, Visits: 14,942
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.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1429868
Posted Tuesday, March 12, 2013 10:03 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 9:55 AM
Points: 1,298, Visits: 782
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)

Post #1429922
Posted Tuesday, March 12, 2013 10:21 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 11:24 AM
Points: 32,781, Visits: 14,942
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
Post #1429934
Posted Tuesday, March 12, 2013 12:07 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 9:16 AM
Points: 20,465, Visits: 14,095
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1430030
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse