|
|
Database System Applications
CSE132B
Setup Instructions for your own machine
Install MS SQL Server Express
You
are encouraged to download and install Microsoft'sSQL
Server Express 2005 to develop the project locally on your machine. There are
a number of obvious benefits like availability and performance, additionaly you would have the added experience of
getting acquainted with the server-side tools. However, a server will be
provided with appropriate connection information.
The server can be found here: MS SQL
Server Express 2012.
After you accept the license agreement, and the initial support files have
been installed, you will be prompted with a System Configuration Check. If
you fulfill the minimum requirements, continue the setup.
Note: you may be warned that your system does not meet the minimum hardware
requirements despite the fact that you do. Just continue with the installation(this is from experience).
Proceed to accept the default settings until prompted for an authentication
Mode. By default they encourage users to only authenticate via Windows
Authentication. Instead, opt for Mixed Mode and enter an administrative
password for the sa (server administrator) account.
Accept further default settings until prompting the server to install. To
install the Server Manager, follow the link on the above page to the SQL Server
Management Studio Express. This tool provides a GUI interface into many of
the server settings and is based on Microsoft's standard Console. Accept the
default settings and the management studio will install.
Configuring SQL Server Express 2012
Run the SQL Server Management Studio Express CTP. For the Server Namefield, select the drop-down box and then select
<Browse for More…>. Under the Local Server tab, expand Database Engine
to reveal your local database name. Select this database and select OK.
For Authentication, select SQL Server Authentication. Now enter your
password you created during the install for the "sa"
account. The manager will then connect to the database. Now that you are in,
the first thing you should do is create a userlogin
under the Login folder contained in the Security folder. It is standard
practice to only use the "sa" account for
administering the system, and to use a user account forapplication
purposes.
You can then create a new database under the Database folder. You now have
the basics and are ready to set up the rest of the system. Note that if you
are using your own database, you'll connect using the credentials you set up,
and will obviously need to use a "localhost"
address where appropriate.
Install Apache Tomcat 6.x
- Download and install the Tomcat
6.x windows installer.
- Please read the Tomcat's website for
further installation instructions and documentation.
- Setup CATALINA_HOME environment variable to the
directory path in which tomcat is installed.
For example set
CATALINA_HOME="c:\program files\tomcat 6.0\"
- Setup JAVA_HOME environment variable to the directory
path in which JDK is installed.
For example set
JAVA_HOME="c:\program files\java\jdk1.6.0_03\"
- Test your Tomcat installation by typing the following
URL in your web browser
http://localhost
- Sometimes it is required to copy manually the
following file
c:\program
files\java\jdk1.6.0_03\bin\msvcr71.dll into c:\windows\system32 if this error is encountered
[174 javajni.c]
[error] The specified module could not be found.
[947 prunsrv.c] [error] Failed creating java
C:\Program Files\Java\jre1.6.0_03\bin\client\jvm.dll
[1202 prunsrv.c] [error] ServiceStart returned 1
Install
the JDBC
The
JDBC is a collection of drivers that allow the JRE to communicate with
Microsoft's SQL Server.
- Download the drivers
from: JDBC
drivers. These drivers are provided by Microsoft and there are
versions for both Windows and Linux systems. After the installation,
make sure that the JAR file (sqljdbc.jar) is included in your Java
CLASSPATH.
- Copy
sqljdbc.jar to the c:\program
files\tomcat 6.0\lib
directory path. This directory contains the JAR or ZIP files of java
classes available to both Tomcat 6.x and all web applications. By
copying sqljdbc.jar to this directory, all web applications will be able
to use the java classes you need to connect to an SQL Server database
via JDBC.
Install the UCSD VPN
To
attain a university IP address to develop the project on the server provided
by us from an off-campus location, you'll need to run the UCSD VPN. Resources
and instructions can be found at: UCSD VPN.
You will most likely have to reboot after installing the VPN.
Installing the GUI client
For
testing and learning purposes you can use a universal SQL GUI client called SQuirreL SQL. It can be found at the following source: http://squirrel-sql.sourceforge.net/.
SQuirreL SQL is a universal SQL GUI such that it
can interface with "most" of the database servers.
After extracting the necessary files,you
can launch the program via "squirrel-sql.bat" for the Windows
version. Once the application is running, you must configure the MicrosoftMSSQL Server JDBC Driver located on the
left-hand list of drivers. If there is a red-X next to the driver, then it is
not properly configured. Double-click the driver to open its properties page.
If the necessary Microsoft JDBC Jar files from above are not in your JavaClass Path, then you will have to add them by
selecting Extra ClassPath and adding the files.
Select the msbase.jar file and click ListDrivers.
This will populate the Class Name field at the bottom of the window. Select
OK and your driver will be ready.
With the driver configured, you must now create an Alias to connect to this
driver. Select the Alias option at the top, and select NewAlias.
For the driver field, select the drop-down box and select the Microsoft MSSQL
Server JDBC Driver. Create some Name for this alias(132b
or something).
For the connection URL, you will either enter
jdbc:sqlserver://localhost:1433
if you are using your own SQL Server Express, or
jdbc:sqlserver://coursedbserver.ucsd.edu:1433
if you are using the SQL Server provided by us. You can also enter your
account name and password.
Then test your connection. Remember that you must be connected to UCSD VPN
in order to access the server from off campus. If your test connection
succeeds, you can save the Alias and connect to the server. Disregard any
warnings that may pop up (these are old but reliable drivers). If you then
select the SQL tab on this new window, you will be able to enter and evaluate
SQL statements against your tables. Note that your account will be initially
empty, so you must create your own tables, using the appropriate DDL SQL
statements. You can contact the TA's if there are any major problems.
|