GoldenGate Connectivity for MS SQL Server 2014

GoldenGate Connectivity for MS SQL Server 2014

Oracle GoldenGate for MS SQL Server: Connecting to SQL Server

 

Microsoft SQL Server is a popular database platform that needs a robust and feature rich replication product that can be used in both a homogeneous and heterogeneous configuration.  I have been involved in a number of SQL Server to SQL Server and SQL Server to Oracle replication projects using Oracle GoldenGate.  Oracle GoldenGate works well with MS SQL Server and the new OGG 12.2 version offers new features that enhance that support.

In the next few months I will be presenting a series of blogs on how to best implement Oracle GoldenGate in a MS SQL Server environment and how to take advantage of its features.  In this entry I will focus on the SQL Server connectivity.

In order for GoldenGate to access SQL Server, an account must be created, a System DSN must be created and the proper privileges must be granted.

The first step is to create a SQL Server Login for the GoldenGate administrator.  I prefer to use ggadmin when working with Microsoft SQL Server.  In this blog entry I will be using SQL Server Authentication, in a later entry I will discuss Windows Authentication.

Create the SQL Server Login using the SQL Server Management Studio.  The privileges necessary are determined by the GoldenGate process role.

Extract:  The GoldenGate administrator account must be granted the sysadmin server role.
Replicat:  The GoldenGate administrator account must be granted db_owner for the database to be replicated to.

Once the account has been created, a System DSN must be created for each database that you will be replicating to and from.  The System DSN determines the connectivity to the database.  If you are extracting from multiple databases you can use the same SQL Server administrator account, but you must configure multiple System DSNs.

 

 

  1. From the Administrative Tools menu select ODBC Data Sources (64-bit). Select the System DSN tab and click Add.  Select the SQL Server Native Client 11.0 from the list of drivers.

1.jpg

Click Finish to continue.

  1. Fill in the DSN Name, a general Description and the name of the Server to connect to. If using named instances you must supply the instance name as well.  The Server connection can be to the server name, or to the local system as designated by “.” (period).

2.jpg

Click Next> to continue.

  1. Choose SQL Server Data Source using Native Client.

3.jpg

Click Finish to continue.

  1. Select SQL Server Authentication and fill in the Login ID and Password of the GoldenGate administrator account.

4.jpg

Click Next> to continue.

  1. Select Change the default database to: and select the database that GoldenGate will be extracting from and/or replicating to. This is critical in determining the source/target database and will be used in the Extract and Replicat parameter files.

5.jpg

Click Next>.

  1. Defaults can be accepted on this page.

6.jpg

Click Finish.

  1. Click Test Data Source.

7.jpg

8.jpg

  1. You should see success. If you do not, return to the configuration and debug the issue.  Do not proceed until the test is successful.

Click OK to exit Add System DSN.

 

  1. You should now see the newly created System DSN and be able to connect to the SQL Server database with GoldenGate.

9.jpg

  1. From GGSCI, login to the database using the System DSN with the User ID and Password from earlier in this blog.

10.jpg

Once you have successfully logged in you can move on to setting up replication.  If you are going to setup GoldenGate replication for multiple data sources, you will need to follow these steps to create a new System DSN to be used for each.

  1. In order to validate that you are connected to the correct database, you can use the LIST TABLES command.

11.jpg

Tip:  I recommend using the credential store for SQL Server logins and keep an obey file for each database login.

First create a diroby directory under the GoldenGate home directory.  Create a dblogin.oby file for each database:  i.e.  ggtest01login.oby, ggtest02login.oby etc.  The obey file will contain the following line:

DBLOGIN SOURCEDB <System DSN> USERID <userid> PASSWORD <password>

Or using the preferred credential store method (requires creation of the credential store):

DBLOGIN SOURCEDB <System DSN> USERIDALIAS <alias>

In the next few weeks I will be blogging more on using Oracle GoldenGate in a SQL Server environment.

By |2017-09-20T08:15:50+00:00March 8th, 2016|Performance Wire|Comments Off on GoldenGate Connectivity for MS SQL Server 2014