Pranay Rana: Manage Sql Server Database within Visual Studio Team System

Thursday, January 20, 2011

Manage Sql Server Database within Visual Studio Team System

Introduction
In the below post I am going show how VSTS use full for the database developers. Note that I am not having full idea about all things but it’s very helpful to DBA and developer who is working on large project with the large no of team member.

Some time when we are releasing our project/product to client problem arise that our dataset version is not matching with the current application version. There is always problem when no of people working on same project and updating database object frequently and we miss updated object at 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 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

   1. Sql Server 2000
   2. Sql Server 2000 Wizard
   3. Sql Server 2005
   4. Sql Server 2005 Wizard


Sql Server 2005 Wizard
Select database instance you installed on your machine or from you network.

Welcome note by wizard

Select you 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 form existing one by selecting from combo box
Choose important options according the needs

Provide information to connect with the database


Build and deploy Configuration


Once you done with the database creation project get created with the folder you see in below screen
  • Stored Procedures
  • Functions
  • Triggers
  • Arbitrary SQL


You can find 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 done with the adding and changing database object you can build and deploy project as you can see in blow project


Summary
DataBase project and the related utilities to support it by VSTS is very important, time saving and make database maintainable.

No comments:

Post a Comment