CORAL Server Refresh

This document provides step-by-step instructions for data services to refresh Datacollection and icReporting in CORAL.

Database Backup:

A back up of MAMBASQL02.Datacollection needs to be taken first. We use the secondary server to reduce load on the Primary server.
In the Database Backup GUI, select the option Copy-only backup which is used for one time restores and not for recovery purposes. Save the *.bak file in \\MAMBASQL02\F$:\..\BackupforOTherservers. In the Media options section check the Append to the existing backup set.

SQL Snapshots:

Create SQL compare snapshots of CORAL(DataCollection and icReporting).
Check with Dev Team and Phuong if there are any tables with data that need to be backed up in CORAL, which will then need to be restored after the refresh

CORAL DataCollection Refresh Script:

Data services team refer the CORAL refresh script in the shared Server Refresh folder for the below steps,

Run the "send email notification script" to notify that the refresh process has begun.
Run the scripts to Drop replication (in order to drop DataCollection database) from DataCollection and icReporting.
Run script to delete backup history
Run script to put DataCollection in restricted user mode, and to drop the database
Run the script to restore database. Make sure to update the location of the backup file.
Differential backup is rarely used during a refresh, to be ignored mostly.
Run script to put DataCollection in simple mode, shrink log file.
Run scripts to update data like connectionstrings,encryption and report locations.
Run scripts to put DataCollection to multi-user mode, and permissions.
Run script to send email notification that Data collection is ready for use.

CORAL icReporting Refresh/Replication Script:

CORAL.icReporting can be refreshed by backing up from MAMBASQL02.icReporting and restoring or we can use replication to refresh CORAL.icReporting. This decision can be made at the start of every refresh, most of the times replication is the preferred method.

Below steps is refresh icReporting through Replication.

Script replication from MAMBASQL01(since CORAL is replica of mambasql01 to get the latest) by right-clicking on Replication(SSMS)->GenerateScripts.(There is no need to script distributor properties of mamba) and only check (to create or enable the components) option. Generate to new query window. This script to be overwritten to the CORAL replication script in the Data services team shared folder.
Replace the words MAMBASQL01,MAMBASQL02 and MINERS to CORAL, so that there is no accidental use of production.
For each publication, after the "-- Adding the transactional subscriptions" statement, the same statement gets created 1 for each MAMBASQL01/02 and MINERS, so after replacing to CORAL there will be three similar statements , so remove two similar statements. Do this for every publication other than "Temporal" publications. For "Temporal publications" the statements are separated so remove the one that is separate and one from the two statements together.
At this point, there should be no publications or subscriptions under the "Replication" in SSMS. Even if there are few subscriptions we can ignore at this time ( needs more research as to why they are not getting dropped when the subscription cleanup script runs)
Run the script Find min QuestionAnswerID for CallDate threshold.sql in CORAL.DataCollection(this is in the data services team shared folder). This will give the min QuestionAnswerID within the last 6 month range to minimize the data to be replicated to CORAL.icReporting. At line 53 of this script, update the filter clause with the min ID and copy that line. Go to the replication script, in publication "QuestionAnswers" and article tblQuestionAnswer append the filter clause at the end of the sp_addarticle statement to replicate only those rows.
Temporal tables cannot be replicated like a non-temporal table script, so we need to turn off system versioning, create the article and turn on the system versioning and all this should be inside a transaction.
Run the script SystemVersioningonoffscript_ForTemporalTables.sql(this is in the data services team shared folder) - This script creates the system versioning on and off scripts for each temporal table that is getting replicated.
Eg, for 1 article., start after permissions script for that publication

Begin transaction

alter table [dbo].[tblAppointment] set (system_versioning = off);--(get from script)

--add the article and columns and views for tblappointment

alter table [dbo].[tblAppointment] set (system_versioning = on (history_table = [history].[tblAppointment], data_consistency_check = off));-- (get from script)

Commit transaction

Non-temporal tables can also be in this publication, no changes required for it.

Last step is to add transactional subscription for that article, then move on to the next publication.
Now the replication script is all updated.
Replication script to be run very carefully, do not double run nor miss a step.
Run the first 1-9 lines in the replication script.
Run each publication at a time (it will include adding transactional publication, permissions, articles, and transactional subscriptions(only 1 statement)). This will create a record in the LocalPublication folder in SSMS. Right click the publication and select "View Snapshot Agent Status" and click on Start. This will create a subscription.
Temporal tables publications will show errors in the monitor, because the schema snapshot for temporal tables needs to be modified.
Go to \\coral\d$\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\ReplData\unc\to the temporal publication folder, and search for all *.sch files, select all and "edit in notepad++". For all temporal tables schema remove the below code and save it.

,

PERIOD FOR SYSTEM_TIME ([SysStartTime], [SysEndTime])

)

WITH

(

SYSTEM_VERSIONING = ON ( HISTORY_TABLE = [history].[tblPayrollRuleState] )
Once all the *.sch files have been updated check the Replication monitor, and click F5 and the error should be gone after all data has been copied.
Once all publication and subscriptions are created, Launch the Replication monitor click on each publication, double click on the subscription and check for errors. At the end close the replication monitor.
Wait till all data is copied and the message "Delivered snapshot successfully" before applying indexes.
Indexes need to be brought over from MAMBASQL02.icReporting using SQL compare snapshot.
1. All tables and indexes should be applied, sensitivity information to be included.
2. Other database objects, confirm with Phuong and then push.
Send email notification that replication is up.

NOTE: This step is when the entire replication is dropped at the server level(Right-click "Replication", go to "Distributor properties"-> "Publishers", click on the eclipse and change the "Default Snapshot folder" value to \\coral\d$\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\ReplData and check if its different. So all publications will get created in this folder instead of C:\ drive.)