Microsoft SQL Server Topics
Syniti Replicate supports use of Microsoft SQL Server for:
- Refresh replications, both source and target
- Mirroring replications, both source and target. For source connections, Syniti Replicate offers the Log Server Agent, transaction log support and triggers
- Synchronization
When setting up SQL Server for use with the Log Server Agent or the transaction log:
-
Define two connections to the database: a non-system administrator connection and a system administrator connection. (DBMS tools)
-
Set up the distributor (Management Center)
-
Establish primary keys on tables (or use trigger-based replication option.) (Management Center or DBMS tools)
This section contains topics that describe:
- SQL Server Transactional Replication Settings (in the Enable Transactional Replication wizard or the Manage Transactional Log Settings dialog)
- SQL Server Transaction Log Information (in the Create Replication wizard and Create Multiple Replications Source Log Info screen)
Syniti Replicate Permissions to enable Trigger-Based Replications for SQL Server Sources
To create a trigger-based replication using SQL Server as the source, the following permissions are required on the source connection:
-
Permission to create connections
-
Create table privileges when creating the source SQL connection, so that the master table can be created in the database you choose on that instance.
NOTE: This is only required when the connection is initially created in Syniti Replicate. After the master table is created, you can revoke the privilege.
-
Table-level ALTER permissions to create the replications, which allows the creation of the triggers. These permissions are required each time a replication is created. If, at some point no other replications will be created, that table-level ALTER permission can be revoked.
NOTE: The security context of a trigger, unless otherwise specified on the trigger's definition, is the context of the principal that caused the trigger to fire (i.e., the caller). Meaning that if the changes on the SQL source are caused by a user called SAPuser, then that user must be able to insert, update and delete records from the master table that is created or chosen when the connection is created.
In other words, the user that will write records on the master table is not really going to be the Syniti Replicate user. The Syniti Replicate user creates the triggers on the source tables, but the data is inserted by whoever made the actual change on the source table. This is usually acceptable because it's a user that's db_owner on most databases when they're application users, but it's good to keep in mind.
If there are special requirements about the above (the security principal that actually writes data to the master table), you could alter the trigger directly in SQL Server once it's created and use an execute as clause. For example CREATE TRIGGER dbo.triggername1 WITH EXECUTE AS OWNER, or CREATE TRIGGER dbo.triggername1 WITH EXECUTE AS HOSTNAME\Username (if using a specific user, that user must have IMPERSONATE permissions to call it from an EXECUTE AS clause).
Further Resources
- For a complete up-to-date list of supported sources and targets, and information on where to obtain a .NET provider, check the Supported .NET Providers article in the Help Center.
- The Help Center also provides database-specific setup notes in PDF format.