Configuring a Microsoft SQL database for vCloud Director 1.5

This articles outlines the steps for creating a Microsoft SQL database for use with vCloud Director 1.5. It is essential that these steps are followed exactly for your database to function correctly.

Cause

SQL Server databases have specific configuration requirements when you use them with vCloud Director. Install and configure a database instance, and create the vCloud Director database user account before you install vCloud Director.

vCloud Director database performance is an important factor in overall vCloud Director performance and scalability. vCloud Director uses the SQL Server tmpdb file when storing large result sets, sorting data, and managing data that is being concurrently read and modified. This file can grow significantly when vCloud Director is experiencing heavy concurrent load. It is a good practice to create the tmpdb file on a dedicated volume that has fast read and write performance. For more information about the tmpdb file and SQL Server performance, see http://msdn.microsoft.com/en-us/library/ms175527.aspx.

Resolution

Prerequisites

• You must be familiar with Microsoft SQL Server commands, scripting, and operation.
• To configure Microsoft SQL Server, log on to the SQL Server host computer using administrator credentials. You can configure SQL server to run with the LOCAL_SYSTEM identity, or any identity with the privilege to run a Windows service.

Configuring the Microsoft SQL Server Database

1. Configure the database server.
   
   A database server configured with 16GB of memory, 100GB storage, and 4 CPUs is adequate for most vCloud Director clusters.
2. Specify Mixed Mode authentication during SQL Server setup.
   
   Windows Authentication is not supported when using SQL Server with vCloud Director.
3. Create the database instance.
   
   This script creates the database and log files, specifying the proper collation sequence:
   
   USE [master]
   GO
   CREATE DATABASE [vcloud] ON PRIMARY
   (NAME = N'vcloud', FILENAME = N'C:\vcloud.mdf', SIZE = 100MB, FILEGROWTH = 10% )
   LOG ON
   (NAME = N'vcdb_log', FILENAME = N'C:\vcloud.ldf', SIZE = 1MB, FILEGROWTH = 10%)
   COLLATE Latin1_General_CS_AS
   GO
   
   The values shown for SIZE are suggestions. You might need to use larger values.
4. Set the transaction isolation level.
   
   This script sets the database isolation level to READ_COMMITTED_SNAPSHOT:
   
   USE [vcloud]
   GO
   ALTER DATABASE [vcloud] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
   ALTER DATABASE [vcloud] SET ALLOW_SNAPSHOT_ISOLATION ON;
   ALTER DATABASE [vcloud] SET READ_COMMITTED_SNAPSHOT ON WITH NO_WAIT;
   ALTER DATABASE [vcloud] SET MULTI_USER;
   GO
   
   For more about transaction isolation, see http://msdn.microsoft.com/en-us/library/ms173763.aspx.
5. Create the vCloud Director database user account.
   
   This script creates the database user name vcloud with password vcloudpass:
   
   USE [vcloud]
   GO
   CREATE LOGIN [vcloud] WITH PASSWORD = 'vcloudpass', DEFAULT_DATABASE =[vcloud],
   DEFAULT_LANGUAGE =[us_english], CHECK_POLICY=OFF
   GO
   CREATE USER [vcloud] for LOGIN [vcloud]
   GO
6. Assign permissions to the vCloud Director database user account.
   
   This script assigns the db_owner role to the database user created in Step 5:
   
   USE [vcloud]
   GO
   sp_addrolemember [db_owner], [vcloud]
   GO