SQL 2005+

Creating SQL Server Databases Part 1

The third part of the SQL Server Programming Fundamentals tutorial investigates the creation of new databases. This first task in any new database project requires the careful consideration of several key configuration options.

SQL Server Management Studio

The Microsoft SQL Server relational database management system executes on a Windows-based computer as a group of Windows services. These background processes run continuously and process any database activities that are required. However, as these are services, they have no user interface that permits their configuration.

SQL Server Management Studio (SSMS) is a tool that is distributed with SQL Server. This powerful utility provides a graphical user interface that connects to one or more local or remote SQL Server installations and allows the user to perform configuration and design tasks. These include the creation of new databases and their constituent parts, and the execution of queries and other SQL statements.

NB: SSMS can be installed using the original SQL Server CD-ROM or DVD-ROM. If you are using SQL Server Express Edition, SQL Server Management Studio Express edition can be utilised instead. If this tool is not installed, you can download it from the Microsoft SQL Server web site.

Using SSMS, databases can be created in two ways. Firstly, SSMS provides screens that guide you through the process. Secondly, SSMS can execute commands to create a database in a textual format using a query window. In this article, we will use SSMS's graphical user interface tools to create a new database for the JoBS project. We will look at the command-based approach later.

This article assumes that you have access to a SQL Server installation and have the details and login credentials required to connect to it and create databases.

Starting SQL Server Management Studio

By default, SQL Server Management Studio is installed into a program group named "Microsoft SQL Server 2005" in the Start menu. To start SSMS, simply click the associated shortcut. After a few seconds, the "Connect to Server" dialog box is displayed.

Connecting to a SQL Server Instance

The Connect to Server dialog box allows you to specify the details of the SQL Server that you wish to connect to and the credentials that you wish to use to authenticate with that server. The information that must be completed is as follows:

• Server Type. SQL Server includes various options for server types, including Database Engine for core database functionality, Analysis Services for analytical data warehouses, Reporting Services for reporting purposes and Integration Services for integration work. Other options may also be available depending upon your particular workstation configuration.
• Server Name. This allows you to specify the name of a SQL Server to connect to. Each SQL Server on your network will have one or more instances. An instance is a named installation of the SQL Server product. If the server instance you wish to connect to is the default instance, you simply provide the server's name. If a specific, named instance is to be used, this is provided in the format <server-name>\<instance-name>. For example, the figure above shows connection to the VIRTUALXP server's default instance. An alternative option may be VIRTUALXP\Instance2.
• Authentication. SQL Server's database engine provides two forms of authentication. Windows authentication uses your current login name to determine your level of access. SQL Server authentication requires that you provide a user name and password.

To connect to SQL Server in readiness for creating the JoBS database, select the "Database Engine" server type and provide the details of the server name, and optional instance name, and your authentication details. Click the Connect button to make the connection. Once connected, the SSMS main screen will be populated.

The SQL Server Management Studio Main Screen

The SSMS main screen includes some standard elements, such as a menu bar and toolbar at the top of the screen and a status bar at the bottom. These are used to perform actions within the tool. The main area of the window is divided into two sections. To the left is the Object Explorer. This displays an expandable tree containing the hierarchy of objects from the SQL Server instance. To the right, the Object Explorer Details section shows the contents or configuration of the selected item in the Object Explorer.

System Databases

On starting SSMS, you will find that some databases already exist. These will be separated into two distinct areas that are accessible by expanding the Databases section of the Object Explorer tree. Within this branch of the tree is a further, expandable node named "System Databases". Databases created by the system appear here whilst user-created databases are displayed at the higher level.

All SQL Server instances include four system databases. These are named master, model, msdb and tempdb.

The "master" Database

The master database contains a series of system tables, stored procedures and other database objects. This database is critical to the correct operation of the entire SQL Server instance. It includes all security information, configuration information for all databases and many other system settings. In some, rare situations it can be useful to query this database. However, in general your only interaction with the master database should be ensuring that it is backed up. Incorrect changes made to this database or corruption of its data can potentially render the entire system unusable.

The "model" Database

The model database is a template for all databases that you create. When you add a new database to a SQL Server instance, a copy of the model is made under the name that you provide. This means that any changes that you make to model will be duplicated in all new databases.

The "msdb" Database

One of the Windows Services that forms part of Microsoft SQL Server is the SQL Server Agent. This service permits the scheduling of tasks such as backups and maintenance jobs. When a SQL Server Agent task is created, the configuration of the job is held in the msdb database.

The "tempdb" Database

The tempdb database is a special system database that is not persisted when the SQL Server service is inactive. Each time the service is started, tempdb is rebuilt. This database is used to hold information temporarily during complex queries, either because SQL Server requires it or because a procedure or query that you write creates a temporary table.

Creating a Database

Creating a new database is a relatively simple task. However, there are a number of options that can be set during creation to alter the behaviour of the new database. In the remainder of this article we will create the JoBS database whilst examining the most relevant configuration items.

NB: In this tutorial we will be creating a database named JoBS. In the event that your SQL Server instance already has a JoBS database, select a different name and use this new name in place of JoBS for the rest of the tutorial.

The New Database Dialog Box

The simplest manner in which to begin the creation of a new database is to right-click the "Databases" tree node in the Object Explorer. This displays a context-sensitive menu. From here, select "New Database..." The New Database dialog box will appear:

Database Name

The first page of the New Database dialog box allows the new database to be named. In the simplest scenarios, this is actually the only piece of information that must be provided. All other configuration options can be left at their default settings.

The name of a database can contain letters, numbers, spaces and many symbols. This gives a great deal of flexibility to the name but can also lead to difficult-to-read names and names that cannot be used without adding delimiters. It is usual, therefore, to use only letters and numbers and no spaces. A good convention is to use Pascal case when joining multiple words and capitalisation for abbreviations, as in the example database.

To set the name of the database, type JoBS into the Database name text box.

Database Owner

All databases are assigned an owner. This is a user that has full rights to modify and maintain the new database and may be any Windows account or SQL Server login with the rights to create databases. For new databases, the Owner textbox shows <default>. This indicates that the owner will be the current user, as used to log in to the database. The owner can be changed by typing a new name or searching for a suitable login using the ellipsis button.

For the JoBS database, use the default option.

Full-Text Indexing

SQL Server includes a facility known as full-text indexing. This allows the construction of special indexes that permit searching of textual data. This can be useful when holding large sections of text that need to be read by a search engine similar to Google, for example.

In this tutorial we will not be examining full-text indexing so leave this checkbox blank.

Database Files

All databases use two files by default. These are the primary data file and the log file. The primary data file keeps track of all other data files and holds some or all of the information that is stored in the database. The log file contains details of transactions that have occurred against the database and allows those transactions to be recovered in the event of a disaster.

Each file is listed in a table in the dialog box. The table shows seven columns:

• Logical Name. This is the name of the file that SQL Server will use. By default, the name is based upon the database name.
• File Type. The file type is either "Data" or "Log", depending upon its purpose.
• Filegroup. The name of the filegroup that the file will be created within. See below for more information on filegroups.
• Initial Size. SQL Server files do not grow continuously in the same manner as files in applications such as those in Microsoft Office. Instead, the files are created with a specific size and grow in increments when the available space is exhausted. This provides improved performance, which is critical in database systems. The Initial Size column shows the starting size of each file in megabytes.
• Autogrowth. When a data or log file becomes full, the autogrowth settings determine the action that is taken by the database engine. If autogrowth is disabled, the database simply raises an error to indicate that the file is full. When enabled, the data file can be set to increase by a specific size in megabytes or by a percentage of the current file size. Each file may be set to grow indefinitely or can be limited to a specific maximum size.
• Path. This column shows the path to the folder in which the file will be created.
• File. The last column in the table shows the name of the database file. For new databases, this appears blank.

Additional files can be added to a database. These may be secondary data files or additional log files. When extra data files are added, the information held in the database is distributed amongst all of the available files. This allows files to be spread over a series of disks for maximum performance.

For the JoBS database, use the default settings for the data and log files.

Filegroup Options

Filegroups are simply administrative containers for database files. Every data file created is allocated to a single filegroup. In many databases, the default "PRIMARY" filegroup is the only one in use.

Filegroups are useful because individual tables and indexes within a database can be allocated to a particular filegroup. Using this facility, the disk location of groups of tables can be determined by the database administrator (DBA). This allows configurations where highly utilised data can be placed in a group of files that is using very fast disks whilst archive data is held in a filegroup using slower hardware.

New filegroups can be added to a database by clicking on the Filegroups option at the left of the dialog box. Additional filegroups can be created by clicking the Add button and providing a name. One filegroup can be marked as the default. This is the one that will be used by new tables and indexes where a filegroup is not specified.

For the JoBS database, we will use the single, default filegroup.

Database Options

The final set of database options can be viewed and modified by clicking "Options" at the left of the dialog box. This page shows various general configuration options that may be applied to the new database.

Collation

The collation setting determines how information in the database is compared and sorted by SQL Server. This is important as different languages and cultures use different rules for comparing and sorting text. The collation also determines whether capital letters and lower-case letters are considered to be equivalent.

When installing SQL Server, a default collation is required. If a collation is not specified for each new database, the default collation is used instead.

For the JoBS database, use the default collation for the SQL Server instance.

NB: The JoBS database in the tutorial examples uses the Latin1_General_CI_AS collation. If your collation is set differently, the results of comparisons and sort operations may vary from those shown.

Recovery Model

SQL Server provides three recovery models that determine how a database's log file is used. Each model has advantages and disadvantages, as explained below:

• Full. The full recovery model is used to minimise the chance of data loss in a disaster situation. In this model, all transactions are logged to the log file in detail. The log file must be backed up along with all data files. In a recovery situation, the data files and log files can be restored to a specific point in time and usually no data is lost. It is possible that the log file can be damaged in which case operations since the last log file backup must be re-run. This model can lead to very large log files if they are not backed up and truncated often. The full recovery model is useful for business-critical systems.
• Simple. In the simple recovery model the log file is automatically cleared by the system to keep it small. There is no requirement, or benefit, to backing up the log file. Instead, only the data files are backed up. This limits recovery operations to restoring to the point at which the database was last backed up. The simple model is useful for development and test databases, or for non-critical live applications.
• Bulk Logged. The bulk-logged model is similar to the full model except that most bulk operations are logged in minimal detail. The lowers the size of the log files at the cost of not being able to recover to a specific point in time. The transaction logs must be backed up when using this option. The bulk-logged model is useful for small periods of bulk transactions. Following these periods, the database can be backed up and switched back to the full model.

As the JoBS database is to be used for example purposes only, choose the simple recovery model.

Compatibility Level

The Management Studio can be used to create databases for earlier versions of SQL Server. If an earlier version is to be targeted, an alternative option can be selected from the Compatibility Level drop-down list. Choosing an earlier version will mean that less functionality is available.

For the JoBS database, ensure that the SQL Server 2005 option is selected.

Other Options

The final area of the New Database dialog box contains a set of further configuration options that can be adjusted as required. These are beyond the scope of this article and should be left at their default settings for the JoBS database.

Creating the New Database

Now that the entire configuration for the new database is complete, it can be generated. Click the OK button to complete the process. You will notice that the status at the bottom-left of the dialog box changes briefly to "Executing" whilst the model database is duplicated and the configuration settings are applied. Once completed, the dialog box will close and the Object Browser will have a new tree node named "JoBS" beneath the Databases branch.

Congratulations, you have now created the new database!