I have been looking at Microsoft's forthcoming SQL Server 2011, code-named Denali, for which the third preview has recently been released. There is plenty to say about Denali, which has many new business intelligence features as well as the intriguing ability to publish a table as a network share accessible from the file system, but I am particularly interested in the new developer tools, known as Project Juneau.
What is Project Juneau? Well, the old SQL Server Management Studio is being redone using the Visual Studio shell, but what is more interesting is the new SQL Server Database Project in the full Visual Studio, along with some new tools for working with databases.
Now at this point I have a confession to make. I have never given Visual Studio Database Projects the attention they deserve. Visual Studio 2008 instroduced a specific database edition, with a specific database project type. In Visual Studio 2010 this became a feature of the Premium and Ultimate editions. Juneau includes the next version of the database project type, now called a SQL Server Database Project.
Just in case others have also paid little attention to Visual Studio database projects, the core feature is the ability to treat databases as code.
How is a database code? It helps to break down what we typically mean by a "database":
1. The data itself.
2. The structure of the database: tables, column types, indexes.
3. Code embedded with the database structure and executed by the database manager, included stored procedures, triggers, user-defined functions.
Of these, it is only the third category that I had previously considered to be code. I was wrong though. The database schema is also code. Further, since the schema can be instantiated by running SQL create statements, you can conveniently represent a schema with that code. Execute the code, and you instantiate the database schema.
Once you start treating the database schema as code, new things become possible. You can do all the things that you usually do with code: put it under version control, refactor it, compare it with other versions, and so on.
This is what Juneau does. When you import a database into Junueau, it becomes a set of SQL create scripts.
This is also what the old Database Project does, so the concept is not new. Microsoft describes the Juneau tools as:
an evolution of the existing Visual Studio Database project type
which can be interpreted to mean that this is a new product which will eventually encompass everything the old product did and more, but that initially there are compromises: while there are new features, there are some other features mssing. Since Juneau is currently in preview it is impossible to be definitive about this yet.
Still, there is plenty of good stuff in Juneau. They follow through on another implication of treating the database as code, which is that you can debug it, by building a local version of the database. The Juneau tools do this, using a new local instance of SQL Server. When you publish the database to production, you have a bunch of options concerning how you want to handle the operation, given that there may be an existing database already present. There is always an option to generate script, rather than executing the operation immediately. The same is true if you change the schema of a connected database in Visual Studio's server explorer. The Juneau tools show all the implications of any change, including warning about data loss when necessary, and offer to generate a script rather than immediately applying the change.
Schema Compare is another useful feature. Imagine that you import am existing database into Visual Studio for application development. This takes three months; but in the meantime the admins have made some changes to the production database, maybe for security or performance reasons. If you have also added some tables and rows for the new application in your development version of the database, this can be awkward to reconcile. Schema Compare lets you see the differences easily.
A goal of the Juneau tools is to make it easy to migrate a database from one platform to another. Microsoft has in mind that some developers will be moving databases from on-premise servers to SQL Server Azure; but irrespective of whether you have cloud hosting in mind, this is a useful feature.
One of the reasons the old Database Projects are perhaps not as well known as they should be is that they are reserved for the high-end Visual Studio editions. I hope Microsoft makes the Juneau tools more widely available, because treating the database as code is a powerful idea, with benefits that should please the operations folk as well as developers.
Listed below are links to blogs that reference this entry: Database as code: Microsoft's new SQL Server "Juneau" tools.
TrackBack URL for this entry: