How to track deployed files

We are currently looking for better alternatives to the current way we do our deployments. I'm looking for any advice. Be aware the current system we use is the only one i've ever used. We are unhappy though because its very error prone (you will see what i mean in the details). Also be aware that we are migrating from SourceSafe to TFS.

Our Company:

I work for a pension system. Most of our code is used internally although some of it used on our external website.

Architecture:

Most of our code is 2-tier based. Our database scheme is pretty large...we have 6000 stored procedures and 1000 tables. Developers develop both .net code & stored procedures.

How our current deployments work:

In sourcesafe we have 3 root folders $/Dev, $/Test, $/Prod that correspond to dev/test/prod databases.

We have a home grown issue tracking app. We will work on all issues in the $/Dev branch. We will then create a document that has every file we changed along with the sourcesafe version # of the file (stored procedures/.sql files are put in sourcesafe just like everything else). These will be give to a deployment person who will move all the files from the $/dev to the $/test folders. Other deployment people will then build all our apps and execute all stored procedure changes against the test database. The issue will be tested by an assigned tester from our user community and as soon as its signed off on then the files will move from $/dev->$/prod. The deployment team has a spreadsheet to make 开发者_高级运维sure no file will ever make it to $/Prod unless it is signed off on.

Problems we face:

First of all it should be apparent how error prone this is. Missed files or incorrect version # of files are common especially for large projects.

I'm wanting any advice or if someone has some good reads on deployment strategies. As you can see we are quite a bit different than the traditional company that just has release build type of structure which most things i've read are geared towards.

I'd suggest having 3 branches in TFS: DEV/TEST/PROD.

Instead of making a document to track all of the changes in DEV just let TFS do that for you. When you are ready to promote your changes to TEST simply do a merge from DEV -> TEST (this is called a Reverse Integration in TFS lingo).

If you want to be able to work on multiple "issues" at once in DEV, and only promote the changes related to a certain issue, you could use feature branches that exist as children of the DEV branch. Only when an issue is completed does it move to the DEV branch. If you use this branching strategy you may not need a separate TEST branch as your testers could simply operate off of DEV which only includes completed issues.

For deployment, the advice depends on what exactly you need to deploy. Since you only mentioned databases I'll focus on the tooling available for that. You can create a Database Project in Visual Studio which represents the database schema and data at a certain point in time. This is source controlled just like any other VS project. When your deployment team wishes to deploy it they would do so either using Visual Studio or a command-line tool (much more common for operations type staff). The tooling takes a look at the DB project in VS and compares it to a live Database and generates a sql script that you can execute against the live database to make it match the VS project. This entire process can be automated rather easily (ideally in a TFS Build).

My goal would be to have a TFS Build defined that triggered anytime code was checked into TEST (or DEV if using feature branches). That automatically built all your code, and deployed any databases and other artifacts to the appropriate testing environment.