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.
Full article...
