Upgrade a Replicated Database

Anthology Student supports replicating schema changes from the Publisher database to the Subscriber database during upgrades, thus updating a Anthology Student reporting database in place.

We recommend that you perform the following steps to update the reporting (Subscriber) database when upgrading the Anthology Student installation:

Create a Transactional Publication (with Snapshot) and a Subscription to it, if not created earlier. Refer to Create a Second Transactional Publication (with Snapshot) and Create a Second Subscription.

Run the DenyWritesForAllUsers script below against the Publisher database to prevent any unauthorized changes to the data.


-- MAKE SURE TO RUN THIS ON THE PUBLISHER DATABASE

DECLARE @SQLText varchar(1000)

IF EXISTS (SELECT 1 FROM sys.tables WHERE Name = 'SyInstallDenyListWork')
	PRINT 'Error: The SyInstallDenyListWork table already exists!. Contact Campus Management Support.'

CREATE TABLE SyInstallDenyListWork (UserName  varchar(256))

INSERT dbo.SyInstallDenyListWork
SELECT DP.NAME
FROM sys.database_principals DP
WHERE type != 'R'
	AND principal_id >= 5
	AND NOT EXISTS (SELECT 1
	FROM sys.database_principals AS DP1
	LEFT JOIN sys.database_role_members AS DRM ON DRM.role_principal_id = DP1.principal_id
	LEFT JOIN sys.database_principals AS DP2 ON DRM.member_principal_id = DP2.principal_id
	WHERE DP1.type = 'R' AND DP1.NAME = 'db_denydatawriter'	AND DP.NAME = DP2.NAME
		)
ORDER BY 1

DECLARE UserList CURSOR FOR
	SELECT 'EXEC sp_addrolemember N''db_denydatawriter'', N''' + UserName + ''''
	FROM SyInstallDenyListWork

	OPEN UserList
	FETCH NEXT FROM UserList INTO @SQLText

	WHILE @@FETCH_STATUS = 0
	BEGIN
		PRINT @SQLText
		EXEC(@SQLText)
		FETCH NEXT FROM UserList INTO @SQLText
	END
	CLOSE UserList
DEALLOCATE UserList

GRANT SELECT on sys.syscomments TO c2000mts
GRANT SELECT on sys.sysobjects TO c2000mts

Note: The DenyWritesForAllUsers script adds the “db_denydatawriter” database role to all database users. The “Sysadmin” server role overrides the “db_denydatawriter” database role, and thus any user with “sysadmin” previlages will still be able to do data modification.

Run the DisableCVueJobs script below against the Publisher database to disable all Anthology Student jobs. This action prevents any unauthorized changes to data.


-- MAKE SURE TO RUN THIS ON THE PUBLISHER DATABASE
DECLARE @JobId UNIQUEIDENTIFIER, @SqlAgentJobName SYSNAME
, @JobsCount INT, @Id INT

IF EXISTS (SELECT 1 FROM sys.tables WHERE Name = 'SyInstallSQLJobsWork')
	PRINT 'Error: The SyInstallSQLJobsWork table already exists!. Contact Campus Management Support.'

CREATE TABLE SyInstallSQLJobsWork (Id INT IDENTITY(1,1) NOT NULL, JobId UNIQUEIDENTIFIER, JobName SYSNAME, IsJobEnabled BIT)

INSERT dbo.SyInstallSQLJobsWork(JobId, JobName, IsJobEnabled)
SELECT DISTINCT sj.job_id, sj.name,sj.enabled 
FROM msdb.dbo.sysjobs sj
INNER JOIN msdb.dbo.sysjobsteps sjs ON sj.job_id = sjs.job_id
WHERE sjs.database_name = (SELECT DB_NAME())
AND sj.enabled = 1

SELECT @JobsCount = @@ROWCOUNT

SET @Id = 1
WHILE @Id <= @JobsCount
BEGIN
	SELECT @JobId = JobId, @SqlAgentJobName = JobName 
       FROM dbo.SyInstallSQLJobsWork WHERE Id = @Id
	EXEC msdb.dbo.sp_update_job @job_id=@JobId, @enabled = 0
	PRINT 'Disabled job ' + @SqlAgentJobName 
	SET @Id = @Id + 1 
END

GO

Check for any recommended preinstallation steps prior to the upgrade to the required version.
Upgrade the Anthology Student Publisher database all the way up to the required version.

Confirm that replication is working by following the steps in the Monitor Replication section and confirming that there are no errors in the Replication Monitor.

Wait until all the DDL changes are replicated and the last 100 synchronizations show No replicated transactions are available (see Distributor To Subscriber History tab).

On the Subscriber database(s), execute the reporting database scripts that are in the installation folder, which is typically at the following location:

<System drive>\Program Files(x86)\CMC\CampusInstall\SetupFiles\Database\CampusVue\Reporting folder

Execute the script below on the Publisherdatabase to add newly created tables as Articles to a publication:


-- MAKE SURE TO RUN THIS ON THE PUBLISHER DATABASE

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SET NOCOUNT ON

CREATE TABLE #Replicate (aid INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, SchemaName VARCHAR(128), TableName VARCHAR(255))

DECLARE @id INT = 0, @SchemaName NVARCHAR(128), @TableName NVARCHAR(255), @tSQL NVARCHAR(2000)= ''

DECLARE @Publication NVARCHAR(128)

SELECT TOP 1 @Publication = p.NAME 
FROM dbo.SysArticles a WITH (NOLOCK)
INNER JOIN dbo.sysPublications p ON a.Pubid = p.pubid
WHERE a.[name] = 'SyReplTableForSnaphotReplicationWork '

BEGIN TRY
IF @Publication IS NOT NULL
BEGIN

 INSERT #Replicate (SchemaName, TableName)
 SELECT schema_name(schema_id) AS SchemaName, NAME AS TableName FROM sys.tables 
WHERE OBJECTPROPERTY(OBJECT_ID, 'TableHasPrimaryKey') = 1 AND is_ms_shipped = 0
AND ( NAME NOT LIKE '%work' AND NAME NOT LIKE '%isir%work%'
AND NAME NOT LIKE '%nsldswork%' AND name NOT LIKE '%isir%_EXT%' 
AND NAME NOT LIKE '%WorkOrigExt%' AND NAME NOT LIKE '%proxy%' 
AND NAME NOT LIKE 'syservice%'  AND name NOT LIKE '%ExternalMap%'
AND NAME NOT LIKE 'syIMS%' AND NAME NOT LIKE 'syQueue%' AND NAME NOT LIKE '%tblPerson%' 
AND NAME NOT LIKE '%Queue' AND NAME NOT LIKE 'SyPerson_Stag' 
AND NAME NOT LIKE 'SyPersonUnique_Stag' AND NAME NOT LIKE 'NetSQLAzman%' 
AND NAME NOT LIKE 'tblStagSyPersonPhoneNumber' AND NAME NOT LIKE 'SyPerson_Stag_Delta' AND NAME NOT LIKE 'Sa1098TBatchToolDetails')
AND NAME NOT IN (SELECT DISTINCT NAME FROM dbo.sysarticles)
AND NAME NOT IN (SELECT [TableName] 
FROM ( VALUES ('WebPrivacyPolicyDetail'), ('webCampusScream'), ('webItems'), ('webPages'), ('WebPrivacyPolicy'),('SyScriptRunStats') , ('syInQueue'), ('syMailInboxQueue'), ('syQueueAudit'), ('webStyles'), ('FaB2BclFileSys'),('SyVersionInfo'), ('SyVersion'), ('SyRemovedFromRepl'),('FaShoppingSheetConsumer'),('SyInstallDenyListWork')
) AS vtable([TableName]))

 
   SELECT TOP 1 @id = aid, @SchemaName = SchemaName, @TableName = TableName 
	FROM #Replicate  WHERE aid > @id ORDER BY aid

   WHILE (@@ROWCOUNT > 0)
   BEGIN	
	SET @tSQL = N'EXEC sys.sp_addarticle @publication = ''' + @Publication + N''''
	+ N' ,@article = ''' + @TableName + N''''
	+ N' ,@source_owner = ''' + @SchemaName + N''''
	+ N' ,@source_object = ''' + @TableName + N''''
	+ N' ,@type = N''logbased'''
	+ N' ,@description = N'''''
	+ N' ,@creation_script = NULL'
	+ N' ,@pre_creation_cmd = N''truncate'''
	+ N' ,@schema_option = 0x00000000080350DF'
	+ N' ,@force_invalidate_snapshot = 1'
	+ N' ,@identityrangemanagementoption = N''manual'''
	+ N' ,@destination_table = ''' + @TableName + N''''
	+ N' ,@destination_owner = ''' + @SchemaName + N''''
	+ N' ,@vertical_partition = N''false'''
	+ N' ,@ins_cmd = N''CALL sp_MSins_'+ @SchemaName + @TableName + N''''
	+ N' ,@del_cmd = N''CALL sp_MSdel_'+ @SchemaName + @TableName + N''''
	+ N' ,@upd_cmd = N''SCALL sp_MSupd_'+ @SchemaName + @TableName + N''''
	
	PRINT @tSQL  
	EXEC sys.sp_executesql @tSQL
       
       SELECT TOP 1 @id = aid, @SchemaName = SchemaName, @TableName = TableName 
	FROM #Replicate  WHERE aid > @id ORDER BY aid 
   END

   EXEC sys.sp_startpublication_snapshot @publication = @publication
END
ELSE
   PRINT N'Please create a second publication '  

DROP TABLE #Replicate
END TRY
BEGIN CATCH
	SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS ErrorSeverity,
 ERROR_STATE() AS ErrorState, ERROR_LINE() AS ErrorLine, ERROR_MESSAGE() AS ErrorMessage;
END CATCH
GO

SET NOCOUNT OFF
GO

Execute the script below on the Subscriber database(s) to drop the required SQL Constraints and Triggers:
```
EXEC [dbo].[cmc_DropConstraintsAndTriggersOnSubscriber]
GO
```

Execute the script below on the Publisher and Subscriber databases to recreate the indexes that were dropped during the upgrade as a result of change in the schema:


DELETE d
FROM dbo.SyIndexRecreatePostUpgradeWork d
LEFT JOIN dbo.SyIndexRecreatePostUpgradeWork i ON d.SchemaName = i.SchemaName
	AND d.TableName = i.TableName AND d.ColumnName = i.ColumnName
	AND d.IndexName = i.IndexName	
WHERE d.SyIndexRecreatePostUpgradeWorkID < i.SyIndexRecreatePostUpgradeWorkID
GO
EXEC [dbo].[cmc_IndexRecreatePostUpgradeWork]
GO

Run the ReverseDenyWritesForAllUsers script below against the Publisher database.


-- MAKE SURE TO RUN THIS ON THE PUBLISHER DATABASE

DECLARE @SQLText varchar(1000)

BEGIN TRY

	DECLARE UserList CURSOR FOR
		SELECT 'EXEC sp_droprolemember N''db_denydatawriter'', N''' 
+ UserName + ''''
		FROM SyInstallDenyListWork

		OPEN UserList
		FETCH NEXT FROM UserList INTO @SQLText

		WHILE @@FETCH_STATUS = 0
		BEGIN
			PRINT @SQLText
			EXEC(@SQLText)
			FETCH NEXT FROM UserList INTO @SQLText
		END
		CLOSE UserList
	DEALLOCATE UserList

	IF EXISTS (SELECT 1 FROM sys.tables WHERE Name = 'SyInstallDenyListWork')
		DROP TABLE SyInstallDenyListWork 
END TRY

BEGIN CATCH
	PRINT   'Denial of priveleges may not have been fully revoked. The following 
error occurred: '  + CAST(ERROR_NUMBER() as varchar(20)) 
+ ' - ' + ERROR_MESSAGE() 
END CATCH
GO

Run the EnableCvueJobs script below against the Publisher database.


-- MAKE SURE TO RUN THIS ON THE PUBLISHER DATABASE

DECLARE @JobId UNIQUEIDENTIFIER, @SqlAgentJobName SYSNAME, @JobsCount INT, @Id INT

IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE Name = 'SyInstallSQLJobsWork')
	PRINT 'Error: The SyInstallSQLJobsWork table already exists!. Contact Campus Management Support.'

SELECT @JobsCount = Count(1) FROM dbo.SyInstallSQLJobsWork
		
SET @Id = 1
WHILE @Id <= @JobsCount
BEGIN
	SELECT @JobId = JobId, @SqlAgentJobName = JobName FROM dbo.SyInstallSQLJobsWork WHERE Id = @Id
	IF EXISTS (SELECT 1 FROM msdb.dbo.sysjobs sj WHERE job_id = @JobId)
	BEGIN
		EXEC msdb.dbo.sp_update_job @job_id=@JobId, @enabled = 1
		PRINT 'Enabled job ' + @SqlAgentJobName 
	END
	SET @Id = @Id + 1 
END
DROP TABLE SyInstallSQLJobsWork
GO