1. Oracle GoldenGate Classic Architecture Documentation
  2. Prepare
  3. Prepare Your Database for Oracle GoldenGate Classic Architecture
  4. SQL Server
  5. Prepare Database Users and Privileges
  6. Oracle GoldenGate for SQL Server

Oracle GoldenGate for SQL Server

Oracle GoldenGate processes require a database user in order to capture from and apply data to a SQL Server database and it is recommended to create a dedicated database user to be used exclusively by Oracle GoldenGate processes.

Oracle GoldenGate for SQL Server supports SQL Server authentication for all of its certified platforms and Windows authentication for Classic Architecture only when Oracle GoldenGate is installed on a Windows server.

  • To use Windows authentication for Oracle GoldenGate Classic Architecture, the Extract and Replicat processes inherit the login credentials of the Manager process. By default, the Manager process runs interactively as the user logged on to the Windows server or optionally can be added as a Windows Service with a default service name of GGSMGR. Whichever method that the Manager process is using, the user that it is running as needs to have the required SQL Server privileges listed above.

  • To use SQL Server authentication, create a dedicated SQL Server login for Extract and Replicat and assign the privileges listed below.

SQL Server and Azure SQL Managed Instance

The following user requirements and minimum database privileges and permissions are required for Oracle GoldenGate to capture from and apply to a SQL Server or Azure SQL Managed Instance database.

  1. Create a dedicated login for Oracle GoldenGate for SQL Server or Azure SQL Managed Instance.

  2. Add the login as a user to the msdb database and to the source or target database.

  3. Create a schema in the source or target database, to be used for objects required for Oracle GoldenGate. This schema should map to the GGSCHEMA value used in the GLOBALS parameter file.

  4. Enable the following privileges and permissions for the Oracle GoldenGate user based on whether the user is for an Extract, or for a Replicat.

Table 3-4 Privileges and Permissions for Oracle GoldenGate User

Privilege Extract Replicat Syntax

msdb Database Roles and Privileges

SQLAgentReaderRole

Yes

No

ALTER ROLE SQLAgentReaderRole ADD MEMBER gguser;

SQLAgentUserRole

Inherited

Yes

ALTER ROLE SQLAgentUserRole ADD MEMBER gguser;

SELECT ON sysjobactivity

Yes

No

GRANT SELECT ON msdb.dbo.sysjobactivity TO gguser;

SELECT ON sysjobs

Yes

No

GRANT SELECT ON msdb.dbo.sysjobs TO gguser;

User Database Roles and Privileges

SYSADMIN

Yes

No

Required for a one time change to enable database level Change Data Capture (CDC) if not already enabled and can be revoked once TRANDATA has been enabled.

ALTER SERVER ROLE sysadmin ADD MEMBER gguser;

Database Administrators with sysadmin credentials can manually enable the database for CDC using the following, which would negate the need for the Extract user to have this privilege:

EXEC msdb.sys.sp_cdc_enable_db ‘source_database’

DBOWNER

Yes

Yes

 ALTER ROLE db_owner ADD MEMBER gguser;