Beginner's Guide to SQL Server Database Development with VSTS Database Edition California

provided by: 
Originally published at Internet.com

Most developers I've talked to agree that the database is the most important component in a typical business application. Yet, for years SQL Server database developers have used Administration tools shipping with SQL Server or a variety of 3rd party utilities. Visual Studio, the Microsoft flagship development environment, was the domain of the VB.NET or C# developer. TSQL support in Visual Studio was rudimentary at best.

That story changed last year with the advent of Visual Studio Team Services Database Edition. VSTS Database Edition was a late addition to VSTS 2005 and was enhanced with the VSTS 2008 release. I'm going to show you how you can incorporate VSTS Database Edition into your database development process.

Important, but not Sexy

Databases are not visually engaging. Unless a database is not working, most application users don't see any part of a database. Generally, databases don't interact with the outside world like a Web Service or an Application Control Library would.

Compared to an Object Oriented-based application, database components are tightly coupled. Tables are coupled to each other via foreign keys. Views, functions, and stored procedures are tightly coupled to tables and each other. Broken database code means a broken database or—even worse—corrupted data.

A database is not like an assembly. There is little you can do to version a SQL Server database like you would version an assembly. Tight coupling and lack of versioning have other ramifications. It's difficult for multiple developers to work on a database. Aside from TSQL, there are other things to manage like Roles and object permissions. Finally, you don't compile a database like you do an assembly.

Simple and Specialized

Alternately, Transact SQL code is pretty compact and specialized. TSQL only does data. There are no delegates, class hierarchies, or multi-threading features to support. TSQL really serves only two roles: Data Definition (DDL), statements like ALTER and CREATE; and Data Manipulation (DML), statements like INSERT, UPDATE, and DELETE. Because database code generally works in the database, there are fewer execution dependencies and test construction is easier than the many levels of testing you typically undergo with, for example, a Desktop or Web Application.

VSTS Database Edition addresses the issues above and leverages the structure of TSQL, turning Visual Studio into a capable database development environment. Now, I'll take you through a sample project so you can see VSTS Database Edition in action.

Creating a Project

First, the Database Edition only works with Visual Studio Team Suite. To showcase the tools and simplify this article, I'm going to build the project from an existing database, rather than building a database from scratch.

Like all Visual Studio development, database development is managed by using projects and solutions. A project typically corresponds to a single database. You can create SQL 2005 projects or SQL 2000 projects. I've installed some additional Power Tools, so your install may look slightly different. Figure 1 displays the SQL Server projects.

Click here for a larger image.

Figure 1: New database project

After creating the project, it's important (but not required) to match project collation with the database collation. Collation defines things such as ordering for text fields and case sensitivity. Neglecting to set collation can lead to confusing warnings and errors. Figure 2 shows where you set project collation on the project properties.

Click here for a larger image.

Figure 2: Project collation property

At this point, I could have begun building a database in VSTS. Like other Visual Studio projects, right-clicking the project and selecting "Add Item" displays the dialog of components you can create in the project.

Instead, as stated earlier, I'm going to use an existing database, the ubiquitous AdventureWorks database. There are two ways to import TSQL into a project: importing directly from a database or importing from a file. Importing from a database will include all the TSQL in the database along with items like Roles and Users.

I prefer to develop in SQL Server Management Studio or to use a tool like ERWIN to generate the DDL, so typically I choose the file import initially and then use the Schema Compare on moving forward from there (I'll explain Schema Compare shortly.) For demonstration purposes, I'm going to import using the database import option. Figure 3 shows the database import steps.

Click here for a larger image.

Figure 3: Import database schema

With a working project and imported database, it's time to start adding things to the database and showcase some of the other development features.

Project Mechanics

As you can see in Figure 4, all database project components can be stored in a separate file. Like other Visual Studio projects each component can be checked out and versioned using source control tools such as SourceSafe or Team Foundation Server. Thus, multiple developers can better coordinate their changes and you can do things like label versions in your source control

Figure 4: Database project default view

A "Schema View" tab allows you to view the project similar to the way it would appear in Management Studio. You may find navigating the Schema view more natural than navigating the files directly. Figure 5 below displays the Schema view.

Figure 5: Database project schema view

When you poke around the project, you'll notice that the "Build" operations are enabled in Visual Studio. In fact, as you change your project, removing or changing tables, your project will do dependency validation. Broken dependencies will show in the Error List.

Visual Studio, working in conjunction with a database, creates and manages locally on your local SQL Server and handles all the dependency checking and compilation for you. Figure 6 displays the local SQL Database from inside of Management Studio.

Figure 6: Project SQL Database

With some basic project operations understanding, you're ready to add code to the project.

Adding to the Project

Hosting TSQL development in Visual Studio is only part of the database development story.

To illustrate how the other development tools work, I'm going to make three changes to the project. One change will be made from inside the project, and two changes will be made from the AdventureWorks database on the server.

The following is a stored procedure I've added directly to the project. CREATE PROCEDURE [dbo].[Procedure1] @param1 int = 0, @param2 int AS SELECT @param1, @param2 RETURN 0;

Below is a comment I've added to the uspGetBillOfMaterials stored procedure in the AdventureWorks database on the server. AS SET NOCOUNT ON /*New Comment*/

I also added a new table to the AdventureWorks database and a foreign key. The DDL code is below. CREATE TABLE [dbo] . [Table_1] ( [Field1] [nchar] ( 10 ) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Field2] [int] NULL, [KeyField] [int] NOT NULL, CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED ( [KeyField] ASC ) WITH ( PAD_INDEX = OFF , IGNORE_DUP_KEY = OFF ) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [dbo] . [Table_1] WITH CHECK ADD CONSTRAINT [FK_Table~ShipMethod] FOREIGN KEY ( [KeyField] ) REFERENCES [Purchasing] . [ShipMethod] ( [ShipMethodID] )

Schema Compare

As I mentioned earlier, TSQL code is compact, specialized, and lacks built-in versioning. The Schema Compare leverages TSQL attributes to overcome its versioning issues. Schema compare allows a developer to compare all of the DDL of databases and project, and then to synchronize the changes.

Schema Coompare is accessed from the "Data" menu. Accessing the Data Menu appears in Figure 7.

Click here for a larger image.

Figure 7: Data menu options

The Schema Compare dialog is in Figure 8.

Click here for a larger image.

Figure 8: Schema comparison dialog options

Results of a Schema compare appear in Figure 9. I've applied a filter option so you can see the results more easily and compare them to the changes you made above.

Click here for a larger image.

Figure 9: Schema compare results

Included in the list of changes are database users and other items. Mostly likely, you will want to exclude users, especially if you're syncing with your development server. To exclude, you need to change the Update Action to "Skip". Right-clicking at the category level allows you to select "Skip All". This action is illustrated in Figure 10.

Click here for a larger image.

Figure 10: Opting to skip all

Changes are applied in a set of DDL statements. You can view the DDL and access other options from a toolbar like the one in Figure 11.

Click here for a larger image.

Figure 11: Toolbar options

The "Write Updates" button on the toolbar will synchronize the source with the Target. I would recommend reviewing the ScriptsIgnoredOnImport.SQL file after synchronizing.

For Further Study

I've covered the basic development tools and features. There are some other useful features.

Data Compare allows a developer to synchronize the data between two databases. Instead of creating DDL statements, it creates DML statements.

Applications may require multiple databases. In fact, databases may even do cross database queries. Reference Variables allow one project to reference another project as if the project were doing a cross database query.

There are also unit testing tools. I'll cover this subject in another article.

Finally, advanced topics are included in the Sources at the end of the article. This article was meant to get you running faster with the tool. "Introducing Visual Studio 2005 Team Edition for Database Professionals" is a much broader introduction.

Conclusion

Transact SQL development requires a specialized development tool. Visual Studio Database Edition is a capable TSQL development platform. VSTS database edition not only manages all of your database artifacts, it also enables various database synchronization scenarios.

Resources

About the Author

Jeffrey Juday is a software developer specializing in enterprise application integration solutions utilizing BizTalk, SharePoint, WCF, WF, and SQL Server. Jeff has been developing software with Microsoft tools for more than 15 years in a variety of industries including: military, manufacturing, financial services, management consulting, and computer security.

Jeff iis a Microsoft BizTalk MVP. Jeff spends his spare time with his wife Sherrill and daughter Alexandra. You can reach Jeff at me@jeffjuday.com.