Install database schema

Optimizely Content Management System (CMS) uses Microsoft SQL Server to store content and other information created in the system. CMS supports database editions, including SQL Azure. See the System requirements for Optimizely for specific versions of SQL Server that CMS supports.

Install from the Visual Studio integration

A database schema is a way to logically group objects such as tables, views, and stored procedures in the database. When you create a new site from the Visual Studio integration, the database with the CMS schema is automatically created. Each version of the database schema targets a specific version of the product. The following topics show how to deploy a database from development to production.

• Deploy to Azure Web Apps
• Deploy to Windows Servers

Install on an empty database

If you need to install a database schema on an empty database, you can use the Initialize-EPiDatabase cmdlet from the Package Manager in Visual Studio. This cmdlet installs the database schemas included in the installed NuGet packages.

Enable automatic creation of database schemas

You can also configure a site to create the database schema automatically during site initialization. When the site starts, Optimizely determines whether you installed the schema in the current database as specified by the connection string. If you did not install the schema and enabled automatic creation, it executes an SQL script that adds the schema to the database.

To enable automatic schema creation, add the attribute createDatabaseSchema="true" to the episerver.framework element in configuration as: <episerver.framework createDatabaseSchema="true">

Creating the automatic schema uses the same process as schema updates, and it lets you interact with the schema creation process by registering an implementation of IDatabaseSchemaValidator in the Optimizely IOC container. See Automatic schema updates for information and examples.

Create database manually

If you want to deploy the schema manually, you can find the SQL script files in the EPiServer.CMS.Core NuGet package under the tools subfolder.

Install optional database schemas

Suppose you configured the web application to use Windows Workflow Foundation or SQL Membership provider module. In that case, you must manually deploy the Windows Workflow Foundation and the SQL Membership provider database schema. The Initialize-EPiDatabase cmdlet does not install these.

Best practices when you set up the database

Consider the following recommendations:

• When upgrading, you should never directly access the tables or stored procedures in the database or create custom tables that interfere with the tables created by CMS. You should always use the documented API instead because backward compatibility on the database level is not guaranteed.
• CMS supports several SQL Server high-availability options for the database's availability and performance, including fail-over clustering and database mirroring. For instance, database mirroring retrieves a "hot" standby database that operates in read-only mode, and transactions are copied to the mirror synchronously or asynchronously. You can configure instant fail-over using a "witness" server.
• When you run an SQL Server in production, create a maintenance plan for the database, which includes backup, checking database consistency, and rebuilding indexes. Suppose you do not create a maintenance plan. In that case, the database's performance will degrade over time, especially if you never rebuild indexes because the query speed degrades as you add content. For information, see Maintenance Plans (Microsoft).