The approach taken when setting up a CopyStorm/SQL Server database may go a long way in reducing long-term ownership costs. There are two primary recommendations that should be followed when configuring a CopyStorm/SQL Server database.

The most common “mistake” made when setting up a SQL/Server database for CopyStorm is to use a Domain User along with a user specific schema for storing Salesforce data. For example,

1. The database administrator creates a database named “salesforce”.
2. User CAPSTORM\greg runs CopyStorm using integrated security (e.g. a domain user login) and creates all database tables in the [CAPSTORM\greg] schema.
3. User CAPSTORM\noah wants to use the CopyStorm database but quickly becomes irritated because:
   • His default schema is [CAPSTORM\noah].
   • All SQL table references have to include [CAPSTORM\greg].

Though a database administrator can get around this problem by making the default schema be [CAPSTORM\greg] for all CopyStorm database users, this is not a great idea. The core problem with this process is that the ownership of shared Salesforce backup data is tied to a real person — when that person wins the lottery or moves to a new position someone else will need to own the data.

Core Principle: The owner of a SQL/Server Salesforce database should not be a Windows Domain Account.

Capstorm considers the following to be the best practice for creating and populating a CopyStorm database in SQL/Server:

1. Create a new SQL/Server database.
   • The only purpose of this database is to hold CopyStorm created backups of Salesforce.
2. Create a non-domain SQL/Server user. Commonly used names include “salesforce”, “copystorm”, or “capstorm”.
   • Give this user ownership of the dbo schema in the CopyStorm database.
   • Make the default schema “dbo” in the CopyStorm database.
3. Always run CopyStorm using the non-domain user.
4. Create a group with read-only access to the salesforce database with a default schema of “dbo”.
5. When a domain user needs access to the SQL/Server Salesforce database, make them of a member of the group created in the previous step.

If your organization is smaller, the only essential best practice steps are 1-3.

If your organization chooses to use domain accounts for all SQL/Server authentication that’s OK, with the caveat that the CopyStorm database will require permission updates when the person tied to the domain account decides to retire.

Performance of CapStorm applications can be greatly impacted by the SQL Server query planner. By default, the “parameterization” parameter is set to “SIMPLE” – which can cause common CapStorm queries to not be fully optimized by the SQL Server query planner.

It is recommended that you set the “parameterization” parameter to “FORCED” – this will enable SQL Server to better optimize query plans for common CapStorm application queries.

You can find out if parameterization is forced by running this query:

• SELECT name, is_parameterization_forced FROM sys.databases WHERE name=DB_NAME()

And to enable forced parameterization, you can run this query:

• ALTER DATABASE CURRENT SET PARAMETERIZATION FORCED