azulraul

I am a problem solver. I have experience in CRM, Social Media and Semiconductor sectors. I'm interested in wine, soccer, good food, writing code, music, history and spending time with my family. I live in Portland, Oregon. raul@azulraul.com

View Raul Macias's profile on LinkedIn

How-To: Install Development Tools on Linux Ubuntu

I’m taking this TDD class and all the hands-on exercises are in C++, so I thought I’d use a virtual machine running Ubuntu to write all the exercises from the training.

The instructor sent all the attendees a zip file with the lab files we were going to use during the class and we were supposed to compile some projects beforehand to be ready. So, I unziped the files, went to the lab project folder and tried to compile it:


$ cd samples
$ make
make: g++: Command not found



I remembered seeing this error before, something about the compilers not being installed (this is a brand new Ubuntu installation) so I went ahead to “investi-google” (I’ve heard that word from a co-worker ;o) ) and found out that I needed to install GCC like so:


$ sudo apt-get update
$ sudo apt-get install build-essential


After this, I was able to compile my lab projects. Success!

“"Design Patterns are solutions to recurring problems." Nice and simple.”

Database Schema Comparison with Visual Studio 2010 (Premium and Ultimate)

Table of Contents



Introduction

This article explains how to use the data schema comparison tool included in Visual Studio 2010 (Premium and Ultimate)

Overview

Let’s suppose you are part of a team working in a project that uses SQL Server as the backend and the team starts with a simple database with two tables and two stored procedures. As a developer you are running your own sql server locally so you have a copy of the initial database. You make no changes to the original schema.

A second developer makes a copy of the same database to run in his own development machine and both developers start working on different areas of the application. Let’s say the second developer needs to make some changes to his local copy of the database so he adds some columns to one or two of the tables and updates the stored procedures appropriately.

After a couple days, both developers end up with different database schemas and it’s time to consolidate all changes to the DB. If the database has just a couple of tables and stored procedures is not a big deal; you could manually check for what has been changed, but what if you are working with big DB with several tables and stored procedures? Manually checking for changes would be a pain in the butt, but if you have the Premium or Ultimate version of Visual Studio, you are in luck.

Features

For sake of simplicity and for demonstration, let’s assume that the first developer’s database schema looks like this:


USE [Master]
GO

CREATE DATABASE [Sales1] ON  PRIMARY
( NAME = N'Sales1', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\Sales1.mdf' ,
   SIZE = 2GB , MAXSIZE = 8GB, FILEGROWTH = 1GB ) 
   LOG ON 
( NAME = N'Sales1_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\Sales1_log.ldf' ,
   SIZE = 1GB , MAXSIZE = 2GB , FILEGROWTH = 10%) GO

USE [Sales1]
GO

CREATE TABLE [dbo].[Product]
(
  [ProductId] [uniqueidentifier] DEFAULT NEWID() NOT NULL,
  [ProductName] [nchar](50) NULL,
  [ProductDescription] [nchar](300) NULL,
  [ProductPrice] MONEY NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Region]
(
     [RegionId] [uniqueidentifier] DEFAULT NEWID() NOT NULL,
     [RegionName] [nchar](50) NULL,
) ON [PRIMARY]
GO

CREATE PROCEDURE [dbo].[Add_Product]
     @ProductName nchar(50),
     @ProductDescription nchar(300),
     @ProductPrice money
AS
BEGIN
     DECLARE @myid uniqueidentifier = NEWID();

     SET NOCOUNT ON;

     INSERT INTO [dbo].[Product]
            ([ProductId]
            ,[ProductName]
            ,[ProductDescription]
            ,[ProductPrice])
      VALUES
            (@myid
            ,@ProductName
            ,@ProductDescription
            ,@ProductPrice)
END
GO

CREATE PROCEDURE [dbo].[Add_Region]
     @RegionName nchar(50)
AS
BEGIN
     DECLARE @myid uniqueidentifier = NEWID();

     SET NOCOUNT ON;

     INSERT INTO [dbo].[Region]
            ([RegionId]
            ,[RegionName])
      VALUES
            (@myid
            ,@RegionName)
END
GO

Now let’s say the second developer after making changes, ends up with the following schema:


USE [Master]
GO

CREATE DATABASE [Sales2] ON  PRIMARY
( NAME = N'Sales2', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\Sales2.mdf' ,
   SIZE = 2GB , MAXSIZE = 8GB, FILEGROWTH = 1GB ) 
   LOG ON 
( NAME = N'Sales2_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\Sales2_log.ldf' ,
   SIZE = 1GB , MAXSIZE = 2GB , FILEGROWTH = 10%) 
GO

USE [Sales2]
GO

CREATE TABLE [dbo].[Product]
(
  [ProductId] [uniqueidentifier] DEFAULT NEWID() NOT NULL,
  [ProductName] [nchar](50) NOT NULL,
  [ProductDescription] [nchar](320) NULL,
  [ProductPrice] MONEY NULL, 
  [LastUpdate] [datetime]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Region]
(
     [RegionId] [uniqueidentifier] DEFAULT NEWID() NOT NULL,
     [RegionName] [nchar](50) NOT NULL, 
     [LastUpdate] [datetime]
) ON [PRIMARY]
GO

CREATE PROCEDURE [dbo].[Add_Product]
     @ProductName nchar(50),
     @ProductDescription nchar(320),
     @ProductPrice money
AS
BEGIN
     DECLARE @myid uniqueidentifier = NEWID();

     SET NOCOUNT ON;

     INSERT INTO [dbo].[Product]
            ([ProductId]
            ,[ProductName]
            ,[ProductDescription]
            ,[ProductPrice]
            ,[LastUpdate])
      VALUES
            (@myid
            ,@ProductName
            ,@ProductDescription
            ,@ProductPrice
            ,GETDATE())
END
GO

CREATE PROCEDURE [dbo].[Add_Region]
     @RegionName nchar(50)
AS
BEGIN
     DECLARE @myid uniqueidentifier = NEWID();

     SET NOCOUNT ON;

     INSERT INTO [dbo].[Region]
            ([RegionId]
            ,[RegionName]
            ,[LastUpdate])
      VALUES
            (@myid
            ,@RegionName
            ,GETDATE())
END
GO

Note: in a real world scenario both databases would have the same name; we’ll just call the first DB ‘Sales1’ and the second DB ‘Sales2’ just for demonstration purposes.

Now to compare both schemas and find what’s different between them, open Visual Studio and follow these steps:

From the menu select File > New Project> Database> SQL Server> SQL Server 2008 Database Project

In the Solution Explorer, right-click on Schema Comparisons and select Add> Schema Comparison, give it a name and click the ‘Add’ button

In the New Schema Comparison dialog, locate the Source Schema section, choose Database > New Connection > Enter the server name
In my case the server is .\SQLEXPRESS

Choose the authentication method and enter the credentials if needed. Select the database to compare, ‘Sales2’. Test the connection

For the Target Schema section, repeat the same steps, just use ‘Sales1’ as the target database

After this a window showing all the differences between both databases is displayed. In this case the comparison tool found out that the two tables Product and Region have changes and that the two stored procedures have changes as well.

If we want to know what’s been changed in the Product table, just click on that row to see the details:

In this example you can see that in the Product table, the ProductDescription column size is now 320 characters and has a new column called LastUpdate of type DateTime, besides other changes.

One of the features that I really liked, was the ability to automatically generate an update script that can be used to keep the Sales1 database up to date.

Conclusion

Comparing database schemas that contain several tables, stored procedures and other type of objects manually is a frustrating and time-consuming task. If you have the Premium or Ultimate version of Visual Studio 2010, the schema comparison tool could save you ton of time doing the hard work for you.

References

Compare and Synchronize Database Schemas - MSDN

History

Sep 26, 2012 Original version

License

This article, along with any associated source code and files, is licensed under The GNU General Public License (GPLv3)

Oregano, Red Radishes, Cabbage, Limes, Chopped Onion, Hot Peppers. This is how you eat Pozole.

Oregano, Red Radishes, Cabbage, Limes, Chopped Onion, Hot Peppers. This is how you eat Pozole.

Conversation between Diego, my 7 year old boy and I:

Diego: “Daddy, when I grow up and become a daddy myself, are we gonna still be best friends?”

Me (with a lump in my throat): “Sure Diego, we can be best friends as long as you wish”