Wednesday, September 24, 2014

vCAC IaaS database creation script


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 create
           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

  

No comments:

Post a Comment

Who am I

My photo
I’m an IT specialist with over 15 years of experience, working from IT infrastructure to management products, troubleshooting and project management skills from medium to large environments. Nowadays I'm working for VMware as a Consulting Architect, helping customers to embrace the Cloud Era and make them successfully on their journey. Despite the fact I'm a VMware employee these postings reflect my own opinion and do not represents VMware's position, strategies or opinions.

Most Viewed Posts

Blog Archive