One of vCloud Automation Center requirements is a
database for the IaaS component.
Like said of vCAC Installation Guide, there
are a few scenario options for creating the database.
- Create the
database Manually
This option uses a script to create the database,
tables, storage procedures, etc.
But the vCAC user requires sysadmin privileges on the
SQL Server, imagine how happy is the DBA right now.
- Create an
empty database
This option uses a script to create an empty database.
Tables and views will be created during vCAC installation. The good thing about
this approach it that vCAC user does not requires sysadmin privileges, db_owner
on vCAC database is enough.
- Create the
database through the installation wizard
This option allows you to create the database during
the installation procedure, but as the first option, vCAC users must be a
sysadmin of the SQL Server.
Despite pros and cons of each scenario, my concern is all
about the location of the script.
Just unzip and it’s there, createDatabase.SQL script.
Ow, wait a second, do I need to install the appliance
just to get that script ?!?!?!
If you are used with VMware’s installation guides, you
will remember there’s always a section with the database creation script.
Well, it’s not the case with vCAC !!!
If you want to create the database up-front to avoid any issue you might have during implementation, here's the script.
Now you don’t need to deploy a vCAC appliance just to get
the script anymore.
/*
Creates the database.
Script Input Variables:
DBName : The name of the datbase to createCreates the database.
Script Input Variables:
DBDir : The path for the database file without final slash
LogDir : The path for the log file without final slash
*/
:On Error exit
-- Create the database $(DBName)
-- Database at path $(DBPath)
-- Log at path $(LogPath)
-- fixed: the data and log file path should be the same
-- just like the mssql default data (mdf) and log (ldf) files both reside under the same directory
-- to minimize code change the LogDir is left alone
-- bug 2747: there is case that requires data and log directories seperately so put it back
PRINT N'Creating Database ''$(DBName)''.';
DECLARE @data_dir NVARCHAR(250)
DECLARE @log_dir NVARCHAR(250)
SELECT @data_dir = N'$(DBDir)'
SELECT @log_dir = N'$(LogDir)'
IF @data_dir <> '\.' AND @log_dir <> '\.'
BEGIN
PRINT N'SQL database data and log file directory''$(DBDir)''.';
CREATE DATABASE [$(DBName)] ON PRIMARY
(NAME = N'$(DBName)', FILENAME = N'$(DBDir)\$(DBName).mdf' , SIZE = 1024MB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024MB)
LOG ON
(NAME = N'$(DBName)_log', FILENAME = N'$(LogDir)\$(DBName)_log.ldf' , SIZE = 512MB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
COLLATE SQL_Latin1_General_CP1_CI_AS;
END
ELSE
BEGIN
PRINT N'Using default SQL database data and log directory';
CREATE DATABASE [$(DBName)]
ALTER DATABASE [$(DBName)]
MODIFY FILE
(NAME = N'$(DBName)', SIZE = 1024MB, MAXSIZE = UNLIMITED, FILEGROWTH = 1024MB)
ALTER DATABASE [$(DBName)]
MODIFY FILE
(NAME = N'$(DBName)_log', SIZE = 512MB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
ALTER DATABASE [$(DBName)]
COLLATE SQL_Latin1_General_CP1_CI_AS;
END
-- Set compatibility level to SQL 2008
PRINT N'Setting database compatibility level to SQL 2008.';
EXEC dbo.sp_dbcmptlevel @dbname=N'$(DBName)', @new_cmptlevel=100;
GO
-- Disable Full Text Search
PRINT N'Disabling full text search.';
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
BEGIN
EXEC [$(DBName)].[dbo].[sp_fulltext_database] @action = 'disable'
END
GO
-- DB Settings
PRINT N'Setting database settings.';
ALTER DATABASE [$(DBName)] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [$(DBName)] SET ANSI_NULLS ON
GO
ALTER DATABASE [$(DBName)] SET ANSI_PADDING ON
GO
ALTER DATABASE [$(DBName)] SET ANSI_WARNINGS ON
GO
ALTER DATABASE [$(DBName)] SET ARITHABORT ON
GO
ALTER DATABASE [$(DBName)] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [$(DBName)] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [$(DBName)] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [$(DBName)] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [$(DBName)] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [$(DBName)] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [$(DBName)] SET CONCAT_NULL_YIELDS_NULL ON
GO
ALTER DATABASE [$(DBName)] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [$(DBName)] SET QUOTED_IDENTIFIER ON
GO
ALTER DATABASE [$(DBName)] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [$(DBName)] SET ENABLE_BROKER
GO
ALTER DATABASE [$(DBName)] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [$(DBName)] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [$(DBName)] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [$(DBName)] SET ALLOW_SNAPSHOT_ISOLATION ON
GO
ALTER DATABASE [$(DBName)] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [$(DBName)] SET READ_WRITE
GO
ALTER DATABASE [$(DBName)] SET RECOVERY SIMPLE
GO
ALTER DATABASE [$(DBName)] SET MULTI_USER
GO
ALTER DATABASE [$(DBName)] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [$(DBName)] SET DB_CHAINING OFF
GO
ALTER DATABASE [$(DBName)] SET ALLOW_SNAPSHOT_ISOLATION ON
GO
ALTER DATABASE [$(DBName)] SET READ_COMMITTED_SNAPSHOT ON
GO
END
