Concept
Control can be configured to have reports pull data from a replicated database.
Cautions
If set up improperly loss of data is possible. Be sure to back up the live database before continuing. The following steps should only be performed by a SQL Database Administrator or experienced SQL Server Users. This configuration is not supported under the Standard Support Agreement.
Steps
Install Replication on both the Publisher and Subscriber
https://technet.microsoft.com/en-us/library/ms172376%28v=sql.105%29.aspx
Publisher = SQL instance which contains database you want to replicate.
Subsciber = SQL instance which will contain the duplicate (replicated) database.
Set up Replication Users and Permissions
https://msdn.microsoft.com/en-us/library/aa337433.aspx
Note: Alternatively you can use an Administrator account for the PC. However this is not recommended for security reasons.
Example setup steps for Publishing Data Using Transactional Replication
To create a publication and define articles
- Connect to the Publisher in SQL Server Management Studio, and then expand the server node.
- Expand the Replication folder, right-click the Local Publications folder, and click New Publication.
- The Publication Configuration Wizard launches.
- On the Publication Database page, select StoreData , and then click Next.
- On the Publication Type page, select Transactional publication, and then click Next.
- On the Articles page, check each of the boxes shown. Click Next.
- On the Filter Table Rows page, click next.
- Select the Create a snapshot immediately and keep the snapshot available to initialize subscriptions check box, and click Next.
- On the Agent Security page, clear Use the security settings from the Snapshot Agent check box.
- Click Security Settings for the Snapshot Agent, enter \repl_snapshot in the Process account box, supply the password for this account, and then click OK.
- Repeat the previous step to set repl_logreader as the process account for the Log Reader Agent, and then click Finish.
- On the Complete the Wizard page, type StoreDataRepl in the Publication name box, and click Finish.
- After the publication is created, click Close to complete the wizard.
To view the status of snapshot generation
- Connect to the Publisher in SQL Server Management Studio, expand the server node, and then expand the Replication folder.
- In the Local Publications folder, right-click StoreDataRepl, and then click View Snapshot Agent Status.
- The current status of the Snapshot Agent job for the publication is displayed. Verify that the snapshot job has succeeded before you continue to the next lesson.
To add the Distribution Agent login to the PAL
- Connect to the Publisher in SQL Server Management Studio, expand the server node, and then expand the Replication folder.
- In the Local Publications folder, right-click StoreDataRepl, and then click Properties.
- The Publication Properties dialog box is displayed.
- Select the Publication Access List page, and click Add.
- In the Add Publication Access dialog box, select \repl_distribution and click OK. Click OK.
To create the subscription
- Connect to the Publisher in SQL Server Management Studio, expand the server node, and then expand the Replication folder.
- In the Local Publications folder, right-click the StoreDataRepl publication, and then click New Subscriptions.
- The New Subscription Wizard launches.
- On the Publication page, select StoreDataRepl, and then click Next.
- On the Distribution Agent Location page, select Run all agents at the Distributor, and then click Next.
- On the Subscribers page, if the name of the Subscriber instance is not displayed, click Add Subscriber, click Add SQL Server Subscriber, enter the Subscriber instance name in the Connect to Server dialog box, and then click Connect.
- On the Subscribers page, select the instance name of the Subscriber server, and select under Subscription Database.
- On the New Database dialog box, enter StoreDataRepl in the Database name box, click OK, and then click Next.
- In the Distribution Agent Security dialog box, click the ellipsis (…) button, enter \repl_distribution in the Process account box, enter the password for this account, click OK, and then click Next.
- Click Finish to accept the default values on the remaining pages and complete the wizard.
Setting database permissions at the Subscriber
- Connect to the Subscriber in SQL Server Management Studio, expand Databases, StoreDataRepl, and Security, right-click Users, and then select New User.
- On the General page, in the User type list, select Windows user.
- Select the User name box and click the ellipsis (…) button, in the Enter the object name to select box type \repl_distribution, click Check Names, and then click OK.
- On the Membership page, in Database role membership area, select db_owner, and then click OK to create the user.
To view the synchronization status of the subscription
- Connect to the Publisher in SQL Server Management Studio, expand the server node, and then expand the Replication folder.
- In the Local Publications folder, expand the StoreDataRepl publication, right-click the subscription in the StoreDataRepl database, and then click View Synchronization Status.
- The current synchronization status of the subscription is displayed.
- If the subscription is not visible under StoreDataRepl, press F5 to refresh the list.
To insert a tracer token and view information on the token
- Connect to the Publisher in SQL Server Management Studio, expand the server node, right-click the Replication folder, and then click Launch Replication Monitor.
- Replication Monitor launches.
- Expand a Publisher group in the left pane, expand the Publisher instance, and then click the StoreDataRepl publication.
- Click the Tracer Tokens tab.
- Click Insert Tracer.
- View elapsed time for the tracer token in the following columns: Publisher to Distributor, Distributor to Subscriber, Total Latency. A value of Pending indicates that the token has not reached a given point.
- Note: The Tracer should complete in a matter of seconds (less than 10). If not, something is wrong.
Control Setup and Configuration
- Upgrade Control and SSLIP to version 5.60.1505.101 or later.
- Add the following options to the Control_Options.txt file:
- CustomReportsDBServer - Overrides the database server used for custom reports.
- CustomReportsDBPath - Overrides the database used for custom reports.
- SystemReportsDBServer - Overrides the database server used for system reports.
- SystemReportsDBPath - Overrides the database used for system reports.
Example:
CustomReportsDBServer=MyServer2\Cyrious
CustomReportsDBPath=StoreDataRepl
- Re-start Control, SSLIP and SQL.
- Log into Control and preview a invoice.
Note: To test reports to ensure they are working, you can change the description of an order in the TransHeader on the replicated database. Then preview an invoice for that order and notice the description is different than the one on the order in Control.
Troubleshooting
Error: SQL Server is unable to connect to server 'SERVER\Instance'
You may receive this error when configuring the Distribution agent, and the “actual server name” at the bottom of the error does not match the server name mentioned at the top of the error message (see example below).
Resolution 1:
- Execute the following in SQL:
select name from sys.servers
- Open a CMD prompt type HOSTNMAME and press Enter.
- Compare the two results. SQL Should be updated if they are different
- Use the following SQL to change the Name in sys.servers:
-- replace sql instance in quotes with the Name in sys.servers exec sp_dropserver 'CYRIOUS46\CYRIOUS' -- replace the sql instance in quotes with correct server name and instance name. exec sp_addserver 'CYRIOUS\CYRIOUS'
- Re-start SQL, Cyrious Services, SSLIP, etc.
Resolution 2:
Un-install SQL Server.
Re-install SQL Server.
Pending Tracer
If the tracer from the Publisher to Distributor finishes in seconds but says Pending for Distributor to Subscriber, check permissions on the Distributor Agent.
Source
Contributor: Steve H., Cyrious Software Support
Date: 5/4/2015
Version: Control 5.6