Stored Procedures In Source Control - Automate Build/Deployment Process
- by Alex
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:
Place schema and stored procedures under source-control.
Automate the build/deployment process.
I would like to proceed per the accepted answer's strategy in this post but have additional questions:
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?
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)
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?
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.
