How to deploy stored procedures to production

Question

I'm developing a Web App that is the front end to a database. I was asked to handle all database readings and writings through store procs. I have about 80 up until now.

I wonder what's the best way to deploy all this procs to production environment one I'm done with the development.

EDIT:

I already have a bunch of .sql files. I was wondering if there is a way to create kind of an installer that runs all these files I already have at once

Answer 1

I used xSQL Executor. It lets you select all sql scripts that you want to run at once. Then, you can create a command that you can execute from the command line. I created batch file with this command and handed it along with the stored procedure scripts and the xSQL Executor executable file to the DBAs.

Answer 2

Visual Studio has a Database Project which can generate a schema (has nice support for diff'ing/cherry-picking between the project and a data source) that can be command-line deployed with VSDBCMD.

One thing I like is that it uses a consistent layout and generates many ".sql" files. VSDBCMD does a schema compare (of the project output) and generates a TSQL script file on-the-fly that is then run to apply the appropriate changes to the target. VSDBCMD (and/or the VS Database Project) can be run from a staging system so long as it can connect to the SQL Server instance.

"It works well enough here", but I can't vouch for it over other tools. It is more than sufficient to create/update stored procedures and comes "for free" with certain Visual Studio versions, if that is already a sunk-cost.

Happy coding

Answer 3

You could use some tool like RedGate SQL Compare, but also you can export the procedures using SQL Management Studio (script as Create) and then import them in the production database. However, RedGate tool will be able to inspect, merge an do other more complicated tasks.

You can also use something like scriptdb if you need more flexibility.