Microsoft SQL Server is the most common database platform used for CM4D. The following examples demonstrate how to create SQL Server 2008 databases in SQL Server Management Studio. These are only simple examples and your local MS SQL DBA should always be consulted.
You should only run the Create Scripts against newly created databases. To upgrade a database to the required schema version for the CM4D application, please follow the instructions in the topic Upgrading a Database Schema. Both the Create and Upgrade Scripts are provided with your CM4D Installation.
Similar procedures may be followed for SQL Server 2005.
The scripts needed to create or upgrade an SQL Server database are included with the CM4D Enterprise and Client Installations. Browse to the Database Scripts\SQLServer folder in your CM4D install directory.
When you examine the contents of the scripts directory you will see a number of scripts each designated with a version number and the database type. Scripts with the prefix 'UpdateSite' are used to upgrade CM4D Site databases. The scripts without this prefix are used to upgrade the CM4D Datasource databases.
In addition to the Update Scripts, you will also notice additional scripts which are used to create a new empty database of each type. For instance, the Site Tables.sql, Site Procedures.sql, Site Grants.sql, and Site Indexes.sql scripts are used to create a clean empty CM4D Site database. Similarly the Tables.sql, Procedures.sql, Grants.sql, and Indexes.sql scripts are used to create clean empty CM4D Datasource databases.
1. Run Microsoft SQL Server Management Studio.
2. Connect to your database server using credentials that have administrative privileges (for example, sysadmin).
3. There are two types of CM4D databases:
a. CM4D Site database - for CM4D configuration information
b. CM4D database - for actual CM4D data; is either unmanaged, or managed by a Site database
4. If you will be using a Site database, continue on to the section Creating a Site Database.
5. If you will not be using a Site database, continue to the section Creating a CM4D Database.
1. Right-click on Databases in the tree and select New Database from the context menu.
a. In the New Database dialog:
i. Enter a Database name (e.g., Site).
ii. Set the Initial Size of the Rows Data file to 50 (MB).
b. Click OK.
2. Next, you will need to run the Create scripts for the Site database.
3. Site Tables are always created first.
4. Click the open file button (
).
a. Browse to the scripts for the Schema version that corresponds to the version of CM4D that you are running. By default, these files are located here: C:\Program Files\Applied Tech Systems\CM4D\Database Scripts
b. Select Site Tables.sql.
c. Click Open.
5. In the use line of the script:
a. Delete the two hypens (--) at the beginning of the line.
b. Change [Your Site DB] to the name of the database you are running the scripts on.
c. As you type in the name, a list of databases will appear, allowing you to select your database name.
6. Once you have the correct database name entered between the brackets, select the Execute button.
7. Check the Messages section for any errors.
a. If the scripts execute successfully, go to the next step.
b. If the scripts return errors, investigate the issue(s).
Review the topic Database Schema Reference to see if any of the error messages are acceptable.
8. Repeat the previous steps for the Site Procedures.sql, Site Grant.sql, and Site Indexes.sql scripts.
9. Once the Site database scripts have been run successfully, continue on to the next section to create the CM4D Databases that will be managed by the Site database you just created.
1. Right-click on Databases in the tree and select New Database from the context menu.
a. In the New Database dialog:
i. Enter the Database name (e.g., CM4D).
ii. Set the Initial Size of the Rows Data file to 500 (MB).
iii. Set the Initial Size of the Log file to 50 (MB).
b. Click OK.
2. Next, you will need to run the Create scripts for the CM4D database.
a. Click the open file button ().
b. Browse to the scripts for the Schema version that corresponds to the version of CM4D that you are running.
c. Select Tables.sql.
d. Click Open.
3. In the use line of the script:
a. Delete the two hypens (--) at the beginning of the line.
b. Change [Your CM4D DB] to the name of the database you are running the scripts on.
c. As you type in the name, a list of databases will appear, allowing you to select your database name.
4. Once you have the correct database name entered between the brackets, select the Execute button.
5. Check the Messages section for any errors.
a. If the scripts execute successfully, go to the next step.
b. If the scripts return errors, investigate the issue(s).
Review the topic Database Schema Reference to see if any of the error messages are acceptable.
6. Repeat the previous steps for the Procedures.sql, Grant.sql, and Indexes.sql scripts.
7. Once the CM4D database scripts have been run successfully, continue on to the next section to create the CM4D user login (if using SQL Authentication).
CM4D can use Windows and/or SQL Authentication when connecting to a SQL Server database, depending on your network policies. Outlined below is a procedure that creates a SQL login account to be used for SQL Server Authentication.
Be sure to run the Grant scripts on any databases that are using SQL Authentication.
1. Right-click on Security > Logins.
2. Select New Login... from the context menu.
a. Enter a Login name (e.g., cm4duser)
b. Select SQL Server authentication.
c. Enter a secure Password.
d. Confirm password.
e. Clear the Enforce password expiration check box.
f. For the Default database, select your new Site database (or CM4D database if you are not using Sites).
g. Select User Mapping.
h. Check the boxes next to your new database(s).
i. Make sure the CM4D user (cm4duser) has public access, both on the CM4D Site (Site) and CM4D (CM4D) databases.
j. Click OK.
Use the login (cm4duser) you just created when connecting to a Site database or connecting to an unmanaged database using SQL Authentication. The same SQL login account is used for both the Site database connection and the CM4D database connection.
Before running any update scripts, refer to the topic Upgrading a Database Schema for information on specific upgrade paths and requirements.
Starting with your current Site database Schema version, you must run each script sequentially until you reach the latest Schema version. If you are not using a Site database, skip to the next section on Updating a CM4D Database.
1. Click the open file button ().
2. Browse to the UpdateSite[ ].sql script for the schema one version higher than your current schema, and click Open.
3. In the use line of the script, change [Your SITE DB] to the name of the Site database you are running the scripts on.
4. Once you have the correct database name entered between the brackets, select the Execute button.
5. Check the Messages section for any errors.
a. If the scripts execute successfully, go to the next step.
b. If the scripts return errors, investigate the issue(s).
Review the topic Database Schema Reference to see if any of the error messages are acceptable.
If you are using SQL Authentication, re-run the Site Grant.sql script.
Starting with your current CM4D DataSource database Schema version, you must run each script sequentially until you reach the latest Schema version.
1. Click the open file button ().
2. Browse to the Update[ ].sql script for the schema one version higher than your current schema, and click Open.
3. In the use line of the script, change [Your CM4D DB] to the name of the CM4D database you are running the scripts on.
4. Once you have the correct database name entered between the brackets, select the Execute button.
5. Check the Messages section for any errors.
a. If the scripts execute successfully, go to the next step.
b. If the scripts return errors, investigate the issue(s).
If you are using SQL Authentication, re-run the Grant.sql script.