Communities
A collaborative platform to connect and grow with like-minded Informaticans across the globe
Product Communities
Connect and collaborate with Informatica experts and champions
Discussions
Have a question? Start a Discussion and get immediate answers you are looking for
User Groups
Customer-organized groups that meet online and in-person. Join today to network, share ideas, and get tips on how to get the most out of Informatica
Get Started
Community Guidelines
Knowledge Center
Troubleshooting documents, product guides, how to videos, best practices, and more
Knowledge Base
One-stop self-service portal for solutions, FAQs, Whitepapers, How Tos, Videos, and more
Support TV
Video channel for step-by-step instructions to use our products, best practices, troubleshooting tips, and much more
Documentation
Information library of the latest product documents
Velocity (Best Practices)
Best practices and use cases from the Implementation team
Learn
Rich resources to help you leverage full capabilities of our products
Trainings
Role-based training programs for the best ROI
Certifications
Get certified on Informatica products. Free, Foundation, or Professional
Product Learning Paths
Free and unlimited modules based on your expertise level and journey
Resources
Library of content to help you leverage the best of Informatica products
Tech Tuesdays Webinars
Most popular webinars on product architecture, best practices, and more
Product Availability Matrix
Product Availability Matrix statements of Informatica products
SupportFlash
Monthly support newsletter
Support Documents
Informatica Support Guide and Statements, Quick Start Guides, and Cloud Product Description Schedule
Product Lifecycle
End of Life statements of Informatica products
Ideas
Events
Change Request Tracking
Marketplace
English
  • Data Replication 9.8.0 HotFix 2
  • All Products

Table of Contents

Search

  1. Preface
  2. Data Replication Overview
  3. Understanding Data Replication
  4. Sources - Preparation and Replication Considerations
  5. Targets - Preparation and Replication Considerations
  6. Starting the Server Manager
  7. Getting Started with the Data Replication Console
  8. Defining and Managing Server Manager Main Servers and Subservers
  9. Creating and Managing User Accounts
  10. Creating and Managing Connections
  11. Creating Replication Configurations
  12. Materializing Targets with InitialSync
  13. Scheduling and Running Replication Tasks
  14. Implementing Advanced Replication Topologies
  15. Monitoring Data Replication
  16. Managing Replication Configurations
  17. Handling Replication Environment Changes and Failures
  18. Troubleshooting
  19. Data Replication Files and Subdirectories
  20. Data Replication Runtime Parameters
  21. Command Line Parameters for Data Replication Components
  22. Updating Configurations in the Replication Configuration CLI
  23. DDL Statements for Manually Creating Recovery Tables
  24. Sample Scripts for Enabling or Disabling SQL Server Change Data Capture
  25. Glossary

User Guide

User Guide

Back Next

Editing Microsoft SQL Server Instance Settings

Editing Microsoft SQL Server Instance Settings

If you run the Server Manager on a local Microsoft SQL Server source system, you can edit some connection settings for SQL Server instances. You can also indicate whether to restart SQL Server Enterprise Edition instances when you edit SQL Server Change Data Capture settings for source tables and indicate how to handle SQL Server CDC capture and cleanup jobs.
  1. On the
    Server Manager
     tab >
    Servers
     view, select a Server Manager instance that runs on the SQL Server source system. Then click the
    SQL Server Instances
     icon on the Server Managers toolbar, or right-click the SQL Server source system and select
    SQL Server Instances
    .
    The
    Microsoft SQL Server Instances
     dialog box appears.
  2. In the
    Server Manager
     list, verify that the Server Manager that is on the SQL Server source system is selected.
  3. In the
    Instance
     column, select the SQL Server instance for which you want to edit settings.
    To update the list of SQL Server instances, click
    Refresh
    .
  4. In the
    State
     column, select the
    DEDIC_ADMIN_CONN(MULTI)
     or
    DEDIC_ADMIN_CONN(SINGLE)
     option to indicate the dedicated administrator connection state.
    A dedicated administrator connection provides administrator access to a running instance of the SQL Server Database Engine and supports encryption and other security features of Microsoft SQL Server. The Server Manager requires the dedicated administrator connection to enable or disable SQL Server Change Data Capture and to get the Change Data Capture status for each table.
    Options are:
    • NOT_RUNNING
      . The SQL Server instance is inactive.
    • RUNNING
      . The SQL Server instance runs without a dedicated administrator connection. Microsoft SQL Server Standard Edition instances use this option by default. The Data Replication Console cannot get the Change Data Capture status from instances that are running in this mode.
    • DEDIC_ADMIN_CONN(MULTI)
      . The SQL Server instance runs with a dedicated administrator connection in multiple-user mode. Microsoft SQL Server Enterprise Edition instances use this option by default. Informatica recommends that you use this option.
    • DEDIC_ADMIN_CONN(SINGLE)
      . The SQL Server instance runs with a dedicated administrator connection in single-user mode.
  5. Verify that the connection information that the Server Manager uses to connect to the SQL Server instance is correct:
    1. Click the
      Account
       button.
      The
      Microsoft SQL Server Account
       dialog box appears.
    2. Verify or edit the connection information.
      The following table describes the connection fields:
      Field
      Description
      Use Windows account
      Clear this check box and complete the
      Login
       and
      Password
       fields in the following cases:
      • If the Server Manager runs under a Windows user account that does not have sufficient privileges to enable or disable SQL Server Change Data Capture
      • If the Server Manager runs on Linux or UNIX
      Login
      If you cleared the
      Use Windows account
       option, enter a user name that has privileges to enable or disable Change Data Capture.
      Password
      If you cleared the
      Use Windows account
       option, enter a valid password for the specified user.
      Use default
      To use the default SQL Server Native Client driver connection string that is initially shown in the
      Custom connection string
       or
      DAC Custom connection string
       fields to connect to the SQL Server instance and get the SQL Server Change Data Capture status of each database, select this check box. To specify another connection string in either the
      Custom connection string
       or
      DAC Custom connection string
       field, clear this check box.
      Custom connection string
      Enter a connection string that the Server Manager uses to connect to the Microsoft SQL Server instance. The Server Manager also uses this connection string to enable Change Data Capture for Microsoft SQL Server Enterprise Edition sources.
      DAC Custom connection string
      Enter a dedicated administrator connection (DAC) string that the Server Manager uses to get the Change Data Capture status for each database. The Server Manager also uses this connection string to enable Change Data Capture for Microsoft SQL Server Standard Edition sources in single-user mode.
      For Microsoft SQL Server Cluster sources, provide the DAC string in the following format: 
      DRIVER=
      SQL_Server_ODBC_driver
      ;Server=
      virtual_IP
      ;
Network=DBMSSOCN;Trusted_Connection=Yes;Port=
      port
      ;
Address=
      virtual_IP,port
      ;
    3. Click
      Save
      .
  6. For Microsoft SQL Server Enterprise Edition sources, use the
    Restart instance
     check box to indicate whether the Server Manager restarts the SQL Server instance in single-user mode to enable a dedicated administrator connection to the source database when you edit the Change Data Capture settings for source tables on the
    Map Tables
     tab.
    • Select the check box to restart the SQL Server instance in single-user mode. SQL Server does not generate the SQL Server CDC capture tables. Data Replication does not require these tables for replication processing.
    • Do not select the check box to run the SQL Server instance in multiple-user mode. If the Server Manager runs on the same system as the SQL Server instance, you do not need to restart the SQL Server instance when you edit the Change Data Capture settings for source tables on the
      Map Tables
       tab.
    By default, the check box is not selected.
    • If the list of instances in the
      Microsoft SQL Server Instances
       dialog box contains only SQL Server Standard Edition instances, the
      Restart instance
       check box is selected and unavailable for editing.
    • For all listed SQL Server Standard Edition instances, the Server Manager must restart the SQL Server instance in single-user mode to enable a dedicated administrator connection to the source database when you edit the Change Data Capture settings for source tables on the
      Map Tables
       tab.
    • If the
      Restart instance
       check box is selected, and you edit the Change Data Capture settings for source tables when multiple SQL Server sources are running on the source system, Data Replication might display an error. To avoid this error, Informatica recommends that you ensure that only the SQL Server Database Engine service is running before editing Change Data Capture settings.
    • The
      Restart instance
       check box is equivalent to the Server Manager RestartMSSQLInstance advanced property. When you edit either the
      Restart instance
       check box or the Server Manager property for Microsoft SQL Server Enterprise Edition sources, the other value is updated to the equivalent setting. For more information, see Editing Properties for the Main Server or a Subserver.
  7. In the
    SQL Server CDC Jobs
     list, select one of the following options to indicate how to handle the CDC capture and cleanup jobs that SQL Server generates for databases with Change Data Capture enabled:
    • Never disable
      . Do not disable the SQL Server CDC jobs. Select this option if you do not want Data Replication to disable the SQL Server CDC jobs because the jobs are already disabled from Microsoft SQL Server Management Studio or you accept that Data Replication might not capture all of the change data. This option is the default option.
    • Disable only if CDC is not enabled
      . Disable the SQL Server CDC jobs if SQL Server Change Data Capture is not enabled for any source tables when you create the configuration in Data Replication. If SQL Server Change Data Capture is enabled for any source table, the SQL Server CDC jobs are not disabled.
    • Always disable
      . Disable the SQL Server CDC jobs regardless of whether SQL Server Change Data Capture is enabled or not enabled for the source tables.
    Default value is
    Never disable
    .
    If you select
    Never disable
     or
    Disable only if CDC is not enabled
     and the Server Manager does not disable the SQL Server CDC jobs, disable Data Replication management of the secondary truncation checkpoint on the
    Runtime Settings
     tab >
    General
     view. Also, ensure that the Extractor reads both the transaction and backup logs. Otherwise, Data Replication might not capture all of the change data.
    The
    SQL Server CDC Jobs
     options are equivalent to the valid values for the Server Manager CdcJobsDisableMode advanced property. When you edit either the
    SQL Server CDC Jobs
     option or the Server Manager property, the other value is updated to the equivalent setting. For more information, see Editing Properties for the Main Server or a Subserver.
  8. Click
    Close
    .
0 COMMENTS
We’d like to hear from you!