Data-tier applications supported out of the box with SQL Server 2008 R2 even in its Nov (2009) - CTP were designed expressly to address the issues of development, deployment and manageability of data-centric applications that were difficult in earlier versions. The main tactic adopted was to integrate with Visual Studio evermore so much so that for all purposes SQL Server databases can be centrally managed by Visual Studio 2010 alone. The central management of deployed environment brings in a lot of refinement to an already well refined product.
Introduction
A Data Application Component of which Data-tier applications are made of is an entity that integrates all data tier related objects used in authoring, deploying and managing into a single unit instead of working with them separately. Programmatically DACs belong to classes that are found in The Microsoft.SqlServer.Management.Dac namespace. DACs are stored in a DacStore and managed centrally. DACs can be authored and built using SQL Server Data-Tier Application templates in VS2010 or using SQL Server Management Studio. In an earlier article (http://www.packtpub.com/article/working-with-data-application-components-sql-server-2008 -r2) creating DAC using SQL Server 2008 R2 Nov-CTP(R2 server was described where a data access component was extracted from an existing database using tools in SSMS and SQL Server 2008 R2 Nov_CTP. Later on it was shown how the extract can be deployed to another server with a construct akin to installing an executable application with an msi file.
In this article you will learn how to create a DATPAC file; the file that can be deployed to a server after connecting to it. The article shows how you may deploy it manually to a server. In particular you will be creating a database which has a table and a view based on the table when the application is not even connected to the server. During deployment you will connect to the server and complete deployment.
Tools needed for this article
In order to start working with this example you need to download SQL Server 2008 R2. The ease with which SQL Server 2008 R2 installs depends on the Windows OS on your machine. I had encountered problems installing it on my Windows XP SP3 where only partial files were installed. On Windows 7 Ultimate it installed very easily but had some problems with Windows 7 Ultimate (http://www.sswug.org/articles/viewarticle.aspx?id=48766). This article uses the R2 Server installed on Windows 7 Ultimate. You can download the R2 Server from this link after registering at the site. Download the x 32 version here (http://technet. microsoft.com/en-us/evalcenter/ee315247.aspx), a 1.2 GB file. You also need Visual Studio 2010 and the version used in this book is Version 10.0.30181 RC1Rel. You can download an evaluation version from here: (http://store.microsoft.com/microsoft/ Visual-Studio-2010-Ultimate-with-MSDN-Renewal/product/C20283FE).
Overview of the article
We will delineate step-by-step the procedure to create a data-tier application in VS2010 using the out-of-the box template. This application will have a data-tier component consisting of a table and a view based on the table. We create both of these within the application. We shall then try when the build succeeds to deploy it to a server. We review the requirements for a successful deployment.
Creating a data-tier application in VS2010
It is assumed that the suggested version of Visual Studio 2010 Ultimate is installed on your computer. From Start | All Programs…you can access the Microsoft Visual Studio 2010 shortcut. Run the program as the administrator of your computer.
Click File | New | Project…menu items to open the New Project window as shown. In the Installed templates on the left expand Database and click SQL Server to reveal all the SQL Server related project items as shown. Follow this by highlighting SQL Server Data-Tier Application as shown. Change the default Name, Location and Solution name fields on the New Project window. The one chosen herein are shown in the next figure.
Click OK to create the project. The project with a folder structure as shown is created. Schema and Scripts folder are created in addition to several other items.
The Schema Objects folder when expanded displays both the Database Level Objects as well as folders related to Schemas (this contains the default one for the dbo) as shown.
The various objects accessible to the dbo are shown here. These are the various database objects such as Tables, Views, Programmability items etc
Creating a table in the application
The first element we add will be a table to the project. Right click Tables in the previous figure and click Add to display the items shown on the left. Click Table…menu item.
This opens the Add New Item window as shown. Click the Database Project | Tables and Views menu. These are items under Installed templates on the left side of the Add New Item window. Click Table. Change the default (table) Name. Herein it is TestTable. Click Add.
This adds a template SQL (TestTable.table.sql) for creating a table as shown. The script has the same name as the table.
Replace the script with the following script. You may use your script as well. The shown script creates a table with a clustered Primary Key.
Build the project by hitting the Build main menu item. The project is built with the result in Output as shown. Notice that the table schema is created in the project model but disconnected from any server.
This results in adding the SQL necessary to create a table in the Tables folder as shown.
Create a view in the application
In here you will create a View based on the table you created in the previous step. Right click Views to open the drop-down lists and choose Add | View… as shown.
Again choose to add a View. Change the default name of view to something different. Herein it is TestView.
Click Add. This adds a template script for the view as shown. The view is to be based on a table or another view as suggested.
Create a view by choosing some of the columnsfrom the table created earlier as shown. The intellisense works for these codes and you should see a drop-down menu as shown.
When you click TestTable in the above drop-down the script is completed as shown.
Again build the project. The Output window shows that the project was built successfully
After the project build, you will notice that a script for creating a view (TestView.view.sql) is added to the Views folder as shown.
Notice that the project is not connected.
Deploying the project
We could go on and create other objects as well along the same lines. For the purposes of this article we will just use these two objects and try to deploy them.
Click Project | Tier01… (In this case, may be different in your case) to open the Tier01 properties window as shown displaying various items from Project Settings to Code Analysis. The Project Settings page of the project properties is shown. Note that the Default schema is dbo.
What else needed for deployment?
You can deploy using the drop-down menu Deploy of the project as shown.
You know the state of the connection is disconnected. If you were to deploy nonetheless, you will get a build error as shown. The lesson is, before you can deploy a DAC, you must specify a connection string in the project as displayed in the build result.
Click Project | Tier01… (In this case, may be different in your case) to open the Tier01 properties window as shown displaying various items. Click Deploy on the left. This brings up the deployment related items that needs configured as shown.
Click Edit…. This brings up the Connect to Server page of SQL Server 2008 R2 as shown.
Make sure you connect to SQL Server 2008 R2’s Database Engine which happens to be the default in the above. Click OK. This enters the connection string in the related field on the Tier01 project properties page as shown.
Build the project and verify it succeeds.
Right click Project (Tier01) and click Deploy in the drop-down menu. The program processes the request (this may take some time) and the result of deployment appears in the Output window as shown here:
------ Build started: Project: Tier01, Configuration: Debug Any CPU ------
Tier01 -> C:\Users\jay\documents\visual studio 2010\Projects\Tier01\Tier01\sql\debug\Tier01.dacpac
------ Deploy started: Project: Tier01, Configuration: Debug Any CPU ------
Pre-Deploy: Starting script execution…
Pre-Deploy: Finished script execution.
Validating upgrade
Preparing system tables in msdb in the SQL Server instance 'HODENTEK3\KUMO'
Preparing deployment script
Creating database 'Tier01_1_0_0_0__129179150926142316'
Creating schema objects in database 'Tier01_1_0_0_0__129179150926142316'
Registering the DAC in msdb
Setting database 'Tier01' to read-only mode
Disconnecting users from database 'Tier01'
Preparing scripts to copy data from database 'Tier01' to database 'Tier01_1_0_0_0__129179150926142316'
Disabling constraints on database 'Tier01_1_0_0_0__129179150926142316' before populating data
Inserting data from database 'Tier01' to database 'Tier01_1_0_0_0__129179150926142316'
Enabling constraints on database 'Tier01_1_0_0_0__129179150926142316'
Setting database 'Tier01' to read-write
Renaming database 'Tier01' to 'Tier01_1_0_0_0__129179150932722692'
Renaming database 'Tier01_1_0_0_0__129179150926142316' to 'Tier01'
Upgrading DAC metadata in msdb to reflect the new DAC version
Previous copy of database Tier01 is available as Tier01_1_0_0_0__129179150932722692.
Post-Deploy: Starting script execution…
Post-Deploy: Finished script execution.
========== Build: 1 succeeded or up-to-date, 0 failed, 0 skipped ==========
========== Deploy: 1 succeeded, 0 failed, 0 skipped ==========
This shows a successful deployment.
Open SQL Server Management Studio and verify the created objects from the DAC deployed. This is shown in the next figure.
Summary
The article described in detail creating a data-tier project in Visual Studio 2010 and deploying it to the SQL Server to create database objects.
