<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss Content Posted by Andy Warren
»
Creating Databases
12 posts, Page 1 of 2
1
2
»»
Creating Databases
Rate Topic
Display Mode
Topic Options
Author
Message
Andy Warren
Andy Warren
Posted Tuesday, August 26, 2008 11:51 PM
SSCertifiable
Group: Moderators
Last Login: Thursday, May 09, 2013 12:38 PM
Points: 6,462,
Visits: 1,384
Comments posted to this topic are about the item
Creating Databases
Andy
SQLShare - Learn One New Thing Each Day
SQLAndy - My Professional Blog
Connect with me on LinkedIn
Follow me on Twitter
Post #559346
jtango
jtango
Posted Wednesday, August 27, 2008 12:25 AM
SSC Veteran
Group: General Forum Members
Last Login: Yesterday @ 10:53 PM
Points: 223,
Visits: 99
Hi. Nice to have someone to go around the basics for a while.
One thing struck my eye though.
Space needed for database.
10 % and autogrow is easy but 10 % of 100 MB is 10 MB, 10 % of 100 GB is 10 GB meaning that a new database is growing fastest in it's early days and while you, as a dba, must monitor database growth, you should take a more careful look at space requirements when creating a new database, maybe the database doesn't need so much growth after one year or so.
My opinion is that a fixed growth is more useful and you should always reserve space enough to survive at least a year without having to allocate more space from disks. I prefer not to leave space allocation to Sql Server's job while it causes overhead specially with disk i-o consuming applications.
At firs I prefer to double the space if db is growing faster than estimated, which is mostly because the space calculatons are not accurate and something is missed.
Getting more disk space may also be tricky afterwards even if you've got a SAN or some other network disk array system while you may have to allocate a totally new volume and transfer databases to it.
So you should always have decent space calculations made with application and customer business specialists before taking any action creating databases for them.
Disk is cheap, downtime is not.
PS. While this is a basic presentation with a lot of defaults, data and logs should never be on the same disk and if you have only one disk use at least different folders and in the case that was presented in the article tell you customers that the reliability and maybe performance also is low.
Disk is cheap, downtime is not.
Post #559359
Knut Boehnert
Knut Boehnert
Posted Wednesday, August 27, 2008 12:59 AM
Right there with Babe
Group: General Forum Members
Last Login: Thursday, April 18, 2013 5:10 AM
Points: 766,
Visits: 274
One thing I would change almost always now that I've learned a bit is to create straight from the start a second filegroup. Call it Data. Then set Data as default.
The reasoning behind is that my sys tables are in the Primary filegroup together with their indices. As all other (production) tables and indices go into Data there is a good chance to be able to recover the master section of the database (aka Primary) on its own in case of serious corruption.
Another reason is that with the backup operation I can do a file backup (1 logical filegroup = 1 physical file). For a data warehouse environment this can tremendously recude the backup time if the backup only works on the file that is currently written too while the others are archives.
The downside to this approach is that a restore operation takes longer but this in a data warehouse environment can be acceptable (ymmv).
Post #559378
Anipaul
Anipaul
Posted Wednesday, August 27, 2008 4:28 AM
SSCarpal Tunnel
Group: General Forum Members
Last Login: Yesterday @ 6:03 AM
Points: 4,787,
Visits: 1,335
Good article. Really after a long time we saw an article on basics. Great work Andy!!!!
Post #559474
AlanBarber
AlanBarber
Posted Wednesday, August 27, 2008 6:36 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Thursday, March 03, 2011 1:41 PM
Points: 198,
Visits: 199
I'm going to voice a me too that percentage based resizes are bad IMO... A good DBA should be monitoring every database and be aware of not only size but also growth rates and trends.
just setting to 10% is really a poor way to manage. 10% of 10gig is a lot different than 10% of 500gigs. based on knowledge of growth rates that 10% of 500 might not be needed.
the other issue i wanted to point out was that atleast for us we always set every database to use Forced Parameterization. We have seen a pretty good performance boost from every db by having this checked.
thanks!
Post #559563
Andrew Peterson-472853
Andrew Peterson-472853
Posted Wednesday, August 27, 2008 6:40 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Wednesday, January 16, 2013 7:40 AM
Points: 136,
Visits: 259
Its good to go over the basics. Its the old 80/20 rule, and most problems come from failed or misunderstood basics.
The more you are prepared, the less you need it.
Post #559564
skyline666
skyline666
Posted Wednesday, August 27, 2008 7:07 AM
Old Hand
Group: General Forum Members
Last Login: Wednesday, September 24, 2008 1:27 PM
Points: 350,
Visits: 229
I don't know if its just me or not, but the picture's aren't there. Good article by the way.
Post #559591
Wayne West
Wayne West
Posted Wednesday, August 27, 2008 8:51 AM
SSC Eights!
Group: General Forum Members
Last Login: Wednesday, May 09, 2012 10:26 AM
Points: 891,
Visits: 1,958
Knut Boehnert (8/27/2008)
One thing I would change almost always now that I've learned a bit is to create straight from the start a second filegroup. Call it Data. Then set Data as default.
The reasoning behind is that my sys tables are in the Primary filegroup together with their indices. As all other (production) tables and indices go into Data there is a good chance to be able to recover the master section of the database (aka Primary) on its own in case of serious corruption.
Another reason is that with the backup operation I can do a file backup (1 logical filegroup = 1 physical file). For a data warehouse environment this can tremendously recude the backup time if the backup only works on the file that is currently written too while the others are archives.
The downside to this approach is that a restore operation takes longer but this in a data warehouse environment can be acceptable (ymmv).
That, Knut, is a very interesting concept. I'm definitely going to have to do some thinking and experimenting with that.
Thanks for the post!
Post #559697
Wayne West
Wayne West
Posted Wednesday, August 27, 2008 8:55 AM
SSC Eights!
Group: General Forum Members
Last Login: Wednesday, May 09, 2012 10:26 AM
Points: 891,
Visits: 1,958
Good article, Andy. Myself, I'm very explicit in telling my developers that putting spaces in database or object names on any of my SQL Servers may be punishable by death, or at least the inconvenience of having to update their code. :D
Another problem that I've encountered is if a hyphen is used in an object name. I've had the system consider it a subtraction operation if you don't remember to set it off with braces, and fun ensues.
For object names, I stick with letters, numbers, and CamelCase. If you need to use an underscore, fine.
Post #559701
Andrew Peterson-472853
Andrew Peterson-472853
Posted Wednesday, August 27, 2008 11:53 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Wednesday, January 16, 2013 7:40 AM
Points: 136,
Visits: 259
Good point on the hyphen. I've found that object names with a hyphen need to be in brackets.
The more you are prepared, the less you need it.
Post #559846
« Prev Topic
|
Next Topic »
12 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
