Epicor Database Replication

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?

Sure - it’s just another Powershell 1 liner:

Invoke-Command -ComputerName "EpicorAPP01" -ScriptBlock { Stop-Service -Name "EpicorICETaskAgent3.2.300.0" }

So you’re actually stopping the Task Service, and not specific Task Agents.

Does this run the risk of interfering with anything on the other Apps (like the Live one that was copied)?

Correct, it’s stopping the service and therefore bringing down all Task agents for the apps hosted on that server. There are no processes configured to run at the same time, and the factory staff that would be working at 1am when it processes this SSIS package wouldn’t be doing anything that requires task agent.

I’ll check the help files in EAC, there could well be a script to stop just my MRP task agent.

Sounds like Mighty Car Mods Unicorn Circuit :yum:

1 Like

Take a look at your [Ice.ExtServiceRegistration] table. I think you might want to clean up any references to your production task agents. Personally I’d be inclined to delete the task agent on your destination app server and add it back again after you’ve done the restore. It makes me feel uncomfortable having any reference to production environments in a test/dev one. As always happy to told different.