Author: Michael Gonzalez
Frequently Answered Questions
Miscellaneous

File Text Search & Replace Utility
HTML Text Extraction using innerText
E-mail (CDONTS.NewMail) Sample Code
SQL Server 7.0/2000

Incorporating ASP and SQL Server
100's of T-SQL Scripts
Don't Use @@ERROR with UPDATE Statements
Exporting Tables to Text Files
Creating SQL Server Databases
ASP (SQL) Query Analyzer
Increasing SQL Server Performance with Indexes
Distributed SQL Server Transactions & Queries
COM/COM+ Development
What is COM?
Isn't ActiveX and COM the same?
How can Components benefit my ASPs?
Am I using COM Components now?
How do I use COM Components in my ASPs?
Creating your First COM Component
Creating a COM Component that uses ASP Intrinsic Objects
Creating a COM Component to access an MS-Access Database
MTS Component Template
MSMQ Component Template / Example

Creating SQL Server 7.0/2000 Databases

Using the SQL Server Enterprise Manager (easiest method):
  1. Go to the server where SQL Server 7.0/2000 is installed.
  2. Open the SQL Server Enterprise Manager.
  3. Double-click on the server that is to be integrated into your ASP pages. If the server is not registered, register it by right-clicking on Microsoft SQL Servers then clicking on New SQL Server Registration.
  4. Open your server by clicking on the + sign.
  5. Right-click on the Databases folder.
  6. Click on New Database....
  7. Type in the name of your database in the Name: field. If you intend on learning the rest of my tutorials, enter the name ASPAlliance.
  8. Click inside of the Initial size (MB) field and enter the size, in megabytes, you want the database to be. If you intend on learning the rest of my tutorials, enter 100 in this field.
  9. If you have more than one hard drive or more than one parition: click on the Location button. Choose a hard drive or partition where this database file is to reside and that it's something other than where the SQL Server program files are stored (this will improve performance and redundancy). You can also specify more than one file to store your database on. If you are to build a database that is going to be hit quite often, I recommend storing the database on two seperate files: each one on a seperate SCSI or IDE hard drive - preferably!
  10. Click on the OK button. Depending on the size of your database, it could take a while for SQL Server to format the new database file.
Using the SQL Server Query Analyzer (T-SQL)(learn this method):
  1. Go to the server where SQL Server 7.0/2000 is installed.
  2. Open the SQL Server Query Analyzer.
  3. Use the following code as a template for creating the database: CREATE DATABASE ASPAlliance ON PRIMARY --This is where you define your first (primary) data file ( NAME = "ASPAlliance_Data", FILENAME = "D:\MSSQL7\Data\ASPAlliance_Data.mdf", SIZE = 200MB, MAXSIZE = 400MB, --> You can also use UNLIMITED in place of a size FILEGROWTH = 10% --> If the file becomes full, SQL Server --> automatically increases the size by this value ) LOG ON --This is where you define your Transaction Log file ( NAME = "ASPAlliance_Log", FILENAME = "D:\MSSQL7\Data\ASPAlliance_Log.ldf", SIZE = 100MB, MAXSIZE = 200MB, --> You can also use UNLIMITED in place of a size FILEGROWTH = 10% --> If the file becomes full, SQL Server --> automatically increases the size by this value ) If you intend on learning the rest of my tutorials, use the exact numbers as is shown here - with the exception of the file path: choose an appropriate location (for both the data and log files).
  4. Press the F5 key to execute the command.
Notes & Tips:
  • SQL Server uses the model database as a template for new databases:
    Never, ever, ever, delete the model database in SQL Server. Why? Whenever you create a new database, SQL Server copies the model database and modifies according to the newly created database's properties. If the model database were ever deleted - you would NEVER be able to create another database again (without reinstalling SQL Server all over again)!
  • You can specify the size using KB or MB:
    In using the T-SQL method to create a database, you can specify the size you want the data file to be in either KB or MB units. The minimum size a database can is 512KB.
  • What files make up a database?
    1) The primary file, or MDF(Master Data File), is used to store all of the data in the database (including objects, metadata, etc.).
    2) The secondary file, or NDF, is used to store data when the primary file runs out of space. I recommend using a secondary file for performance reasons (as discussed below).
    3) The transaction log file, or LDF(Log Data File), is used to store transactions: SQL commands are considered transactions and, depending on whether there's a failure or not, are commited to the data file(s), or saved, or rolled back, deleted.
  • sp_helpdb is your friend:
    Use this T-SQL EXEC sp_helpdb command to get information about all of the databases on a given SQL Server installation. (Use the SQL Server Query Analyzer to perform this)
  • Simple and fast database creation:
    The simplest and fastest way to create a default type of database is by using the CREATE DATABASE MyDB command (substitute MyDB with the desired name). (Use the SQL Server Query Analyzer to perform this)
  • Did you know you could store a database on a non-NFTS/non-FAT partition?
    Yep - you can! Partitions that have not been formatted using the NTFS or FAT file systems can be used to store your databases - these are called raw partitions. This can be beneficial if you have a large partition somewhere that has been formatted with another FS such as Linux or Unix. To create a database on a raw partiion, you use the T-SQL CREATE DATABASE command and, instead of specifying a path to the database file, you specify the drive letter of the partition. Normally, you gain access to a non-NFTS/FAT partition by mounting it in some fashion. This allows you to access the partition by using a drive letter (such as E:). Here's an example on how to use this method: CREATE DATABASE Employees ON ( NAME = Empl_dat, FILENAME = 'f:', --> Points to a raw partition SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 ) LOG ON ( NAME = 'Sales_log', FILENAME = 'g:', --> Points to a raw partition SIZE = 5MB, MAXSIZE = 25MB, --> You can also use UNLIMITED in place of a size FILEGROWTH = 5MB ) GO There are disadvantages to using raw partitions for your databases: you can't use normal NT file system commands such as COPY; you can't perform NT Backups on the database files; only one database file can be created on each raw partition; etc.
  • Renaming a database with sp_renamedb:
    You can use the SQL Server Enterprise Manager to rename a database or the T-SQL command EXEC sp_renamedb @oldname = "olddbname", @new_name = "newdbname". For example, if you wanted to rename a database called MyDatabase to MyDB, you would type and execute the following T-SQL command in the SQL Server Query Analyzer: EXEC sp_renamedb "MyDatabase","MyDB" -- Or, you can use -- EXEC sp_renamedb @old_name = "MyDatabase", @new_name = "MyDB"
Personal Recommendations:
  • Always seperate your SQL Server program files from your data files:
    By default, the SQL Server (both 7.0 & 2000) installation installs both the data and program files in the same folder and on the same drive.

    The problem I find with this default is one of redundancy and performance: If you have your SQL Server installation with both the data and program files stored on, let's say, drive E:, and drive E: failed to operate, you would loose both your data and your program files. Not only would you have to restore your data files, you would also have to reinstall SQL Server all over again. This isn't very efficient, especially in an OLTP (Online Transaction Processing) environment such an accounting system where people are entering data all day long - a long wait for an SQL Server installation and restoration can be quite time-consuming!

    If your program files are stored on the same drive as your data files, you could get a performance decrease as a result. Why? Because, the SQL Server services are constantly calling program files as needed to handle service requests. This causes your hard drive to work hard at both granting access to program files and data files! So, I recommend placing the program files on one hard drive and the data files on another!
  • Use more than one file per database for heavily used servers:
    With SQL Server 7 and 2000 (not 6.5), you can use multiple files to store the contents of one database. This can improve performance goals significantly! I would suggest storing one file on one hard drive and the other on another hard drive. This will improve disk I/O.
Comments & Questions Form

Send It!