SQL Server Database Development with
Visual Studio 2010
1. Establish Project Environment
2. Perform Isolated, iterative development
3. Build the Project
4. Deploy from Project environment
Establish the Project Environment
The following illustration shows how the team member who performs the role of the database administrator (DBA) establishes the project environment.
The DBA starts by creating a database project and importing the database schema from the production database. The DBA then can create a data generation plan to create test data for the isolated development environments. Finally, the DBA checks the database project into version control, which makes it available to the team
Perform Isolated, iterative Development
Each database professional synchronizes their development environment to version control. They can check out files as they are changed and develop and test those changes in isolation. Changes made to their copy of the database project are deployed only to their isolated development environment. After a team member generates realistic test data and runs unit tests against a private copy of the database, the team member checks the changes into version control. Other team members obtain tested changes from version control.
Generate Daily Builds of the Project
how the build cycle works for database projects
The daily build is generated by synchronizing to the most recent tested version of the database project in the version control system. That build can be deployed to a test database where unit tests are run against generated test data.
Deployment from Project Environment
how the DBA can deploy a version of the database project into production
When it is time to deploy a version of the database to production, the DBA synchronizes to a label from the version control system. The DBA retrieves matching files for the database project, related scripts, and tests. Next, the DBA builds the .dbschema file. From the .dbschema file, the DBA generates the deployment script, makes manual modifications as needed, and does a test deployment to the staging server. The DBA iterates this process until the script is ready to be deployed to the production server. If the DBA made changes to the schema or to the pre-deployment or post-deployment scripts, those changes will be checked back in to version control.