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,
- The database administrator creates a database named “salesforce”.
- User CAPSTORM\greg runs CopyStorm using integrated security (e.g. a domain user login) and creates all database tables in the [CAPSTORM\greg] schema.
- 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:
- Create a new SQL/Server database.
- The only purpose of this database is to hold CopyStorm created backups of Salesforce.
- 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.
- Always run CopyStorm using the non-domain user.
- Create a group with read-only access to the salesforce database with a default schema of “dbo”.
- 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.