Wednesday, June 22, 2011

Visual Studio Database Project for comparing Dev and QA Schema

This post will demonstarte how to setup database project and compare two different envoirement (QA and Dev) database schema by using Visual Studio Database Project.
 
Setup Database : 

We created two database projects
- EMP_MASTER ( Development Database Project)
- EMP_MASTER_QA ( QA Database Project)
Perform the following steps to compare the Dev and QA Schema
EMP_MASTER :
- Create Database Project
- Import the database schema from database to database project.
- Remove the following scripts related to User Settings and Role settings.
 rolememberships.sql
 user.sql
- Build the database project (Output: EMP_MASTER .dbschema )
EMP_MASTER_QA:
- Create Database Project
- Import the database schema from database to database project.
- Remove the following scripts related to User Settings and Role settings.
 rolememberships.sql
 user.sql
- Build the database project (Output: EMP_MASTER _QA.dbschema )


Schema Comparison:
Now, with help of Schema Comparison option (Data Schema Compare) we can compare the both database’s object schema (Tables, Stored procedure, View) and also generate alter script for deployment process

>

SQL Server - Identify unused indexes

 In this blog, we learn about the index usage information (SYS.DM_DB_INDEX_USAGE_STATS) and analyze the index usage data (USER_SEEKS, USER_S...