Making Virtual SpiesCreating Spy Net in SQL Server 2000
- Making Virtual Spies—Creating Spy Net in SQL Server 2000
- Creating the SQL Spy Net Application Database
- Using the Data Definition Language (DDL) to Create Databases and Objects
- Bringing Our Data Model and Database Together
- Summary
- Next Steps
In This Chapter
Playing Program Director (Sys Admin) for SpyNet
Creating the SQL Spy Net Application Database
Using the Data Definition Language (DDL) to Create Databases and Objects
Bringing Our Data Model and Database Together
Ladies and gentlemen, this is it! The moment that we (well, I) have been waiting for. In this chapter things are really going to begin rocking. We will create the SQL Spy Net application database and our application tables. We will discuss important database concepts such as normalization and integrity. We also will finish drawing our entity relationship diagram (ERD) and complete the analysis of our application.
But first we must tidy up a couple of administration tasks. We must make sure SQL Server's installation settings are tailored for our project.
This combination makes for a bit of a long chapter, but these two segments, moving from diagram to database, go hand in hand. Along the way, I give you several tidbits of information on relational theory and good design practices—the "why" behind the option selections. You might want to review these asides when you create your next project.
Playing Program Director (Sys Admin) for SpyNet
First things first. Let's ensure our application (and SQL Server 2000) are secure. We will also get a brief introduction to user administration.
Because I am not psychic, I do not know what you set your sa password to, but I assume that you either did not supply one at all or that you used something like password.
sa stands for System Administrator. This gives you god-like control over SQL Server 2000. This user account can do anything it wants to the instance of SQL Server 2000 or the databases inside the instance. So be careful—that's a lot of rope to hang yourself with!
Even if you didn't set the password to either of these, the practice of changing your password will assist in your understanding of SQL Server 2000's security and show you how to change passwords, especially when users forget theirs, as they inevitably will!
Warning - By now I assume you've either followed the instructions in Appendix B, "Installing and Configuring SQL Server 2000," or you've gotten SQL Server up and running on your own. It's crucial that you follow the same configurations for connecting to SQL Server, and it's a good idea to check all your settings against those I've walked you through in Appendix B.
So, if you're operating on Windows NT or 2000, be sure you're using the sa account rather than the administrator account. If your administrator doesn't want you monkeying around with SQL Server on a network, reconsider installing Personal Edition on a standalone machine, as I've done for this book.
Setting the sa Password
If you do not have Enterprise Manager running, start the application now.
Navigate your way through the tree-view control in Enterprise Manager. You will find a folder called Security. This folder holds information relevant to the security of SQL Server 2000, including database logins (such as sa), server roles (such as System Administrators), and linked and remote servers (which allow you to link to other servers).
Within the Security folder you will see the Logins icon.
Click the Logins icon, and you will see the logins defined for this instance (and all the databases) of SQL Server 2000. If your screen is like mine, you will only see the sa login, as shown in Figure 3.1.
Double-click the sa login icon now. You will see a screen similar to that shown in Figure 3.2.
){kind=link}
){kind=link}
Figure 3.1
Logins defined in your instance of SQL Server 2000.
Figure 3.2
Details for the sa login.
Type your new password into the dialog box (make it something that you will remember but not that others can easily guess) and click Apply or OK. The system will automatically ask you to verify the password change that you have just implemented.
Warning - The only catch to changing your password is that you must edit the server registration properties that you use.
Click the SQL Server 2000 instance (under the server group that you created in Appendix B), right-click and choose Edit SQL Server Registration Properties. You will be presented with a dialog box similar to the initial registration screen. This appears in Figure 3.3.
){kind=link}
Figure 3.3
Edit SQL Server Registration properties.
After you enter the new password and click OK, SQL Server 2000 will ask if you want to change the connection information, as shown in Figure 3.4.
){kind=link}
Figure 3.4
Confirm active server connection changes.
After you have answered yes, SQL Server 2000 will update the connection information to use when you use one of the client tools to connect to SQL Server 2000.
Note - When SQL Server 2000 attempts to update your connection information, it disconnects the current connection that you have to the server, and then reconnects for you.
If you have any session-specific information set, for example the way to handle NULLs, this information might be lost. Therefore, when SQL Server 2000 reconnects, just check that your configuration options are still set.
Okay that's about it, a nice and easy process. For your users, however, you would not need to change the connection information, only their passwords.
The second administration task that we must perform—and this is quite important—is to ensure that the configuration options of the model database in SQL Server 2000 are set to best suit our needs.
The model database is a system database from which SQL Server 2000 uses to copy your new databases. SQL Server 2000 has several system databases, such as master and tempdb.
Configuring the Model Database to Meet Our SQL Spy Net Application Requirements
The model database has some basic configuration options that are copied when you create a new database, either through Enterprise Manager (using the user interface) or Query Analyzer (using Transact-SQL).
After SQL Server 2000 reconnects for you, drill through Enterprise Manager and locate the model database. We will check and alter where necessary the configuration settings of the model database to ensure we get the best settings to suit our needs when creating a new database.
With the model database selected, right-click the database and choose Properties. You will see a window similar to that shown in Figure 3.5. This screen provides a basic summary of the model database settings.
){kind=link}
Figure 3.5
The property dialog box for the model database.
Note - In most scenarios you would not need to alter these options except the initial space allocated option. On the whole, the defaults of SQL Server 2000 are normally fine.
We must ensure the following options are set for each tab; we will approach this in three stages:
Storage space allocation
Configuring the options for the model database
Who can do what—checking out the permissions
I'll break up the process into smaller chunks to give you more information on the options you're setting on each tab and why.
File Space
First we will check the basic storage space allocation for this database and any copied databases.
Click the data files tab; you will see a screen similar to that shown in Figure 3.6.
){kind=link}
Figure 3.6
Data files configuration settings.
This is the storage allocation for the data files that your database will contain. As you can see, in this configuration I have my data files for the database stored on the D drive. This is because disk I/O (input/output) is one of the largest bottlenecks in any application development.
Excursion
Watching Out for Bottlenecks - Although disk drives have gotten bigger and the access slightly faster, the same basic method for accessing data has remained virtually unchanged for more than 20 years!
For a database to request data from a disk, a single head (like the arm of a record player) scans the disk for information. You can imagine if you have 200 concurrent users that the job of that single head is enormous. It must retrieve data, update data, and maintain storage information at a rate fast enough to support all those users. So the best thing we can possibly do is split the load.
One of the best methods for achieving this is through a RAID array configuration, called Data Striping (RAID 0).
Redundant Array of Independent Disks (RAID) is a system of having multiple disks (called an array) to give better performance, reliability, more storage, and at a lower cost. RAID also has a fault-tolerance capability built in, which is actually one of the biggest factors to using RAID. It is available on RAID systems 0 through to 5.
Although RAID is not part of SQL Server 2000, it is actually a hardware configuration; the RAID levels 0, 1, and 5 are generally used with SQL Server 2000 and can affect the performance of SQL Server 2000.
Unfortunately, a RAID configuration is not an option on Windows 95 or 98 platforms.
This allows many disks to perform many tasks at the same time and all to be in sync with each other.
But if you are like me and this option is not available, what do you do? If you have multiple physical disks, you can split the data, transaction logs, and the program files across many disks. Unfortunately, if you don't have multiple disks you cannot implement this sort of option, but "hey them's the breaks."
To ensure that we do not run out of disk space, we manipulate some of the properties in the window. Make sure the Automatically Grow File option is selected and the File Growth Setting is configured to 10%.
Okay, I've done it, but what does it mean? The Automatically Grow File option specifies to SQL Server 2000 that when the data file reaches its upper limit of 5MB it needs to increase the file size by 10% (500KB).
Alternatively, we could use the Increase File Size by Megabytes option, but the problem with this option is that it can run out of disk space very early on, or there might not be enough file size increase later. For example, when the data file increases to 5MB, the system automatically increases it to 6MB. But if we only have 6MB left on disk, an error is returned.
In the second scenario, when our data file is 300MB in size, increasing it by 1MB will not give us enough of a growth in storage. If it is set to 10%, that is 30MB, which can tide us over for a while.
We also have the ability to set the maximum size of the data file, effectively allowing us to limit its growth to a set size. If we leave the unrestricted file growth option set, the data file will grow until we run out of disk space. However, if we know that our database will never grow too large or we want to put limits on its size, we can specify that the data file never be allowed to grow larger than a whole number of megabytes.
What are the other options on the screen for?
The filename is the name that SQL Server 2000 uses internally to refer to the data storage area. The space allocated is the initial total space allocated for the data file to grow. If, like my configuration, yours is set to 1MB, you need to bump this up to 5MB. You can achieve this by clicking in the field and typing the new size that you want to allocate.
The filegroup property is the filegroup to which the data file belongs. When a database is created the default filegroup of PRIMARY is created. The primary data file is added to this group along with any other data files that are not specifically assigned to any other filegroups.
The primary filegroup also contains all the system tables. We increased the initial size to ensure that the filegroup does not run out of disk space. If this happens, no new entries can be added to the system tables, and an error will be returned. This means you won't be able to create any new users, tables, stored procedures, and so on; and that is very fatal to any application!
The Transaction Log
Next we need to check the transaction log settings. Click the Transaction Log tab. As you see in Figure 3.7, the screen is similar to the data files screen, including options that are similar.
){kind=link}
Figure 3.7
Transaction log configuration settings.
The only difference here is that you do not specify a filegroup to which the transaction log belongs. Once again we are going to change the space-allocated size to 5MB.
Let's stop here and explain the data file and transaction logs. When you or your team inserts data into your database, it is written to the data file, which is physically stored on disk. This is what makes a database different from a C++ or similar application; we can persist the data.
Persisting data—With a C++ or VB application the data is held in memory. Therefore, the data is available only while the application is running. With a database the data is written to disk, allowing you to reuse it any time you want, hence you persevere (or persist) the state of the data.
The transaction log keeps a record of all changes made to the data in the database. This allows us to undo a change (rollback is the technical term; more about this in Chapter 8) if we need to. Generally your transaction logs will not grow too large, but this depends on the individual system's circumstances.
Filegroups
Now that you understand the changes we made, let's take a look at the next tab, Filegroups, as shown in Figure 3.8.
){kind=link}
Figure 3.8
The Filegroups tab settings.
Earlier we talked about the Primary filegroup, which contains all the system tables and any other tables that are not assigned to other filegroups. What does this allows us to do? You can have all your system tables defined into one filegroup (Primary) so that they can reside on one disk by specifying that a particular data file belongs to the filegroup.
Note - Although we can specify the following settings for our SQL Spy Net database (which we will build shortly), we cannot enforce these changes for the model database. If we made these the default specifications for the model database, whenever we create a new database the filegroup specifications would be copied also, and we would end up overwriting our data in our data files. Not a very good idea!
To prevent this from happening, SQL Server 2000 will not allow you to create new data files, transaction logs, or filegroups on the model database or any other system databases.
We can then have all our user tables defined into another filegroup so that they can reside on a separate disk. We can also have some user tables defined into one filegroup and others defined into another filegroup. This enables us to segregate our data files, thus increasing performance for our application.
How do we achieve this? We need to specify a filegroup as being the default. This means that whenever a table is created it is stored in the default filegroup. If a group is not specified as being the default, all tables will be stored in the Primary filegroup.
To remove a filegroup, you only need to select it and click Delete.
Note - To delete (drop) a filegroup, all data files that reference that group must be assigned to another group or removed. You will not be able to drop a filegroup unless all the data files have been removed.
Setting the Options for Our Model Database
The Options tab, shown in Figure 3.9, allows us to configure database-level operations that can make a dramatic difference to how the application behaves.
){kind=link}
The first setting that we have for the Options tab is the Access setting. This allows us to specify who can access the database. The following changes can be made to this:
Restrict Access allows us to specify that only certain database roles have access to the model database. This can be important to set if you want to prevent any users other than those in these roles from being able to modify the database settings. The Single User option means that only one user can access the database at any given time. Do not select this option; we do not need to restrict access at this stage.
Read-Only stops the data and the system objects from being able to be altered. Nothing in the database can be altered until the Read-Only setting has been removed. Do not set the database to be read-only. We need the databases that are created from the model to be editable.
Figure 3.9
The Options tab settings for the model database.
The second setting on the Options tab, the Settings group, allows us to specify the behavior of our database application.
ANSI NULL Default allows you to specify whether a column in a table will be NULL or NOT NULL by default. When this option is checked, the ANSI SQL-92 standard is used, which means that a NULL is assumed. However, this isn't SQL Server 2000's default, and this option should remain unchanged. Also, I believe in reducing NULLs out of our database as much as we can, and this option will assist in achieving this. Make sure this option is not enabled. We will take a look at NULLs again shortly.
The Recursive Triggers option allows triggers to fire recursively (the capability of a trigger to call itself). We will not be using recursive triggers immediately; so do not enable this option at this stage.
The Select Into/Bulk Copy option allows us to perform a copy of a database table's data and structure without logging it in the transaction log. Why would you do this? To insert data into a table the transaction is logged in the transaction log, so that you can rollback the transaction. When you use the Select Into statement, the transaction is not written to the transaction log, so the copy of the data and the table is much faster. The database does not keep track of the changes; the only issue is that you cannot rollback the changes. We might need this statement in our application database, so we will leave this option enabled (check the option if it is not already checked).
Truncate Log on Checkpoint allows SQL Server 2000 to reduce the size of the transaction log at some given point in time. This option prevents the transaction log from growing exponentially and thus prevents it from filling the disk. Ensure this option is enabled; it will save some difficulties about log sizes for the immediate future.
Torn Page Detection allows SQL Server 2000 to locate incomplete pages. When data is stored on disk it is written into a page. A page is 8KB in size, and disk I/O operations are performed using a 512-byte sector. So we get one database page on 16 disk sectors. When a sector is complete, a switch (a bit operator which is a 1 or 0) is set to indicate that the sector is complete for a page. If, in the process of storing data on the sector a power outage occurs, then the switch will not be set and SQL Server 2000 will detect that the sector was not written correctly. In other words, SQL Server 2000 will detect the torn page. It is best to leave this option enabled so SQL Server 2000 can detect corrupt data pages.
Auto Close specifies that the database will be shut down and all stray processes tidied up when the last user exits the database. This is especially useful for desktop editions of SQL Server 2000 (and is the default) because it allows the database to be manipulated as though it were any other file in the file system. This allows for copying, packaging, and mailing. It is not the default for other editions of SQL Server 2000 because the overhead of closing and reopening the database automatically when a user connects or disconnects is very costly. While deploying on a desktop version of SQL Server 2000 we will leave this option selected. This will help us to preserve resources on our PCs.
Auto Shrink allows SQL Server 2000 to shrink the data files and the transaction logs periodically. The files are shrunk only when they contain more than 25% of unused space. Like Auto Close, this is turned on by default for desktop edition databases and not for any other editions of SQL Server 2000. To prevent running out of disk space, ensure this option is turned on.
Auto Create Statistics allows SQL Server 2000 to improve query performance. The built-in query optimizer will be able to use the statistics for best evaluating how to execute a query. This is the default option (turned on) for all editions of SQL Server 2000. You can turn this option off and build the statistics manually, but it really is easier to allow SQL Server 2000 to handle this task. Because the default is to have this option enabled, and I am all for making our jobs easier, ensure that it is turned on.
Auto Update Statistics specifies to SQL Server 2000 to update the statistics that it uses. This option improves performance for queries because the query optimizer will be able to best evaluate how to execute a query with up-to-date statistics. Once again this option can be turned off so you can build the statistics manually. Ensure this option is enabled to gain the best performance from our queries.
Use Quoted Identifiers allows you to define object names with double quotes (""). This is useful if you have object names that do not follow the Transact-SQL rules or includes keywords. When this option is turned on and you are referring to an exact value (literal) you must use single quotes (''). By default this option is off because it is not good practice to use keywords for object names. We will discuss this a little later when creating our first table. One thing to note though is if you do have an object name that does not follow the rules, you can reference this with square brackets ([]). These brackets can be used whether this option is set or not. Because it is desirable not to use invalid object names in a database, ensure this option is not enabled. As an aside, some ODBC drivers do not interpret the double quotes as we would like them to, so it is better not to use this option.
Note - We will take a look at naming conventions and reserved words when we create our first tables in the "What Can We Call Our Columns?" excursion a little later in this chapter.
The final option that we are able to specify for the model database, and of course subsequent databases, is the compatibility levels of the database.
The Level option allows us to specify whether our database will be compatible with previous versions of SQL Server. The main effect this has is on a few Transact-SQL statements and on some internal processing of NULLs, namely CONCAT NULL YIELDS NULL. When setting to versions earlier than version 7.0 this option is ignored, so when a string is appended to a NULL, the value of the string is returned rather than a NULL.
Note - Watch out for the section "Preventing NULLs in Our SQL Spy Net Application" later in the chapter for more riveting information about NULLs.
The main benefit to setting the compatibility level to a version earlier than 8.0 (2000) is for planned upgrades. This means that a database written in a previous version can be slowly upgraded to ensure that no compatibility issues are between the syntax of the earlier Transact-SQL statements and the requirements of the newer version.
Even if a database is set to earlier compatibility than the current version, the database will still gain all the performance benefits of the newer tools, making upgrades a simple and easy process. Way cool!
Because we are building our application from scratch, ensure the compatibility level is set to Database Compatibility Level 80.
Let's look at each section of the CONCAT NULL YIELDS NULL statement individually. CONCAT is short for concatenate, which means to append two things together. A NULL is a special value that we have discussed earlier, which is like nothing but is not equal to either zero or an empty string. YIELDS is the product of an equation.
Once again I will use an example to demonstrate the concept behind the statement. If we have a string variable with a value of MyString and we try to append (or concat) this to another string variable with the value of NULL, the product (or yield) will be NULL. The formula is as follows: MyString + NULL = NULL)
Permissions
The last tab available is the Permissions tab. With this we can specify the default permissions for the actions that a user or role (similar to a group in NT) can perform on our database. As you can see in Figure 3.10, the only permissions you can alter are for the Public role, which we take a more in-depth look at in Chapter 9.
){kind=link}
Figure 3.10
Permissions that a user or group can perform.
If you do not specify what rights or permissions a user or role has at this level then the default is no permission. So the Public role will not be able to create tables, views, or stored procedures or to perform backups and so on.
Whose Database Is It, Anyway?
Why is the Public role the only one shown? We haven't created any other users or roles yet. We will implement some security in Chapter 9, but SQL Server 2000 has a default role called Public. This role cannot be removed from the instance of SQL Server 2000, but the permissions for this role can be modified.
We do not specify users in the model database because users are relevant to each database. So when the model database is copied we do not need any users it might contain.
What about the sa login—why doesn't it show in the list? The sa login is actually a member of the Public role, but it is also a member of a special role called db_owner.
This means that sa will have all the Public role's permissions, as well as all the permissions of the db_owner role. db_owner is a system role that doesn't allow us to alter the permissions for it. This is to prevent us from accidentally locking ourselves out of our own database.
For our application database and subsequent databases, we are better off leaving the default security settings intact for the model database.
We do not want to create problems for ourselves before we even begin! Because our users will be created later (in Chapter 9), if we modify the security now we might have problems ensuring the users have correct permissions and access.
And so, ladies and gentleman, this concludes our preliminary setup options for our databases that we create in SQL Server 2000. Next, we are going to create the SQL Spy Net application database and turn our plan into an application. So, put in your favorite spy-movie soundtrack, take a stretch if you need to, and let's get on with it!