SQL Server 2008 R2

Now that SQL Server 2008 R2 November CTP is available, I found some time to install it and test the existing Visual Studio 2008 Team System Database Edition GDR R2 release with it. The only thing that is really different from a SQL Server point of view besides two changes to the SQL parser, is that the version number changed from 10.00.xxxx to 10.50.yyyy (10.00.2531 for the November CTP). As we will see enough to confuse the VSDB 2008 GDR R2 release.

First step, is to create a connection inside Solution Explorer, no problems here, I can create a connection, navigate the schema using Solution Explorer, use the table and query designer tools. Next step is to create a project. In this case I created a SQL Server 2008 Database project named sql2008r2.

Import Database Schema

The next step is to import a schema from the SQL Server 2008 R2 instance, using the Import Database Schema option, beforehand I created an instance of the good old Northwind database on the SQL Server 2008 R2 instance, named Northwind.


The problems start after hitting the Start option, or if you were using the new project wizard to create the project and import the schema in a single step, you would face the problem after hitting the Start button inside the wizard.

The problem manifests itself as follows:


The change in version number blocks the Import Database Schema code to connect to the SQL Server 2008 R2 instance, since it presents itself with a 10.50.yyyy instead of the 10.00.xxxx version number.


Schema Compare

Now we have an empty project, so lets attempt to use Schema Compare, and compare the SQL Server 2008 R2 database (as the source) with the project as the target.


This works great, not problems! You only need to make sure that you select all the object types you want to include. By default permissions and extended properties are excluded from the comparison and would therefore not get imported.


Now we have a project, lets deploy it to a new database. In first instance this failed, because Schema Compare does not parameterize the file definitions of the database, so I had to update these, as shown below.

Updated file definitions:

    1:  ALTER DATABASE [$(DatabaseName)]
    2:      ADD FILE (NAME = [$(DatabaseName)_data], FILENAME = '$(DefaultDataPath)\$(DatabaseName).mdf', SIZE = 3328 KB, MAXSIZE = UNLIMITED, FILEGROWTH = 1024 KB) TO FILEGROUP [PRIMARY];
    4:  ALTER DATABASE [$(DatabaseName)]
    5:      ADD LOG FILE (NAME = [$(DatabaseName)_log], FILENAME = '$(DefaultDataPath)\$(DatabaseName).ldf', SIZE = 832 KB, MAXSIZE = 2097152 MB, FILEGROWTH = 10 %);

After this deploy and incremental deploy works as expected, without any problems. Next steps were to test Data Compare, Data Generation and Database Unit Testing, which all worked as expected as well.

So besides the glitch in Import Database Schema, SQL Server 2008 R2 and the VS 2008 Database Edition GDR R2 get along just fine, good to know.

GertD @ www.DBProj.com