Database Reference
In-Depth Information
Figure 5-12.
This database is missing a valid owner
Perhaps the easiest way to resolve this issue is to assign a valid owner using a SQL login that you know will
be on all SQL Server installations. The SA login was used in Figure
5-7
.) The SA login is built into Microsoft SQL
Server as a system administrator and works very nicely for this occasion. Although it is disabled by default, it still
exists on each and every installation of SQL Server and by its mere existence can be used as a valid owner. When
you create your database using the new database dialog window, the SA account is a simple and effective choice
that can safely be used for nonproduction computers. On a production machine, you normally use a Windows
domain account.
■
The authors, and the legal department, are really hoping that you are not using a production server to
perform the exercises in this topic, so the SA login should work just fine throughout the text! Please let the lawyers
sleep at night and use only nonproduction servers. They get grouchy when they don't get their sleep!
Note
Setting the Database Size
During this creation process, many decisions must be made; such as where the files for the database should go
or what their initial size will be. In the examples from this topic, you can safely leave these at the default settings
because the samples are very small. In real life, however, you will probably want to put your data warehouse on
its own separate hard drive and try to match the initial size as close as possible to its expected size.
Every database you create on SQL Server will have at least two files with which to be concerned. They are
the data file and the log file. The data file holds all the data from your tables, and the log file records any changes
to the data file. All changes are recorded to the log file before they are changed in the data file. A synchronization
process, known as a
checkpoint,
occurs on a regular basis to record all the changes from the log file into the data
file. This is performed automatically.
When estimating the size of the data warehouse, the two files should be adjusted according to their
function. The data file needs to be large enough to hold all your data, but the log file can be quite small. In an
OLTP database, the transaction log is expected to be receiving continual transaction entries, but the log file has
much less work to do in an OLAP data warehouse. The log file records data entries while importing data from the
OLTP source to the OLAP destination, but reporting statements are not logged. Microsoft recommends an OLTP
database log file be set to 25% of the size of its associated data file. In an OLAP data warehouse, you can usually
get by with a lot less: approximately 10% of the data file. So, if you made your data file 100 MB, the log file will
then be 10 MB.
Listing 5-1 shows the code for creating a typical OLAP database. We have placed the files on a different hard
drive to maximize performance and started with the D drive, so the Windows operating system is on a separate
hard drive as well.
