Overview

This article is intended to provide technical users with a broad overview of the processes and considerations involved with the manual creation of a database(s).

This article is geared toward those organizations with installations of 100 GB or higher.

Considerations

Storage Mediums

The storage medium chosen for data storage will depend on an array of factors such as (cost, administration, performance, etc.) It is important to design such systems accordingly as to support the growth, longevity, and meet the needs of the organization. Although many organizations have already adopted storage strategies / technologies we will provide a quick overview of some of the most common storage mediums.

The following provides a quick overview of storage mediums and additional research should be done to ensure that the right solution for the organization

Direct Attached Storage (DAS)

This is the most common of server storage options. Disks are either internal to the server or may be in an array that is directly attached to the server.

Pros

Each server has its own dedicated storage disk(s)
Often less costly to implement during onset

Cons

No sharing of data resources
Wasted utility of disk space
Backing up data among disparate disks can become complex
Poor scalability
More costly in the long run

Storage Area Network (SAN)

SAN's provide organizations with a powerful alternative that allows for the sharing of data resources.

Pros

Ability to share data resources
Backup times generally very fast and straightforward
Provides scalability and flexibility

Cons

More costly to implement
Often times more complex than other options

Manually Creating a Database in SQL Server Management Studio

Creating the Staging Database and Data Warehouse

1. Open Management Studio and navigate to your Object Explorer.

2. Right click the Databases folder and select New Database...

The New Database dialog window opens

3. In the Database name: field, assign a name for your database. In this example we named our database JetNavStage.

Notice how the logical name column is assigned to the database files

In the Database files: pane you will notice two files. In this example JetNavStage is our main data file (.mdf) and JetNavStage_log is our log data file (.ldf).

The main data file will contain all of our current data.
The log data file keeps track of your database transactions and helps to ensure data and system integrity.

4. In the Initial Size (MB) column, set the Initial Size of your .mdf and .ldf. Our goal is to make the data files large enough to hold all of your expected data.

Set the size for main data file

For example if we know we have 200 GB of data we will want to allocate 204,800 MB to our main data file.

1 Gigabyte = 1024

1024 * 200 = 204,800

Set size for log data file

Set the log file size to 25 % of your main data file.

For example if our main data file is 204,800 MB we will want to allocate 51,200 MB to our log file.

204,800 / .25 = 51200

The size of your data file is only limited by the size of your hard drive.

5. Select the path for the main data file and the log data file. In this example we will accept the default path

The default path may be different in your environment.

6. Navigate to the Options page

6. Set the Recovery Model to Simple

6. Click OK

7. Repeat this process for the Data Warehouse.

Setting Backups

There is no need to backup the Data Warehouse or the Staging database as this will only incur unneeded overhead. This is due to the fact that we can easily rebuild these structures using the Jet Data Manager.

Manually Create a Database in Management Studio (Large Installations)