> For the complete documentation index, see [llms.txt](https://learn.capstorm.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://learn.capstorm.com/best-practices/microsoft-sql-server-database-setup.md).

# 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. There are two primary recommendations that should be followed when configuring a CopyStorm/SQL Server database.

## Use a Service Account

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.

## Enable Forced Parameterization

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`