When you prepare to deploy a Web application project that uses one or more SQL Server databases, you can enter settings that specify database scripts that must run during deployment. These settings apply whether you deploy by using one-click publish or by using a Web deployment package.
The first two procedures in this topic provide instructions for specifying database information to deploy when the database does not already exist in the destination environment. The remaining procedures explain how to change the settings after the application has already been deployed. After you deploy an application the first time, the database will already be on the destination server. You might not want to run database scripts, or you might want to run different scripts.
Configuring for First-Time Deployment
You use the Package/Publish SQL tab of the project Properties page to configure settings that determine which scripts will run during deployment. This tab is organized in a master/detail layout. In a grid at the top, you specify databases to deploy. In the rest of the tab, you specify settings that apply to the database you have selected in the grid.
To specify databases to deploy
1. In Solution Explorer, select the project name and then select Properties.
2. In the Properties page, click the Package/Publish SQL tab.
3. In the Configuration drop-down list, select the build configuration that you want to deploy.
You can configure deployment for the Debug or Release build configurations, or for a custom build configuration that you have created by using the Configuration Manager dialog box. For example, you might want different settings for deploying to a test server and for deploying to a production server.
The default value for the Configuration list is Active. This means that the settings on this tab will apply to the build configuration that is currently selected for the project. The currently selected build configuration is shown in the main toolbar and in the Configuration Manager dialog box. (To open Configuration Manager, select it from the Build menu.)
4. Click Import from Web.config.
A row is created in the grid for each connection string that is found in the Web.config file. By default, "-Deployment" is appended to the connection string name.
5. If the Web application uses databases that are not represented by connection strings in the Web.config file, manually add a row for each of those databases. For rows that you enter manually, you can use any name that helps you remember which database the row applies to.
After you have specified the databases to deploy, you can enter settings for each database. Perform the following steps for each database that you entered in the Database Entries grid.
To enter settings for a database
1. In the Database Entries grid, select the row that you want to enter settings for.
The values that you enter in the Database Entry Details section apply only to the currently selected row in the Database Entries grid. If no rows are selected in the Database Entries grid, the Database Entry Details section is disabled.
2. In the Connection string for destination database box, enter the connection string to use during deployment.
This connection string might be different from the one that you use in the deployed Web.config file for the same database. For example, the connection string to use for deployment might need administrator permissions that you do not want the connection to have when users run the application.
3. If you want to automatically generate scripts from an existing database, select the Pull data and/or schema from an existing database check box.
By default, this check box is selected for rows in the Database Entries grid that were imported from the Web.config file. When this option is selected, the Database Scripts grid includes a row to indicate that automatically generated scripts will run in the destination database.
4. If you selected the Pull data and/or schema from an existing database check box, perform the following steps:
- In the Connection string for the source database text box, enter the connection string to use during deployment.
By default, for rows in the Database Entries grid that were imported from the Web.config file, this field is initialized to the value from the Web.config file.
- In the Database scripting options drop-down list, specify whether you want the scripts that are automatically generated for this database to duplicate only the database structures (Schema Only), to duplicate both the structures and the data (Schema and Data), or to duplicate only the data (Data Only) in the destination environment.
5. If you want to specify custom database scripts to run in the destination database during deployment, add them to the Database Scripts grid by using the Add Script button.
When you click Add Script, a Select File dialog box is displayed. This dialog box enables you to select a script that you have written and to add it to the Database Scripts grid.
6. If the rows in the Database Scripts grid are not in the order in which the scripts should run, rearrange the rows by using the up arrow and down arrow buttons.
7. In the Web.config transform file for the build configuration that you are deploying, add markup that will transform the connection string for this database to the value that it should have in the deployed Web application.
8. If you do not want the automatically generated script to run in a transaction, edit the project file to set the Transacted attribute of the script's Source element to False.
9. If the source or destination database is SQL Azure and if you are running an automatically generated script, edit the script's PreSource element in the project file so that it includes the following attributes:
targetDatabaseEngineType=SQLAzureDatabase
targetServerVersion=version100
prefetchObjects=false
Set the targetServerVersion to version100 for SQL 2008 or version105 for SQL 2008 R2.
Redeploying Without Database Changes
After you have deployed a Web application the first time, you might have to deploy it again. The following procedure describes how to disable database updates for all databases or for selected databases when you redeploy the Web application.
To redeploy without database changes
1. In Solution Explorer, right-click the project name and then click Properties.
2. Click the Package/Publish Web tab.
3. Make sure that the build configuration that you want to change settings for is selected in the Configuration drop-down list.
4. If you want to disable updating for all databases that are listed in the Database Entries grid, clear the Include all databases configured in Package/Publish SQL tab check box.
5. If you want to disable updating for selected databases that are listed in the Database Entries grid, clear the Deploy check box for each database that you do not want to run scripts for.
Redeploying By Using Automatically Generated Scripts
After you have deployed a database by using automatically generated scripts, you might make database changes and then redeploy the database. In that case, the deployment process must drop existing database objects before the objects are re-created. The following procedure describes how to specify that automatically generated scripts should include SQL Drop statements.
To redeploy a database by using automatically generated scripts
If you want the automatically generated script to drop database objects such as tables, views, and stored procedures individually before it creates new versions of these objects, edit the project file to set the ScriptDropsFirst attribute of the PreSource element of the automatically generated script to True.
This setting causes a SQL Drop statement to be generated for each database object that is created from the source database. This Drop statement for a particular object will run just before the statement that creates that object. If an object occurs in the destination database but not in the source database (typically because it was dropped from the source database after the previous deployment), no Drop statement will be generated for it.
If you want to drop the entire database before re-creating individual database objects, set the DropDestinationDatabase attribute of the Source element to True.
Deploying Database Changes by Using Custom Scripts
After you have deployed a database, you might make database changes and then want to deploy only the changes to the destination database. This lets you preserve any data that might have been entered in the destination database. The following procedure describes how to disable automatically generated scripts and add custom scripts that update the database.
To deploy changes to a database and preserve data
1. Create custom database scripts that will make the changes that you want in the destination database.
2. In Solution Explorer, right-click the project name and then select Properties.
3. Click the Package/Publish SQL tab.
4. In the Database Scripts grid, clear the Include check boxes in the rows for scripts that should not run.
If you selected the Pull data and/or schema from an existing database check box, clear the Include check box for the automatically generated script.
5. Add the custom database scripts for the selected database by using the Add Script button.
When you click Add Script, a Select File dialog box is displayed that enables you to select a script that you have written and add it to the Database Scripts grid. By default, the Include check box will be selected.
6. Optionally, if the rows in the Database Scripts grid are not in the order in which the scripts should run, rearrange the rows by using the up arrow and down arrow buttons.
What is so SPECIAL on ASPHostDirectory.com ASP. Net 4 Hosting?
We know that finding a cheap, reliable web host is not a simple task so we’ve put all the information you need in one place to help you make your decision. At ASPHostDirectory, we pride ourselves in our commitment to our customers and want to make sure they have all the details they need before making that big decision.
We will work tirelessly to provide a refreshing and friendly level of customer service. We believe in creativity, innovation, and a competitive spirit in all that we do. We are sound, honest company who feels that business is more than just the bottom line. We consider every business opportunity a chance to engage and interact with our customers and our community. Neither our clients nor our employees are a commodity. They are part of our family.
The followings are the top 10 reasons you should trust your online business and hosting needs to us:
- FREE domain for Life - ASPHostDirectory gives you your own free domain name for life with our Professional Hosting Plan and 3 free domains with any of Reseller Hosting Plan! There’s no need to panic about renewing your domain as ASPHostDirectory will automatically do this for you to ensure you never lose the all important identity of your site
- 99,9% Uptime Guarantee - ASPHostDirectory promises it’s customers 99.9% network uptime! We are so concerned about uptime that we set up our own company to monitor people’s uptime for them called ASPHostDirectory Uptime
- 24/7-based Support - We never fall asleep and we run a service that is opening 24/7 a year. Even everyone is on holiday during Easter or Christmast/New Year, we are always behind our desk serving our customers
- Customer Tailored Support - if you compare our hosting plans to others you will see that we are offering a much better deal in every aspect; performance, disk quotas, bandwidth allocation, databases, security, control panel features, e-mail services, real-time stats, and service
- Money Back Guarantee - ASPHostDirectory offers a ‘no questions asked’ money back guarantee with all our plans for any cancellations made within the first 30 days of ordering. Our cancellation policy is very simple - if you cancel your account within 30 days of first signing up we will provide you with a full refund
- Experts in ASP. Net 4 Hosting - Given the scale of our environment, we have recruited and developed some of the best talent in the hosting technology that you are using. Our team is strong because of the experience and talents of the individuals who make up ASPHostDirectory
- Daily Backup Service - We realise that your website is very important to your business and hence, we never ever forget to create a daily backup. Your database and website are backup every night into a permanent remote tape drive to ensure that they are always safe and secure. The backup is always ready and available anytime you need it
- Easy Site Administration - With our powerful control panel, you can always administer most of your site features easily without even needing to contact for our Support Team. Additionally, you can also install more than 100 FREE applications directly via our Control Panel in 1 minute!
Happy Hosting!