How do I maintain two copies of an ArcSDE database in a SQL Server instance?

3804
2
Jump to solution
03-17-2015 12:11 PM
ConorBarber
New Contributor II

I want to maintain two identical copies of an --unversioned-- ArcSDE database in the same SQL server instance (a QA and a production database). ESRI documentation notes that you cannot rename a SQL server database when copying it. What is the recommended way to make a scheduled copy of an ArcSDE SQL Server database in a single instance?

I have access to the full range of SSMS tools and SSIS as well as ArcInfo license, ArcPy and ArcEngine.

Tags (2)
0 Kudos
1 Solution

Accepted Solutions
DavidColey
Frequent Contributor

Conor-

You can do that, but the databases cannot be named the same.  Its a lot of work and isn't really well designed for sde repositories, but you can use sql database replication if you must have both data sets un-versioned. But unless you have some business logic or exclusionary use case requiring both databases to contain un-versioned data, then I would set up one-way data replication between a parent edit database and a child production database, where the child database contains the un-versioned data.

Thanks

David

View solution in original post

0 Kudos
2 Replies
DavidColey
Frequent Contributor

Conor-

You can do that, but the databases cannot be named the same.  Its a lot of work and isn't really well designed for sde repositories, but you can use sql database replication if you must have both data sets un-versioned. But unless you have some business logic or exclusionary use case requiring both databases to contain un-versioned data, then I would set up one-way data replication between a parent edit database and a child production database, where the child database contains the un-versioned data.

Thanks

David

0 Kudos
ConorBarber
New Contributor II

Hey David,

Thanks for your reply. Seems like something of this order is more possible in PostGIS, DB2 or Oracle, but the ArcSDE renaming limitation in SQL Server makes the aforementioned three database setup (Dev, QA, Production, QA and Production being exact copies of each other) a lot of maintenance. I had attempted snapshot replication in the past but it turns out that you cannot not drop tables on the parent (QA in my case) once they are part of a SQL Server replica (only ALTER TABLE is allowed on tables part of a replica). This was a no-go for our current ETL process which requires us to drop existing tables when moving from Dev to QA. In the end I will likely go with a straight Dev to Production ETL that incorporates QA into the process itself. Long term we may move toward using an ETL tool such as FME (we do everything in ArcPy now) that is more accommodating for schema changes as ArcGIS' middleware replication leaves a lot to be desired in that category. I'm going to wait a day or two to see if anyone has any other ideas before I mark your answer as correct.

Thanks,


Conor

0 Kudos