Month: February 2007

Now that we have a database system installed and configured, let’s pick a practical application and see if we can build it in each of the environments. The one that comes to mind is a library card catalog system that allows students to check out and check in books. With this system we will create a table that contains information about books and create a table that contains basic information about students. I am picking this example for two reasons. First, it is a simple problem that we should be able to build. Second, my kids school needs a library system and building it helps the school as well as helps me learn. The origional design will be simple. Once we get this design done we will look at more advanced features like protecting columns in a table with encryption, partitioning the data so that we can drop last semesters transactions, backing up the data securely, and restricting table access to prevent dropping or deleting entries from a repository. We might also want to integrate this database with a business process engine to generate reports and late fee reminders.

It is important to keep an eye on our exercise. I am trying to build a foundation of knowledge in database design, administration, and deployment to help learn the Oracle database products and its surrounding packages. I am going to build this exact solution on multiple operating systems and multiple databases as would be done at a corporation. The intention is to be a learning exercise. At each step I will generate a summary of the cost of this solution and effort required to build the “product”. I will try to build this using Oracle Unbreakable Linux, Oracle Database Express Edition, and SQL Developer to provide a zero cost solution to anyone who wants to take this and run with it.

One commercial/non-profit web site that I will use for this exercise is www.librarything.com. This site allows me to create a virtual card catalog of my book collection and export the list as an Excel or CVS file. From this I will import the data into a database table and use it as the list of books that are in our library.

next step, design a data definition for our project. This needs to include table definition, entity relation diagrams, and use cases for our library. I would like to create this solution as a web interface to allow books to be added to the library, students to be added to the school, and books to be checked in and out from the library.

Earlier I posted that I had some problems installing SQL Server Express on a Windows VMWare instance. It turns out that the software would not install because it did not have the right patches for the operating system thus would not install the databases. I got the vmware images from someone in the office to do some testing and quickly realized that they would not work. The images could not be updated because they were licensed to someone else. I had…… wait for it….. pirated copies of the operating system. The horror. The shame. I was in violation of a copywright. It seems a little ironic that I work for a software company and was having trouble with software piracy from another company to learn my own product suite.

I swallowed my pride and obtained valid licenses, re-installed, re-patched, and updated my base installations with legal copies of the operating system. Once I did this, it was trivial to install the SQL Server Express Edition. I did have to install .NET Framework 2.0 before I could install the database. I also had to download and install a jdbc driver and open up port 1433 on the firewall so that SQL Developer could attach to the repository. Once I performed these steps, everything worked.

In summary, I have created vmware images for Windows XP, Windows 2000, Windows 2003, Red Hat Enterprise Edition 4, and Oracle Unbreakable Linux Version 4. I have successfully installed Oracle Express Edition and mySQL on all five platforms. I have successfully installed SQL Server Express and SQL Server on the three windows platforms.

The simplest to install was any of the packages on Windows. The most difficult to install was Oracle Express Edition on Linux. With these foundations I am ready to start testing some basic features and sharpen my skills and knowledge of these products.

The only question that this exercise leaves me is how do I share my work with others? I can’t give copies of my work to my peers because it becomes a copywright violation with Windows. I can easily do this Oracle Linux because binary distribution is free, support is where money is involved. The SQL Developer, JVM, and Express Editions do not have cost associated with them. How do other people handle sharing vmware images when there is a license involved for the operating system or software suite? How can you use these for training and testing without purchasing a license for each of the products for everyone who wants to play and experiment? It seems like a large cost just to learn.

next, creation of a library schema to allow for checking out and checking in books for a school library……………

Ok, I thought that I would be able to knock out an install of SQL Server Express Edition today with little or no problem. Man was I wrong. Since Oracle Express Edition and mySQL were easy to install, I thought the Microsoft product would be trivial to install as well. When I tried to install the package I got an error that I needed the .NET Framework 2.0 installed first. Ok, no problem. I can find and download that. It wasn’t that difficult to install. Once I got that installed, I found out that I was running XP Service Pack 1. The SQL Server suite does not install on this service pack so I had to upgrade to service pack 2. That turned out to be a little more difficult since the Windows upgrade program kept generating an error. Based on some blogs and web sites I needed to download the service pack standalone and install it manually. No problem, I’m only three hours into this exercise and I haven’t even seen an install screen for SQL Server. Once I get the service pack downloaded, it complains that it needs encryption services enabled to verify the download before it will execute. Ok, five minutes later I have it enabled and are starting the service pack install.

Now that I am into this installation for three and a half hours, I finally get a splash screen. The software looks like it is installing but it complains that I don’t have some service configured properly. Looking at various web sites, it looks like I need to perform a two part install; first the core database then the client services. Once I get the server installed I then successfully go back and get the client services installed.

Great. It only took me four hours to get the product installed and I can now install and configure sqlDeveloper. I find out that I have the wrong jdbc driver so I need to get a new one and configure it. No problem. I try to test the connection but get a network connection error. Network connection error? It looks like I got everything installed and configured. It looks like I have the right port and the right account enabled for login. How do I test this? Does the express edition come with a browser that enables me to troubleshoot it? With mySQL I at least had a command line interface to create a user account and test the connection.

I guess it is back to the web to figure out how to look at a SQL Server Express Edition and see if it is listening on the right port. I tried turning off the firewall to see if it was blocking access, no luck. I tried restarting the server to make sure it was listening on the port, no luck. Back to the web browser.

Unfortunately, I thought that this installation was going to be the simplest. It turns out to be the most complex because there are operating system patch and service dependencies that are strictly enforced. Probably not a bad thing but it does add to the complexity. I hated this on Solaris when it required a specific version of a patch or package and would just stop. It would be so much nicer if there were a link in the GUI interface to initiate the download and installation of the dependency rather than coming up with an error screen with an Ok button or Exit button and no log file generation.

It appears that nothing is perfect, just perfectly complex. I guess this is why most DBAs are paid the big bucks.

more later on the trials and tribulations of installing SQL Server and SQL Server Express…..

The installation of mySQL community version 5.1 was relatively simple and painless on Windows. The GUI install tool worked very well, asked me for passwords and port information, and ocnfigured itself to run as a windows service. Linux, for both RHEL4 and Unbreakable Linux, was a different challenge. I could not get the 7.x version to install or configure properly so I dropped back to the 5.1 version. What happened to 6.x? Did they skip that version entirely or just stop with the community edition and split off a commercial version from a different source base? For Linux I had to drop back to the tar distribution of 5.1 and unpack it according to the directions in the installation note. This installation included creating a user mysql, changing permissions on files and directories, and starting the database. This worked fine but was a little cumbersome when trying to attach to it with sqlDeveloper. The default jvm that comes with both versions of Linux was out of date and would not work properly. I had to download a jvm from java.sun.com and install it. Once it was installed I had to change the path to find it and launch sqlDeveloper.

The problems did not stop here. Once I had sqlDeveloper up and running I had to configure the jdbc driver as I did with Windows and try to connect to the database. Unfortunately, the tar installation does not create a user. To do this I had to launch mysql as root and create a user. I did this with the command:

Once I did this, I was able to test the connection from sqlDeveloper using oracle/oracle10g. Everything just worked as it did on the Windows installation at this point.

My two questions that come from this installation are….
1) Why is Windows always easy for installation and configuration of software packages. Is the administration of Linux that difficult? Are the administrators of Windows that lazy? I have been a Solaris and Linux admin for years and found it relatively easy to configure and install software packages by editing configuration files. It seems like Windows installations must use a GUI tool to lead you to get the right answer.
2) Why do packages not list dependencies? When I was installing the software on Linux I had to go pull down a jdbc driver and jvm. The jvm was included with the Windows distribution. Why not on Linux? The readme file should list what packages are required. Apache does a very good job of listing software packages required to add options and build specific components.

The size of mySQL was very trivial when it comes to disk space. It consumed about 400K of memory and less then 100M of disk space. The initial functionality appears to be similar to the Oracle XE installation but more tests will show if this is true or not.

next, installation of SQL Server on Windows

I am seeing a trend in the way that marketing and engineering is bundling management packs together. Last year they did a bulk discount if you purchased three or more packs. This year they are bundling some of the common features into system and services offerings. For example, included in the Unbreakable Linux offering is the Provisioning, Configuration, and Host Management packs. These are provided free of charge if you pay for Basic and Premium support level packaging. It looks like somthing similar is being done for the E-Business Suite, PeopleSoft, and Siebel Management packs. The bundled application suites included the Configuration, Service Level Management, and Performance packs.

I personally like this bundling of functionality. The next thing that I think is needed is a bundling with something like Remedy, openSNMP, and an SMS service to tie help desk tickets, real time reporting, and paging into one service.

status update on the database installations….. I got mySQL installed on Windows this weekend. It was a trivial install. I struggled getting it installed on RHEL4 because I tried installing it via rpm packages. I had substantially more luck getting it working with tar.gz distributions. I was able to successfuly integrate SQL Developer with the mySQL deployment and see tables and views just like with an Oracle XE installation. The mySQL distribution on Linux includes a installation wizard but once it said that it was finished I could not find the binaries like mysqld to start the database and could not figure out what port it was default configured to listen on. Using the tar.gz distribution, I was able to start, configure, and connect to the database according to the documentation. It looks like they are diverging how to install and configure this software package and the two methods are substantially out of sync with the documentation.

this is a test to see if I can add an image to a blog….

this looks like it works but it does require a link to another computer. I need to figure out how to include an image locally if possible. Anyone know how?

two events today. the first was a customer visit and presentation. the secons was a guest lecture at a users group.

first. we gave a good demo and talk on a new management pack. unfortunatly we forgot to ask if the customer manages their Peoplesoft implementation or outsource administration. we talked to the customer ahead of time. we sent an agenda. we talked about what we were presenting. at the end he customer said that this was interesting but they do not manage the applications.

the second talk was about how storage relates to brain function and how internet access is approaching cognitive brain function plus the comunal mind. pack rats have had a field day sucking information off the web quicker than the brain can process it. i can download a recording of a five day class in fifteen minutes but it takes two days to scan the content of the material. just because we have the bandwidth do we necessarily need to use it?

more next week on installation of other databases into vmware images.

So the first phase of my testing I installed Oracle Express Edition on Windows XP, Windows 2000, Windows 2003, Red Hat Enterprise Edition 4, and Oracle Unbreakable Linux 4 Update 4. The installation required two sets of binaries, one for Windows and one for Linux. There were the Express Edition binaries and the SQL Developer binaries. The size of the binaries looked like…

Linux – Express Edition – 215Mbytes
         – SQL Developer – 39Mbytes
         – JDK for Linux 57Mbytes
         – install footprint 200M app, 450M database – 650M
         – memory footprint – 512Mbytes
Windows – Express Edition – 162Mbytes
                – SQL Developer – 67Mbytes
                – install footprint – 1.1G (includes JDK as part of package)
                – memory footprint – 512Mbytes

The installation on Windows was trivial. Execute the database installation executable and select the defaults. The only data that needed to be entered was the password for the system account. The installation on Linux was a little more difficult. The Linux installation did not have a large enough swap footprint so I had to double the swap area to properly perform the installation. The installation was a simple rpm folled by a custom script generated by the installation. To install the software on Linux I had to perform the following steps

1) verify swap space
     swapon -s
     On RHEL4 I had to add swap space which involved adding another disk through vmware which required halting the operating system and adding a vmware SCSI disk. Once I did this I had to use fdisk to define a primary partition and label it as Linux Swap. Once I did this I had to use the mkswap command to initialize the device and then add it to swap using the swapon command.
     On Unbreakable Linux, this was not an issue because the swap space was defined at 2G.

2) verify that the network was up and running with ifconfig -a
     On Unbreakable Linux the network was bound to a MAC address that was different than the hardware that I was running on. I had to uncheck this and make sure that I could get a DHCP address

3) mount the software images from the localhost
     On RHEL4 and Unbreakable Linux I had to define the vmware host in the /etc/hosts file and share the external USB disk from the vmware host as well. Once I did this I was able to mount the disk using
     mkdir /mnt/e
     mount -t smbfs //vmware-host/e /mnt/e

4) install the database software
    cd /mnt/e/products/database/Express Edition
    rpm -i oracle-xe-10.2.0.1-1.0.i386.rpm
    /etc/init.d/oracle-xe configure
       – define HTTP port
       – define database listener port
       – define password for SYS and SYSTEM
       – opt to start the database on boot which also starts the database now.

5) install the SQL Developer software
    cd /mnt/e/products/sqldeveloper
    rpm -i sqldeveloper-1.1.23.61-1.noarch.rpm

On RLEH4 I had to download a JDK and the Firefox browser to get SQL Developer working. I could have done these when I installed RHEL4 but the image provided to me did not contain these packages. Installation of these packages were simple but did require an additional step on this platform.

To install the software on Windows I just executed the OracleXE.exe and unzipped the sqldeveloper-2364.zip file. The OracleXE binary asked for an installation directory location and password for the system account. Once these were finished, the database was installed, configured, and running. When I started the SQL Developer I had to define a connection to the localhost and login using the ports and password provided when I installed the database.

Once the software was installed, it looked the same independant of the operating system configuration. The SQL Developer and Database web management interfaces function and look the same on all operating system levels.

The reason I started with the Express Edition and SQL Developer is that these are the basis for most software development and testing. These platforms in conjunction with Oracle Unbreakable Linux can be obtained at zero cost for a developer. Many companies use this model for development or use Windows XP as the basis because that is the operating system that comes with new desktops or laptops.

next up…. installing standard edition on the same platfoms.