SQL Server initial setup
For initial load, see SQL Server initial load.
For continuous real-time replication using MS SQL Reader or MSJet, significant setup is required. See SQL Server continuous real-time replication using CDC.
For continuous incremental replication using Incremental Batch Reader, see SQL Server continuous incremental replication.
The following discussions of networking, security, and Forwarding Agent setup apply to both initial load and either approach to continuous replication, and to both MS SQL Reader and MSJet.
Networking setup
The following applies to both initial load and any approach to continuous replication.
You need to establish proper network connectivity between your Striim environment and the SQL Server database. This involves configuring network access, firewall rules, and connection parameters to ensure reliable communication.
Ensure that the Striim server can connect to your SQL Server database on the correct port (typically 1433). If your SQL Server database is behind a firewall, you need to configure the necessary firewall rules to allow inbound connections from the Striim server. For cloud deployments, such as Amazon RDS for SQL Server, you need to configure security groups to allow access from your Striim instance.
Also consider network latency and bandwidth requirements, especially for high-volume CDC scenarios. For optimal performance, minimize the network latency between Striim and SQL Server.
For secure connections, you can configure SSL/TLS encryption between Striim and SQL Server. This requires setting up SSL certificates and configuring both SQL Server and Striim to use encrypted connections.
Security
Security configuration for SQL Server integration involves multiple layers, including authentication, authorization, network security, and data protection measures.
You must implement proper authentication mechanisms between Striim and SQL Server. This includes creating dedicated database users with minimal required privileges following the principle of least privilege. You should avoid using administrative accounts and instead create specific users for Striim operations with only the necessary permissions for the tables and operations required.
For enhanced security, you can implement SSL/TLS encryption for all communication between Striim and SQL Server. This protects data in transit, ensuring that credentials and sensitive data are not transmitted in clear text.
Network security considerations include implementing proper firewall rules.
You should implement access control at multiple levels, including database-level permissions, schema-level access controls, and table-level privileges. You should regularly review and audit the permissions granted to Striim users and implement proper password policies and rotation procedures for service accounts.