As a SQL Server Database Administrator, you need to understand enterprise development. Your responsibilities probably include coordinating schedules and changes with software-development teams to create unified database applications. You can significantly reduce the effort that this coordination requires by adopting tools and processes that are as similar as possible to those of the software developers who are also in your organization.
As many teams move to more iterative, rapid-development paradigms for writing code (such as the Agile method), both software and database developers have an even greater need for tools such as version control. These tools can reduce the risks that are associated with team-based development. By using similar tools and processes, you can gain significant benefits not only for the databases that you manage but also for the integration points between those databases and the software that interacts with them.
Visual Studio provides features that are targeted at the database developer and database administrator roles of today. For example, Visual Studio supports the concept of an isolated development environment for each database professional. Team members can work on schema-related activities without causing hardship to other team members or the production environment. The long-term vision adds features for the database architect role and expands features for database administrators.
To support high-level developer goals, Visual Studio helps you perform the following high-level tasks:
- Create and deploy a new database under version control.
- Put an existing database schema under version control by using the database project. The database project schema is created by importing the schema from a database or existing scripts.
- Modify an offline representation of a database and then deploy the changes.
- Compare schemas or data between databases.
- Develop and run unit tests against database objects.
- Generate predictable, representative test data without compromising sensitive production data.
- Rename database tables, columns, views, or other objects and propagate those changes to the rest of the database project, which includes associated unit tests, views, stored procedures, triggers, and so on.
- Create, edit, validate, execute, and analyze scripts and queries.
- Establish the project environment
- Perform isolated, iterative development
- Build the project
- Deploy from the 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
The following diagram shows how team members perform iterative development work in an isolated environment:
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
The following illustration shows 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 the Project Environment
The following diagram shows 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.