SQL Server database setup for the ExamDeveloper application is achieved with the following three steps:
Create a new database
Create a new database and name it "ExamDeveloper". Let's see how a new database is created in SQL Server.
Open SQL Server Management Studio and connect to the one being used with ExamDeveloper.
- Right-click Database node in Object Explorer and select New Database from the context menu.
- Click the General page and specify the name of the database on the right in the Database name text box. Here, we have specified this as ExamDeveloper.
- You may opt to specify a different path for the database file and the log file if necessary by clicking the Browse button next to their paths in the Database files listing. This can be done to ensure that there is enough space for the application database on the specified path.
- Click OK to save the database.
- Proceed to the next step to restore the blank database files for ExamDeveloper.
Restore database files
Restore ExamDeveloper database with the following steps.
- Extract BlankDb.bak from BlankDb.7z found in <SVNFolder>\Database Utility\ into the same folder. Use the 7-Zip extraction utility from http://www.7-zip.org/download.html to extract the file.
- Right-click Databases in Object Explorer and click Restore Database from the context menu.
This opens the Restore Database window.
- In Source section of the General page, select Device radio button, and click the Browse button.
- This opens the Specify backup devices window. Click the Add button, browse to select the backup file BlankDB.bak located in <SVNFolder>\Database Utility\ as extracted earlier in step 1, and click OK.
- The backup set is added to Backup sets to restore part of the Restore plan section. Ensure that the Restore check box of the backup set is selected. Select ExamDeveloper from the Database drop-down of the Destination section.
- Optionally, select the Files page on the left and choose the physical file locations where you would like to restore the database and log files for the ExamDeveloper database. This step is optional and can be skipped.
- Select the Options page on the left, and under the Restore options section, select Overwrite the existing database (WITH REPLACE). Click OK.
- Ensure that the owner of the database is "sa". Open the properties of the ExamDeveloper database by right-clicking ExamDeveloper under Databases and clicking Properties. The Database Properties window opens.
- Select the Files page on the left of the Database Properties window and specify "sa" as the Owner, if that text box is blank. Click OK.
SQL Server user management
In order to use the ExamDeveloper database, the user is required to have sufficient privileges. While the administrator user (the default 'sa' account in our example) can handle all the tasks, it is recommended that a new user with just the necessary privileges be created. Create an SQL Server user with ownership of ExamDeveloper database. This user account is used in the connection strings for the respective applications.
We will create a new user having sufficient privileges to use the ExamDeveloper database via the following steps:
Start SQL Server Management Studio. You can use SQL Authentication and "sa" account to connect to the server.
Go to Security → Logins, right-click Logins and click New Login as shown below.
Select the General page on the left. Select the SQL Server authentication radio button, enter the Login name and Password, and the same password again in the Confirm password box for confirmation as shown below. (In our example, the username is "ExamDev" and password is "P@s$w0rd"). De-select the Enforce password policy check box.
Select the Server Roles page on the left. Select the roles you want to give to the user. Ensure that dbcreator is selected in order to create the databases (public is selected by default and cannot be de-selected).
Click User Mapping tab on the left. Select the Map check box corresponding to the ExamDeveloper database. This is used to give individual rights for users for each database. Select the role membership as shown below. The following minimum role memberships are required to use the ExamDeveloper database from the application: db_datareader, db_datawriter, and dd_ddladmin, ensure that these are selected (public is selected by default and cannot be deselected). Click OK.
You can use this login ID and password to connect to ExamDeveloper database and this can also be used in ExamDeveloper configuration files to connect to the application database as detailed in Configure ExamDeveloper - ConnectionString.