Creating a Microsoft SQL Server database

This topic describes how to set up, verify, and begin your work with a Microsoft SQL Server Database.

In this topic:

StarTeam and MSSQL

StarTeam Server stores everything except for file archives and server startup information in the database of your choice.

You can use the Microsoft Windows version of StarTeam Server with the supported Microsoft databases.

For detailed information on Microsoft SQL Server performance tuning, consult the Microsoft SQL Server documentation.

Back to top

Terminology

The terms "instance" and "database"used in this section are Microsoft terminology. When you install Microsoft SQL Server on a computer, you can install up to 16 instances of it. Each instance can manage a number of different databases. Each StarTeam Server configuration uses its own database. When you perform a typical installation of the StarTeam Server, you install one instance of Microsoft SQL Server Express.

Back to top

Logging onto databases

It is highly recommended that you use a dedicated user account to run or log onto the databases used with StarTeam Server configurations. System administrator accounts usually have unlimited privileges. Any anomalies or errors that occur while you are logged in as the system administrator may result in unrecoverable damage to databases and other databases managed by the same database server.

Back to top

Encoding

StarTeam Server sends data encoded as UTF-8. Microsoft SQL Server and Microsoft SQL Server Express do not support UTF-8 at the database level. They support nchar, nvarchar, and ntext to store fixed format Unicode data (UTF-16).

• UTF-8 is a variable length character set in which the characters can expand from one to six bytes depending on the language.
• UTF-16 is a fixed length encoding mechanism in which every character expands to two bytes. UTF-16 tends to use up more space than UTF-8 when applied to character sets in which one character always translates to one byte.

Back to top

Because of how StarTeam Server encodes data, non-English data is human-readable from clients, but not from Microsoft SQL Server and Microsoft SQL Server Express.

Back to top

Guidelines for data files and transaction logs

Based on the number of users, we suggest the following guidelines for data files and transaction logs. Your needs may be different from those shown in the following tables.

The following table lists the number of data files and their size based on the number of users.

Number of users	Number of data files	Size of each data file
Up to 15	3	50 MB
Between 15 and 50	3	300 MB
Between 51 and 100	5	300 MB
Between 101 and 300	7	500 MB
>300	7	800 MB

The following table lists the number of log files and their size based on the number of users.

Number of users	Number of log files	Size of each log file *
Up to 15	3	50 MB
Between 15 and 50	3	300 MB
Between 51 and 100	5	300 MB
Between 101 and 300	5	500 MB
>300	6	500 MB

* The transaction log file sizes are relevant only if the transaction log backup is performed frequently.

Transaction log backups are essential. After a transaction is backed up, Microsoft SQL Server and Microsoft SQL Server Express databases automatically truncate the inactive portion of the transaction log. This inactive portion contains completed transactions and is no longer used during the recovery process. The basic advantage comes with the fact that Microsoft SQL Server reuses this truncated, inactive space in the transaction log instead of allowing the transaction log to continue to grow and use more space. This is a huge plus from a performance standpoint.

Allowing files to grow automatically can cause fragmentation of those files if a large number of files share the same disk. Therefore, it is recommended that files or file groups be created on as many different available local physical disks as possible. Place objects that compete heavily for space in different file groups.

Back to top

General Instructions

If you will be using a Microsoft SQL Server database, you must complete the following tasks:

1. Install Microsoft SQL Server.

   Note: When you install a Microsoft SQL Server database instance, it defaults to the collation for the locale set for the operating system. This locale setting should be used so long as it is correct for your team. Otherwise, when you automatically create a database from StarTeam Server, you cannot provide a database name, user name, or password in your language.

2. Install StarTeam Server.

3. Create and start a StarTeam Server configuration.

   • If you want to automatically create the database, see Creating and starting a new server configuration.
   • Otherwise, see Creating a database manually.

   If you plan to use a supported version of Microsoft SQL Server, see Connecting to a Microsoft SQL Server database.

   Caution:
   • Never modify the database contents other than through the StarTeam Server client or the Server Administration Tool. Direct database manipulation is not supported.
   • Never modify vault files other than through the StarTeam Server client or the Server Administration Tool.

Back to top

Creating a server configuration (for an existing database)

The first time you start a new server configuration, StarTeam Server creates all tables in the database you specify.

This section explains how to create a StarTeam Server configuration using a previously created Microsoft SQL Server or Microsoft SQL Server Express database.

Database names must comply with the following:

• Begin with a letter.
• Contain letters and numbers only.
• Not contain spaces.
• Not be a SQL reserved word such as create, delete, if, then, else, or goto.

To create a server configuration using an existing database:

1. Start the Server Administration tool. Click Start > Programs > Micro Focus > StarTeam Server <version> > StarTeam Server. The Server Administration tool opens.
2. Click Server > New Configuration. The New Configuration dialog box opens.

3. Enter the new configuration data:

   1. Type the name of the configuration in the Configuration Name field. If you want the server configuration to have the same name as the database (a nice convention, especially if you have several server configurations), you must follow the database naming conventions explained at the beginning of this section.
   2. Type or click Browse to specify the Repository Path location to be used to store log files and other information. If the repository path that you enter does not exist, the application creates it for you. The Repository Path is also the location for the default hive.
   3. Select Microsoft SQL Server/SSE (the default) from the Database Type list.
   4. Uncheck the option to Create new StarTeam database, so that StarTeam Server will not automatically create the database for it.

   5. Create an initial hive for the Native-II vault by doing one of the following:

      Accept the default settings

      Leave the Default option selected and proceed to the next step. With the default settings, StarTeam Server: Creates an initial hive named DefaultHive. Creates subfolders on the repository path named Archives and Cache to be used by the DefaultHive. Stipulates that the maximum cache size is 20% of the space currently available on the drive on which the cache is located. Uses the default setting of 600 seconds (10 minutes) between cache cleanups. Uses the default setting of 95% for the storage threshold, the point at which this drive is considered full.

      Specify custom values

      Select the Custom option and change any of the hive settings.

   6. Click Next, and enter the Database Server name, Database name, Database login name, and password in the appropriate text boxes.
   7. Optionally, if you are using a port other than the default, check Edit Database Port and type the port number in the text field.
   8. Click Verify Connection to test the connection. If the connection fails, review and change your settings.
   9. Click Finish. This action re-displays the Server Administration tool, which shows your new server configuration as a child of the Local node.

      Note: In addition to creating the server configuration, StarTeam Server adds information about the new server configuration to your starteam-server-configs.xml file. For more information about this file, see the Server Administration Tool Help.

4. By default, all server configurations are set to use the TCP/IP endpoint (port) 49201. However, each server configuration on a given computer must have a unique endpoint so it is recommended that you edit the default endpoint. To change the endpoint:

   1. Select the server configuration.
   2. Click the Start with Override button (or click Actions > Start with Override from the main menu). The Start with Override dialog box opens.
   3. Enter the endpoint that you want to use in the TCP/IP Endpoint field, and click OK.
5. Be sure to configure your new server configuration (for information, see the Server Administration Tool Help) and plan a backup schedule for it.

Back to top

Creating and starting a new server configuration

The first time you start a new server configuration, StarTeam Server creates all tables in the database you specify.

This section explains how to create a StarTeam Server configuration and start it for the first time. It assumes that you want StarTeam Server to automatically create a Microsoft SQL Server or Microsoft SQL Server Express database. If that is not the case, see Creating a database manually.

Database names must comply with the following:

• Begin with a letter.
• Contain letters and numbers only.
• Not contain spaces.
• Not be a SQL reserved word such as create, delete, if, then, else, or goto.

To create a server configuration using an existing database:

1. Start the Server Administration tool. Click Start > Programs > Micro Focus > StarTeam Server <version> > StarTeam Server. The Server Administration tool opens.
2. Click Server > New Configuration. The New Configuration dialog box opens.

3. Enter the new configuration data:

   1. Type the name of the configuration in the Configuration Name field. If you want the server configuration to have the same name as the database (a nice convention, especially if you have several server configurations), you must follow the database naming conventions explained at the beginning of this section.
   2. Type or click Browse to specify the Repository Path location to be used to store log files and other information. If the repository path that you enter does not exist, the application creates it for you. The Repository Path is also the location for the default hive.
   3. Select Microsoft SQL Server/SSE (the default) from the Database Type list.
   4. Check the option to Create new StarTeam database, so that StarTeam Server automatically creates the database.

   5. Create an initial hive for the Native-II vault by doing one of the following:

      Accept the default settings

      Leave the Default option selected and proceed to the next step. With the default settings, StarTeam Server: Creates an initial hive named DefaultHive. Creates subfolders on the repository path named Archives and Cache to be used by the DefaultHive. Stipulates that the maximum cache size is 20% of the space currently available on the drive on which the cache is located. Uses the default setting of 600 seconds (10 minutes) between cache cleanups. Uses the default setting of 95% for the storage threshold, the point at which this drive is considered full.

      Specify custom values

      Select the Custom option and change any of the hive settings.

   6. Click Next.
4. Enter the server and database information:
5. Enter the information for creating the data files and transaction logs, and click Finish.

   Note: We recommend keeping the data files and transaction log files on different physical drives under their own disk controllers.

   The default settings are appropriate for your use if you have fewer than 15 users and expect to store 1GB or less data.

   If you are very familiar with Microsoft SQL Server and Microsoft SQL Server Express databases, you may choose to make some changes by first clearing the Use Default Configuration check box and then altering sizes and locations for data files and log files.

   Use at least 3 data files and at least 3 transaction log files when creating a database, because Microsoft SQL Server and Microsoft SQL Server Express databases use a proportional fill strategy. This way all the files tend to become full at about the same time.

   To avoid fragmentation, make the data files as large as possible, based on the maximum amount of data expected in the database.

   The Server Administration tool displays your new server configuration as a child of the Local node.

   Note: In addition to creating the server configuration, StarTeam Server adds information about the new server configuration to your starteam-server-configs.xml file. For more information about this file, see Server Administration Tool Help.

6. By default, all server configurations are set to use the TCP/IP endpoint (port) 49201. However, each server configuration on a given computer must have a unique endpoint so it is recommended that you edit the default endpoint. To change the endpoint:

   1. Select the server configuration.
   2. Click the Start with Override button (or click Actions > Start with Override from the main menu). The Start with Override dialog box opens.
   3. Enter the endpoint that you want to use in the TCP/IP Endpoint field, and click OK.
7. Be sure to configure your new server configuration (for information, see Server Administration Tool Help) and plan a backup schedule for it.

Back to top

Creating a database manually

Despite the fact that StarTeam Server has automated Microsoft SQL Server and Microsoft SQL Server Express database creation, you may prefer to create your own. This makes more sense for Microsoft SQL Server because there are good tools for database creation.

It is imperative that you use the directions in the following procedure.

Database names must comply with the following:

• Begin with a letter.
• Contain letters and numbers only.
• Not contain spaces.
• Not be a SQL reserved word such as create, delete, if, then, else, or goto.

To create your own Microsoft SQL Server or Microsoft SQL Server Express database:

1. Install Microsoft SQL Server or Microsoft SQL Server Express. If you plan to use a supported version of Microsoft SQL Server, be sure to review the section Connect to the database.
2. Install StarTeam Server. For details, see Installing StarTeam Server on Windows.

3. Create a StarTeam Server database. Contact your database administrator about the specifics. Be sure that:

   • The database is owned by a StarTeam user.
   • The default database for the StarTeam user is the StarTeam database.
   • The database will fit the expected growth patterns for storing your StarTeam data. For details, see Guidelines for data files and transaction logs
   • The name of the database follows the conventions explained earlier in this section.
   • To create or upgrade the StarTeam database, the Microsoft SQL Server instance must have one of the following collations:
     • Latin1_General_CI_AI
     • SQL_Latin1_General_CP1_CI_AS

     On a Japanese double-byte operating system, where the default collation for the Microsoft SQL Server installation is not supported by StarTeam , you must use a named instance with one of the supported collations.

     Note: Microsoft SQL Server's multi-instance feature supports the use of multiple instances in different locales on the same database server.

4. Create and start a StarTeam Server configuration as described in Creating a server configuration (for an existing database).

Back to top

Database collation

The physical storage of character strings in the supported versions of Microsoft SQL Server and Microsoft SQL Server Express databases is controlled by collations. A collation specifies the bit patterns that represent each character and the rules by which characters are sorted and compared.

Microsoft SQL Server supports objects that have different collations being stored in a single database. Separate Microsoft SQL Server collations can be specified down to the level of columns. Each column in a table can be assigned different collations.

In a computer, characters are represented by different patterns of bits being either ON or OFF. A program that uses one byte (eight bits) to store each character can represent up to 256 different characters. A program that uses two bytes (16 bits) can represent up to 65,536 characters.

Single-byte code pages are definitions of the characters mapped to each of the 256 bit patterns possible in a byte. Code pages define bit patterns for uppercase and lowercase characters, digits, symbols, and special characters such as !, @, #, or %. Each European language, such as German or Spanish, has its own single-byte code page. Although the bit patterns used to represent the Latin alphabet characters A through Z are the same for all the code pages, the bit patterns used to represent accented characters (such as é and á) vary from one code page to the next. If data is exchanged between computers running different code pages, all character data must be converted from the code page of the sending computer to the code page of the receiving computer. If the source data has extended characters that are not defined in the code page of the receiving computer, data is lost. When a database serves clients from many different countries, it is difficult to pick a code page for the database that contains all the extended characters required by all the client computers. Also, a lot of processing time is spent doing the constant conversions from one code page to another.

Single-byte character sets are also inadequate to store all the characters used by many languages. For example, some Asian languages have thousands of characters, so they must use two bytes per character. Double-byte character sets have been defined for these languages. Still, each of these languages have their own code page, and there are difficulties in transferring data from a computer running one double-byte code page to a computer running another.

For information about synchronizing collation settings with another Windows locale, see the following Microsoft site: http://msdn2.microsoft.com/en-us/library/aa176553.aspx.

What is the default collation?

By default, Microsoft SQL Server setup chooses the Windows collation that supports the Windows locale of the computer on which the instance of Microsoft SQL Server is being installed. If the computer is using the US English locale, the instance’s default collation is Latin1_General_CI_AS.

On a Japanese double-byte operating system, where the default collation for the Microsoft SQL Server installation is not supported by StarTeam, you must use a named instance with one of the following supported collations.

• Latin1_General_CI_AI
• SQL_Latin1_General_CP1_CI_AS

The multi-instance feature of supported Microsoft SQL Server versions supports the use of multiple instances in different locales on the same database server.

Back to top

Configure encrypted connections

To configure encrypted connections in SQL Server, SQL Server must be configured with “force encryption” using these steps:

Prerequisite for SQL Server 2019: the following driver must be installed on the StarTeam Server machine: Microsoft OLE DB Driver for SQL Server(x64) version 18. This driver is not installed with StarTeam Server.

1. In SQL Server Configuration Manager, expand SQL Server Network Configuration, right-click Protocols for <server instance>, and then select Properties.
2. In the Protocols for <instance name> Properties dialog box, on the Certificate tab, select the desired certificate from the drop-down for the Certificate box, and then click OK.
3. On the Flags tab, in the ForceEncryption box, select Yes, and then click OK to close the dialog box.
4. Restart the SQL Server service.

Back to top