VIPER Server Refresh

This document provides step-by-step instructions for data services to refresh DataCollection and icReporting in VIPERSQL01/02.

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 VIPERSQL01(DataCollection), VIPERSQL01(icReporting) and VIPERSQL02(icReporting).
Check with Dev Team and Phuong if there are any tables with data that need to be backed up in VIPERSQL01/02, which will then need to be restored after the refresh

VIPERSQL01 DataCollection Refresh Script:

Data services team refer the VIPER 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 VIPERSQL01(DataCollection and icReporting) and VIPERSLQ02(icReporting), change connection to the respective servers while running these scripts. At the end change the connection back to VIPERSQL01.
Remove DataCollection from AvailabilityGroup by right clicking(SSMS) on Always On High Availability->Availability Groups->Availability Databases ->Rightclick on DataCollection->Remove Database from Availability Group. Only after removing the database from availability group can it be dropped.
Run script to delete backup history
Run script to put DataCollection of VIPERSQL01 in restricted user mode.
Run script to drop DataCollection in VIPERSQL01/02.
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 shrink log file.
Run scripts to update data like connection strings, 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.
Add database back to Availability Group using GUI, right click on Always On High Availability->Availability Groups->Availability Databases ->Add Database -> Go to the right most end of DataCollection, copy password (this password is the highest level of encryption certificate to authenticate DataCollection restore) from the refresh script and click on refresh at the bottom. Check the DataCollection and connect to VIPERSQL02(using windows authentication). Check Automatic seeding and finish the process. This will start restoring DataCollection to VIPERSQL02.
Need to check if ViperSQL02.DataCollection is completely restored before moving on to the next step.

VIPERSQL02 icReporting Refresh/Replication Script:

VIPERSQL02.icReporting can be refreshed by backing up from MAMBASQL02.icReporting and restoring or we can use replication to refresh VIPERSQL02.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 VIPERSQL01 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 VIPER replication script in the Data services team shared folder.
Replace the words MAMBASQL01 to VIPERSQL01,MAMBASQL02 to VIPERSQL02 and the statements referring to MINERS and GARTER should be removed, so that there is no accidental use of production.
For each publication, after the "-- Adding the transactional subscriptions" statement, remove the statements referring to MINERS.
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 VIPERSQL01.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 VIPERSQL02.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 the first article in the publication "Temporal"., 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

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(VIPERSQL01->Replication->Local 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 \\vipersql01\f$\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 \\vipersql01\f$\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.)