Requirements for SQL Server

From HVR
Jump to: navigation, search

This section describes the requirements, access privileges, and other features of HVR when using SQL Server for replication. For information about the capabilities supported by HVR on SQL Server, see Capabilities for SQL Server.

For information about compatibility and supported versions of SQL Server with HVR platforms, see Platform Compatibility Matrix.

To quickly setup replication using SQL Server, see Quick Start for HVR on SQL Server.

SQL Server
Capture Hub Integrate
Icon-Yes.png Icon-Yes.png Icon-Yes.png

Location Connection

This section lists and describes the connection details required for creating SQL Server location in HVR.

SC-Hvr-Location SQLServer.png
Field Description
Database Connection
Server The name of the machine on which SQL Server is running and the Port number or SQL Server instance name. The following formats are supported:
  • <server name> : This format uses the server name and default port to connect to the machine on which SQL Server is running.   Example: myserver
  • <server name>,<port number> : This format allows you to specify the port number along with the server name to connect to the machine on which SQL Server is running. This format is used when using custom port for connection.   Example: myserver,1435
  • <server name>\<server instance name> : This format uses the server name and server instance name to connect to the machine on which SQL Server is running. This format is not supported on Linux. Also, it is not supported when Integrate /NoTriggerFiring is to be defined for this location.   Example: myserver\HVR5048
Database The name of the SQL Server database which is to be used for replication.
  Example: mytestdb
User The username to connect HVR to SQL Server Database. This user should be defined with SQL Server Authentication or Windows Authentication.
  Example: hvruser
Note: If Windows Authentication is used for connecting to SQL Server Database, the User and Password field should be left blank (empty).
Password The password of the User to connect HVR to SQL Server Database.
ODBC Driver The user defined (installed) ODBC driver to connect HVR to the SQL Server Database.


Connecting to SQL Server from HUB machine

To connect from a HVR hub machine to a remote SQL Server database there are three options:

  1. SC-Hvr-NewLocation SQLServer HvrProtocol.png
    Connect to a HVR installation running on the machine containing the SQL Server database using HVR’s protocol on a special TCP/IP port number, e.g. 4343. On Windows this port is serviced by a Windows service called HVR Remote Listener. This option gives the best performance, but is the most intrusive.

  2. SC-Hvr-NewLocation SQLServer SQLServerProtocol.png
    Connect to a SQL Server database using the SQL Server protocol (equivalent to TNS). To use this method, Microsoft SQL Server Native Client should be installed on the machine from which HVR will connect to SQL Server database.

  3. SC-Hvr-NewLocation SQLServer HvrProtocolProxy.png
    Connect first to a HVR installation on an extra machine using HVR’s protocol (a sort of proxy) and then connect from there to the SQL Server database machine using the SQL Server protocol (equivalent to TNS). This option is useful when connecting from a Unix/Linux hub to avoid an (intrusive) installation of HVR’s software on the machine containing the SQL Server database.


All three of the above connections can be used for both capture and integration. Specifically: HVR’s log-based capture can get changes from a database without HVR’s executables being physically installed on the source machine.

SQL Server on Linux

HVR supports Capture and Integrate for SQL Server running on Linux.

  • HVR requires the Microsoft ODBC Driver for SQL Server.
    1. Download and install the latest Microsoft ODBC Driver for SQL Server on Linux. For more information, refer to Installing the Microsoft ODBC Driver for SQL Server on Linux and macOS
    2. Create a symbolic link (symlink) for the ODBC driver. Following is an example for Microsoft ODBC Driver for SQL Server libmsodbcsql-17.0.so.1.1,
    3. ln -s  /opt/microsoft/msodbcsql/lib64/libmsodbcsql-17.0.so.1.1  $HVR_HOME/lib/libmsodbcsql-17.so
      
  • It is also recommended to verify the dynamic dependencies. For example,
  • ldd $HVR_HOME/lib/hvr_ms17.so
    
  • The HVR User should have read access to the .mdf and .ldf files. For this, the HVR User should typically be added to the Operating System user group mssql.

Hub

HVR allows you to create hub database in SQL Server. The hub database is a small database present on the hub machine which HVR uses to control its replication activities. This database stores HVR catalog tables that hold all specifications of replication such as the names of the replicated databases, the list of replicated tables, and the replication direction.

Grants for Hub Database

To capture changes from source database or to integrate changes into target database, the HVR hub database(User) requires the privileges mentioned in this section.

It is recommended to create a new database (schema) for HVR hub. If an existing database is to be used for HVR Hub, then the HVR's catalog tables can be separated by creating a new database schema and associating it with HVR's user as follows:

create schema hvrschema
grant create table to hvruser
grant create procedure to hvruser
grant select, insert, delete, update on schema::hvrschema to hvruser
grant control on schema::hvrschema to hvruser
alter user hvruser with default_schema=hvrschema

Capture

HVR allows you to capture changes from SQL Server database. The two log read methods supported for capturing from SQL Server are DIRECT (capture changes directly from SQL Server's logging files ) and SQL (capture changes over an SQL connection).

By default, HVR uses the DIRECT method to capture changes from the SQL Server's current and backup transaction log files. It is not required to define action Capture /LogReadMethod=DIRECT. This capture method is the fastest option and supports capture from SQL Server Always On AG secondary database. This method requires the following:

  • The HVR agent must be installed on the SQL Server source database server.
  • The database account must have SysAdmin (SA) privileges.
  • On Windows, the HVR User must have Windows Administrator privileges.
  • On Linux, the HVR User must have read access to the .mdf and .ldf files.

To capture changes using the SQL method, define action Capture /LogReadMethod=SQL. This method is much slower than the DIRECT method, exposes additional load on the source database and may require incomplete row augmenting. This method does not require HVR agent to be installed on the SQL Server source database server and can work with reduced permissions.

Grants and Steps for Log-Based Capture

HVR's log-based capture using SQL method supports three permission models: SysAdmin, DbOwner, and Minimal. However, the DIRECT method supports only the SysAdmin model.

Note: The DbOwner and Minimal models are only available for SQL Server 2012 and above. For older versions, the SysAdmin model should be used.
  • SysAdmin
  • The HVR User should be granted sysadmin role. There is no need for operators to perform special SQL statements manually.
    For this permission model, perform only install steps 1 and 2 below; the others are unnecessary.

    Note: This permission model cannot be used when Capture /ArchiveLogOnly is used with DIRECT log read method.
  • DbOwner
  • The HVR User should be granted a db_owner role for the source database, but not sysadmin role. An operator must perform a few special SQL statements manually only when setting up a new database for capture. For this permission model, perform only install steps 1-4 below; numbers 5-7 are unnecessary.

  • Minimal
  • The HVR User does not require sysadmin or db_owner roles at runtime. But whenever an HVR Initialize command is run (for example to add a new table to a channel) then a user with db_owner privilege must perform SQL statements manually. For this permission model, perform all install steps below; numbers 1-7.

Installation steps depend on the setting of action Capture /SupplementalLogging. It can use elements of SQL Server's Change Data Capture ('CDC') feature or elements of SQL Server's own replication components (called 'articles'), or both. Articles can only be created on tables with a primary key. CDC tables are used by default on SQL Server Enterprise and Developer editions. Articles are always used for SQL Server Standard edition (prior to SQL Server 2016 Service Pack 1) to implement supplemental logging.

Installation steps

  1. For log-based capture from SQL Server Enterprise Edition or Developer Edition, if articles are used (see above) HVR requires that SQL Server Replication Components option is installed.
  2. This step is needed once when HVR is installed for an SQL Server instance.

  3. If articles are used (see above) a user with sysadmin must create a distribution database for the SQL Server instance, unless one already exists. To do this a user with sysadmin should run SQL Server wizard Configure Distribution Wizard. which can be run by clicking Replication > Configure Distribution... Any database name can be supplied (just click Next > Next > Next).
  4. This step is needed once when HVR is installed for an SQL Server instance.

    If Always On AG is installed and articles are used then only one distribution database should be configured. Either this can be setup inside the first node and the other nodes get a distributor which points to it. Or the distribution database can be located outside the Always On AG cluster and each node gets a distributor which points to it there.

  5. [For this step and subsequent steps the HVR binaries must already be installed].
  6. For log read method SQL, a user with sysadmin must create a special ‘wrapper’ SQL procedure called sp_hvr_dblog so that the HVR can call SQL Server’s read-only function fn_dump_dblog. This must be done inside SQL Server database’s special database msdb, not the actual capture database.

    The SQL to create these procedures is in file hvrcapsysadmin.sql in directory %HVR_HOME%\sql\sqlserver.

    The HVR user must then be allowed to execute this procedure. For this the HVR User (e.g. hvruser) must be added to the special msdb database and the following grant must be done there:

    use msdb
    create user hvruser for login hvruser
    grant execute on sp_hvr_dblog to hvruser
    grant execute on sp_hvr_dbcc to hvruser
    

    This step is needed once when HVR is installed for an SQL Server instance. But if Always On AG is installed then this step is needed on each Always On AG node.

  7. A user with sysadmin must grant the HVR user a special read-only privilege in the master database.
  8. use master
    grant view server state to hvruser
    

    This step is needed once when HVR is installed for an SQL Server instance. But if Always On AG is installed then this step is needed on each Always On AG node.

  9. If 'articles' are used or the log read method is SQL then a user with db_owner (or sysadmin) must create ‘wrapper’ SQL procedures in each capture database so that HVR can call SQL Server’s read-only procedures sp_helppublication, sp_helparticle and fn_dblog.
    The SQL to create these three read-only procedures is in file hvrcapdbowner.sql in directory %HVR_HOME%\sql\sqlserver.
    The HVR user must then be allowed to execute these procedures. The following grants must be done inside each capture database:
  10. use capdb
    grant execute on sp_hvr_check_publication to hvruser
    grant execute on sp_hvr_check_article to hvruser
    grant execute on sp_hvr_dblog to hvruser
    grant execute on sp_hvr_repldone to hvruser
    grant execute on sp_hvr_repltrans to hvruser
    

    This step is needed once when each new source database is being setup.

  11. A user with db_owner (or sysadmin) must grant HVR a read-only privilege.
  12. use capdb
    alter role db_datareader add member hvruser
    

    This step is needed once when each new source database is being setup.

  13. When an HVR Initialize command is performed it may need to perform SQL statements that would require sysadmin or db_owner privilege. One example is that it may need to create an ‘article’ on a replicated table to track its changes. In that case HVR Initialize will write a script containing necessary SQL statements, and then show a popup asking for this file to be performed. The file will be written in directory %HVR_CONFIG%\files on the capture machine; its exact filename and the necessary permissions level is shown in the error message. The first time HVR Initialize gives this message then a user with sysadmin privilege must then perform these SQL statements. Subsequently these SQL statements can be performed by a user that just has db_owner privilege.

Capturing from SQL Server Always On Availability Groups

HVR allows you to capture from SQL Server Always On Availability Groups (AG) - a technology which provides High-Availability (HA) and Disaster-Recovery (DR) solution in SQL Server.

When using DIRECT method, HVR can be configured to capture from either the primary or secondary node (active or passive).

However, when using SQL method, HVR can be configured to capture only from the primary node.

Configuring Failover for Connections to SQL Server Always On AG

If HVR is connecting using its own protocol to a database inside an SQL Server Always On AG, the following is required:

  1. Create an HVR Remote Listener on each node
  2. Inside Failover Cluster Manager configure a Role with a static IP address that controls the HVR Remote Listener service on all nodes. Run Configure Role Wizard, Next > Generic Service > HVR Remote Listener > Name > Next > Next > Next > Finish. To configure a static IP address, click on Resources > Name> IP address> then change it to an available static address.
  3. Configure a Group Listener inside the Availability Group. Start Management Studio on the primary node, click AlwaysOn High Availability > Availability Groups > Name > Availability Group Listeners > Add Listener ….

    For Always On AG inside Azure an extra step is required:
  4. Configure an Internal or External Azure load balancer for the HVR Remote Listener using Powershell. Then attach each Azure node to this load balancer. When this is done, fill in the IP address of the Azure load balancer into the Node field in the Connect to HVR on remote machine section of the HVR location dialog.

HVR can now connect to Node as configured in step 1 or step 4 and Server as configured in Step 3.

Configuring Backup Mode and Transaction Archive Retention

HVR log-based capture requires that the source database is in Full recovery model and a full database backup has been done since this was enabled. Normally HVR reads changes from the 'online' transaction log file, but if HVR is interrupted (say for 2 hours) then it must be able to read from transaction log backup files to capture the older changes. HVR is not interested in full or incremental backups; it only reads transaction log backup files.

Transaction log (archive) retention: If a backup process has already moved these files to tape and deleted them, then HVR capture will give an error and an HVR Refresh will be needed before replication can be restarted. The amount of 'retention' needed (in hours or days) depends on organization factors (how real-time must it be?) and practical issues (does a refresh take 1 hour or 24 hours?).

HVR normally locates the transaction log backup files by querying the backup history tables in the msdb database. But if Always On AG is configured then this information source is not available on all nodes. So when HVR is used with Always On AG, the transaction log backups must be made on a directory which is both accessible from all Always On AG nodes and also from the machine where the HVR capture process is running (if this is different) via the same path name. HVR should be configured to find these files by defining action Capture with two additional parameters /ArchiveLogPath and /ArchiveLogFormat. The parameter /ArchiveLogPath should point to a file system directory which HVR will use for searching directly for the transaction log backup files, instead of querying msdb. The parameter /ArchiveLogFormat should specify a pattern for matching files in that directory. The pattern can contain these special characters:

Pattern Description
* Wildcard to match zero or more characters.
%d Database name. This is not case sensitive.
%Y Year (up to 4 digit decimal integer).
%M Month (up to 2 digit decimal integer)
%D Day (up to 2 digit decimal integer)
%h Hours (up to 2 digit decimal integer)
%m Minutes (up to 2 digit decimal integer)
%s Seconds (up to 2 digit decimal integer)
%n File sequence number (up to 64 bit decimal integer)
%% Matches %

All other characters must match exactly. HVR uses the %Y, %M, %D, %h, %m, %s and %n values to order files.

Dropping the Source Database

Depending on the setting of action Capture /SupplementalLogging HVR will use some of SQL Server's own 'replication components' or it will use SQL Server's Change Data Capture (CDC) feature.

Based on this, HVR may enable the source database for publication, which will mean that attempts to drop the database will give an SQL Server error.

Alternatively HVR may enable Change Data Capture (CDC) on source databases, which will also mean attempts to drop the database may also give an SQL Server error because of the running CDC capture and cleanup jobs.

When command HVR Initialize is used with Drop Objects (option -d) then it will disable the ‘publish’ replication option if there are no other systems capturing from that database. It will also disable the CDC for the database, if there are no other CDC table instances exist in that database. The database can then be dropped.

To drop the database immediately (without running the HVR Initialize first) the sysadmin must perform the following SQL statement:

exec sp_replicationdboption 'capdb', 'publish', 'false'
use [capdb]
exec sp_cdc_disable_db

Grants for Trigger-Based Capture

HVR allows you to perform trigger-based capture (Capture /TriggerBased) from SQL Server. To enable trigger‑based capture for SQL Server:

  • HVR's user should be made a database owner (db_owner role).
  • The extended stored procedure hvrevent should normally be installed on the capture machine. This is not needed if parameters Capture /TriggerBased is not defined or /ToggleFrequency or Scheduling/CaptureStartTimes or /CaptureOnceOnStart are defined. This step must be performed by a user that is a member of the system administrator role. For more information, see Installing HVR on Windows.

Integrate and Refresh (Target)

HVR allows you to integrate changes into SQL Server database.

Grants for HVR on Target Database

This section provides information about the user privileges required for replicating changes into SQL Server database using HVR Refresh.

When replicating changes into a target SQL Server database, HVR supports the following two permission models: DbOwner, and Minimal.

  • DbOwner
  • In this permission model, the HVR User must be made a database owner (db_owner role). Normally, the database objects which HVR sometimes creates will be part of the dbo schema as the replicated tables. Alternatively, these HVR database objects can be put in a special database schema so that they are not visible to other users. The following SQL is needed:

    create schema hvrschema
    grant control on schema::hvrschema to hvruser
    alter user hvruser with default_schema=hvrschema
    
  • Minimal
  • In this permission model, the HVR User does not need to be a database owner. This model cannot use parameter /Schema to change tables with a different owner. The following SQL is needed so that HVR can create its own tables:

    grant create table to hvruser
    create schema hvrschema
    grant control on schema::hvrschema to hvruser
    alter user hvruser with default_schema=hvrschema
    

    If action Integrate /DbProc is defined, then create procedure privilege is needed.

Compare and Refresh (Source)

To perform HVR Compare or HVR Refresh (in Source Database), the HVR User requires the privileges mentioned in this section.

When HVR is reading rows from a database (no capture) it supports two permission models: DbOwner model, and Minimal model.

Grants for DbOwner privilege mode
For this model, the HVR User must be made owner of the source database (db_owner role).
Grants for Minimal privilege mode
For this model, the HVR User does not need to be a database owner.

If the HVR User needs to select from tables in another schema (for example if action TableProperties /Schema is defined), then select privilege should be granted.

grant select to hvruser		       -- Let HVR read all tables
grant select on schema::dbo to hvruser    -- Let HVR only read DBO tables