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.
Storage Medium Considerations
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.
- Each server has its own dedicated storage disk(s)
- Often less costly to implement during onset
- 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.
- Ability to share data resources
- Backup times generally very fast and straightforward
- Provides scalability and flexibility
- More costly to implement
- Often times more complex than other options
Creating the Staging Database and Data Warehouse
Open Management Studio and navigate to your Object Explorer.
Right click the Databases folder and select New Database...
The New Database dialog window opens
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.
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
If we know we have 200 GB of data we will want to allocate 204,800 MB to our main data file.
One Gigabyte = 1024
1024 * 200 = 204,800
Set the log file size to 25 % of your main data file.
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.
- Set the size for main data file
Select the path for the main data file and the log data file. In this example we will accept the default pathThe default path may be different in your environment.
Navigate to the Options page
Set the Recovery Model to Simple
Repeat this process for the Data Warehouse.
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.