Siusic Dot Com

I found a lot of posts on the internet regarding how to set up a MSSQL link server to MySQL server. I didn’t find any of them gave a complete picture. So I wrote my own post and hope it can help.

The first thing need to do

when setting up a link server to MySQL is to make sure the server running MSSQL server has the necessary components to access MySQL. You may want to install MySQL client utility on the computer running MSSQL server and run the MySQL client utility on the computer to make sure you can connect to MySQL server using the appropriate host name, username and password. You can download MySQL installation package from MySQL download website and install the client utility only when you install it.

The second thing is to download and install MySQL ODBC driver onto the computer running MSSQL server. After that set up a ODBC DSN pointing to the MySQL server you want to link using the already tested host name, username and password. I want to emphasize these installations and tests should be done on the computer running MSSQL server.

When the above installation and tests are all successful we can proceed to set up the link server in MSSQL server as follow. Go to linked servers under the security folder in Enterprise Manager. Right click and select “New Linked Server…”, put in a name in the “Linked Server” box and choose Microsoft OLEDB provider for ODBC drivers in the “Other data source” drop down box. Put in something in “Product Name” box. It doesn’t matter. Put in the ODBC DSN you set up in previous step in the “Data source” box and leave provider string and location empty. Put in the MySQL database name in the “Catalog” box and click “OK”. It is done.

However MySQL link server tables can not be access using a three part name. It has to be accessed using openquery function like this:

select * From openquery (MySQL_Linked_Server_Name, ’select * from tablename’). Alternatively you can refer to the following document on How to set up MySQL as a linked server in MS SQL server

I was told by various documentations and technical articles that SQL Server Query Optimizer does a good job on selecting which index to use when establishing a query execution plan. And it is in rare cases that you will find justification to use index hint. I believe I encountered this kind of rare cases a number of times over the years. Most recently this is the situation that I countered.

I have the following query:

select a.*, b.*
from a with (nolock)
join b with (nolock) on a.id=b.id
where
b.col1=’some value’

There are non clustered indexes built on the id columns of both tables. And there is a non clustered index built on the column col1 of table b. The query runs really fast. It returns about 200 rows of data almost instantly when it runs. I want to limit the result set to return just one record and I know I can achieve it by adding condition b.col2=’some value’ to the where statement.

select a.*, b.*
from a with (nolock)
join b with (nolock) on a.id=b.id
where
b.col1=’some value’ and
b.col2=’some value’

There is an index built on b.col2 also. However when I add that condition to the query it took forever to return. I didn’t border to fine the cause because the database was maintained by software vender by contract. The only thing I want is to get my information quick and I knew without the condition on b.col2 the query ran quick. So Query Optimizer must be selecting a different index when I added the condition on b.col2. To force it to use the index on b.col1 I try the following.

select a.*, b.*
from a (nolock)
join b with (index(b_col1_index),nolock) on a.id=b.id
where
b.col1=’some value’ and
b.col2=’some value’

And yes this query return the row that I want almost instantly

It is not really a problem specifically related to Tomcat. It is just that I found this problem when I installed Tomcat and I couldn’t find a straight forward answer on the internet. The problem I encountered was basically that I want to configure Tomcat to run under non-root user and I could not use port 80 because the process listening on port 80 had to be running under root. I kept tomcat listening on its default port 8080 and used IPTables to set up port forwarding so that request on port 80 was forwarded to 8080.

I used the following command to set up the port forwarding rules.

iptables -t nat -I PREROUTING -d tomcat-listening-ip -p tcp –dport 80 -j REDIRECT –to-ports 8080.

It didn’t work. I did some test and later found that if the ip address that tomcat used was tied to the physical network interface eth0 then it worked. If the ip address was tied to virtual interface eth0:0 then it didn’t work. In the late case the following command will do the trick.

iptables -t nat -I PREROUTING -d tomcat-listening-ip -p tcp –dport 80 -j DNAT –to-destination tomcat-listening-ip:8080

Since Siusic.com is now sitting on its own dedicated Linux server I have to learn a lot of Linux shell commands to manage the Linux server myself. One of the thing that I wanted to do after installing Tomcat was to make it start running automatically as a user other then root. In order to do that a shell script has to be written to take a parameter either “start” or “stop” to start or stop Tomcat.

When the shell script is put into the /ect/int.d directory it will be called by the system at boot time and passed “start” as parameter. That is how Linux start a program automatically at boot time. It is analogous to add programs to the Windows registry \HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Run in order to start a program automatically when Windows boot up.

I wrote my first ever Linux shell script “tomcat” to do that. I post the script here because I didn’t find a straight forward answer when I Google it or Baidu it. Hopefully it will help some new Tomcat and Linux users.

ARGV=”$@”
STARTTOMCAT=’Tomcat_Installation_Path/bin/startup.sh’
STOPTOMCAT=’Tomcat_Installation_Path/bin/shutdown.sh’
source /What_Ever_Path/tomcat.env
case $ARGV in
start)
echo $”Starting Tomcat”
su tomcat $STARTTOMCAT
ERROR=$?
;;
stop)
echo $”Stopping Tomcat2″
su tomcat $STOPTOMCAT
ERROR=$?
;;
esac
exit $ERROR

That is it. The script first store the start-up and shut-down script for Tomcat in variable
It then execute the command in tomcat.env to set environment variables. The tomcat.env file is like this

export JAVA_HOME=Java_Installation_Path
export PATH=$JAVA_HOME/bin:$PATH
export CATALINA_HOME=Tomcat_Installation_Path
export CATALINA_BASE=Tomcat_Instance_Path

After that the script execute the start-up or shut down script using user Tomcat. So prior to that you will have to create a user called tomcat which has proper permission to the tomcat installation directory and the tomcat instance directory.

Put the tomcat script file into /etc/init.d directory. Make it executable using the chmod command. Don’t forgot to create a link to the tomcat file in the /etc/rc5.d directory. After that Tomcat will start automatically at on boot time as user tomcat.

The other day I was asked to provide a list of database server and all the databases on them with the disk space each database used. I was looking for an easy way to do this instead of opening up the property page of each database in management studio and looking at the size. First I found this store procedure sp_spaceused. This is a very nice system store procedure that can give you the disk usage information of a database, a table or an index.

But when you use it to get database size you have to execute it within the database in question. It doesn’t take a database name as parameter. So it can be automated to run for each database using T-SQL. What I want is query that is able to list all database on the server together with the size they take up. So I wrote the following query to do that. It worked fine for me.

use master

declare @PageSize varchar(10)
select @PageSize=v.low/1024.0
from master..spt_values v
where v.number=1 and v.type='E'

select name as DatabaseName, convert(float,null) as Size
into #tem
From sysdatabases where dbid>4

declare @SQL varchar (8000)
set @SQL=''

while exists (select * from #tem where size is null)
begin
select @SQL='update #tem set size=(select round(sum(size)*'+@PageSize+'/1024,0) From '+quotename(databasename)+'.dbo.sysfiles) where databasename='''+databasename+''''
from #tem
where size is null
exec (@SQL)
end

select * from #tem order by DatabaseName
drop table #tem

The first select statement is to get how many kilobytes a data page has. SQL Server allocates disk space in the unit of data page. Currently each SQL server data page contains 8k bytes. The number of data pages allocated to each database file is recorded in the sysfiles system table. With this information on hand the script creates a temporary table #tem and update the temporary table with size information which is gathered by querying the sysfiles table.