Introduction
In this article, I am going to show how VSTS is useful for database developers. Note that I do not have a full idea about all things, but it’s very helpful to a DBA and developer who is working on a large project with a large number of team members. Sometimes, when we release our project/product to client, problems arise that our dataset version does not match with the current application version. There is always a problem when there are a number of people working on the same project and updating database object frequently and we miss updated object at the time of release. But now with the help of the VS team system 2008, we can easily resolve this issue and keep the database changes in VSS. In this article, I am going to show how to create Database project and the option you get after creating the database. You get the following advantages:
- Manage DB change through schema management "One version of the truth" Offline sandbox for multiple developers
- Source control for DBs ability to store different versions as change sets
- Schema and data compare Generate scripts/apply updates
Start with Create DataBase Project:
- SQL Server 2000
- SQL Server 2000 Wizard
- SQL Server 2005
- SQL Server 2005 Wizard
Sql Server 2005 Wizard
Select database instance you installed on your machine or from your network.
Welcome note by wizard
Select your schema or the object type
A database schema is a way to logically group objects such as tables, views, stored procedures, etc. Think of a schema as a container of objects. You can assign user login permissions to a single schema so that the user can only access the objects they are authorized to access. Schemas can be created and altered in a database, and users can be granted access to a schema. A schema can be owned by any user, and schema ownership is transferable.
Select database collation and some other options
A collation encodes the rules governing the proper use of characters for either a language, such as Greek or Polish, or an alphabet, such as Latin1_General (the Latin alphabet used by western European languages).
- Each SQL Server collation specifies three properties:
- The sort order to use for Unicode data types (
nchar, nvarchar, and ntext). A sort order defines the sequence in which characters are sorted, and the way characters are evaluated in comparison operations. - The sort order to use for non-Unicode character data types (
char, varchar, and text). - The code page used to store non-Unicode character data.
Create new database or import database schema from an existing one by selecting from combo box. Choose important options according to the needs:
Provide information to connect with the database
Build and deploy Configuration
Once you are done with the database creation, project gets created with the folder you see in below screen:
- Stored Procedures
- Functions
- Triggers
- Arbitrary SQL
You can find the below options which allow you to compare data or schema of the database.
- Allows comparisons of: Project -> database; database -> database
- Object level script difference between DBs
- Notifies when data loss may occur
- Generate script or apply changes directly
- It’s smart! Understands constraints, creates temp tables to hold data, more
- Compare security settings Users, roles and permissions
After you are done with adding and changing database object, you can build and deploy project as you can see in the below project:
Summary
Database project and the related utilities to support it by VSTS are very important, save time and make database maintainable.
History
- 23rd January, 2011: Initial post
