Microsoft SQL/Server Database Setup

The approach taken when setting up a CopyStorm/SQL Server database may go a long way in reducing long-term ownership costs.

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.