1. It started with Visual Studio 2005
In Visual Studio 2005
MS released a project template call “Database Project”. It can be used to manage any RDBMS database with OLEDB driver (Oracle, MySQL etc. of course including SQL/Server)
As an addition to Visual Studio 2005, MS offered SQL/Server project template under “Team Edition for Database Professionals GDR” which was designed to manage SQL/Server database in a much efficient way.
This was the first time when we could manage SQL/Server database objects from within Visual Studio. It offers build / deployment, and data scripts functionality.
In Visual Studio 2008
MS released “SQL/Server Project” template under Microsoft Visual Studio Team system 2008 Database edition”
Figure 1: SQL/Server database project in VS2008
Figure 2: Database project in VS2008 (for OLDB database)
In Visual Studio 2010
MS further developed the database capability by adding one more project template “SQL/Server 200x Server Project” in additional to “SQL/Server 200x database Project” and provided some good Wizards. SQL/Server 200x database Project is designed to manage database objects. SQL/Server 200x Server Project is designed to manage database server objects
However, In Visual Studio 2010, MS discontinued “Database project” for OLDB database project template.
Figure 3: SQL/Server Project templates in VS2010
2. SQL/Server Project, what it does?
a. It provides an environment where we can develop/ manage and version control database objects includes ( not limited to)
i. Tables, Views
ii. Stored procedures, triggers
iii. CLR assemblies
iv. Schemas
b. It provides build process to verify the integrity of the database when changes are made to database objects
c. It provides deployment process to manage database creation, delta deployment, database schema comparison.
d. It provides post/ prior deployment script execution for populating/cleaning data to/from the database.
e. It provides project Wizards for reverse engine to establish database project from actual database or database script files.
3. What does it mean to me as a database developer?
a. I can easily establish database project and version control script files of database objects from existing database.
b. I can design database schema and version control the script files for database object (tables, views, triggers, functions etc…)
c. I can verify my changes with all other database objects by building the project before deploy the changes to any instance of the database.
d. I can automate the deployment process by using the deployment feature. (I no longer need to manually prepare the deployment script when changes are made to the database schema.
4. What does it mean to me if I am in charge of deployment process?
a. Manually maintain the incremental script will become the history in the past.
b. I can run the deployment process on the fly or generate the deployment script and hand it to someone to run it.
c. I can use post deployment script to generate data for the database.
d. I can specific different deployment configuration for different region ( Development, Test, Staging and production)
5. What does it mean to me if I am an application developer?
a. I can develop my database object within the same solution where I work on my .Net program.
b. I can establish my local database for development with just one click. <Deploy>
c. I can establish test data in the database using post deployment scripts
d. I can version control my script files for stored procedures and functions.
e. I can verify the code change I made with existing schema to find break points without deploy the code to database or run any test scripts.
6. Bonus come with SQL/Server project
With SQL/Server project, we can do code analysis on database project as we do on .Net project. It offers lots of checks on compliance of “Best Practices”
Figure 4: Code analysis for SQL/Server project
7. For existing database, how and where to start?
Wizard is your best friend
- Which version of SQL/Server versions it Visual Studio 2010 supports?
- SQL/Server 2000
- SQL/Server 2005
- SQL/Server 2008
- SQL/Server 2008 R2
- Conclusion
If you can make computer do the work for you, why do it yourself. Life is short, let’s enjoy it.
Nice post very helpful
ReplyDeletedbakings