Sitefinity 4 Database Deployment

We previously took a walkthrough these installation scenarios:

• Installing Sitefinity 4 on IIS 7
• Installing Sitefinity 4 on IIS 6

These are helpful for users getting started on a fresh site for both development and live environments with little or no customizations.

However, there are users who prefer or are required to develop projects locally, then deploying the website to a staging or live server. For the most part, this process is identical to the previous installation instructions linked above.

The only difference lies in how the database is deployed and referenced in the published website. After installing your site on the webserver, instead of creating a new connection as outlined in the previous posts, we'll register the credentials to connect to an existing database.

The options available for deploying your database vary according to your environment, such as shared vs private hosting. Today we'll take a brief look at these options for publishing your Sitefinity database.

Deploying the Database

The most important step in deployment is publishing the actual database to your server. Depending on your hosting environment, you have several different options available.

Note: Be sure that the SQL Server version of your published server matches that of your development server.

Option 1: Attach Database

By far, the simplest option available is a simple attachment of the database file. This is especially simple for sites developed with the Sitefinity database in the App_Data folder.

The .mdf file in that folder is the database file to be attached.

Copy this file to the Data folder for your SQL Server instance, then attach the file using SQL Server Management Studio.

Shared Hosting Considerations

Most shared hosting providers will generally not provide users access via SQL Management Studio. However many will accept an MDF file (usually via FTP, email, or a support ticket attachment) and attach it for you.

Check with your hosting provider to see what options the provide for attaching .mdf files. If they do not offer this feature, or you do not have access to attach them yourself, read on for other deployment options.

Option 2: Copy Database

Another simple option for deploying your database is using the Copy Database tool available in SQL Server Management Studio. This tool copies a database between servers, provided that you have permissions to both the source and destination servers.

Note: this option requires the full version of SQL Server and is not available for SQL Express.

Option 3: Backup / Restore

SQL Management Studio provides an option to generate backups of attached databases, allowing you another option for easily transferring databases between servers. Generally for shared hosting, this is the most common deployment option.

If your local database is running attached to SQL Server, you can create a backup using the SQL Server Management Studio.

Copy this to the Backup folder of the destination SQL Server instance. Connect to that instance with SQL Server Management Studio and choose “Restore Backup” from the context menu, choosing the BAK file you created.

Shared Hosting Considerations

Different hosts provide different methods of restoring backups. Some may give you direct access via SQL Management Studio. Others may require you to upload or email a backup, then request restoration through a support ticket.

Most hosts, however, will provide you a web interface allowing you the ability to upload your own file and restore it yourself.

Check with your hosting provider to see what options the provide for restoring database backups.

Option 4: Scripting the Database

SQL Server also provides you with the ability to generate SQL scripts for your database and its content. Simply connect to the database and select that option from the menu.

Be sure to select the option to script all objects as well as the full data. You can then save the script to open on a remote machine, or open the query in SQL Management Studio so you can run it immediately.

Scripting Database in App_Data in Visual Studio

If you are connecting to the MDF file in the APP_DATA folder, it's not an easy process to open this in SQL Management Studio to run the script generation tool.

Fortunately, Visual Studio has this ability built in. Connect to your database by double-clicking the MDF file in the APP_DATA folder.

This opens your database in the Server Explorer. In the context menu for this database is an option to "Publish to Provider". Here you can generate a script file that you can open and run in SQL Management Studio.

Using SQLCMD for Large Scripts

If you have a large amount of data in your database, the script that is generated may be too large to open and run in SQL Server Management Studio, generating the System.OutOfMemoryException error. If this is the case, you can use the SQLCMD command-line tool to batch-execute the script line by line. Here is an example command to execute a script.

sqlcmd -S (local)\SQLEXPRESS -U dbuser -P password -i "C:\database.sql"

A complete explanation of the SQLCMD utility as well as all of the optional parameters is available on MSDN.

Set Connection Strings

Now that you've deployed your database, you need to modify your production configuration strings. These are located in the file /App_Data/Sitefinity/Configuration/DataConfig.config. Update this file with the connection string to your deployed database.

When you're done, you need to restart your website so it uses the new connection details. This can be done by simply re-saving the root web.config file.

You're Live!

Once your website is restarted, it will connect to your deployed database and you're done!

This guide attempted to cover a wide range of deployment scenarios. If you experience any issues, or have questions on a scenario that I didn't cover, please send feedback via the Sitefinity 4 Deployment discussion forum.