SQL Server Database Creation

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.

http://technet.microsoft.com/en-us/library/ms190249%28SQL.105%29.aspx

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

http://msdn.microsoft.com/en-us/library/ms176061.aspx

Written By

                Elmozamil Elamir Hamid