Now that you know about the DACFx in-memory database model and its associated serialized representation, the DACPAC, it’s time to cover how to generate an in-memory model. There are three ways to create an in-memory model:
- De-serialize (load) a DACPAC into a model
- Build T-SQL into a model
- Extract a model from a live database
Number one was covered in the basics section, number 3 will be covered in the next post, that leaves number two.
Database developers and administrators code in T-SQL so naturally DACFx must provide some way to convert T-SQL into an in-memory model. DACFx provides what is referred to as the compilation unit that allows you to load in arbitrary TSQL scripts into a container along with some other information and compile (or build) an in-memory model from a jumble of T-SQL statements. Once the in-memory model has been created, it can be serialized to disk as a DACPAC. The database projects in Visual Studio 2010 and the upcoming SQL Server Development Tools Code Name “Juneau” follow this workflow exactly: when building a project which is T-SQL they emit a DACPAC.
Here’s a quick example:
Video Coming Soon!
Declarative programming is pretty straightforward: you must declare the existence of an object, and nothing else. For example you can declare that table T1 exists with some set of columns but you cannot alter table T2 or alter table T3. By and large, alter syntax is not supported by the DACFx compilation service and there’s a good reason why. DACFx is not a SQL engine! It does not maintain database state and allow you to perform CRUD operations on an instance of a database, it merely creates an in-memory model of a database from defined (by CREATE statements) objects.
Effectively, this cuts down on the programmatic surface area the developer needs to worry about when authoring a database or making a change to an existing one.
Video Coming Soon!
The defined database must also be consistent. For example, I can go to a database, create a table T1, create a view V1 that refers to T1 and that’s perfectly legitimate. Of course, the view is useless, but it’s still there nonetheless. If I were to create the same view V1 that refers to a nonexistent table, then the create statement will fail because the dependent object doesn’t exist. Similarly, DACFx compilation validates all of your database object dependencies when being provided with a set of T-SQL scripts and the compilation will fail if it discovers a discrepancy such as in the case above.
DACFx also verifies that your database is contained. Containment in the database context means that the objects defined in the database and any other objects they might depend on are all contained inside the scope of the same database. Containment is an important concept that is best exemplified in SQL Azure.
SQL Azure is a fully contained database model by necessity. All databases in SQL Azure have to be contained because SQL Azure is a multi-tenant environment. In order to guarantee a base level of performance, availability, and security SQL databases have to be scoped to only allow users the ability to consume database level assets. In a nutshell, that’s why many features available today in the SQL Server box product are not currently available in SQL Azure. Over time, these features will be added into SQL Azure with a contained implementation.
The enforcement of database containment by DACFx does present a bit of an issue for on-premise SQL Server customers if they want to leverage any of the uncontained features in SQL Server. As the contained database model expands, DAC will also expand in lock-step so DAC will become more usable for these types of customers with uncontained feature requirements. For SQL Azure customers (or SQL Server customers without the need for uncontained features), the contained model fits the available features and objects so that there aren’t any limitations.
DACFx also allows the developer to set the version number, application name, or description either in the project or programmatically in the CompiliationUnit’s associated properties. You can see that here in Visual Studio 2010:
Video Coming Soon!
We’ll see how these DAC facets surface later on.