What database modelling tool would you suggest (for SQL Server)? Altova databaseSpy, Sparks Enterprise Architect, Embarcadero etc. or what?

NORMA. Also here and Wikipedia on ORM

It is also free and generic

Note, modelling is separate to implementation: you cannot capture implementation or RDBMS specific stuff when modelling.

ADO.Internet Entity Framework or NHibernate