Database creation questions 101

Question

Database creation questions 101

Ed7

SSCrazy Eights

Points: 8519
More actions
February 2, 2007 at 8:48 am

#116084

Hi All,
Can anyone help me to check my database scripts?
Any flags, please give your commands?

CREATE
DATABASE SWEETWATER
ON
PRIMARY
(NAME = SweetwaterPrimary,
FILENAME = 'C:\Sweetwater_Data\SweetwaterPrimary.mdf',
SIZE = 100MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 20),
FILEGROUP
SweetwaterFG
(NAME = SweetwaterData1,
FILENAME = 'C:\Sweetwater_Data\SweetwaterData1.ndf',
SIZE = 200MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 100),
(NAME = SweetwaterData2,
FILENAME = 'C:\Sweetwater_Data\SweetwaterData2.ndf',
SIZE = 200,
MAXSIZE = UNLIMITED,
FILEGROWTH = 100),
FILEGROUP
SweetwaterHistoryFG
(NAME = SweetwaterHistory1,
FILENAME = 'C:\Sweetwater_Data\SweetwaterHistory1.ndf',
SIZE = 100MB,
MAXSIZE = 500,
FILEGROWTH = 50)
LOG
ON
(NAME = SweetwaterArchlog1,
FILENAME = 'C:\Sweetwater_Data\SweetwaterLog.ldf',
SIZE = 300MB,
MAXSIZE = 800,
FILEGROWTH = 100)

Thank a lot
TJ

Viewing 12 posts - 1 through 11 (of 11 total)

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

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 1

HI,

I don't know your requirements for the database, but I would not create databases with several filegroups on C: I would not put any databases on the system drive assumning C is the system drive. I would plan the filegroup location and architecture based on your data usage, like tables and its frequently used non-clustered indexes should go to different drives for performance or big tables that are frequently joined should go to different physical drives.

Additionally I would not put anything Unlimited on the C drive, I did once and will not do it again.

Regards,Yelena Varsha

MohammedU SSCoach Points: 19644 More actions · Answer 2

Please post your requirement and exact question to get correct reply...

MohammedU
Microsoft SQL Server MVP

Ed7 SSCrazy Eights Points: 8519 More actions · Answer 3

Hello Mohammed,

Actually, I am looking a good sample db creation scripts.

Thanks!!

TJ

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 4

Here is a quick'n' dirty one. Data and log on separate drives that are not for the OS, page file or application executables and such.

---

--- create_xxx.sql - 08-24-2006 Rudyx

---

use master

go

create database xxx on (

name=xxx_Data,

filename='G:\MSSQL\DATA\xxx_Data.mdf',

size=10Mb,

maxsize=50Mb,

filegrowth=5Mb

&nbsp

log on (

name = 'xxx_Log',

filename='H:\MSSQL\DATA\xxx_Log.ldf',

size=5mb,

maxsize=25mb,

filegrowth=5mb

&nbsp

go

---

alter database xxx set recovery full

go

---

exec xxx..sp_changedbowner 'sa'

go

checkpoint

go

---

--- end of create_xxx.sql

---

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

Ed7 SSCrazy Eights Points: 8519 More actions · Answer 5

Rudy,

What is the secure and correct method to create database?

Since my Windows Server 2003 was already setup and

it did not partitions to E, D, F drive.

Please advise. Thank a lottttt.

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 6

Don't create multiple filegroups on the same disk/array unless you require a read only filegroup, you'll probably degrade performance.Multiple files are different however.

Check out BOL, msdn, technet and the article archives at SQL Central about creating databases.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 7

Colin has offered the best advice available.

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

Ed7 SSCrazy Eights Points: 8519 More actions · Answer 8

The Window Server 2003 is already setup with single C drive.

They won't allow me to re-install the O/S and create partition C, E, and F.

What is the best option to setup and creating database with single partition C drive?

Thank you.

Edwin

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 9

Will this be development or production is the first question to ask. If development, then use th defaults. If production then you'll need to rethink things a bit starting with hardware first. Then the discussion can get rather lengthy. Here's kind of a laundry list of questions that may or may not apply if your server is going to be production.

Are your SQL Servers 'dedicated' servers ?

Do your SQL Servers they have application software installed on them ?

Do your SQL Servers have IIS installed on them ?

How many databases do you have ?

How 'large' are your databses (and how many) ?

Less than 10 Gb

Less than 100 Gb

Greater than 100 Gb

How are your database applications split ?

OLTP - Online Transaction Processing

Batch

DSS - Decision Support Systems

Are any of your applications using:

DTS

Full Text Search

Analysis Server

Reporting Services

How would you categorize your applications ?

Available 24x7

Available 9 to 5

Available 'other' hours

What kind of 'maintenance window' or SLA (Service level agreement) do you have for each application ?

How are you performing database backups ?

Directly to tape ?

To disk, then to tape ?

What kind of storage do your SQL Servers use ?

Internal disks

DAS - Direct attached storage

SAN storage

Some 'other' combination

Are you using SAN storage today ?

Are you looking for high-availabilty using clustering ?

Are you looking for DR (disaster recovery) capability using 'hot' spare servers and log shipping ?

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

Ed7 SSCrazy Eights Points: 8519 More actions · Answer 10

Hi Rudy,

I try to answer your questions:

1. It's the productions database.

2.No application software installed on SQL Server 2005.

3. I believed no IIS installed. What is the best method to check IIS was installed?

4. I created 5 database instances and will continue to grow for OLTP database.

5. Less than 10 GB.

6. Reporting Services.

7. Available 24/7

8. Backup to tape..

Thanks a lot.

TJ

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 11

1. It's the productions database.

- OK

2. No application software installed on SQL Server 2005.

- Very good, a dedicated SQL Server

3. I believed no IIS installed. What is the best method to check IIS was installed?

- Based on #6 you will have to have IIS installed.

4. I created 5 database instances and will continue to grow for OLTP database.

- Do you mean 5 application databases in one instance or 5 named instances with one application database each ?

5. Less than 10 GB.

- Baby databases, whether all 5 databases are 10 Gb in total or there are 5 10 Gb databases. Standard SQL Maintenance plans should do you just fine.

6. Reporting Services.

- See #4

7. Available 24/7

- SQL Server is made for this. SQL 2000 is OK. But SQL 2005 is much better (as you are intending on using).

8. Backup to tape..

- Do you mean database backups directly to tape ? If so I'd strongly suggest database backups to disk for performance reasons and then backup the disk fiels to tape.

You do not mention the number of users for OLTP processing nor do you mention the number of users for Reporting services. For SQL Reporting Services there will also be 2 more databases as well which are created at instalaltion time for this feature. This in conjunction with the database/instance question in #4 can change your hardware. In speaking of hardware, there is disk. This in and of itself is a large topic. You may want to scan the forums on this site for disk and server architecture in addition to paying visits to the MSDN and technet websites at MS as well. Have fun !!!

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."