Database Setup

Database Setup

Setting up the Database involves steps to ensure that the database is properly configured, secured and ready to support the application's data storage and retrieval needs. The Database setup consists of the following steps. 

1. Login to the database server. 

2. Create a DB backup from the blob for which the QA has given the signoff. 

Restore Database

3. Go to Microsoft SQL Server Management Studio (SSMS), right click on Database and click Restore Database. 

Figure: SSMS

4. Select the path of the backup file under Device. Provide the Database name as per the instance name. Click OK and the Database is created. 

Figure: Restore Database

5. When restored, we get the following message. Refresh the Database to see if the database has been restored. 

Figure: Message

Create User and map to Database

6. Create a user (new login for the newly created instance). Right click on Security > Logins.

7. Under the General page, provide a valid Login name, select SQL Server authentication, and provide a Password.

8. Under Server Roles page, enable the checkbox for public.

9. Under User Mapping, select the database under Users mapped to this login and select db_owner under Database role membership. Click OK. 

Update URL and Vault

10. URL Change scripts is available in Blob. Perform URL changes by executing this script when setting up a new instance.

11.  Update Vault details and DB connection string details by executing the script provided in the Plt_app_setting. 

To know more about URL change script, refer to URL Change Script. 

To know more about Dynamic Query to Update Vault, refer to Dynamic Query to Update Vault.

Generate a Password and assign to User

12. Run the query "select * from plt_users where email_id like'%Fred$'. 

13, Select the run and run the query 'update plt_users set password = 'xx' where id = 143;"

Create Maintenance Jobs 

14. Create the job on the preinstalled System Stored Procedures on Create Missing Index, Maintenance, and Update Statistics

15. Right-click on System Stored procedure Maintenance > Properties > Steps > Delete AG_Check. 

To know more about USP DB Maintenance, Update Statistics and Missing Index, refer to USP DB Maintenance, Update Statistics and Missing Index.

Create a Missing Job Index 

16. To create the Missing Index job, right-click Jobs > New Job.

17. Enter the Name for the Missing Index. 

18. In the Steps page, provide a Step Name. Select the Database on which it needs to run and type 'EXEC dbo.USP_CREATEMISSINGINDEX GO' in the Command. Click OK. 

19. Go to Schedule > New. Provide the Name and set up Frequency and Daily Frequency timings. Click OK. 

Run the Jobs 

To run the newly created Maintenance and Missing Job Index, perform the following steps. 

20. Right-click the Jobs > Start Job at Step.