Managing Sql Server Database Version Control in Large Commands

Question

Over the past few years, I was the only developer who processed the databases that we created for our web projects. This meant that I got full control over version control. I cannot keep up with all the work with the database, and I want to involve some other developers in the process.

We use Tortoise SVN and store all storage on a dedicated server within the company. Some clients require us not to have real data on our office servers, so we only save scripts that can generate their database structure along with scripts to create useful fake data. In other cases, our customers want us to have the most current information about our development machines.

So, what workflow do larger development teams use for version control and database sharing. Most developers prefer to deploy the database to an Sql Server instance on their development machine. Should we

• Save scripts for each database in SVN and force developers to export new scripts if they make even minor changes.
• Detach the databases after making the changes and transfer the MDF file to SVN
• Place all development copies on a server on your own network and force developers to connect through the remote desktop to make changes.
• Some other options that I didn’t think about

Answer 1

Never has an MDF file in the source tree. MDF is the result of deploying the application, not part of the application sources. Thinking in a database from the point of view of the development source is a short cut to the line.

All development results should be scripts that deploy or update the database. Any change, no matter how small, takes the form of a script. Some recommend using comparison tools, but I think this is a hole for rats. I support the database metadata version and have scripts for upgrading from version N to version N + 1. When deployed, the application can check the current deployed version, and then run all the upgrade scripts that bring the version to the current one. There is no script to deploy the current version directly, the new deployment deploys the v0 database first, and then goes through all version updates, including deleting an object that is no longer in use. Although this may seem a bit extreme, that is how SQL Server itself tracks various changes that occur in the database between releases.

As simple text scripts, all database update scripts are stored in version control in the same way as any other sources, with change tracking, differences and checks.

For more discussion and some examples, see "Versioning" and your database .

Answer 2

Option 1). Each developer can have their own local copy of the database. (Updated value, recreated from the scripts controlled by the latest version (basic + incremental changes + basic data + startup data). To perform this work, you should have the opportunity to locally deploy any database with one click.

Answer 3

You really can't go wrong with a tool like Visual Studio Database Edition. This is the version of VS that manages database schemas and much more, including deploying (updating) the target server (s).

VSDE integrates with TFS, so your entire database schema is under the control of the TFS version. This becomes the "source of truth" for managing your circuit.

Typically, developers will work against the local development database and update its schema by synchronizing it with the schema in the VSDE project. Then, when the developer is satisfied with their changes, they are checked in TFS, and assembly and then deployment can be performed.

VSDE also supports refactoring, comparing schemas, comparing data, generating test data, and more. This is a great tool, and we use it to manage our circuits.

Answer 4

In the previous company (which used Agile in monthly iterations) .sql files were checked in version control, and the (optional) part of the complete assembly process was to rebuild the database from the production process, and then apply each .sql file to the order.

At the end of the iteration, the .sql commands were combined into a script that creates a production database assembly, and the script files were moved. Thus, you apply updates only for the current iteration, and not to start the project.

Answer 5

Have you looked at a product called DB Ghost ? I have not personally used it, but it looks comprehensive and can offer an alternative as part 4 in your question.