Configuring Replication Server for replication from the primary database

This section describes the configuration steps required for replication from the primary database. It includes:

Creating a Replication Server connection to the primary database
Creating a database replication definition
Creating a database replication subscription

Go to the Mirror Replication Agent scripts directory, %SYBASE%\MA-15_1\scripts\mssql

Make a copy of the mssql_create_rs_primary_connection.sql script:

copy mssql_create_rs_primary_connection.sql
my_mssql_create_rs_primary_connection.sql

Before executing the my_mssql_create_rs_primary_connection.sql script against your Replication Server, change {pds}.{pdb} to the name of the connection between Mirror Replication Agent and Replication Server, where:
- {pds} is rs_source_ds (located in %SYBASE%\MA-15_1\init\mssql.rs).
- {pdb} is rs_source_db (located in %SYBASE%\MA-15_1\init\mssql.rs).
You must save the defined values to be used for the Mirror Replication Agent mssql.rs resource file later in the process.

For example, mymrm.pubs.
Change sa and sa_pwd to the user ID and password of the Microsoft SQL Server user who must have permission to apply DML operations against all user tables to be replicated, where:
- sa is the Microsoft SQL Server user ID.
- sa_pwd is the Microsoft SQL Server password.
This user must be a valid user in the Microsoft SQL Server database.

The following example command creates a Replication Server connection to the primary database:
```
create connection to mymrm.pubs
```
```
set error class rs_sqlserver_error_class
```
```
set function string class rs_msss_function_class
```
```
set username newuser
```
```
set password newuser
```
```
with log transfer on, dsi_suspended
```
```
go
```
Execute the script in Replication Server:
```
isql –Usa –P –SSAMPLE_RS -imy_mssql_create_rs_primary_connection.sql
```
A message is displayed that indicates the Replication Server connection to the primary database is created.

Navigate to the %SBYBASE%\MA_15-1\scripts\sybase directory.

Make a copy of the create_rs_db_repdef.sql script:

copy create_rs_db_repdef.sql
my_mssql_create_rs_db_repdef.sql

Before executing the my_mssql_create_rs_db_repdef.sql script, change the value of “{pds}.{pdb}” to the name of the connection string you defined for the primary database, where:
- pds is rs_source_ds, located in %SYBASE%\MA-15_1\init\mssql.rs, for example, mymrm.
- pdb is rs_source_db, located in %SYBASE%\MA-15_1\init\mssql.rs, for example, pubs.
This is a database replication definition example:
```
create database replication definition mymrm_repdef1
with primary at mymrm.pubs
replicate DDL
go
```
Execute the script in Replication Server:
```
isql -Usa -P –SSAMPLE_RS -imy_mssql_create_rs_db_repdef.sql
```
A message is displayed that indicates the database replication definition is created.

Make a copy of the create_rs_db_sub.sql script:

copy create_rs_db_sub.sql 
my_mssql_create_rs_db_sub.sql

Before executing the my_mssql_create_rs_db_sub.sql script, change “{pds}.{pdb}” and “{rds}.{rdb}” to the appropriate connection name, where:
- pds is rs_source_ds, located in %SYBASE%\MA-15_1\init\mssql.rs.
- pdb is rs_source_db, located in %SYBASE%\MA-15_1\init\mssql.rs.
- rds is the DirectConnect access service name, for example, my_standby_mssql_service.
- rdb is the name of the standby database, for example, standby_db.
Here is an example of create database replication subscription:
```
create subscription mymrm_sub1
for database replication definition mymrm_repdef1
with primary at mymrm.pubs
with replicate at my_standby_mssql_service.standby_db
without materialization
go
```
Execute the script in Replication Server:
```
isql -Usa -P –SSAMPLE_RS -imy_mssql_create_rs_db_sub.sql
```
A message is displayed that indicates the subscription is in the process of being created.
Use isql to log in to the Replication Server:
```
isql -Usa -P -SSAMPLE_RS
```
Verify that the database replication definition database subscription exists:
```
check subscription mymrm_sub1
for database replication definition mymrm_repdef1
with primary at mymrm.pubs
with replicate at my_standby_mssql_service.standby_db
go
```
A message is displayed that indicates the subscription is valid for the primary and replicate databases.
Replication Server comes with users who have the permissions necessary for Mirror Replication Agents. However, because Mirror Activator has the capability to create replication definitions, you must grant CREATE OBJECT permission to the Mirror Replication Agent user:
```
grant create object to SAMPLE_RS_ra
go
```
Permission is granted to user SAMPLE_RS_ra.

This user will be entered in the rs_username parameter when building the Mirror Replication Agent for Microsoft SQL Server instance. If you want a user other than the default replication agent user, make sure that the user is granted CONNECT SOURCE and CREATE OBJECT permission.