If you attended TechEd in Boston last month, you might have seen a session introducing Microsoft's new database development tool, Visual Studio Team Edition for Database Professionals (VSDB Pro). I had the pleasure of learning about this new product from two of SQL Server Magazine's longtime authors and friends: VSDB Pro's Architect and Development Manager Gert Drapers and Program Manager Architect Richard Waymire (who is also one of this magazine's original contributing editors). Gert conceived the idea of VSDB Pro and heads the development team. Richard's role includes making sure VSDB Pro has the features that database developers and DBAs need and ensuring that the product stays on schedule. (See the Web-exclusive article, "The Power to Control Change," http://www.sqlmag.com, InstantDoc ID 50303, for more information on how VSDB Pro will affect DBAs.)
I hope the following interview conveys the enthusiasm Gert and Richard exuded as they explained how VSDB Pro aims to bridge the gap between database and application developers. VSDB Pro will also support script management (see the Web-exclusive sidebar, "Admin Scripts," http://www.sqlmag.com, InstantDoc ID 50272). VSDB Pro's database development life cycle tools mirror Visual Studio's (VS's) tools for the application development life cycle stages of build, deploy, manage changes and versions, track processes and work items, and reporting.
The What, Who, and Why
Karen: What is your new product?
Gert: It's a SKU of VS, a member of the VS Team Suite. The official name is Visual Studio Team Edition for Database Professionals, or VSDB Pro. The code name was K2, also internally known as the Data Dude.
Karen: It had two code names?
Gert: There's a funny story here. We had an executive presentation about what we wanted to do as part of VS. We were explaining who our customer would be. VS had personas for the Developer, the Tester, the Architect, but our customer, the SQL Server person, was missing. Our vice president at the time, Eric Rudder, stuck this name to our customer: "Oh! He's the Data Dude."
Karen: What's the relationship between the Data Dude and the existing VS personas?
Gert: VS has always targeted application developers, and we've also added testers and architects. Most applications have a database, but VS never paid attention to this whole database thing. Our goal is to acknowledge that the database developer is important, that this role has not been getting the attention and tool support available to other personas.
The Data Dude project is about bridging the worlds of application development and database development. We asked how we could bring the database developer into the development life cycle, like an app developer who starts a project, designs, writes code, builds, and debugs. The developer goes through this cycle working in a team and using source control. But what does a database developer have? Query Analyzer. That's the only tool.
Our other observation was that every change a database developer makes is directly on the database. It immediately goes into effect, so the truth is in the database. Therefore, there is no change tracking, and no design-build-deploy metaphor. Because database developers have no development life cycle, it's hard for them to play in the world with other developers that do have a development life cycle. So our goal for V1 is to provide tooling for database developers, to support them with change management—specifically SQL Server schema change management.
Karen: Is a DBA a Data Dude?
Gert: Whoever maintains the schema is our audience. So VSDB Pro targets the function of managing changes in the schema. My biggest audience is Query Analyzer users.
Point-and-click administrators who only want to make mouse-click changes will not be our first audience. They will be in \[SQL Server Management Studio\] SSMS. If you're part of a development team or want to work in a structured process, work disconnected (like a consultant on the road), play around with the schema and try out changes, VSDB Pro is your environment.
Karen: You mentioned that VSDB Pro would bridge the gap between database developers and app developers. How much of the gap is closed in V1?
Gert: First we're focusing on the database. In the future, we'll try to connect the app developer more with the database developer and make sure these two worlds can collaborate.
Richard: Some of that connection is in V1: You'll be able to define a solution that has both a C# project and a SQL Server database, and then you'll be able to deploy your application and the corresponding schemas that go with it. So it's partially there.
Karen: Will you support older versions of SQL Server?
Gert: Yes. But we distinguished SQL Server 2000 and 2005 projects because, of course, the types of objects are different. At TechEd, we supported only SQL Server 2000 objects because we had to limit our scope and that's the biggest audience, but we will fully support SQL Server 2005 by the time we ship. We're at CTP 3. Then there will be 4 and 5 and RTM, which is expected to ship by the end of this year.
Richard: With SQL Server 2000 being a subset of SQL Server 2005, it also builds us a very nice core for moving towards full \[SQL Server 2005\] support.
Karen: Will VSDB Pro support databases other than SQL Server?
Gert: This is all about SQL Server. It's not about other database vendors, but we are in VS.
Karen: How will customers purchase VSDB Pro? Will it be part of SQL Server?
Richard: We are a completely stand-alone SKU sold either separately or as part of the VS Team suite.
We don't plan to ship it with SQL Server. We do have a prerequisite of using an instance of SQL Server for internal validation within our project, and eventually you'll need SQL Server under the covers, but it'll be part of VSDB Pro and you won't have to buy a separate SQL Server license.
The Truth Is in the Project
Karen: How will a Data Dude work with VSDB Pro?
Gert: It all starts with a database project in VS. That approach facilitates the build-and-deploy metaphor because you build a project that puts out a .sql file, which you can then deploy towards the target.
Karen: So you no longer have to make changes directly to your database.
Gert: We're proposing one big mental change to the database developer: The truth is not inside your database, but the truth is inside your project. You will be able to take schema objects, build a project from them, and start working on the project. Out of the build, you'll get simply a .sql script that you can deploy to a database.
Karen: Can you create a project for an existing database?
Gert: We support two deployment scenarios: Start from scratch (i.e., a brand new installation), and incremental update (i.e., you have x, you need to go to y, and we'll build the difference for you).
Richard: We certainly will support an existing database because today the version of the truth is the database. We'll reverse engineer a database back into a project. Customers will start by creating a new database project, import their existing schema into the project from their existing database, and then work in our project system. From there, they move towards deployment to send updates out to their existing installations.
Karen: So what is a database project?
Richard: A project reflects a user database. If you had an application consisting of multiple user databases, you would have multiple database projects in a single solution. We explicitly made the decision not to make this hierarchy any deeper.
Gert: Today, a database project is merely a placeholder for a script file and a connection. With VSDB Pro, you can have scripts, but more important, we treat everything as objects. So a table is an object that has columns, constraints, indexes—all these aspects are editable, but so are versions. If you change a column name or add a constraint, that information is recorded in the version control system. We support whatever source control systems VS supports. In V1, we'll support change management for schemas.
Karen: How does your version tracking work?
Gert: Version tracking is nothing more than keeping track of all the changes in the underlying scripts inside a version control system. We treat the changes as text. We don't do anything funky. We made an explicit decision that every SQL Server tool should be able to consume the scripts that we have underneath. We don't want to divorce this from the use of existing tools. So people can still use Query Analyzer. You can integrate your project with VS Team Foundation Server for process or work-item tracking and version control, and then we have some basic reporting facilities in the schema.
Karen: Will this approach be foreign to Data Dudes?
Gert: At the end of the day, you're still working with a .sql file. We're not trying to build a new storage mechanism. We're trying to be as close as possible to what SQL Server has now. You can still use Query Analyzer and sqlcmd, but we're tracking changes inside the schema.
The big bet is that the truth is inside the project, not inside the database. So you can build a database project and reverse engineer something you already have. You can take multiple things and put them inside the project. You can start making changes to the objects.
Richard: And because the truth is in the file system, you can take a snapshot of the project, throw it on your laptop, work on it on the plane disconnected from your data center. When you come back, you can check in your changes, synch and resolve them, and do source control. So you no longer have only one guy at a time working on a live connection to your production or test server.
Refactoring
Karen: Can you talk about refactoring?
Gert: You can start by building a database project and reverse engineer it to a database you already have. You can put multiple objects in the project and start changing the objects. Some changes are not just renaming; they're really refactoring.
For example, say you have a reference to table Sales in your stored procedure. Inside your view, you rename Sales to Orders. VSDB Pro will go through your schema and make changes so that everywhere you have that table, it reflects the rename of Orders instead of Sales. If it finds a stored procedure or function that uses those names, it will also recreate them.
Richard: We'll also do validation. If you make a constraint that's not valid, we'll give you a visual indicator in your project, identifying which things have been invalidated by your changes. So say you break a table—like you delete the contents of a table and five foreign keys referenced it. We'll visually indicate which tables' foreign keys the changes have broken.
Karen: What are the limitations on refactoring in this first version?
Gert: In this release, we're restricting refactoring to renames. We will not do, for example, type changes (e.g., I changed this column, which is bound to this parameter, so I'll change the Declare statement from small int to big int). That we couldn't bite off due to time constraints. We'll do that next time.
Unit Test
Karen: Can you explain what you're doing with unit testing?
Gert: We're also adding unit testing for database objects on top of the MSTEST infrastructure. So if you have a stored procedure, you can write a unit test. We have two flavors. First, you can take the SQL-only approach and write a T-SQL-based unit test, have validations, and make that part of your unit test list and have that be part of your build system. Second, since this is all integrated into the existing test infrastructure, app developers can take the code approach and use C# or VB.
Richard: Let me give you a concrete example of a unit test. I created a unit test for a stored procedure with Northwind. I said, "Get categories by count," and I added two test conditions: Make sure it returns a nonempty result set, and make sure the row count is nine. When I ran the test, it was able to go against a defined connection, get the row count back from the stored procedure execution, tell me about the success or failure of the test, and then generate a report of the test results. Because it's integrated with the rest of the unit tests, I could have hundreds of tests all together in the same project.
Karen: So this is moving toward the integration with the app developer that you're aiming for.
Richard: Yes. When we talk about integration with the app developer, each test is a different file in a project, so I could have a database unit test (or five or ten) and then I could have some C# unit tests or some VB unit tests, all in a project. And I could have the unit test project and the database project in the same solution file, check them in, and check them out together. So now I'm a database developer, I go in and make a change to a stored procedure. I deploy that stored procedure out to a test system that has some data and run my unit tests on it and know whether the change I just made broke it.
Gert: It gets better. Wouldn't it be great if you could unit test against real data? So we add another tool, the Data Generator, to generate meaningful data. Another advantage of our Data Generator: We can generate the same test set over and over. So this is great for unit testing.
Richard: And because we're mirroring the stats, we can even get similar if not identical plans, as you'd get in a production server.
So now I define my project in VS. I build and deploy that project to a test SQL Server. I use DGEN to populate it with data. Then I run unit test against the newly populated database. And I literally just take all the pieces of this project, flow them together, and now I have a repeatable test cycle of my version of the truth being deployed repeatedly as I make changes. Then I can measure specifically with every single stored procedure, every function, row counts, performance of my regular SQL queries because I can run those as scripts. And I can repeatedly deploy, make changes, deploy, test out, and have each run checked back into source control.
I can actually measure what change I made: What was the performance or data generation impact? Did queries slow down? Did I get more data than I expected? And because all this is checked in, I can repeat those experiments every time. Now we have data to back all those questions up.
Gert: To complete this, we have the ability to compare schemas. You can compare the project with what you have in production. You can compare a test implementation and a production implementation. The ability to compare schemas is really important to us.
The Right Track
Karen: What has been the response from customers who've used VSDB Pro?
Gert: The general feedback is that nobody provides an integrated solution to do this today. That's exactly what we're doing. VSDB Pro's refactoring is unique. The way we do data generation is unique. We're meeting the need by providing all these things together as an integrated solution working in an integrated process.
