LearnNowOnline

Blog

Roots of SQL Server Data Tools

Excerpt by Don Kiely | September 20, 2013

Microsoft has long supported application database development with features built into Visual Studio, ranging from support for data access to the ability to create a connection to a database and manipulate database objects and data in simple ways. Various add-ons over the years have expanded those basic abilities, but even developers were constantly having to bounce between Visual Studio and either Enterprise Manager (in earlier versions of SQL Server) or Management Studio (in more recent versions, including SQL Server 2012) to perform data-related tasks, such as to create database tables, set security features, or execute stored procedures. Neither Visual Studio nor the SQL Server tools fully supported database development.

Then several years ago, Microsoft released its first version of the Team System Edition of Visual Studio 2008. This was a huge package of group software collaboration tools, including a long overdue successor to its Visual Source Safe source code control product. Over time, Team System spawned Architecture, Testing, and Development editions. But for a long time, one piece was missing: a database edition. Microsoft soon rectified that, and released the product code named Data Dude. Officially, it is Visual Studio 2008 Team System Database Edition. Definitely a case where the code name was way cooler than the official name!

Data Dude, er, Database Edition, was a set of tools for managing the full database development lifecycle. Software developers were intimately familiar with the process, since it mirrors the code, build, deploy, rinse, repeat cycle common in software development. (And it can handle agile development methodologies as well.) Database administrators had to get used to some things, but could most certainly be part of the process. Microsoft had several important high-level goals for the product:

• Provide integrated change management, just like source control systems. This lets you go back in time to earlier versions of the database project and also provides controlled access to the project components to various developers for collaborative, simultaneous development.
•  Manage database projects by allowing you to monitor progress and perform code reviews.
•  Find and understand differences between versions. This way you can easily find the differences that cause problems in order to resolve them quickly.
•  Make changes and see their effectiveness, without necessarily needing to build an entire project.
• Support isolated development environments, so that each developer can work on their part of the project and test the changes before checking them in and distributing them to the rest of the team.
• Test solutions to validate the design and code. You can use Team System's support for unit tests to make sure that the design, code, and data doesn't introduce any breaking changes.
• Simplify deployment, of both the entire database and changes. This supports a variety of deployment scenarios during development and after the database goes into production, including finding the changes made to a test version of the database relative to a production version of the database.
• Facilitate collaborative development of databases, so that team members can work together productively.

Database Edition was a huge step forward to make the process of creating database projects similar to creating application projects, particularly with its collaboration tools, support for source control, and test integration. But even with this step, things just weren't as deeply integrated as they could be, in three primary ways: Developer Edition wasn't fully integrated with the database server. It enabled developers to perform more database-specific actions than earlier tools, but you still had to pop over to Management Studio too often.

• Database developers had one tool to create database objects, and another tool-the Business Intelligence Development Studio
• (BIDS)-for BI projects, adding a significant third tool to the mix in addition to Visual Studio and Management Studio.
• Developer Edition made many simple things very easy, but once you went past simple stuff things got very hard. This is a rather subjectiv assessment, we realize, but there just seemed to be too many work arounds for what should have been easy.

The Database Edition was a great set of tools, particularly relative to what came before it. But with SQL Server 2012, Microsoft introduced what has so far proven to be the best set of tools yet, truly integrating database development tools into Visual Studio 2010 and 2012.

This post is an excerpt from the online courseware for our SQL Server 2012: Advanced SQL CLR course written by expert Don Kiely.