Set Up Plant Applications SQL Server for Replication

About this task

Important:

In this task, angle brackets (< >) indicate placeholder text. If such placeholder text is part of a command, you must replace it with an appropriate value before running the command.

Before you begin

When installing Microsoft SQL, in the Feature Selection page, ensure that the SQL Server Replication check box is selected.
Ensure that you are a system administrator (that is, you must be assigned the sysadmin role).

Procedure

Access the server that hosts the Plant Applications database.
Access SQL Server Management Studio (SSMS).
Create a user login with the sysadmin role for the Plant Applications database.
Note:
For more information, refer to the SSMS documentation.
Important:
- You can configure Windows or SQL Server authentication for the login.
- You can replace the sysadmin role of the user login with the db_datareader role after you perform the Create Backups of Plant Applications SQL Server Transaction Log task later in the deployment process.
Execute the following query to switch the database to full recovery model:
```
USE [master];
ALTERDATABASE [SOADB] SET RECOVERY FULL ; 
```
Install the replication components for the Plant Applications SQL server instance using the SQL Server Installation Wizard.

Note:
For more information, refer to the SSMS documentation.
Ensure that the SQLSERVERAGENT service is running, and then create a local publication of the Plant Applications database.
Important:
You must perform the following steps while creating the local publication:
- Set Transactional publishing as the Publishing Type.
- Select at least one database owner (dbo) table to publish.
- Configure the Snapshot Agent process to run under the SQL Server Agent service account by impersonating the process account.
- Enter AR_PUBLICATION_00006 as the Publication Name.

Set up a distribution database as a system administrator by performing one of the following steps:

Run the Configure Distributor wizard in the Microsoft SQL server. For instructions, refer to https://docs.microsoft.com/en-us/sql/relational-databases/replication/configure-publishing-and-distribution?view=sql-server-ver15. Delete the publication that is created.

Run the following script after replacing the database name and location with the appropriate names:

use master
exec sp_adddistributor @distributor = @@ServerName, @password = N''
GO
DECLARE @PATH_DATA VARCHAR(250), @PATH_LOG VARCHAR(250),@DBNAME VARCHAR(250)
SET @DBNAME = N'ADAPA';  -- <- Specify the DB Name
SELECT @PATH_DATA = physical_name FROM sys.master_files WHERE database_id = DB_ID(@DBNAME) AND type_desc = 'ROWS'
SET @PATH_DATA = LEFT(@PATH_DATA, LEN(@PATH_DATA)-CHARINDEX('\', REVERSE(@PATH_DATA),1))
SELECT @PATH_LOG = physical_name FROM sys.master_files WHERE database_id = DB_ID(@DBNAME) AND type_desc = 'LOG'
SET @PATH_LOG = LEFT(@PATH_LOG, LEN(@PATH_LOG)-CHARINDEX('\', REVERSE(@PATH_LOG),1))
exec sp_adddistributiondb @database = N'distribution', @data_folder = @PATH_DATA, @log_folder = @PATH_LOG, @log_file_size = 2, @min_distretention = 0, @max_distretention = 72, @history_retention = 48, @security_mode = 1
GO
 
use [distribution]
DECLARE @PATH_REPL varchar(250),@DBNAME VARCHAR(250)
SET @DBNAME = N'ADAPA'; -- <- Specify the DB Name
SELECT @PATH_REPL = physical_name FROM sys.master_files WHERE database_id = DB_ID(@DBNAME) AND type_desc = 'ROWS'
SET @PATH_REPL = LEFT(@PATH_REPL, LEN(@PATH_REPL)-CHARINDEX('\', REVERSE(@PATH_REPL),1)) -- get up to DATA
SET @PATH_REPL = CONCAT(LEFT(@PATH_REPL, LEN(@PATH_REPL)-CHARINDEX('\', REVERSE(@PATH_REPL),1)),'\ReplData')
if (not exists (select * from sysobjects where name = 'UIProperties' and type = 'U '))
       create table UIProperties(id int)
if (exists (select * from ::fn_listextendedproperty('SnapshotFolder', 'user', 'dbo', 'table', 'UIProperties', null, null)))
       EXEC sp_updateextendedproperty N'SnapshotFolder', @PATH_REPL, 'user', dbo, 'table', 'UIProperties'
else
       EXEC sp_addextendedproperty N'SnapshotFolder', @PATH_REPL, 'user', dbo, 'table', 'UIProperties'
GO
 
-- works for SQL 2012 only because the path is hardcoded for a specific SQL version (11); should be replaced with 13 for SQL 2016, to find a way to easily read the version, other than @@VERSION
DECLARE @PATH_REPL varchar(250)
SET @PATH_REPL = 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup'
exec sp_adddistpublisher @publisher = @@ServerName, @distribution_db = N'distribution', @security_mode = 0, @login = N'sa', @password = N'', @working_directory = @PATH_REPL, @trusted = N'false', @thirdparty_flag = 0, @publisher_type = N'MSSQLSERVER'
GO


use [SOADB]
exec sp_replicationdboption @dbname = N'SOADB', @optname = N'publish', @value = N'true'
GO
-- STOP HERE

After the AlwaysOn Availability group is created and articles are used, set up only one distribution database (if it does not exist).

Note:
For instructions on setting up transactional replication, refer to https://docs.microsoft.com/en-us/sql/relational-databases/replication/configure-distribution?view=sql-server-ver15.
Run the transaction log backup script. You can backup the transaction logs on Fileshare or on the primary node.
Note:
If you want to use Fileshare, update the file path in the scripts. This will maintain the latest transaction log backup with the current state of the database. It is ideal to maintain at least 30 days of transaction data for highly changing databases. This gives us enough time to reconnect to the database in case of a complete hub failure or any similar issues.
The following lines of code is a sample script for a full backup:
```
DECLARE @backuppath varchar(250),@filename varchar(250);
--Change the path as per what's configured in the system for backpath
SET @backuppath = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\'; 
SET @filename = CONCAT(@backuppath,'SOADB_FULL.bak')

BACKUP DATABASE SOADB TO DISK = @filename
   WITH FORMAT,
      NAME = 'Full DATABASE Backup of SOADB';
```
The following lines of code is a sample script for a logs backup:
```
DECLARE @backuppath varchar(250),@filename varchar(250);
--Change the path as per what's configured in the system for backpath
SET @backuppath = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\'; 

-- Perform a Transaction Log Backup of the Database

SET @filename = CONCAT(@backuppath ,'SOADB_backup_' +CONVERT(varchar, GETUTCDATE(), 23)+N'.bak' )
BACKUP LOG SOADB TO  DISK = @filename WITH RETAINDAYS = 30, 
NOFORMAT, NOINIT,  
NAME = 'SOADB Database LOG Backup', 
SKIP, 
NOREWIND, 
NOUNLOAD,  
STATS = 10
GO
```
Note:
Backup transaction logs only after you have performed a full backup of the database. You cannot backup transaction logs using a simple recovery model. You cannot restore transaction logs without a corresponding database backup. For more information, refer to https://www.easeus.com/backup-utility/three-sql-server-recovery-models.html.
1. Start the Microsoft SQL server agent and create a new job.
2. Specify the steps for creating a transaction log backup.
3. Specify the schedule for the backup.
4. Verify that the jobs run successfully.
5. Verify the publication parameters by running the following command: sp_helppublication 'awsdms'
6. Verify that the publication is published by running the following command:
```
SELECT @@Servername AS
<PlantAppsSQLServerInstanceName>,sysdatetime() AS TS, COUNT(*) AS
PendingTasks FROM dbo.PendingTasks
SELECT @@Servername as Servername, name, state_desc,recovery_model_desc, log_reuse_wait_desc,is_supplemental_logging_enabled, is_published, is_subscribed,is_distributor,is_cdc_enabled FROM sys.databases WHERE name = '<PlantAppsDatabase>'
```

Optional: Perform the following steps on the Plant Applications SQL server instance to verify whether its configuration supports replication:

Execute the following query to view the number of pending tasks:

select @@Servername as <Plant_Application_SQL_Server_Instance_Name>,sysdatetime() as TS, COUNT(*) as PendingTasks from dbo.PendingTasks

In the query results, verify whether the number of pending tasks is less enough for the time intervals.

Execute the following query to view the current status and general settings:

select @@Servername as Servername, name, state_desc, recovery_model_desc, log_reuse_wait_desc,
is_supplemental_logging_enabled, is_published, is_subscribed, is_distributor,
is_cdc_enabled from sys.databases where name = '<Plant_Applications_Database>'

In the query results, verify whether the value for the is_published parameter is 1.

Execute the following query to view the log details:

create table #TmpLOGSPACE(
    DatabaseName varchar(100)
    , LOGSIZE_MB decimal(18, 9)
    , LOGSPACE_USED_PERCENT decimal(18, 9)
    , Used_MB AS LOGSIZE_MB * LOGSPACE_USED_PERCENT / 100.0
    , LOGSTATUS decimal(18, 9))
insert #TmpLOGSPACE(DatabaseName, LOGSIZE_MB, LOGSPACE_USED_PERCENT, LOGSTATUS)
exec('DBCC SQLPERF(LOGSPACE)')
select @@Servername as <Plant_Applications_SQL_Server_Instance_Name>, * from #TmpLOGSPACE where DatabaseName = '<Plant_Applications_Database>'

In the query results, verify whether the value for the LOGSPACE_USED_PERCENT parameter is less than 70.
Execute the following query to delete the #TmpLOGSPACE table:
```
drop table #TmpLOGSPACE
```