My company provides a large .NET service-oriented solution. The services layer interact with a T-SQL back-end consisting of hundreds of tables and stored procedures. Our C# code is in version-control (SVN) but our stored procedures and schema are not.

After much lobbying of expedient upper-management, I was allowed to review our (non-existent) build/deployment process to accomplish the following goals:

1. Place schema and stored procedures under source-control.
2. Automate the build/deployment process.

I would like to proceed per the accepted answer's strategy in this post but have additional questions:

1. I would like to use Hudson as my build server. Is this a reasonable choice for a C#/SQL solution? What better alternatives should I explore?

2. Assuming I have all triggers, stored-procedures, schema, etc... under source control, and that they are scripted to individual files, how do I generate a build script which will take into account dependencies/references between these items? (SQL Server does this automatically, but it generates one giant script)

3. What does the workflow of performing an update at the client look like? i.e. I have to keep existing table data. How do I roll-back schema changes?

4. I am the only programmer. Several other pseudo-technical staff like to make changes directly inside SQL Management Studio. Is it realistic to expect others to adhere to this solution -- how can I enforce this?

Thank you in advance for your help.