How do I create a file and filegroup in SQL Server?

After you install Microsoft SQL Server database, you must create the database, filegroup, and a database user with appropriate rights. A filegroup is a grouping of database objects and files that is used for administrative and data placement purposes.

The  TrueSight Operations Management   Report Engine  installation files contain the   create_sql_user.bat  script that you can use to create the filgroup and database user for the Report Engine. You must have SYSDBA priveleges to run the script. A successful execution of the script creates the required filegroup and database user with appropriate rights.

The create_sql_user.bat script creates the following users:

• Schema Owner: When prompted for the database user name, the script creates the user with the provided name. This user is used to create the schema when you install TrueSight Operations Management Report Engine.  
  The create_sql_user.bat script provides following permissions to this user.

  Permission state	Permission type
  GRANT	ALTER
  GRANT	CONNECT
  GRANT	CREATE FUNCTION
  GRANT	CREATE PROCEDURE
  GRANT	CREATE TABLE
  GRANT	CREATE TYPE
  GRANT	CREATE VIEW
  GRANT	EXECUTE
  GRANT	SELECT ON MSDB This permission is required during Report Engine installation and executing the pr_analyze_configtables_once job. The pr_analyze_configtables_once job analyzes the config data table for the first time. The installer requires this permission to verify whether an existing job is available. After a successful installation and successful running of the pr_analyze_configtables_once job, you can revoke this permission. If you are installing multiple Report Engines, you can revoke this permission after the installation of all the Report Engines. BMC recommends you to revoke the permission after 24 hours of installation.

• Read-only User: The script, by default, creates the read-only user with reuniv name, and the RE#Adm1n password.

For the Read-only User, you can change the password after the successful installation. This user is used by BusinessObjects while creating universe connections.

To create the database, user, and filegroups

Do the following to create the filegroup and database user by using the create_sql_user.bat script:

1. Navigate to the Util folder.
2. Copy the AdminDatabaseScripts folder to the computer where you have installed the Reporting database. 
3. Log in to the Microsoft SQL Management Studio with the SQL admin user. The defualt admin user is SA.
4. In the Microsoft SQL Management Studio, navigate to Explorer.
5. Expand  SQL Server > Databases. 

6. Right-click Databases > New Database.

   Note

    Ensure that you are using the default collation as a case insensitive collation. For example, in English, the default collation is  SQL_Latin1_General_CP1_CI_AS.

7. In the New Database window, type the database name and click OK. The newly created database is added to the list of databases.

8. At the command prompt, navigate to the location where you have copied the AdminDatabaseScripts folder, and run the  create_sql_user.bat script. 

9. Enter the required data for the script:
   • When prompted, type the Report Engine database server name, instance name, and database server port.
     You can find the SQL Server instance name in the services.msc file. The default value is  MSSQLSERVER.
     The default server port is 1433.
   • When prompted, type an authentication mode for the sysadmin user.
     Type 1 for SQL sysadmin user (sa) or 2 for Windows sysadmin user (hostname\administrator). The default value is SQL sysadmin (sa).
     If you type the SQL sysadmin user as the authentication mode, type the sysadmin user name and password when prompted.
     If you type Windows sysadmin user as the authentication mode, type the SQL Server database name you created when prompted.
     
   • When prompted, type a login mode.
     Type 1 for SQL Server login or 2 for Windows login. The default value is SQL Server login.
     If you type the SQL Server login as the authentication mode, type the SQL Server login name and password when prompted.
     If you type Windows login as the authentication mode, type the SQL Server database user name when prompted.
     
   • When prompted, type a path for the filegroup to specify the location where you want the filegroup to be created.
   • When prompted, type a database size. The size can be small, medium, or large. The default value is small.

The system executes the script, and the user and file groups are created. 

Warnings

• After the file size of the files created by the script is full, you must manually add the files to the filegroup. While adding the files, do not set the maximum filesize to Unrestricted file growth.
• To avoid data collection issues, do not give the sysadmin role to the Report Engine database user