{"id":200,"date":"2011-11-17T14:04:45","date_gmt":"2011-11-17T13:04:45","guid":{"rendered":"http:\/\/www.bussink.ch\/?p=200"},"modified":"2018-05-23T18:00:17","modified_gmt":"2018-05-23T16:00:17","slug":"vcd-1-5-database-creation-using-transact-sql-on-sql-server-2008-r2-2","status":"publish","type":"post","link":"https:\/\/www.bussink.ch\/?p=200","title":{"rendered":"vCloud Director 1.5 database creation using Transact-SQL"},"content":{"rendered":"

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.<\/p>\n

Create [vcloud] database<\/strong><\/div>\n
\n
USE<\/span>\u00a0[master]<\/span><\/div>\n
GO<\/span><\/div>\n
CREATE<\/span>\u00a0DATABASE<\/span>\u00a0[vcloud]<\/span>\u00a0on<\/span>\u00a0PRIMARY<\/span><\/div>\n
(<\/span>NAME<\/span>\u00a0=<\/span>\u00a0N’vcloud’<\/span>,<\/span>\u00a0FILENAME<\/span>\u00a0=<\/span>\u00a0N’D:\\Microsoft SQL Server\\vcloud-director\\vcloud.mdf’<\/span>,<\/span>\u00a0SIZE<\/span>\u00a0=<\/span>\u00a01024MB<\/span>,<\/span>\u00a0MAXSIZE<\/span>\u00a0=<\/span>\u00a016384MB<\/span>,<\/span>\u00a0FILEGROWTH<\/span>\u00a0=<\/span>\u00a0512MB<\/span>)<\/span><\/div>\n
LOG<\/span>\u00a0ON<\/span><\/div>\n
(<\/span>NAME<\/span>\u00a0=<\/span>\u00a0N’vcloud_log’<\/span>,<\/span>\u00a0FILENAME<\/span>\u00a0=<\/span>\u00a0N’D:\\Microsoft SQL Server\\vcloud-director\\vcloud.ldf’<\/span>,<\/span>\u00a0SIZE<\/span>\u00a0=<\/span>\u00a0128MB<\/span>,<\/span>\u00a0MAXSIZE<\/span>\u00a0=<\/span>\u00a02048MB<\/span>,<\/span>\u00a0FILEGROWTH<\/span>\u00a0=<\/span>\u00a0128MB<\/span>)<\/span><\/div>\n
COLLATE<\/span>\u00a0Latin1_General_CS_AS<\/span><\/div>\n
GO<\/span><\/div>\n<\/blockquote>\n
\n
\"\"<\/a>

SQL vCD Database – Create vcloud database<\/p><\/div>\n<\/div>\n

Alter [vcloud] Database<\/strong><\/div>\n
This is the step 4 on Page 17 for the\u00a0vCloud Director 1.5 Installation and Configuration Guide.<\/div>\n
VMware Version:<\/div>\n
\n
USE<\/span>\u00a0[vcloud]<\/span><\/div>\n
GO<\/span><\/div>\n
ALTER<\/span>\u00a0DATABASE<\/span>\u00a0[vcloud]<\/span>\u00a0SET<\/span>\u00a0SINGLE_USER<\/span>\u00a0WITH<\/span>\u00a0ROLLBACK<\/span>\u00a0IMMEDIATE<\/span>;<\/span><\/div>\n
ALTER<\/span>\u00a0DATABASE<\/span>\u00a0[vcloud]<\/span>\u00a0SET<\/span>\u00a0ALLOW_SNAPSHOT_ISOLATION<\/span>\u00a0ON<\/span>;<\/span><\/div>\n
ALTER<\/span>\u00a0DATABASE<\/span>\u00a0[vcloud]<\/span>\u00a0SET<\/span>\u00a0READ_COMMITTED_SNAPSHOT<\/span>\u00a0ON<\/span>\u00a0WITH<\/span>\u00a0NO_WAIT<\/span>;<\/span><\/div>\n
ALTER<\/span>\u00a0DATABASE<\/span>\u00a0[vcloud]<\/span>\u00a0SET<\/span>\u00a0MULTI_USER<\/span>;<\/span><\/div>\n
GO<\/span><\/div>\n<\/blockquote>\n
My modified version with the database in Simple Recovery mode.<\/div>\n
\n
USE<\/span>\u00a0[vcloud]<\/span><\/div>\n
GO<\/span><\/div>\n
ALTER<\/span>\u00a0DATABASE<\/span>\u00a0[vcloud]<\/span>\u00a0SET<\/span>\u00a0RECOVERY<\/span>\u00a0SIMPLE<\/span>;<\/span><\/div>\n
ALTER<\/span>\u00a0DATABASE<\/span>\u00a0[vcloud]<\/span>\u00a0SET<\/span>\u00a0SINGLE_USER<\/span>\u00a0WITH<\/span>\u00a0ROLLBACK<\/span>\u00a0IMMEDIATE<\/span>;<\/span><\/div>\n
ALTER<\/span>\u00a0DATABASE<\/span>\u00a0[vcloud]<\/span>\u00a0SET<\/span>\u00a0ALLOW_SNAPSHOT_ISOLATION<\/span>\u00a0ON<\/span>;<\/span><\/div>\n
EXEC<\/span>\u00a0sp_addextendedproperty<\/span>\u00a0@name<\/span>\u00a0=<\/span>\u00a0N’ALLOW_SNAPSHOT_ISOLATION’<\/span>,<\/span>\u00a0@value<\/span>\u00a0=<\/span>\u00a0‘ON’<\/span>;<\/span><\/div>\n
ALTER<\/span>\u00a0DATABASE<\/span>\u00a0[vcloud]<\/span>\u00a0SET<\/span>\u00a0READ_COMMITTED_SNAPSHOT<\/span>\u00a0ON<\/span>\u00a0WITH<\/span>\u00a0NO_WAIT<\/span>;<\/span><\/div>\n
EXEC<\/span>\u00a0sp_addextendedproperty<\/span>\u00a0@name<\/span>\u00a0=<\/span>\u00a0N’READ_COMMITTED_SNAPSHOT’<\/span>,<\/span>\u00a0@value<\/span>\u00a0=<\/span>\u00a0‘ON’<\/span>;<\/span><\/div>\n
ALTER<\/span>\u00a0DATABASE<\/span>\u00a0[vcloud]<\/span>\u00a0SET<\/span>\u00a0MULTI_USER<\/span>;<\/span><\/div>\n
GO<\/span><\/div>\n<\/blockquote>\n
\n
\"\"<\/a>

SQL vCD Database – Alter vcloud database<\/p><\/div>\n


\n<\/span><\/p>\n<\/div>\n

\n
Create user vcddb<\/strong><\/div>\n
\n
\n
USE<\/span>\u00a0[vcloud]<\/span><\/span><\/div>\n
GO<\/span><\/span><\/div>\n
CREATE<\/span>\u00a0LOGIN<\/span>\u00a0[vcddb]<\/span>\u00a0WITH<\/span>\u00a0PASSWORD<\/span>\u00a0=<\/span>\u00a0‘PASSWORD’<\/span>,<\/span>\u00a0DEFAULT_DATABASE<\/span>\u00a0=<\/span>\u00a0[vcloud]<\/span>,<\/span>\u00a0DEFAULT_LANGUAGE<\/span>=<\/span>[us_english]<\/span>,<\/span>\u00a0CHECK_POLICY<\/span>=<\/span>OFF<\/span><\/span><\/div>\n
GO<\/span><\/span><\/div>\n
CREATE<\/span>\u00a0USER<\/span>\u00a0[vcddb]<\/span>\u00a0for<\/span>\u00a0LOGIN<\/span>\u00a0[vcddb]<\/span><\/span><\/div>\n
GO<\/span><\/span><\/div>\n<\/blockquote>\n<\/div>\n
\n
\"\"<\/a>

SQL vCD Database – Creat vcddba account<\/p><\/div>\n<\/div>\n

Modify user to add db_owner Role\u00a0<\/strong><\/div>\n
\n
USE\u00a0[vcloud]<\/div>\n
GO<\/div>\n
sp_addrolemember\u00a0[db_owner],[vcddb]<\/div>\n
GO<\/div>\n<\/blockquote>\n<\/div>\n
\"SQL<\/a><\/div>\n","protected":false},"excerpt":{"rendered":"

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 […]<\/p>\n","protected":false},"author":1,"featured_media":224,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[9],"tags":[18,19,31],"_links":{"self":[{"href":"https:\/\/www.bussink.ch\/index.php?rest_route=\/wp\/v2\/posts\/200"}],"collection":[{"href":"https:\/\/www.bussink.ch\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.bussink.ch\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.bussink.ch\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.bussink.ch\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=200"}],"version-history":[{"count":33,"href":"https:\/\/www.bussink.ch\/index.php?rest_route=\/wp\/v2\/posts\/200\/revisions"}],"predecessor-version":[{"id":1973,"href":"https:\/\/www.bussink.ch\/index.php?rest_route=\/wp\/v2\/posts\/200\/revisions\/1973"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.bussink.ch\/index.php?rest_route=\/wp\/v2\/media\/224"}],"wp:attachment":[{"href":"https:\/\/www.bussink.ch\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=200"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.bussink.ch\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=200"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.bussink.ch\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=200"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}