Creating SRM Database(SQL) “HOW TO”


Here is my quick note to create SRM Database for SRM 4.0, 4.1 and 5.0,

Below Sample DB Creating for DR site only, you will need to create same things for production site as well. After creating DB setup system DSN as usual from SRM server to DB server. SRM used ODBC 32-bit.You will need to launch 32-bit ODBC datasource from “c:\Windows\SysWOW64\odbcad32.exe”

Variable Table;

Site Name SRM DB Name Schema Name ODBC Connection User Name
Production VI_SRMDB_Prod SRMDB_Prod_SRVC SRMDB_Prod_SRVC
DR VI_SRMDB_DR SRMDB_DR_SRVC SRMDB_DR_SRVC

1. Connect SQL Server with SQL Mgmt Studio

2. Log in with SA or Windows Admin account, which has Full Rights for SQL Server

3. Create SQL DB ( DB Name = VI_SRMDB_DR)

4. Create Schema for VI_SRMDB_DR database. The schema name must be the same as  the user name. (Schema Name : SRMDB_DR_SRVC)

5. Create Database User for server login and ODBC connection.

Login Name: “SRMDB_DR_SRVC” (MUST BE SAME WITH SCHEMA NAME).

Select Default DB to “VI_SRMDB_DR”.

Select “BULKADMIN” Server Role.

Select User Mapping to assign “Default Schema” and Database role membership for

  • db_accessadmin
  • db_backupoperator
  • db_datareader
  • db_datawriter
  • db_ddladmin
  • db_denydatareader(DON’T SELECT)
  • db_denydatawriter(DON’T SELECT)
  • db_owner
  • db_securityadmin
  • public

6. Select Schema owner for VI_SRMDB_DR,

Testing newly created database

7. Test Database Connection With newly created user name. To do this, create empty text file and change file extension to .udl. Then open the file to test data connection to Database.

8. Log in to SQL Mgmt Studio with SRMDB_DR_SRVC account. Create a new query to VI_SRMDB_DR and EXECUTE. New table should be create successfully.

Reference from VMware SRM Administration Guide

Microsoft SQL Server Configuration
A Microsoft SQL Server configuration must meet specific requirements to support SRM.
Microsoft SQL Server has the following configuration requirements when used as the SRM database:

  • There are three requirements for the database schema:
  • It must be owned by the SRM database user (the database user name you supply when configuring the SRM database connection).
  • It must have the same name as the SRM database user.
  • It must be the default schema for the SRM database user.
  • The SRM database user must have database administrator privileges.
  • The SRM database user must be granted the following permissions:
  • bulk insert
  • connect
  • create table
  • create view
  • If you are using Windows authentication, the SRM server and database server must run on the same host.
  • If the SRM server and database server run on different hosts, you must use mixed mode authentication.
  • If SQL Server is installed locally, you might need to disable the Shared Memory network setting on the database server.
Advertisements
Gallery | This entry was posted in VMware and tagged , , , , , , . Bookmark the permalink.

3 Responses to Creating SRM Database(SQL) “HOW TO”

  1. AKMyint says:

    communities.vmware.com/community/vmtn/vcenter

  2. Thanks for this – the screenshots were especially useful.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s