Creating SQL Server Databases for SQL Reporting

Two databases are required on the SQL Database Server for normal operation. These databases can be created in different ways:

In either case, some manual steps must be performed prior to running Setup:

Configuring the tempdb File Locations

It is possible for the SQL DBA to pre-configure the location of the ndf and mdf files, including any other temporary files. This should be done after the Databases have been created manually.

If the Databases are created using the SQL Reporting Setup script, then the tempdb and other locations will be set to the defaults

Databases Created Manually

Using a login with appropriate permissions, the following two databases must be created prior to running Setup.

Database

Recommended Name

Example name

Datamart database

BigDataInsight<Product>

BigDataInsightUC

Staging database

BigDataInsight<Product>Source

BigDataInsightUCSource

If necessary, the databases may have different names. If different names are chosen, note them down as they will be required during the Setup process.

The following database setting must be configured for each database. Any settings not mentioned here can be configured according to any other needs in the environment.

Setting

Required Value

Collation

Latin1_General_CI_AS

Once the databases are created, the Setup User must be assigned the following permissions for each database. This is required both during Setup and on an ongoing basis for that user.

Permission Type

Permission

Associated Object

Reason

Database role

db_owner

Datamart database

To allow the Setup script to populate the databases. Is also required after Setup to run the ETL.

Database role

db_owner

Staging database

To allow the Setup script to populate the databases. Is also required after Setup to run the ETL.

Default schema

dbo

Datamart database

To ensure that the database objects created by the Setup script have a consistent schema of dbo.

Default schema

dbo

Staging database

To ensure that the database objects created by the Setup script have a consistent schema of dbo.

In order to complete this using SQL Server Management Studio, use the following steps:

Start SQL Server Management Studio (SSMS) from the MS Windows Start menu.

Connect to the database instance on the MS SQL Server. The login that is used to connect must have permission to create databases.

In the left-hand 'Object Explorer' pane, expand the item top-level connection item.

Expand the 'Security' folder and then expand the 'Login' folder.

In the list of logins, if the Setup User does not exist:

a)    Right click on the 'Logins' folder and then click 'New Login...'

b)    In the 'Login name' box, enter the Setup User and ensure that the 'Windows Authentication' radio box is selected.

c)    Click the OK button.

Create the Datamart database:

a)    Right click the 'Databases' folder and then click 'New Database...'

b)    In the 'Database name' box, give a name to the new database. The recommended name is 'BigDataInsight<Product>', for example 'BigDataInsightUC'.

c)    In the 'Owner' box, enter the Setup User.

d)    Perform any other database configuration necessary, such as changing the database storage location or changing the database recovery model.

e)    Click the OK button.

Create the Staging database:

a)    Right click the 'Databases' folder and then click 'New Database...'

b)    In the 'Database name' box, give a name to the new database. The recommended name is 'BigDataInsight<Product>Source', for example 'BigDataInsightUCSource'.

c)    In the 'Owner' box, enter the Setup User.

d)    Perform any other database configuration necessary, such as changing the database storage location, or changing the database recovery model.

e)    Click the OK button.

Confirm Setup User has default schema 'dbo'.

a)   Expand the 'Security' folder and then expand the 'Login' folder.

b)   Find the Setup User, right click on it and then click 'Properties'.

c)   In the left-hand navigation pane, select 'User Mapping'

d)   Check that the default schema for the Datamart database is set to 'dbo', if not, set it to 'dbo'.

e)   Check that the default schema for the Staging database is set to 'dbo', if not, set it to 'dbo'.

Databases Created by SQL Reporting

In order for the Setup script to create the two required databases, the Setup User must have permission to create databases on the SQL Database Server.

Permission Type

Permission

Associated Object

Reason

Server role

db_creator

SQL Server

To allow the Setup script to create the necessary databases

This permission must be granted to the user before running the 'Setup' script. Once setup has been completed successfully, it is possible to revoke this permission without causing issues.

The Setup User will need to retain ownership of the databases.

In order to complete this using SQL Server Management Studio, use the following steps:

Start SQL Server Management Studio (SSMS) from the MS Windows Start menu.

Connect to the database instance on the MS SQL Server. The login that is used to connect must have permission to grant the 'dbcreator' role, for example a sysadmin user.

In the left-hand 'Object Explorer' pane, expand the item top-level connection item.

Expand the 'Security' folder and then expand the 'Login' folder.

In the list of logins, if the Setup User does not exist:

a)   Right click on the 'Logins' folder and then click 'New Login...'

b)   In the 'Login name' box, enter the Setup User, and ensure that the 'Windows Authentication' radio box is selected.

c)   Click the OK button.

In the 'Login' folder, find the Setup User, right click on it and click 'Properties'.

In the left-hand navigation pane, select the 'Server Roles' page.

If the 'dbcreator' role is not ticked, tick it.

Click the OK button.

During setup, the databases will be created with the following non-default settings:

Database Setting

Value used during setup

MDF file location

Same location as MDF file of the SQL Server [master] database.

LOG file location

Same location as MDF file of the SQL Server [master] database.

Recovery Model

SIMPLE

Initial Size

1 GB

File Growth

25%

Provide feedback on this article