|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
What is a "Standard Database"?Hi,
I'm looking for some guidelines to design, develop and maintain a standard database. I mean standard in terms of naming conventions, schema, security, availability, maintenance and every aspect that there is standards. However I'm not sure if there can be standard rule for all of them. Recently I read "Joe Celko's SQL Programming Style" book, it was great but as I mentioned, more aspects of database standardization I need. Maybe this is controversial question but I like to know your ideas! Thanks in advance, Leila I would say there "is" a standard, but that most of us use it only to some
extent. There are always certain traditions that change a standard in some direction. I guess one thing you can do is take a look at the Northwind database that comes with all instances of SQL Server, and then follow the code examples in Books OnLine and MSDN. You will then, at least, be close to Microsoft's standard connotation. That might be a start. I agree,
Actually these examples inherit the standards from particular source or reference, I'm looking for them! Show quote "Per Bylund" <PerByl***@discussions.microsoft.com> wrote in message news:38AEBE96-8F4B-4561-B73C-104644D07506@microsoft.com... >I would say there "is" a standard, but that most of us use it only to some > extent. There are always certain traditions that change a standard in some > direction. > > I guess one thing you can do is take a look at the Northwind database that > comes with all instances of SQL Server, and then follow the code examples > in > Books OnLine and MSDN. You will then, at least, be close to Microsoft's > standard connotation. That might be a start. "Leila" <Lei***@hotpop.com> wrote in message There's no one-size-fits-all standard for naming database columns, tables news:Oz37mMfRHHA.4832@TK2MSFTNGP04.phx.gbl... >I agree, > Actually these examples inherit the standards from particular source or > reference, I'm looking for them! and other objects... if naming conventions is your primary concern here. Celko's references in his books to the ISO and other standards bodies are the closest you will probably get if you want a primary source for justification of adopting a particular naming convention (so long as you agree with their recommendations and Joe's interpretation of those recommendations). If I can ask, for what reason do you need to find naming convention "standards"? Are you trying to set up a standard set of rules for naming databases and other objects throughout your department or organization? Thanks Mike,
Actually Joe's book is enough for naming conventions and some other issues,but I'd like to know if there are standard rules for maintaining a database with regard to Security, Availability and Backup/Restore Strategy and.... Show quote "Mike C#" <x**@xyz.com> wrote in message news:Opf0g3%23RHHA.2252@TK2MSFTNGP02.phx.gbl... > > "Leila" <Lei***@hotpop.com> wrote in message > news:Oz37mMfRHHA.4832@TK2MSFTNGP04.phx.gbl... >>I agree, >> Actually these examples inherit the standards from particular source or >> reference, I'm looking for them! > > There's no one-size-fits-all standard for naming database columns, tables > and other objects... if naming conventions is your primary concern here. > Celko's references in his books to the ISO and other standards bodies are > the closest you will probably get if you want a primary source for > justification of adopting a particular naming convention (so long as you > agree with their recommendations and Joe's interpretation of those > recommendations). If I can ask, for what reason do you need to find naming > convention "standards"? Are you trying to set up a standard set of rules > for naming databases and other objects throughout your department or > organization? > Leila (Lei***@hotpop.com) writes:
> Thanks Mike, There are best practices, but best practices is not the same as standard.> Actually Joe's book is enough for naming conventions and some other > issues,but I'd like to know if there are standard rules for maintaining a > database with regard to Security, Availability and Backup/Restore Strategy > and.... Most of all, different shops have different requirements, and that affects you approachs these issue. If ten minutes of downtime costs you a fortune, then you need configure your system and staff the monitoring of it, so that this does not occur. But this will be also very expensive, so those measures should be applied in a shop which can live with the database being offline for a whole day. So I don't think you can find any "standard" in this regard. What possibly could make sense, is a set of standard questions to answer, and from them determine appropriate strategies. How much unplanned downtime can you afford? How much planned downtime? If the database goes belly-up, will you need up-to-the point recovery? Or is it OK to restore the latest backup? Preferrably, all such questions should be related to costs. And, very important, once you have decided for a strategy on recovery, you need to test and verify it, so that when disaster strikes, you know that you will be able to live up to it. For instance, one of our clients had a DB crash this summer. My plan was to do a point-in-time recovery to about half-an-hour before the database finally gave up (because there were log entries indicating that things were bad already then). But when I was to apply the log backups, I found that one backup in the chain was missing, because the job that took a full backup, completed the job with a log backup WITH INIT. But there was anonther hourly log backup that had run before that, while the full backup was running. As long as the full backup completed within 55 minutes, the scheme work, but this night the full backup took over 90 minutes. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Thanks Erland :-)
Show quote "Erland Sommarskog" <esq***@sommarskog.se> wrote in message news:Xns98CD7E8F1C999Yazorman@127.0.0.1... > Leila (Lei***@hotpop.com) writes: >> Thanks Mike, >> Actually Joe's book is enough for naming conventions and some other >> issues,but I'd like to know if there are standard rules for maintaining a >> database with regard to Security, Availability and Backup/Restore >> Strategy >> and.... > > There are best practices, but best practices is not the same as standard. > Most of all, different shops have different requirements, and that > affects you approachs these issue. If ten minutes of downtime costs you > a fortune, then you need configure your system and staff the monitoring > of it, so that this does not occur. But this will be also very expensive, > so those measures should be applied in a shop which can live with the > database being offline for a whole day. > > So I don't think you can find any "standard" in this regard. What possibly > could make sense, is a set of standard questions to answer, and from them > determine appropriate strategies. How much unplanned downtime can you > afford? How much planned downtime? If the database goes belly-up, will > you need up-to-the point recovery? Or is it OK to restore the latest > backup? > Preferrably, all such questions should be related to costs. > > And, very important, once you have decided for a strategy on recovery, > you need to test and verify it, so that when disaster strikes, you know > that you will be able to live up to it. For instance, one of our clients > had a DB crash this summer. My plan was to do a point-in-time recovery to > about half-an-hour before the database finally gave up (because there > were log entries indicating that things were bad already then). But > when I was to apply the log backups, I found that one backup in the > chain was missing, because the job that took a full backup, completed > the job with a log backup WITH INIT. But there was anonther hourly > log backup that had run before that, while the full backup was running. > As long as the full backup completed within 55 minutes, the scheme work, > but this night the full backup took over 90 minutes. > > -- > Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Leila
For example if you "care" about the data and downtime is critical I'd suggest you to create a FULL backup database and then every 30 minutes BACKUP Log file in order to allow restore the database at point of time. But what if the user calls you and wants to restore database from three weeks ago and between 1PM and 2PM? 1) Ok, if you ran FULL BACKUP and BACKUP LOG (without WITH INIT) file you can restore FULL database backup and then apply all logs (it could be 100 or more ) and get the database at the right stage. I have a client where we INIT the LOG file just after FULL backup for instance, so in the above question I could not get at time between 1PM and 2PM. 2) Another method is creating DIFFERENTIAL backup database and perform it every two-three hours , so you can INIT your LOG file each time after FULL BACKUP database. You restore a FULL backup that is three weeks old and then apply the last DIFFERENTIAL backup that was done on the this specific day.(It could be on 2PM exactly:-))) Remember that DIFFERENTIAL backup contains all data that has changed since last FULL backup Show quote "Leila" <Lei***@hotpop.com> wrote in message news:eEtdeZASHHA.4028@TK2MSFTNGP03.phx.gbl... > Thanks Mike, > Actually Joe's book is enough for naming conventions and some other > issues,but I'd like to know if there are standard rules for maintaining a > database with regard to Security, Availability and Backup/Restore Strategy > and.... > > > > "Mike C#" <x**@xyz.com> wrote in message > news:Opf0g3%23RHHA.2252@TK2MSFTNGP02.phx.gbl... >> >> "Leila" <Lei***@hotpop.com> wrote in message >> news:Oz37mMfRHHA.4832@TK2MSFTNGP04.phx.gbl... >>>I agree, >>> Actually these examples inherit the standards from particular source or >>> reference, I'm looking for them! >> >> There's no one-size-fits-all standard for naming database columns, tables >> and other objects... if naming conventions is your primary concern here. >> Celko's references in his books to the ISO and other standards bodies are >> the closest you will probably get if you want a primary source for >> justification of adopting a particular naming convention (so long as you >> agree with their recommendations and Joe's interpretation of those >> recommendations). If I can ask, for what reason do you need to find >> naming convention "standards"? Are you trying to set up a standard set >> of rules for naming databases and other objects throughout your >> department or organization? >> > > Good suggestions! Thanks Uri!
Show quote "Uri Dimant" <u***@iscar.co.il> wrote in message news:%23574IiFSHHA.3412@TK2MSFTNGP02.phx.gbl... > Leila > > For example if you "care" about the data and downtime is critical I'd > suggest you to create a FULL backup database and then every 30 minutes > BACKUP Log file in order to allow restore the database at point of time. > But what if the user calls you and wants to restore database from three > weeks ago and between 1PM and 2PM? > > 1) > Ok, if you ran FULL BACKUP and BACKUP LOG (without WITH INIT) file you > can restore FULL database backup and then apply all logs (it could be 100 > or more ) and get the database at the right stage. I have a client where > we INIT the LOG file just after FULL backup for instance, so in the above > question I could not get at time between 1PM and 2PM. > 2) > Another method is creating DIFFERENTIAL backup database and perform it > every two-three hours , so you can INIT your LOG file each time after FULL > BACKUP database. You restore a FULL backup that is three weeks old and > then apply the last DIFFERENTIAL backup that was done on the this specific > day.(It could be on 2PM exactly:-))) > Remember that DIFFERENTIAL backup contains all data that has changed > since last FULL backup > > > > > "Leila" <Lei***@hotpop.com> wrote in message > news:eEtdeZASHHA.4028@TK2MSFTNGP03.phx.gbl... >> Thanks Mike, >> Actually Joe's book is enough for naming conventions and some other >> issues,but I'd like to know if there are standard rules for maintaining a >> database with regard to Security, Availability and Backup/Restore >> Strategy and.... >> >> >> >> "Mike C#" <x**@xyz.com> wrote in message >> news:Opf0g3%23RHHA.2252@TK2MSFTNGP02.phx.gbl... >>> >>> "Leila" <Lei***@hotpop.com> wrote in message >>> news:Oz37mMfRHHA.4832@TK2MSFTNGP04.phx.gbl... >>>>I agree, >>>> Actually these examples inherit the standards from particular source or >>>> reference, I'm looking for them! >>> >>> There's no one-size-fits-all standard for naming database columns, >>> tables and other objects... if naming conventions is your primary >>> concern here. Celko's references in his books to the ISO and other >>> standards bodies are the closest you will probably get if you want a >>> primary source for justification of adopting a particular naming >>> convention (so long as you agree with their recommendations and Joe's >>> interpretation of those recommendations). If I can ask, for what reason >>> do you need to find naming convention "standards"? Are you trying to >>> set up a standard set of rules for naming databases and other objects >>> throughout your department or organization? >>> >> >> > > "Leila" <Lei***@hotpop.com> wrote in message Like Erland and Uri pointed out, your business requirements should drive news:eEtdeZASHHA.4028@TK2MSFTNGP03.phx.gbl... > Thanks Mike, > Actually Joe's book is enough for naming conventions and some other > issues,but I'd like to know if there are standard rules for maintaining a > database with regard to Security, Availability and Backup/Restore Strategy > and.... your decisions in regards to all of these items. There are several "best practices" available - if you Google them or visit MS' website and search for "best practices SQL Server", "SQL Server security", "SQL Server availability", etc., you should find a ton of white papers and technical articles describing these best practices. Basically you have to decide based on your business model how these items fit in. You might have a database in place that you could potentially work without for a few hours or even a day; or you might not be able to afford more than 5 minutes of downtime per month (or maybe even less). Of course whatever you do needs to fall within your budget as well. Those are the some of the decisions that determine which "best practices" you need to implement... Unfortunately there's no one size fits all in these areas either. Great! I think "Best Practices" are something near standard rules that I'm
looking for! Show quote "Mike C#" <x**@xyz.com> wrote in message news:ONmghIPSHHA.996@TK2MSFTNGP02.phx.gbl... > > "Leila" <Lei***@hotpop.com> wrote in message > news:eEtdeZASHHA.4028@TK2MSFTNGP03.phx.gbl... >> Thanks Mike, >> Actually Joe's book is enough for naming conventions and some other >> issues,but I'd like to know if there are standard rules for maintaining a >> database with regard to Security, Availability and Backup/Restore >> Strategy and.... > > Like Erland and Uri pointed out, your business requirements should drive > your decisions in regards to all of these items. There are several "best > practices" available - if you Google them or visit MS' website and search > for "best practices SQL Server", "SQL Server security", "SQL Server > availability", etc., you should find a ton of white papers and technical > articles describing these best practices. Basically you have to decide > based on your business model how these items fit in. You might have a > database in place that you could potentially work without for a few hours > or even a day; or you might not be able to afford more than 5 minutes of > downtime per month (or maybe even less). Of course whatever you do needs > to fall within your budget as well. Those are the some of the decisions > that determine which "best practices" you need to implement... > Unfortunately there's no one size fits all in these areas either. > Leila wrote:
> Hi, I'm looking for some guidelines to design, develop and maintain a The nice thing about standards is that there are so many to choose from.> standard database. I mean standard in terms of naming conventions, > schema, security, availability, maintenance and every aspect that > there is standards. However I'm not sure if there can be standard > rule for all of them. Recently I read "Joe Celko's SQL Programming > Style" book, it was great but as I mentioned, more aspects of > database standardization I need. Maybe this is controversial question > but I like to know your ideas! Ex-mainframers always make fieldnames UPPER.CASE; Java programmers use camelCase; Unix bods only do all_lowercase. Every corporation and every professional and industry institution seems to have its own take on standards, so all I can suggest is you adhere to whatever is prevalent in the environment you're working in, unless it's obviously way out of line (like being so silly it prevents interoperation). Wouldn't life be boring if we had one single Schema or DTD for every application on the planet? ///Peter Peter,
We're working on it! It's called the Unified Schema theory. One schema for all applications. We're calling it EAV. <jokingly> -- Bill Show quote "Peter Flynn" <peter.n***@m.silmaril.ie> wrote in message news:52hqs9F1ln9p1U1@mid.individual.net... > Leila wrote: >> Hi, I'm looking for some guidelines to design, develop and maintain a >> standard database. I mean standard in terms of naming conventions, >> schema, security, availability, maintenance and every aspect that >> there is standards. However I'm not sure if there can be standard >> rule for all of them. Recently I read "Joe Celko's SQL Programming >> Style" book, it was great but as I mentioned, more aspects of >> database standardization I need. Maybe this is controversial question >> but I like to know your ideas! > > The nice thing about standards is that there are so many to choose from. > Ex-mainframers always make fieldnames UPPER.CASE; Java programmers use > camelCase; Unix bods only do all_lowercase. Every corporation and every > professional and industry institution seems to have its own take on > standards, so all I can suggest is you adhere to whatever is prevalent > in the environment you're working in, unless it's obviously way out of > line (like being so silly it prevents interoperation). > > Wouldn't life be boring if we had one single Schema or DTD for every > application on the planet? > > ///Peter "Leila" <Lei***@hotpop.com> wrote in message The best place to start (if you haven't covered it already) is with a review news:%23yU0CscRHHA.4896@TK2MSFTNGP04.phx.gbl... > Hi, > I'm looking for some guidelines to design, develop and maintain a standard > database. I mean standard in terms of naming conventions, schema, > security, availability, maintenance and every aspect that there is > standards. However I'm not sure if there can be standard rule for all of > them. > Recently I read "Joe Celko's SQL Programming Style" book, it was great but > as I mentioned, more aspects of database standardization I need. > Maybe this is controversial question but I like to know your ideas! of proper normalization. Naming conventions, etc., while important, don't mean a nearly as much if your data turns into sludge because the database isn't built correctly to begin with. Google up 1NF, 2NF, 3NF (Normal Forms) and you'll find tons of info. to get you started. On Thu, 1 Feb 2007 10:04:14 +0330, "Leila" <Lei***@hotpop.com> wrote: There are different standards in every shop I've ever seen, and the>I'm looking for some guidelines to design, develop and maintain a standard >database. I mean standard in terms of naming conventions, schema, security, >availability, maintenance and every aspect that there is standards. However >I'm not sure if there can be standard rule for all of them. >Recently I read "Joe Celko's SQL Programming Style" book, it was great but >as I mentioned, more aspects of database standardization I need. >Maybe this is controversial question but I like to know your ideas! standards are violated with different frequency (and consequences!) in each as well. And some of the standards were pretty awful. Don't forget configuration management and the use of a source control tool like (or better than!) VSS. Josh  |
|||||||||||||||||||||||
Other interesting topics