Home/Web Forms/Tutorials/Chapter 9. Deployment/Deployment to a Hosting Provider/Visual Studio Web Deployment - Migrating to SQL Server

Overview

The Deploying SQL Server Compact and Deploying a Database Update tutorials explained some of the reasons why you might eventually want to upgrade from SQL Server Compact to full SQL Server. This tutorial shows you how to do that.

SQL Server Express versus full SQL Server for Development

Once you've decided to upgrade to full SQL Server, you'll want to use SQL Server Express or full SQL Server in your development and test environments. In addition to the differences in tool support and in database engine features, there are differences in provider implementations between SQL Server Compact and other versions of SQL Server. These differences can cause the same code to generate different results.

Typically, you choose SQL Server Express for your development and test environment (when the test environment is on your local computer) because SQL Server Express is free and is installed with Visual Studio by default. Unlike SQL Server Compact, SQL Server Express is essentially the same database engine and uses the same .NET provider as full SQL Server. When you test with SQL Server Express, you can be confident of getting the same results as you will with full SQL Server in production. You can use most of the same database tools with SQL Server Express that you can use with full SQL Server (a notable exception being SQL Server Profiler), and it supports other features of full SQL Server like stored procedures, views, triggers, and replication. (You typically need to use full SQL Server in production, however. SQL Server Express can run in a shared hosting environment, but it was not designed for that, and many hosting providers do not support it.)

Combining Databases versus Keeping Them Separate

The Contoso University application has two SQL Server Compact databases: the membership database (aspnet.sdf) and the application database (School.sdf). When you migrate, you can migrate these to two separate databases or to a single database. You might want to combine them in order to facilitate database joins between your application database and your membership database. Your hosting plan might also provide a reason to combine them. For example, the hosting provider might charge more for multiple databases or might not even allow more than one database. That's the case with the Cytanium Lite hosting account that's used for this tutorial, which allows only a single SQL Server database.

In this tutorial, you'll migrate your two databases this way:

• Migrate to two SQL Server Express databases in the development and test environments.
• Migrate to one combined full SQL Server database in the production environment.

Reminder: If you get an error message or something doesn't work as you go through the tutorial, be sure to check the troubleshooting page.

Creating SQL Server Express Databases for the Test Environment

Before you can deploy to SQL Server Express databases in the test environment, you have to manually create the databases. You won't have to manually create tables or insert data into them; Web Deploy will do that for you automatically.

From the View menu select Server Explorer, then right-click Data Connections and select Create New SQL Server Database.

In the Create New SQL Server Database dialog box, enter ".\SQLExpress" in the Server name box and "aspnetTest" in the New database name box, then click OK.

Follow the same procedure to create a new SQL Server Express School database named "SchoolTest".

(You're appending "Test" to these database names because later you'll create an additional instance of each database for the development environment, and you need to be able to differentiate the two sets of databases.)

Server Explorer now shows the two new databases.

Creating Grant Scripts for the New Databases

When the application runs in IIS on your development computer, the database will be accessed using the default application pool's credentials. However, by default, the application pool identity does not have permission to open the databases. So you need to run a script to grant that permission. In this section you create the script that you'll run later to make sure that the application can open the databases when it runs in IIS.

In the solution's SolutionFiles folder that you created in the Deploying to the Production Environment tutorial, create a new SQL file named Grant.sql. Copy the following SQL commands into the file, and then save and close the file:

IF NOT EXISTS (SELECT name FROM sys.server_principals WHERE name = 'IIS APPPOOL\DefaultAppPool')
BEGIN
    CREATE LOGIN [IIS APPPOOL\DefaultAppPool] 
      FROM WINDOWS WITH DEFAULT_DATABASE=[master], 
      DEFAULT_LANGUAGE=[us_english]
END
GO
CREATE USER [ContosoUniversityUser] 
  FOR LOGIN [IIS APPPOOL\DefaultAppPool]
GO
EXEC sp_addrolemember 'db_datareader', 'ContosoUniversityUser'
GO
EXEC sp_addrolemember 'db_datawriter', 'ContosoUniversityUser'
GO

Configuring Database Deployment for the Test Environment

Currently your deployment settings are designed to handle database deployment that involves just copying files in the App_Data folder. Now you need to configure deployment to create SQL scripts and run them in the destination database. Essentially, what Web Deploy will do for you is the following for each database that you are deploying:

• Generate a SQL script that creates the source database's structure (tables, columns, constraints, etc.) in the destination database.
• Generate a SQL script that inserts the source database's data into the tables in the destination database.
• Run the scripts in the destination database.

In order to begin configuring settings for deployment to the test environment, open the Project Properties window and select the Package/Publish Web tab. Then select Active (Test) or Test in the Configuration drop-down list.

Make sure that Exclude files from the App_Data folder is selected. Web Deploy will read the data in the .sdf files in App_Data in order to create tables and data in the new SQL Server Express databases, but it should not copy the files to the destination.

Make sure that Include all databases configured in Package/Publish SQL tab is selected. The Package/Publish SQL tab is where you'll configure deployment to the new SQL Server Express databases. Until now this check box has been selected, but it has had no effect because you didn't specify any databases on the Package/Publish SQL tab.

Select the Package/Publish SQL tab, and with the build configuration still set to Test, click Import from Web.config.

Visual Studio looks for connection strings in the Web.config file, finds one for the membership database and one for the School database, and adds a row corresponding to each connection string in the Database Entries table. The connection strings it finds are for the existing SQL Server Compact databases, and your next step will be to configure how and where to deploy these databases. You enter database deployment settings in the Database Entry Details section below the Database Entries table. The settings shown in the Database Entry Details section pertain to whichever row in the Database Entries table is selected. (The changes you need to make will be detailed below. At this point it's just important to understand the master-detail relationship between the Database Entries and Database Entry Details sections.)

Configuring Deployment Settings for the Membership Database

Select the DefaultConnection-Deployment row in the Database Entries table in order to configure settings that apply to the membership database.

In Connection string for destination database, you need to enter a connection string that points to the new SQL Server Express membership database. You can get the connection string you need from Server Explorer. In Server Explorer, expand Data Connections and select the aspnetTest database, then from the Properties window copy the Connection String value.

The connection string is also shown below. Copy this connection string into Connection string for destination database in the Package/Publish SQL tab.

Data Source=.\SQLExpress;Initial Catalog=aspnetTest;Integrated Security=True;Pooling=False

Make sure that Pull data and/or schema from an existing database is selected. This is what causes SQL scripts to be automatically generated and run in the destination database.

The Connection string for the source database value is extracted from the Web.config file and points to the development SQL Server Compact database. This is the source database that will be used to generate the scripts that will run later in the destination database. Since you want to deploy the production version of the database, change "aspnet-Dev.sdf" to "aspnet-Prod.sdf".

Change Database scripting options from Schema Only to Schema and data, since you want to copy your data (test accounts) as well as the database structure.

To configure deployment to run the grant scripts that you created, you need to add them to the Database Scripts section. Click Add Script, and in the Select File dialog box, navigate to the folder where you stored the grant script (this is the folder that contains your solution file). Select the file named Grant.sql, and click Open.

The settings for the DefaultConnection-Deployment row in Database Entries now look like this:

Configuring Deployment Settings for the School Database

Next, select the SchoolContext-Deployment row in the Database Entries table in order to configure the School database deployment settings.

You can use the same method you used earlier to get the connection string for the new SQL Server Express database. The connection string is shown below. Copy this connection string into Connection string for destination database in the Package/Publish SQL tab.

Data Source=.\SQLExpress;Initial Catalog=SchoolTest;Integrated Security=True;Pooling=False

Make sure that Pull data and/or schema from an existing database is selected.

The Connection string for the source database value is extracted from the Web.config file and points to the development SQL Server Compact database. Change "School-dev.sdf" to "School-Prod.sdf" to deploy the production version of the database.

Change Database scripting options to Schema and data.

You also want to run the script to grant read and write permission for this database to the application pool identity, so add the Grant.sql script file as you did for the membership database.

When you're done, the settings for the SchoolContext-Deployment row in Database Entries look like this:

Save the changes to the Package/Publish SQL tab.

Specifying Transacted Mode for the Grant Script

The deployment process generates scripts that deploy the database schema and data. By default, these scripts run in a transaction. However, custom scripts (like the grant scripts) by default do not run in a transaction. If the deployment process mixes transaction modes, you might get a timeout error when the scripts run during deployment. In this section, you'll edit the project file in order to configure the custom scripts to run in a transaction.

In Solution Explorer, right-click the ContosoUniversity project and select Unload Project.

Then right-click the project again and select Edit ContosoUniversity.csproj.

The Visual Studio editor shows you the XML content of the project file. Notice that there are several PropertyGroup elements. (In the image, the contents of the PropertyGroup elements have been omitted.)

The first one, which has no Condition attribute, is for settings that apply regardless of build configuration. One PropertyGroup element applies only to the Debug build configuration (note the Condition attribute), one applies only to the Release build configuration, and one applies only to the Test build configuration. Within the PropertyGroup element for the Test build configuration, you'll see a PublishDatabaseSettings element that contains the settings you entered on the Package/Publish SQL tab:

You'll notice there is an Object element that corresponds to each of the grant scripts you specified (notice the two instances of "Grant.sql"). In the Object elements for the grant scripts, change the value of the Transacted attribute of the Source element to True. The PublishDatabaseSettings element now looks like this:

Save and close the project file, and then right-click the project in Solution Explorer and select Reload Project.

Setting up Web.Config Transforms for the Connection Strings to Test Databases

The connection strings for the new SQL Express databases that you entered on the Package/Publish SQL tab are used by Web Deploy only for updating the destination database during deployment. You still have to set up Web.config transformations so that the connection strings in the deployed Web.config file point to the new SQL Server Express databases.

Open Web.Test.config and replace the connectionStrings element with the connectionStrings element shown below:

<configuration xmlns:xdt="http://schemas.microsoft.com/XML-Document-Transform">
  <connectionStrings>
    <add name="DefaultConnection" 
         connectionString="Data Source=.\SQLExpress;Initial Catalog=aspnetTest;Integrated Security=True;Pooling=False;MultipleActiveResultSets=True" 
         providerName="System.Data.SqlClient"
         xdt:Transform="SetAttributes" xdt:Locator="Match(name)"/>
    <add name="SchoolContext" 
         connectionString="Data Source=.\SQLExpress;Initial Catalog=SchoolTest;Integrated Security=True;Pooling=False;MultipleActiveResultSets=True" 
         providerName="System.Data.SqlClient"
         xdt:Transform="SetAttributes" xdt:Locator="Match(name)"/>
  </connectionStrings>
  <!-- appSettings element, comments, and system.web element -->
</configuration>

This code causes the connectionString and providerName attributes of each add element to be replaced in the deployed Web.config file. These connection strings are not identical to the ones you entered in the Package/Publish SQL tab — the setting "MultipleActiveResultSets=True" has been added to them because it's required for the Entity Framework and the Universal Providers.

Deploying to the Test Environment

You are now ready to deploy to the test environment.

In the Solution Configurations drop-down box, select the Test build configuration, and in the Publish profile drop-down box, select Test. Click Publish Web. (If you can't do this in the toolbars because you have have customized toolbars, see the Deploying a Code-Only Update tutorial for an alternative method.)

Visual Studio deploys the updated application and reports success in the Output window.

You can now open a browser and run http://localhost/ContosoUniversity to test the application. Run the Add Students page, add a new student, then view the new student in the Students page. This verifies that the School database was deployed and that that you have read and write access to it. Select the Update Credits page (you'll need to log in) to verify that the membership database was deployed and you have access to it.

Creating a SQL Server Database for the Production Environment

Now that you've deployed to the test environment, you're ready to set up deployment to production. You begin as you did for the test environment, by creating a database to deploy to. As you recall from the Overview, the Cytanium Lite hosting plan only allows a single SQL Server database, so you will set up only one database, not two. All of the tables and data from the membership and School SQL Server Compact databases will be deployed into one SQL Server database in production.

Go to the Cytanium control panel at http://panel.cytanium.com. Hold the mouse over Databases and then click SQL Server 2008.

In the SQL Server 2008 page, click Create Database.

Name the database "School" and click Save. (The page automatically adds the prefix "contosou", so the effective name will be "contosouSchool".)

On the same page, click Create User. On Cytanium's servers, rather than using integrated Windows security and letting the application pool identity open your database, you'll create a user that has authority to open your database. You'll add the user's credentials to the connection strings that go in the production Web.config file. In this step you create those credentials.

Fill in the required fields in the SQL User Properties page:

• Enter "ContosoUniversityUser" as the name.
• Enter a password. This tutorial will assume you have entered Pas$w0rd.
• Select contosouSchool as the default database.
• Select the contosouSchool check box.

Configuring Database Deployment for the Production Environment

Now you're ready to set up database deployment settings in the Package/Publish Web and Package/Publish SQL tabs, as you did earlier for the test environment.

Open the Project Properties window, select the Package/Publish Web tab, and select Active (Release) or Release in the Configuration drop-down list.

Select Exclude files from the App_Data folder. Database deployment to SQL Server does not involve copying files in the App_Data folder, it involves generating and running scripts in the destination database.

Make sure that Include all databases configured in Package/Publish SQL tab is selected.

Select the Package/Publish SQL tab. With the build configuration still set to Release, click Import from Web.config as you did earlier for the Test build configuration. The same two rows are added to the Database Entries table, one for each of the existing SQL Server Compact databases.

When you configure deployment settings for each database, the key difference between what you are doing for production and test environments is that for the test environment you entered different destination database connection strings, but for the production environment the destination connection string will be the same for both source databases. This is because you are deploying both databases to one database in production.

Configuring Deployment Settings for the Membership Database

To configure settings that apply to the membership database, select the DefaultConnection-Deployment row in the Database Entries table.

In Connection string for destination database, enter a connection string that points to the new production SQL Server database that you just created. You can get the connection string from your welcome email. The relevant part of the email contains the following sample connection string:

Data Source=vserver01.cytanium.com;Initial Catalog={myDataBase};User Id={myUsername};Password={myPassword};

After you replace the three variables, the connection string you need is this:

Data Source=vserver01.cytanium.com;Initial Catalog=contosouSchool;User Id=ContosoUniversityUser;Password=Pas$w0rd;

Copy this connection string into Connection string for destination database in the Package/Publish SQL tab.

Make sure that Pull data and/or schema from an existing database is selected.

The Connection string for the source database value is extracted from the Web.config file and points to the development SQL Server Compact database in the App_Data folder. Change "aspnet-Dev.sdf" to "aspnet-Prod.sdf".

Change Database scripting options to Schema and data.

When you're done, the settings for the DefaultConnection-Deployment row in Database Entries look like this:

Configuring Deployment Settings for the School Database

Next, select the SchoolContext-Deployment row in the Database Entries table in order to configure the School database settings.

Copy the same connection string into Connection string for destination database that you copied into that field for the membership database.

Make sure that Pull data and/or schema from an existing database is selected. The Connection string for the source database value is extracted from the Web.config file and points to the development database. Change "School-Dev.sdf" to "School-Prod.sdf".

Change Database scripting options to Schema and data.

When you're done, the settings for the SchoolContext-Deployment row in Database Entries look like this:

Save the changes to the Package/Publish SQL tab.

Setting Up Web.Config Transforms for the Connection Strings to Production Databases

Next, you'll set up Web.config transformations so that the connection strings in the deployed Web.onfig file point to the new production database. The connection string that you entered on the Package/Publish SQL tab for Web Deploy to use is the same as the one the application needs to use, except for the addition of the MultipleResultSets option.

Open Web.Release.config and replace the connectionStrings element with the connectionStrings element shown below:

<configuration xmlns:xdt="http://schemas.microsoft.com/XML-Document-Transform">
  <connectionStrings>
    <add name="DefaultConnection" 
         connectionString="Data Source=vserver01.cytanium.com;Initial Catalog=contosouSchool;User Id=ContosoUniversityUser;Password=Pas$w0rd;MultipleActiveResultSets=True" 
         providerName="System.Data.SqlClient"
         xdt:Transform="SetAttributes" xdt:Locator="Match(name)"/>
    <add name="SchoolContext" 
         connectionString="Data Source=vserver01.cytanium.com;Initial Catalog=contosouSchool;User Id=ContosoUniversityUser;Password=Pas$w0rd;MultipleActiveResultSets=True" 
         providerName="System.Data.SqlClient"
         xdt:Transform="SetAttributes" xdt:Locator="Match(name)"/>
  </connectionStrings>
  <!-- appSettings element, comments, and system.web element -->
</configuration> 

You will sometimes see advice that tells you to always encrypt connection strings in the Web.config file. This might be appropriate if you were deploying to servers on your own company's network. When you are deploying to a shared hosting environment, though, you're trusting the security practices of the hosting provider, and it's neither necessary nor practical to encrypt the connection strings.

Deploying to the Production Environment

Now you're ready to deploy to production, which will read the SQL Server Compact databases in your project's App_Data folder and re-create all of their tables and data in the production SQL Server database.

Before you deploy, upload app_offline.htm, as shown in the Deploying to the Production Environment tutorial.

In a real application in which the database was being updated in production, you would then use the File Manager feature of the Cytanium control panel to copy the aspnet-Prod.sdf and School-Prod.sdf files from the production site to the App_Data folder of the ContosoUniversity project. This would ensure that the data you're deploying to the new SQL Server database includes the latest updates made by your production website.

In the Solution Configurations drop-down box, select Release, and in the Publish profile drop-down box select Production. Click Publish Web. (If you can't do this in the toolbars because you have have customized toolbars, see the Deploying a Code-Only Update tutorial for an alternative method.)

Visual Studio deploys the updated application and reports success in the Output window.

Before you test the site, use the File Manager in the Cytanium control panel to delete app_offline.htm. You can also at the same time delete the .sdf files from the App_Data folder.

You can now open a browser and go to the URL of your public site to test the application. Run the Add Students page, add a new student, then view the new student in the Students page. This verifies that the School database was deployed and that you have read and write access to it. Select the Update Credits page (which requires you to log in) to verify that the membership database was deployed and you have access to it.

Switching to SQL Server Express in Development

As was explained in the Overview, it's generally best to use the same database engine in development that you use in test and production. (Remember that the advantage to using SQL Server Express in development is that the database will work the same in your development, test, and production environments.) In this section you'll set up the ContosoUniversity project to use SQL Server Express.

The simplest way to do that is to let Code First and the membership system create both new development databases for you:

• Change the connection strings to specify new SQL Express databases.
• Run the application, and Code First automatically creates and seeds the application database.
• Click Logon and then Register to register whatever user accounts you need for testing, and the ASP.NET membership system automatically creates and iniitializes the membership database.

However, if you have a large number of test user accounts and you don't want to manually re-enter them, you can use the deployment process to migrate that data from SQL Server Compact to SQL Server Express. If you prefer to do that, follow the instructions that start in the next section. If you do not need to see how to do that, skip the following sections until you get to the Updating Connection Strings in the Web.config file section.

Creating a Development Membership Database

From the View menu, select Server Explorer, then right-click Data Connections and select Create New SQL Server Database.

In the Create New SQL Server Database dialog box, enter ".\SQLExpress" in the Server name box and "aspnetDev" in the New database name box. Click OK.

Configuring Database Deployment

You'll need to configure deployment settings in order to set up deployment to the new development membership database, but you don't want to change your existing settings for the test environment. To create new settings that you can use without affecting the existing ones, create a new build configuration named "MigrateToSQLExpress" based on the existing Test build configuration.

From the Visual Studio Build menu, select Configuration Manager to display the Configuration Manager dialog box.

In the Active solution configuration box, select New. When the New Solution Configuration dialog box appears, enter "MigrateToSQLExpress" as the name of the new build configuration, and copy settings from Test. Leave Create new project configurations selected, and click OK.

Open the Project Properties window and select the Package/Publish Web tab. With Active (MigrateToSQLExpress) in the Configuration drop-down list, select Exclude files from the App_Data folder and make sure Include all databases configured in Package/Publish SQL tab is selected.

Select the Package/Publish SQL tab. With the build configuration set to Active(MigrateToSQLExpress), click Import from Web.config.

In the Database Entries table, clear the check box next to SchoolContext-Deployment (since you don't need to deploy to the School database), then select DefaultConnection-Deployment to configure membership database settings.

In Connection string for destination database, enter the connection string shown below. This points to the new SQL Server Express membership database that you just created for your development environment.

Data Source=.\SQLExpress;Initial Catalog=aspnetDev;Integrated Security=True;Pooling=False;

Make sure that Pull data and/or schema from an existing database is selected.

The Connection string for the source database value is extracted from the Web.config file and points to your development SQL Server Compact membership database.

Change Database scripting options from Schema Only to Schema and Data.

Save the changes to the Package/Publish SQL tab.

Deploying to the Test Environment

In the Solution Configurations drop-down box, select MigrateToSQLExpress build configuration. In the Publish profile drop-down box, select Test. Click Publish Web. (If you can't do this in the toolbars because you have have customized toolbars, see the Deploying a Code-Only Update tutorial for an alternative method.)

Visual Studio deploys the updated application and reports success in the Output window.

Updating Connection Strings in the Web.config file

Open the Web.config file and replace the connectionStrings element with the following code:

<connectionStrings>
  <add name="DefaultConnection" connectionString="Data Source=localhost\SQLExpress;Initial Catalog=aspnetDev;Integrated Security=True;Pooling=False;MultipleActiveResultSets=True;" providerName="System.Data.SqlClient" />
  <add name="SchoolContext" connectionString="Data Source=localhost\SQLExpress;Initial Catalog=SchoolDev;Integrated Security=True;Pooling=False;MultipleActiveResultSets=True;" providerName="System.Data.SqlClient" />
</connectionStrings>

Run the site by pressing Control-F5. As you did for the test and production environments, run the Add Students page, add a new student, then view the new student in the Students page. This verifies that the School database was created and initialized and that you have read and write access to it.

If you followed the instructions above to use the deployment process to migrate your user accounts, select the Update Credits page and log in to verify that the membership database was deployed and that you have access to it. If you did not migrate your user accounts, create an administrator account and then select the Update Credits page to verify that it works.

Cleaning Up SQL Server Compact Files

You no longer need files and NuGet packages that were included to support SQL Server Compact. If you want (this step is not required), you can clean up unneeded files and references.

In Solution Explorer, delete the .sdf files from the App_Data folder and the amd64 and x86 folders from the bin folder.

In Solution Explorer, right-click the ContosoUniversity project and select Add Library Package Reference.

In the Add Library Package Reference dialog box, select the EntityFramework.SqlServerCompact package and click Uninstall.

When uninstallation is complete, do the same for the and SqlServerCompact package. (The packages must be uninstalled in this order because the EntityFramework.SqlServerCompact package depends on the SqlServerCompact package.)

Follow the same procedure to remove the same two packages from the ContosoUniversity.DAL project.

Finally, you can delete the MigrateToSqlExpress Build configuration, since you created it only for the migration. Select Configuration Manager from the Build menu, and then in the Configuration Manager dialog box select Edit in the Active solution configuration drop-down box. In the Edit Solution Configurations dialog box, select MigrateToSQLExpress and click Remove.

You have now successfully migrated to SQL Server Express and full SQL Server. In the next tutorial you'll make another database change, and you'll see how to deploy database changes when your test and production databases use SQL Server Express and full SQL Server.

1. ← PreviousVisual Studio Web Deployment - Deploying a Database Update
2.  You're ViewingVisual Studio Web Deployment - Migrating to SQL Server
3. → NextVisual Studio Web Deployment - Deploying a SQL Server Database Update