What's the best way to manage sql change scripts for two developers?

Question

Up until now I've been a lone wolf on my client projects. Anytime I would make changes to SQL Server: tables updates, stored procs, etc. I would generate the change script and plop it into a directory. When the application was ready for release, I would run the scripts on the live server and be done.

Soon I will have another developer working on the same project. The project files are all in source control. I'm just not exactly sure how to go about handling the change scripts. I'm guessing they should be under source control as well? If so, what would be the best naming convention? How exactly would I determine which scripts are to be executed on the next release? Keeping in mind that this is a fairly low-key, informal web project that does not have any version numbers or project management software.

Thanks.

Answer 1

Yes, you should put them in source control. The naming convention does not matter as much as being consistent with it does. One way is to append an artificial (create one) application version number in the filename of each script. We could probably give you better naming examples with if you give more detail. But, definitely you want them in source control.

Answer 2

An important component I would suggest addressing is ordering - the files should include (perhaps at the beginning) some kind of sortable date & time stamp. This way you can accurately test and debug the order that the scripts are executed in.

Answer 3

I currently store my sql change scripts in a folder and name them, script order number, tablename, description of change

1-User-create-table.sql

2-User-added-columns.sql

...

n

When I've executed these scripts I move them into a new folder, named "release 2009-09-01" and and then continue with the next number

Answer 4

We source control the change scripts as .sql files, then keep the order of execution for the sql files in a batch file, which is also under source control. The batch file calls OSQL with the sql file as a parameter:

SQLScripts.Bat:

SET BASEDIR=%%1
SET SERVER=%%2
SET DATABASE=%%3

CALL RUNISQLW CreateUserPresets %BASEDIR% %SERVER% %DATABASE%
CALL RUNISQLW CreateFundWorkflows %BASEDIR% %SERVER% %DATABASE%
CALL RUNISQLW spFundWorkflowAddFromTemplate %BASEDIR% %SERVER% %DATABASE%
CALL RUNISQLW spFundWorkflowListForGrid %BASEDIR% %SERVER% %DATABASE%
CALL RUNISQLW spWorkflowTasksListForGrid %BASEDIR% %SERVER% %DATABASE%
CALL RUNISQLW fGetToleranceDate %BASEDIR% %SERVER% %DATABASE%
CALL RUNISQLW fGetNotifyDate %BASEDIR% %SERVER% %DATABASE%
CALL RUNISQLW spWorkflowTasksManager %BASEDIR% %SERVER% %DATABASE%
CALL RUNISQLW spWorkflowTasksAnalyst %BASEDIR% %SERVER% %DATABASE%
CALL RUNISQLW spWorkflowTasksNotify %BASEDIR% %SERVER% %DATABASE%
CALL RUNISQLW AddGateFrequency %BASEDIR% %SERVER% %DATABASE%

pause

RUNISQLW:

@REM First Parameter: Name of SQL file, without the .SQL extension. @REM Second Parameter: Base Directory to run the file in. @REM Third Parameter: Name of the server to run the file on. @REM Fourth Parameter: Name of the Database on the server.

osql -S %3 -d %4 -E -i %2\%1.sql -o %2\Output\%1.txt

We then call the SqlScripts batch file from a deployment.bat file for each configuration environemt, Dev, Staging or Production. This keeps it consistent across configs.

Answer 5

If you are using VS Team Edition you can use the database edition to create a database project for the sql server version you are using.

Then, build the project from the database, so you get all the functions, views, tables into the project.

Then, whenever you make changes, make it in the project, so it can be in svn easily (each file is there) and then you can just sync up your sql server database.

This way you can update your database from changes your teammate made without messing up your data.

Answer 6

Another way is to have a tool like http://www.red-gate.com/products/SQL_Compare/index.htm

generate a change script for a single deployment. The good think is that you dont have to count on the discipline of developers anymore.

Apart from that I still like to have the db in svn. I use SQLScript for that see here Stackoverflow

Answer 7

Start learning how to use branches with your source control. Which can be very valuable when working collaboratively.

Some branching strategies can be:

• Name the branch after the developer (and each developer on the project will then be responsible for keeping their branch in sync with the mainline).
• Create a new branch for every feature and then delete the branch once you have merged it back (Only really feasible with a distributed version control system)

Answer 8

Hey mate,
Have a look at the patching engine used in DBSourceTools.
It's been specifically designed to help developers get SQL server databases under source-code control.

This tool will allow you to baseline your database at a specific point, and create a named version (v1).
Then, create a deployment target - and increment the named version to v2.
Add patch scripts to the Patches directory for any changes to schema or data.
Finally, check the database and all patches into source-code control, to distribute with devs.

What this gives you is a repeatable process to test all patches to be applied from v1 to v2.
DBSourceTools also has functionality to help you create these scripts, i.e. schema compare or script data tools.

Once you are done, all of the files in your patches directory become your release, and will upgrade your database from v1 to v2.

Have fun.