Posts Tagged Convert

Convert VS 2010 Database Project to SSDT and Automate Publishing with Jenkins – Part 2/3

Objectives of 3-Part Series:

Part I: Setting up the Example Database and Visual Studio Projects

  • Setup and configure a new instance of SQL Server 2008 R2
  • Setup and configure a copy of Microsoft’s Adventure Works database
  • Create and configure both a Visual Studio 2010 server project and Visual Studio 2010 database project
  • Test the project’s ability to deploy changes to the database

Part II: Converting the Visual Studio 2010 Database and Server Projects to SSDT

  • Convert the Adventure Works Visual Studio 2010 database and server projects to SSDT projects
  • Create a second Solution configuration and SSDT publish profile for an additional database environment
  • Test the converted database project’s ability to publish changes to multiple database environments

Part III: Automate the Building and Publishing of the SSDT Database Project Using Jenkins

  • Automate the build and delivery of a sql change script artifact, for any database environment, to a designated release location using a parameterized build.
  • Automate the build and publishing of the SSDT project’s changes directly to any database environment using a parameterized build.

Part II: Converting the Visual Studio 2010 Database and Server Projects to SSDT

Picking up where part one of this three-part series left off, we are ready to convert the Adventure Works VS 2010 database project and associated server project to the new SSDT project type. Once converted, we will create an additional Solution configuration for Production. Finally, we will publish (vs. deploy) changes to database project’s schema to both the Development and Production environments. Note that Microsoft refers to the new format as either SSDT project type or SQL Server Database Project. I chose the prior in this post, it seemed clearer.

Convert the Projects to SSDT

Microsoft could not have made the conversion to the new SSDT project-type any simpler. Right-click on the Development server project and select ‘Convert to SQL Server Database project’. Select ‘Yes’, select ‘Backup originals for converted files’, and click ‘OK’. The conversion process should take only a minute or two. Following that, you are presented with a Conversion Report when the process is complete. The report should show the successful conversion to the SSDT project type.

Convert Project to SQL Server Database Project

Convert Project to SQL Server Database Project

Server Project Conversion Report

Server Project Conversion Report

Repeat this process for the AdventureWorks2008 database project. Again, you see a Conversion Report when complete. It should also not contain any errors, nor files marked as ‘Not converted’.

Database Project Conversion Report

Database Project Conversion Report

The New Project File Format

Reviewing the Conversion Report for the databae project, note the change to the primary project file. This is the first key difference between the VS 2010 project types and the new SSDT project types. The project file was converted from ‘AdventureWorks2008.dbproj’ to ‘AdventureWorks2008.sqlproj’ (see Conversion Report screen grab, above). Although the earlier project file with the ‘.dbproj’ file suffix is still in the project’s file directory, the Visual Studio Solution is now associated with the new ‘.sqlproj’ project file. This is the same for the server project. The ‘.dbproj’ files are no longer needed. You can drop then from the project’s file directory or from your source control system. This will prevent any confusion going forward.

Publishing Profiles

The second change you will note after the conversion is in the Solution Explorer tab. Each project has three items with the file suffix ‘.publish.xml’. These are publishing profiles. There are profiles for the each Solution configuration – Debug, Release, and Development. A publishing profile has all the settings necessary to publish changes made to the SSDT database project to a specific database environment. As part of the conversion to SSDT, all existing project settings migrated into the current project. Portions of the configuration-specific settings stay in the converted Solution configurations, while publish-specific settings are in the publishing profiles. Publishing profiles, like pre- and post-deployment scripts, are not part of the build. Select a profile in the Project Explorer tab. Note the ‘Build Action’ property in the Properties tab is set to ‘None’.

Solution Explorer View of Converted Projects

Solution Explorer View of Converted Projects

Additional Project and Profile Settings

There are also new settings in the converted projects. They support newer technologies like SSDT, SQL 2012, and Azure. As part of our first major conversion to SSDT, took the opportunity to review all project and publish settings with our database developers and DBAs. We stove to understand all the setting’s purpose and make sure they were correctly configured and documented for each of our many database environments.

Development Publishing Profile Advanced Settings

Development Publishing Profile Advanced Settings

Testing the Converted Projects

To test the successful conversion of the both project to the SSDT project types, select the Development Solution configuration and perform a Rebuild on the Solution. In the Build section of the Output tab, you should see both projects built successfully.

Initial Build Results of SSDT Projects

Initial Build Results of SSDT Projects

Development Publishing Profile

Right-click on the ‘Development.publish.xml’ file in the AdventureWorks2008 project and select ‘Publish…’ Wait for the project to build. Selecting Publish or opening a publishing profile causes the project to build. Afterwards, you should see the ‘Publish Database’ window appear. Here is where you change the settings of Development profile. When converting the Adventure Works project to SSDT, I’ve found the database connection information does not migrate to the profile. Setup the ‘Target database connection’ information in the ‘Connection Properties’ pop-up window by clicking ‘Edit…’. When finished, click ‘OK’ to return to the ‘Publish Database’ window. Finally, save the revised Development publishing profile by clicking ‘Save Profile As…’. I will not cover the specific profile settings, accessed by clicking ‘Advanced…’. Many of these settings will be specific to your environments and workflows. They can be left as default for this demonstration.

Development Publishing Profile

Development Publishing Profile

Development Publishing Profile Connection Properties

Development Publishing Profile Connection Properties

Saving Development Publishing Profile Settings for Server

Saving Development Publishing Profile Settings for Server

Generate Script for Adventure Works Database

Without leaving the ‘Publish Database’ window, click ‘Generate Script’. As in the first post, this action will initiate a schema comparison resulting the generation of a script that aggregates all the schema changes to the project, not already reflected in the database. The script represents the schema ‘delta’ (the difference) between the project and the database. The script will automatically open in Visual Studio’s main window after being created. In the ‘Data Tools Operations’ tab you should see messages indicating generation of the script was successful.

Generate Script Results

Generate Script Results

Also included in the script, along with the schema changes, are any pre- and post-deployment scripts. You should see the single post-deployment script that we created in part one of this series. Pre- and post-deployment scripts are always included in the script, whether or not they have already been executed. This is why it is imperative that pre- and post-deployment scripts are re-runnable. Scripts must have the ability to be executed more than once without producing unintended changes to the database.

Publishing to the Development Database

Next, right-click on the ‘Development.publish.xml’ file, and select ‘Publish…’. This will return you to the same window you were just in to generate the script. Click ‘Publish’. Again, in the ‘Data Tools Operations’ tab you should see messages that the publish operation completed successfully.

Successful Script Generation and Deployment to Development

Successful Script Generation and Deployment to Development

Congratulations, you have completed and tested conversion of the Adventure Works database project to SSDT project type.

Note with SSDT, the term ‘Deploy’, which refers to a specific MSBuild target, is replaced with ‘Publish’, a SSDT-specific build target. Instead of deploying changes to the database, like we did in the first post, we will publish changes with SSDT. To understand how MSBuild is able to call the new SSDT Publish target, open the AdventureWorks2008.sqlproj file by right-clicking on the project and selecting ‘Edit Project File’. In the project file’s xml you will find an ‘Import’ tag that imports the SSDT targets into the project, making them accessible to MSBuild.


<!--Import the settings-->
MSBuildExtensionsPath)\Microsoft\VisualStudio\v$(VisualStudioVersion)\SSDT\Microsoft.Data.Tools.Schema.SqlTasks.targets" />

New Production Environment

Between posts, I installed another instance of SQL Server 2008 R2, named ‘Production’. Into this instance I installed another copy of the Adventure Works database. I added the same ‘aw_dev’ user that we used in Development, with the same permissions. This SQL Server instance and Adventure Works database simulates a second database environment, Production. Normally, this instance would be installed on to separate server, but for simplicity sake I installed the Production instance on the same physical server as the Development instance. It makes no difference for the purposes of this post.

If you wish to follow all examples presented in the next two posts, you will need to install and configure the Production instance of SQL Server. Otherwise, you can disregard the portions of the two posts on publishing to Production, and just stick with the single Development environment. The conversion to SSDT doesn’t require the added Production environment.

New Production Configuration and Publish Profile

Next, we will create a new configuration in the SSDT project’s Solution and configure the resulting publishing profile, targeting the Production environment. We will use this to publish changes from the project to the Production environment. Using the Solution’s Configuration Manager, create a new Solution configuration. This process is unchanged from the VS 2010 database project-type.

New Production Solution Configuration

New Production Solution Configuration

Solution Configuration Manager

Solution Configuration Manager

New Production Configuration Build Settings for Database Project

New Production Configuration Build Settings for Database Project

Right click on the AdventureWorks2008 project and select ‘Publish…’. This will return us to the ‘Publish Database’ window. Like before, with the Development publishing profile, complete the connection string information, this time targeting the Production instance. Change the ‘Publish script name’ setting to ‘Production.sql’. Click ‘Save Profile As…’, and save this profile configuration into the project file path as ‘Production.publish.xml’. Repeat this process for the Development SSDT server project.

Database Project

Production Publishing Profile Settings for Database

Production Publishing Profile Settings for Database

Production Publishing Profile Connection Properties for Database

Production Publishing Profile Connection Properties for Database

Saving Production Publishing Profile Settings for Database

Saving Production Publishing Profile Settings for Database

Server Project

Production Publishing Profile Settings for Server

Production Publishing Profile Settings for Server

Saving Development Publishing Profile Settings for Server

Saving Development Publishing Profile Settings for Server

We now have a new Production Solution configuration and corresponding publishing profiles in each of our two projects.

Solution Explorer View of Production Publishing Profiles

Solution Explorer View of Production Publishing Profiles

We can now target two different database environments from our AdventureWorks2008 project, Development and Production. In a typical production workflow, as a developer, you would make changes to the database project directly, or copy using a source control system like TFS. After testing your changes locally, you execute the publish task to send your schema changes and/or pre- and post-deployment scripts to the Development database instance. This process is be repeated by other developers in your department.

After successfully testing your application(s) against the Development database, you are ready to release the database changes to Testing, or in this example, directly to Production. You execute the Publish task again, this time choosing the Production Solution configuration and Production publishing profile. The schema changes and any pre- and post-deployment scripts are now executed against the Production database. You would follow the same process for other environments, such as Testing or Staging.

Making Schema Changes to Multiple Environments

For this test, we will make schema changes to the ‘Employee’ table, part of the ‘HumanResources’ schema. In Visual Studio, open the Employee table and add two new columns to the end of the table, as shown below. If you have not worked with the SSDT project type before, the view of the table will look very different to you. Microsoft has changed the earlier table view to include a friendlier design view as seen in SSMS, versus the earlier sql create statement only view. There is also a window which details all the key, indexes, and triggers associated with the table. I consider this light years better in term of usability from the developer’s standpoint. Save the changes to the table object and close it.

New SSDT Table Object View

New SSDT Table Object View

Select the Development Solution configuration. Right-click on the Development profile in the AdventureWorks2008 project and click ‘Publish…’ Wait for the project to build. When the ‘Publish Database’ window appears, click ‘Publish’. You have just deployed the Employee table schema changes to the Development instance of the database.

Schema Changes to Employee Table in Script

Schema Changes to Employee Table in Script

Repeat this same process for Production. Don’t forget to switch to the Production Solution configuration and select the Production publish profile. You have now applied the same schema changes to the Production environment. Your customer will be happy they can now track the drug testing of their employees.

Successful Script Generation and Deployment to Development

Successful Script Generation and Deployment to Development

There are other methods available with SSDT to deploy changes to the database. Using a script is the method I have chosen to show in this post.

Conclusion

In this post we converted the Adventure Works database project and Development server project to SSDT project-types. We created a new Solution Configuration and publishing profiles, targeting Production. We made schema changes to the SSDT database project. Finally, we deployed those changes to both the Development and Production database environments.

In Part III of this series, I will show how to use Jenkins CI Server to automate building, testing, delivering scripts, and publishing to a database from the SSDT database project.

, , , , , , , , , , , , , , , , , ,

7 Comments

Convert VS 2010 Database Project to SSDT and Automate Publishing with Jenkins – Part 1/3

Objectives of 3-Part Series:

Part I: Setting up the Example Database and Visual Studio Projects

  • Setup and configure a new instance of SQL Server 2008 R2
  • Setup and configure a copy of Microsoft’s Adventure Works database
  • Create and configure both a Visual Studio 2010 server project and Visual Studio 2010 database project
  • Test the project’s ability to deploy changes to the database

Part II: Converting the Visual Studio 2010 Database and Server Projects to SSDT

  • Convert the Adventure Works Visual Studio 2010 database and server projects to SSDT projects
  • Create a second Solution configuration and SSDT publish profile for an additional database environment
  • Test the converted database project’s ability to publish changes to multiple database environments

Part III: Automate the Building and Publishing of the SSDT Database Project Using Jenkins

  • Automate the build and delivery of a sql change script artifact, for any database environment, to a designated release location using a parameterized build.
  • Automate the build and publishing of the SSDT project’s changes directly to any database environment using a parameterized build.

Background

Microsoft’s Visual Studio 2010 (VS 2010) IDE has been available to developers since April, 2010. Microsoft’s SQL Server 2008 R2 (SQL 2008 R2) has also been available since April, 2010. If you are a modern software development shop or in-house corporate development group, using the .NET technology stack, you probably use VS 2010 and SQL 2008 R2. Moreover, odds are pretty good that you’ve implemented a Visual Studio 2010 Database Project SQL Server Project to support what Microsoft terms a Database Development Life Cycle (DDLC).

Now, along comes SSDT. Recently, along with the release of SQL Server 2012, Microsoft released SQL Server Data Tools (SSDT). Microsoft refers to SSDT as “an evolution of the existing Visual Studio Database project type.” According to Microsoft, SSDT offers an integrated environment within Visual Studio 2010 Professional SP1 or higher for developers to carry out all their database design work for any SQL Server platform (both on and off premise). The term ‘off premises’ refers to SSDT ‘s ability to handle development in the Cloud – SQL Azure. SSDT supports all current versions of SQL Server, including SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012 and SQL Azure.

SSDT offers many advantages over the VS 2010 database project type. SSDT provides a more database-centric user-experience, similar to SQL Server Management Studio (SSMS). Anyone who has used VS 2010 database project knows the Visual Studio experience offered a less sophisticated user-interface than SSMS or similar database IDE’s, like Toad for SQL. Installing Microsoft’s free SSDT component, after making sure you have SP1 installed for VS 2010 Professional or higher, you can easily convert you VS 2010 database projects to the new SSDT project type. The conversion offers a better development and deployment experience, and prepare you for an eventual move to SQL Server 2012.

Part I: Setting up the Example Database and Visual Studio Projects

Setting up the Example

To avoid learning SSDT with a copy of your client’s or company’s database, I suggest taking the same route I’ve taken in this post. To demonstrate how to convert from a VS 2010 database project to SSDT, I am using a copy of Microsoft’s Adventure Works 2008 database. Installed, the database only takes up about 180 MBs of space, but is well designed and has enough data to use as a good training tool it, as Microsoft intended. There are several version of the AdventureWorks2008 database available for download depending on your interests – OLTP, SSRS, Analysis Services, Azure, or SQL 2012. I chose to download the full database backup of the AdventureWorks2008R2 without filestream for this post.

Creating the SQL Server 2008 R2 Instance and Database

Before installing the database, I used the SQL Server Installation Center to install a new instance of SQL Server 2008 R2, which I named ‘Development’. This represents a development group’s database environment. Other environments in the software release life-cycle commonly include Testing, Staging, and Production. Each environment usually has its own servers with their own instances of SQL Server, with its own copy of the databases. Environments can also include web servers, application servers, firewalls, routers, and so forth.

After installing the Development instance, I logged into it as an Administrator and created a new empty database, which I named ‘AdventureWorks’. I then restored the downloaded backup copy of Adventure Works 2008 R2, which I downloaded, to the newly created Adventure Works database.

Creating the Database 01

Creating the New Database

Creating the Database 02

Creating the New Database

Creating the Database 03

Creating the Database: Restoring the Full Backup

Creating the Database 04

Creating the Database: Restoring the Full Backup

Creating the Database 05

Creating the Database: Restoring the Full Backup

You may note some differences between the configuration settings displayed below in the screen grabs and the default configuration of the Adventure Works database. This post is not intended to recommend configuration settings for your SQL Server databases or database projects. Every company is different in the way it configures its databases. The key is to make sure that the configuration settings in your database align with the equivalent configuration settings in the database project in Visual Studio 2010. If not, when you initially publish changes to the database from the database project, the project will script the differences and change the database to align to the project.

Creating the Database 06

Creating the Database: Database Properties

Creating the Server Login and Database User

Lastly in SSMS, I added a new login account to the Development SQL Server instance and a user to the Adventure Works database, named ‘aw_dev’. This user represents a developer who will interact with the database through the SSDT database project in VS 2010. For simplicity, I used SQL authentication for this user versus Windows authentication. I gave the user the minimal permissions necessary for this example. Depending on the types of interactions you have with the database, you may need to extend the rights of the user.

New Database Login 01

New Database Login

New Database Login 02

New Database Login

Two key, explicit permissions must be assigned for the user for SSDT to work properly. First is the ‘view any definition’ permission on the Development instance. Second is the ‘view definition’ permission on the Adventure Works database. These enable the SSDT project to perform a schema comparison to the Adventure Works database, explained later in the post. Lack of the view definition permission is one of the most common errors I’ve seen during deployments. They usually occur after adding a new database environment, database, database user, or continuous integration server.

New Database Login: ‘View any definition’ Permission

New Database Login: ‘View any definition’ Permission

New Database Login 03

New Database Login: ‘View definition’ Permission

Setting up Visual Studio Database Project

In VS 2010, I created a new SQL Server 2008 database project, named ‘AdventureWorks2008’. In the same Visual Studio Solution, I also created a new SQL Server 2008 server project, named ‘Development’. The database projects mirrors the schema of the Adventure Works database, and the server project mirrors the instance of SQL Server 2008 R2 on which the database is housed. The exact details of creating and configuring these two projects are too long for this post, but I have a set of screen grabs, hyperlinked below, to aid in creating these two projects. For the database project, I only showed the screens that are signficantly different then the server project screens to save some space.

Server Project

VS 2010 Server Project 01

VS 2010 Server Project: New Project SQL Server 2008 Wizard

VS 2010 Server Project 02

VS 2010 Server Project: New Project SQL Server 2008 Wizard

VS 2010 Server Project 03

VS 2010 Server Project: Project Properties

VS 2010 Server Project 04

VS 2010 Server Project: Set Database Options

VS 2010 Server Project 06

VS 2010 Server Project: Import Database Schema

VS 2010 Server Project 08

VS 2010 Server Project: Database Connection Properties

VS 2010 Server Project 09

VS 2010 Server Project: Configure Build and Deploy

Database Project

VS 2010 Database Project 01

VS 2010 Database Project: New Project SQL Server 2008 Wizard

VS 2010 Database Project 02

VS 2010 Database Project: Project Properties

VS 2010 Database Project 03

VS 2010 Database Project: Summary

Reference from Database Project to Server Project

After creating both projects, I created a reference (dependency) from the Adventure Works 2008 database project to the Development server project. The database project reference to the server project creates the same parent-child relationship that exists between the Development SQL Server instance and the Adventure Works database. Once both projects are created and the reference made, your Solution should look like the second screen grab, below.

Adding a Reference to the Database Project

Adding a Reference to the Database Project

Database Project Dependencies

Database Project Dependencies

Final View of VS 2010 Solution with both Projects and Reference

Final View of VS 2010 Solution with both Projects and Reference

Creating the Development Solution Configuration

Next, I created a new ‘Development’ Solution configuration. This configuration defines the build and deployment parameters for the database project when it targets the Development environment. Again, in a normal production environment, you would have several configurations, each targeting a specific database environment. In the context of this post, a database environment refers to a unique combination of  servers, server instances, databases, data, and users. For this first example we are only setting up one database environment, Development.

New Development Solution Configuration 01

New Development Solution Configuration

New Development Solution Configuration 02

New Development Solution Configuration

The configuration specifies the parameters specific to the Adventure Works database in the Development environment. The connection string containing the server, instance, and database names, user account, and connection parameters, are all specific to the Development environment. They are different in the other environments – Testing, Staging, and Production.

Testing the Development Configuration

Once the Development configuration was completed, I ran the ‘Rebuild’ command on the Solution, using the Development configuration, to make sure there are no errors or warnings. Next, with the Development configuration set to only create the deployment script, not to create and deploy the changes to the database, I ran the ‘Deploy’ command. This created a deployment script, entitled ‘AdventureWorks2008.sql’, in the ‘sql\Development’ folder of the AdventureWorks2008 database project.

Create a Deployment Script Only

Database Project Configuration Settings to Create a Deployment Script Only

Deployment Script Location in Project

Deployment Script Location in Project

Since I just created both the Adventure Works database and the database project, based on the database, there are no schema changes in the deployment script. You will see ‘filler’ code for error checking and so forth, but no real executable schema changes to the database are present at this point. If you do see initial changes included in the script, usually database configuration changes, I suggest modifying the settings of the database project and/or the database to align to one another. For example, you may see code in the script to change the database’s default cursor from global to local, or vice-versa. Or, you may also see code in the script to the databases recovery model from full to simple, or vice-versa. You should decide whether the project or the database is correct, and change the other one to match. If necessary, re-run the ‘Deploy’ command and re-check the deployment script. Optionally, you can always execute the script with the changes, thus changing the database to match the project, if the project settings are correct.

Testing Deployment

After successfully testing the development configuration and the deployment script, making any configuration changes necessary to the project and/or the database, I then tested the project’s ability to successfully execute a Deploy command against the database. I changed the Development configuration’s deploy action from ‘create a deployment script (.sql)’ to from ‘create a deployment script (.sql) and deploy to the database’. I then ran the ‘Deploy’ command again, and this time the script is created and executed against the database. Since I still had not made any changes to the project, there were no schema changes made to the database. I just tested the project’s ability to create and deploy to the database at this point. Most errors at this stage are insufficient database user permissions (see example, below).

Test Deployment to the Database

Test Deployment to the Database

View Any Definition Permission Error

View Any Definition Permission Error

Testing Changes to the Project

Finally, I tested the project’s ability to make changes to the database as part of the deployment. To do so, I created a simple post-deployment script that changes the first name of a single, existing employee. After adding the post-deployment script to the database project and adding the script’s path to the post-deployment script file, I again ran the ‘Deploy’ command again, still using the Development configuration. This time the deployment script contained the post-deployment script’s contents. When deployed, one record was affected, as indicated in VS 2010 Output tab. I verified the change was successful in the Adventure Works database table, using SSMS.

Test Changes Deployed to Database 02

Simple Post-Deployment Script

Test Changes Deployed to Database 01

Simple Post-Deployment Script

Test Changes Deployed to Database 03

Post-Deployment Script Location in Project

Test Changes Deployed to Database 04

One Row Affected by Deployment this Time

Before Post-Deployment Script was Ran

Before Post-Deployment Script was Deployed

After Post-Deployment Script was Ran

After Post-Deployment Script was Deployed

Conclusion

We now have a SQL Server 2008 R2 database instance representing a Development environment, and a copy of the Adventure Works database, being served from that instance. We have corresponding VS 2010 database and server projects. We also have a new Development Solution configuration, targeting the Development environment. Lastly, we tested the database project’s capability to successfully build and deploy a change to the database.

In Part II of this series, I will show how to convert the VS 2010 database and server projects to SSDT.

, , , , , , , , , , , , , , , , , , ,

5 Comments