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.

About this task

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.

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.

  • See VMware Knowledge Base article https://kb.vmware.com/kb/2148767 for information about using Microsoft SQL Server Always On Availability Groups with the vCloud Director database.

Procedure

  1. Configure the database server.

    A database server configured with 16GB of memory, 100GB storage, and 4 CPUs should be adequate for most vCloud Director server groups.

  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.

    The following 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.

    The following 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.

    The following script creates 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.

    The following script assigns the db_owner role to the database user created in 5.

    USE [vcloud]
    GO
    sp_addrolemember [db_owner], [vcloud]
    GO