Epicor Database Replication

Hi,

We have a requirement to replicate Epicor production DB. the purpose is to provide full access (Global Security manager) to developers on replicated DB connected Epicor environment so that the developers don’t have access on live production DB to alter any data.

The primary requirement is, developers need to have updated data in production DB to available on replica so that they can work with latest data when require troubleshooting or test customization.

For this purpose we have looked into following 2 options provided by Microsoft.

https://docs.microsoft.com/en-us/sql/relational-databases/replication/transactional/transactional-replication?view=sql-server-ver15

https://docs.microsoft.com/en-us/sql/relational-databases/tables/copy-columns-from-one-table-to-another-database-engine?view=sql-server-ver15

So the concerns are,

  1. Does setting up transnational replication cause altering tables in production DB? Has anyone tried these and encountered any issues?

  2. Are there any other replication tools you have used or recommended for this purpose?

Note that the production DB size is 60 GB (uncompressed).

Depends how up to date you require - I have setup a package in SSIS (free with SQL Server) that does the following:

Backup Live DB
Stop TEST System AppPool
Restore live backup to TEST
SQL Script to amend TEST system name to “*** TEST SYSTEM - todays date ***”
Start TEST app pool
Restart Task Agent

This allows us to have current data to test and develop against. I actually run this on a script for one of our TEST environments, so that each day I have a system to check against if accounting have made a mistake etc and want to test the reversing documents before committing to live system.

5 Likes

Another free tool you can use is PowerShell and REST. If you’re on-prem, there are PowerShell cmdlets for SQL server that can do a backup and restore. There are PowerShell cmdlets for IIS to control the AppPool. Epicor exposes the Erp.BO.CompanySvc which would allow you to update the Company records too.

In addition to the steps above, others disable Tasks, stop integrations to third party apps like Avalara, DocStar, EDI, etc. There are also some updates to location of Enterprise Search, REST endpoint names, etc. There are posts in this forum that has gone over this.

All of these can be done with PowerShell + REST. As the industry is moving to more of a DevOps strategy, the more we can automate these tasks, the more we can reduce the time to make copies of environments and reduce the errors while doing so.

Mark W.

3 Likes

(I don’t think we’ve actually answered the OP’s question but only given alternatives)

@AmilaAmarasinghe_Str - up until SQL 2016 (I think), you couldn’t actually do replication of Epicor’s database because the SQL replication process balked and having GUID key fields (or something like that). Trust me, I tried it a few times between SQL 2000 and 2014.

In turn, everyone like @Mark_Wonsil, @markdamen and I have developed different ways to provide a DEV copy of the data on demand. You’ve heard about SSIS, PowerShell/REST, and I use good old SQL scripting + a little manual appserver management.

None of these are bad, but more automation is always good. With the new versions of SQL server, you should be able to do a one-direction replication to a DEV server without issue. I might reach out to EpicCare and ask about current replication process/issues. Back a few years you had to run a script to alter some key indices or something in order to make it work, but I haven’t looked into recently.

2 Likes

I only bring up PowerShell/REST as it is a solution that works well for both on-prem and cloud users. :wink:

I think common tools makes a stronger solution as it allows companies to move freely from on-prem to the cloud and vice-versa.

2 Likes

Agreed @Mark_Wonsil - Common tools/methods that work for both of us are certainly better…

@AmilaAmarasinghe_Str never mentioned if they are cloud or not, and given the direction of Epicor, I think @markdamen and I may be re-writing our ‘scripts’ sooner than we want to be!

We have recently set up transaction replication for reporting and it works fine. As all of the code for bpms, baqs, dashboards is al in the database now, I am not sure how that would impact the dev environment.

This is a link to KB0050119 on the steps needed after moving a database.

https://epicorcs.service-now.com/epiccare?id=epiccare_kb_article&sys_id=d78dd100db24fb44e325a334ca961971

@markdamen Does the daily copy not wipe out code you are working on?

Didn’t Epicor offer an option for an education type environment that would replicate your live data, for users to train on?

Yes, when you purchase embedded education, they provide an environment that you use to do the “lab work.”

I have multiple test enviroments - the daily refresh one is called “MRP”, and allows us to test things that we know will get overwriten at 1am the following day.

We have another called Dev, which we refresh once every few months or after a version change.

We have one called UpgradeTest, which I always apply the latest version to for testing.

4 Likes

@markdamen Thanks, I am going live in January and still working on how to do a weekly let alone nightly testng area.

1 Like

I’ll check that my SSIS package doesn’t include any passwords (shouldnt, because we use Windows Auth) and then happily share it with you.

@markdamen nd @Mark_Wonsil Thank you both for sharing the solutions. Will try both.

Can you tell me what’s the size for DBs? Usually how much time does the process takes?

@MikeGross - Actually i did opened a case in Epiccare and what they mentioned is they do not have particular recommendation on what options are better and asked me to reach community here for their experience.

Does anyone have experience with any other 3rd party tools such as Oracle GoldenGate?

Hi Mark

Would also be interested in that as would love to automate that process.

Been recently listening to The Phoenix Project on audible about IT in a large manufacturing business. Not all relevent to an SME but definately makes you think about how best to apply IT in an SME.

Regards

Richard

1 Like

Stop App Pool
Invoke-Command -ComputerName "EpicorAPP01" -ScriptBlock { Stop-WebAppPool -Name "ERP102300MRP" }

Backup DB

BACKUP DATABASE [Epicor10Live] TO  DISK = N'T:\AutoCopy_Epicor10DB.bak' WITH COPY_ONLY, NOFORMAT, INIT,  NAME = N'Epicor10Live-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10

Restore DB

ALTER DATABASE [Epicor10MRP] SET SINGLE_USER WITH ROLLBACK IMMEDIATE RESTORE DATABASE [Epicor10MRP] FROM  DISK = N'T:\AutoCopy_Epicor10DB.bak' WITH  FILE = 1,  MOVE N'Epicor10Live' TO N'M:\SQL DBs\Epicor10MRP.mdf',  MOVE N'Epicor10Live_log' TO N'L:\SQL Logs\Epicor10MRP_Log.ldf',  NOUNLOAD,  REPLACE,  STATS = 5
ALTER DATABASE [Epicor10MRP] SET MULTI_USER
GO

Update SQL Script

USE [Epicor10MRP]
GO

UPDATE [Erp].[Company]
	SET [Name] = '*** BV Dairy - MRP Test Environment ' + convert(varchar, GetDate(), 103) + ' ***'
	WHERE [Company] = 'BVD'
GO

UPDATE [Ice].[SysCompany]
	SET [Name] = '*** BV Dairy - MRP Test Environment ' + convert(varchar, GetDate(), 103) + ' ***'
	WHERE [Company] = 'BVD'
GO

UPDATE [Ice].[FavItems]
   SET [AppServerURL] = REPLACE(AppServerURL, 'Live', 'MRP')
GO

UPDATE [Ice].[Menu]
   SET [Program] = REPLACE(Program, 'Live', 'MRP')
GO

UPDATE [Ice].[SysCompany]
   SET [EntSearchURL] = ''
GO

UPDATE [Ice].[SysConfig]
   SET [SysCharacter01] = ''
GO

UPDATE [Ice].[BpDirective]
   SET [IsEnabled] = 0
   WHERE [Source] = 'DB' and [BpMethodCode] = 'Erp.PartTran.Update' and [Name] = 'KanbanAutoPrint'
GO

Start App Pool

Invoke-Command -ComputerName "EpicorAPP01" -ScriptBlock { Start-WebAppPool -Name "ERP102300MRP" }

Build Email Body

public void Main()
		{
			// TODO: Add your code here

            string body = string.Empty;
       
            body = "Epicor10LIVE Database has been copied to Epicor10MRP\n\n";
            body += "The LATEST Transaction Date in the newly copied Epicor10MRP DB is ";
            body += Dts.Variables["User::LatestTranDate"].Value;
            body += "\n\nIf this date is not today's date, then check to ensure the copy has worked as expected.";
            Dts.Variables["User::EmailBody"].Value = body;
         
            Dts.TaskResult = (int)ScriptResults.Success;
		}
8 Likes

Thanks Mark will give this a try.

Really appreciate.

Regards

Richard

@markdamen - Thanks for sharing that!!!

@AmilaAmarasinghe_Str - I’ve not seen anyone mention another product such as that but there are plenty of ‘scripting’ mechanisms out there. For example, many folks can do this all in PowerShell and if you do a bit more research the Epicor Admin Console functions are almost all available to you in command line form. Given all the options presented here (and above) I’m sure you can fashion an easily maintainable and reliable process for little or no $$.

1 Like

There’s a follow-up to The Phoenix Project called The Unicorn Project.

@markdamen - could you provide the code for the steps to stop and start the task agent?