|
Using Microsoft SQL Server
How to set up Microsoft SQL Server for use with Hit List
Enterprise or Hit List Live
As you know, Microsoft SQL Server (versions 6.5 or 7.0) can
be used with Hit List Enterprise or Hit List Live as a back-end
database to store the information from your webserver. Below
are instructions on how to set up either SServer 7.0 or SServer
6.5 for use with Hit List.
SQL Server 7.0
Hardware / Memory Suggestions
Hit List and SQL Server may be installed on the same machine
or, if you already have a fast SQL Server machine, on different
machines. You will get optimal performance from the multiple
machine configuration if both machines are powerful. The SQL
Server machine should be a fast multi-processor Pentium II
with at least 512MB of RAM. If Hit List is running on a separate
machine, that machine may be a single-processor with at least
256MB of RAM. We also strongly recommend running SQL Server
on a machine with at least one RAID array rather than isolated
IDE or SCSI drives. This may improve performance by several
times (Microsoft suggests a 10x speed improvement for RAID
vs. isolated SCSI drives).
Always apply the latest SQL Server Service packs before using
Hit List. For SQL Server 7.0 Service Pack 1 has now been released,
for SQL Server 6.5, Service Pack 5 - you should check with
Microsoft for the
latest information.
Server Configuration
Using the SQL Enterprise Manager, select the server to
configure and use a right-mouse click to view its Properties
(or select Properties from the Actions menu). Establish
these settings:
Memory tab - Use a "fixed memory" setting of 320
for 512MB machines. On machines with more memory than 512
mb, use a "fixed memory" setting of betweem 2/3 and 3/4
of the computer's memory for SQL Server. Select "Reserve
physical memory for SQL Server".
Processors tab - Use ALL processors, and do NOT
boost SQL Server priority
Connections tab - Max connections = 64, or simply
leave the default (0 for unlimited)
Everything in the "Default Connection Options" list should
be left UNchecked.
Database Configuration
1. Create a SQL Server database for Hit List to
use by selecting the Databases branch and using a right-mouse
click to select New Database.
2. In the General tab:
A. Enter the name of the new database (e.g.
HitList).
B. Select the location of the primary data file
by either accepting the default or using the ƒ button
to choose a location.
C. Set the initial size. We suggest a default data
size of 2GB.
D. Ensure that the "Automatically Grow File" and
"Unrestricted Filegrowth" options are both ON.
3. Switch to the Transaction Log tab.
A. IMPORTANT! For optimal performance, we very
strongly suggest that the primary data file, as specified
in the General tab, and the Log file, as specified here,
are on separate physical drives with no file compression
if you are using issolated SCSI drives. If youÍre using
a single RAID array, youÍll probably see best performance
by putting the Data File and the Transaction log on the
same RAID 0 array rather than the Data file on the RAID
and the Transaction Log somewhere else. The ideal configuration
is to use two RAID arrays. The Data RAID array should
be RAID 0 and the Transaction Log RAID should be RAID
5.
B. Select the location of the primary log file
by using the ƒ button to choose a location on a different
drive than the one that the data file is stored on.
C. Set the initial size. We suggest a default size
of 1GB.
D. Ensure that the "Automatically Grow File" and
"Unrestricted Filegrowth" options are both ON. Now hit
OK and SQL Server will create the database.
4. IMPORTANT! Once the Database has been created
by SQL Server as a result of (3), switch to the Options
tab and ensure that both "Select Into / Bulk Copy" and "Truncate
log on checkpoint" are on. These settings are critical to
making sure the database can grow when it needs to, and
that the transcation log doesn't outstrip the actual database
in growth.
5. OPTIONAL: You may want to create a SQL Server
"login" for the Hit List database (to be used in the next
step as well as when directly running queries against
the database from a query window). This is fine, create
the login according to the instructions in your SQL Server
documentation, just remember that you MUST have the login
'aliased as dbo' for the Hit List database to allow Hit
List to do everything it needs to do (create tables, optimize,
update, etc. etc.). If you don't want to create a login,
you can use the 'sa' account as noted below with the 'sa'
password.
6. Ensure that ODBC is installed and that you
have a System DSN set to use/communicate with the newly
created Hit List SServer database. To set up this System
DSN, read the following (note that your configuration
may vary slightly given your needs or SQL Server requirements/location
on your network):
Setting Up an ODBC System DSN:
Go to the ODBC applet in the Control Panel.
Create a new System DSN for use with Hit List, by going
to the System DSN tab and clicking "Add"
Pick the SQL Server driver, making sure it's 3.60xxxx
or later (if not, you WILL want to update your Data
Access Components first from http://www.microsoft.com/data
- download and install before going any further - pick
the approx 6.2 meg download, that will do fine).
Use "HitList" for the name of the data source, and
pick Local from the drop down select for the SQL Server
to connect to, and hit Next.
Now use "SQL Server authentication", NOT "NT Authentication",
and check the "connect to SQL Server . . " checkbox,
enter "sa" without quotes as the Login ID and its password
(assuming you have an sa password, some don't, in which
case you can leave this blank) and hit Next. Note here
that you also can use a different SQL Server login account
for the Hit List SQL Server database as discussed above
in (5), but you need to have set this up beforehand.
Check your SQL Server documentation for more details.
Now check the "default database" setting, pick the
Hit List database you've created already (before today
or as described above) leave the other settings alone,
and then hit Next.
Now "Let SQL Server ODBC driver choose the translation
method" and hit Next.
Now hit Finish.
Now "test data source" - it should work, meaning your
ODBC connection should be ok. Hit OK, then OK again.
Now go to Hit List. Under Options/Database, Browse
and pick HitListNew as your data source, "sa" without
quotes as the user and leave the password field blank
or enter the sa password, then hit OK.
7. After you start Hit List, select the Database
tab in Tools/Options dialog box then enter this ODBC System
DSN you just created, including user name and password
to connect to the Hit List database on SQL Server.
8. Final note: you will NOT be able to open the
Database Manager in Hit List against this new SQL Server
database until you either put some logs into the database
or if using Live, some packet data. The Database Manager
will error out, saying the database cannot be opened,
and it is correct - there's nothing in the database for
it to look at yet, until you put at least a minimal amount
of data in.
SQL Server 6.5
Hardware / Memory Suggestions
Hit List 4.0 was designed for SQL Server 7.0 not 6.5. Although
the system will function correctly on SQL Server 6.5, a number
of performance changes to the SQL Server architecture in version
7.0 make it vastly preferable to SQL Server 6.5.
See the SQL Server 7.0 section above for hardware recommendations.
Always apply the latest SQL Server Service packs before using
Hit List. As of this writing, Service Pack 5a was available
for SQL Server 6.5. You can download Service Packs from Microsoft
Server Configuration
Use the Configuration dialog box in the SQL Server Enterprise
Manager (Server/SQL Server/Configure/Configuration tab)
to establish the following settings:
1. Locks = 20000
2. User Connections = 64
3. Open Objects = 500
4. Memory = 163840 (assuming a 512MB system)
5. Default SortOrder ID = 50 (Binary order). Using
the default setting of 52 (Dictionary order/case insensitive)
also works but will be about 25% slower.
NOTE: SortOrder can only be changed by running the
SQL Server Setup program again and reinstalling SQL Server.
This setting applies to all databases you create in SQL
Server, Hit List databases or otherwise and if you change
it AFTER setting up your SQL Server, you will have to
reinstall the entire SQL Server setup.
NOTE: Most of these options do not take effect until
SQL Server is restarted, so after setting them as indicated
above, stop then restart the SQL Server NT Service so
they will take effect.
Database Configuration
1. Create a database in SQL Server for Hit List
to use.
A. General. For best performance, we very
strongly suggest using a different data device, on different
physical hard drives, for the Data and for the Logs. For
example, create a 2GB Database Device on D: called HitListData
and a 2GB Database Device on E: called HitListLogDevice.
B. Data Size. We suggest a default data
size of at least the size of the log files you wish to
process if you need to store Detail data. If Summary data
will suffice (keep in mind that almost all the built-in
reports run just using Summary data), you can set the
database to one-third the size of the logs or even smaller
if you take advantage of the QuickList Extreme optimizations.
However, remember that unlike SQL Server 7.0, SQL Server
6.5 databases and database devices do NOT have the ability
to grow automatically when necessary, so keep that in
mind as you set up the Hit List database.
C. Log Size. We suggest a Log Device setting
of 2GB. This is necessary because Hit List will use a
very large amount of temporary space in the log when using
SQL Server 6.5. This is not true with SQL Server 7.0.
Once again, for best performance, the Log device and the
Data device should be on different physical drives.
2. Open the database Options by highlighting the database
you just created and choosing Edit. Select the Options tab.
A. Check the "Truncate log at checkpoint" option.
B. Check the "Select Into / Bulk Copy" option.
3. Now Edit the Tempdb database and do the same
as (2). Also, you should increase the Tempdb size to at
least 50 mb, which will mean increasing the size of the
Database Device where Tempdb sits first.
4. Ensure that ODBC is installed and that you have
a System DSN set to use/communicate with the newly created
Hit List SServer database. For instructions on how to set
this System DSN up, scroll up to (6) in the SQL Server 7.0
Setup instructions above.
5. After you start Hit List, select the Database
tab in Tools/Options dialog box then enter the ODBC datasource
you just created, user name and password to connect to the
Hit List SQL Server database.
|
