01-sqlsrv2k db creation

8/3/2019 01-Sqlsrv2k DB Creation

1/51

Creating Database


2/51

Creating Databases

Database Structure

Files

Filegroups


3/51

Files

As mentioned, a SQL Server database is made up of a set of

operating system files. A database file can be either a data fileor a log file. Data files are used to store data and objects,

such as tables, indexes, views, triggers, and stored

procedures. There are two types of data files: primary andsecondary. Log files are used to store transaction loginformation only. Log space is always managed separately

from data space and can never be part of a data file.

Every database must be created with at least one data fileand one log file


4/51

Files

Primary data file

A primary data file contains all of the startup information for

the database and its system tables and objects.

It points to the rest of the files created in the database.

Each database must have exactly one primary file.

The recommended file extension is

mdf.


5/51

Files

Secondary data filesSecondary data files are optional.

A database might not have any secondary files if all its data is

placed in the primary file

Some databases need multiple secondary files in order tospread data across separate disks

The recommended file extension is.ndf


6/51


7/51

Files

The maximum file size for a SQL Server database is 32terabytes (TB) for data files

The maximum file size for a SQL Server log files :4 TB


8/51

Filegroups

Filegroups enable you to group files for administrative and

data placement purposes(segments in Microsoft SQL Serverversions 6.5 )

Filegroups can improve database performance by allowing adatabase to be created across multiple disks, multiple disk

controllers, or RAID systems

You can create tables and indexes on specific disks by using

filegroups, thus enabling you to direct the I/O for a certaintable or index to specific physical disks, controllers, or arrays

of disks.


9/51

Filegroups

There are three types of filegroups

Primary filegroup

User-defined filegroups

Default filegroup


10/51

Primary filegroup

Contains the primary data file and all other files not put intoanother filegroup

System tableswhich define the users, objects, andpermissions for a databaseare allocated to the primary

filegroup for that database.


11/51

User-defined filegroups

Includes any filegroups defined by the user during the process

of creating (or later altering) the database

A table or an index can be created for placement in a specificuser-defined filegroup.


12/51

Default filegroup

Holds all pages for tables and indexes that do not have aspecified filegroup when they are created.

The default filegroup is, by default, the primary filegroup.

Members of the db_owner database role can switch thedefault status from one filegroup to another

The ALTER DATABASE command is used to change the defaultfilegroup.


13/51

Default filegroup

The ALTER DATABASE command is used to change the default

filegroup.

Tyntax for this Transact-SQL command

ALTER DATABASE database_name MODIFY FILEGROUP filegroup_nameDEFAULT


14/51

Default filegroup

To improve performance, you can control data placement by creating tablesand indexes in different filegroups. For example, you might want to place atable that is heavily used in one filegroup on a large disk array (made up of10 disk drives, for example) and place another table that is less heavily usedin another filegroup located on a separate, smaller disk array (made up of 4

disk drives, for example).

Therefore, the more heavily accessed table will be spread

across the greater number of disks, allowing more paralleldisk I/O.

For example, you can create a separate file on each diskdrive, placing each file in a separate user-defined filegroup.This allows you to place each table and index in a specific file(and on a specific disk) by designating the filegroup when you

create the table or index


15/51

Default filegroup

one secondary data file in each of the user-defined filegroups (FG1 andFG2) on the E and F drives, and one log file on the G drive. You can

then create tables and indexes in either user-defined filegroupFG1 orFG2.


16/51

Default filegroup

user-defined filegroup (FG1) that includes two secondarydata files, one on drive E and one on drive F (with the log

file on drive G and the primary file on drive C).


17/51

Raid

If you use a RAID system, you might need to spread the data

from a larger-than-average table across multiple logical diskarrays configured on two or more RAID controller

SQL Server enables you to optimally distribute your dataacross disk drives because it automatically stripes, or

distributes, data proportionally across all the files in afilegroup. ("Striping" is the term used to describe distributing data across

more than one database file. )

SQL Server file striping is independent of RAID disk striping,and it can be used alone or in conjunction with RAID, as wesaw in our previous examples


18/51

Default filegroup


19/51

Rules and Recommendations

You should have a well-developed strategy for the use of files

and filegroups before creating your database. In order to dothat, you must know the following SQL Server 2000 rules:Files and filegroups cannot be used by more than onedatabase.A file can be a member of only one filegroup.Data and transaction log information cannot be part of the

same file. Log space is always managed separately from dataspace.Transaction log files are never part of a filegroup.Once a file is created as part of a database, it cannot bemoved to another filegroup. If you want to move a file, youmust delete and re-create it.


20/51

In addition to these rules

Most databases perform well with only a primary data file and

one transaction log file. This is the recommended design fordatabases that are not particularly I/O intensive. If you havean I/O-intensive system that requires many disk drives, youwill probably want to use user-defined filegroups to allow youto spread the data across disks or disk arrays for parallel I/Operformance.Always place log files on separate physical disks from thedisks containing data filesIf you do need to use multiple data files, use the primary data

file for system tables and objects only, and create one ormore secondary data files for user data and objects.


21/51

In addition to these rules

Create files and filegroups across as many physical disks asare available to allow a greater amount of parallel disk I/Oand to maximize performance.

Place nonclustered indexes for heavily used tables in aseparate filegroup on different physical disks from the diskscontaining the table data itself. This technique also allows forparallel disk I/O.Place different tables that are used in the same query on

different physical disks, if possible, to allow parallel disk I/Owhile the search engine is searching for data.

The last two items might not hold true for a system usingRAID volumes with many disk drives.


22/51

Automatic File Growth

SQL Server allows files to grow automatically when

necessary. When a file is created, you can specify whether toallow SQL Server to automatically grow the file. Allowingautomatic growth, which is the default when you are creatinga database, is recommended, as it saves the administrator theburden of manually monitoring and increasing file space

NOTE Automatic file growth is different from proportionalfilling. With automatic file growth, SQL Server automatically

increases the size of a file when the file becomes full. Withproportional filling, SQL Server places data in files inproportion to how much space the files have available butdoes not increase the files' size.


23/51

System Databases

When you install SQL Server, four system databases are

created

master

tempdb

model

msdb


24/51

System Databases

master

Records the system level information, SQL Server

initialization information, and configuration settings for SQLServer. This database also records all login accounts, theexistence of all other databases, and the location of theprimary file for all user databases. Always keep a recent

backup of the master database.


25/51

System Databases

tempdb

Holds temporary tables and temporary stored procedures.

This database is also used for other temporary storage needs

of SQL Server, such as for sorting data. A clean copy of thetempdb database is re-created at its default size every timeSQL Server is started. It then grows automatically, as

necessary. If you need a large amount of tempdb space, youcan increase the database's default size by using the ALTER

DATABASE command.


26/51

System Databases

model

Serves as a template for all other databases created on the

system, including tempdb. When a database is created, the

first part of it is created as a copy of the contents of the modeldatabase. The rest of the database is filled with empty pages.The model database must exist on the system because it isused to re-create tempdb every time SQL Server is started.You can alter the model database to include user-defined data

types, tables, and so on. If you alter the model database,each database you create will have the modified attributes.


27/51

System Databases

msdb

Holds tables that SQL Server Agent uses for scheduling jobs

and alerts and for recording operators. (Operators areindividuals who are assigned responsibility for jobs andalerts.) This database also holds tables used for replication.


28/51

Database Creation

SQL Server provides three methods for creating a database

Create Database Wizard

SQL Server Enterprise Manager

T-SQL commands that can be saved in a file and run as ascript


29/51

Database Creation

The Create Database Wizard has some limitations

The wizard places all data files it creates on a single drive in

one folder that you specify

Warning: you cannot put data files in different physicallocations (neither on different drives nor in different folders) ifyou use the wizard.

. You can place log files on a drive or in a folder separatefrom the data files but, again, in only one physical location.

User-defined filegroups cannot be specified, and all filesinherit the same growth options


30/51

Using the Create Database Wizard

For novice database builders, the

Create Database Wizard might bethe easiest method

Start SQL Server EnterpriseManager, and select the serveron which you want to create yourdatabase. To select the server,begin by expanding the MicrosoftSQL Servers folder


31/51


2. Double-click Create A Database to begin the Create Database

Wizard, which is shown in Figure 9-5.


32/51


3. Click Next to go to the NameThe Database And Specify ItsLocation screen, as displayed inFigure 9-6. Type the name of thedatabase you want to create andthe path locations where youwant to store your data files andyour log files


33/51


3. The Name The Database Files screen is displayed (Figure 9-

7). In this screen, you can type the name and initial size foreach of your database files.


34/51


5. The Define The Database File Growth screen is displayed

SQL Server can automatically increase the size of your databaseas needed, which helps to reduce maintenance overhead.


35/51


6. The Name The Transaction Log Files screen appears. Thisscreen looks the same as the Name The Database Filesscreen, but this version is for the log file. Be careful to not getthe screens mixed up. As you did in step 4 for your databasefiles, type the name and initial size for your transaction log.

(Remember that a transaction log contains a record of alldatabase modifications for recoverability in case of systemfailure.) The first transaction log file is automatically createdand given the database name as a prefix in its name. You canaccept this name or type a different one. The transaction logdata is stored in a file with the .ldf extension. You can add

more log files on different drives, if needed. If you have someidea of how big the transaction log will be, type a value now.Otherwise, keep the default size; you can modify it later usingEnterprise Manager or the ALTER DATABASE command. ClickNext to continue.


36/51


7. The Define The Transaction Log File Growth screen is

displayed. This screen looks the same as the Define TheDatabase File Growth screen, but here we are defining growthoptions for the log file. As you could in step 5, you can selectAutomatically Grow The Database Files, and if you want, youcan specify the growth parameters and maximum file size.Click Next to continue.

U i h C D b Wi d


37/51


8.The Completing The Create Database Wizard screen, shown in

Figure 9-9, is displayed. Review the information you have specifiedfor your new database. If it is acceptable, click Finish to completeyour database creation; otherwise, click Back and make anynecessary changes.

U i th C t D t b Wi d


38/51


9. Once your database has been created, a Create Database

Wizard message box appears to inform you that the databasehas been successfully created. Click OK to close this messagebox.10. Another message box appears, asking whether you would

like to create a maintenance plan for your new database.Creating a maintenance plan is recommended to ensure that

your database performs well, is regularly backed up in case ofsystem failure, and is checked for inconsistencies.

U i E t i M


39/51

Using Enterprise Manager

SQL Server Enterprise Manager enables you to create more

complex databases than does the Create Database Wizard. You can specify growth options for each file created, ratherthan for all the files as a group, and you can create user-defined filegroups. To create a database using Enterprise

Manager, complete the steps outlined in the following list. Inthis example, we will create a sample database named MyDB,

with a primary data file, three secondary data files (whichreside in the same user-defined filegroup), and one log file.

U i E t i M


40/51


Open Enterprise Manager. In the left pane, expand the SQL

Server group that contains the name of the server you wantto build the database on, and then expand the server nodeitself. Then right-click the Databases folder and choose NewDatabase.

The Database Properties window opens, with the General tabin front. Type the name of the database in the Name box.

U i E t i M


41/51


U i E t i M


42/51


3. Click the Data Files tab. As shown in Figure 9-11,

Enterprise Manager automatically creates the primary datafile, with the name of your database as a prefix and withPRIMARYas the filegroup. You can change the name, location,and size of the primary file, but you cannot change the

filegroup for the primary data file. Type the filename (logicalname), location (physical name), size, and filegroup for each

data file you want to create. For each data file other than theprimary file, you can type a user-defined filegroup name, andthat filegroup will be created for you. In our example, we

created the secondary data file MyDB_Data2 in the filegroupMy_FG.

U i E t i M


43/51




44/51


4. In the File Properties area at the bottom of the window, youcan select automatic-growth options on a per-file basis. Selectthe name of the file for which you want to set growth options.Select the Automatically Grow File check box to enableautomatic growth for that file. You can then specify the growth

increment in megabytes or as a percentage of free space leftin the file. You can also specify a maximum file size by clickingRestrict File Growth and entering a limit in megabytes, or youcan leave the file size unrestricted. You can set these optionsas you create each file, or you can leave the defaults and setthem later using the Enterprise Manager Database Properties

window. If you need to delete a file from the list, select thename of the file and press the Delete key.



45/51


5. After you have finished configuring all the data files, click

the Transaction Log tab in the Database Properties window toconfigure the transaction log files. Log files are configured inthe same way as data files, except that you cannot select afilegroup for log files because they do not belong to any

filegroup. Type the filename (logical name), the location(physical name), and the initial size for the log file or files.

Also, choose the automatic-growth options for the log files asdescribed step 4 for data files.6. When you have all the files defined as you want them, click

OK. SQL Server will create the database. Go back toEnterprise Manager and click the Databases folder for theserver you just added a database to. In the right pane of

Enterprise Manager, you'll see that SQL Server has added anicon for that database

Using T SQL Commands


46/51

Using T-SQL Commands

You might prefer to create or alter your databases by using T-

SQL commands or scripting rather than by using a graphicaluser interface (GUI). Creating your own scripts can be usefulwhen you create databases. Let's say you create the databasebut then realize you specified the wrong location for a file. You

can drop the database and start over. If you used a T-SQLscript to create the database, you will be able to edit it and

rerun it quickly, instead of having to reenter all the data into aGUI. You can also run the same script if you need to createthe database on another system, such as a warm backup

system



47/51


REAL WORLD A Simple Database In the following

example, we'll create a database named MyDB that contains aprimary data file (MyDB_root); one secondary data file(MyDB_data1), which remains in the primary filegroup bydefault; and one transaction log file (Log_data1).



48/51


CREATE DATABASE MyDB ON (NAME = MyDB_root, --Primary data file FILENAME = 'c:\mssql2k\MSSQL\data\mydbroot.mdf', SIZE = 8MB, MAXSIZE = 9MB, FILEGROWTH = 100KB), (NAME = MyDB_data1, --Secondary data file

FILENAME = 'c:\mssql2k\MSSQL\data\mydbdata1.ndf',SIZE = 1000MB, MAXSIZE = 1500MB, FILEGROWTH = 100MB) LOG ON (NAME = Log_data1, --Log file FILENAME = 'e:

\log_files\logdata1.ldf', SIZE = 1000MB, MAXSIZE = 1500MB, FILEGROWTH = 100MB)



49/51


A More Complex Database



50/51


RAID system

A primary data file, Sales_root.mdfThree secondary data files, customer_data1.ndf,customer_data2.ndf, and customer_data3.ndf, in the filegroupcustomers_groupTwo secondary data files, product_data1.ndf andproduct_data2.ndf, in the filegroupproducts_groupOne log file, log_data1.ldf

Sales



51/51


As the comments in this code indicate, the primary filegroup

can be explicitly stated before the primary data file is defined.The primary filegroup is the default.

All of the files listed after a filegroup definition will be placedin that filegroup until another filegroup is defined or the LOGON clause is reached.

The log file is also on a separate disk, with no data files, toallow the log to perform sequential writes.

01-sqlsrv2k db creation

Documents