Database Deployment with Visual Studio 2010 .Net

Database Deployment with Visual Studio 2010 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: 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: 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: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… Destination Database Information Source 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: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: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…nce 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 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… will soon write a detailed post on how to use the new “Publishing” features of Visual Studio 2010… Hope this information was useful…