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).
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.
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
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
1 Gigabyte = 1024
1024 * 200 = 204,800
- Set size for log 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
6. Navigate to the Options page
6. Set the Recovery Model to Simple
6. Click OK
7. 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.