ODBC.ini update did not work

After running the same database for quite some time for a rails application, we finally had to change the database in odbc.ini to point to the new server. Alas we changed the odbc.ini file, shut down the old database and relaunched the application. Expecting the application to immediately start talking to the database on reboot (since we just changed an IP) we were surprised to find that the application was not connecting to the database anymore.

The rails application was connecting to SQL Server as detailed in this previous post using ODBC and FreeTDS.
Read the rest of this entry »

Finding Duplicate Fields in a Database Table

Occasionally it is necessary to find duplicate fields within a database table.  The following query can be used to accomplish this easily.
SELECT *
FROM 'table'
WHERE ('item' IN (SELECT 'item'
FROM 'table'
GROUP BY 'item'
HAVING COUNT(*) > 1))
ORDER BY 'item'

This will list all rows in the desired ‘table’ where the given field (‘item’) is duplicated. This is a great query to use if you want to find duplicate email addresses in a database, or any other field that may be duplicated.

Read the rest of this entry »

Quickly Restore a SQL Server Database

If you frequently need to restore other peoples DB’s for testing here is a quick way to do so in Microsoft Sql Server.

1. Open your Sql Server manager
2. Create a new database (note the names and locations of the created files)
3. Select the newly created DB and select Restore.
4. Select from Device and choose the backup file to restore.
5. Make sure you are overwriting the new database you created, usually quite apparent, look at the path.
6. Check the log file and database name, modify where needed to the new database and log files (usually under logical name)
7. Hopefully once set, you can restore the database, proceed hopefully, if not check the error and modify accordingly.
8. Now you likely need to take ownership of the DB, run the following on the newly restored database to give it sa ownership:

   exec sp_changedbowner 'sa','true'

Read the rest of this entry »

Rails and Microsoft Sql Server

So you want to connect your rails application to Microsoft SQL Server. The following instructions will take you through setting this up quite simply. This setup is on a a Ubuntu 8.04 server connecting to SQL Server 2000 (we will use the Northwind sample database). The first thing you need to do is install the following gems:

$ gem install dbi --version 0.4.0
$ gem install dbd-odbc --version 0.2.4
$ gem install rails-sqlserver-2000-2005-adapter -s https://gems.github.com

More info can be found on the sqlserver adapter gem development page. This is just the first step. Read the rest of this entry »

Costs Using Amazon EC2 & S3

Figuring out all the costs when hosting on EC2 using S3 storage can be tricky. This is break down of what you can approximately expect to pay using a few basic scenarios, such as a lamp server and S3 backups, with Amazon services. This will also compare the costs to what you would expect to pay if you want to do the hosting yourself.

To fully understand EC2 charges, you need to know the difference between the instance types and what they cost to best meet your requirements. For the purpose of this post I will summarize – I have converted the Amazon Compute Units into basic CPU power and will then detail the costs based upon each of these instances.
Read the rest of this entry »