David on Laptop Encounters with the World Wide Web

MS SQL Server 2008 with PHP

February 18th, 2009

I personally choose not to use Micro$oft products when it is possible, but sometimes the choice is not given. For these unfortunate who have to deal with SQL Server 2008 (or 2005), here is how to get around without too many swear words and avoid licensing costs.

Requirements

Windows Vista (Win XP should do also)
.Net Framework 3.5 SP1
Windows Installer 4.5
Windows PowerShell 1.0
MS Core XML Services (MSXML) 6.0

Note: You need to have genuine windows copy to download PowerShell from Microsoft website; however it is possible to find it elsewhere if you google around.

Setup SQL Server

Download and install MS SQL Server Express 2008 with Advanced Services and choose the following options:
Single instance Install (not clustered !)
Name the instance "SQLExpress"
In the options box choose "SQL Server", and "Management Studio" (do not install full-text search, as it requires clustering)
Set the Server Service Account Name to "NT AUTHORITY\NETWORK SERVICE", auto
Set the SQL Server Browser to "NT AUTHORITY\NETWORK SERVICE", manual
Choose mixed mode authentication
Set SQL Server to listen on TCP port.
1. Start "SQL Server Configuration Manager"
2. Network Configuration -> Protocols : enable "Named Pipes" and "TCP/IP"
3. Right-click "TCP/IP" -> Listen All = Yes"
4. In tab "IP addresses" -> Section IPAll -> TCP Dynamic Ports = 1433
5. Repeat these steps for SQL Native Client.
6. Restart SQL Server
Create your database and set the owner to user 'sa', and change the user sa password.

Setup PHP

This assumes you have wamp installed, otherwise just change the path.

Download the good version of ntwdblib.dll (2000.80.194.0)
Overwrite C:\wamp\bin\apache\apache2.2.8\bin\ntwdblib.dll
Overwrite C:\wamp\bin\php\php5.2.6\ntwdblib.dll
Enable php_mssql.dll in php.ini
Restart Apache

$server = 'BANGKOK\SQLEXPRESS';  // The format is "HOST\SERVERNAME[,PORT]"
$username = 'sa';
mssql_connect($server, $username, $password);

Debugging

You can test if SQL Server is listening on port 1433 by using NMap/Zenmap for Windows to do an "Intense scan, all TCP ports" on localhost, and you should see port 1433 open.

When trying to log within PHP you should see an entry in the log file:

c:\program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Log\ERRORLOG

I hope this will save you some time and swears!

Posted by david Filed in

1 comments »

Capistrano 2 Recipe for Dreamhost Shared Hosting and mod_rails

June 30th, 2008

Introduction

I could not find an updated documentation on how to setup properly a rails application on Dreamhost, so I thought I'd share the information I gathered.. First off, thanks to the Phusion team, with Passenger (a.k.a. mod_rails), it's now very convenient, easy, and cheap to run a rails application on Dreamhost.

Also, special thanks to Todd Huss for discovering the multi dreamhost user hack. Basically, you need to run each of your rails application on Dreamhost under a different shell user. Their process monitor, will start killing your processes when the total memory per user exceeds 200 MB of virtual memory (VSZ).

So here's the guide, even perfect newbees and skript-kiddies should be able to follow :

The complete step by step

INFO : Let Capistrano create the directory structure on the server BEFORE you set the public dir of your application in the edit domain section of the Control Panel. This will avoid you conflicts when Capistrano attemps to create the symlinks.

First, configure your new rails application using Dreamhost Control Panel :
1. Create a new repository for your application (screenshot)
2. Create a new shell / SSH user (read why here) with the same name as your application (screenshot)
3. Create a new database (screenshot)
Add your application to your repository

Create your rails skeleton (if it's done yet), on your local machine (assuming you use *nix, but should be similar on Windows)
$ rails myrailsapp --freeze

Add your application to the repository

$ cd myrailsapp
$ svn mkdir http://svn.myrailsappdomain/myrails/trunk
$ svn checkout http://svn.myrailsappdomain/myrails/trunk ./
$ svn add  *
$ svn commit

Exclude database configuration, temporary files, and any user upload directory from the source control :

$ svn copy config/database.yml config/database.yml.example
$ svn remove config/database.yml -f
$ svn propset svn:ignore "database.yml" config/
$ svn remove tmp/*
$ svn propset svn:ignore "*" tmp/
# If you need to share unversionned user-uploaded files, between deploys :
$ mkdir public/uploads
$ svn add public/uploads
$ svn propset svn:ignore "*" public/uploads/
$ svn remove log/*
$ svn propset svn:ignore "*" log/
$ svn remove db/*.sqlite3
$ svn propset svn:ignore "*.sqlite3" db/
$ svn commit

Next, create the Capistrano recipe :

Create recipe skeleton :

$ cd /path/to/your/myrailsapp
$ capify .
[add] writing `./Capfile'
[add] writing `./config/deploy.rb'
[done] capified!

Replace the content of config/deploy.rb with the following :

#############################################################
## General
#############################################################
set :application, "myrailsapp"


#############################################################
## Servers 
#############################################################
set :use_sudo, false    # Dreamhost does not support sudo
set :user, application  # Dreamhost SSH User
set :domain, "myrailsapp.domain.com"

server domain, :app, :web
role :db, domain, :primary => true


#############################################################
## Subversion
#############################################################
set :scm, :subversion
set :scm_user, application   # Sets 'my_svn_user' instead, if you are using different name than your app.
set :scm_auth_cache, true  # Prompts for password once
set :scm_password, Proc.new { Capistrano::CLI.password_prompt("SCM password for #{scm_user}:") }
set :repository,  "http://svn.myrailsappdomain/#{application}/trunk"
set :deploy_to, "/home/#{user}/#{domain}"
# keeps a local checkout of the repository on the server to get faster deployments
set :deploy_via, :remote_cache


#############################################################
## Tasks
#############################################################

namespace :deploy do
  desc "Restart Application (using tmp/restart.txt)"
  task :restart_passenger do
    run "touch #{current_path}/tmp/restart.txt"
  end

  desc "Restarts your application."
  task :restart do
    restart_passenger
  end

  desc "Link shared files"
  #task :before_symlink do
  before :symlink do
    run "rm -drf #{release_path}/public/uploads"
    run "ln -s #{shared_path}/uploads #{release_path}/public/uploads"
    run "rm -f #{release_path}/config/database.yml"
    run "ln -s #{shared_path}/database.yml #{release_path}/config/database.yml"
  end
end

Then, prepare the directory structure on the server :

Create the Capistrano structure (releases/, shared/), by typing this on your local machine :
$ cap deploy:setup
Login to your server to manually create your shared directories and files (if any)
12
$ ssh myrailsapp.domain.com[tootsie]$ cd myrailsapp/shared

Configure your database on the server (you don't want this file in your repository) :

# config/database.yml

development:
  adapter: sqlite3
  database: db/development.sqlite3
  timeout: 5000

test:
  adapter: sqlite3
  database: db/test.sqlite3
  timeout: 5000

production:
  adapter: mysql
  encoding: utf8
  database: myrailsapp
  username: myrailsapp
  password: [your_db_password]
  host: mysql.[myrailsapp.domain].com
  timeout: 5000

Create your upload directory (OPTIONAL) :
[tootsie]$ mkdir uploads

Deploy your application. Note : it will only prompt for your SVN password at the first deploy.
$ cap deploy
And finally the most important, configure your domain like this :

[UPDATE 2008/07/01]

If you need to install custom gems on your account, you can find more info here, here and here.

References

Posted by david Filed in Linux, Ruby on Rails

7 commentss »

Hello !

June 2nd, 2008

I am David Lauzon, a freelance web developer since February 2005. I am usually based in Montréal (QC, Canada), but I will be blogging from Chiang Mai (Thailand) for the following months.

I will be writing about Ruby on Rails, Web 2.0, Linux, and general IT developments. I also will be releasing open source code here, so stay tuned !