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