In the past few weeks I have had to reinstall and clean up the vCloud Director 1.5 database on SQL Server 2008 R2. After a few times doing it using the SQL Server Management Studio GUI, I decided to automated it using four simple Transact-SQL scripts, so it would save me time and make it less error prone, and to better document it. I did modify the Transact-SQL part for the ALTER Database section, and I’m using a Simple Recovery mode for my database.
Create [vcloud] database
USE [master]GOCREATE DATABASE [vcloud] on PRIMARY(NAME = N’vcloud’, FILENAME = N’D:\Microsoft SQL Server\vcloud-director\vcloud.mdf’, SIZE = 1024MB, MAXSIZE = 16384MB, FILEGROWTH = 512MB)LOG ON(NAME = N’vcloud_log’, FILENAME = N’D:\Microsoft SQL Server\vcloud-director\vcloud.ldf’, SIZE = 128MB, MAXSIZE = 2048MB, FILEGROWTH = 128MB)COLLATE Latin1_General_CS_ASGO
Alter [vcloud] Database
This is the step 4 on Page 17 for the vCloud Director 1.5 Installation and Configuration Guide.
VMware Version:
USE [vcloud]GOALTER 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
My modified version with the database in Simple Recovery mode.
USE [vcloud]GOALTER DATABASE [vcloud] SET RECOVERY SIMPLE;ALTER DATABASE [vcloud] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;ALTER DATABASE [vcloud] SET ALLOW_SNAPSHOT_ISOLATION ON;EXEC sp_addextendedproperty @name = N’ALLOW_SNAPSHOT_ISOLATION’, @value = ‘ON’;ALTER DATABASE [vcloud] SET READ_COMMITTED_SNAPSHOT ON WITH NO_WAIT;EXEC sp_addextendedproperty @name = N’READ_COMMITTED_SNAPSHOT’, @value = ‘ON’;ALTER DATABASE [vcloud] SET MULTI_USER;GO
Create user vcddb
USE [vcloud]GOCREATE LOGIN [vcddb] WITH PASSWORD = ‘PASSWORD’, DEFAULT_DATABASE = [vcloud], DEFAULT_LANGUAGE=[us_english], CHECK_POLICY=OFFGOCREATE USER [vcddb] for LOGIN [vcddb]GO
Modify user to add db_owner Role
USE [vcloud]GOsp_addrolemember [db_owner],[vcddb]GO