Click here to Skip to main content
Click here to Skip to main content

Manage SQL Server Database

, 23 Jan 2011
Rate this:
Please Sign up or sign in to vote.
Manage SQL Server Database within Visual Studio Team System

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:

  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 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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Pranay Rana
Software Developer (Senior) GMind Solusion
India India

Microsoft C# MVP (12-13)

 
Hey, I am Pranay Rana, working as a ITA in MNC. Web development in Asp.Net with C# and MS sql server are the experience tools that I have had for the past 5.5 years now.
 
For me def. of programming is : Programming is something that you do once and that get used by multiple for many years
 

You can visit my blog

StackOverFlow - http://stackoverflow.com/users/314488/pranay
My CV :- http://careers.stackoverflow.com/pranayamr
 
Awards:


Follow on   Twitter   LinkedIn

Comments and Discussions

 
GeneralMy Vote of 5 PinmemberRaviRanjankr23-Jan-11 22:56 
GeneralRe: My Vote of 5 PinmemberPranay Rana23-Jan-11 23:40 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.140721.1 | Last Updated 23 Jan 2011
Article Copyright 2011 by Pranay Rana
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid