SQL to SQL Replication

Yahoo Archive
1
We are using SQL transactional replication to maintain a reporting server, works fine. Also works for EPM. Send me an email if you would like to discuss.

--- In vantage@yahoogroups.com, "Ned C." <TechnoBabbly@...> wrote:
>
> I am just wondering what people running with SQL as their backend are doing
> for SQL to SQL Replication of the Epicor Database out there?
>
>
>
> I have gotten some conflicting information about it and want to get some
> information from people who are actually using it and what you are doing
> with it. The main issue that keeps being brought up in conversations deals
> with Primary Keys in the tables not existing for some of them, therefore
> being a problem with the straight SQL to SQL replication.
>
>
>
> I want to simplify the use of some of the 3rd party products, EPM more
> specifically in this very instance, so that it will have a better, more up
> to date source for the information being used by EPM or any product,
> including eventual SSRS usage.
>
>
>
> The main problem currently is that the system I am working with is trying to
> use the Epicor Replication which relies on Sonic, and the use of an OpenEdge
> product to be replicating data one direction between SQL databases is just
> completely ridiculous and inefficient. The fact that the replication doesn't
> work properly only makes the issue worse.
>
>
> Right now there is a manual backup and restore that is being done to the
> secondary server for "data replication".
>
>
>
> And with a separate but related issue maybe someone has dealt with, I have
> tried scripting a backup and restore, but the restoration "WITH MOVE" of
> Full Text Catalogs files to a new file location has been a sticking point,
> and wondering if anyone else has worked through something similar.
>
>
>
>
>
>
>
> [Non-text portions of this message have been removed]
>
2
I am just wondering what people running with SQL as their backend are doing
for SQL to SQL Replication of the Epicor Database out there?



I have gotten some conflicting information about it and want to get some
information from people who are actually using it and what you are doing
with it. The main issue that keeps being brought up in conversations deals
with Primary Keys in the tables not existing for some of them, therefore
being a problem with the straight SQL to SQL replication.



I want to simplify the use of some of the 3rd party products, EPM more
specifically in this very instance, so that it will have a better, more up
to date source for the information being used by EPM or any product,
including eventual SSRS usage.



The main problem currently is that the system I am working with is trying to
use the Epicor Replication which relies on Sonic, and the use of an OpenEdge
product to be replicating data one direction between SQL databases is just
completely ridiculous and inefficient. The fact that the replication doesn't
work properly only makes the issue worse.


Right now there is a manual backup and restore that is being done to the
secondary server for "data replication".



And with a separate but related issue maybe someone has dealt with, I have
tried scripting a backup and restore, but the restoration "WITH MOVE" of
Full Text Catalogs files to a new file location has been a sticking point,
and wondering if anyone else has worked through something similar.







[Non-text portions of this message have been removed]
3
Ned, are you doing replication due to multiple sites/plants in geographically different areas? Or simply having a replicated database for your bolt-ons and reporting?

Are you making changes in your “child” db or is it for read-only actions?

If you’re simply looking at having a mirrored db for SSRS, etc, then I would see no reason why you couldn’t use the native SQL Replication Services.



From: Ned C.
Sent: Thursday, March 15, 2012 2:58 PM
To: vantage@yahoogroups.com
Subject: [Vantage] SQL to SQL Replication


I am just wondering what people running with SQL as their backend are doing
for SQL to SQL Replication of the Epicor Database out there?

I have gotten some conflicting information about it and want to get some
information from people who are actually using it and what you are doing
with it. The main issue that keeps being brought up in conversations deals
with Primary Keys in the tables not existing for some of them, therefore
being a problem with the straight SQL to SQL replication.

I want to simplify the use of some of the 3rd party products, EPM more
specifically in this very instance, so that it will have a better, more up
to date source for the information being used by EPM or any product,
including eventual SSRS usage.

The main problem currently is that the system I am working with is trying to
use the Epicor Replication which relies on Sonic, and the use of an OpenEdge
product to be replicating data one direction between SQL databases is just
completely ridiculous and inefficient. The fact that the replication doesn't
work properly only makes the issue worse.

Right now there is a manual backup and restore that is being done to the
secondary server for "data replication".

And with a separate but related issue maybe someone has dealt with, I have
tried scripting a backup and restore, but the restoration "WITH MOVE" of
Full Text Catalogs files to a new file location has been a sticking point,
and wondering if anyone else has worked through something similar.

[Non-text portions of this message have been removed]




No virus found in this message.
Checked by AVG - www.avg.com
Version: 2012.0.1913 / Virus Database: 2114/4872 - Release Date: 03/15/12


[Non-text portions of this message have been removed]
4
I know I should be able to, but my customer keeps being told we can't do it, and I am just looking for some actual information from people who are using it and how their experiences have been.

This is just one way replication for read only purposes.

-----Original Message-----
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of Vic Drecchio
Sent: Thursday, March 15, 2012 3:15 PM
To: vantage@yahoogroups.com
Subject: Re: [Vantage] SQL to SQL Replication

Ned, are you doing replication due to multiple sites/plants in geographically different areas? Or simply having a replicated database for your bolt-ons and reporting?

Are you making changes in your “child” db or is it for read-only actions?

If you’re simply looking at having a mirrored db for SSRS, etc, then I would see no reason why you couldn’t use the native SQL Replication Services.



From: Ned C.
Sent: Thursday, March 15, 2012 2:58 PM
To: vantage@yahoogroups.com
Subject: [Vantage] SQL to SQL Replication


I am just wondering what people running with SQL as their backend are doing for SQL to SQL Replication of the Epicor Database out there?

I have gotten some conflicting information about it and want to get some information from people who are actually using it and what you are doing with it. The main issue that keeps being brought up in conversations deals with Primary Keys in the tables not existing for some of them, therefore being a problem with the straight SQL to SQL replication.

I want to simplify the use of some of the 3rd party products, EPM more specifically in this very instance, so that it will have a better, more up to date source for the information being used by EPM or any product, including eventual SSRS usage.

The main problem currently is that the system I am working with is trying to use the Epicor Replication which relies on Sonic, and the use of an OpenEdge product to be replicating data one direction between SQL databases is just completely ridiculous and inefficient. The fact that the replication doesn't work properly only makes the issue worse.

Right now there is a manual backup and restore that is being done to the secondary server for "data replication".

And with a separate but related issue maybe someone has dealt with, I have tried scripting a backup and restore, but the restoration "WITH MOVE" of Full Text Catalogs files to a new file location has been a sticking point, and wondering if anyone else has worked through something similar.

[Non-text portions of this message have been removed]




No virus found in this message.
Checked by AVG - www.avg.com
Version: 2012.0.1913 / Virus Database: 2114/4872 - Release Date: 03/15/12


[Non-text portions of this message have been removed]



------------------------------------

Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and Crystal Reports and other 'goodies', please goto: http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto: http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto: http://groups.yahoo.com/group/vantage/linksYahoo! Groups Links
5
Ned, yeah, I’ve done that before. For my scenario, I used the “child” db as a development/test platform. Unidirectional, asynchronous.

In SQL 2005/2008 it’s not too challenging to set that up.

Regarding your backup/restore method, I’ve used this script successfully to copy from one db to another... perhaps it will help you.

This restores a .BAK file to my Pilot environment, modifies the Company name to “PILOT” (or whatever), Updates the proper ports in SysAgent for the PILOT db, and then truncates the System Agent tables (because I don’t want scheduled processes from LIVE to run in PILOT).

I love SQL.

--
--
-- PROCEDURE TO UPDATE PILOT DB
--
-- *** MAKE SURE TO CHANGE "FROM DISK" LOCATION ***


RESTORE DATABASE [MfgPilot803]
FROM DISK = N'D:\SQLBackup\YOUR_DB.bak' -- **** CHANGE **** --
WITH FILE = 1,
MOVE N'mfgsys803' TO N'D:\SQLData\mfgpilot803.mdf',
MOVE N'mfgsys803_log' TO N'D:\SQLLogs\MfgPilot803_log.LDF',
MOVE N'sysft_custxprt' TO N'd:\fulltextcatalogs\pilot\custxprt\custxprt',
MOVE N'sysft_fscalldt' TO N'd:\fulltextcatalogs\pilot\fscalldt\fscalldt',
MOVE N'sysft_fscontdt' TO N'd:\fulltextcatalogs\pilot\fscontdt\fscontdt',
MOVE N'sysft_glacct' TO N'd:\fulltextcatalogs\pilot\glacct\glacct',
MOVE N'sysft_glchart' TO N'd:\fulltextcatalogs\pilot\glchart\glchart',
MOVE N'sysft_invchead' TO N'd:\fulltextcatalogs\pilot\invchead\invchead',
MOVE N'sysft_jobasmbl' TO N'd:\fulltextcatalogs\pilot\jobasmbl\jobasmbl',
MOVE N'sysft_jobhead' TO N'd:\fulltextcatalogs\pilot\jobhead\jobhead',
MOVE N'sysft_langorg' TO N'd:\fulltextcatalogs\pilot\langorg\langorg',
MOVE N'sysft_langtran' TO N'd:\fulltextcatalogs\pilot\langtran\langtran',
MOVE N'sysft_orderdtl' TO N'd:\fulltextcatalogs\pilot\orderdtl\orderdtl',
MOVE N'sysft_orderhed' TO N'd:\fulltextcatalogs\pilot\orderhed\orderhed',
MOVE N'sysft_part' TO N'd:\fulltextcatalogs\pilot\part\part',
MOVE N'sysft_podetail' TO N'd:\fulltextcatalogs\pilot\podetail\podetail',
MOVE N'sysft_quotedtl' TO N'd:\fulltextcatalogs\pilot\quotedtl\quotedtl',
MOVE N'sysft_reqdetail' TO N'd:\fulltextcatalogs\pilot\reqdetail\reqdetail',
MOVE N'sysft_rfqitem' TO N'd:\fulltextcatalogs\pilot\rfqitem\rfqitem',
MOVE N'sysft_rcvdtl' TO N'd:\fulltextcatalogs\pilot\rcvdtl\rcvdtl',
MOVE N'sysft_shipdtl' TO N'd:\fulltextcatalogs\pilot\shipdtl\shipdtl',
MOVE N'sysft_ECORev' TO N'd:\fulltextcatalogs\pilot\ECORev\ECORev',
MOVE N'sysft_FAsset' TO N'd:\fulltextcatalogs\pilot\FAsset\FAsset',
MOVE N'sysft_CostPart' TO N'd:\fulltextcatalogs\pilot\CostPart\CostPart',
MOVE N'sysft_HDCase' TO N'd:\fulltextcatalogs\pilot\HDCase\HDCase',
MOVE N'sysft_ResourceCollection' TO N'd:\fulltextcatalogs\pilot\ResourceCollection\ResourceCollection',
MOVE N'sysft_WCGroup' TO N'd:\fulltextcatalogs\pilot\WCGroup\WCGroup',
MOVE N'sysft_xfileref' TO N'd:\fulltextcatalogs\pilot\xfileref\xfileref',
NOUNLOAD, REPLACE, STATS = 10
GO

USE MFGPILOT803;

UPDATE COMPANY SET NAME = '< PILOT DB >' WHERE COMPANY = 'YOUR_COMPANY' ; --UPDATE NAME HERE!!

UPDATE SYSAGENT
SET APPSERVERURL = 'AppServerDC://localhost:8333',
MFGSYSAPPSERVERURL = 'AppServerDC://localhost:8331'
WHERE AGENTID = 'SystemTaskAgent';

TRUNCATE TABLE sysagentsched;

TRUNCATE TABLE sysagenttask;

TRUNCATE TABLE sysagenttaskparam;



From: Ned C.
Sent: Thursday, March 15, 2012 3:29 PM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] SQL to SQL Replication


I know I should be able to, but my customer keeps being told we can't do it, and I am just looking for some actual information from people who are using it and how their experiences have been.

This is just one way replication for read only purposes.

-----Original Message-----
From: mailto:vantage%40yahoogroups.com [mailto:mailto:vantage%40yahoogroups.com] On Behalf Of Vic Drecchio
Sent: Thursday, March 15, 2012 3:15 PM
To: mailto:vantage%40yahoogroups.com
Subject: Re: [Vantage] SQL to SQL Replication

Ned, are you doing replication due to multiple sites/plants in geographically different areas? Or simply having a replicated database for your bolt-ons and reporting?

Are you making changes in your “child” db or is it for read-only actions?

If you’re simply looking at having a mirrored db for SSRS, etc, then I would see no reason why you couldn’t use the native SQL Replication Services.



From: Ned C.
Sent: Thursday, March 15, 2012 2:58 PM
To: mailto:vantage%40yahoogroups.com
Subject: [Vantage] SQL to SQL Replication

I am just wondering what people running with SQL as their backend are doing for SQL to SQL Replication of the Epicor Database out there?

I have gotten some conflicting information about it and want to get some information from people who are actually using it and what you are doing with it. The main issue that keeps being brought up in conversations deals with Primary Keys in the tables not existing for some of them, therefore being a problem with the straight SQL to SQL replication.

I want to simplify the use of some of the 3rd party products, EPM more specifically in this very instance, so that it will have a better, more up to date source for the information being used by EPM or any product, including eventual SSRS usage.

The main problem currently is that the system I am working with is trying to use the Epicor Replication which relies on Sonic, and the use of an OpenEdge product to be replicating data one direction between SQL databases is just completely ridiculous and inefficient. The fact that the replication doesn't work properly only makes the issue worse.

Right now there is a manual backup and restore that is being done to the secondary server for "data replication".

And with a separate but related issue maybe someone has dealt with, I have tried scripting a backup and restore, but the restoration "WITH MOVE" of Full Text Catalogs files to a new file location has been a sticking point, and wondering if anyone else has worked through something similar.

[Non-text portions of this message have been removed]

No virus found in this message.
Checked by AVG - www.avg.com
Version: 2012.0.1913 / Virus Database: 2114/4872 - Release Date: 03/15/12

[Non-text portions of this message have been removed]

------------------------------------

Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and Crystal Reports and other 'goodies', please goto: http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto: http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto: http://groups.yahoo.com/group/vantage/linksYahoo! Groups Links




No virus found in this message.
Checked by AVG - www.avg.com
Version: 2012.0.1913 / Virus Database: 2114/4872 - Release Date: 03/15/12


[Non-text portions of this message have been removed]