SQL Server Database Creation:
Database Planning:
1. Storage.
Space needed to
store your database the size of database determines where you can store it, and
the growth of database will determine the size of storage you need to store
data.
2. Performance
Your database
may have a lot of transactions and many concurrent users connect to the
database, in this case you need to identify the server resources that meet your
needs, you must choose proper CPU, RAM, NIC, and so on.
3. Protect and maintain.
Protecting your
database is most important thing that have to plan carefully, who can login to
the server, who can see this database, who can see those tables, who can edit,
and many levels of users in your database.
You must
maintain your database by backup and restore, and maintain indexes which will
improve your performance, main your security roles.
When you decide to create
database you must plan your database carefully, if your create database with
existing server that contains many other database you should be careful about
performance, space, and security.
File and File Groups:
SQL Server database require at
least two operating system files. Data file which use to store tables, indexes,
views, SP, and all database objects and the other file is log file which used
to store log information which used to recover database in case of failure.
Every transaction against your database is recorded in the log file if the
system fail SQL server will recover the database from your log file.
We have three types of files:
Primary file it is data file and
has .mdf extension (you can use any other extension but this is well known and
is the default) any database has only one primary file group.
Secondary file it is a database
and has .ndf extension (you can use any other extension but this is well known
and is the default) the database may have no secondary file it may use only the
primary file. The secondary files are used to spread data across multiple disks
you may have ten files each file in separate disk.
There are two types of file
group:
Primary file group which contains
the primary file and can contain secondary file, the other file group user
defined file group which contains the secondary files. This structure helps you
to maintain your database and provide partial availability you can backup only
on file group or file and also you can restore only one file group (we will
speak about partial restore in another post).
Database Options:
Always decide which options you
need for your database, if you want it to be simple recovery mode or full, or
bulk_log, or you want it to be single user, or restricted, or multi user. The
default options is multi user and full recovery mode.
To create database in SQL server
you can use scripts or you can use GUI:
GUI:
1. Connect to your SQL Server instance.
2. Right click on Database node and choose New Database.
3. Enter your database name in the textbox name database name.
4. In database files you can chick on Add and add new database
file, scroll and you will find many options (path, size, growth (it is better
to put number not percent in the file growth I will speak about this in another
post)).
5. On the top left of the window click on Options you we see
different options.
6. On the top left of the window click on file groups in this you
can add user defined file groups and you can add many file groups.
·
(You can see there is button
(in the top of the screen) named script if you click on it will write the
script on behave of you.
7. After you finish click on OK.
Scripts:
You can use Create database
command the syntax as this
CREATE DATABASE DatabaseName
ON PRIMARY
(NAME = N'filename', FILENAME = filepath.mdf' , SIZE = 5120KB , FILEGROWTH = 2MB)
LOG ON
( NAME = N'logname', FILENAME = logpath.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO
Written By
Elmozamil
Elamir Hamid
PK Domain (PVT) Limited is a provider of Shared, Reseller, VPS and Dedicated Web hosting, Web Designing & Development in all over Pakistan. Privately held and based in San Francisco, California, USA, the company was founded in 2002 by young entrepreneur.The Company is also providing Surveillance services all over Pakistan from 2012. You can check latest prices and packages on www.pkdomain.com.pk. The Company has also setup its International offices in various countries all over the world and maintaining web hosting servers from San Francisco, California.
ReplyDelete