Saturday, May 30, 2009

Database Deployment with Visual Studio 10

Visual Studio 2010 (VS 10) Beta1 was released recently...  You can download VS 10 Beta1 from Microsoft download page….

Do check out the update on Visual Studio features for Web Developers on our team blog…

We have been discussing about a bunch of deployment features previously and today we will discuss about Database Deployment with VS 10….

VS 10 will allow you to deploy your databases both while creating a web package as well as while publishing directly to a web server provided by your hoster…

To catch up on the previous web deployment posts check out the below links:

  • Web Deployment with VS 2010 and IIS
  • Web Packaging: Creating a Web Package using VS 2010
  • Web Packaging: Creating web packages using MSBuild
  • How does Web Deployment with VS 10 & MSDeploy Work?
  • Installing Web Packages using Command Line
  • Web.config Transformation

     

    Apart from the “Package/Publish” tab, with VS10 we will now have a new tab called “Deploy-SQL” in the project properties of Web Application Projects (WAPs)

    The “Deploy SQL” tab looks like below:

    Deploy SQL tab

    Firstly let us note that this tab is Build configuration aware i.e. you can configure different settings for “Release” vs “Debug” environment… I have set the configuration to “Release”  and Platform to “Any CPU” for the above example…

  • TIP:  If you are deploying to a hosted environment then you can create different build configurations per hoster e.g. “DiscountASP” or “OrcsWeb”, this way you can configure various Database deployment settings per hoster…  You can use the same principle for various different database servers you are going to even within your intranet…

    Anyways, the first grid on “Deploy SQL”  tab looks like below and actually is a MASTER part of MASTER-DETAIL view of your Database Deployment Settings.  Every Database Entry in the grid below is a representative of a group of database settings representing source and destination database information:

    connectionString name grid

    i.e. Database Entry (Friendly Name) = Source Database Information + Destination Database Information

    My advice would be to create one entry per every destination database you are deploying to…  The reason why I am emphasizing destination database is coz many folks want to deploy more than one source databases to a single destination database…

    One classic example of this is when you are using ASP.NET membership or login features then ASP.NET automatically creates ASP.NET DB in your project AppData directory, apart from this people also like to create a separate ApplicationDB which has the application specific business data.  As you can imagine this results into two different source DBs.  When you are trying to deploy to a shared hosting environment then many times you are paying per database to your hoster and the obvious thought “my ApplicationDB does not conflict with ASP.NET DB in anyway, I am happy to have both of the DBs to go to the same destination DB without paying for two databases”…  In such a situation you would want to create one database entry in the grid above for every destination connection string that you have.

    The “Add” button at the bottom allows you to add a “Friendly Name” for your Database Entry…  If you would like to go to more than one destination database you can feel free to add as many Database Entries in the grid above as you want… For simplicity of tracking I would recommend using your connectionString name as the Friendly Name in the grid above.

    As you might have already noticed each entry in the grid also has a check box next to it…  The reason why this check box is provided is so that once you configure the database entry then you do not have to completely remove it  by clicking “Remove” button (and loose all the configured data)… If you do not want to include a  particular database Entry (friendly name) in a particular deployment scenario you can simply exclude it by un-checking the checkbox. 

    In order to permanently “Remove” the database entry you can select the entry to be removed and can click the “Remove” button….

  • Let us now take a look at the details of a database entry (e.g details of “productsDB” in the above example).  As I mentioned above the details are going to be broken down into two major categories…

    1. Destination Database Information

    2. Source Database Information

    _____________________________________________________________________________________________________

    Destination Database Information: This section looks as below:

    destination database information

    It is relatively simple, coz all it contains is connection string for the destination database… Typically when you get a hosted account your hoster will send you the connection string of your hosted database in an email.  This connectionString should go into the above text box.

    This field is kept optional and the reason behind it is that if you are using Web Packaging as a model for your deployment and are likely to hand over the web package to your system administrator or someone who will provide connectionString at install time then you can feel free to leave this field empty. 

    Do note that if you are directly publishing to a hoster (i.e. not packaging) and you have left this field empty then you will get an error during publishing.

    __________________________________________________________________________________________________

    Source Database Information: This section looks as below:

    source database information

    The first thing you will see in the Source Database Information is a check box which indicates “Pull data from an existing database” … If you have a local SQL Server database (SQL 2005 or 2008) which you are using for development then this will allow you to extract your database to deploy to the destination database (mentioned in the section above)….

    The first setting under “Pull data from an existing database” is Connection string for the source database… This text box is enabled only when you check the check box for “Pull data from…”  You can also click the “…” button next to the connection string text box which will launch the connection String builder dialog as below:

    connectionString Builder

    This dialog can help you easily create the connection String for your local database…

    The next set of controls under the source connection String text box allow you to determine what kind of SQL Script would like to generate from your source database.  Currently there are two options available…

    Schema Only  - Choosing this will only script the schema of your source database

    Complete Database – Choosing this will script both schema and data of your source database

    On your dev SQL Server it is likely that you might have some test data, in that situation you would want to choose “Schema Only” option which will only pick up the DB schema leaving out the data…  On the other hand if you choose complete database everything from your source database will be scripted…

    Insider Note:  VS 10 Database Deployment is using SQL Management Objects (SMO) behind the scenes to script your database…  SMO is a tried and tested API for scripting huge databases… scriptSchema & scriptData are SMO scripting options which Visual Studio is setting when you choose one of the options above…

    The next check box is “Exclude object with no schema information” which will give you further fine grain control on scripting your database.  If you do not care about schemaQualify feel free to uncheck this checkbox…

    Database Source Scripts -  You might have noticed that in this section there is already an entry called “[Auto-generated Schema Only]”… This entry is added to the source database scripts as soon as you check the “Pull data from an existing database”… At the bottom of Database Source Scripts grid you will notice “Add Script” and “Remove Script” button…  These buttons will allow you to add & remove extra .SQL scripts as your source…

    Most of the times developers or DBAs create .SQL scripts to deploy databases…  These custom .SQL scripts can be added and ordered in the grid (and thereby during deployment) using the up and down arrows…  The “[Auto-generated Schema Only]” is a representative of the script that VS will automatically generate from your source database, it is added to the grid so that you can order it up and down along with the rest of the .SQL scripts that you might want to add…

    Also note that if you are generating your .SQL scripts via tools like Visual Studio Database Edition (Data Dude) or Red Gate then you can add those scripts here to be included in your web package as well. 

    NOTE: There is a way to set up automated build and deployment environment in which you can collect .SQL scripts from Data Dude or Red Gate and provide to VS 10 during web deployment.  I will cover this topic separately in a different blog post…

    _________________________________________________________________________________

    The last section of the “Deploy SQL” tab is a place for you to provide Database Deployment notes so that you can note specific steps you took or scripts you used while deploying databases; this is purely for your reference purposes…. The deployment notes section looks as below…

    Database Deployment Notes

    Once you have provided all of this information you are good to deploy your databases using Visual Studio 2010 Web Deployment…

    Web Packaging  + DB Deployment

    After setting up your DB deployment as shown above you can now right click on your project and hit Package –> Create Package as shown below…Create Package

    The package created as a result will contain your database…  You can read details about creating and installing the web packages below:

  • Web Packaging: Creating a Web Package using VS 2010
  • Web Packaging: Creating web packages using MSBuild
  • Installing Web Packages using Command Line
  •  

    Web Publishing + DB Deployment

    Once you set up the DB then not only will it get included in Web Packages but will also be available to be deployed via Publishing…  You can publish you project by right clicking on your project and hit Publish as shown below…

     Publish

    I will soon write a detailed post on how to use the new “Publishing” features of Visual Studio 2010…

    Hope this information was useful…

    -Vishal

    19 comments:

    katerina said...

    Thanks for share!

    Vishal R Joshi said...

    Glad you like it Katerina...

    Anonymous said...

    Is this for Standard, Professional, or Team System?

    Vishal R Joshi said...

    This feature will be available in all VS SKUs including VWD Express 2010... It won't be avaialable for C# Express, VB Express or C++ Express editions but it will be available in Pro, VSTS etc...

    Daniel Root said...

    Does this support any sort of database versioning? For example, when you publish an update to the sample app above, how should the developer detect which version of the production database is being updated and add the necessary columns/tables/etc?

    Vishal R Joshi said...

    Hi Daniel,
    Out of the box Web Deployment in VS 2010 does not support automatic diffing between the two databases...

    In the database source scripts section there is an option to attach the incremental DB diff scripts which can be produced by using external tools which do Schema & Data Diff...

    Eventually we will work on providing this functionality in the box... I am hoping to write a blog post explaining how to use external providers for doing incremental DB deployment so look forward to that...
    Thanks
    Vishal

    Anonymous said...

    This new experience is completely frustrating and effectively useless. VS2010 Professional includes beautiful, perfect "Database Projects" that can now automatically figure out database schema dependencies and handle the deployment of schemas.

    What I would expect is to be able to select my DB Project from my Web Project and have MSDeploy use VSDBCMD to deploy the .dbschema file. *no one* in their right mind is going to "deploy from a database" as the current implementation in 2010 Web Projects basically requires you to do.

    I'm going to have a .SQL file, or a set of .SQL files, or (more likely) a .dbschema file. End of story. My dev machine does not have a live, ready to go database. My build server does not have a database, and the staging servers that will deploy to our other IIS servers do not have a database.

    So why on earth does the UI ask for one? "Import from Web.config" - are you guys nuts? A developer's local web.config does NOT point to a production database!

    Anonymous said...

    The way I see it, that Professional user will certainly use .sql file and dbproject, which VS2010 supports anyway. But for beginners using web express, it's just easier for them to deploy from a sql server express from their dev machine.

    Xinyang from Vishal's team.

    Jonx said...

    Is this the only way to get the current database data and schema scripted? Do I need to go trought all that fuss and call Build Deployment Package to get access to the sql script of my DB? No other way to get it? Any hint? Thanks...

    Anonymous said...

    VS2010 installation installed Microsoft Web Deploy tool in "%programfiles%\iis\microsoft web deploy" folder. You can use the simple command line like following to do the scripting yourself.

    msdeploy -verb:sync -source:dbfullsql="Data Source=myServer;Initial Catalog=SimpleDB1_test13;User Id=sa;Password=XXXX" -dest:dbfullsql="d:\temp\SimpleDB1_test13.sql"

    or

    msdeploy -verb:sync -source:dbfullsql="Data Source=.\SQLEXPRESS;AttachDbFilename=d:\temp\WebApplication1\App_Data\Database1.mdf;Integrated Security=True;User Instance=True",scriptdata=true -dest:dbfullsql="d:\temp\test1.sql"

    For more details, please search for msdeploy in Bing. :)

    Xinyang from Vishal's team

    Jonx said...

    Excellent, very good, thanks a lot...

    Anonymous said...

    For initial deployment from local dev to test, staging or production this is just great and I like it. I'm a bit concerned about preserving data in destination deployment locations, though. From your earlier post of September 1st 2009 2:22 pm, you said:

    In the database source scripts section there is an option to attach the incremental DB diff scripts which can be produced by using external tools which do Schema & Data Diff...

    Can you clarify how incremental changes can be deployed to existing databases while preserving their data? I can see how to include additional scripts post-deployment but surely the main deployment would overwrite the current destination database...?

    Have I missed something?

    Kris.

    Vishal R Joshi said...

    Hi Kris,
    In visual studio (I believe VSTS) there is a specific project called as Databse project... When you create one of those you can right click and say "Import Database Objects & Settings" which will allow you to point it to your actual DB... Note that this has to be your V1 DB... Now you can make changes to the DB project to get it to V2 state and then when you ask it to produce the SQL script it will produce the Diff scripts to get V1 to V2...
    When you hook that script into Package/Publish SQL tab then at that time you will have to uncheck the "Pull data and/or schema from an existing database" that way VS will not try to script the DB out from ground up but instead use the diff script that you added using "Add Script" button...
    There is an article which my team wrote about even automating the process at http://blogs.msdn.com/b/webdevtools/archive/2010/03/24/extending-the-web-publishing-pipeline-to-package-database-project-deployed-sql-file.aspx..
    Hope this helps...
    Thx
    Vishal

    Anonymous said...

    Vishal

    "Now you can make changes to the DB project to get it to V2 state and then when you ask it to produce the SQL script it will produce the Diff scripts to get V1 to V2..."

    - How do I do this? All I seem to be able to do is create a fresh complete database script.

    Kris.

    Anonymous said...

    Vishal

    With a bit more research and effort I managed to get it all to work. Thanks for giving me just enough information to work it out for myself!

    Kris.

    Vishal R Joshi said...

    Kris, glad to know things are working for you now...
    -Vishal

    Diva said...

    nice info..
    thx, because it can help me finish my job :D

    Anonymous said...

    Vishal,
    I am not a sophisticated user of Visual Studio but from what I have read I should be abble to use the Database Publishing Wizard to deploy my .MDF database to my hosted SQL Server. I am connected to the DB in VS2010 Professional, the tables and data are all there, but when I right click the DB I do not have a choice of "Deploy to Provider". I have seen in the forums that others had this problem in VS 2008. I have searched for the .exe file for the Wizard, but no luck. According to other blogs and posts it is standard in VS 2010. Any ideas on how to solve this will be greatly appreciated.

    Vishal R Joshi said...

    Re: Database Publishing Wizard in VS 2010..

    I think you need to go View--> Windows --> Server Explorer within Visual Studio... There you will see your MDF file associated with your project... When you right click on that MDF file then you can see the Database Publishing Wizard...

    Although ideally I would recommend that you follow "DB Deployment" and "One Click Publish" articles at http://vishaljoshi.blogspot.com/2009/09/overview-post-for-web-deployment-in-vs.html instead of using Database Publishing Wizard...

    Hope things will work out for you, if not do not hesistate to ask...

    -Vishal