Scenario 1

The primary and the replicate database can be refreshed from a different database source while existing replication definitions and subscriptions continue to be used.

If the source database has never participated in replication, temporarily add the database to a Replication Server so that it has all the tables and stored procedures needed for replication, before making a copy of it.

This scenario uses a third database (for example, a production database) to populate the source and target database environment (for example, a test database). You would use this scenario when you want to refresh a test system from a copy of a production database.

Before you begin

In the current primary, validate that the objects that have been marked for replication. If the database has text and image columns and these columns will be replicated, decide whether to mark the database for replication prior to the copy or after the primary database has been established.

Check whether the database has been marked for replication:
```
use pri
go
sp_reptostandby pri
go
The replication status for database 'pri' is 'ALL'.
The replication mode for database 'pri' is 'off'.
(return status = 0)
```
Note: In this scenario, the database has been marked for database replication and has not been marked for SQL statement replication.
If the database has not been marked for replication, check to see whether the tables are marked for replication:
```
use pri
go
sp_setreplicate
go
Name      Type
----      ----------
t1         user table
t2         user table
rs_marker  stored procedure
(3 rows affected)
(return status = 0)
```
Note: Any table or stored procedure beginning with "rs_" is created by Replication Server. Therefore, in this scenario, t1 and t2 are application tables, which were marked for replication and rs_marker is a stored procedure created by adding the database to the replication system.

Get the current generation number of the primary database.

use pri
go
dbcc gettrunc
go
secondary trunc page secondary trunc state dbrepstat
------------------- --------------------- -------
2669                           1            173
generation id database id database name ltl version
------------- ------------ ------------- ---------
0                7            pri           720

At the RSSD, obtain the maintenance users for the connections:

use PRS2_RSSD
go
rs_helpuser
go

User Name      Permission(s) Name
------------ ------------------------------
PRS2_id_user    no grants
sa              sa
PRS2_ra         connect source
PRS2_rsi        connect source
PRS2_RSSD_prim  source, primary subscr

Maintenance Users
User name       Destination DS.DB
------------ -------------------------------------
PRS2_RSSD_maint  sunak1505x.PRS2_RSSD
pri_maint        sunak1505x.pri
pri_maint        sunak1505x.rep
(return status = 0)

In this example, “pri_maint” is the maintenance user for both the connection to the primary database, sunak1505x.pri. and replicate database, sunak1505x.rep.

Begin the resynchronization

You can stop all user activity to the source database; however, it is not required.

When a new database is brought online, Adaptive Server creates a database that is transactionally consistent at the time of the dump.

Stop all user activity of the primary database including the existing RepAgent:

sp_stop_rep_agent pri
go
The Replication Agent thread for database 'pri' is
being stopped.
(return status = 0)

Suspend the connection to the primary and replicate database:

isql –Usa –P –SPRS2
suspend connection to sunak1505x.pri
go
Connection to 'sunak1505x.pri' is suspended
suspend connection to sunak1505x.rep
go
Connection to 'sunak1505x.rep' is suspended.

Dump the database of the source database:

dump database diffprim to
'/c11014900/sybase1520x/diffprim.dmp'
go

Load the source database dump to the primary database:

load database pri from
'/c11014900/sybase1520x/diffprim.dmp'
go

Bring the database online:
```
online database pri
go
```
Add the maintenance user of the primary connection to the primary database and grant the appropriate privileges. If the maintenance user already exists on this server, synchronize the “suid” of the maintenance user and any other user that will be logging in to the primary database.
```
use pri
go
sp_adduser pri_maint
go
New user added.
(return status = 0)
grant execute on rs_get_lastcommit to pri_maint
go
grant all on rs_lastcommit to pri_maint
go
```
Note: If the maintenance user is new to this server, add the maintenance user login to this server, and grant the replication role:
```
sp_addlogin <maintenance user>, <maintenance userpassword>, <database>
go
sp_role "grant", replication_role, <maintenance
user>
go
```
If the password to the maintenance user is unknown, set a new password in Adaptive Server. In the Replication Server, change the password of the maintenance user to match with what was assigned in the Adaptive Server:
```
alter connection to <dataserver>.<database>
set password to <new password>
go
```

If the source database dump came from a primary database that had a Replication Agent, remove the secondary truncation point and the existing Replication Agent from the new primary database:

use pri
go
dbcc settrunc (ltm,ignore)
go
secondary trunc page secondary trunc state dbrepstat
-------------------- --------------------- --------
2667                     0                      166
generation id database id database name ltl version
------------- ------------ ------------- ----------
0                7               pri         720

Note: A secondary truncation state of 0 indicates that the secondary truncation point is inactive.

sp_config_rep_agent pri,'disable'
go
Replication Agent disabled for database 'pri'. The
secondary truncation point
in the database is no longer active.
(return status = 0)

Enable the Replication Agent at the primary database:
```
sp_config_rep_agent pri,
'enable','PRS2','PRS2_ra','PRS2_ra_ps'
go
Replication Agent enabled for database 'pri'. The
Replication Agent thread needs
to be started using sp_start_rep_agent.
(return status = 0)
```
Note: Any Replication Server user with “connect source” privilege can be used. If the password of the Replication Server is unknown, it can be reset using the alter user Replication Server command.

Modify the settings in the RSSD and the primary database to have the Replication Agent start at the end of the transaction log:

use PRS2_RSSD
go
rs_zeroltm sunak1505x,pri
go
Locater has been reset to zero.
(return status = 0)
isql –Usa –P –Ssunak1505x
use pri
go
dbcc settrunc (ltm,valid)
go
secondary trunc page secondary trunc state dbrepstat
-------------------- --------------------- --------
2670                         1               167
generation id database id database name ltl version
------------- ------------ ------------- ----------
0                7          pri            720

Increase the generation number of the new primary database by 1 in case the log pages of the new database are numerically less than the log pages of the previous copy of the database:
```
dbcc settrunc (ltm,gen_id,1)
go
secondary trunc page secondary trunc state dbrepstat
-------------------- --------------------- --------
2670                         1               167
generation id database id database name ltl version
------------- ------------ ------------- ----------
0                7          pri            720
```
Note: The previous value of the generation id was obtained in step 3 of “Before you begin” section . If text or image columns need to be marked for replication, mark the tables and columns for replication here.

Load the replicate database with the source database dump:

load database rep from
'/c11014900/sybase1520x/diffprim.dmp'
go

Bring the replicate database online:
```
online database rep
go
```
Add the maintenance user of the replicate connection, to the replicate database and grant all appropriate privileges using the information gathered prior to the resynch.
```
use rep
go
sp_adduser pri_maint
go
New user added.
(return status = 0)
grant execute on rs_get_lastcommit to pri_maint
go
grant all on rs_lastcommit to pri_maint
go
```
Note: If the maintenance user is new to this server, add the maintenance user login to this server, and grant the replication role:
```
sp_addlogin <maintenance user>, <maintenance userpassword>, <database>
go
sp_role "grant", replication_role, <maintenance
user>
go
```
If the password to the maintenance user is unknown, set a new password in Adaptive Server. In the Replication Server, change the password of the maintenance user to match with what was assigned in the Adaptive Server:
```
alter connection to <dataserver>.<database>
set password to <new password>
go
```

If the source database had a RepAgent, release the secondary truncation point and remove the RepAgent:

use rep
go
dbcc settrunc (ltm,ignore)
go
secondary trunc page secondary trunc state dbrepstat
-------------------- --------------------- --------
2667                     0                   166
generation id database id database name ltl version
------------- ------------ ------------- ----------
0                7             pri          720

Note: A secondary truncation state of 0 indicates that the secondary truncation point is inactive.

sp_config_rep_agent rep,'disable'
go
Replication Agent disabled for database 'rep'. The
secondary truncation point
in the database is no longer active.
(return status = 0)

Truncate the rs_lastcommit table at the replicate database. Enter:
```
truncate table rs_lastcommit
go
```
Grant appropriate permissions to the replicate tables so that the maintenance user can apply commands to the replicate database:
```
grant all on t1 to pri_maint
go
grant all on t2 to pri_maint
go
```

Purge the inbound queue of the primary connection and the outbound queue of the replicate connection on the Replication Server to eliminate any in-process work from the old replication setup:

isql -Usa -P –SPRS2
sysadmin hibernate_on
go
The Replication Server has now entered hibernation
mode.
sysadmin sqm_purge_queue, 104,1
go
sysadmin sqm_purge_queue, 106,0
go
sysadmin hibernate_off
go
The Replication Server has now finished hibernation
mode.

Resume connection to the primary and the replicate database:

resume connection to sunak1505x.pri
go
Connection to 'sunak1505x.pri' is resumed.
resume connection to sunak1505x.rep
go
Connection to 'sunak1505x.rep' is resumed.

Start the Replication Agent of the primary database:

sp_start_rep_agent pri
go
Replication Agent thread is started for database
'pri'.
(return status = 0)

On the Replication Server, ensure that both the RepAgent and the DSI threads were successfully started

admin who
go
Spid Name State Info
---- -------- ------------ ------------------
139 DSI EXEC Awaiting Command 104(1) sunak1505x.pri
135 DSI Awaiting Message 104 sunak1505x.pri
129 DIST Awaiting Wakeup 104 sunak1505x.pri
130 SQT Awaiting Wakeup 104:1 DIST
sunak1505x.pri
123 SQM Awaiting Message 104:1 sunak1505x.pri
56 SQM Awaiting Message 104:0 sunak1505x.pri
142 REP AGENT Awaiting Command sunak1505x.pri
143 NRM Awaiting Command sunak1505x.pri
145 DSI EXEC Awaiting Command 106(1) sunak1505x.rep
144 DSI Awaiting Message 106 sunak1505x.rep
124 SQM Awaiting Message 106:0 sunak1505x.rep

Validate that replication is available and working.
Allow users on the primary database.